Tips & Tricks #9: How Do Changes on the Source Report (Dataset) Get Reflected in MicroStrategy Report Services 9.x Documents
In MicroStrategy Report Services Documents, document datasets and their original source reports (such as a grid report being used as a dataset) are not completely connected to each other. Depending on the changes made on the source report, it can be reflected differently on the document. Basically, the change can be divided into two types.
Type 1 – Formatting Changes
Formatting changes, for example, changing autostyles, thresholds, subtotals.
If a user chooses the option Add to Section without Formatting, the grid/graph showing on the document will not use the report’s stored formatting. Any formatting changes on the source report will not be reflected on the document.
If a user chooses the option Add to Section with Formatting, the grid will be added with the current format of the report. However, any formatting changes made to the source report AFTER the dataset has been included in the document will NOT be reflected on the document.
For example, a user disabled the subtotal (see screenshot below) for the original report after the report has been included as a dataset in a document, the document will still show the subtotals.
To force the document to recognize the report’s formatting changes, the user needs to delete the grid/graph from document section and add it again using the With Formatting option. By doing this, the latest formatting properties of the grid/graph on the source report are retrieved.
Type 2 – Adding/Removing Objects and Modifying Report Filters
Another type of changes made on the report involving adding/removing objects and modifying report filters.
Unlike the formatting change, this type of change does carry over from the source report to the document datasets.
For example, if users add/remove/modify report filters (see screenshot below), the change will be reflected on the data when running the document.
If an object is removed from the source report (see screenshot below), the user can see the change in the document’s Dataset Objects window. After the user runs the document, the object will be removed from grid/graph on the document.
If an object is added to the report (see screenshot below), the change will show in the document’s dataset objects window. However, the object will not be automatically added to the grid/graph. The user has to manually add the object to the grid/graph or add the dataset to the section again to make it show up.
NOTE: As of MicroStrategy 9.0, a new feature was introduced where a user can add a dataset report to a Report Services document as a shortcut by selecting the Add to Section As a shortcut option, as shown below:
If the grid/graph is added to the document using this option, then the document would be updated automatically if ANY type of change is made on the source report.
This is something that I actually had the opportunity to use this week. It really came in handy and helped satisfy my client’s requirements.
Just a reminder as we discuss this tip, I am using MicroStrategy v9.4.1 and MicroStrategy Developer. Developer is the new name for MicroStrategy Desktop as of v9.4.1
This example demonstrates how to create a 3-level deep nested prompt that will prompt the user to select a year, then a quarter within that year, then a month within that quarter.
Prompt-in-prompt is a feature in which the answer to one prompt is used to define another prompt. This feature is only implemented for element list prompts. The following steps describes how to achieve this.
Create the Year Prompt
- Create the highest level filter first. This is a filter which contains a prompt on an attribute element list. Create a filter on the attribute “Year” and click “Prompt“.
- Click “prompt on attribute element list” and then click “Next” through the rest of the screens to accept the default values.
- Do not set any additional conditions. Save the filter as “Year Filter“.
From this level on, each filter will contain both a prompt on an element list and will have its element list filtered by the filter created one level above it. There is no limit to the number of such filters that can be layered.
Create the Quarter Prompt
- Create a filter on attribute “Quarter” and click “Prompt“.
- Select “Use a filter to reduce the number of elements” and select the “Year Filter” created in the previous step.
- Save this filter as “Quarter Filter” .
Create the Month Prompt
- Follow the same basic procedure as above to create a Month filter. Create a filter on attribute “Month“.
- Click “Prompt“.
- Select “Use a filter to reduce the number of elements” and select the “Quarter Filter” created in the previous step.
- Save this filter as “Month Filter” .
Try it Out
Place only the lowest level filter in the filter section of the report. In this case, the “Month Filter” will be placed in the filter section.
When the report is executed, it will prompt for Year, then for Quarters in the selected Year, then for Months in the selected Quarter, as shown in the following sequence of screen shots.
By default, when users apply subtotals in a report, the name of the subtotal is displayed in the subtotal line items that appear in the report. Users can use custom subtotals to give more control over the characteristics of a subtotal. Custom subtotals allow users to define custom subtotal line items that appear on the reports.
Users can make the subtotal name dynamic by typing special characters in the subtotal name field as listed in the following table.
To define a specific subtotal displays for a report like the one shown above, follow the steps below:
- Select Subtotals from the Data menu. The Subtotals dialog box opens. Clear the Totals check box to remove the standard subtotals.
- Click Advanced.
- Click New to create a custom subtotal.
- Type the following for the name: “Total for the #P #0″. Remember that P displays the parent attribute and 0 (the number zero, not the letter o) displays all the forms of the parent attribute. In this case, only one form exists for each, as shown below.
All the metrics on the report are listed. Users can select the subtotal function to use for each. Total is correct for all of the metrics.
- Check the Total for the #P #0 subtotal (shown below).
- Click Advanced.
- Select Across level and then select the Region and the Employee as the levels.
- Click OK to save the new subtotal.
- Click OK to return to the Subtotals dialog box.
- Click OK.
The report should now look like this.
Tips & Tricks #1: How to filter more than one substring in the Find search box when selecting a long list of elements from a dynamic prompt list
There are cases in which a dynamic prompt is used to qualify attributes selected to be part of a report, but the attribute list is very long.
Attributes like ‘Street Address’ may contain keywords like ‘St’, ‘Ave’, ‘Dr’ that can be used to reduce the list of selected elements.
It is possible to filter the list using the find box and to include one or more substrings that can be logically ‘ORed’ (Will be included if any of the substrings is found) or logically ‘ANDed’ (Will be included if all substrings).
Pattern delimiters are ‘ ‘ (space), ‘%’ and ‘_ ‘
Space or blank = will be used for logical ‘OR’
‘%’ = Will be used for logical ‘AND’ usually in pairs (begin-end of string)
‘_’ = Will be used as wildcard in lieu of space (blank)
CASE 1: Filter all attributes that contains ‘z’ or ‘x’ strings:
In this example, all listed addresses contains string ‘z’ OR string ‘x’ in any position of the description.
CASE 2: Filter all attributes that contains first the string ‘Old’ AND then ‘Hwy’:
Observe that substring ‘Old’ and ‘Hwy’ can be part of other string and can start in any position. However, using %Hwy%%Old% will produce a different result.
In this example, this filter will produce an empty list as there is no address that have substring ‘Hwy’ before ‘Old’.
CASE 3: Filter all attributes that contains the substring ‘Old Hwy’:
Special character ‘_’ should be used to represent a single space. Cannot be duplicated for representing multiple space.
In this example, if the filter were ‘_Old_Hwy’, the last two elements will not be shown as they do not begin with spaces.
NOTE: Examples shown in MicroStrategy Desktop, but it apply also to the Web interface when using the find box in dynamic prompts.