Ideas about how to produce reports from a database

Nelson78

Active Member
Joined
Sep 11, 2017
Messages
434
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.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
609
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,000
Messages
5,411,753
Members
403,394
Latest member
Mohan Kumar

This Week's Hot Topics

Top