cindyfiller
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!RowNu mber.Value =1,fieldna me,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??
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!RowNu
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??
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
can you please provide some sample of expected output, so we can understand a request?