Selected Week - 1

Question

Selected Week - 1

erdem1973

SSCommitted

Points: 1843
More actions
September 16, 2010 at 8:20 am

#226367

Hello Experts,
I want to create a SSRS reports that displays unit sales of styles. Currently, I can retrieved this resultset based on a WeekOfYear report parameter.
But, how can I also display the previous weeks' sales data. I mean , If I pick the value 33 as a current week,then I would also like to list week 32 and week 31 sales data.
I have attached my MDX query string as follows:
SELECT NON EMPTY { [Measures].[Sale Unit] } 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
SELECT NON EMPTY { [Measures].[Sale Unit] } 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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 1

My MDX is very rusty, I don't think I can write the correct code without experimenting with the source but I would consider adding something like

ParallelPeriod(Week,1,[DimDate].[Calendar].CurrentMember)

Good luck.

erdem1973 SSCommitted Points: 1843 More actions · Answer 2

Thank you for your reply.

Sorry,I am a little bit confused.

Where do I put this code?

erdem1973 SSCommitted Points: 1843 More actions · Answer 3

Thank you again. I have solved the problem. Your vision has opened the door!

I have added a member to my MDX query. I think it will be very useful for similar cases. And I hope other developers can use this code

Here is the code

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

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 4

Good one. I didn't even think of using a member. Like I said my MDX is pretty rusty. But I think you nailed it, a member with the parallel period is the perfect way to go. Thanks for posting your code back.