locked
Mix Summary and Detail RRS feed

  • Question

  • User1510859543 posted

    We have a reporting need to print a non-standard invoice.  The first page needs to show a summary of the products purchased. The pages following need to show a grouping detail of the items purchased.  The trick is that it must print summary for invoice 1, page break, then details for invoice 1, page break, then summary for invoice 2, page break, then details for invoice 2, etc. so it would be like below.

    Invoice 1 Summary
    Product 1    $1,000.00
    Product 2    $2,150.00
    --Page break--
    Invoice 1 - Product 1 - Item A   $500.00
    Invoice 1 - Product 1 - Item B   $500.00
    Invoice 1 - Product 2 - Item D   $2,150.00

    --Page break--
    Invoice 2 Summary
    Product 1   $500.00
    Product 3   $250.00
    --Page break--
    Invoice 2 - Product 1 - Item A   $500.00
    Invoice 2 - Product 3 - Item C   $250.00

    ... etc.

    I have separate stored procedures for the summary and the detail but could come from a single sp as the base source for both is the BillingInvoice table.

    Any suggestions on how to structure this would be helpful.  I have tried 2 separate tablix but they each printed all summary invoice pages first, followed by all detail pages, almost like 2 separate reports.

    Thanks.

    Tuesday, May 19, 2015 3:17 PM

All replies

  • User1711366110 posted

    Any suggestions on how to structure this would be helpful.  I have tried 2 separate tablix but they each printed all summary invoice pages first, followed by all detail pages, almost like 2 separate reports.

       As per this case, you can retrieve the datas by using single SP is enough because the datas belongs to same table.
    Moreover I suggest you to use cascade tablix report. for that you can do the following steps :

    1.create one tablix & pull the data using the same dataset

    2.Go to grouping panel >>Row Grouping >>right click on the Details group>> Group properties >>General tab>> Group on should be “Product” click.

    3.go to report layout and add sum function to numerical value

    4.create one more tablix using the same dataset

    5.Preview the report. you can see first tablix showing summary data and second tablix showing detail data

    6.Go to Report layout >> first tablix>> right click on left most of the row >> Insert row>> Insert Group below

    7.Merge the cells of newly created row

    8.Right click on the second tablix, copy and paste on newly created row of first tablix. you can remove country column as it is already available in first tablix.

    9.For better visibility, we can add drill down also. For adding drilldown,  right click on the row where second tablix is pasted>>Row Visibility>>Change the display option – When the report is initially run to “Hide” and display can be toggled by Country Name textbox and click ok

    for more information, Click here to refer the tablix inside tablix in step by step process.

    Wednesday, May 20, 2015 5:08 AM
  • User1510859543 posted

    That worked great, with two additional needs.  Also, FYI, the highest level group is InvoiceNumber which groups the Products in summary tablix 1 and it also groups Product Detail in tablix 2.  The tablix 2 was placed into a merged cells row as the last row of the InvoiceNumber group and it works great as the summary prints, then all the details print, then starts over again in 2nd InvoiceNumber which is perfect.

    1. How can I get the 2nd tablix to start on a new page?  I tried setting the tablix 2 PageBreak to Between and even tried Start but neither setting resulted in the tablix 2 starting on a new page. Also, in this situation the page numbers are not resetting. I have tried both in tablix 1 and in tablix 2 but page numbers do not reset for each instance of tablix 1 like I prefer.

    2. We need to print the Company ID number in the footer of the report and it works on summary page because it refers to a ReportItem!CompanyID.Value but that TextBox does not exist in detail tablix 2 pages.  Is there a way to have it print on all pages?

    Thanks again, you have been a great help so far.

    Wednesday, May 20, 2015 9:35 AM