Reputation:
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.
Upvotes: 1
Views: 5127
Reputation: 51
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.
Upvotes: 1
Reputation: 23789
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.
Upvotes: 0