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