Avoid Detail Filter in Page Set

Here is a cool puzzle posted by CommandoCognoise and fully solved by CognosPaul in cognoise.com

The simple report displays a crosstab with two sets of rows, [Product] and [Sales Employee]. Columns consist of [Year] and measure is the [Sales].

However there is a requirement to display the crosstab across multiple pages per employee. Thus page 1 will look like:

and page 2 will look like:

At first glance, the requirement could easily be accomplished using page sets grouped by [Sales Employee] and creating a master-detail relationship on that column. However this will result to the [Product] getting filtered by the specific employee.

To get around this, I nested the [Product] row with the root member of [Employee] dimension.

Nesting the columns with the [Employee] root member will also work.

But there is another solution which will not require nesting the rows or column. This is particularly useful if the format is required as the report is getting exported to excel to be processed further.

First step is to create another query with an [Employee] data item. This will be used for the page set.

The second query is the one which contains the items to be used in the crosstab. Add a data item calculation which looks like:

#prompt('emp','mun','[MUN OF ROOT MEMBER OF EMPLOYEE]')#

Replace the [Employee] row with this data item. The last step is to replace the master-detail relationship linking the [Employee] data item from the first query used in the page set with the 'emp' parameter.

One coolest solution!

DQM in Cognos Analytics

MS SQL Database

As of Cognos 11 sqljdbc4.jar is only needed in <install folder>\drivers

Do not put in <install folder>\v5dataserver\lib nor in <install folder>\webapps\p2pd\WEB-INF\lib
Doing so will generate a BMT-IMP-0002 CAF-WRN-2082 error when adding a Query Item in Framework Manager.

Defining both instance name and port is ok. Make sure you know the port number. If not, use this query to find out the port number:

select distinct local_net_address, local_tcp_port
from sys.dm_exec_connections
where local_net_address is not null

If the user credentials is not authorized to run that query, then for now you are out of luck.

Datasources with jdbc connector can also be used as data servers by checking the "Allow web-based modeling" box on Connection tab in datasource properties section.

In your Framework Manager model, you can set the query mode property to DQM or Compatible mode by selecting the Project and changing the query mode property.

Cognos Analytics Prevent View Link Breaks

I'll edit later. For now I need to save this link

https://www.ibm.com/communities/analytics/cognos-analytics-blog/administration-changing-the-behaviour-of-object-copying-in-cognos-analytics-11-0-7-release-7/

Install Cognos v11 with Single Sign On

In this post, I will put in detail the steps how to install Cognos v11 with single sign on (SSO) capability. I will also write down the issues I encountered as well as the solutions to the said issues. Note that I was installing Cognos Analytics for Windows 64bit version 11.0.13. I'll add another note as well that we do not require dynamic query mode (DQM) so I don't know if there are additional files needed for DQM.

Since we were using Microsoft SQL Server for our backend as well as our content store database, the first thing to do is:

• Download the SQL Server driver. The file you required is sqljdbc4.jar. 

After several attempts to install trying out different options, the only way I was able to make SSO work smoothly is to

• Install using custom installation and select all components. Particularly include optional gateway.

Before running Cognos Configuration,

• Copy the sqljdbc4.jar in <install folder>\webapps\p2pd\WEB-INF\lib

or any folder which is included in the java path like <install folder>\drivers. The first folder I mentioned has .jar files in it so the sqljdbc4.jar seems to be at home in that folder. Again, do this only if you will use MS SQL Server database.

** this cause BMT-IMP-0002 CAF-WRN-2082 error when importing tables in Framework Manager. Try to copy sqljdbc4.jar in <install folder>\drivers instead.

• Run Cognos Configuration.

You only need to configure your content store and authentication (we use LDAP). And while you are in authentication, might as well disable anonymous login. Start your Cognos services and test by logging in to:

http://<servername>:9300/bi/v1/disp

If you can open this page successfully and login, you are half way through.

• Download the IIS automated script here:

https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.inst_cr_winux.doc/t_gateway_iis.html

You can initially follow the instructions manually. You will appreciate the script a lot more if you do. And it is a great learning experience.

appcmd list app

appcmd delete app