Prmt in SSRS
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
May 21st, 2012 2:28am

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
Free Windows Admin Tool Kit Click here and download it now
May 21st, 2012 2:46am

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
May 21st, 2012 2:53am

ya i used sql only..how to use case statement give some idea
Free Windows Admin Tool Kit Click here and download it now
May 21st, 2012 2:54am

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
May 21st, 2012 3:05am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics