Prompt Token

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 these steps gives you some Ideas.
Good luck!