Creating a data entry page in Cognos

Cognos is a wonderful BI platform, but it does not handle write-backs to the database very well. By no means should Cognos be considered a data entry platform, but there are times when you may want to store comments associated with a specific report. Other times you may want to give users the ability to enter and save targets for specific data. It is even possible to set up a system to store the prompt selections in a report in a table.

The solution presented here was created in 10.2.1, but will work in every version since 8.4.

At the most basic level, writing back data is very simple. You run a stored procedure which takes specific parameters and passes them to the database. A system for entering comments is a good example:
DE Adding a comment

Multiple comments:
DE Multiple Comments

To begin with, let’s take a look at the stored procedure:

ALTER PROCEDURE [dbo].[insertDateComment]
	@dateKey numeric(18,0)
	, @comment varchar(1000)
AS
BEGIN
	SET NOCOUNT ON;

	  INSERT dbo.datecomments (DateKey,comment)
	  VALUES (@dateKey, @comment) ;
	SELECT @@ROWCOUNT as "rowCount"
END

You can’t get much simpler than this. It accepts two values, dateKey as a numeric and comment as a string, and inserts them into a table. In real-world usage more fields would be necessary, such as the username running the procedure, or the report name, or any other metadata you would want tracked. Since we’re using this in a Cognos report, a value must be returned. Even a simple “select 1 as whatever” would be sufficient.

In Framework, import the SP and populate the input parameters with prompt macros:
DE importDateCommentSP

There are two reports shown, the first report is a simple list showing date and comment (if exists). That’s accomplished by joining the comments table and the time dimension.

The second report contains a conditional block, a singleton, and two input boxes. When the report runs, the conditional block checks if the comment parameter is populated. If not, it renders the first page containing a hidden input for the dateKey, and a visible textbox for the comment. The user enters a comment and presses the refresh button. Again, the report runs, checking if the comments parameter is populated. Since it is this time, it renders the singleton block. The singleton contains the reference to the procedure. Since the parameter names in the textboxes match the parameter names from the procedure, it runs those. As the return value from the procedure is useless to the end user I hide it by checking “rowCount” in the properties of the singleton, and dropping a text item with a descriptive “data entered” message.

So far this has all been simple. But what happens when you want to enter multiple values? One at a time simply won’t do. The trick here is to dynamically change the name of the parameter in the framework. This method is a little more complex, as it uses HTML items and a little bit of JavaScript.

In this method the stored procedure is upserting the value into the table. The SQL for the procedure is using the merge function, which is not the most efficient way of doing an upsert.

ALTER PROCEDURE [dbo].[upsertValue]
	@DateKey [numeric](18, 0)
	, @Value [numeric](18, 0)
AS
BEGIN
	SET NOCOUNT ON;

	MERGE dbo.DataEntry as t
	USING (select @DateKey as DateKey, @Value as Value) as n 
	  on t.DateKey = n.DateKey 
	WHEN MATCHED then update set t.Value = @Value 
	WHEN NOT MATCHED THEN 
	  INSERT (DateKey,Value)
	  VALUES (@DateKey, @Value) ;
	SELECT @@ROWCOUNT as "rowCount"
END

The reference to the procedure uses prompt macros, but with a slight twist.
DE framework upsert

The prompt for the macro is set to #prompt(prompt(‘DateKey’,’integer’,’0′)+’Value’,’integer’,’0′)#

The DateKey prompt will actually change the parameter name for the Value prompt. So for date 1100, the parameter for the value would be 1100Value.

In the report, a simple list of dates is entered, with an HTML item
DE datelist

The HTML item is a report expression with the expression:

'<input 
  type="text" 
  name="p_'+number2string([DataEntry].[Day Key])+'Value" 
  value="'
+case when [Value] is null then '' else number2string([DataEntry].[Value]) end
+'"
  onchange="updateDateArr('+number2string([DataEntry].[Day Key])+')"
/>'

Each row will generate an input box, the name will be the dateKey + ‘Value’ (just like the parameter in Framework). Parameters are populated by inputs with a specific name. So an input with the name p_123Hello will populate the parameter 123Hello. You would be able to use that parameter in a query, or on the page with paramDisplayValue(‘123Hello’).

When a change is made to the input, it will be added to a list of dateKeys, that list is to ensure the procedure is run on updated values only:

<script>
var fW = (typeof getFormWarpRequest == "function" ? getFormWarpRequest() : document.forms["formWarpRequest"]);
if ( !fW || fW == undefined) {fW = ( formWarpRequest_THIS_ ? formWarpRequest_THIS_ : formWarpRequest_NS_ );}
var preFix = "";
if (fW.elements["cv.id"]){preFix = fW.elements["cv.id"].value;}    
var nameSpace = "oCV" + preFix;


var dateArr = [];

Array.prototype.unique = function() {
    var o = {}, i, l = this.length, r = [];
    for(i=0; i<l;i+=1) o[this[i]] = this[i];
    for(i in o) r.push(o[i]);
    return r;
};

function updateDateArr(dateKey){
  dateArr.push(dateKey);
  dateArr=dateArr.unique();
  document.getElementById('p_UpdatedDates').value=dateArr.join(',');
}

</script>
<input type="hidden" name="p_UpdatedDates" id="p_UpdatedDates">
 

Whenever the updateDateArr is called, it will add the new dateKey into the array, make the array unique, and then push it into the p_UpdatedDates input.

In the previous method, the stored procedure was only called when the Comments parameter was populated. The same thing is possible here. Create a conditional block with the Boolean variable paramValue(‘UpdatedDates’) is not null.

Inside the yes block, drag in a new list, stick in the dateKey, and filter that query with

dateKey in (#prompt('UpdatedDates','token')#)

Make sure to set the pagination options of the list. Check “Repeat every page” and uncheck “Allow contents to break across pages” and “Allow horizontal pagination”. This will ensure that the entire list appears in one page. In the same thread, go to File -> Report Properties, and set “Page break by data container for interactive HTML” to Yes. Without setting this option, the next list would be pushed to the next page.

When the report is run with updated values, that list will generate only those rows with updated dates.
DE Only Updated

Put a list in that list, drag in the rowCount from the upsertValue procedure, and define a master detail connection from Day Key to the parameter DateKey:
DE Master detail

Now when you run it, it will update the values.
DE It works

Hiding the list is a simple as wrapping it with a hidden div:
<div style=”display:none”> to the left of it, </div> to the right.
Here it is:
DE Entering multiple values

This post is a bit unusual in that it has several files attached. Three reports, the framework model, and an SQL Server 2012 backup.

Reports:
Data-Entry-Reports.zip (683 downloads)

Model:
Data Entry - Model.zip (470 downloads)

Database backup (SQL Server 2012):
Data Entry Database (525 downloads)

Recovering changes made to Framework after a crash

Have you ever spent hours working on a model? Perfecting each join, meticulously defining the cardinality of each individual join, fastidiously testing the queries each step of the way, only to have Framework crash on you?

In this screenshot, I’ve spent entire MINUTES building the joins.

this represents hundreds of seconds of my life

Now, because I’m a hotshot developer I know I can get away with not having Auto Save (Project –> Options –> Auto Save) turned on.  I live on the edge, so I also turn off the rows restriction when testing my queries.

Now here I go, testing my joins when suddenly… Disaster!

Framework is frozen and will soon die.

I’ve killed Framework! (After setting up a Cartesian join between two fact tables, and disabling the rows restriction and randomly clicking until Windows thought the process wasn’t responding.) And now, with dread in my heart, I open the model already knowing what I’ll see.

Reopening the model reveals that nothing was saved. Which makes sense, since I didn't save anything.

But there’s some hope! Looking at the folder, there’s an XML file there called “session-log-backup.xml”

Saved!

Under the projects menu there’s a Run Script option.

script player

Clicking that opens a file browser. Let’s select that session-log-backup…

run script

It recorded every action performed on the model. What happens if we run it?

My hide is saved

And clicking on accept…

it didnt remember the display settings

Almost perfect, it recreated all of the joins (including the crazy Cartesian) and the packages. But it didn’t remember that I like the layout as Star, not as Standard. Now instead of spending hours of my life recreating my work, It’ll take me entire SECONDS to get the diagram back as a star.

I’m still not using Auto Save though.

BmtActionsHelper.cpp(500) crash when trying to delete an object in Framework

Ever since the upgrade to 10.1.1, many developers have been reporting an issue with Framework Manager. At some point during the development, Framework suddenly starts crashing whenever they delete or move specific items from the model. Frustratingly, trying to fix the issue by importing the model into a new model tends to copy the same problem.

The error in question is as follows:

Invariably, the developers would be frothing at the mouths because either they forgot to save their changes when it crashed, or they have saved their changes, and now they don’t have a way to revert back to before it started crashing like this.

The manifestation of the bug tends to follow a similar pattern. The models are generally complex, old, and contain more than one package. Some models that exhibit this bug have security, and some don’t. Deleting some fields trigger the bug, and some fields don’t. However, because the crash only happens with specific data items, it makes it easier to track down where the problem is.

In this case, a simple verify model explains what the issue is:

A glance at the model.xml shows that, somehow, there are two security views with the same name.

The easiest way to fix the problem is to delete one of the duplicated securityView nodes. There is a risk that Framework will continue to crash if the remaining securityView contains references to deleted objects, so at the very worst you can paste in:

<securityView><name>GO Sales (query)</name><definition/><access/><functionSets/></securityView></securityViews>

If this happens, you will have to redefine the package definitions, but at least the model will be viable again.

EDIT: Lose your work after the crash? See here on how to recover everything!