Subscribing to Cognos Distribution Lists

A reader recently sent me an interesting challenge. They have a large set of reports that are run automatically and saved to the content store. Users may then log into Cognos and viewed the saved output. Now the users want to actually receive the reports in the email. Distribution lists were suggested, but nobody wants to maintain them. So how can we let the users themselves add or remove themselves from these distros, without having to open tickets for always overworked IT staff?

We can set up a stored procedure in the content store to add and remove users from a list. In order to prevent users from signing up to the wrong distro, we can create distro-specific reports with hardcoded values. A secondary benefit of doing it like this is we can ensure that only people who have access to that report can ever sign up.

First, a big WARNING.

This technique requires a connection to the content store. It involves creating two stored procedures that add and remove rows from content store tables. Some Cognos admins have intense aversions to connecting to the content store. While I have tested this technique on my laptop, I have not tested it on your server. I cannot be held responsible for any damage this technique causes, be it losing a distribution list or creating a world-eating blackhole inside the server or any other catastrophic disaster.

Now we can move on to how to implement it.

First, let’s take a look at a distribution list.

I have a grand total of 1 user on my laptop, and I never bothered setting up an auth provider, so I’m using the anonymous user for testing.

The information for this is stored in the content store in the CMREFNOORD2 and CMREFORD1 tables. CMREFORD1 contains the sorting information for the screen, and users won’t appear if they’re not in this table.

As you can see from the SQL, it’s a simple matter to get the table name or the user details. This means we could make a stored procedure similar to:


CREATE PROCEDURE [dbo].[addCAMIDToList]
	@listName varchar(500)
	, @camid varchar(500)
AS

	SET NOCOUNT ON;

	declare @listCMID integer;
	declare @userCMID integer;
	declare @maxSort integer;

--get the list ID	
select @listCMID = distName.cmid
from cmobjnames distName 
where distName.Name = @listName

--get the user ID
select @userCMID = users.CMID
from 
	CMOBJPROPS1 users 
where 
	users.OBJID=@camid

--if there's any sorting, we need to know about it here.
select @maxSort = coalesce(max(CMREFORD1.ORD),0)+1 
from 
	CMREFORD1 
where
	CMREFORD1.CMID=@listCMID

	begin
	if @userCMID  not in (select refcmid from dbo.CMREFNOORD2 where cmid = @listCMID and refcmid = @userCMID)
	  INSERT dbo.CMREFNOORD2 (CMID,REFCMID)
	  VALUES (@listCMID, @userCMID) ;
	if @userCMID not in (select refcmid from dbo.CMREFORD1 where cmid = @listCMID and refcmid = @userCMID)
	  insert dbo.CMREFORD1 (propid, cmid, ord, refcmid)
	  values (27, @listCMID, @maxSort, @userCMID);
	end;
	
	
	
	SELECT @@ROWCOUNT as "rowCount"

Removing a user from the distro is even easier.

CREATE PROCEDURE [dbo].[removeCAMIDFromList]
	@listName varchar(500)
	, @camid varchar(500)
AS
BEGIN tran
	SET NOCOUNT ON;


delete distMembers
from 
	CMREFNOORD2 distMembers
	inner join cmobjnames distName on distName.CMID = distMembers.cmid
	inner join CMOBJPROPS1 users on distMembers.REFCMID = users.CMID
where 
	users.OBJID=@camid
	and distName.NAME = @listName;

delete distMembers
from 
	CMREFORD1 distMembers
	inner join cmobjnames distName on distName.CMID = distMembers.cmid
	inner join CMOBJPROPS1 users on distMembers.REFCMID = users.CMID
where 
	users.OBJID=@camid
	and distName.NAME = @listName;

	SELECT @@ROWCOUNT as "rowCount";

We can now create a framework model to import them into Cognos. First, set up the datasource to the Content Store database.

Then, in framework, run the metadata wizard to import the two SPs.

In both cases, the parameters are the same, so use the following:
listName: #prompt('listName','token')#
camID: #substitute('"\)','',substitute('CAMID\("','',join('',CAMIDListForType('account'))))#

Let’s publish it!

With the package published, we can create the reports to add users to or remove users from the distros. With these two reports, it’s just a matter of adding a drillthrough to the “Add users” to the Cognos saved version, and a drillthrough to “Remove from distro” to the emailed version of the report. No need for drillthrough parameters or any JavaScript. As mentioned before, you can secure the Add Users report using standard Cognos security. This will ensure only those people who should be able to see the report are included in the distro list.

Attached in this post is the model.xml, and the two report XMLs.

Distro-lists.zip (874 downloads)

Guest Post: On Cognos Reports Performance Improvement

One of the tasks I’m hired to do quite often is to analyse why a report (or a group of reports) is loading slowly, and how could this be improved. The good news are, when a report runs for a very long time, there is almost always something we can do to improve it. But first, we must analyse what causes the report to run long.

The first thing we want to do is to rule out the query as the culprit. That is, we want to see how long it takes to get the report data from the database. This is best done by getting someone who is well versed in SQL or MDX to write a query that will pull the relevant data from the database. Many BI developers have sufficient knowledge of SQL, but in case you don’t, get a DBA to help.

One might ask – why not just take the SQL Cognos generates, and run that against the database. The answer is that we are trying to figure out what Cognos does wrong, and that includes the query generation. We need to compare what Cognos does with a well structured query.

If your manual query takes a long time to give results, it means that the underlying database has a hard time supporting your query. There could be many reasons for that. First of all, check that your tables are properly indexed and, if necessary, partitioned. Again, developers with gaps in knowledge in terms of DB tuning will do well to consult a DBA. If you are aggregating large amount of rows, or creating many joins, maybe an aggregated table or a materialised (Indexed) view will solve the problem. If you are doing multiple aggregates based on a few million rows, perhaps a Dynamic Cube would be a good bet, or you could try building an OLAP cube to support your reporting needs (Almost any cubing solution would be better than Transformer, but if push comes to shove, Transformer could be better than nothing).

If your data source is OLAP, and a query takes long to come back, it probably means you might want to design your cube differently. The most important tip I can give you here is to avoid nesting in as much as possible – try to hierarchise the nested columns under one single hierarchy, it will also help with aggregates. A rugged hierarchy is often preferable to a full cross join between different hierarchies, even when nonempty is defined in the query.

If your manual query returns within a reasonable time, it’s time to compare it with the Cognos generated query (Obtain it from tools->Show generated SQL/MDX). Just skim through the Cognos query and see that there aren’t any major differences. If there are, it could be your framework model is problematic.  Run the Cognos generated query against the DB – how long does it take to come back? If much longer, then your model is probably at fault. The most usual culprit in cases like these are ill defined relations.

If the query Cognos generates is fine, we’re probably looking at some processing done on the result set. There are several of those, the common ones are master-detail relationships, chart generation and crosstab generation. There are ways to improve master-detail relationships over CQM (Follow this link, and also make sure that you are sectioning by a single key field), but ultimately, a DQM model would optimise master-detail relationships better than any wizardry. Crosstabs and charts over relational are rendered by creating mini-cubes on the server side, these may take some processing – again, the best cure is DQM, or creating a dimensional data source. If you are rendering many charts, the charting engine will queue some of them – not a lot to be done here other than increasing number of connections to the Graphics Service, minimising the amount of charts by using matrix rows/columns where appropriate or by loading the serially, or creating a RAVE based chart that spans multiple charts together.

These are the basics. There are obviously many cases which aren’t covered here, but that should give you the first-aid steps. If you tried all this and your report still slugs, then it is time to call the cavalry in.

 

Nimrod (Rod) Avissar is a Cognos expert, with a penchant for specialized UX solutions. Feel free to drop me a line! (LinkedIn).

 

Guest Post: A New Take on Date Range Prompts

Since version 10.2 of IBM Cognos BI Suite, IBM included an API to access and manipulate prompt objects. Since prompt objects are the main instrument we use to allow users to communicate with a report (Interactivity or user selection), being able to manipulate them however we see fit can change user experience dramatically for the better. There are countless examples of how the prompt API can be used to achieve this. For example, dynamic defaults: Suppose you have two prompts, for region and for products. You want the default product selected to be the best selling product in the region selected. With prompt API, this can be achieved easily.

In this post I’d like to showcase one of the first solutions I ever wrote using Prompt API, because it was one of the things I wanted to solve for a long time.

Every so often we add “from date” and “to date” prompts to a report, to use for filtering the report output to show only data from the date range selected. The problem is, most users and most use cases don’t require the sort of flexibility a date range offers: most users will not run their sales-per-branch report between April 23rd and May 2nd, for instance, because it’s an arbitrary chunk of dates. Instead, users are likely to filter dates for last month, this MTD, QTD, YTD, last week and so on. So, basically, set, standard, comparable time frames. And sometimes the date range prompt can be replaced with a drop down list of such pre-set ranges, but other times, users ask to still have the flexibility of choosing to and from date, but nonetheless, still mostly use the set, comparable ranges.

Now, in order to select last month’s dates with two date prompts, your average user will need 6 clicks: One to open from date calendar, one to page back to last month, one to click on “1”, and the same process with the “To date” prompt. For YTD, they might need more. That’s a lot of clicks. Also, developers often have to write scripts to get the default value right, and because these date prompts are never done in a centralised, reusable manner, they end up writing a script for each report. I have long fought the war on developers wasting time by doing things more than once, and this case is no different. Even if reports require different default times, the solution can still be generalised, and therefore made reusable.

My solution uses JavaScript and Prompt API to add to the date prompt functionality. Here is how it works:

Date Solution

I’m using two date prompts, and adding 7 pre-defined links, which, when clicked, fill in their respective dates. So, for example, clicking on MTD will set the from date prompt to the 1st of the current month, and the to date prompt to today’s date. There’s also a verification mechanism in place to ensure that from date is always earlier than to date, or equal to it.

But how do I make this solution generalised? Let’s take a look at the report studio report:

RS Look

The bit in blue is the error message the user will get if they choose an end date that’s prior to the start date. The bit in blue is a text that should be replaced with another text – containing just one number between 1 and 7, corresponding with a dynamic ate range.  “1” is YTD, 4 is WTD and so on.

Now, if you drag in a layout reference object to this interface, here’s what you’ll get:

override

You can override&replace the warning message and the default text. So, if the default for a certain report is “last month”, you’ll override “Defaults”

replace

Drag in a text item and insert “5”

default set

When you run the report, the default would be last month:

final result

This way you can set a different default value for each report in a reusable manner.

I’m attaching the XML, of course, but pay attention to these caveats:

1. The script has seven preconfigured date ranges. You can change them or add to them as you require, and use the general example in the code, but it requires some knowledge of scripting. Unfortunately, I will not be able to provide support for such customisations.

2. If you’re relying on my script to manipulate weeks, pay attention that my script assumes Monday is the first day of the week. Israelis especially, this means you’ll have to change this logic (Weeks in Israel begin on Sundays).

3.This is 10.2.2 – You can downgrade it to 10.2.x by changing the version number at the top.

 

daterange.txt (9222 downloads)

 

Nimrod (Rod) Avissar is a Cognos expert, with a penchant for specialized UX solutions. Feel free to drop me a line! (LinkedIn).