MDX Help Same Time Period

This is tough finding MDX help. I have written an MDX query, and I am not an
expert but could use some help. It shouldn't be complicated, but I can't
figure it out. Please help. I have provided the code.

Issue: In my WHERE statement, I am filtering for 11 months, but the
parallelperiod is for the entire year. I need the parallelperiod to compare
the same period (11 Months). The Date Range can be hardcoded, as I can build
dynamic MDX in SSRS

Purpose: To be used by Reporting Services. Need to compare the same exact
time periods but for the previous year.


EXAMPLE MDX:

WITH MEMBER [Measures].[Margin] AS ([Measures].[Sale Amt - Shipped
Fact]-[Measures].[Cost Amt - Shipped Fact]), FORMAT_STRING = "Currency"
MEMBER [Measures].[LYMargin] As
(
PARALLELPERIOD(
[Shipped Time].[Year - Quarter - Month - Date - Date Time - Is
Holiday - Is Weekday].[Year]
, 1
, [Shipped Time].[Year - Quarter - Month - Date - Date Time - Is
Holiday - Is Weekday].CurrentMember)
,[Measures].[Margin]), FORMAT_STRING = "Currency"

MEMBER [Measures].[Cost] AS ([Measures].[Cost Amt - Shipped Fact]) ,
FORMAT_STRING = "Currency"
MEMBER [Measures].[LYCost] AS
(
PARALLELPERIOD(
[Shipped Time].[Year - Quarter - Month - Date - Date Time - Is
Holiday - Is Weekday].[Year]
, 1
, [Shipped Time].[Year - Quarter - Month - Date - Date Time - Is
Holiday - Is Weekday].CurrentMember)
,[Measures].[Cost]), FORMAT_STRING = "Currency"
MEMBER [Measures].[CostPct] AS (IIF([Measures].[LYCost] IS NULL OR
IsEmpty([Measures].[LYCost]) OR [Measures].[LYCost] = 0, NULL,
([Measures].[Cost]-[Measures].[LYCost])/[Measures].[LYCost])), FORMAT_STRING
= "Percent"
MEMBER [Measures].[CostPctTtl] AS
( [Measures].[Cost]/( [Measures].[Cost],
Axis(1)(0)(Axis(1)(0)).Dimension.CurrentMember.Parent) ), FORMAT_STRING =
"Percent"

SELECT
NON EMPTY {
[Measures].[Cost],
[Measures].[LYCost],
[Measures].[CostPct],
[Measures].[CostPctTtl],
[Measures].[Margin],
[Measures].[LYMargin]} ON COLUMNS,
NON EMPTY {
([Product Dimension].[Department - Class - Sub Class -
Category].[Department])
*

[Shipped Time].[Year].[Year].[Calendar 2008] } ON ROWS
FROM [JBI Warehouse MF]
WHERE ([Shipped Time].[Month].[Month].[January 2008]:
[Shipped Time].[Month].[Month].[November 2008])

Looks like the multi-select problem discussed in this blog entry:

http://sqlblog.com/blogs/mosha/archive/2005/11/18/writing-multiselect-fr
iendly-mdx-calculations.aspx
>>
Writing multiselect friendly MDX calculations

One very common problem with authoring MDX calculations historically has
been the behavior of these calculations when the user who browses the
cube selects multiple members in the slicer as opposed to single one.
...
>>


So, for your specific query which has multi-select for months, you might
try something like:

WITH MEMBER [Measures].[Margin] AS ([Measures].[Sale Amt - Shipped
Fact]-[Measures].[Cost Amt - Shipped Fact]), FORMAT_STRING = "Currency"
MEMBER [Measures].[LYMargin] As
Sum(existing [Shipped Time].[Month].[Month].Members,
(
PARALLELPERIOD(
[Shipped Time].[Year - Quarter - Month - Date - Date Time - Is
Holiday - Is Weekday].[Year]
, 1
, [Shipped Time].[Year - Quarter - Month - Date - Date Time - Is
Holiday - Is Weekday].CurrentMember)
,[Measures].[Margin])), FORMAT_STRING = "Currency"

MEMBER [Measures].[Cost] AS ([Measures].[Cost Amt - Shipped Fact]) ,
FORMAT_STRING = "Currency"
MEMBER [Measures].[LYCost] AS
Sum(existing [Shipped Time].[Month].[Month].Members,
(
PARALLELPERIOD(
[Shipped Time].[Year - Quarter - Month - Date - Date Time - Is
Holiday - Is Weekday].[Year]
, 1
, [Shipped Time].[Year - Quarter - Month - Date - Date Time - Is
Holiday - Is Weekday].CurrentMember)
,[Measures].[Cost])), FORMAT_STRING = "Currency"
...


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***