February 2008


I’ve been burned by this one so many times it’s not funny.  Although if you like seeing me get frustrated, then I guess it is funny.

Here’s the issue: you have a SQL SELECT statement that you’re using in a Cognos Report Studio report, and you’ve verified that it is syntactically correct. It runs fine in Studio Express, for example.  But then you try to add a filter (also one that is syntactically correct) in Report Studio, and you get an error.  The SQL SELECT statement is correct, the filter is correct, but enable the filter and if fails.  What…?

Short answer: Don’t put “ORDER BY” sort statements in the SQL SELECT command. Your order statements should only occur in Report Studio.

Long answer: 

The reason this errors out is that when you put in a filter in the report (not in the original SQL SELECT), Report Studio adds that filter to the end of the SELECT statement it constructs.  So if the SQL is:  

SELECT ApplicationName from R_Applications

And you add a filter like “[ApplicationName]=’something’”, then Cognos bundles them together and sends this request to the SQL server:

SELECT ApplicationName from R_Applications 
WHERE [ApplicationName]=’something’

If the statement in the original SQL is

SELECT ApplicationName from R_Applications
ORDER BY ApplicationName

Then when Cognos sends the statement with the filter enabled it sends:

SELECT ApplicationName from R_Applications
ORDER BY ApplicationName
WHERE [ApplicationName]=’something’

Which is a syntax error: WHERE cannot come after ORDER BY.

Ta da!

Caveat: running v8 of Report Studio, I hear rumors that this behavior changes slightly in later .x revisions.

When you create burst reports or run any Report Studio report so that it sends the results out as an email, you have a few options on the format in which the file goes out. Some of them don’t work so well in our environment because of restrictions on file types that we have in our email systems, so .htm or .html files will never make it past our filters: that one is obvious. What other types can you send reports out as?

  • CSV: success!
  • XML: success!
  • PDF: success!
  • Excel: FAIL!

That last one is a little confusing, to say the least. I would expect XML to fail before XLS. There’s a reason it does fail, though: no matter how you attempt to send an Excel attached file, Cognos actually sends out an .mht file instead, albeit with a MIME type of application/vnd.ms-excel. In my mind this is astonishingly backwards, especially considering there is no indication of what it’s going to do and why.

In any case, here’s how to fix it: you must add a server parameter to send the mht file using an .xls extension. This means you’re still sending an .mht file, but it at least looks like, and behaves like, an Excel file. To do this you must add a server parameter.

Steps:

  1. Click the Tools menu in the Cognos portal and select “Server Administration”
  2. Select “Set Properties” for ReportService
  3. Select the “Settings” tab
  4. In “Advanced Settings” (usually the first option), click the ‘Edit…’ link
  5. Select “Override”
  6. In the first empty set of boxes, type in the parameter name RSVP.FILE.EXTENSION.XLS and set the value to TRUE

Repeat the above steps to Set Properties for BatchReportService, and when you send the reports send them as Excel 2002 (NOT as Excel 2000 or Excel single sheet).

Not very smart, misleading, and the cause of the error you get when you try to open an “Excel” file that came from Cognos, where it states that the file is not in the format that the extension indicates. An error that you get every. time. you. open. the. file.