Thursday, September 4, 2008

[updated] Relative Package Names

It is possible to have a report be built on a package that is specified with a relative (to the report) location.
It is not possible to set relative package names through the UI in ReportStudio but you can copy the XML source into an editor, make the changes and paste it back to RS.
The reference to the package is right at the beginning of the XML in the <modelPath> tag.
I tested with relative path on 8.3 and it worked.

This is great for keeping multiple environments on the same Cognos box.
The report age can be the same. There is no need to relink the report with a different package. The package can be redeployed into different folders (new feature in 8.3) with the same name. The packages would be practically the same only they would use different datasources. Changing the datasource is easy... just run a sed script on the model's XML and publish.
If you need to maintain a lot of environments eg. QA, staging, production, support on the same Cognos then you will find this very useful!

Update
Damn, it doesn't work. Report Studio axccepts the relative path but before saving the report it substitutes it with an absolute path.
the good news is that in 8.3 you can relink a report to another package through the portal by setting the properties... there's no need to open up the report in RS.

Monday, September 1, 2008

Crosstabs with Column Headers

aka How to make Crosstabs that look like lists



| sales
------+--------------+---------
branch| sales person | <1234>
+--------------+---------
| sales person | <1234>


will look like


| sales
-----------+---------+--------
1st Avenue | Aaron A | 1,000
+---------+--------
| Betty B | 2,000
-----------+---------+--------
2nd Street | Clare C | 1,500
...


If it was a list it would have column headers and would look like this:


branch | sales person | sales
-----------+--------------+--------
1st Avenue | Aaron A | 1,000
+--------------+--------
| Betty B | 2,000
-----------+--------------+--------
2nd Street | Clare C | 1,500
...



To achieve this using a crosstab you need to add extra Text Items and hide some cells by setting their box type to ``none''



HIDE | "branch" | "sales person" | sales
---------------------+----------+----------------+------
branch| sales person | HIDE | HIDE | <1234>
+--------------+----------+----------------+-------
| sales person | HIDE | HIDE | <1234>



Hiding the top left corner will pull the extra Text Items to the left so that the "headers" will nicely align with their columns.
But adding these new text item still creates extra empty columns in the crosstab... these have to be hidden as well.

The resulting crosstab will look just like a list, displaying column headers.

You may need to play around with styling it to get everything in the right color....