Drill through customization

[ViRa]

Hi all,

I'm trying to customize the drill cube to meet client's requirement. The origination cube displays member names and their claim details as rows and columns respectively among other various contexts such as year, month, version etc. When any of the claim details cell is drilled, the drill cube opens showing detailed claims breakup along with other demographic details. (Cube view was used as the source to set up the drill cube).

So currently, if for the month of Feb, claim detail is drilled from the origination cube for a member, the drill cube view for the month of Feb opens up for all the members. However, I want to customize it in a way that the drill cube view shows only that member's details who has been selected from the origination cube.

I searched this forum and found http://www.tm1forum.com/viewtopic.php?f ... ube#p43578 helpful. However, I'm still not able to get the desired results as I'm stuck with issues.

In the Prolog tab of the Drill process, I wrote the following code to capture the claim detail (covered amt) for the member and assigned it to the drill cube -

Code: Select all

SubsetDestroy('Member Name', 'MCSubset');
SubsetCreate('Member Name', 'MCSubset');
SubsetElementInsert('Member Name', 'MCSubset', (NumberToString(CellGetN('Account Extracts Cube', 'Actual', '2014', 'Feb', 'All Accounts', 'All ICD Codes', 'All Types of Services', 'Mennen Coleman', 'Covered Amt'))), 1);

ViewSubsetAssign('Account Extracts Drill Cube', 'Leaf1', 'Member Name', 'MCSubset');

As seen in the above code, I created a subset and inserted the element which captures covered amount from the origination cube for that member. Then I assigned this subset to the drill cube view. When I run this process, I get error - 'Dimension Element Not Found'.

Could somebody please guide me as to whether the approach I used is correct? How to rectify the issue so that the element is added in to the dimension and can be passed on to the drill cube? Appreciate your time and help.

[Wim Gielis]

Did you already check what TM1 returns for the CellGetN?

For example:

Code: Select all

AsciiOutput( NumberToString(CellGetN('Account Extracts Cube', 'Actual', '2014', 'Feb', 'All Accounts', 'All ICD Codes', 'All Types of Services', 'Mennen Coleman', 'Covered Amt')) );

What do you get and does it exist in the dimension 'Member Name'?

Am I right when I say that you look up an amount into a dimension containing names? Seems strange.

As to the subset and the view: I would not destroy and create and assign the subset again.
Just do a SUBSETDELETEALLELEMENTS and a SUBSETELEMENTINSERT.

[declanr]

As per Wim's post I think what your code suggests you are trying to do seems a little strange. It would be useful if you could present a screenshot from the cube you wish to drill from with an example snapshot of the destination view that you would like it to build.

[ViRa]

Thanks Wim and Declanr for your replies.

Did you already check what TM1 returns for the CellGetN?

Wim, I had tried Asciioutput before as well and faced issues -

- When I try Asciioutput as suggested by you, it gives me error saying 'Unexpected Parenthesis'.

Unexpected Parenthesis Error

Unexpected Parenthesis.JPG (116.57 KiB) Viewed 10312 times

- If I remove NumberToString function, it throws error - 'Invalid String Expression'

Invalid String Expression

Invalid String Expression.JPG (121.92 KiB) Viewed 10312 times

Hence, I tried adding this value to a test cube using CellPutN and the actual Covered Amount for the member is added correctly as shown below.

CellPutN

CellPutN.JPG (165.24 KiB) Viewed 10312 times

What do you get and does it exist in the dimension 'Member Name'?

The dimension 'Member Name' just consists of member's name.

Am I right when I say that you look up an amount into a dimension containing names? Seems strange.

Against each member name, the dimension called 'Dollar Amount' shows the consolidated claims dollar amount. This is the origination cube. In the drill cube, the break-up of the claims are displayed.

As to the subset and the view: I would not destroy and create and assign the subset again.
Just do a SUBSETDELETEALLELEMENTS and a SUBSETELEMENTINSERT.

When I ran this process initially (before assigning the subset to a view) I was getting error 'Subset already exists'. Hence, I chose to destroy it everytime. I will use your suggestion do a SUBSETDELETEALLELEMENTS and a SUBSETELEMENTINSERT

[tomok]

Can you explain just what exactly you are trying to accomplish with this line of code?:

SubsetElementInsert('Member Name', 'MCSubset', (NumberToString(CellGetN('Account Extracts Cube', 'Actual', '2014', 'Feb', 'All Accounts', 'All ICD Codes', 'All Types of Services', 'Mennen Coleman', 'Covered Amt'))), 1);

Why would you look up the Covered Amt value in your cube and then try and use that with the SubsetElementInsert function? The result of your CellGetN function is not returning a member of the Member Name dimension, it is returning a dollar amount from your cube. Your code makes no sense.

Your drill thu TI process should already include a parameter that represents the Member Name element clicked on from the original cube. Use that parameter in your SubsetElementInsert function, not your CellGetN bastardization.

[Willi]

I've not enough time right now to go deep inside your issue but I can see that you have to Change the AsciiOutput-Command. The first Argument has to be the filename in what the Output should be written.

[Wim Gielis]

I've not enough time right now to go deep inside your issue but I can see that you have to Change the AsciiOutput-Command. The first Argument has to be the filename in what the Output should be written.

Yes sorry, I forgot the first argument of the AsciiOutput function: a filename and/or an extension and/or a directory.

Without extension, .cma will be assumed.
Without directory, TM1's data directory will be assumed.

[ViRa]

Decalnr, screenshot showing drill from origination cube to drill cube is as shown below -

Org cube to drill cube

Org Cube to Drill Cube.JPG (130.34 KiB) Viewed 10271 times

Tomok, i get my mistake. I was trying to pass an element from one dimension ($ amt) to another (member name); hence the element was not being added. As per your suggestion, I tried to pass the parameter value that represents the member name into the SubsetInsert function. So now for any member selected from the origination cube, the drill cube shows only the member that was passed through the subsetinsert function. How do I achieve the functionality of dynamically passing the selected member name to the drill cube so that only equivalent member's details are displayed?

Should I create a view for the members in the member dimension and pass on this view to the ViewSubsetAssign function?

Please guide.

[ViRa]

Could somebody please assist me...appreciate your time and help

[renken158]

Dear all,
Could anyone tell me how to locate the element of certain dimension, such as 'Mennen Coleman' ?

Thanks