Link to home
Start Free TrialLog in
Avatar of cindyfiller
cindyfillerFlag for United States of America

asked on

Total doesn't work in a matrix in SSRS 2008 R2

I have a budget report that sums up the transactions by account code and person and then lists the total of those transactions and the budget for each line item.  The transactions are based on summing the dollars, but the budget is one amount for the sum of the transactions.  I've done this in a matrix - here is a quick example

John Doe
   10-6070          Entertaining        $800 (sum of transactions)              $3,000 (budget for that account code/person)
   10-6073          Lodging                $400                                                    $2,500
                                 Total                $1200                                                  $5,500

When I sum the column of transactions, it is correct.  But I can't sum(first(budgetamount)) as you can't use a first in an outer aggregate.  

On tables (not matrixes) I've been able to use a combination of:
on the sql side:   row_number() over (partition by fieldname order by fieldname) as 'RowNumber'

followed by this on the report side:  =sum(cdec(iif(Fields!RowNumber.Value=1,fieldname,0)))

Unfortunately, this does not work on a tablix - I end up with a value that appears to be just one of the random budget amounts.

How can I total up the budget so it is correct??
Avatar of Arifhusen Ansari
Arifhusen Ansari
Flag of India image

Hi,

can you please provide some sample of expected output, so we can understand a request?
Avatar of cindyfiller

ASKER

I'll expand on the example from above:

   Account           Description          Expense                                              Budget
    10-6070          Entertaining        $800                                                    $3,000
    10-6073          Lodging                $400                                                    $2,500
    10-6955          Air Travel             $700                                                     $4,000

               Total Should be:              $1200                                                  $9,500

Under the budget total the number that displays could be any of the numbers above - it could be $3,000 or $2,500 or $4,000 - it seems to be random on what number it takes.
ASKER CERTIFIED SOLUTION
Avatar of Arifhusen Ansari
Arifhusen Ansari
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial