Prompts Archives - Page 4 of 4

Macros allow you to alter SQL/MDX before it’s send to the datasource. Using the macro functions timestampmask, you can easily select specific members, or query certain tables.

Prompt macros give you an extremely fine grain of control. You can, for instance, allow a user to which measure he wants to see in his graph, or create dynamic default values.

To begin with, the structure of the prompt macro:

#prompt/promptmany(‘parameterName’,’dataType’,’defaultValue’,’leadingText’,’source’,’trailingText’)#

parameterName is self explanatory. Note that it is case sensitive. If you want to pass a value to that parameter via the URL or POST you need to preface it with a p_ so Par_Year becomes p_Par_Year.

The dataType will reject anything not covered by the datatype. The string ‘Hello World!’ would be rejected if the datatype was set to integer. The datatype also determines the type of prompt generated. ‘Integer’, for example, will always create a text box prompt even if the source field (see below) is filled.

The defaultValue will be returned if no parameter is selected. This does not need to obey the dataType. For example, you could have a filter set up: [Year] = #prompt(‘parYear’,’integer’,’year(getDate())’)# If 2010 was selected the filter would be [Year] = 2010, if no value was returned to the prompt the filter would be [Year] = year(getDate()).

The leadingText is text that is automatically entered before the selected parameter. For example a filter: #prompt(‘parYear’,’integer’,’1=1′,'[Year] = ‘)# In that example if 2010 was selected [Year] = 2010 would be returned to the filter. If nothing was selected, the filter would be 1=1.

The source will a list or tree prompt based on a referenced field. #prompt(‘timeMember’,’memberuniquename’,”,”,'[NS].[TimeDimension].[TimeHierarchy]’)# will create a tree prompt that will allow the user to select any member from the TimeHierarchy. #prompt(‘timeMember’,’memberuniquename’,”,”,'[NS].[TimeDimension].[TimeHierarchy].[MonthLevel]’)# will create a list prompt that will allow the user to select a single member from the MonthLevel. You can also include functions in the source: #prompt(‘timeMember’,’memberuniquename’,”,”,’filter([NS].[TimeDimension].[TimeHierarchy].[MonthLevel],[Measures].[Sales]>1000)’)# will create a list prompt of all months that had more than 1000 sales.

The trailingText, much like the leadingText, placing text after any values selected. #promptmany(‘Years’,’memberuniquename’,’children([NS].[TimeDimension].[TimeHierarchy]->[All Member])’,’set(‘,'[NS].[TimeDimension].[TimeHierarchy].[YearLevel]’,’)’)# will either return the children of the All member (if no values are selected), or will return a set of members that the user selected.

One of the requirements that I have often encountered is a dynamic year prompt. The user would like a list of the past n years, defaulting to a specific one (the previous year for instance). Cognos does not have a built-in way of dynamically setting a default value on a prompt. There is a way around this though.

To start go to the query that needs to be filtered. In this example I’ll be using a relational source.

The filter line should be

[ns].[Time].[Year] = #prompt('Year','integer','year(getDate))-1')#

The prompt macro will automatically insert the default year into the filter, in this case the current year – 1. Note that the year(getDate()) is T-SQL, PL/SQL users should using something like to_char(add_months(sysdate, -12),’YYYY’).

If there is no value returned the parameter on runtime the filter will default to the previous year. This works well, but the user wants the year selected on the value prompt.

Add some fields to the report page to test it.

For that we’ll need the prompt on the page. Create the value prompt, give it the parameter name Year, and click finish. Set the name of the prompt to Year. Set Auto-Submit to Yes. Add static choices 1 – 5.

The next step is to change the numbers into years, and to automatically select the previous year.

Add an HTML item to the right of the prompt:

<script>
var form = getFormWarpRequest();
var YearList = form._oLstChoicesYear;
var i = 0;
d=new Date();
year = d.getYear()
while (i <= 4)
{
YearList.options[i+2].text=year - i;
YearList.options[i+2].value=year - i;
i++;}
YearList.remove(0);
YearList.remove(0);
YearList.options[1].selected=true;
YearList.removeAttribute("hasLabel");
</script>

Note that the while statement loops 5 times. If you want more than 5 options increase the number of loops accordingly.

If it works the report will look like: It works except for one small issue. When the user selects another year, the report is correct but the Javascript forces the year in the prompt back to the default selection!

The best way to solve this is to set the HTML Source Variable with a Boolean variable.

Use the report expression

ParamValue('Year') is not null

Select the new variable from the Condition Explorer and click on the HTML Item. If it is not already, set the HTML Source Variable to the variable that was just created. Set the Source Type to Report Expression and paste in the following code:

'<script>
var form = getFormWarpRequest();
var YearList = form._oLstChoicesYear;
var i = 0;
d=new Date();
year = d.getYear()
while (i <= 4)
{
YearList.options[i+2].text=year - i;
YearList.options[i+2].value=year - i;
i++;}
YearList.remove(0);
YearList.remove(0);
YearList.removeAttribute("hasLabel");
YearList.options[' + number2string(extract("year",date2timestamp  (Today()))-string2int32(ParamValue('Year'))) + '].selected = true;
</script> '

Now when no year is selected it will default to the previous year, and select the second value in the prompt. When a year is selected, it will find the number of years between that year and current year (extract(“year”,date2timestamp (Today()))-string2int32(ParamValue(‘Year’))), and will select the year at that index. Some locales use a semi-colon instead of a comma.

CognosPaul

Category: Prompts

Macros (3 of n++): Prompts

Dynamic Year prompt