Why do you want a new job?

Top Ten Reasons Why Large Companies Fail To Keep Their Best Talent

Is it one of these reasons? I've defiantly changed jobs because of some of these complaints. Developers in the BI area have somewhat of a green field of jobs to choose from. Companies need to recognize the retention risks around keeping their most talented employees.

The Downside of Stored Procedure Based Query Items

In Cognos Framework Manage one of the query item options is a stored procedure. This item seems promising, but I would suggest avoiding it. First of all, to setup a stored procedure based query item in framework manager:

1. Write your stored procedure. It can expect prompts.
2. Select that stored procedure in Framework Manager as a query item source. (I would suggest pulling this in at the database level, or at the business level if you want to make it clear that it's not a base table) It will be scanned and expected prompts will be presented, similar to a drill through definityion.
3. Enter a prompt macro in the 'Value' field. At the report level we can setup a prompt to populate this value. This procedure expects 1 prompt of 'dept'. We can define a parameter of dept in a report to pass this value.

Within the framework model, this will behave as an any other query item. It can be related to other items in the model.

The major downside to this is in the execution plans cognos gennerates around these stored procedures. In a normal query cognos' query engine will look at the selected objects and gennerate an optimal query based on the relationships defined in the framework. Ideally, all the query work can be gennerated in 1 query that can be sent to the database. In the case of stored procedures, since cognos doesn't know about the contents of the stored procedure, the sql in the procedure is not included in the optimization plan. Meaning local processing will be required to join the results of the stored procedure to any other object in the model. Be careful to only include absolutely necessary queries in stored procedures.

I would use stored procedures for any procedure needing to use several temporary tables or user defined functions. (Although, in the case of user defined functions, I'd rather registered them with the Cognos gateway so that they can be called directly from Cognos reports)

Display a # of rows per page based on output type

Most of these articles are about things that frustrated me for enough of my day to swear a bunch and then say 'hey that's annoying enough to make an article about'. Today's tip has the added bonus of being part of a report I didn't even want to build. My user wants a summary of dollars over a variable period of time with the ability to drill into the total and see the distinct set of users that gave those dollars. This is fine, but my dataset has some spots in it that it'd be pretty inefficient to drill through on. Though the standard deviation of donors/unit is pretty high, I'd rather work on a way to assure a horrid query is never passed to the database. (hard to read, large query, resources, developers headaches, etc. But hey data analysis for potential hazard queries and allowing dump reports are post to save for another time)

That being said, the user also wanted to potentially run this thing out to HTML or Excel. Well, if the data set is potentially large I want to show a good amount of it on the screen in html but not enough that rendering will take a significant amount of time. For this you can use the Rows Per Page property.

This is fine for HTML only. Based on this setting when you try to run to xls 2007 this rows per page option will create a separate workbook tab for each 150 rows. If you run to xls 2003 single sheet cognos will contain the output to one workbook but the headers and footers will be repeated each 150 rows. BUT, in xls 2007 if the default rows per page (20 unless your admin changes it) setting will cause output to be rendered on a single tab.

What do we do? I want 150 rows in html and 1 workbook tab in XLS. Well, it's Report Expressions to the rescue! There is a cognos report function ReportOutput() that will return a string populated with the output type at render time. The function definition (as of the 8.4.1 documentation) is defined as:

ReportOutput ()

Returns the name of the output format. Possible return values are: CSV, HTML, layoutDataXML, MHT, PDF, rawXML, singleXLS, spreadsheetML, XLS, XML, XLWA.

Which is somewhat obtuse, but here's a nice chart of the output formats that I'm interested in. One thing to note is that you need the right string for the version of xls you're expecting to use:

CSV	Comma Seperated Value
HTML	HTML output
layoutDataXML	XML
MHT	I assume this is a MHTML output type for 'saved html'
PDF	PDF Output
rawXML	XML
singleXLS	Excel 2000 single sheet
spreadsheetML	Excel 2007 output
XLS	appears not to be generated in xls outputs as of 8.4
XML	XML
XLWA	Excel 2003

So, ideally I'd like to be able to update the properties setting on the list based on the report format. I couldn't figure out if that was possible to overload via javascript, so I took the easy way out. I created 2 lists, one that renders when xls is run and one for html:

1. Create a copy of your list A and A1.
2. Set the rows per page property to 150 on A.
3. Leave the rows per page property blank on A2
4. Create a new string variable with 2 Values 'XLS' and 'Other' the expression can be defined as:
   

   if (ReportOutput () = 'spreadsheetML') then ('XLS') else ('Other')

5. Set the Render Variable property to this new variable on list A and check the Other value.
6. Set the Render Variable property to this new variable on list A1 and check the XLS value.