Parameter list with root 'All ...' member

Hi,
I am trying to create simple parameter list that include members of some
dimension with their root 'All ...' member.
I want to accomplish list like this
All Countries
France
Germany
USA
...
Simple MDX, but in Reporting services dataset I get
<null> 1000
France 100
Germany 100
USA 100
... ...
Both 'All Countries' and Country members are present but with <null> for
'All Countries'.
I try different MDX-es with same result.
How can I get such results?
Thanks.Hi,
I too have the same problem..I am using Analysis services dataset. I am
seeing the "ALL" parameter in the drop-down list, for countries..But,when I
click on "all" I don't see any result..the rest works fine..why would this
happen?..
Please help us.
Thanks,
Sankar
"Don" <Don@.discussions.microsoft.com> wrote in message
news:7AD87583-D1A6-4453-86E1-70D39F99BB45@.microsoft.com...
> Hi,
> I am trying to create simple parameter list that include members of some
> dimension with their root 'All ...' member.
> I want to accomplish list like this
> All Countries
> France
> Germany
> USA
> ...
> Simple MDX, but in Reporting services dataset I get
> <null> 1000
> France 100
> Germany 100
> USA 100
> ... ...
> Both 'All Countries' and Country members are present but with <null> for
> 'All Countries'.
> I try different MDX-es with same result.
> How can I get such results?
> Thanks.
>

parameter issue in with member

Not sure where the problem is The @.bucket-size is an integer value being entered by the user.

with

set [end] as strtomember("[REL TURN HRS].[TRN HRS].&[" + @.bucket_size + "]")

member measures.bucket1 as

iif( isempty(sum([REL TURN HRS].[TRN HRS].&[0] :

[end],[Measures].[FACT CUT RELEASE Count])),0,sum([REL TURN HRS].[TRN HRS].&[0]:

[end],[Measures].[FACT CUT RELEASE Count]))

SELECT NON EMPTY { measures.bucket1} ON COLUMNS,

NON EMPTY {[CUSTOMER JOB].[Cust-Title-Issue-Job].[JOB_NUMBER].ALLMEMBERS } ON ROWS

FROM [DW INSIGHT]

WHERE ([CUSTOMER JOB].[JOB_NUMBER].&[A403EZ0])

some indication of what the problem is might be usefull. Any error messages?|||

here is the error i get

Parser: The query contains the bucket_size parameter, which is not declared. (msmgdsrv)

|||Have you created the parameter throught the Report->Report Parameters window? Also your question states @.bucket-size and your query uses @.bucket_size, is this a typo?

param prob

hi ,
I have created 2 datasets. my first dataset quary parameter is BU. query is like following.

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS strtoset(@.BU)

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

my second dataset query is like following,

SET [FilteredBUList] AS strtoset(@.BU)

SET [customtimeset] AS.....

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
intersect( [FilteredBUList],...) on rows

FROM Profitability

i need to map previous query BU parameter to the second dataset parameter. pls tel m the proper way.
cant access report parameter in this way from different dataset.
WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS strtotuple(@.BU)

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[FilteredBUList] on rows

FROM Profitability

ParallelPeriod: Same Day Last Month in long months

I am using the following MDX query to return same day last month values:

WITH

MEMBER [Measures].[Same Day Last Month] AS

'(ParallelPeriod([Reporting Date].[Year - Quarter - Month - Date].[Month],1),[Measures].[Active Account Indicator])'

SELECT

{[Measures].[Active Account Indicator],[Measures].[Same Day Last Month]} ON COLUMNS,

NON EMPTY {[Reporting Date].[Date].Members} ON ROWS

FROM [Financial]

This works for most days. However, for the last day of the month this cannot work when the current month has more days than the previous month. For example, on March 29, 30 and 31, the query will return NULL for [Same Day Last Month], since February 29, 30, 31 is an invalid date.

I am sure others have run into the same problem. What is the best way to solve this?

I'm grateful for any insights.

Dan

This could perhaps be more efficiently handled in scoped MDX script; but a recursive approach like this should work (except in the first month):

WITH

MEMBER [Measures].[Same Day Last Month] AS

iif(ParallelPeriod([Reporting Date].[Year - Quarter - Month - Date].[Month]) is null,

([Reporting Date].[Year - Quarter - Month - Date].PrevMember,

[Measures].[Same Day Last Month]),

(ParallelPeriod([Reporting Date].[Year - Quarter - Month - Date].[Month]),

[Measures].[Active Account Indicator]))

|||

Thank you, Deepak. I appreciate your response.

I believe I would have to use nested recursive statements for March 31. One month ago would indicate February 31, previous day would be February 30, and so forth until a valid date is reached.

However, this lead me to another solution which worked and follows our business rules: The last day of any month always contains values. Therefore, I was able to solve my problem using the following approach, employing the IsLeaf(), and the ClosingPeriod() function:

WITH

MEMBER [Measures].[Same Day Last Month] AS

'IIf(

--valtest

IsLeaf(

ParallelPeriod(

[Reporting Date].[Year - Quarter - Month - Date].[Month],

1

)

,

--valtrue

(

ParallelPeriod(

[Reporting Date].[Year - Quarter - Month - Date].[Month],

1

),

[Measures].[Active Account Indicator]

)

,

--valfalse

(

ClosingPeriod(

[Reporting Date].[Year - Quarter - Month - Date].[Date],

ParallelPeriod(

[Reporting Date].[Year - Quarter - Month - Date].[Month],

1

[Reporting Date].[Year - Quarter - Month - Date].CurrentMember.Parent

)

),

[Measures].[Active Account Indicator]

)

) --End IIf

'

SELECT

{[Measures].[Active Account Indicator],[Measures].[Same Day Last Month]} ON COLUMNS,

NON EMPTY {[Reporting Date].[Date].Members} ON ROWS

FROM [Financial]

ParallelPeriod that never returns null

I'm sure this is easy, but I haven't been able to cook up the secret sauce: I need a function like ParallelPeriod that will always return a member (unless I'm asking for a member that predates the oldest member in the dimension, when null is perfectly fine).

For example, ParallelPeriod([Month],1,[2006].[December].[31]) returns null. Instead, I want to get back [2006].[November].[30] (the last day of the month).

What's the secret?

There are many ways to do it. One way is to have an attribute which keeps number of days needed to go back to get to the day in the previous month. Then you will use

Date.CurrentMember.Lag(Date.DaysToGoBack.MemberValue)

for December 30 it will be 30, and for December 31 it will be 31 - so both of them will land on November 30.

|||How 'bout a way that doesn't involve changing the definition of the cube?|||

Possible too, but it won't have as good performance as the solution I proposed first. Again, there are many ways, but they revolve around the following theme (note that the formula below is only valid for the Month level. I am not sure what you want to do on the other levels).

IIF(Date.CurrentMember IS Date.CurrentMember.Parent.LastChild, Date.CurrentMember.Parent.PrevMember.LastChild, ParallelPeriod(...))

|||

OK, I see the pattern. The solution you propose above won't quite work (think of March), but it's close enough to something that will work to see where to take it.

If I decide that I really need this function, I'll probably go with the first suggestion you made, since I can still change the cube design at this point, I was just looking for a wider variety of options.

Thanks for the suggestions!

ParallelPeriod Error

I keep getting an error "The ParallelPeriod function expects a member expression for the argument. A tuple set expression was used." Is there something that I can use instead of LastPeriods to get this to work?

Here is the trend expression for a KPI that I get the error for:

(KPIVALUE('ABI Aggregate Test')-(CoalesceEmpty(

Aggregate({[Entities].[SITE].&[1]}

* {[Entities].[EntityType].[Site]}

* {[TDVs].[Parameter - Material].&[Bitum_Coal_Usage_Site -]}

* {ParallelPeriod([Date].[Year - Quarter - Month - Date].[Date], 1,

LastPeriods(3, StrToMember('[TDV Result Date].[Year - Quarter - Month - Date].[Year].[Calendar ' + EssMdx.Ess.Epm.AnalysisServices.MdxFunctions.Timeframe.GetLastFullYear() + ']')))}

, [Measures].[Result]),0)))

Could you describe what you are trying to do? It looks like you are trying to go back 1 day from a set consisting of the last 3 years. If you are trying to compare the KPI value to the same day 3 years ago you could use the Lag() function instead of LastPeriods(), but I don't really think that is what you are trying to do.|||

Sorry, the above line {ParallelPeriod([Date].[Year - Quarter - Month - Date].[Date], 1, should be {ParallelPeriod([Date].[Year - Quarter - Month - Date].[Year], 1,

I am trying to get a trend expression based on 1 year back (or 2 or 3...). ParallelPeriod has been working fine except with date ranges. The KPI value expression will give me a value for the last 3 full years (2006, 2005, and 2004) and is the same expression without the ParallelPeriod function. If the trend is 1 year back then it should be 2005, 2004, and 2003 (or it may be 2003, 2002, 2001).

Thanks

|||

That makes a bit more sense. In this case you simply have the LastPeriods and ParallelPeriod function nested the wrong way around, changing it to the following should work. Basically you need to pass the result of your GetLastFullYear Function to ParallelPeriod() and then pass the member from that to LastPeriods.

eg

(KPIVALUE('ABI Aggregate Test')-(CoalesceEmpty(

Aggregate({[Entities].[SITE].&[1]}

* {[Entities].[EntityType].[Site]}

* {[TDVs].[Parameter - Material].&[Bitum_Coal_Usage_Site -]}

* {LastPeriods(3,
ParallelPeriod([Date].[Year - Quarter - Month - Date].[Date], 1,StrToMember('[TDV Result Date].[Year - Quarter - Month - Date].[Year].[Calendar ' + EssMdx.Ess.Epm.AnalysisServices.MdxFunctions.Timeframe.GetLastFullYear() + ']')))}

, [Measures].[Result]),0)))

|||Thanks.

ParallelPeriod Calculated Member - Don't want to tie to year

I currently have Calculated Member in a cube that shows ParallelPeriod using a hierachy based off the year. So you always get prior year amounts, even while drilling into the hierarchy. Here is this Calculated Member (nothing special). This takes the amount you are on and subtracts the previous years amount.

Code Snippet

[Measures].[Amount] - ([Measures].[Amount], ParallelPeriod([Date].[Year - Quarter - Month].[Year], 1, [Date].[Year - Quarter - Month].CurrentMember))

What I am looking to do is always show the prior period based on where you are in the hierarchy (and do it with one calculated member). So I want to take amount and subtract it by the previous period based on where you are in the hierachy (Q2 2007 - Q1 2007, and not Q2 2007 - Q2 2006). This would be an example.

Hierarchy is Year - Quarter - Month. When looking at Years, you see previous year....when you drill into Quarter, you see the previous Quarter of the same year (so you see Q2, 2007 compared to Q1, 2007 and not Q2, 2006). And so on.

Hope that makes sense. Any help is greatly appreciated.

Would PrevSibling be applicable here.

Steve

|||Try [Measures].[Amount] - ([Measures].[Amount], [Date].[Year - Quarter - Month].PrevMember)
|||

Jeffrey Wang wrote:

Try [Measures].[Amount] - ([Measures].[Amount], [Date].[Year - Quarter - Month].PrevMember)

This seems to work. I knew it was something straightforward. Thank you very much for your help!