SSRS How to Compare Columns to First Column in Group

1
votes

I'm trying to create what seems like should be a pretty simple matrix report and I'm hoping someone can help. I have dataset that returns sales region, Date, and sales amount. The requirement is to compare sales for the various time periods to the current date. I'm looking to get my matrix to look something like this:

   CurrentSales    Date2Sales  CurrentVSDate2    Date3Sales   CurrentVSDate3 
1   1000           1500        -500              800           200
2   1200           1000         200              900           300
3   1500           1100         400              1400          100

I can get the difference from one column to the next, but I need all columns to reference the CurrentSales column. Any help would be greatly appreciated.

Currently my data set is pulling in a date, region, product and sales amount. I then have three parameters, CurrentDate, PreviousMonth, PreviousQuarter. The regions and products are my row groups and the dates are the column groups. Next I added a column inside the group with the following expression: =Sum(Fields!SalesAmount.Value)-Previous(Sum(Fields!SalesAmount.Value),"BookingDate"). I know this isn't correct because it compares the values to the previous date in the column group and I need the comparision to be to the First date in the column group.

2
sqlreporting-servicesssrs-2008ssrs-grouping
Can you tell us what your current structure is, what you've tried, share the expressions etc? – Jeroen
I updated the question include more details. Thanks. – Shawn C

2 Answers

1
votes

Example:

Using Expressions you can:

=iif(Sum(Fields!SalesAmount.Value)= Previous(Sum(Fields!Date2Sales.Value)),

=iif(Sum(Fields!EndBalance.Value)=0, Nothing, Sum(Fields!EndBalance.Value)) You can also use Switch.

0
votes

The easiest way to get this result would probably be in your query. Add a field to every row returned maybe called "Current Sales." Use a correlated subquery there to get the right value for comparison. Then your comparison can be as simple as =Fields!Sales.Value - Fields!CurrentSales.Value or similar.

There are some ways to do this at the report level, but they are more of a pain: my current favorite of those is to use custom code embedded in the report. Another approach is to use Aggregates of aggregates.