Ideas about how to produce reports from a database

Nelson78

Well-known Member
Joined
Sep 11, 2017
Messages
525
Office Version
  1. 2007
Hello everybody.

I've just taken charge of the database you can see in this link.

From this db, updated day by day (see column N), I have to produce some reports.

https://imgur.com/wHb7VtH

First of all I want to specify that my predecessor used to manage the reports with formulas, for this reason I've inherited a too heavy workbook (I think it will blow up sooner or later). So, I have to change strategy.

The report framework is usually with an horizontal axes as a timeline (based on column N), and a vertical axis with the requested information.
For instance, you can see one of them in the link below.

https://imgur.com/CsMUpLd

At the moment, I've create an array to store the database.


Code:
Sub database()

Dim database As Worksheet
    Set database = Sheets("Database")

Dim Ary As Variant, Tmp As Variant, Cols As Variant
Dim r As Long, c As Long

Dim lr As Long
    lr = database.Cells(Rows.Count, "A").End(xlUp).Row

'Si definisce l'area della tabella su cui effettuare la lavorazione
 With database.Range("A2:U" & lr)
      Ary = .Value2    
End With

   ReDim Cols(1 To UBound(Ary, 2) - 1)
   For c = 2 To UBound(Ary, 2)
      Cols(c - 1) = c
   Next c

'.............................................


How can I perform the task?

Considering the reports analised once per month, I can also produce the entire report when requested, even if I have to wait some minutes.

Or maybe a pivot strategy can fullfill the needs, without any elaboration with vba?

Thank you in advance for your contributions.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Hi,

I would say it would depend what your comfortable with maintaining. Pivot tables make it easy to slice & filter data if setup correctly and that would be the same for formulas & VBA.

From the look of your data I would probably go with formula extraction if pivot tables didn't give me the result and easier to troubleshoot.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top