Ideas about how to produce reports from a database

[Nelson78]

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.

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.

 

[RasGhul]

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.