Archive for the ‘Cognos BI’ Category

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.

Advertisements

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’.

Default Value in Date & Time Prompts

February 24, 2012

 To set the default value of a date prompt to yesterday’s date – or to any other date- you can use a javascript in the prompt page. IBM provides a small javascript code that you can use:
<script>
function subtractDay ()
{
var dtToday = new Date();
var dtYesterday = new Date( dtToday – 86400000 );

// NOTE 86400000 = 24 hours * 60 (minutes per hour) * 60 (seconds per minute) * 1000 milliseconds per second)
var strYesterday = [dtYesterday.getUTCFullYear(), dtYesterday.getMonth()+1, dtYesterday.getDate()].join(“-“);

return strYesterday;
}
function subtractTime ()
{
var Time = “08:00:00.000”;
return Time;
}
pickerControlPDateTime.setValue( subtractDay() );
timePickerPDateTime.setValue( subtractTime() );
</script>

You can also change the number of milliseconds and use the code for other relative dates. The problem with code is when you try to schedule the report with default value “yesterday” the code will not be executed. I will show you how to solve that issue in the next post.

Credentials

January 4, 2012

Just an annoying issue about the scheduler:

 If you change the password for the user you use to schedule reports you just need to renew your credentials in Cognos. Otherwise all scheduled reports will fail until you do it so.

 You need to go to My Preferences > Personal Tab and select “Renew the credentials”

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.

Excel Palette

December 6, 2011

In BI Report Development process often you are asked to copy/mimic old excel reports or use them as a base.

And sometimes it is “required” to use excel colours in charts.

You can have total control on your chart palettes in Cognos; just select your chart and select “Palette” from properties.

Then you need select excel palette to be used in your charts.

Cognos 10

October 23, 2010

Ready for Cognos 10?