Hello, I'm new to reporting services and can't find a way of doing this. I have trying to create an income statement. My dataset has the ledger grouped by Report Group and Categories, and I need to calculate differences between reporting groups. example: Revenue Jan Feb Total Cat 1 10 15 25 Cat 2 20 25 45 Sub Total 30 40 70 Ope Expenses Cat 1 10 15 25 Cat 2 25 20 45 Sub Total 35 35 70 Var Inc -5 5 0 <--- I can't do this. How can I say subtract the total of group 2 from the total of group 1? Other Income Cat 1 5 5 10 Cat 2 5 5 10 Subtotal 10 10 20 This is all a single dataset, how can I do this? THANK YOU!

In your dataset, jan, feb are separate columns or they all come in one field in the dataset but used as column groups in the report. Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH

Hi MaPaMa, Since there are all single datasets, I think you might not lay all these datafields into one data region control directly. However Reporting Services 2008 R2 has a lookup function to retrieve the value from the specified dataset for a name/value pair where there is a 1-to-1 relationship. For example, for an ID field in a table, you can use Lookup to retrieve the corresponding Name field from a dataset that is not bound to the data region. If you want to retrieve multiple values for a single name or key field where there is a 1-to-many relationship, use LookupSet Function (Report Builder 3.0 and SSRS). To call Lookup for a set of values, use Multilookup Function (Report Builder 3.0 and SSRS). If the report services you are using is not reporting service 2008 R2, you should join these datasets into a new dataset. Then you could design the chart report with the new dataset. For more information, please see, Lookup Function (Report Builder 3.0 and SSRS): http://msdn.microsoft.com/en-us/library/ee210531.aspx If you have any question, please feel free to ask. Thanks, Challen Fu Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Hi MaPaMa, Since there are all single datasets, I think you might not lay all these datafields into one data region control directly. However Reporting Services 2008 R2 has a lookup function to retrieve the value from the specified dataset for a name/value pair where there is a 1-to-1 relationship. For example, for an ID field in a table, you can use Lookup to retrieve the corresponding Name field from a dataset that is not bound to the data region. If you want to retrieve multiple values for a single name or key field where there is a 1-to-many relationship, use LookupSet Function (Report Builder 3.0 and SSRS). To call Lookup for a set of values, use Multilookup Function (Report Builder 3.0 and SSRS). If the report services you are using is not reporting service 2008 R2, you should join these datasets into a new dataset. Then you could design the chart report with the new dataset. For more information, please see, Lookup Function (Report Builder 3.0 and SSRS): http://msdn.microsoft.com/en-us/library/ee210531.aspx If you have any question, please feel free to ask. Thanks, Challen Fu Please remember to mark the replies as answers if they help and unmark them if they provide no help.

BH, They are column groups in the tablix.

BH, They are column groups in the tablix.

Thank you, I will check it out. I found a way of getting done using the Dataset filters in the Tablix. I added a field to the dataset and then "filter" on that field for the particular group that I wanted. That way, I could pick the groups for a particular tablix's total, I then listed the tablix in the order that the report needed them.

Thank you, I will check it out. I found a way of getting done using the Dataset filters in the Tablix. I added a field to the dataset and then "filter" on that field for the particular group that I wanted. That way, I could pick the groups for a particular tablix's total, I then listed the tablix in the order that the report needed them.