Results 1 to 3 of 3
  1. #1
    Join Date
    May 2008
    Posts
    2

    Unanswered: Totals & Grouping in SSRS 2005

    I think this is a simple question for creating a report with SQL Reporting Services, but I can't seem to find any straight forward answers for this.

    I have some detail data I'm pulling in from 1 view into my report dataset:
    ------------------------------------------------------
    Loan Number Loan Amount Payment Date Payment
    1000 550000 Jan 2008 15000
    1000 550000 Feb 2008 15000
    1000 550000 Mar 2008 15000
    1002 300000 Jan 2008 50000
    1003 450000 Jan 2008 20000
    1003 450000 Feb 2008 20000
    ------------------------------------------------------
    I'm creating a RDL with this data and in my detail row, I'm showing the Loan Pmt and Loan Date with Grouping on the Loan Number & Loan Amount.

    My problem is that when I look at my grand total, it is summing up the Loan amount more than 1 time - once for each detail record.
    ------------------------------------------------------
    RDL
    Loan Number Loan Amount Payment Date Payment
    1000 550000 Jan 2008 15000
    Feb 2008 15000
    Mar 2008 15000
    Subtotal 45000
    Loan Balance 505000

    1002 300000 Jan 2008 50000
    Subtotal 50000
    Loan Balance 250000

    1003 450000 Jan 2008 20000
    Feb 2008 20000
    Subtotal 40000
    Loan Balance 410000

    Grand Totals 2850000 135000
    ------------------------------------------------------

    So my total on the Loan Amount column is incorrect, it should really be 1,300,000, but instead it's calculating 2,850,000.

    Can someone explain to me how I should correct my grouping or dataset in a table?

    I was able to use a Subreport for my detail records, and keep the Loan details in my Parent report to get the right totals, but then I hit the issue with exporting to Excel - "Subreports within table/matrix cells are ignored"

    Any suggestions??

  2. #2
    Join Date
    May 2007
    Posts
    49
    I think there is no easy solution to your problem, one way is you can change your query like -

    Select
    L.Loan_Id,
    L.Loan_Amount,
    L.Payment_Date,
    L.Payment,
    (Select Sum(Distinct Loan_Amount) from Loan) as Total
    From Loan L

    and then use the Toal field from dataset in Page footer to display total -
    =Fields!Total.Value
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  3. #3
    Join Date
    May 2008
    Posts
    2
    Thanks Mihir, I did end up doing that, I just thought there would be an easier way to handle this. I guess I'll have to check out SSRS 2008!
    I think that version will handle the export with the Subreport.

Posting Permissions

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