none
SSRS 2005 + display multiple invoices

    Question

  • Hi,

    I use the following link  http://jesborland.wordpress.com/2010/11/17/  to create SSRS 2005 Invoice report. It is used for only 1 invoice. I used same to show multiple invoices.

    This is how I did:

    The query I used returns the data as:

    InvoiceID

    Invoice Heading

    User

    Desc

    Category

    AccountID

    Amount

    1

    Invoice Changes1

    Robert

    Invoice at JPM

    Personal

    1

    1.00

    1

    Invoice Changes1

    Robert

    Invoice at JPM

    Personal

    2

    2.00

    2

    Invoice Changes2

    Robert

    Invoice as check

    Savings

    1

    3.00

    2

    Invoice Changes2

    Robert

    Invoice as check

    Savings

    3

    4.00

    2

    Invoice Changes2

    Robert

    Invoice as check

    Savings

    4

    5.00

    The above result set contain both invoice header and details.

    I want a report like this:

    Page1:

                                                    Invoice Changes1 (page header)

    User:Robert

    InvoiceID: 1

    Description: Invoice at JPM

    Category:Personal

    Accounts:

    Account

    Amount

    1

    $1.00

    2

    $2.00

                   

     Audit Details:

    Creation Date:

    Created By

    Page footer Here

      

    Page2:

    Invoice Changes2 (page header)

    User:Robert

    InvoiceID: 2

    Description: Invoice as Check

    Category:Savings

    Accounts:

    Account

    Amount

    1

    $3.00

    3

    $4.00

    4

    $5/-

                   

    Audit Details:

    Creation Date:

    Created By

    Page footer Here

      

    In other words, each invoice should be displayed in one page. I tried it this way.

    I added list control to the report and placed everything in list. For accounts I added table and dragged account fields on to the table. So table is with in List control. I right clicked on List control ->Properties->editGroup Details -> General tab. I told to group on ‘Invoice ID’ and check the check box for ‘Page break at end’.

    When I run report, the above is shown in 2 pages. But, in second page only account table data is changing. Rest of invoice deails like user, desc etc is remaining same as page 1. So not sure what is happening.

     I appreciate your help.

    Thursday, October 24, 2013 6:02 PM

Answers

  •  I was able to fix the issue. In List properties, in addition to the above settings, I set

    On General Tab:

             set datasetname, check the boxes for 'Insert a page break after the list' and 'Fit this list on one page if 

                         possible'.

    On Edit Details group:

              set group on Expression and Parent group to same field and check checkbox for 'Page break end'.

    This made it work. each invoice on each page and all invoices showed up.

               

    • Marked as answer by Spunny Monday, October 28, 2013 6:16 PM
    Monday, October 28, 2013 6:16 PM

All replies

  • Did you try putting a group in report for Invoice fields and add all account fields inside group?

    Then put Account details inside detail rows

    Thursday, October 24, 2013 6:18 PM
  • Hi Visakh,

    Thanks for reply. I am not getting what you said. But all I did is on

    List control, right click - go to properties - click on edit group details and add invoiceID to group on and

    check the checkbox for page break after this.

    Please explain me how to do your way.

    putting a group in report for Invoice fields and add all account fields inside group: what do you mean by this. group by each field. you mean right click on field textbox and group?

    Account details inside detail rows - of what. They are detail rows of table that I added which is with in list control.

    Thanks

    Thursday, October 24, 2013 6:24 PM
  • See this link. I've put a sample report for you based on your sample data

    Report Link

    Please Mark this as answer if this matches your requirement

    Thursday, October 24, 2013 7:02 PM
  • Hi Visakh,

    I am not able to open the rdl report in BIDS. It just shows as xml and get error saying 'The value expression for the textbox8 contains error: Operation is not valid due to the current state of object.

    Thanks

    Monday, October 28, 2013 6:12 PM
  •  I was able to fix the issue. In List properties, in addition to the above settings, I set

    On General Tab:

             set datasetname, check the boxes for 'Insert a page break after the list' and 'Fit this list on one page if 

                         possible'.

    On Edit Details group:

              set group on Expression and Parent group to same field and check checkbox for 'Page break end'.

    This made it work. each invoice on each page and all invoices showed up.

               

    • Marked as answer by Spunny Monday, October 28, 2013 6:16 PM
    Monday, October 28, 2013 6:16 PM