data entry Archives - CognosPaul

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