Page 1 of 1
Using parameters in MDX (subset editor)
Posted: Tue Mar 04, 2014 9:51 pm
by Marioler
Hi Guys
I wonder if it is possible to use parameters (e.g. a value from a parameter cube) in a MDX statement in the subset editor's expression window (not in an active form in excel - I know how to do it there).
Example:
Children of element "123" in dimension "CostCenter".
MDX: {[CostCenter].[123].children}
Question: Is it possible to get "123" as a parameter from another cube?
If the MDX is used in an active form in an excel report it would be no problem to get the value "123" using a DBR function and then referring to that cell in the MDX. But I don't know how I can solve it in the subset editor's expression window directly.
I checked this document called "MDX primer" but could not find an answer to this question.
Thanks for your help.
Mario
Re: Using parameters in MDX (subset editor)
Posted: Tue Mar 04, 2014 10:17 pm
by EvgenyT
As far as Mdx primer goes: Using parameters in queries, page 26
In your example: something along this ways ...
{tm1filterbypattern ({tm1subsetall([cost centre])},[CubeName].(strToMember("[First Dim].[Element]"),[Second Dim].[Element] )) }
That will pull that cost centre from another cube...
see if you can work out how to use TM1SubsetToSet after you get that cost centre
Let me know how you go, will be happy to assist further
Thanks
Evgeny
Re: Using parameters in MDX (subset editor)
Posted: Tue Mar 04, 2014 10:37 pm
by tomok
Marioler wrote:I checked this document called "MDX primer" but could not find an answer to this question.
How about this section in the document
http://www.bihints.com/using_parameters_in_queries? Seems spot on in my opinion.
Re: Using parameters in MDX (subset editor)
Posted: Wed Mar 05, 2014 1:21 pm
by Marioler
Hi
Thank you very much for your help guys! It works now.
I finally found the last step in the section called "Using TM1 Subsets, TM1Member, TM1SubsetToSet and Item" in the MDX primer. with the following example:
{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Account] )}, 1)}, [Reconciliation].([Entity].[All Entities],TM1Member([Date].[Current Date].Item(0),0),[Reconciliation Measures].[Transaction Balance]) <> 0 )}"
That in combination with your answers made it possible for me to solve it.
Thanks again!
Re: Using parameters in MDX (subset editor)
Posted: Thu Feb 19, 2015 4:39 am
by jdeberardis
hi Guys,
Very sorry to necro an old thread but it is the closest one i've found that is close to my question.
I've been trying to wrap my head around it all day and cannot work it out.
Code: Select all
{
FILTER(
{TM1FILTERBYLEVEL({TM1SUBSETALL( [product] )}, 0 )},
[Merch3].(
[Version].[Actual]
, [Warehouse].[Group Total]
, [Year].[FY15]
, [Period].[Week 33]
, [Product Status].[All Status]
, [Merch3 Measures].[Closing Stock Quantity]
) <> 0
)
}
I would like to pull from the System Info cube both the Year and the Period/week.
Code: Select all
[System Info].([System Info].[YEAR], [String].[String])
I have been working through the mdx primer and I'm not sure if my syntax is correct or i'm simply just not getting it.
All the best,
James
Re: Using parameters in MDX (subset editor)
Posted: Thu Feb 19, 2015 6:42 am
by lotsaram
jdeberardis wrote:I would like to pull from the System Info cube both the Year and the Period/week.
Code: Select all
[System Info].([System Info].[YEAR], [String].[String])
I have been working through the mdx primer and I'm not sure if my syntax is correct or i'm simply just not getting it.
StrToMember is a tricky beast, especially when combining with a larger query. I have another way which I solve the same problem that is simpler. In the system info cube a whole host of time parameters are tracked like current & last year, current & last month, current & last week, etc. a nightly maintenance TI reads the system info cube and maintains single child reporting nodes for each of these relative time periods.
Regardless of whether a user in in the cube viewer or an excel report they can then just query "last week" for example and get the correct result. If you adopt this inyour time dimensions then the MDX becomes trivial as the relative time periods can just be literal strings hardcoded in the MDX as there's a member called "Current FY" and "Last Week".
Re: Using parameters in MDX (subset editor)
Posted: Fri Feb 20, 2015 3:33 am
by jdeberardis
Thanks for the reply lotsaram and apologies, I'm not sure what i was thinking yesterday but i only presented part of the problem. Please allow me to have another go
I am trying to build a dynamic subset over the product dimension, i've used MDX before to do this and can even do it without the record expression option. I've been racking my brain for sometime on how to retrieve a list of products where the closing stock quantity is not equal to zero. I can hard code both the Year eg FY15, and the period eg Week 33 into the MDX. This list will then be used in excel to retreive product codes and then do some excelly stuff to it. This particular subset will be used by the product team in their weekly reporting. The product dimension is part of a "Merch" cube that they use.
What i would like to do is to have this product list refresh based on the closing stock constraint for a particular year and week and have this done automatically hence the System Info cube which does exactly as you say. It updates based on a TI process on a nightly basis
- snapshot of system cube fields
- systemInfoCube.png (13.7 KiB) Viewed 8400 times
. I would like to reference the AGYEAR and AGWEEK in the MDX of this particular subset to make the list dynamic with little to no intervention from the end-user.
I just noticed that in my code example it looks like i have used "string" as a substitute for the actual dimension.member. Someone set this up before my time and I have not renamed them in a better way. Confusing as all hell
A quick thank you for also validating the use of a system cube for dates etc, i didn't know if this was the right approach or if there was a better alternative.
All the best,
James