Prompt Set Manager – Saving and Re-using Prompts in Cognos BI

1. Prompt Set Manager – Saving and Re-using Prompts in Cognos BI Crop Production Services Loveland, CO May 2011

2. About Crop Production Services • Retail distributor of agricultural inputs • Chemical • Seed • Fertilizer • Services (blending, application) • Headquartered in Loveland, CO • Retail locations in USA, Canada, S. America, and Australia • Approximately $9 billion in annual revenue • Owned by Agrium, Calgary Canada (Symbol = AGU) • Cognos users since ReportNet 1.1 (2004)

3. CPS North America

4. CPS BI Design Principles Develop reports that are: • Highly flexible • Write 1, not 10 • Self-describing • Textual help on prompt page • Prompt values always output on report page • Should require little or no training • Graduated user interface • Simple navigation

5. Typical CPS Cognos Report

6. Prompt Menu (shows current selections)

7. Sample Prompts

8. More Sample Prompts

9. Column Projection Prompts

10. CPS Item Sales Report • Prompt Page contains 65 total prompts • 28 data filtering prompts • 37 data projection (column output) prompts • Allows user to build a highly summary or highly detailed report based on their selections • Leverages typical javascript techniques to hide and show prompts to reduce complexity for the user • Most heavily used report at CPS • But had one significant shortcoming….

11. Frustrated Users…. -----Original Message-----From: < A Typical User> Sent: 3/2/2009 10:21:32 AMTo: Wheat, David [mailto:david.wheat@cpsagu.com]; CC: <Pretty much everybody> Subject: Saved Parameters for Item Sales ReportsDavid, Is there any way to save parameters on the Cognos Item Sales report accessible from the Decision Support Portal like we can in Segmentation? Thanks, Dave

12. The PromptSet Manager Toolbar • Developed by BrightStar Partners for CPS in 2009 • www.brightstarpartners.com • Services and utilities that enhance Cognos modeling, development, and administration • Moderate COGNOise technical forum www.cognoise.com • Available for purchase (we think) from BSP • Requires: • A SQL Server DB to save prompt sets • Modifications to reports to include PromptSet Manager

13. Saving current prompt values

14. Reusing a saved prompt set

15. Manage Prompt Sets

16. Technical Requirements Five Key Components: • Prompt Set Manager Website/Virtual Directory • PSM must have its own Virtual Directory in a IIS website • CPS uses the same website as IBM Cognos • MS SQL Database • Handles Stored Procedures, Prompt Sets, Parameters, Reports and Users • Data Access layer • .aspx pages access and update the database • User Interface • What you have seen from previous slides • Client Scripts • JavaScript ‘method calls’ that access PSM scripts

17. Prompt Set Manager Website/Virtual Directory • Traditional IBM Cognos url • http://dcpcognos2:80/ibmcognos/cgi-bin/cognosisapi.dll?b_action=cognosViewer&ui.action=run&ui.object=%2fcontent%2fpackage%5b%40name%3d%27UAP%20DW%27%5d%2ffolder%5b%40name%3d%27Sales%20and%20Margin%27%5d%2ffolder%5b%40name%3d%27Pre%20Production%20Testing%27%5d%2freport%5b%40name%3d%27PSM%20Example%27%5d&ui.name=PSM%20Example&run.outputFormat=&run.prompt=true • PSM url with IBM Cognos search path • http://dcpcognos2/promptsetmanager/Default.aspx?username=gcadamc&format=PDF&formats=PDF&title=PSM Example&searchpath=/content/package[@name='UAP DW']/folder[@name='Sales and Margin']/folder[@name='Pre Production Testing']/report[@name='PSM Example'] • The IBM Cognos report Search Path is appended to the Prompt Set Manager Website URL

18. Database • MS SQL Server tables and Store Procedures • Dynamic table creation and minimal maintenance

19. Data Access Layer • Flow IBM Cognos report with a Set of Selected Prompts .aspx calls and POST MS SQL PSM DB

20. User Interface • Components • HTML file containing a toolbar and Iframe • The Iframe houses the actual report prompt page and strips the prompt values and display values from the page. • But how is this done?

21. Client Scripts • JavaScript Components in PSM • General Layout Requirements • Finish button • HTML Items • JAVASCRIPT • Surrounding ParameterDisplayValue DIV tags

22. General Layout Requirements • At CPS we have a lot going on with our prompt pages • However PSM only requires a few of the HTML Items seen above

23. General Layout Components

24. Finish Button • The Finish Button is actually an HTML item with a button tag. • onclick this button calls a function.

25. Finish Button Function • OnclickrunReport() function • Parent.Runreport(); • This method activates PSM, saves the prompts and runs the report. • promptButtonFinish(); • This method is an embedded IBM Cognos function that is the standard function that cognos uses when a regular button is clicked. It runs the report if PSM is not found.

26. Other Functions • runPageFunctions() • If PSM and a previously saved prompt set is found outside the Iframe, this function updates the parameters on the prompt page on page load

27. Surrounding ParameterDisplayValue DIV tags • To have the previously selected Prompt Set’s values appear, surround paramdisplayvalues in div tags with a distinct ID. • PSM recognizes the ID and displays the Prompt Sets on Saved Prompt Set load.

28. Questions?