Reporting Services - Comparison Report

Posted on 2010-01-05
Last Modified: 2012-05-08
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:
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..
Question by:innocent1973
    LVL 26

    Expert Comment

    by:Chris Luttrell
    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.

    Author Comment

    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)
    LVL 26

    Expert Comment

    by:Chris Luttrell
    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.

    Author Comment

    I am trying to compare the max week and previous week in my dataset.
    I use SQL Server 2005 components
    LVL 26

    Accepted Solution

    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)) -

    Author Closing Comment

    What can I say??? PERFECTTTTT

    Author Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Have you ever had to extract data from a Microsoft SQL Server database and export it to an Excel file, but did not want to use a DTS package? The concept in this article is not new, but it is the answer and will also work on 64-bit SQL boxes.   …
    Introduction Earlier I wrote an article about the new lookup functions ( that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now