(MDX Question) How to use a Dimension's Current element as an Attribute name

[Gollapudi]

Hi,

Just to give a background we are using Cognos Express 10.2.2 and this question is relevant to MDX (Creating a dynamic subset using MDX Expression)

Following is my requirement...
I have a Dimension called "Vertical" which has got elements like HR, Sales, Finance etc...
And another Dimension called "Metrics" which has got metrics and Attributes like HR, Sales Finance etc.. these attributes works like Flags to identify if a particular Metric is applicable for respective Vertical.

Now I need to create a Dynamic subset based on Current Vertical filtered in a view using these Attributes, for which I need to use CurrentMember of Vertical as Attribute name in filter expression.

For e.g. If I have Sales Filtered in my View Context, I need to display only the Metrics applicable for Sales.

Let me know if any one has got an idea.

Thanks,
VG.

[Wim Gielis]

Hello there,

What client do you use to do this ?
If Excel or websheet, this does not seem difficult.
Use a cell with a SUBNM formula to pick a Vertical. Say cell B10.
Then the MDX query would be something like:
="{Filter( {TM1SubsetAll( [Metrics] )}, [Metrics].[" & B10 & "]=""X"")}"

Assuming an X as the value in the }ElementAttributes_Metrics cube.

[Gollapudi]

Hi Wim Gielis,

Many thanks for your reply, yes your right, it works fine in Excel (Active Form) like you said, I have done that in following way.

C12 = "{FILTER( {TM1DRILLDOWNMEMBER( { HIERARCHIZE( {TM1SUBSETALL( [Metrics_Sales] )} ) }, ALL, RECURSIVE )}, [Metrics_Sales].[" & C15 & "] = "&"""Y"""&")}"
TM1RPTROW($B$9,"CtM:Metrics_Sales","","","", 0, C12,"Caption_Default")
Where as, C15 is my Vertical SUBNM & C12 is MDX string

But the issue is, Number format is not working as expected in Active form, as you know I have metrics in Rows, few rows are like decimal and few are percentages, I have created an additional formatting row called N% and applied calculation in Column A to make it N% instead of N if Metric contains "%" (excel SEARCH function), it works as expected in Perspectives Active form, but not in TM1Web.

So I'm planning to create a View with dynamic subset that filters Metrics based on Vertical selected in Context dimension (using Flag attributes I have in my Metrics dimension), so that I can show the View in TM1web isntead of Active form, where I will not have any Number/Data formatting issues.

Thanks,
VG.

[babytiger]

But the issue is, Number format is not working as expected in Active form, as you know I have metrics in Rows, few rows are like decimal and few are percentages, I have created an additional formatting row called N% and applied calculation in Column A to make it N% instead of N if Metric contains "%" (excel SEARCH function), it works as expected in Perspectives Active form, but not in TM1Web.

Are you using any other excel functions for your formatting? Have you tried to unhide the formatting rows and columns to see what calculations (ones that drive your formatting) has came through.

[Gollapudi]

Only that SEARCH function like I mentioned before, I am decoding (as u can see below) "N" as "N%" at leaf level as I have got few of the metrics supposed to show in percentages.

=IF(TM1RPTELISCONSOLIDATED($C$28,$C28),IF(TM1RPTELLEV($C$28,$C28)<=3,TM1RPTELLEV($C$28,$C28),"D"),IF(OR(IFERROR(SEARCH("%",C28,1)>0,FALSE),IFERROR(SEARCH("%",C28,1)>0,FALSE)),"N%","N"))


And I have added a row for N% between [Begin Format Range] and [End Format Range] and applied Percentage format for data cells of this row.

[Begin Format Range]
0
1
2
3
D
N
N%
[End Format Range]

In Active form, every thing works as expected, but in TM1 Web it doesnt, thats the issue, let me know if you need more details.

Thanks,
VG.

[babytiger]

I don't think IFERROR is supported by tm1 web.

[BrianL]

According to http://www.ibm.com/support/knowledgecen ... ons_NE0019 the IFERROR function is not supported (at least as of 10.2.2.1). I see ISERROR is supported, so you might be able to code around this limitation.

[gtonkin]

IFERROR is definitely not supported on TM1 Web (10.2.2 FP4) - you will need to change your formulae to use IF and ISERROR - Had this issue myself recently.
See the Appendix A of TM1 Web User guide for supported functions.

edit - Snap BrianL

[Gollapudi]

Excellent Guys! Thanks aton... ISERROR worked.

This will solve the current issue, but I am still curious if I can do such an MDX filter in Dynamic Subset, so that I can reuse the same in multiple views, with current solution I have to create an active form for every situation.

Thanks,
VG.

[declanr]

I am still curious if I can do such an MDX filter in Dynamic Subset, so that I can reuse the same in multiple views, with current solution I have to create an active form for every situation.

You can achieve what you are looking for with MDX in a cubeviewer that will work in any activeform; the caveat is that the relevant dimensions must be in the relevant contextual areas - I am actually surprised it hadn't been mentioned already. Using ISERROR etc. is obviously an option as you have found but I try and shy away from intentionally using errors to achieve a result - there is no specific beneficial reason for that; just personal preference.

The following code will cause "Dim_Filtered" to bring back only elements where an attribute's contents is the same value as the content of an attribute in "Dim_Filter" (obviously you must maintain these attributes); there are similar ways to get around it without the attributes but the concept remains the same - the code below is quite a simple example

Code: Select all

{FILTER({[Dim_Filtered].[MDX_Level_0])},[Dim_Filtered].[Attribute_Name_Dim_Filtered]=[Dim_Filter].[Attribute_Name_Dim_Filter])}

So in a cubeviewer:
Dim_Filter (must be a title element)
Dim_Filtered (must be either column or row dimension)

In an Activeform:
Dim_Filter (must be a title element)
Dim_Filtered (must be a TM1RptRow formula)

I use this sort of concept regularly in cubeviewers so that when a user selects a new version for example; the other dimensions automatically update without them needing to go through the hassle of choosing each 1 by 1 until they find values... obviously you could use zero suppression but it some cases you want to show the zeroes.

Hopefully that will give you some ideas.