SSRS - Previous Week's Sales Data

Hello experts,
I designed an SSRS report that shows weekly sales units of styles.
I want to add a column that show previous week's sales data.

My report parameter dimWeek shows the selected week's data

Any idea?
innocent1973Asked:
Who is Participating?
 
innocent1973Author Commented:
I solved the question!! Thanks
WITH 

MEMBER [Measures].[MyMeasure Previous Week] AS
	( PARALLELPERIOD([Dim Date].[Calendar].[Week Of Year],	1,[Dim Date].[Calendar].CurrentMember),[Measures].[Sale Unit]
	)
-- the end of the tuple 

SELECT
	{
	[Measures].[Sale Unit],
	[Measures].[MyMeasure Previous Week]
	}
on columns,
	
NON EMPTY { ([Dim Group MAX].[Group ID].[Group ID].ALLMEMBERS ) } 
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
FROM ( SELECT ( STRTOSET(@DimDateCalendar, CONSTRAINED) ) ON COLUMNS 
FROM [HCSALES_MAX]) 
WHERE ( IIF( STRTOSET(@DimDateCalendar, CONSTRAINED).Count = 1, STRTOSET(@DimDateCalendar, CONSTRAINED), [Dim Date].[Calendar].currentmember ) ) 
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Open in new window

0
 
sundara2010Commented:
R u pass the proper query on that  parameter. Please send me script
0
 
innocent1973Author Commented:
Thanks for your reply.
I have attached my query string. I want to add a column that shows CURRENTWEEK - 1 salesunits..

SELECT NON EMPTY { [Measures].[PS], [Measures].[Sale Unit], [Measures].[ITPP], [Measures].[RPP], [Measures].[Sales Value], [Measures].[SalesBGP], [Measures].[BGPPercentage], [Measures].[Return Of Sale], [Measures].[Return Of Sale Value] } ON COLUMNS, NON EMPTY { ([Dim Product MAX].[Hierarchy].[Style Code].ALLMEMBERS * [Dim Product MAX].[ERP Style Code].[ERP Style Code].ALLMEMBERS * [Dim Product MAX].[Original Style Code].[Original Style Code].ALLMEMBERS * [Dim Product MAX].[Season Desc].[Season Desc].ALLMEMBERS * [Dim Product MAX].[Group Desc].[Group Desc].ALLMEMBERS * [Dim Product MAX].[Department Desc].[Department Desc].ALLMEMBERS * [Dim Product MAX].[Class Desc].[Class Desc].ALLMEMBERS * [Dim Product MAX].[Color Name].[Color Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DimClassMAXClassID, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimDepartmentMAXDepartmentID, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimGroupMAXGroupID, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimSeasonMAXSeasonID, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@FromDimDateCalendar, CONSTRAINED) : null ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@FromDimDateYear, CONSTRAINED) : null ) ON COLUMNS FROM [HCSALES_MAX])))))) WHERE ( IIF( STRTOSET(@DimSeasonMAXSeasonID, CONSTRAINED).Count = 1, STRTOSET(@DimSeasonMAXSeasonID, CONSTRAINED), [Dim Season MAX].[Season ID].currentmember ), IIF( STRTOSET(@DimGroupMAXGroupID, CONSTRAINED).Count = 1, STRTOSET(@DimGroupMAXGroupID, CONSTRAINED), [Dim Group MAX].[Group ID].currentmember ), IIF( STRTOSET(@DimDepartmentMAXDepartmentID, CONSTRAINED).Count = 1, STRTOSET(@DimDepartmentMAXDepartmentID, CONSTRAINED), [Dim Department MAX].[Department ID].currentmember ), IIF( STRTOSET(@DimClassMAXClassID, CONSTRAINED).Count = 1, STRTOSET(@DimClassMAXClassID, CONSTRAINED), [Dim Class MAX].[Class ID].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.