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

Tab Solution That’s Easy to Maintain Without Code – Guest Post

“How corrupting boredom is, everyone recognizes also with regard to children. As long as children are having a good time, they are always good. This can be said in the strictest sense, for if they at times become unmanageable even while playing, it is really because they are beginning to be bored[…]Adam was bored alone; then Adam and Eve were bored en famille. After that, the population of the world increased and the nations were bored en masse. To amuse themselves, they hit upon the notion of building a tower so high that it would reach the sky. This notion is just as boring as the tower was high and is a terrible demonstration of how boredom had gained the upper hand. Then they were dispersed around the world, just as people now travel abroad, but they continued to be bored” (S. Keirkegaard, the Rotation of Crops).

I like writing nifty solutions for Cognos, which look good, work well, and add a necessary functionality. Tabs for Cognos reports (Not Active Reports, where tabs exist out of box) are a great demonstration for such functionality: It’s a necessary functionality, and when done properly, they make a report look wonderful.

However, I get bored doing the same thing over and over. When writing a tabbing solution, the principal is always the same: There are the tabs themselves, which are essentially links to be clicked on, and when clicked, there are the contents which need to be either hidden or shown based on the tab that was clicked.

Every now and again a developer who may not be very well versed in HTML, JavaScript and/or CSS would ask me to add, remove or change a tab, or change the style of a tabbed menu, or any such thing. To me, this is boring, and as Kierkegaard noted, boredom is corrupting – indeed, it is the root of all evil.

What needed to be done, then, was to come up with a tabbing solution that allows developers to add, remove and edit tab contents and tab styles without needing to write a single letter of code. The scripted solution needed to be generalized so that developers would be able to use Cognos built-in features to control all aspects of tabs. In other words, I needed to write a tabbing solution that would be a piece of cake to maintain, leaving me with more free time to get bored productively in Cognoise forums.

POCT (=Piece of Cake Tabs) is just that solution. It requires no coding to add, remove, or edit the content a tab. It allows the user to style the tabs using nothing but Cognos built in features. Basically, once you set the solution up, you never have to open an HTML item again.

Let’s look at the page structure:

POCT

Under “Tab Headers” HTML Item, one would simply drop in text items one after the other, each containing the name of the tab (The text that would be written in the tab).

Under “Tab Content” HTML Item, one would create a block for each tab, and drop in the necessary objects for each tab. The uppermost block is the content for the leftmost tab header, and so on – tab headers from left to right, content blocks from top to bottom.

Under “Style” HTML Item there are 3 table cells. One can style them as one wish. The leftmost cell represents how a selected tab would look. The middle one represents the styling of a tab which isn’t currently selected. The rightmost cell controls how a tab looks on mouse over. You can also control the look of the entire tab row, by highlighting the blue table cell where the tab headers are and changing its design to modify the design of the tab row.

And that’s it.

Want to add a tab? Add a text item and a block accordingly. Want to change the content of a tab? Just change the content of a block. Want to remove a tab? Remove the header text item and the corresponding block. And styling is truly piece of cake.

Report XML (10.1) – you can either use that as base or copy the main block and anything in it to any report. This was tested on all Cognos 10.x versions, and should work fine on 8.4.x.

POCT-XML-101.txt (1330 downloads)

Nimrod (Rod) Avissar is a BI Front-End Specialist, with a penchant for specialized UX solutions (LinkedIn).

Advanced Select and Search Prompts

This solution was built on 10.2.1.1, but should work on all versions after 10.2. Versions prior to 10.2 will need some rewriting as this is based on the Prompt API.

One of the biggest complaints I get about search prompts is the amount of real estate they take from reports. The multiselect prompts are huge, and users hate them for it. So I was asked to find a way to shrink it down.
SnS hogging the page

Obviously the solution was to everything but the search input and button. The keyword and instruction text can be controlled by the properties, simply setting it to “Specified Text” is sufficient to hide them:
Hiding Keywords

That leaves the Options link and the search results. Personally I don’t believe the results list need to appear unless there are actually results. The options should be at the mercy of the report author, so my solution includes a way to choose a default value and to hide the options.

First a look at the JS

/* function paulScripts.advancedSnS - Paul Mendelson - 2013-09-16
 * Will convert a standard search and select. It will add a button to expand/collapse the 
 * are selection area. The button will display how many matches have been found, and how 
 * many selected. 
 * 
 * The Option param will control the default search method. 
 * 1 = Starts with any of these keywords **DEFAULT**
 * 2 = Starts with the first keyword and contains all of the remaining keywords
 * 3 = Contains any of these keywords
 * 4 = Contains all of these keywords
 *
 * Hide the options link by setting 1 in the hideOption param.
 */ 
paulScripts.advancedSnS = function(promptName,option,hideOption){
  var prompt = paulScripts.getControl(promptName)
    , elm = document.getElementsByName(prompt.getParameterName())[0].parentNode
    , selectTable = elm.getElementsByTagName('table')[4]
    , selectDiv = document.createElement('div')
    , selectOptions = cognos.Report.getReport("_THIS_").prompt.getControlByName(promptName).getSelectOptions()
    , selectChoices = cognos.Report.getReport("_THIS_").prompt.getControlByName(promptName).getSelectChoices()
    , tr = elm.getElementsByTagName('button')[0].parentNode.parentNode.parentNode.appendChild(document.createElement('tr'))
    , td= tr.insertCell(0)
    , slideDiv = document.createElement('div')
    , btn = document.createElement('input');


    if(option) elm.getElementsByTagName('input')[option+2].checked=true;
    if(hideOption) tr.parentNode.parentNode.parentNode.parentNode.nextSibling.style.display='none';
    td.setAttribute('colSpan',2);

//Hacky – Building a new dispatcher request to save the state of the SnS. Do any IBMers have any comments? 
  if(typeof oReq ==='undefined') oReq = new ViewerDispatcherEntry(window['oCV'+'_THIS_'].getRV().getCV());
  if(!oReq.getFormFields().m_aValues[promptName]) oReq.addFormField(promptName,'hidden')

  btn.type='button';
  btn.value='';

  if(!selectOptions&&!selectChoices) btn.value='click to open';
  if(selectOptions)  btn.value = selectOptions.length + ' found. ';
  if(selectChoices)  btn.value += selectChoices.c.length + ' selected.';

  selectTable.parentNode.insertBefore(selectDiv,selectTable);
  selectDiv.appendChild(selectTable);
  
  //the right side of the table is a bit ugly; not enough padding.
  selectDiv.style.paddingRight='5px';

  // Sets the table of the select options to position absolute. This will prevent the page from expanding vertically
  // when the table is visible;
  selectDiv.style.position='absolute';
  selectDiv.style.overflow='hidden';
  selectDiv.style.backgroundColor='white';

  btn.onclick = function() {
    if(selectDiv.style.visibility=='hidden')
      {slidedown(selectDiv);
oReq.addFormField(promptName,'visible');}
    else {slideup(selectDiv);oReq.addFormField(promptName,'hidden');}
      return true;

  }

  selectDiv.style.visibility=oReq.getFormFields().m_aValues[promptName];
  td.appendChild(btn);
  return true;
}

The JS itself is fairly straight forward. The variables define the DOM element, a JSON array of the found options, another JSON array of the selected objects, and a couple of generated elements.

If a default search option is selected, it will set that.

if(option) elm.getElementsByTagName('input')[option+2].checked=true;

If hideOption is set to true, it will hide the options link.

 if(hideOption) tr.parentNode.parentNode.parentNode.parentNode.nextSibling.style.display='none';

Then it will insert a button, and finally the “results” lists are moved into a generated div which can then be slid open or shut.

  td.appendChild(btn);

Whenever I create a solution that changes the appearance of the page, showing or hiding an element, the users demand that the change persist through a page reset. The easiest way would be to create a new parameter to store the display state. Possibly a hidden text box with box type set to none. But if we always did things the easy way, we’d never learn anything.

 //Hacky – Building a new dispatcher request to save the state of the SnS. Do any IBMers have any comments? 
  if(typeof oReq ==='undefined') oReq = new ViewerDispatcherEntry(window['oCV'+'_THIS_'].getRV().getCV());
  if(!oReq.getFormFields().m_aValues[promptName]) oReq.addFormField(promptName,'hidden')

I haven’t tested this on a wide scale, so I don’t know of any performance impact. To be honest, while I love figuring out little hacks like these, without a more in-depth understanding of what it’s doing, it may not be wise to push this to all reports. The attached report contains two functions, the first using the hacky dispatcher request, and the second using a secondary text box to retain the state. The disadvantage of using the text box is that you’ll need to remember to add that in.

Once the JS is in place, we can call the functions with a simple:

paulScripts.advancedSnS ('Retailers', 3,1);

It will look for the prompt named Retailers, set the option to “Contains any of these keywords”, and hide the options link.

And the final product:
Searchng and Selecting

Report XML:
Advanced SnS (760 downloads)