September 21, 2011 at 11:12 am
Hey everyone...hoping someone can give me some direction here. I don't have much experience with Matrix reports so maybe someone can shed some light.
I have a report that has a MultiValue parameter called SEASON, which basically is the control for which columns to display. The user selects 2 seasons they want to compare, and it will show Open, Pick, Invc, Price, Net Price, Cost and Profit for each of those seasons side by side. This is working fine.
The user who requested this report now wants to see a variance on Net Price between the 2 sets of data. So basically, get the Net Price from season 2, compare it to the Net Price from season 1, and display a percentage of change off to the right. I cannot figure out how to get this to work using this report.
I have attached a screenshot of how the report looks in BIDS setup, and an excel file of what is being output presently and what the user wants added highlighted in yellow.
Hoping someone can give some guidance here. Thanks for your help!
Tony
September 21, 2011 at 11:16 am
There's a previous function but afaik it applys only to rows.
The only thing I can offer as work around (and I hope you find a better way) is to add an extra column in the dataset for previous year. So then you'll be able to do the maths.
September 21, 2011 at 11:32 am
Yeah, from what I've read so far the Previous function won't work in a Matrix. I read something that was kinda ugly but gave me hope, setting up 2 calculated variables to get the Difference and the FirstValue then setting up a table outside the Matrix to display the values...but I couldn't figure out how to get that to work, since the Seasons are a parameter and aren't a fixed Spring this year vs Spring last year. It could be Spring vs Summer.
The user could just export to Excel and put in a formula to calculate the variance...but that's too much work. 😉
Tony
September 21, 2011 at 11:36 am
Well you could always do the pivot in the query and return 16 columns instead of 8. Then you could do the math.
Since you seem to say you only compare 2 items at a time, this could be a viable solution.
September 21, 2011 at 12:30 pm
Starting to wonder if that really is the best way to go here. Going to have to think about that unless anyone else has a good idea.
Thanks!
September 21, 2011 at 5:07 pm
tschmitt300 (9/21/2011)
Starting to wonder if that really is the best way to go here. Going to have to think about that unless anyone else has a good idea.Thanks!
I never said I liked the idea. I said it could be a viable solution ASSUMING the comparaison always stays at 2 objects. This is a big assumption.
Like I said my favorite would have been to save the output to temp table, calculate the previous year in there for year 2+ and then just use that new column.
The overhead on the server should be minimal and the coding would then work however many years you have in the final report.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply