Announcement

Collapse
No announcement yet.

Create summary report using Pivot Table and publish as PDF

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Create summary report using Pivot Table and publish as PDF



    I have a database (please see attached xlsx for a short sample) with price information for 4 firms.

    The data in cols A thru F is to be updated on a regular basis.
    I'm trying to create summary report that compares the pricing for these 4 firms; information to be included in the 1 sheet summary:
    1,2,&3 are per category and total
    1. Firm 1 vs Firm 2: Number of products (count of IDs), Num of products not listed by Firm 2 (blanks in col D), Num of products with same prices, Num of products with Firm 1 having greater prices, Num of products with Firm 2 having greater prices.
    2. Firm 1 vs Firm 3: Number of products (count of IDs), Number of products (count of IDs), Num of products not listed by Firm 3 (blanks in col E), Num of products with same prices, Num of products with Firm 1 having greater prices, Num of products with Firm 3 having greater prices.
    3. Firm 1 vs Firm 4: Number of products (count of IDs), Number of products (count of IDs), Num of products not listed by Firm 4 (blanks in col F), Num of products with same prices, Num of products with Firm 1 having greater prices, Num of products with Firm 4 having greater prices.
    4. I have created Ranks for the Firms on a product basis. A summary comparison of product counts per rank is needed.
    5. Average Price comparison per category/total.

    My question is can all these be done in a pivot table/chart? If yes, any pointers?
    any other way of creating this report?

    Also, How can I make the sheet easy to update? My line of thought is that the data in cols A thru F can get automatically updated if a single text file is changed. A s/w tool can generate and dump the data in a data file (txt or xls), the summary excel sheet should update its raw data (highlighted in blue) as this linked data file changes.

    Can I automate a PDF file generation of this summary sheet? if yes, any pointers?
    * i have a free tool that converts excel to pdf, i'm wondering if i can call it thru a macro and automate the pdf generation. (also email the pdf thru excel)

    Please do share any thoughts you have.
    Attached Files
    Last edited by AAE; September 10th, 2010, 06:00. Reason: revise thread title

  • #2
    Re: Create summary report using Pivot Table and publish as PDF

    I am not any good with Pivot Tables but there are a lot of tutorials at OzGrid and contextures. They should be able to help OzGrid http://www.ozgrid.com/Excel/excel-pivot-tables.htm and contextures http://www.contextures.com/xlpivot07.html

    Comment


    • #3
      Re: Create summary report using Pivot Table and publish as PDF

      Its not quite clear to me if your database is actually in Excel or if you are retrieving the data from another source. It is certainly possible to update Excel data from a .txt or .xls file. As for updating the summary Workbook from the source Workbook there is no need to do that via an intermediate file. You can just directly link between the two workbooks. If you have not done this before it is a similar process to when you create a formula on one Worksheet with arguments from another Worksheet, only you have both Workbooks open in Excel at the same time and you link between them.

      Comment


      • #4
        Re: Create summary report using Pivot Table and publish as PDF

        Originally posted by Rob Xaos View Post
        if your database is actually in Excel or if you are retrieving the data from another source.
        I retrieve data from another source. Since the raw data has 54000 rows, I think it might be better to use a .txt file.

        How can I link my xls sheet to the data file, so that every time the .txt file is updated the raw data in the xls sheet also gets updated.
        Last edited by Hrithik; September 11th, 2010, 00:09.

        Comment


        • #5
          Re: Create summary report using Pivot Table and publish as PDF

          It rather depends on the external data source. For most data sources (Access/SQL etc) linking your Excel directly to the data may be better than exporting the external data to a .txt and then importing the .txt to Excel.

          Comment


          • #6
            Re: Create summary report using Pivot Table and publish as PDF

            We have a proprietary external data source and txt file dumps are what we currently use for data access.

            Comment


            • #7


              Re: Create summary report using Pivot Table and publish as PDF

              Ok to set up the link between your Excel workbook and your text file the piece of code you need is:

              Code:
                  With ActiveWorkbook.Worksheets(<sheet identifier>).QueryTables.Add( _
                          Connection:= "TEXT;<file path>\<file name>.txt", _
                          Destination:=Range("<top left of data table on worksheet>"))
                      .Name = "<data name>"
                      .FieldNames = False
                      .RowNumbers = False
                      .FillAdjacentFormulas = False
                      .PreserveFormatting = True
                      .RefreshOnFileOpen = True
                      .RefreshStyle = xlInsertDeleteCells
                      .SavePassword = False
                      .SaveData = True
                      .AdjustColumnWidth = False
                      .RefreshPeriod = 0
                      .TextFilePromptOnRefresh = False
                      .TextFilePlatform = 850
                      .TextFileStartRow = 1
                      .TextFileParseType = xlDelimited
                      .TextFileTextQualifier = xlTextQualifierDoubleQuote
                      .TextFileConsecutiveDelimiter = False
                      .TextFileTabDelimiter = True
                      .TextFileSemicolonDelimiter = False
                      .TextFileCommaDelimiter = False
                      .TextFileSpaceDelimiter = False
                      .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
                      .TextFileTrailingMinusNumbers = True
                      .Refresh BackgroundQuery:=False
                  End With
              The above only needs to be done when you first make the connection to your file or if the file changes.

              Most of the properties should be self explanatory. The above is for a text file with fields delimited by single tabs and with no field headings.

              You can arrange for the QueryTable (a range in your Workbook linked to the external file) to update on a regular basis (RefreshPeriod), when the Workbook is opened (RefreshOnFileOpen) and/or via a manual update.

              You can trigger a refresh from VBA with:
              Code:
              ActiveWorkbook.Worksheets(<sheet identifier>).QueryTables("<data name>").Refresh
              See if that is enough to get you started, if not come back to us.

              Comment

              Working...
              X