Results 1 to 2 of 2

Thread: Ideas about how to produce reports from a database
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2017
    Posts
    382
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Ideas about how to produce reports from a database

    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.

  2. #2
    Board Regular RasGhul's Avatar
    Join Date
    Jul 2016
    Posts
    548
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Ideas about how to produce reports from a database

    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.

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •