January 5, 2011

MSExcel Output Formats

All seven of the initial m-Fin reports can output to MSExcel but only two can output csv.

Cognos offers three output formats for MSExcel.

cognos_msexcel_formats

  1. 2000 Single Sheet — all pages in one MSExcel worksheet.
  2. 2002 — each page in a separate MSExcel worksheet (ie. tab).  For m-Fin reports that page by speedtype, each speedtype will be in its own worksheet.
  3. CSV — comma separated values in one MSExcel worksheet.

The first two formats make something in MSExcel that approximates the look of the HTML output format.  Subtotal lines are included.  Subtotals are simply additional lines of data in the worksheet, not MSExcel formulas.

The CSV output format is different.  It isn’t really the report but rather the SQL answerset upon which the report is based.  You’ll prefer this output format if you want to work with the data yourself such as sorting a unique way or summarizing in a PivotTable.  CSV output format works only for two of the current seven m-Fin reports:  SIMPLE SPEEDTYPE QUERY and FINANCIAL DETAIL.  I can’t stop Cognos from offering CSV on the other reports, but if you try it you will be confronted with an additional FOPPS prompt page.  All five values are required.  Yuck.

csv_extra_mfin_prompting

I know this is bad.  I’m sorry!  This is a compromise I made for handling no-data situations.  What’s a no-data situtation?  When you ask for a speedtype that has had no activity whatsoever.  These reports will render a page with the speedtype’s header information.  It turns out this also helps these reports run fast.  But when you request CSV output format you get this unwanted prompting.

It’s critical for some users, however, that the CSV output format work properly for the m-Fin FINANCIAL DETAIL.  So I made a different compromise in that report (and also in the m-Fin SIMPLE SPEEDTYPE QUERY).  CSV works but there is less header information and speedtypes having no activity do not appear in the output.

You will need to disable pop-up blockers.  If your output window exists only for a moment and then vanishes (a frustrating experience) it is likely the window was shut automatically by a pop-up blocker.  Sometimes it is hard to find where to turn these things off.  An alternative is to hold down the Ctrl key.  You may need to hold it down starting when you click the finish button and continuing all the way until when the output is finally delivered.