By Craig Harding, Consultant for PerformanceG2
 

Better performance will be realized by using parameters and prompts within Model Query Subjects, filter objects or stand alone calculations. This will allow for better SQL generation than inserting them within the SQL of a Datasource Query Subject.

These recommendations lead to better SQL generation, which in turn, leads to better performance.

Nearly any prompting can be done in a Model Query Subject:
[Product dimension].[Product line].[PRODUCT_LINE_CODE] =
#prompt(‘Product Line Code’, ‘integer’, ‘1’)#

Same with Calculations:
Case [Product dimension].[Product line].[PRODUCT_LINE_CODE]
When #prompt(‘Target Product Line Code’, ‘integer’, ‘1’)# then 1
Else 0
End

The added benefit of placing prompts in a stand alone calculation is that if the report author does not use the calculation, then the prompt is no longer needed. There is no need to add this case statement to the Base SQL.

When using calculations and filter objects in Model Query Subjects or as stand alone objects, the Datasource Query Subjects can be left untouched. This means the SQL remains minimized and eliminates the need to fetch metadata.

 
 
Contact the Blogger

 
By Craig Harding, Consultant for PerformanceG2
 

To try this, build a real simple list report and add staff name and revenue.

Add a value prompt to the report page. Name the parameter sort, and click ‘finish’. Add two static choices through the properties pane of the value prompt. For the first static choice, display value will be ascending with a use value of 1. For the second static choice, set the display value to descending, and the use value to -1.
 

 

You must add a default selection in order to avoid being prompted on report load. This can be done through the properties of the prompt. I put a value of 1 in the default selections for ascending.

Also, set the auto submit property to yes.

Add a data item to the query. The expression should read [Revenue]*?sort? (no need to type this, drag and drop from the insert-able objects pane). Name the data item sortkey and set the presort property to ascending.

Return to page one and select the list. The properties property of the list must have the sortkey checked.
 

 
Run the report to test.
 

 

 
 
Contact the Blogger

 
By Craig Harding, Consultant for PerformanceG2
 
 
Slow rendering portal pages?

If you are having trouble getting your portal pages to render in a decent amount of time here is a little trick that can greatly improve performance.

When rendering a report in Cognos viewer portlet, in a portal page, the hour glass may just spin and spin. If the report runs fine on its own and is only slow in a portal page, then try this: instead of using a Cognos viewer portlet to render the report, use an HTML viewer portlet instead. I have used this technique for a dramatic increase in performance with multiple clients.

Stay tuned for more “tricks of the day” blog posts through out the next couple of months!

 
 
Contact the Blogger

 
By Craig Harding, Consultant for PerformanceG2
 
 
Conditional Blocks

The goal of this report is to prompt users on which display they would prefer, a list or a chart.

To start, create a simple list report in Report Studio. Using the Cognos sample data, product line, order method and revenue are added to the list. From the toolbox, drag and place a pie chart below the list. Select the chart and in the properties pane, attach this chart to query 1:
 

 
 
Now the chart can be populated from the data items tab.
 

 
 
The consumers of this report need the ability to choose between the displays they want to see. To accomplish this, build a blank prompt page allowing them to select from a drop down list either list or chart. Drag a value prompt to the prompt page with the following settings:
 

 
At this point hit ‘Finish”. The dropdown list has to be populated manually though the static choices property of the value prompt. The two choices added are list and chart.
 

 

 
*You can leave the display value blank. If you do so, Report Studio is smart enough that it will take the use value for the display value.

Now that my prompt page is set, return to the Report Page. In order to conditionally display an object, use the conditional blocks object in the toolbox. Drag this to the bottom of the report.
 

 
 
The next step is to attach the variable to the conditional blocks block variable property. This variable is a Boolean variable that either equals a list or doesn’t.
 

 

 

 
 
We need to test if our parameter is equal to the value ‘List’. If the user selected List this expression will evaluate to true, if the user selected ‘Chart’ from the prompt, then this will evaluate to false.

After this step is completed there is a new property on the conditional blocks object that tell us which block we are currently looking at. First, drag the chart into the ‘No’ block.
 

 

 
Now, select the conditional block again, and switch the current block property to ‘Yes’. The conditional blocks object appears empty because there is currently nothing in the ‘Yes’ block.
 

 
The final step is to drag the entire list inside the ‘Yes’ block.
 

 
Now when the report is run, the user will be prompted for their display of choice.
 
 
Contact the Blogger

By Craig Harding

In Cognos 8, you now have the ability to create global filters and shared drill behavior from a dashboard in Cognos Connection. I have put together a video with step by step instructions on how to do this. You can find it at http://www.youtube.com/watch?v=K7ntBdPvtT0 .

Contact the Blogger

By Craig Harding

Slicers – Slicer member set is a set expression that returns members from a single hierarchy of a single dimension.

Since Cognos does not recommend using relational style filters against OLAP data sources, I decided to show you a few techniques for working with Slicers. You can think of Slicers as one of the dimensional techniques to filter your data (the filter () function being another).

A simple unfiltered report:

What I want to do now is have this report only return data for the Order Methods Email and Web. Since this is an OLAP data source I am going to use a slicer. From query explorer, simply select both the email and web members and drag to the slicer pane.

Report Studio automatically makes this a set. Since only data is returned for Email and Web orders now, the results returned look like (much lower revenue amounts):

That was a little too easy so we can make it more dynamic with a prompt. Here is the syntax for a single select prompt:

 [Great Outdoor Company].[Order Method].[Order Method].[Order Method1] -> ?om?

For a multi select prompt wrap the whole expression in the set() function:

Set([Great Outdoor Company].[Order Method].[Order Method].[Order Method1] -> ?om?)

If you are working with very large amounts of data, you might want all information returned except for a couple of specific selections. You can accomplish this using the except () function. The way it works is that it will return all of the first argument, except what is returned by the second argument.

In this case all order methods would be returned, except for those chosen in the prompt.

m2awnv34ed

Craig Harding

First of all, scripts may break when upgrading versions. There are new objects available to use and some taken away with each release. I will be talking about Cognos 8.3 for the purposes of this blog.

I want to show you a basic script that can be very useful. The idea for this came from a client who asked me to solve a problem. They had a report that had 2 optional prompts. The problem was some users were not making use of either. This was resulting in very large result sets being returned.

What we wanted to accomplish was requiring the user to choose a value from either prompt. It did not matter which one.  To accomplish this I put two list prompts on a prompt page. One was called om (I am making use of the provided sample data) for order method and the other was called pl for product line. An html item was inserted near the top of the prompt page which includes my function.

<script type=”text/javascript”>

function checkNumSelected()

{

// The following 4 lines of code are needed in Cognos 8.3

var fW = (typeof getFormWarpRequest == “function” ?

getFormWarpRequest() : document.forms["formWarpRequest"]);

// if it’s undefined, check if it is running in a portlet.

if ( !fW || fW == undefined) { fW = ( formWarpRequest_THIS_ ?

formWarpRequest_THIS_ : formWarpRequest_NS_ );}

//setting up my variables

       var countom=0;

       var countpl=0;

       var omcount = fW._oLstChoicesom;

       var plcount=fW._oLstChoicespl;
       // count how many choices were selected in the Order method prompt
       for (var i=0; i < omcount.length; i++)
       {
             if (omcount.options[i].selected)
                  countom = countom +1 ;
       }      
      // count how many choices were selected in the Product Line prompt
      for (var i=0; i < plcount.length; i++)
       {
             if (plcount.options[i].selected)
                  countpl = countpl +1 ;
       }    

     // If at least one option was selected in wither prompt call the promptbuttonfinish fuction
    // If nothing was selected throw up an alert box with directions
         if (countom >=1 || countpl>=1) {
          promptButtonFinish();
     }
     else if (countom == 0 && countpl==0) {
          alert(“You have not selected a value!\n Select a value in one of the prompts.”);
   }

}

</script>

In order to call this function I deleted the finish button on the prompt page and created my own through an HTML item with the following code

//<input type=”button” OnClick=”checkNumSelected()” value=”Finish”

Contact the Blogger