Set Font for Date Prompts using JavaScript

Have you ever wondered how to set the font for a date prompt. Date prompts provided by Cognos do not have a Font property. Whe you design report prompt pages and set the font for all other objects and if you have date prompts for which Font is not set, it sticks out in your prompt page (if you know what I mean).

So here is a small script to help set the Font.

<script>
var fW = (typeof getFormWarpRequest == "function" ?getFormWarpRequest() : document.forms["formWarpRequest"]);
if ( !fW || fW == undefined)
{ fW = ( formWarpRequest_THIS_ ?formWarpRequest_THIS_ : formWarpRequest_NS_ );
}
var FromDateTextBox = fW.txtDateFromDate;
FromDateTextBox.style.fontFamily = 'Arial';
FromDateTextBox.style.fontSize = '8pt';
</script>

where the date prompt is named as FromDate.

Note: This technique uses JavaScript against underlying report objects in a IBM Cognos 8 BI report. For this reason, there is no guarantee that reports created using this technique will migrate or upgrade successfully to future versions without requiring modifications. Any such modifications are the responsibility of the report designer.

JavaScript for Date Validation

I keep receiving a lot of requests for Date Validation Javascript, thought I would share the same. Though this information is available on millions of sites, thought this could be of some use to all those who use this blog often.

Step 1: Drag 2 Date Prompts into the prompt page

Step 2: Name the Date Prompts as FromDate and ToDate

Step 3: Drag an HTML Item below the prompts and include the below script:

<script type="text/javascript">
function validateDate()
{
var fW = (typeof getFormWarpRequest == "function" ?getFormWarpRequest() : document.forms["formWarpRequest"]);
if ( !fW || fW == undefined)
{ fW = ( formWarpRequest_THIS_ ?formWarpRequest_THIS_ : formWarpRequest_NS_ );
}

var FromDate = fW.txtDateFromDate.value;
var ToDate = fW.txtDateToDate.value;

if(FromDate > ToDate)
{alert('From Date is greater than To Date');}
else
{promptAction('finish');}
}
</script>

Step 4: Drag another HTML Item to create the prompt button. The onclick event of the button will trigger the date validation function.

<input type=button onclick='validateDate()' value='Finish'>

Step 5: Drag a Prompt Button - Finish to enable the submission of the prompt page and hide the button by seting the height and width to 0px.

Note: This technique uses JavaScript against underlying report objects in a IBM Cognos 8 BI report. For this reason, there is no guarantee that reports created using this technique will migrate or upgrade successfully to future versions without requiring modifications. Any such modifications are the responsibility of the report designer.

Font impact on Nesting members in Charts

Nesting Members inside of an axis in Cognos results in Font issues for that axis. Font no loger gets applied to this axis even if you explicitly set that.

Cognos Deployment Across Environments - Part 1

Today I was guiding a colleague on Movement of Cognos reports/ packages across environments and thought I would share the same on my blog for the benefit of first time users of Cognos. Though the same is shared in Cognos documents but felt I would simplify it and just provide the basics on it. Let me know if this helps anyone:

To move reports and packages across any environment there are 3 simple steps that need to be followed : create an archive of the object by exporting it, copy and paste this archive from one environment to another and import the archive. Consider an archive as a zip file that is in Cognos format. So essentially we are zipping the contents and moving it across environment.

The most basic steps and properties that need to be adhered to for creating an archive / export is detailed below:

Step 1: Navigate to Content Administration in your Cognos Connection. In 8.4 this is available under Launch > IBM Cognos Administration > Configuration

Step 2: Click on New Export

Step 3: Provide a name for the export. Note this is not the archive name. Consider this like a workflow name to help you re-execute the workflow of archiving Cognos objects. Click Next.

Step 4: The next page asks you for a deployment method, options being - Select public folders and directory content, Select the entire Content Store. Choose the 1st option unless you want to move all the Cognos Connection objects across environments. Click Next.

Step 5: In the next page that opens select the objects (Report / Package / Folders ) you want to deploy. Remember when you export packages that contains reports under the package object even those reports would get deployed.

Step 6: Remember to check the Disable after import option for the objects you want to export unless you want them disabled in the new environment after deployment.

Step 7: In case you want to include report output versions, run history, schedules; then select the corresponding check boxes. Click Next.

Step 8: If you are deploying objects to a new environment that doesn't have Cognos groups and roles, Distribution lists, Contacts, Data Sources and Connections set up then select the corresponding check boxes. Click Next.

Step 9: If you want to carry forward any permissions that you have set for the objects you are exporting, select the checkbox against the "Include access permissions" property. You can apply permissions to any new objects that you are exporting to the new environment or you can also apply permissions to new objects and existing objects that you are exporting to over-write in your new environment.

Step 10: If you wish to include references to external namespaces, select this option.

Step 11: Next property that needs to be set is something that Cognos administrators would insist on. This is ownership property. When you export objects you can either set the owner on the new environment to the owner from the source or to the user performing the import. Usually this is set to the user performing the import. The reason behind this being only administrators usually perform the import and they need to be set as owners to prevent the original owners from modifying objects in the new environment. Click Next

Step 12: Provide the archive name in the next page.

Step 13: You an encrypt the archive and provide a password for the encrypted archive as well. Click Next.

Step 14: Review the options selected in the next page and click Next.

Step 15: In the Next page, you an choose to save and run this export once or save and schedule this export or just save this export to be run at a later time. Click Finish.

Step 15: If you choose to Save and Run once, in the next page that opens you can choose to either run it immediately or defer the run to a future date. Click OK to create the archive.

Step 16: Once the export is completed you can view the details of the same by clicking on More Actions against your export name in the Content Administration list. Click on View history to view the details of the export run.

Step 17: The archive is always created on the server machine. To copy and paste the archive to a new environment, locate the archive in the deployment folder path on your server machine. By default the deployment folder is located in /deployment

ex: C:\Program Files\cognos\c8\deployment

Step 18: Copy the required archive and paste it into the deployment folder of your new environment.

This completes the export and movement of the Cognos objects. The steps for import are fairly simple and would be covered in the next post.

Default Date Prompts to Current Date and Current Date - 30 days

Step 1: Name the Date prompts as FromDate and ToDate.

Step 2: Drag an HTML Item into the report just below the Value Prompt and insert the below script into the same:

<script type="text/javascript">
function DefaultDateSelection()
{
var fW = (typeof getFormWarpRequest == "function" ?getFormWarpRequest() : document.forms["formWarpRequest"]);
if ( !fW || fW == undefined)
{ fW = ( formWarpRequest_THIS_ ?formWarpRequest_THIS_ : formWarpRequest_NS_ );
}

var months = new Array('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');

var ToTime=new Date();
var ToMonth=months[ToTime.getMonth()];
var ToDate= ToTime.getDate();
var ToYear=ToTime.getYear();
var ToDate = ToMonth + ' ' + ToDate + ', ' + ToYear;

var FromTime = ToTime;
FromTime.setDate(FromTime.getDate()-30);
var FromMonth=months[FromTime.getMonth()];
var FromDate= FromTime.getDate();
var FromYear=FromTime.getYear();
var FromDate = FromMonth + ' ' + FromDate + ', ' + FromYear;

pickerControlFromDate.setValue(FromDate);
pickerControlToDate.setValue(ToDate);
}
DefaultDateSelection();
</script>

Note: This technique uses JavaScript against underlying report objects in a IBM Cognos 8 BI report. For this reason, there is no guarantee that reports created using this technique will migrate or upgrade successfully to future versions without requiring modifications. Any such modifications are the responsibility of the report designer.

JavaScript to Deselect Value Prompt Box Selections

Step 1: Name the Value prompt as Owner.

Step 2: Drag a HTML Item into the report just below the Value Prompt and insert the below script into the same:

<script type="text/javascript">
function Refresh()
{
var fW = (typeof getFormWarpRequest == "function" ?getFormWarpRequest() : document.forms["formWarpRequest"]);
if ( !fW || fW == undefined)
{ fW = ( formWarpRequest_THIS_ ?formWarpRequest_THIS_ : formWarpRequest_NS_ );
}
var obj = fW._oLstChoicesOwner;
obj.selectedIndex = -1;
}
</script>
<input type='button' onclick='Refresh()' value='Refresh'>

Note: This technique uses JavaScript against underlying report objects in a IBM Cognos 8 BI report. For this reason, there is no guarantee that reports created using this technique will migrate or upgrade successfully to future versions without requiring modifications. Any such modifications are the responsibility of the report designer.

Impact of Summary Filters on Reports - Part 2

This is in continuation to the post on Impact of Summary Filters on Reports.

Scenario 8: No grouping in report, no scope in summary filter, Rank Data Item's Rollup is set to None
Result: Filtering effect

Scenario 9: Include Sales, Product in Query 1. In Query 2 reference Query 1's Product and Sales and create Rank Data Item in Query 2 and summary filter in Query 2. No scope for summary filter, no grouping in report.

Result: Filtering effect

If you notice the query, the difference between setting Rank Data Item's Rollup to Automatic and None is as shown below:


Automatic -
(rank() over ( at XSUM(Order_details8.Revenue ) order by XSUM(Order_details8.Revenue ) desc nulls last ) < 5)


None -
(rank() over ( at Order_header6.ORDER_NUMBER,Product7.PRODUCT_NAME order by XSUM(Order_details8.Revenue for Order_header6.ORDER_NUMBER,Product7.PRODUCT_NAME ) desc nulls last ) < 5)

Here's my understanding -

Any summary filter works on the principle of After Auto Aggregaion / Rollup Aggregation. Hence Rollup aggregation of Rank is taken into consideration as Rank is used in summary filter. By setting this rollup to None, we are specifiying that no rollup aggregations need to be applied to this data item as well as objects inside of this data item. Hence whatever Rank function was applied inside of the detail query is applied at the rollup level as well.

This is a litle confusing to explain. But hope atleast I was able to throw light on the fact that summary filters are similar to rollup calculations or have an impact on rollup calculations.

Value Prompts 1st Line of Display

Value prompts display the Data Item Names on which they are based as the 1st Line inside the Value prompt just above the -----.

When you set the Name and Label to what actually needs to be displayed the Value prompt may not still display the required value.

Set the parameter name to the desired label name and then create a dummy data item and drag the use value of the prompt into this and set the value prompt's use value to the newly created data item. The value prompt will then start displaying the parameter names.

Impact of Sumary Filters on reports - Part 1

Consider the following columns in your report: Product, Sales and Rank - Rank(Sales).

To display only top 5 products you create a summary filter where Rank < 5.

Scenario 1: No grouping in report, no scope in summary filter
Result: No filtering effect

Scenario 2: Product grouping in report, no scope in summary filter
Result: No filtering effect

Scenario 3: Rank grouping in report, no scope in summary filter
Result: Filtering effect

Scenario 4: Product grouping in report, Product scope in summary filter
Result: Filtering effect, validation warning

Scenario 5: Rank grouping in report, Rank scope in summary filter
Result: Filtering effect, validation warning

Scenario 6: Product Grouping in report, Rank expression changed to include for Product, no scope in summary filter
Result : Filtering Effect

Scenario 7: Product Grouping in report, Rank expression changed to include for Product, A summary calculation - max - maximum(Rank) included for Product Grouping level, summary filter changed to max < 5, set scope of this filter to Product then only those groups that have maximmum ranks of 5 are displayed. Not setting scope will result in no data.

A summary filter should hence be used only when groups need to be filtered out otherwise an after aggregation filter should be used.

Parameter Maps in 8.4

Parameter Maps can now be based on Query Subjects in 8.4.

You can now have a query subject that retrieves the Key and Value pair as columns and base the Key - Value pair of Parameter Map on this. So now we no longer need to hard-code Key-Value pair which was earlier making parameter maps a unusable feature of Cognos.

Lists and Crosstabs

Lists and Crosstabs work differently with DMR. My requirement is to allow the user to select any member from the Product Hierarchy and the report should display the members in the selected level along with the children of each such member and the corresponding revenue.

Basically if the user selects any Product Line then the report should display Product Line, Product Type and Revenue or if the user selects any Product Type then the report should display Product Type, Product and Revenue.

I have created 2 data items for the same -

Parent - members(level([great_outdoors_company].[Products].[Products] -> ?Parent?))
Child - descendants([Parent],1)

and included Revenue measure.

When I drag the same into the crosstab the results are perfect while a list report based on the same query results in no data.

Has anyone else come across this issue?

[Update] The same can be achieved in List by creating the expressions using a Calculated Member rather than a Query Calculation. This feature is available 8.2 onwards.

Use Singleton to display data when there is no data in Report Page Query

In a report that was migrated from Cognos 8.2 to Cognos 8.3, I noticed that when there was no data in the report page, there was no data getting displayed for other queries in my report page. I have 2 queries being used in the report page - one for the main list report and another to return the user name using a macro. Now when no data was returned by the main query my user name was also not getting displayed. The user name was being displayed by a list with no borders and formatted to look like a singleton object as this was developed using 8.2. Now to rectify this I used a singleton object instead of the list and it worked fine.
 

List vs Chart

I have just finished delivering a set of Cognos reports for UAT and am awaiting for the results ;-)..So in the meantime thought I would post about some issues that I faced while creating the reports. You are welcome to post your thoughts on the same..

Have you ever noticed that a list report and a chart report behave differently when it comes to querying. I found that out fairly recently. Let me give you an example of how differently the two work. Assume you are to display for each selected Month the Top Products by Sales along with the monthly % contribution towards the sales. Ideally you would drag Product, Month, Sales, Rank(Sales for Month), Total(Sales for Month) - This is the total sales for the top products, % Contribution being - Sales / Total (Sales for Month) - basically how much did this Product contribution towards the total sales. I have a rank filter - rank < = 5 with after auto aggregation property set.

Now when I drag Product, Month, and % Contribution into my Chart report and List report, I see that the values are different. The list report displays the expected value while the chart report doesn't. On closer look I notice that the chart report doesn't apply the rank prior to the calculation of Total (Sales for Month). This means that my chart is calculating the total sales across all products for the month rather than Total sales for the Top 5 products.

Now that is weird when the List and chart behave differently. To work around this, I had to create a subquery which gets me the Product, Month, Sales and Rank with filter Rank < = 5. On top of this query I have another query that does the % contribution calculation. Now the list and chart work perfectly.

In retrospection I think the chart was working as expected, the rank filter was getting applied after aggregation. It was the list that wasn't working fine even though it returned the data I required.

The point though that I would like to make here is that not always lists and charts work the same way or fire the same query.

Reader Tips:

shiv shankar
Using solve order would have solved the issue.