Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

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

  1. #1
    Join Date
    12th August 2010
    Posts
    72

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by AAE; September 10th, 2010 at 06:00. Reason: revise thread title

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    17th August 2010
    Posts
    71

    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    9th September 2009
    Posts
    1,599

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    12th August 2010
    Posts
    72

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

    Quote 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 at 00:09.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    9th September 2009
    Posts
    1,599

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    12th August 2010
    Posts
    72

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    9th September 2009
    Posts
    1,599

    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:

    VB:
    With ActiveWorkbook.Worksheets([COLOR=blue]<sheet identifier>[/COLOR]).QueryTables.Add( _ 
        Connection:= "TEXT;[COLOR=blue]<file path>[/COLOR]\[COLOR=blue]<file name>[/COLOR].txt", _ 
        Destination:=Range("[COLOR=blue]<top left of data table on worksheet>[/COLOR]")) 
        .Name = "[COLOR=blue]<data name>[/COLOR]" 
        .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:
    VB:
    ActiveWorkbook.Worksheets([COLOR=blue]<sheet identifier>[/COLOR]).QueryTables("[COLOR=#0000ff]<data name>[/COLOR]").Refresh 
    
    
    See if that is enough to get you started, if not come back to us.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Statistical Summary Report
    By pytelium in forum EXCEL HELP
    Replies: 6
    Last Post: February 11th, 2008, 23:06
  2. Generate Summary Report
    By cozartistrue in forum EXCEL HELP
    Replies: 1
    Last Post: January 3rd, 2007, 12:24
  3. creating a pivot Table-this should be really easy
    By erinnicole782 in forum EXCEL HELP
    Replies: 2
    Last Post: April 27th, 2006, 02:42
  4. creating special pivot table report
    By h in forum EXCEL HELP
    Replies: 4
    Last Post: September 12th, 2005, 14:14
  5. Summary report
    By Bigfoot in forum EXCEL HELP
    Replies: 4
    Last Post: November 20th, 2004, 07:44

Bookmarks

Posting Permissions

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