Transformer Cube Datasource

 Cognos Transformer datasources can be found in cs7g.ini located in <install location>\CS7Gateways\bin folder.

There will be entries under [Databases]. The name will be the same as the one defined in Datasources window.

Cognos v11 Long Columns When Exporting to Excel v1

I encountered another export to excel issue where the 3rd column stretched too long because a text somewhere in the headers is too long and the solution in my previous post did not work. 

After a bit of reading and testing I found another solution... add multiple columns to the table where the text is located, then merge the columns. 

How many columns you need to merge depend on several factors including how long the text is, the other text headers/labels in other part of the report, etc. You just have to experiment to get the best excel output you can get.

Cognos v11 Long Columns when Exporting to Excel

 I encountered this issue of unsightly long header columns when exporting to excel.

Simple solution is to set the column width of the offending cell to limit its length.

Viola better looking report rendered in Excel.

A bit of a side effect is that the HTML output looks like this. Not that bad though.

QE-DEF-0030 QE-DEF-0360 2-part Reference

 I recently encountered this error in one of the crosstabs of a report I am editing:

QE-DEF-0030 Expression parsing error.

QE-DEF-0360 The query contains a reference to at least one object '[SQL2].[migratedColumn]' that does not exist. Possible cause is the use of 2-part reference to a model query item, which is a deprecated feature.

This happens when I run or preview the report. Tabular view of the query is ok and returns data.

The root query, let us call it YTD, has SQL as its source. The crosstab's query source, is using YTD as its source:

I check everything in the query and crosstab, including the data items for sorting, etc and everything seems to be ok. .Took me several hours to figure out what is wrong.

Turns out, I edited the SQL and somehow did not validate it. Validating the SQL made the error go away.

FM Datasource Configuration

I was having a little bit of a hard time figuring out why the numbers is my cognos report do not match with the data from my SQL Server query. After some tests, I realized I might not have same datasource. After some more checking, I noticed this configuration in my Framework Manager model:

I need to do some test again to describe my observations and infer what is happening.

Content Store Tables

Here are some information on the content store tables with a couple of edits. The sources are found in the links below. Credit (or blame :)) goes to them.

CMOBJECTS - content store objects (as of v11)
CMCLASSES - object type (report view = 19, etc)
CMREFNOORD1 - reference table. Here the report source of a view can be obtained.


https://www.ibm.com/developerworks/community/forums/html/topic?id=77777777-0000-0000-0000-000014692216


http://it.toolbox.com/wiki/index.php/Cognos_Content_Store

Cognos Content Store Database Tables Table name Description
CMSYSPROPS This table has the Content Store Version.
CMOBJNAMES This table has the names of all the objects in the content store.
CMOBJPROPS1 Users, Roles \ Group Distribution list and contact information such as Email, phone number, Fax Given name etc are stored in this table
CMOBJPROPS2 Report scheduling information is stored. This table has fields like hour, day week etc
CMOBJPROPS3 Stores Screen Tip and Object description provided while creating the objects are stored here
CMOBJPROPS4 Stores the printer paper setting details like height and width of A3 , A4 , letter and 11X17 paper orientations
CMOBJPROPS6 Has the details of the all packages which were published using the FM.
CMOBJPROPS7 This table stores the XML of all reports and models. This is basically to maintain the metadata about the structure of the reports and models.
CMOBJPROPS10 Contact information is stored in this table. This table has columns like Contact Email and Contact.
CMOBJPROPS11 This table stores the data sources configuration details like connect string, the cube location etc.
CMOBJPROPS13 It stores the names of parameter passed to the range prompts.
The binary value in CPARMVALUE is XML compressed with gzip.
CMOBJPROPS14 Has details regarding the versions, creation time etc of the objects in the content store.
CMOBJPROPS16 This table provides the status of the multiple services of Cognos like LogService, MonitorService, ReportService, SystemService, JobService
CMOBJPROPS17 Stores performance details of each component like “query Studio, Analysis Studio, Event Studio” etc
CMOBJPROPS18 This table stores the drill path from the source to the final target report.
CMOBJPROPS20 Stores details regarding which are all the reports for which prompting has been enabled. And also has the details of which are the reports which has the default report options overridden.
CMOBJPROPS24 Stores the printer configuration details
CMOBJPROPS25 Stores the data regarding the objects deployed, like the deployed folder, the reports, the number of folders present in the deployment archive, etc
CMOBJPROPS26 This table stores the data about all the packages imported / exported in C8, with the properties selected during the process.
CMOBJPROPS27 Has the details regarding the data source created in the content store using Cubes.
CMOBJPROPS30 Stores the registration , service description etc details about portlets in this table
CMOBJPROPS31 Has the custom logging level for each of the Cognos services, CMID can be be linked to CMOBJNAMES for the names of each of the services
CMOBJPROPS32 Has the details of the stored procedures used as the query items in the FM model.
CMOBJPROPS33 Detail related to users, user groups user roles.
CMOBJPROPS34 Has the details regarding the drill through parameters of the drill through reports. The parameter assign values are present in coded format
CMOBJPROPS36 Has the list of all the Models published using framework manager
CMOBJPROPS37 This table has the details of the routing sets configured for server / load balancing for each package published in the content store
CMOBJPROPS38 Configuration details about number of items to retrieve in studios, for a package are saved under this table.
CMOBJPROPS39 Has values for properties of reports and views. PROPID can be linked to CMPROPERTIES for property names
CMOBJPROPS52 Contains the properties for the connections
CMOBJPROPS55 Has the URI for icons for each entry in Cognos Connection
CMLOCALES Has the locale ids associated with each language supported by Cognos 8

Define Data Server Connection in Cognos v11

for Microsoft SQL Server:

JDBC URL is:
jdbc:server://<server name>\<instance>:<port number>;DATABASE=<database name>

This is the format I was able to make it test successfully. Make sure your port number is correct. To find out SQL server port, please see previous post.

This also worked:
jdbc:server//<server name>:<port number>;DATABASE=<database name>;INSTANCENAME=<instance name>

You can also use a datasource connection already defined in Cognos Administration provided the JDBC connection for that datasource is enabled and configured (DQM enabled). You just have to check the Allow web-based modeling checkbox. You can find this checkbox in Connection tab of Properties for the datasource.