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 (23 downloads)

Defining GMail as the SMTP Mail Server in Cognos Connection

In anticipation of some upcoming articles, I needed to set up an email provider. Because it’s so ubiquitous, and because I’m lazy, I’m going to use GMail as my SMTP server. Here’s what to do.

First, make sure you enable IMAP in the GMail settings. Click on the gear icon, settings, Forwarding and POP/IMAP, Enable IMAP, and Save Changes.

gmail settings

Now on GMail settings screen there a configuration instructions links for your applications, but for some reason Cognos isn’t listed. So here they are, in Cognos Configuration, open the Notification settings and set the following:

SMTP mail server: smpt.gmail.com:465
Account and password: Google account credentials
Default Sender: your own email
SSL Encryption: True

cognos connection. Now remember, you should use YOUR email, not mine. Mine is reserved.

Restart Cognos.

Now let’s set up a quick report to test the email.
quick and dirty report in Query Studio. A true masterpiece. Also, did I really need to tell people to restart? That should be obvious, right?

And run it…

Cognos report run page. I'm actually taking these screenshots as I'm writing the article. Hope it works.

Fingers crossed…

It works! I was actually worried for a moment. How embarrassing would it be if it DIDN'T work? I would have had to scupper the entire article! And since I have so many planned, this would have been horrible.

Obviously this shouldn’t be used for a production environment. There are also a few Google set limitations, the biggest of which is a 99 email limit per day – so no crazy burst jobs. This is really good for a quick and dirty example.

If I find the time this year (no promises), I’ll be writing about setting up dynamic email subject lines, and different report outputs in a single email. Another article I’m planning is a combination of the data entry method and bursting.

Different Drillthroughs for Graph Elements

I recently received an interesting problem. A multi bar graph needed to have drillthroughs pointing to separate reports. The requirement is to have it seamless for the end-user, no transitional screens. If the user clicks on the revenue bar, it needs to go to the revenue report. If they click on the planned revenue bar, it goes to the planned revenue report.

As the product is currently built, drillthroughs are defined on the graph level, not a measure level. Let’s take a look at the actual HTML being generated:

separateDrillsDefaultChart

<area 
  dttargets="<drillTarget drillIdx=\"2\" label=\"Planned revenue\"/><drillTarget drillIdx=\"3\" label=\"Revenue\"/>" 
  display="914,352,803.72" 
  tabindex="-1" 
  ctx="27::22::18" 
  title="Year = 2010 Revenue = 914,352,803.72" 
  coords="157, 309, 157, 174, 118, 174, 118, 310, 157, 310" 
  shape="POLY" 
  type="chartElement" 
  class="dl chart_area" 
  ddc="1" 
  href="#"
>

In this example, I have a chart with two bars. In the area of each bar, the dttargets is defined with both drills. The drills themselves I’ve named the same as the data item of the measure. We can then use JavaScript to extract the dttargets string, match the label of the drill to the data item name, and place the correct one in there.

/*
 * This will loop through every chart and replace multiple drill definitions with one. 
 */
paulScripts.fixChartDrills = function(chartName){
  var oCV = window['oCV'+'_THIS_']
  , areas = paulScripts.getElement(chartName).parentNode.previousSibling.getElementsByClassName('chart_area')
  , areasLen = areas.length
  , areaDataItemName
  , drills=[]
  , dtargets=[]
;

for (var i=0;i<areasLen;++i){
  if(!areas[i].getAttribute('dttargets')) continue;
  areaDataItemName=oCV.getDataItemName(areas[i].getAttribute('ctx'));

  drills = areas[i].getAttribute('dttargets');
  dtargets =drills.split('>');

  for (var j=0;j<dtargets.length;++j){
    var regexp = /label...(.+?)."/g;
    var match = regexp.exec(dtargets[j]);

    if(match&&match[1] == areaDataItemName) areas[i].setAttribute('dttargets',dtargets[j]+'>');

  }

First we’re finding the chart that we want to do this on, and finding the area map. We loop through the areas, skipping the ones that don’t have any dttargets (like the legend or labels).
For each area with a dttarget, we get the source data item name. Fortunately for us, there’s a useful Cognos JavaScript function to do it! Then a little hackey JS magic to get the label for each individual dttarget we can finally match and replace the dttargets attribute.

Let’s see it in action!
separateDrills

Now it’s very important that the drillthroughs have exactly the same names as the data items. If they don’t do that, this script won’t work – but of course that wouldn’t stop you from using different logic. I built this in Cognos 10.2.2, but I have no reason to think it’s not backwards compatible. The full JavaScript, including the paulScripts.getElement can be found in the report XML below.

separateDrills.txt (132 downloads)