Cognos Analytics 12 - Dynamic Prompt Parameters Part II
Abstract
This article will continue the discussion of the new Parameter setup function new to Cognos Analytics version 12. The previous blog on this subject introduced the new Parameters view and the basics of setting up a parameter and assigning a default value. This article will explore leveraging this new Parameters view/function to provide default parameter values in Scheduled reports and other more advanced techniques.
Note: The Parameters view has a couple subtle changes going from 12.0.0 to 12.0.1:
The “Found” tab in 12.0.0 is now named “All Parameters” in 12.0.1… function appears to be the same between the 2 versions.
Parameter definitions didn’t work in background/scheduled executions in 12.0.0, but this functionality seems to be working as intended in 12.0.1.
Assumptions:
User has read the previous blog on this topic.
User has Access/Capabilities to the report editor in Cognos Analytics.
User has Access to / knowledge of a data source for authoring… relational preferred for this topic.
User has basic knowledge of authoring in Cognos Analytics reporting.
Relative Date Prompt Default Values for Scheduling:
A common requirement in many, if not all, business analytics projects is the ability to schedule reports and for those runs to use date parameters that run the report relative to the current date or month. Without the new Parameter setup functionality introduced in Cognos Analytics version 12, this required some advanced/complex authoring techniques. With this new functionality, the task is now more in line with an intermediate task. Let’s work on an example:
1. Create a simple report that has a filter on a date. I will be using the relational GO Sales (query) package found in the Cognos Analytics samples for the past couple of decades for this example and will be leveraging a range of dates… using “between” 2 separate parameters or “in_range” with 1 parameter will both function as desired. My example is a list showing revenue by Product line for each date in the date range. The requirement is that using the current date, have the report run for the range First of the current month to yesterday.
Note: Since GO Sales dates are from 2010 to 2013, I have altered them in this example to be in the range of the current date, Dec 19, 2023… here is the calculation I am using to do this:
cast(_add_months (_add_years ([Sales (query)].[Time].[Date],14),-1),date) Simple list with no constraint on date yet:
2. Add the date filter… I will use in_range operator, but between 2 separate parameters could also be used.
[Date] in_range ?DateRange?3. Add a prompt page and put a date prompt on the page, using the “DateRange” parameter, as defined in the filter above.
4. The date prompt will default to today for both the From and To… we will attend to this shortly.
Run the report and select Dec 16, 2023, for both From and To and you should see 1 grouping of 4 rows:
5. Next, we will add parameter defaults in the Parameters view… click on the Parameters view in the report hierarchy navigation:
6. In the Parameters view, the default tab viewed will be All Parameters… you should see the Parameter defined in the filter and prompt, “DateRange”.
7. Click the Default values tab, which should show nothing at this point. Click the + icon in the upper right-hand corner of the Parameters list on the Default Values tab and choose Date parameter when prompted.
8. You will then see a dialog to either create a new parameter, or to choose an existing parameter… click on the entry for “DateRange” under report parameters since it is already defined in the report, and click OK.
9. There will now be 2 sections… left side showing the parameters and the right side showing the default value(s) for the parameter selected in the left side. Select “DateRange” parameter.
10. Click the + icon under the right-side pane, and then choose Range value when prompted, as the parameter “DateRange” is indeed a range of values.
11. You will be presented with a dialog which is used to set up either or both Minimum and Maximum defaults. By default, they will both be unselected. Since we have a requirement to default both, click them both to enable them.
12. Click the icon at the right of the minimum value and a new dialog box will appear.
a. There are a few ways to set the default, including using a query.
b. For our example, we don’t need to set a query so leave that set to (none).
c. Under the value section, change Source type from text to Report expression.
d. Click the icon at the right of the Report expression box to define the report expression.
e. The familiar Report expression dialog will appear… enter the following code to return the first of the month of today, using the ReportDate() function:
_first_of_month (ReportDate ())f. Click OK to finish defining the minimum value.
13. Repeat step 12 for Maximum value default.
a. Use this expression for the maximum value:
_add_days (ReportDate (),-1)14. Your default value entry will now show on the right-side:
15. Save the report and close the editing window, so you are back at Cognos Analytics home / content screen.
16. From the Cognos Analytics main page, navigate to the location where your report resides and click it to run it interactively to test it. You should see the 1st of the current month and yesterday preselected.
Today is Dec 19, 2023… I am seeing the desired default range now, and the expected dates returned:
17. Now that we have our report ready, we can create a schedule to run it daily, with the “To” date automatically adjusting each run based on the current date, as defined in our report’s Parameter view.
18. In Cognos, bring up the report properties and choose the Schedule tab. Click create schedule. The requirement is to run this report every morning at 8:00 AM. (I will skip anything delivery related for sake of time).
a. Schedule tab, Frequency section
i. Type: Daily
ii. Repeat every: 1 days
b. Schedule tab, Period section
i. Start:
1. Today’s date is fine.
2. Enter the run time desired, in my requirement it is 8:00 AM
c. Options tab, setup your desired format(s), delivery, etc.
d. Prompts tab, no prompt values is desired here as we want these to be dynamically set by the report run.
19. Check your results the next day after your schedule executes the report. Today is Dec 20, 2023… so I am now seeing the grouped row for Dec 19, 2023, as expected.
Note: I added a layout expression above the list to show the parameter used… the fact this executed in the background, via the schedule, is why the format is timestamp, rather than date. For the sake of time, I didn’t investigate how to show this as dates… when run interactively they do show as dates.
Using a Query to Set the Default For a Prompt/Parameter
Another way to leverage this new feature in Cognos Analytics 12, is to use a query to set the prompt/parameter default.
Typical use cases for this might be to have the user’s default country be selected in a Country prompt, or a user’s default Product selected. I have been on a few projects where this was a requirement and implementing that was somewhat complex. If these defaults are stored in the data source, we can now easily retrieve them and use this new feature to set the parameters.
GO Sales doesn’t have these types of defaults, so I will use a different method to demonstrate the use of a query for setting the default. Let’s say our requirement is to show the most profitable Product type on the report’s initial rendering.
1. Create a report that shows the trend of planned revenue vs revenue by quarter… no date filtering necessary. In my example, I am using Cognos 11.1.x visualization called a Target Column.
2. This is for all Product types… we need to constrain the data to a single product type and pre-select the most profitable product type. So, next we need to add a filter to this visualization’s query on Product type, that includes a parameter, we will name it “pProductType”:
[Sales (query)].[Products].[Product type code] = ?pProductType?3. Next, we need to add a prompt control to the report. The requirement is for the users to be able to quickly switch between Product types, so I will use an in-line prompt (an in-line prompt is a prompt that is simply on the report page, rather than on a prompt page).
4. I will use a value prompt that returns the Product type code as it’s Use property, and the Product type – Gross Profit % as the Display property and set the prompt control to auto-submit. I sort the prompt values by Gross Margin % descending. Let’s test the prompt… Save your work and run the report.
Since there is no prompt page, Cognos generates a prompt page since “pProductType” is a required parameter. Just type in any number and click finish to test your prompt. Unless you entered a real product type code you want to see data in the report, but the prompt should be in good form.
5. Now, to avoid the Cognos generated prompt page and to see data for the Product type with the best Gross Profit %, we will add the Prompt/Parameter default using the same query we use for the prompt.
6. Navigate to the Parameters view, and click the All Parameters tab… you should see “pProductType” and that it is required, and is a number that is Set by a value prompt
7. Click on the Default values tab, click + to create a new parameter, select String parameter, and finally select the existing Report parameter “pProductType”.
8. With this parameter selected, click + on the Default values side to add a default value definition for “pProductType” and when prompted, select Value.
9. In the Default value dialog box that appears:
a. Select the query that provides the data for the Product Type prompt… in my example it is “prompt_Product type”.
b. On Properties, click the icon at the right of the and select the “Margin” data item.
c. Set the row limit to 1, as the filter is single-select.
d. In the Value section of the dialog box:
i. Source type should be set to data item value.
ii. Select the Use value from the prompt, in my example that is “Product type cd”.
iii. Enable the Display value.
iv. Source type should be set to data item value.
v. Select the Display value from the prompt, in my example that is “Display”.
10. Click OK to save the default value, save your work and run the report. You should no longer get the Cognos generated prompt page and the top value in the Product type prompt is selected, and visualization is showing the data for that Product type… in my example, that is “Insect Repellent”.
Notes About the Dynamic Prompt Parameters Feature New to Cognos Analytics 12
Once the report renders the first time, the default value(s) that are setup in the report are no longer going to be use/interrogated during the interactive session.
The default value setup will have no effect after the initial report rendering if:
Cascading prompt is set on the prompt tied to a default parameter value and the cascade parameter is changed.
If the report is re-prompted.
Conclusion
This feature has been long-awaited for years, and now that it is in the application, it offers the author a much less complex way to set defaults along with increased flexibility. In my experience, in-line prompts are gaining in popularity so the ability to easily set prompt defaults is an important addition to Cognos Analytics.
Explore even further with our Dynamic Default Prompt Parameters YouTube video. Whether you prefer reading or watching, there's something for everyone to dive into and understand the concept thoroughly.
Next Steps
We hope you found this article to be insightful and informative. If you have any additional questions about dynamic prompt parameters or would like PMsquare to provide guidance and support for your analytics solution, contact us today. Be sure to subscribe to our newsletter for more PMsquare updates, articles, and insights delivered directly to your inbox. Lastly, if you would like to hear about more about report authoring in Cognos Analytics, please let us know!