December 8, 2011 at 12:07 am
I'm fairly new to BIDS and SSRS. I'm writing a query to that needs to subtotal transaction type for the rows and period on the columns. I have 3 transaction types, Reciepts, Disbursements and Prior Year transactions. I can subtotal for each of these. However, I need a field in each period column that will take (Reciepts - Disbursments + Prior Year Transactions).
Row is Grouped by Fund > Transaction Type > Transaction Code
Column is Grouped by Period.
I'm hoping for some friendly help. I'm stumped on this one.
Thanks.
December 8, 2011 at 4:17 am
A screenshot will be helpful. But try this:
For all subtotal cells give a name -- click on a cell then go to properties pane, Under General section there is a property called "Name" -- give relevant names to each of these 3 subtotals.
Where ever you want to add the formula: (Reciepts - Disbursments + Prior Year Transactions) -- right click on the cell, select Expression and add the formula.
eg: =(ReportItems!Reciepts.Value - (ReportItems!Disbursements.Value + ReportItems!PriorYearTransactions.Value))
Hope this helps,
regards
Natraj
December 8, 2011 at 8:36 am
Hi Thanks for the response.
I've attached 2 images. One in design view and one in preview mode. I can't seem to figure out how to distinguish one total from another in the design view to do the calcualtion. You will see in the preview mode Totals for B, C and D.
Thanks for any suggestions or help you can provide.
David
December 8, 2011 at 1:58 pm
Tnx for screenshots. Try below link, there is a formula with sum(iff) using report group function. Iam sure that's what ur after.
Goodluck,
Regards
Natraj
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply