• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1056
  • Last Modified:

Reporting Services - Comparison Report

Hello Experts,
I am a newly beginner SSRS developer and want to create a comparison report.
My dataset contains the following columns:
ClassID --> Product class ID
Year --> Product Year
WeekOfYear --> indicates week of the retail transaction date
SatisMiktari --> SalesQuantity

My purpose is to create a report that contains the following 3 columns:
ClassID
SatisMiktari
WeekComparison (A calculated columns that compares previous week and the last week of the sum of SalesQuantity)

How can I do this?
Any help is appreciated..
Regards
0
innocent1973
Asked:
innocent1973
  • 4
  • 3
1 Solution
 
Chris LuttrellSenior Database ArchitectCommented:
What is your query supplying the data?  I think some of the logic should be in the query to not retrun unneeded data if you are only looking at the last two weeks data.  And can you show some sample data, I think I know what you are looking for, but an example speaks volumes.
0
 
innocent1973Author Commented:
Firstly, Thank you for your reply
Ok....let me summarize the case:
I have attached a screenshot. My purpose is variance between the selected "WeekOfYear" and the previous WeekOfYear value.
For example: the difference between the week 19 and 20 for the class AC is 0.
And the difference between the week 19 and 20 for the class CJis 50

I want to put these two values as columns (0 and 50)
weeklycomparison.bmp
0
 
Chris LuttrellSenior Database ArchitectCommented:
Is the week calendar based and can you use the normal datepart functions or have to do something else?  Also, not sure if the "last" week can be based off of today or if it is the last full week (so you are only comparing full weeks vs one day of sales this week against all of last weeks) or if it is just the max week in your dataset?
Sorry for so many questions, but whether you do it in the query or in the code logic in SSRS, you have to know the right way to group items.
Also, which version of SSRS are you running?  Could make a difference in how to structure the solution.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
innocent1973Author Commented:
Yes
I am trying to compare the max week and previous week in my dataset.
I use SQL Server 2005 components
0
 
Chris LuttrellSenior Database ArchitectCommented:
See if something like this works for you, I got the displayed output in the following test.

I created a query with an additional computed column to have the max week available because RS doesn't like nesting aggrigates.

select ClassID, Year, WeekOfYear, SatisMiktari, max(WeekOfYear) over () AS MaxWeek
from yourTable

then I did a simple table, grouped by ClassID, and used these two expressions to get the right values in the other 2 columns:
=Sum(IIF(Fields!WeekOfYear.Value=Fields!MaxWeek.Value,Fields!SatisMiktari.Value,0))
=Sum(IIF(Fields!WeekOfYear.Value=Fields!MaxWeek.Value,Fields!SatisMiktari.Value,0)) -
Sum(IIF(Fields!WeekOfYear.Value=Fields!MaxWeek.Value-1,Fields!SatisMiktari.Value,0))
SSRS2005WeekCompareReport.png
0
 
innocent1973Author Commented:
What can I say??? PERFECTTTTT
0
 
innocent1973Author Commented:
You retrieve this resultset from a db table. But my data source is an OLAP cube. You will understand my problem when you see the screenshot below. I could not create your query,yet
weeklycomparison2.bmp
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now