Northwestern University Business Intelligence Solutions Cognos 8 Best Practices

1. Northwestern UniversityBusiness Intelligence SolutionsCognos 8 Best Practices Volume 3 Advanced Developer Techniques

2. Agenda • JavaScript Prompt Limiter • How to use JavaScript to limit the number of elements a user can select in a multi-select prompt. • Report Cache • Used to cache prompts for a report to optimize the prompt page response. • Burst Reports • Use distribution lists to distribute reports. • Join/Union/Intersect/Except/Master-Detail • How to join data from multiple queries.

3. Section 1 JavaScript Prompt Limiter

4. Report Prompt Behavior Users can overload the selection easily by using the Select all button.

5. Multi-Select Prompt Properties In the properties pane for a multi-select prompt, you can set the number of rows to be displayed per page in the list, but there is no option to limit how many selections a user can make.

6. Why Limit User Selection • Oracle only allows 1,000 items to be used in the IN statement of a SQL query. • Large amount of values in a prompt can cause the report performance issues. • Large values in a prompt can return a report too large to supply good Business Intelligence. • Allow the users access to choose from all the possible dimensional members, even if they can’t choose all of them at one time.

7. Add JavaScript to Prompt Page • In Cognos 8.4, IBM Cognos changed the names of items making older JavaScript not run correctly. • The new way to call items now should be future proof since the prompt is defined in a DIV tag and identified with the getElementByID function. • JavaScript should be used minimally in reports as a best practice, but sometimes the only way to force client behavior. • The cost/benefit balance in this case lies distinctly in the use of JavaScript.

8. How to Build Prompt Page </div> <script … <div id="selectValues"> Put three HTML items on the page, one before the prompt, one right after the prompt, and one to replace the Finish button. <div class="clsPromptComponent“ …

9. HTML Items 1 • The first HTML item starts the div tag for this prompt object. • This item should be the object just before the prompt object. • If the prompt object is in a table, this item should be in the same cell. • This will allow the JavaScript function to find the prompt object • <div id=“select Values”>

10. HTML Item 2 • This item starts with the ending Div tag to close the prompt object. • This item then starts the JavaScript and defines the function used to count the items in the prompt and either move forward or show an alert. • This function needs to be different depending on the type of prompt used. • It must be placed right after the prompt object to correctly wrap the prompt in the DIV tag. • </div><script language="javascript" type="text/javascript">…</script>

11. HTML Item 3 • This object replaces the Finish button. • It must create the Finish button. • On click, instead of running the PromptAction(‘finish’) it will call the Function we created in HTML Item 2 • <div class="clsPromptComponent" pt="btn"><button type="button" name="FinishButton" id="finishButton" class="bp" onmouseover="" onmouseout="" onclick="CountSelected()" style="">Finish </button></div><script>…</script>

12. Search and Select Prompt

13. Script for Search & Select • </div> • <script language="javascript" type="text/javascript"> • function CountSelected() • { var eleSearch1 = document.getElementById("selectValues"); • varselectObject = eleSearch1.getElementsByTagName("SELECT"); • var eleSearch1Details = null; • var Search1Len = 0; • varfoundSelections = false; • if (selectObject[1] && selectObject[1] != undefined) • { eleSearch1Details = selectObject[1].getElementsByTagName('OPTION'); • Search1Len = eleSearch1Details.length; • if (Search1Len < 11) • { foundSelections = true;} } • if (foundSelections == true) • { promptAction('finish'); } • else • {alert('The number of selected items is too high, it should be 10 or less, you currently have ' + Search1Len + ' items selected. Please remove some items then click on Finish again.'); } } • </script>

14. Picklist Prompt

15. Script for Picklist Prompt • </div> • <script language="javascript" type="text/javascript"> • function CountSelected() • { var Counter = 0; • vari; • var eleTg1 = document.getElementById("selectPicklist"); • varSelectObject = eleTg1.getElementsByTagName("select"); • if (SelectObject[0] && SelectObject[0] != undefined) • { for ( i = 0; i < SelectObject[0].options.length; i++) • { if (SelectObject[0].options[i].selected == true) • { Counter++; } } • if (Counter < 11) • { promptAction('finish'); } • else • { alert('The number of selected items is too high, it should be 10 or less, you currently have ' + Counter + ' items selected. Please remove some items then click on Finish again.');} } } • </script>

16. Checkbox Prompt

17. Script for Checkbox Prompt • </div> • <script language="javascript" type="text/javascript"> • function CountSelected() • { var Counter = 0; • vari; • var eleTg1 = document.getElementById("selectPicklist"); • varSelectObject = eleTg1.getElementsByTagName("input"); • for ( i = 0; i < SelectObject.length; i++) • { var node = SelectObject[i]; • if (node.checked) • { Counter++; } } • if (Counter < 11) • { promptAction('finish'); } • else • { alert('The number of selected items is too high, it should be 10 or less, you currently have ' + Counter + ' items selected. Please remove some items then click on Finish again.');} } • </script>

18. Multiple Prompts Here is a case where you might have two different multiple selection prompts and you want each one limited, possibly even with a different limit value.

19. <script language="javascript"> function CountSelected() {varnumOfPrompts = 2; var limitForPrompt1 = 10; var limitForPrompt2 = 10; varisPromptsGood = 0; varcounterCheckbox = CountCheckbox(); varcounterPicklist = CountPicklist(); if (counterCheckbox <= limitForPrompt1) //Check first prompt {isPromptsGood += 1;} else {alert('The number of selected items is too high in the checkbox, it should be ' + limitForPrompt1 + ' or less, you currently have ' + counterCheckbox + ' items selected. Please remove ' + (counterCheckbox - limitForPrompt1) + ' items then click on Finish again.');} if (counterPicklist <= limitForPrompt2) //Check second prompt {isPromptsGood += 1;} Else {alert('The number of selected items is too high in the picklist, it should be ' + limitForPrompt2 + ' or less, you currently have ' + counterPicklist + ' items selected. Please remove ' + (counterPicklist - limitForPrompt2) + ' items then click on Finish again.');} if (isPromptsGood == 2) //Check if all prompts are good {promptAction('finish');} Else {alert('Fix the prompts then click on Finish again.');} } </script> Scripts for Multiple Prompts </div> <script language="javascript" type="text/javascript"> function CountCheckbox() {var Counter = 0; vari; var eleTg1 = document.getElementById("selectPicklist"); varSelectObject = eleTg1.getElementsByTagName("input"); for ( i = 0; i < SelectObject.length; i++) { var node = SelectObject[i]; if (node.checked) {Counter++;} } return Counter;} </script> <div id="selectPicklist"> </div> <script language="javascript" type="text/javascript"> function CountPicklist() {var Counter2 = 0; var i2; var eleTg2 = document.getElementById("selectPicklist2"); var SelectObject2 = eleTg2.getElementsByTagName("select"); if (SelectObject2[0] && SelectObject2[0] != undefined) {for ( i2 = 0; i2 < SelectObject2[0].options.length; i2++) {if (SelectObject2[0].options[i2].selected == true) {Counter2++;} } } return Counter2;} </script> <div id="selectPicklist2"> <div class="clsPromptComponent" pt="btn"><button type="button" name="FinishButton" id="finishButton" class="bp" onmouseover="" onmouseout="" onclick="CountSelected()" style="">Finish </button></div> <script language="javascript" type="text/javascript"> varpromptButtonFinish = new CPromptButton (document.getElementById("finishButton"), PROMPTBUTTON_FINISH, true, "NS"); oCV_NS_.pageNavigationObserverArray = oCV_NS_.pageNavigationObserverArray.concat("promptButtonFinish"); oCV_NS_.notify(); </script>

20. Multiple Prompts • This process can also be used to check multiple prompts. • Create the DIV tags and functions around each prompt item as before, however don’t use the PromptAction(‘finish’) inside the individual functions, instead return a result of True/False. • Create another HTML Item with a function that calls the individual functions and determines if their results are all True, if so, it runs the PromptAction(‘finish’). • Have the Finish button call this function.

21. Code Examples Folder A new folder has been created on Dev to house Code Examples created by the BI team. These objects can be opened in Report Studio to see and copy/paste the javascript to reuse in other prompt reports.

22. Section 2 Report Cache

23. Report Prompt Performance A user clicks on run and gets the hourglass waiting for the prompt page to retrieve its data and render.

24. Set the Report Cache Click on the Set Properties for the report.

25. Set the Report Cache cont. On the Report tab, click on the Advanced options to expose the Report Cache properties. Click on the Refresh the Report Cache property. Then set the time to hold the cache before it is refreshed. We recommend it should be refreshed at least as often as the dimensional data the prompt uses.

26. Section 3 Burst Reports

27. Why Burst Reports • Burst Reports Run Once Distribute Many • Burst Reports are subset of data in the report based on grouping • Each grouping can go to a unique recipient • Recipients can be email addresses, Cognos Contacts, Cognos Groups, or Cognos Distribution Lists • Recipient/Grouping relationship can be defined in a table stored with the data and used for all burst reports

28. Examples of Report This grouping report emailed to aorozco@mycompany.com This grouping report emailed to atorta@mycompany.com This report is grouped by the Sales Rep and then sent out to each sales rep with only their data.

29. Create Grouping and Burst Data Items This report has two important columns to allow bursting, the Staff name data item is the grouping column, and the UserID column actually defines the recipients for the grouping.

30. Set Burst Options Go to File/Burst Options to define how to burst the report.

31. Set Burst Options Cont. Set the data item to define the recipient of the burst group, again, there must be a relationship in the report, or framework package, between the recipient and the grouped data item. Define the grouping data item to use for the burst separation, in this case the Staff name of the sales rep is used to create our report example. Set the type of bursting, in this case all of our recipients are email addresses so that is what we set.

32. Burst Recipients Mixed Type case lower(substring([gosales_goretailers].[Sales reps].[First name],1,1)+[gosales_goretailers].[Sales reps].[Last name]) when 'atorta' then 'galadin@linderoth.org' when 'awilcox' then 'CAMID(":Burst Test Group 1")' else 'galadin1@netscape.net' END

33. Run a Burst Report In the run options screen, click on the advanced options link.

34. Run a Burst Report Cont. Set the report to run in the background. If the burst report is to be emailed to its recipients, make sure to click on the Send the report by email. Set the Bursting option on for the report run.

35. Schedule the Burst Report The schedule options screen is basically the same, just make sure you click on the Override the default values to get to this screen to set the options.

36. Burst Output in Archive

37. Burst Table Example Add a table to your data source, it should include a Recipient value like what is listed below. Add the table to your Framework Package and create a join, in this case on the Sales Rep to the Staff Name table in the GO Sales package.

38. Section 4 Joins Unions Intersects Excepts Master-Detail

39. Multiple Queries • Reports often require multiple queries • Queries can be related through different types of operations • It is important to use the correct relationship to get the results required

40. Joins • Joins are the most basic type of relationship, they function the same as they would in a SQL query. • There are two basic types of joins that are defined by the cardinality • Inner joins • Outer joins

41. Unions • Unions are used to combine multiple queries into a single result-set. • There must be the same number of data items. • The data items must be compatible types.

42. Intersects • Intersects are used to combine multiple queries and return the matching set. • Same criteria as Unions.

43. Excepts • Excepts are used to return Q1 minus the matching records from Q2. • Same criteria as Unions.

44. Master Detail Relationships • A report that delivers information that would usually require two or more reports. • Often used to combine lists and charts. • Requires a parent container with a nested detail container • Parent frame for master query and nested frame for the detail • Associate Report page with master query and data container for detail query

45. Page Set Example In this example, a page set is created using Query 1 and paged by the Product name data item.

46. Page Layout This line holds the Query 1 elements of the Product image and Product name. A Chart object is added to the page, this is Query 2, after the data items are added to the query, the Master Detail relationship is defined so this chart only shows the Product name being displayed on the current page.

47. Report Output Example Product Image Product Name Sales by Retailer for Product Sales by Staff for Product