locked
Prmt in SSRS RRS feed

  • Question

  • hi

    i create report in SSRS.in that report i send prompt YEAR..in that report i am having 2 columns

    1st column value shows this Year value like - 2012

    2ns column value want to show previous year - 2011

    so i wrote expression like this means i cant get previous data values

    select * from <table_name> where year_number =@year_number

    so my report look like this

    Prompt -- 2011

    Name of the Product   this Year sale(2011)      Last year sale (2010)

    Tv                                     45260                        35860

    Camera                            176213                      147623

    if i change prompt like 2012 so this year sale (2012),last year sale (2011)

    How to do this in ssrs

    Monday, May 21, 2012 6:09 AM

Answers

  • With 
    FirstYearCTE As
    (
      Select productName, SUM(Sales) from table
      Where year = '@datePassedFromReport'
      Group by productName 
    ),
    LastYearCTE As
    (
      Select productName, SUM(Sales) from table
      Where year = DATEADD(year,-1,'@datePassedFromReport')   
      Group by productName 
    ),
    Select * from 
    FirstYearCTE, LastYearCTE 
    where FirstYearCTE.productName = LastYearCTE.productName

    Note: This query is not tested. take care of casting as and when required. There always is a more optimal solution.

    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    • Proposed as answer by Lola Wang Tuesday, May 22, 2012 1:57 AM
    • Marked as answer by Elvis Long Friday, June 1, 2012 2:11 AM
    Monday, May 21, 2012 6:46 AM

All replies

  • What kind of query is this MDX or a SQL?

    For SQL: You can use CASE statements or CTE's

    For MDX: ParallelPeriod()


    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Monday, May 21, 2012 6:34 AM
  • ya i used sql only..how to use case statement give some idea
    Monday, May 21, 2012 6:35 AM
  • With 
    FirstYearCTE As
    (
      Select productName, SUM(Sales) from table
      Where year = '@datePassedFromReport'
      Group by productName 
    ),
    LastYearCTE As
    (
      Select productName, SUM(Sales) from table
      Where year = DATEADD(year,-1,'@datePassedFromReport')   
      Group by productName 
    ),
    Select * from 
    FirstYearCTE, LastYearCTE 
    where FirstYearCTE.productName = LastYearCTE.productName

    Note: This query is not tested. take care of casting as and when required. There always is a more optimal solution.

    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    • Proposed as answer by Lola Wang Tuesday, May 22, 2012 1:57 AM
    • Marked as answer by Elvis Long Friday, June 1, 2012 2:11 AM
    Monday, May 21, 2012 6:46 AM