Posts Tagged ‘Cognos’

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

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.

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.

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
 
 

Cognos 10

October 23, 2010

Ready for Cognos 10?