Posts Tagged ‘Cognos TM1’

Reporting from TM1 – Part6 : JavaScript

April 11, 2012

I prefer to convert one prompt value to another by modifying MUNs with Cognos macros such as prompt and promptmany.

But when these macros are not enough you may try to use JavaScript.

For example suppose you have a month prompt based on a TM1 dimension and YTD prompt. You simply want to select the same month value for month and YTD.

If for some reason it is not possible to convert one to another what you can do is to put your values in the same order into two Value prompts. Then hide one of them.

When the other one is selected hidden prompt will be populated with the item with the same order.  

Put an HTML item after the Back button and put this code:

<script>

function populatePrompts() {

var fW = (typeof getFormWarpRequest == “function” ? getFormWarpRequest() : document.forms);

 

if ( !fW || fW == undefined) { fW = ( formWarpRequest_THIS_ ? formWarpRequest_THIS_ : formWarpRequest_NS_ );}

var listPandLMonth = fW._oLstChoicesp_PandLMonth;

var listPandLYTDmonth = fW._oLstChoicesp_PandLYTDmonth;

 

                for (i = 0; i < listPandLMonth.length; i++) {

                                if (listPandLMonth.options[i].selected) {

                                                listPandLYTDmonth.options[i].selected=true;

                                                break;

                                }

                }

                                                               

                promptButtonFinish();

}

</script>

 

Delete the NEXT & FINISH buttons and put another HTML item with the code:

 

<input type=”BUTTON” class = “bp” onclick=”populatePrompts()” value=”Finish”>

 

Of course the code above is just an example to have the same order in two different prompts. You can modify this code for different selection as below:

 <script>

function populatePrompts() {

var fW = (typeof getFormWarpRequest == “function” ? getFormWarpRequest() : document.forms);

 

if ( !fW || fW == undefined) { fW = ( formWarpRequest_THIS_ ? formWarpRequest_THIS_ : formWarpRequest_NS_ );}

var listPandLMonth = fW._oLstChoicesp_PandLMonth;

var listPandLYTDmonth = fW._oLstChoicesp_PandLYTDmonth;

var listPandLYear = fW._oLstChoicesp_PandLYear;

var k =1;

                for (i = 0; i < listPandLMonth.length; i++) {

                                if (listPandLMonth.options[i].selected) {

                                                listPandLYTDmonth.options[i].selected=true;

                                                break;

                                }

                }

 

                for (i = 0; i < listPandLMonth.length; i++)

                {

                                if (listPandLMonth.options[i].selected)

                                {             

                                                if(i==2){k=2;}

                                                if(i>=3 && i<15){k=3;}

                                                if(i>=15 ){k=4;}

                                                listPandLYear.options[k].selected=true; break;

                                }

                }

 

                                                               

                promptButtonFinish();

}

</script>

 

This code enables to have different selections in the second prompt.

But keep in mind that these JavaScript codes are not supported by IBM.

Reporting from TM1 – Part5 : Promptmany Macro

March 29, 2012

But if you need a multi-select prompt; prompt macro command will not be enough for you. Then the command you will need is “promptmany”

 Here is an example how you can use it; suppose we again have two TM1 cubes with the Country dimension shared. If you want to create a multi-select prompt your macro will look like something like this:

 

set ( 

#join(‘,’,

substitute (  ‘Profit and Loss’, ‘KPI’,   split(‘;’,   promptmany(‘Country’, ‘MUN’)  )

)   )# 

)

 

Main difference from the previous sample is this time promptmany command returns with more than one item. So you need split and join commands to modify the dataset.

Reporting from TM1 – Part4 : Prompt Macro

March 28, 2012

Let’s go on with the TM1 reporting;

You will definitely need some knowledge about Cognos macros while working with TM1 cubes. A full list of Cognos macro commands can be found in the official documentation, so I just want to focus on practical examples here.

When you need to use more than one TM1 cubes in your report and you have a prompt in your report then you already have a problem: TM1 cubes structurally use shared (conformed) dimensions but that is absolutely meaningless for Cognos BI. For Cognos BI they are just different dimensions.  

So if you have a month prompt in your report you have the problem of filtering two different TM1 cubes with one prompt. At this point you may get some help from Cognos macros; instead of a traditional prompt-filter definition try to  use a prompt macro in your report:

# substitute (  ‘Profit and Loss’ ,’KPI’, prompt (‘Month’, ‘MUN’))     #

In this case we based our macro on the time dimension of ‘Profit and Loss’ cube by the prompt macro. And use it in the related queries. On the other hand we can modify the returning result –MUN- in order to use it in the KPI cube related queries. Substitute command simply substitutes  ‘Profit and Loss’  with the word ‘KPI’.

Reporting from TM1 – Part3 : ODBC

December 19, 2011

ODBC connections might be a trouble when you are dealing with both Cognos BI and TM1;

you will most probably need 32-bit version of the ODBC Administration Tool (odbcad32.exe) and the 64-bit version.

You will need 64-bit version for your 64-bit TM1 installation, which you can find under \Windows\System32 directory.

And you might need 32-bit version for your BI to integrate it with any 3rd party data sources (not for TM1, but TM1 will never be enough as a data source, so eventually you will need to use other sources with your TM1 cubes) which you can find under  \Windows\SysWow64 directory.

Reporting from TM1 – Part2 : Attributes

December 13, 2011

TM1 cubes need to be published to Cognos Connection (with Framework Manager) in order to develop Cognos BI reports from them. In the publish process you have the option to swap item names in each dimension with an attribute of the dimension (if there are any).  

This is generally a good option because in TM1 cube development process you use codes in the dimensions as the item names and use actual names as attributes, aliases.

Country dimension in the example below consists of country and region codes, but the country names are defined as attributes. So in the publish process in Framework Manager, country names should be swapped instead of codes.

Country names in BI

Country names in TM1

Reporting from TM1 – Part1 : Levels

December 9, 2011

TM1 –Cognos BI integration is getting more common in the market (or sometimes in the form of Cognos Express edition) so I will try to focus on reporting from TM1 for a while.

Let me start with some simple issues.

We use levels in multidimensional reporting, but when we publish TM1 cubes for reporting we will not be able to see levels in the dimensions. We need to specify levels and name them in TM1 before publishing them as a package.

For this in TM1 select View > Display Control Objects from the menu in order to access hidden objects and find 

}HierarchyProperties cube.

This cube gives you the opportunity to name your levels in all your dimension.

TM1 Groups

July 31, 2011

Just a small error message I’ve encountered when trying to add some groups to TM1 security:

“Error Occurred While Adding Group”

It seems it was due to a problem in one of the Cell Security cubes; in the rules there is still a reference to a group which was deleted a while ago. Deleting the related rule line solved the whole issue.

IBM Cognos TM1 Certification

February 17, 2011
I have just found some time to take the TM1 certification exam.
TM1 
To become a certified Cognos TM1 Developer you just need to take one exam, COG-310 :
 
http://www-03.ibm.com/certify/certs/47010601.shtml