Solved

SSRS - Previous Week's Sales Data

Posted on 2010-09-16
3
897 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Suggested Solutions

Title # Comments Views Activity
Data source in SSRS 2 60
SSRS Problems 9 101
Subreport Parameter using default not what is passed. 4 31
How to instal SSRS in visual studio 2010 2 18
Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…

740 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