COGNOS: Add a Multimedia File to a Report

Place your Video ( .wmv or .avi  format)  in the following path

C:\Program Files\ibm\cognos\c10\webcontent\samples\images\

Note: You must have Windows Media Player installed on your computer.

1. In the Insertable Objects pane, on the Toolbox tab, drag the HTML Item object  to the report.
2. Select the HTML Item.
3. double-click the HTML Item 
4. In the HTML dialog box, type the following:
   <OBJECT classid="CLSID:6BF52A52-394A-11D3-B153-00C04F79FAA6"> <PARAM NAME="URL" VALUE="/c10/webcontent/samples/images/yourVideo.wmv"/> </OBJECT>

Run the report and click on Video and enjoy...

Cognos: Age calculation

We have two ways to calculate age in years and age in yy years mm months and dd days format


Calculate Age in years...

Create a query Calculation as below...

_years_between (current_date, [Birth date column])

Calculate Age in yy years, mm months and dd days format...
(replace [Birth Date] with your own Data of birth column)


substring (cast(_ymdint_between  (current_date, [Birth date]),char(6)),1,2) || ' Years '||

substring (cast(_ymdint_between  (current_date, [Birth date]),char(6)),3,2)

|| ' months ' ||

substring (cast(_ymdint_between  (current_date, [Birth date]),char(6)),5,2)

|| ' Days '

cognos : 100% stacked Bar chart displaying percentages in bars using Java Script

We can't display percentages in middle of the bars in cognos 10.2, 100% stacked bar chart. But the same has been fixed in Cognos 10.2.1.

We can do the same thing using Java Script. just follow the below steps..

Suppose we have the following columns in List Report..

Region, Actual, 100% stacked bar chart with percentages, Budget as follows..

1) take the columns Region, Actual, and budget into List Report

2) goto Query Explorer

3) Open Query1

4) Add Data Item, enter the following expression to calculate Actual percentage over total of actual and budget

                [actual]/([actual]+[Budget])* 100

5) Name this data Item in properties as  "actp'

6) Add another Data Item to calculate Budget percentage and enter the below expression

                  [Budget]/([actual]+[Budget])* 100

7) Name this data Item in properties as  "budp'

8) Goto Page explorer and select Page1 to see the list report

9) Drag one HTML item and drop between the columns Actual and Budget as a column

10) Click LOCK icon in tool bar  to unlock

11) Add 4 more HTML items next to previously added HTML item, so you should have 5 HTML items

12) double click First HTML item and add the following script

     <script>

           var bar1Percent =

13) Click OK to close the HTML box

14) Just click to second HTML item to select (dont  Open)

15) Goto properties, got to the property "Source Type"

16) Change to "Data Item Value" 

17) For the Property Data Item Value, select the column  "actp"

18) Double click 3rd HTML item and enter the following expression      (dont forget semicolon as first line)

          

                 ;     

                 var bar2Percent = 

       

            

 19) Select 4th HTML item, goto properties and change the following propertiesr

                  Source Type: Data Item Value

                  Data Item Value: budp  (this is data item that we have created in 6 the step)

20) double click 5th HTML item and enter the following script as it is (dont forget about semicolon in first line)

;

var bar1=bar1Percent;

var bar2=bar2Percent;

var barL=bar1Percent+bar2Percent;

var width_driver = 200;

var barheight = ((width_driver) / 10);

            

           

var myTotalBar = '<div style="background-color:red;width:' + barL + ';height:' + barheight + '">';

            

var percent1 = '<div style="background-color:#F15A29; font-family:Source Sans Pro; font-size:9px; color:white; float:left; text-align:center; display:table-cell; vertical-align: middle;  width:' + bar1Percent + ';height:' + barheight + '"> <span style="display:table-cell; width:inherit;height:inherit; vertical-align: middle"> <strong>' + bar1.toFixed(1) + '%</strong></span></div>';

var percent11 = '<div style="background-color:#F15A29; font-family:Source Sans Pro; font-size:9px; color:white; float:left; text-align:center; display:table-cell; vertical-align: middle;  width:' + bar1Percent + ';height:' + barheight + '"> <span style="display:table-cell; width:inherit;height:inherit; vertical-align: middle"> </span></div>';

            

var percent2 = '<div style="background-color:#2B3990; font-family:Source Sans Pro; font-size:9px;color:white; float:right;text-align:center; width:' + bar2Percent + ';height:' + barheight + '"> <span style="display:table-cell; height:inherit; width:inherit; vertical-align: middle"> <strong>' + bar2.toFixed(1) + '%</strong></span></div>';

var percent22 = '<div style="background-color:#2B3990; font-family:Source Sans Pro; font-size:9px;color:white; float:right;text-align:center; width:' + bar2Percent + ';height:' + barheight + '"> <span style="display:table-cell; height:inherit; width:inherit; vertical-align: middle"> </span></div>';

if (bar1Percent<20)

{

percent1=percent11;

}

if (bar2Percent<20)

{

percent1=percent22;

}

myTotalBar = myTotalBar + percent1 + percent2 + '</div>';

document.write(myTotalBar);

</script>

You will get the output as below...

Note: This chart does not show percentage on bar if percentage < 20 as the width bar is very low so we can't show text on it.

Please note that, we can do the same thing using Micro Chart, but we will get extra space around the bar therefore, the lines appear with unnecessary space that we can't avoid

filter a report based on comma separated values entered in a text box prompt

Create a text box prompt with parameter like 'p'

enter the filter expression in detail filter as follows: 

cast(column name,varchar(200)) in ( #csv ( split(' ', split(',', split(' ,', split(' , ', split(', ', prompt('p','token') ) ) ) ) ) ) # )

Cognos insight free download

Now you can download IBM Cognos insight 10.2 personal edition free from the following link

https://www.analyticszone.com/

1) click download

2) select IBM cognos insight personal edition

3) register (free)

4) download

IBM Cognos Perfomance tips

IBM has uploaded several videos which walk you through some of the Tips and techniques to achieve Maximum performance

IBM Cognos BI - Designing Reports for Performance - Part 5
http://www.youtube.com/watch?v=c170hFlmdWA


IBM Cognos BI - Designing Reports for Performance - Part 1
http://www.youtube.com/watch?v=OiRvUrUTs-c

IBM Cognos BI - Designing Reports for Performance - Part 3
http://www.youtube.com/watch?v=zLS26-zKsCI


IBM Cognos BI - Designing Reports for Performance - Part 2
http://www.youtube.com/watch?v=QiIIPlaCMOU


IBM Cognos BI - Designing Reports for Performance - Part 4
http://www.youtube.com/watch?v=XbSbXoxxzeA

Cognos Schedule report will be not delivered issue?

Some times Cognos sccheduled Report output attachment  will not be delivered even the the output of the  report is around 1.5 MB.

Resolution:

Check your mail server for incoming/outgoing attachment size limit
(you can't do it, ask your admins)

How to burst a crosstab report

Resolving the problem

Add the burst key to a data container with one dimension, such as a list report. To burst a data container with multiple dimensions such as a chart or crosstab, create a master-detail relationship between this query and a single dimensional query, and then add the burst key to the single dimensional query.

Drop the crosstab within a list object that contains the burst key. Please see the attached Cognos 8.2 report spec going against the GO Sales and Retailers package.

1. Create a list report with the data item (Staff Name (Multiscript)) that you want to burst on as your first column. This will be the master list.
2. Insert your chart or crosstab as the second column of the list. This automatically creates a second query, which will be the detail query.
3. Add data items to your crosstab as needed. (Order Method as Rows, Order Year as Column, Quantity as Measure)
4. In the list, create a section with the item you want to group on. This is for formatting only.
5. Hide the list column title under the List property General -> Column Titles -> Hide.
6. In the Query Explorer, add the burst key data item that is in Query1 to Query2 (Staff Name and Email)
7. Select a crosstab cell and use the ancestor tool to move up to 'Crosstab' In the properties for the crosstab, edit the Master Detail Relationship property to link the burst key in both queries. (Staff Name)
8. Click on the row section of the crosstab (Order Method in the example provided). In the properties pane under Data, open the Properties dialog and click on the box for the burst key so it is checked.
9. Set Burst Options to burst on the list object's burst key.
10. Burst the report and view the results. 

Source: http://www-01.ibm.com/support/docview.wss?uid=swg21341874

Cognos Schedule Issue

Some times Cognos Scheduled jobs may not run even though we do not either disable it or modify it.

The reason is,, if password is expired of the user whoever scheduled the job, then Cognos schedules stop working, 

Resolution:

Very simple,  The person who have scheduled the jobs has to login into the cognos once with new password and run the schedule manually once from Cognos administration.