?
Solved

SSRS - Previous Week's Sales Data

Posted on 2010-09-16
3
Medium Priority
?
920 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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Time Corrections for Reports Working with a report, we made some interesting discoveries about the time corrections/updates We are using the following Parameters: Starting Entered Date (Date) formatted as Data type: "Date/Time" Ending Entered …
Have you ever had to extract data from a Microsoft SQL Server database and export it to an Excel file, but did not want to use a DTS package? The concept in this article is not new, but it is the answer and will also work on 64-bit SQL boxes.   …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses
Course of the Month12 days, 20 hours left to enroll

578 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