CALL US NOW:

1-401-727-2400 U.S.
1-905-532-0440 Canada

Archive for the ‘Report Tips and Techniques’ Category

Creating Tabbed Reports in Report Studio

By Robert Edis, Senior BI Consultant

Business Problem:

User does not want to navigate between separate prompt pages as that requires too many mouse clicks and looks old fashioned. They want to move between collections on prompts either on a single prompt page or the main report page.

Using a Prompt Page allows the user to achieve this result with only one mouse click on the Run button, takes advantage of the inbuilt functionality and behavior of a Prompt Page, and does not interfere with a clean look and feel on the report page. Does not allow the user to run report and see results based on defaults initially.

Using a Report Page allows the user to achieve this result with no additional mouse clicks and displays an initial report result based on defaults without user having to enter prompt values. Does not allow for use of inbuilt Prompt Page functionality and may cause standard prompt button behavior to change.

Both options require a significant amount of JavaScript and may not work the same across browsers. The solution shown here was developed and tested on Microsoft Internet Explorer 9.

Solution:

Add tab panels to a single prompt page. Example uses the Go Sales (Query) package.

Note that conditional blocks or version 10.2.2 tabs are not used. This solution has been tested on Internet Explorer 9 only and I cannot provide assurance that it will work the same way in other web browsers.

Figure 1 – as rendered when run

  1. Create a new list report in Report Studio.
  2. Add a Prompt Page then open it for editing.
  3. Add a block to the page body. Name it “blkTabs”.
  4. Add a table to the block (2 rows, 1 cell). Name it “tblTabs”.
  5. In row one, cell one of tblTabs add another table. (1 row, 3 cells). Name it “tblTabsHdr”.
  6. In the first cell add the following:
    1. An HTML item. Add Description “Tab Headers”. Add the code <span id=”tabHeaders”>. This begins a span with an identifier that can be reference in the JavaScript.
    2. A block after the HTML item. Name it “blkTabNames”. In this block add text items, one for each tab you require. Name each text item as “txtTabn” where n is an integer starting with 1.
    3. An HTML item after the block. Add Description “End tab headers”. Add the code </span>. This closes the span. A span is an HTML object use to contain an area on a page.
  7. In the next cell add an HTML item use the Description “Finish button”. Note this is only required if you want to hide the default navigation buttons on the Prompt Page or if you need to execute some JavaScript before the Finish action. Add the code
    <input type=”button”
    style=”padding: 3px; font-weight: bold; font-size: 10pt; font-family: Arial, Serif; background-color: #669900; color: white; border: 1px solid silver;” onclick=” [add your JS code or function call here]; promptAction(‘finish’);” value=”Submit”> [you can use a different label than “Submit”].
  8. The third cell is just a spacer to make it easier to control the layout.
  9. In row two, cell one of tblTabs add the following:
    1. An HTML item. Description “Tab content”. Code <span id=”tabContent”>.
    2. A block for each tab after the HTML item. Name them “blkTabn” where n is an integer starting with 1. Format the blocks (BG colour, font, size, border, etc.) per reqquirements.
    3. An HTML item after the last block. Description “End tab content”. Code </span>.
  10. Below the block blkTabs add:
    1. An HTML item. Description “Tab styles”. Code <span id=”styleMaker” style=”display:none”>. The display:none hides the content of the span area.
    2. A table named “tblStyles”, (1 row, 3 cells).
    3. In each cell add a text item with the following names and text:
      1. Name: “txtSlctTab”, Text: “Selected Tab”.
      2. Name: “txtUnslctTab”, Text: “Unselected Tab”.
  • Name: “txtHover”, Text: “Hover”.
  1. Format each cell with the background color, foreground color, font, border, etc. you want. These will be the way the tab buttons will be rendered on the page.
  2. Add a Text Box prompt control below the table tblStyles. Not required, parameter name as “currentTab”, name as “currentTab”. This is used to record which tab the user selects.
  3. An HTML item. Description “End tab styles”. Code </span>.
  4. An HTML item. Description “Tab functions”. This could also be placed above the block blkTabs. Use code below: (note: text box smaller than contained text. Select all, copy and paste to text editor to view).
<script>/*initialise Tabs*/
var headersDiv = document.getElementById(“tabHeaders”);
var theHeaders = headersDiv.getElementsByTagName(“span”);
var contentDiv = document.getElementById(“tabContent”);
var allDivs = contentDiv.getElementsByTagName(“div”);
var theContent = new Array();
/***** theContent will contain all relevant divs */for(var di = 0; di < allDivs.length; di++)
{
if(allDivs[di].parentNode.id&&allDivs[di].parentNode.id==”tabContent”)
{
theContent.push(allDivs[di]);
}
}var theStyles = document.getElementById(“styleMaker”);
var allTabStyles = theStyles.getElementsByTagName(“td”);
var selectedTab = allTabStyles[0].style.cssText, unselectedTab = allTabStyles[1].style.cssText, hoverTab = allTabStyles[2].style.cssText;/*first tab is selected by default. currentTab does not yet have a value. */theHeaders[0].style.cssText = selectedTab;
theHeaders[0].tabID = 0;/***** Starts at 1 to set all other tabs to be unselected */for(var thIdx = 1; thIdx < theHeaders.length; thIdx++)
{
theHeaders[thIdx].style.cssText = unselectedTab;
theHeaders[thIdx].tabID = thIdx;
theHeaders[thIdx].onclick = function()
{
clickTab(this.tabID);
cognos.Report.getReport(‘_THIS_’).prompt.getControlByName(‘currentTab’).setValues([{‘use’:this.tabID}]);
}

theHeaders[thIdx].onmouseover = function(){this.style.cssText = hoverTab;this.style.cursor = “pointer”;};
theHeaders[thIdx].onmouseout = function(){this.style.cssText = unselectedTab;this.style.cursor = “default”;};
}

for(var ci = 1; ci < theContent.length; ci++){ theContent[ci].style.display = “none”;}

/*End of tabs initialization*/

 

if(cognos.Report.getReport(‘_THIS_’).prompt.getControlByName(‘currentTab’).getValues().length)
{
clickTab(cognos.Report.getReport(‘_THIS_’).prompt.getControlByName(‘currentTab’).getValues()[0].use)
}

 

function clickTab(id)

{
//loop through headers
for(var i = 0; i < theHeaders.length; i++)
{
if(i != id)
{
theHeaders[i].style.cssText = unselectedTab;
theHeaders[i].onclick=function(){clickTab(this.tabID);};
theHeaders[i].onmouseover=function() {this.style.cssText=hoverTab;this.style.cursor=”pointer”;};
theHeaders[i].onmouseout=function() {this.style.cssText=unselectedTab;this.style.cursor=”default”;};
}
else
{
theHeaders[i].onmouseover=””;
theHeaders[i].onmouseout=””;
theHeaders[i].style.cssText=selectedTab;
cognos.Report.getReport(‘_THIS_’).prompt.getControlByName(‘currentTab’).setValues([{‘use’:id}]);
// Update hidden control with curr tab index
}

}

 

//loop through content

for(var j=0;j<theContent.length;j++)
{
if(j!=id) { theContent[j].style.display=”none”; }
else { theContent[j].style.display=”block”; }
}

}

 

</script>

  1. If you don’t want to see the default navigation buttons then select the page footer and set the Box Type property to “None”.
  2. Test the results by running the report.

At this point your report should look something like the below. Now it’s time to add content to the tab panels.

To use this technique on the report page instead of a prompt page add an additional tab header and block to the beginning of the series. The block should be hidden. The tab label can be something like “Results”. Move the blkTabs block to the report page header below the title or the page body above the list.

Click here for Report Studio Report Specification


How to Implement Custom Search and Select Prompt

By Robert Edis, Senior BI Consultant

Business Problem:

Users require a special multi-select prompt control using a filtered query. They need to add/remove choices, and reorder choices in the control. This is useful when the possible choices are very many. They need:

  1. A multi-select list box to display a filtered list of values. This is populated from a query that is filter by user choices in prior prompt controls.
  2. From the list they need to add choices to a final list of choices to display in the report.
  3. They can add choices to or remove them from the final list.
  4. They can reorder the choices in the final list.
  5. They can change the original filter prompts on the same page to get a different list from which they can append to the final list.

Solution:

This solution uses a prompt page with all prompt controls on the same page. There is significant use of JavaScript to manipulate the prompt controls. A count of the choices in the final list is displayed. This technique has been tested with Internet Explorer 9 only. It uses the Go Sales (Query) package.

 

searchprompt_fig01
Figure 1 – Prompt Page at run time
searchprompt_fig02
Figure 2 – Filtered report results

 

 

  1. Create a new list report. In the list add: Region, Retailer Country, Retailer Site (from Retailers), Product Line, Product Type, Product (from Products), Revenue, Gross Profit, and Quantity measures.
  2. Add a prompt page. Open it for editing.
  3. Add a table (3 rows, 3 cols).
    1. Row 1, col 1, add a text item to be a label for the first prompt control. Text is “Product Line”.
    2. Row 1, col 2, add a text item to be a label for the second prompt control. Text is “Product Type”.
    3. Row 1, col 3 is a spacer.
    4. Row 2, col 1, add a value prompt. Parameter and name are both “pProdLine”. Don’t create a parameterized filter. Create a new query named “qProdLine”. Use Product Line Code, Display Product Line. Set Multi-select property to “Yes” and List Box. Set width to fit options.
    5. Row 2, col 2, add a value prompt. Parameter and name are both “pProdTyp”. Don’t create a parameterized filter. Create a new query named “qProdTyp”. Use Product Type Code, Display Product Type. Set Multi-select property to “Yes” and List Box. Set width to fit options.
    6. Row 2, col3 is a spacer.
    7. Row 3, cols 1 and 2 are spacers.
    8. Row 3, col 3, add an HTML item. Description “Refresh filters”. Note that I am using an image instead of a button label. Left justify the cell. Code <button onclick=”promptAction(‘reprompt’)” style=”padding: 3px; font-weight: bold; font-size: 10pt; font-family: Arial, Serif; background-color:silver; color:blue; border: 1px solid silver;”><img src=”../samples/images/blue_arrow_right.gif” /></button>
  4. Add another table (2 rows, 4 cols) below the first and name it “tblSrchSlctPrmt”.
    1. Row 1, col 1, add a text item to be a label for the filter list prompt control. Text “Available Products”.
    2. Row 1, col 3, add a text item to be a label for the final list prompt control. Text “Selected Products”.
    3. Row 1, col 4, add a text box prompt (displays counter). Parameter is “pSlctCnt”. Control name is the same. Click Finish to bypass parameterized filter and query dialog. Required: No, Multi-select: No, Hide Adornments: Yes. Name: “pSlctCnt”.
    4. Row 2, col 1, add a value prompt. Parameter and name are both “pAvailable_Products”. Don’t create a parameterized filter. Create new query name “pProducts”. Use Product Number, Display Product. Set Multi-select to “Yes” and List Box. Sort on Product. Set width to fit options.
    5. Right click on the prompt control and Go To Query. Add an optional Detail Filter as ([Sales (query)].[Products].[Product line code] IN (?pProdLine?)) OR ([Sales (query)].[Products].[Product type code] IN (?pProdTyp?)). Return to edit the prompt page.
    6. Row 2, col 2, add a table (2 rows, 1 col) named “tblBtnAddRmv”. Set cell properties to horizontal align as Middle, vertical align as Center.
      1. Row 1, add an HTML item. Description is “Add fields”. Code <button onclick=”iScripts.selectColumns.selectField(‘_Products’)” style=”background-color:white”> <img src=”../ps/portal/images/move_right.gif” /></button>
      2. Row 2, add an HTML item. Description is “Remove fields”. Code
    7. Row 2, col 3, add a value prompt. Parameter and name are both “pSelected_Products”. Click Finish to bypass both the parameterized filter and query dialogs. Set as optional and multi-select list box. Set width to fit options.
    8. Row 2, col 4, add a table (4 rows, 1 col) named “tblBtnMv”. Set cell properties to horizontal align as left, vertical align as middle. Add an HTML item to each row.
      1. Description “Top”. Code <button onclick=”iScripts.selectColumns.moveToTop(‘_Products’)” style=”background-color:white”><img src=”../skins/modern/viewer/images/top.gif”></button>.
      2. Description “Up”. Code <button onclick=”iScripts.selectColumns.moveUp(‘_Products’)” style=”background-color:white”><img src=”../skins/modern/viewer/images/pageup.gif”></button>.
  • Description “Down”. Code <button onclick=”iScripts.selectColumns.moveDown(‘_Products’)” style=”background-color:white”><img src=”../skins/modern/viewer/images/pagedown.gif”></button>.
  1. Description “Bottom”. Code <button onclick=”iScripts.selectColumns.moveToBottom(‘_Products’)” style=”background-color:white” ><img src=”../skins/modern/viewer/images/bottom.gif”></button>.
  1. Add a margin of 20 to the top of the tblSrchSlctPrmt table to provide a separation space from the first table.
  1. Add a text box prompt control after the table tblSrchSlctPrmt. Parameter and name are both “pMergeSelected_Products”. Required: No, Multi-select: No, Visible: No. This is used to manipulate the selected values in the final list. This is needed if the values in the Selected Products control are used to hide/show columns.
  2. Add an HTML item. Description: “Reselect”. Code <script>iScripts.selectColumns.reselectFields(‘_Products’)</script>.
  3. Add a final HTML item to hold the JavaScript logic. See text box. Note box is smaller than content. Select all in box, copy and paste into an editor. Note that the page has to finish loading before the functions here can be referenced.
<script language="javascript">// <![CDATA[
/***** Create and initialise global variables */
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 displayStyle = "";
var noDisplayStyle = "none";
var iScripts = {}; // Create a namespace for the functions

iScripts.isInArray = function(value, array) { for(var j = 0; j < array.length; ++j) { if(array[j] === value) {return true;} } return false; }

iScripts.selectColumns = {}; // Fill the Available<_name> prompt with fields from the package.

iScripts.selectColumns.selectField = function(name)
{
 var available = fW['_oLstChoicesAvailable' + name];
 var selected = fW['_oLstChoicesSelected'+name];
 var selectedCount = 0;
 var maxCount = 100;
 var totalCount = selected.length;
 for(var i = 0; i < available.length; ++i) { if(available.options[i].selected) ++selectedCount; }
 totalCount += selectedCount; // Add the current measure selection count to the total
 for(var i=0; i<selectedCount; ++i)
 {
  if (totalCount <= maxCount) { selected.appendChild(available.options[available.options.selectedIndex]); }
  else
  {
   alert('*W* Maximum number of items exceeded - Please reduce your selection of ' + totalCount  + ' to 100 or less.');
   totalCount -= selectedCount; // Subtract the current measure selection count from the total
   break;
  }
 }
 /***** Update the selected measure total count displayed on prompt page */
 var mCnt = fW._textEditBoxpSlctCnt;
 mCnt.value = totalCount;
}

iScripts.selectColumns.removeField = function(name)
{
 var available = fW['_oLstChoicesAvailable' + name];
 var selected = fW['_oLstChoicesSelected' + name];
 var selectedCount = 0;
 for(var i = 0; i < selected.length; ++i) { if(selected.options[i].selected) ++selectedCount; }
 for(var i = 0; i < selectedCount; ++i) { available.appendChild(selected.options[selected.options.selectedIndex]); }
 // Update the selected measure total count displayed on prompt page
 var mCnt = fW._textEditBoxpSlctCnt;
 mCnt.value = selected.length;
}

/***** Before modifying the use values, join the values into the merged textbox. This allows us to reselect those fields, in that order, on page load. */
iScripts.selectColumns.mergeFields = function(name)
{
 var selected = fW['_oLstChoicesSelected' + name];
 var merged = fW['_textEditBoxMerged' + name];
 var mergedValue = []; // Set up an array to hold values
 for(var i = 0; i < selected.length; ++i) { mergedValue.push(selected.options[i].value); }
 merged.value = mergedValue.join('||');
}

/***** On page reload, select the fields that appear in the merged textbox */
iScripts.selectColumns.reselectFields = function(name)
{
 var selected = fW['_oLstChoicesSelected' + name];
 var merged = fW['_textEditBoxMerged' + name];
 var available = fW['_oLstChoicesAvailable' + name];
 var mergedArr = merged.value.split('||');
 for(var i = 0; i < mergedArr.length; ++i) { for(var j = 0; j < available.length; ++j) { if(available.options[j].value === mergedArr[i]){selected.appendChild(available.options[j])} } }
}

iScripts.selectColumns.moveToTop = function(name)
{
 var selected = fW['_oLstChoicesSelected' + name];
 var j = 0;
 for(var i = 0; i < selected.length; ++i) { if(selected.options[i].selected) {selected.insertBefore(selected.options[i],selected.options[0+j]); ++j} }
 selected.style.display = noDisplayStyle;
 selected.style.display = displayStyle;
}

iScripts.selectColumns.moveUp = function(name)
{
 var selected = fW['_oLstChoicesSelected' + name];
 for(var i = 1; i < selected.length; ++i) { if(selected.options[i].selected &#038;! selected.options[i-1].selected) { selected.insertBefore(selected.options[i],selected.options[i-1]); } }  selected.style.display = noDisplayStyle;  selected.style.display = displayStyle; } iScripts.selectColumns.moveDown = function(name) {  var selected = fW['_oLstChoicesSelected' + name];  for(var i = selected.length-2; i >= 0; --i) { if(selected.options[i].selected &#038;! selected.options[i+1].selected) { selected.insertBefore(selected.options[i],selected.options[i+2]); } }
 selected.style.display = noDisplayStyle;
 selected.style.display = displayStyle;
}

iScripts.selectColumns.moveToBottom = function(name)
{
 var selected = fW['_oLstChoicesSelected' + name];
 var j = 0;
 for(var i = 0; i < selected.length; ++i) { if(selected.options[i-j].selected) { selected.appendChild(selected.options[i-j]); ++j; } }
 selected.style.display = noDisplayStyle;
 selected.style.display = displayStyle;
}

/* After the fields have been merged to the text box, it is time to prepare them. The
 * report will search for the fields by looking for a number surrounded by pipes: |1| or |2|
 * This function will loop through and modify the use value to incorporate the pipes.
 */
iScripts.selectColumns.prepUseValues = function(name)
{
 var selected = fW['_oLstChoicesSelected' + name];
 for(var i = 0; i < selected.length; ++i)   
 {   selected.options[i].value += '|'+i+'|';   selected.options[i].selected = true;  } 
} // ]]>

 

  1. Edit the query Query1. Add a filter as [Sales (query)].[Products].[Product number] IN (?Selected_Products?).
  2. Test the results by running the report.

At this point your report should look something like the below.

searchprompt_fig03     searchprompt_fig04
Figure 3 – Prompt page layout     Figure 4 – List of queries

 

searchprompt_fig05

Figure 5 – Query1 definition with filter

 

searchprompt_fig06

Figure 6 – qProducts definition with optional filter

   
Figure 7 – qProdLine      Figure 8 – qProdTyp

 

 

 

 

 

The example above is designed to filter report rows (products). This technique can also be used to filter columns. This is described in “How to Include Dynamic Measure Columns Using a Custom S&S Prompt in Report Studio”.

 

 

Click here for Report Studio report specifications for this report


Hide/Show Columns in a List Using a Multi-Select Prompt

Business Problem:

The business requirements state that users need to select from a large list of attribute columns in a report to display on a list report. The maximum number of columns is known but due to the variety of users it is not known until run time which attribute columns a particular wants to see in the final results. The users want a multi-select Check Box prompt from which they can select any or all attributes to display in the report. They is a default set of attributes that will display if the user does nothing with the prompt.

Solution:

Using a multi-select check box prompt control each attribute column is be displayed to the user when the report runs. Some check boxes are already selected based on business requirements. Any column check box selected will be display on the report results. The displayed attribute columns together identify the fact grain and the measure columns are automatically aggregated to this grain.

 

The column values are determined by the user selection. If a column is not selected the data item expression will result in no value (i.e. NULL). This is required to ensure the correct fact measure rollup.

 

Render Boolean variables are used, one for each column, to control if the column is displayed or not. Render variables are applied to the whole list column rather than the header and body separately. This reduces the number of places where the control is set. Render variables in this case will work the same as if you used a Style Variable and set the No condition on the list column but require less effort to implement.

 

The solution is based on the Go Sales (Query) package.

Figure 1 – prompt control for selecting which columns to display on the report results.

 

  1. Create a list report in Report Studio. Add the following items to the list:
    1. [Sales (query)].[Sales staff].[Sales region], [Sales (query)].[Sales staff].[Sales region code]
    2. [Sales (query)].[Sales staff].[Country], [Sales (query)].[Sales staff].[Country code]
    3. [Sales (query)].[Sales staff].[City] (as Branch Name), [Sales (query)].[Sales staff].[Branch code]
    4. [Sales (query)].[Sales staff].[Manager], [Sales (query)].[Sales staff].[Manager code]
    5. [Sales (query)].[Sales staff].[Staff name], [Sales (query)].[Sales staff].[Sales staff code]
    6. [Sales (query)].[Retailers].[Retailer], [Sales (query)].[Retailers].[Retailer code]
    7. [Sales (query)].[Order method].[Order method type], [Sales (query)].[Order method].[Order method code]
    8. [Sales (query)].[Products].[Product line], [Sales (query)].[Products].[Product line code]
    9. [Sales (query)].[Products].[Product type], [Sales (query)].[Products].[Product type code]
    10. [Sales (query)].[Products].[Product], [Sales (query)].[Products].[Product number]
    11. [Sales (query)].[Sales].[Revenue], [Sales (query)].[Sales].[Quantity]
  2. Create a prompt page and open it for editing.
  3. Add a table to the page with 2 rows and 2 columns.
  4. Merge the cells in row 1.
  5. Add a Text Item to row 1. Text: “Attributes to Display”. Set the horizontal alignment as Center.
  6. Add a value prompt in row 2, cell 1. Parameter: “pDispAttIds”. Click Finish to bypass the filter and query dialogs. Set the properties of the prompt control as:
    1. Required: No
    2. Multi-select: Yes
    3. Select UI: Check box group
    4. Width: 135px
    5. Height: 170px
    6. Name: “pDispAttIds”
    7. Results Select All Text: blank
    8. Results Deselect All Text: blank
    9. Static Choices:
      1. Use: “a”, Display “Region Code”
      2. Use: “b”, Display “Country Code”
  • Use: “c”, Display “Branch Code”
  1. Use: “d”, Display “Manager Id”
  2. Use: “e”, Display “Sales Rep Id”
  3. Use: “f”, Display “Retailer Id”
  • Use: “g”, Display “Order Method Code”
  • Use: “h”, Display “Product Line Code”
  1. Use: “i”, Display “Product Type Code”
  2. Use: “j”, Display “Product Number”
  1. Default values: a, c, f (each is a separate entry)
  1. Copy the above prompt into row 2, cell 2. Change the properties as below:
    1. Parameter: “pDispAttDescs”
    2. Name: “pDispAttDescs”
    3. Static Choices:
      1. Use: “a”, Display “Region Name”
      2. Use: “b”, Display “Country Name”
  • Use: “c”, Display “Branch Name”
  1. Use: “d”, Display “Manager Name”
  2. Use: “e”, Display “Sales Rep Name”
  3. Use: “f”, Display “Retailer Name”
  • Use: “g”, Display “Order Method”
  • Use: “h”, Display “Product Line”
  1. Use: “i”, Display “Product Type”
  2. Use: “j”, Display “Product Name”
  1. Default values: a, e, h (each is a separate entry)
  1. For both cells in row 2 set the padding property with 5px on each side. The result should look like:
  2. Create a set of Boolean variables, one for each attribute column.
    1. vDispRgnCd – ParamValue(‘pDisplAttIds’) CONTAINS ‘a’
    2. vDispRgnNm – ParamValue(‘pDisplAttDescs’) CONTAINS ‘a’
    3. vDispCntryCd – ParamValue(‘pDisplAttIds’) CONTAINS ‘b’
    4. vDispCntryNm – ParamValue(‘pDisplAttDescs’) CONTAINS ‘b’
    5. vDispBrnchCd – ParamValue(‘pDisplAttIds’) CONTAINS ‘c’
    6. vDispBrnchNm – ParamValue(‘pDisplAttDescs’) CONTAINS ‘c’
    7. vDispMgrId – ParamValue(‘pDisplAttIds’) CONTAINS ‘d’
    8. vDispMgrNm – ParamValue(‘pDisplAttDescs’) CONTAINS ‘d’
    9. vDispSlsRepsId – ParamValue(‘pDisplAttIds’) CONTAINS ‘e’
    10. vDispSlsRepNm – ParamValue(‘pDisplAttDescs’) CONTAINS ‘e’
    11. vDispRtlrId – ParamValue(‘pDisplAttIds’) CONTAINS ‘f’
    12. vDispRtlrNm – ParamValue(‘pDisplAttDescs’) CONTAINS ‘f’
    13. vDispOdrMthCd – ParamValue(‘pDisplAttIds’) CONTAINS ‘g’
    14. vDispOdrMth – ParamValue(‘pDisplAttDescs’) CONTAINS ‘g’
    15. vDispPrdctLineCd – ParamValue(‘pDisplAttIds’) CONTAINS ‘h’
    16. vDispPrdctLine – ParamValue(‘pDisplAttDescs’) CONTAINS ‘h’
    17. vDispPrdctTypCd – ParamValue(‘pDisplAttIds’) CONTAINS ‘i’
    18. vDispPrdctTyp – ParamValue(‘pDisplAttDescs’) CONTAINS ‘i’
    19. vDispPrdctNbr – ParamValue(‘pDisplAttIds’) CONTAINS ‘j’
    20. vDispPrdctNm – ParamValue(‘pDisplAttDescs’) CONTAINS ‘j’
  3. Now edit the list. For each column body in turn, select the select then using the Ancestor button go up a level to select List Column. In the properties add a Render Variable. From the list select the variable that matches the column.
  4. The Data Item Expression for each attribute column must be modified so that if the column is not selected for display then the value is NULL. This ensures that the measure value totals will be correct no matter which columns are display.
  5. Test the results by running the report.


Figure 2 – Default prompt selection

Figure 3 – Results with default prompt selection

 

Figure 4 – changed prompt value selection


Figure 5 – Results with changed selections

Click here for Report Studio Report Specification

 

 


Cognos Active Reports

By Itzik Maoz, Director of Business Analytics Practice for Creative Computing, Inc.

One of the most rewarding aspects of teaching students about Cognos Business Intelligence is to witness their enthusiasm when they grasp the software’s possibilities and power. They don’t need to be IT professionals to learn how to extract vital information, analyze it and create meaningful reports.

I’m especially excited about Cognos’ ability to produce active reports. This unique format lets you create dashboards for a comprehensive view of multiple aspects of your vital information, and you can see it on your tablet, Android smart phone or computer. You can also run active reports in a variety of browsers. You get information right when you need it, even if you are not connected to the Internet or your network, so that you – or your customers – can make informed decisions.

With Cognos’ active report feature, the information is presented in a very dynamic way. The active reports let you use a variety of interactive prompts and charts, including bubble charts, trend Chart, etc. You can view different types of information, such as sales by region or by product, on one dashboard. There are so many options; it’s easy to customize the reports.

You can also quickly process information in an extremely visual way, especially if you’re using IBM’s Rapidly Adaptive Visualization Engine (RAVE), which makes the visualizations interactive and animated. It’s not just a static report. It visually changes, moves and shifts on command. For example, you can visually observe how your different product lines shift as you slide the time slider with your finger on your iPad.

Create and Interact
In a LearnQuest class, we talk about how to create active reports and how to interact with them. It’s a combination of demonstrations and hands-on work. We discuss the features and the concepts. Students practice using sample data – typically sales data because it’s easy to understand.

Before taking any Cognos class, it helps tremendously if you review the prerequisites. You’ll simply get more out of the class, if everyone enters at the same level.

I’m a trainer in these sessions, but I can also share my knowledge as a consultant and a frequent user. I help people understand how the software relates to the student’s industry or their job. Cognos is used in all industries and at all organization levels. My students are from a variety of industries, with the majority of them from the Finance and Sales departments.

Sharing my knowledge is part of my personality. Showing people how to get the most out of this great technology so they can grow their company gives me a lot of satisfaction.

About the Author
Itzik Maoz is Director of Business Analytics Practice for Creative Computing and has more than 15 years of Business Analytics experience. Based in Providence, R.I., he has worked with many of New England’s most recognizable companies in Manufacturing, Retail, Healthcare and Higher Education. He is proficient in the entire IBM Business Analytics stack and is well versed in the entire Business Analytics project lifecycle.


Smart Shortcuts

By David Pacific, Education Services Practice Lead, Creative Computing, Inc.

Who among us doesn’t want to save time? Who among us wouldn’t want to know that there are shortcuts in the system they’re already using, shortcuts that could dramatically simplify their lives and level of effort?

I’ve participated in countless Cognos Report Studio projects and often find that the client is asking for help to do what I like to call “monkey work” — changing report titles, headers and footers, logos or simple text elements. Most of these clients are unaware of a very powerful concept in the Object Library, which is why I think it’s imperative to discuss this capability here and share this knowledge with the masses. This can save days of editing and maintenance when tedious template or layout changes are required.

With Layout Component References, Report Studio allows you to reuse objects from other reports. You can use this “library” — actually a report itself — to store commonly used objects, such as headers, footers or a complex report template for a specific set of tables containing commonly used report elements. This allows standards to be utilized across an enterprise so there is no longer a question of what logos, color schemes, layout calculations or prompt tables should appear or function.

I have personally used and implemented an Object Library on many of my projects to store all of the prompt tables that would be used on the design of multiple reports. I have found report development to be very quick and successful in creating a “self-service” template that has all of the possible permutations for commonly used report objects. Instead of giving developers the task of creating new report content from scratch, they have a contained template utilizing Layout Component References and can simply remove components they do not care about.

What’s more, with an Object Library you can make a change to an element that’s referenced multiple times, and the change will be propagated across all locations without even having to open any of the reports that contain the reference. Rather than opening and editing hundreds of reports to repeatedly change text or a header, you can make that change once, and it will appear in every subsequent report that includes the reference. This creates consistency across all reports, which is always crucial for successful report implementation. If you need to eliminate a referenced object from a new report, you can always override it, while still keeping the object in your library for later use.

It sounds so obvious, but once leveraged, this feature can save you hours and hours — and possibly even weeks — of development and maintenance time. After all, there’s no need to constantly reinvent the wheel when there’s content you can simply reuse, maintain and update once. Put this best practice in place and you’ll see the immediate benefit and value added from a concept that all Report Studio developers should be utilizing.

About the Author
As Creative Computing’s Education Services Practice Lead, David Pacific oversees all training engagements and participates in analysis and requirement gathering for all potential training opportunities. In this respect, he coordinates to identify training needs and provides a fit for scheduling and courseware. He also has a combination of onsite development skills, so when he is not overseeing or delivering trainings he is working on implementation for a multitude of Fortune 500 clients. This has helped him develop an excellent mix of skills sets between the classroom and real world development work, which has proven to be a valuable asset as a trainer.


Using Prompt Token Syntax to Create Efficient Reports

Using Prompt Token Technique to Create Efficient Reports
Helpful Technical Tips for Cognos 8 Users

Using Prompt Token Technique to Create Efficient Reports

Users appreciate flexible reports, and so we provide them with all sorts of prompts and calculations.  A frequent request is to allow them to choose which data to include, or to group or sort their report in various ways.  Or even decide which columns should be included in a report.  If you used to be an Impromptu Report writer, you might have done this using IF-Then-Else calculations to determine the sort value based on the users selection.

Let’s look at several report outputs:

Revenue by Manager

Revenue by Order Method

Revenue by Product Line

One of the great features of Report Studio is that even if there are a lot of items in a query, only the items that are actually included in a report are retrieved from the database (or cube).  It would be terrific if when a user chose to report on Manager, Order Method or Product Line, only the required field would be included in the query.

In Cognos 8, the prompt/token syntax can be used to do this in a very efficient way.  Most prompts return a data value – Acme Inc., 9/15/2008, etc.  These are used to compare in calculations.  A prompt token doesn’t return data, it returns a report “object” – for instance a query column.  When the user makes a choice from a list of token options, only the one they choose is included.  That way, if the different values are coming from different tables, only the required tables are touched.

Let’s look at the query items for the above reports:

The first five items are pulled directly from the GO Data Warehouse (query) package.  Product line, Order method and Level 2 manager are not included in the list report.  Only the calculated column Dimension Column is included:

And here’s the magic – the prompt/token syntax:

This is the syntax in its simplest form.  In this case, dim_column refers to a parameter that I’m making up.  It could be called anything.  Including it in the prompt syntax automatically creates the parameter.  The word token, in single quotes, is a reserved word – it must appear exactly as shown.  This tells report studio to substitute the object that the prompt returns.  In our example, we have included several dimension columns in the query – Order method, Level 2 manager and Product line.  To include them as objects, the values returned must be surrounded with square brackets – just as they would look if they were included as part of a calculation.  So, when prompted for a dim_column, if I type in [Product line] (exactly matching the query item – note lower case ell), then this report is produced:

Note that the column header says Dimension column – we’ll fix that in a moment.  First, to make the report friendlier for the user, a prompt should be defined – this could be on the report page or prompt page.  A value prompt will be created, with static choices.

Once the value prompt is dragged onto the page header, you are asked if you want to use an existing parameter.  I chose dim_column.

Then I just hit the Finish button to create the prompt.  The next step is to create static choices (these could be pulled from a query also, but they must be in the correct format).  They should like like this:


Note the use of the square brackets in the Use column.  This refers to items that we’ve already added to the query.  It would also be acceptable to put in fully qualified items from the package, e.g. [Sales (query)].[Product].[Product line].   If that approach is used, the items don’t even need to be added to the query – although I find it simpler and more transparent to have the items included in the query.

It’s also wise to add a default selection.  This can be done in the prompt syntax, but here it’s being done in the Report Studio GUI:

Finally, set the prompt type to “auto-submit”.  With that setting, as soon as the user changes the value, the report is rerun.  That’s especially helpful if the prompt is on the report page.  The report now looks like this:

Finally, let’s change the column header/title.  To do that, you must unlock the report using the lock/unlock icon .

Once it’s unlocked, you can simply change the heading from a Data Label to a  Report Expression.  The expression is simply the ParamDisplayValue – the display values that were used in the static items definition:  ParamDisplayValue (‘dim_column’).  This is the same name assigned in the static choices for our value prompt.

Now, when the report runs, It looks like this:

Note that the header shows Level 2 Manager – the same as the prompt value.  It will change dynamically when a different value is chosen.

A similar method can be used for sorting a report.  Our report query will be the same as the original report, including the Dimension Column.  The difference is that all of the columns will be included on the list report.

The report looks like this:

We’ll make two changes to the list report – forcing a sort on the Dimension Column, and hiding that column.  Both of these are done in the normal Report Studio GUI.  When you set the boxtype to none for the column, be sure to do the same thing for the column title.

When this report is run, the list will be sorted by whichever column the user chooses:

Voila!!!

There are other ways to experiment with this syntax too.  The full syntax is:

#prompt(‘parameter_name’,’token’,’defaultvalue’,’prefix’,’’,’suffix’)#

For instance, #prompt(‘adv_dimension’,’token’,’Product line’,'[‘,”,’]’)#.  By adding a prefix and suffix, we could skip the opening and closing square brackets in the prompt values – this might allow them to serve as filters also – Product line instead of [Product line].  I’ve used suffixes to employ one prompt to drive several calculated columns.  For instance, rather than simply showing Product line for Product, let’s show Product line and Product type.  For Managers, we’ll also show two levels, Level 2 Manager and Level 3 Manager.

In this case, we’ll add items to the query, and name them predictably – e.g. Product-1, Product-2 and Manager-1 and Manager-2.  The static items in the prompt are just ‘Product’ and ‘Manager’.  The prompt syntax is:

#prompt(‘dim_column’,’token’,’[Product-1]’,’[‘,’’,’-1]’)#

Here, when the prompt returns Product, with the prefix and suffix added on, it becomes ‘[Product-1]’.  We’ll add two of these calculated prompt columns, the second one is identical, but it points to the next level:

#prompt(‘dim_column’,’token’,’[Product-2]’,’[‘,’’,’-2]’)#

Which will point to ‘[Product -2]’

Here’s how the static choices look:

Since we’ve defined a default prompt value, it doesn’t have to be done in the Report Studio prompt GUI, but if you prefer to do it there, don’t forget to change the default selection in the GUI to Product.

Here’s the query:

Finally, here’s the report – note that the column headers haven’t been addressed yet….

Hopefully this gives you some idea of the power of this approach.
Good luck!

Josh Lobel
Creative Computing, Inc.
401-727-2400
www.mycci.com