Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SSRS - Previous Week's Sales Data

Posted on 2010-09-16
3
Medium Priority
?
910 Views
Last Modified: 2012-05-10
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?
0
Comment
Question by:innocent1973
  • 2
3 Comments
 

Expert Comment

by:sundara2010
ID: 33690004
R u pass the proper query on that  parameter. Please send me script
0
 

Author Comment

by:innocent1973
ID: 33690475
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
 

Accepted Solution

by:
innocent1973 earned 0 total points
ID: 33695962
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. Introduction: In a previous article (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Reporting-On-Data-From-Stored-Procedures-part-1.html) I announced that I would writ…
Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Loops Section Overview
Suggested Courses

963 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