In this blog I will talk about how I create prompts within an Enterprise system. This method has the advantages of
- Code reuse.
Reducing testing and overall lifetime costs
- Code abstraction.
Allowing less experienced report developers to use complicated prompt
- Consistent user experience across a report suite.
Enhancing the user experience, requiring less training to use gained knowledge across multiple reports.
These advantages are what places Cognos above many other Management Information (MI) tools.
One thing that is often overlooked in comparing in MI tools is that the comparison should be done across developing an MI suite and not an individual report or dashboard and that costs should be compared across the lifecycle of the system not just report development.
This blog post builds on the topics that I covered in a previous blog post: Layout Component References and More Reusable Prompts
Within that post I introduce Layout Component References and Overrides. They are key concepts to understand before reading further. That post also explains some design decisions you will see later on.
This blog assumes a knowledge of report development. I will not be explaining the basics of report development.
I am also using some garish colour choices for simplifying this blog – in reality I assure you my tastes are far more refined!
The Problem
Within an enterprise MI solution, a dimension will be used across multiple facts. For example, a product dimension could be used in a Sales Report, a Returns Report or a Stock Inventory report.
We want to provide users with a consistent Product prompt experience across our report suite.
I will be using the sample Great outdoors data module throughout this post. Within this data module there is a Products dimension:
Across our reports we want to give users the ability to filter on this product dimension.
The Library Report
We will create a library report to hold the components we want to reuse across our MI suite. I have created a report called _Library and saved this is an area accessible to all users. This report will not be used by itself. It merely serves as a container for our common components.
The final structure of the Library report is up to you, I am merely offering a few suggestions in this blog.
Library Classes
The first thing I will add to my comment report is a the stdReportComment class. This allows developers to easily set an object as a comment within a report.
The border, background colour and foreground colour are set to make a comment stand out on the page. The Box Type none ensure the comment is only visible to report developers and not the report consumer.
In order for comments to be visible to report developers the Show Hidden Objects Visual aid should be enabled.
I will now some prompt classes so I can easily and consistently format any prompts.
- stdPromptMainTitle
Foreground Colour Blue and Bold Font
- stdPromptSubTitle
Foreground Colour Blue
The library report classes now looks like
Product Prompt
Across our reports we want the ability to filter on the Product dimension and that is the Product Line, Product Type or Product. For simplicity I will build three value prompts.
Within my library report I have built the following:
Working from the top to the bottom the key point here are:
- All objects are contained in a block Name property set to PromptProduct.
This builds a single object that can be used as a LCR within reports.
- Product Title.
A simple text item formatted using our stdPromptMainTitle class.
- Developer Comments.
A two column table that tells our developers all they need to know about this prompt.
The pProductLine, pProductType and pProduct text items are named so that report authors can override these and set defaults for the prompts if needed.
As the prompts are value prompts driven by a query a report author using this prompt needs to know which query is needed.
- User Assistance Text.
Some text that tells the user what the prompt is for.
- Product Line Prompt.
This is a 1 column by 2 row table.
The table properties Margin is 10px, Box type inline and Name is SubPromptProductLine.
The Naming of items is important as it allows us to override these objects later.
The first row is formatted using our stdPromptSubTitle class.
The second row contains a value prompt setting the pProductLine parameter.
The prompt is populated using the stdPromptProduct query and is optional.
- Similar tables are used for the Product Type and Product.
By using inline the tables are nested alongside each other in a responsive design based on the browsers window width.
There is a lot going on here!
The Sales Report
Let’s use the prompt as a report author would. The report author has created a simple sales report.
The report author wants to use the standard styles defined in our library report. They have set the Report Style refences to the library report.
By doing so they inherit all the classes we have defined in the library report. If we look at the classes of the sales report we can see the linked Library classes.
The author now wants the ability to filter the products included within the report. They add an LCR to the Product prompt.
This adds the product prompt to the report.
From the developer comments the author can see they need to copy the stdPromptProduct query from the library into their report report (unfortunately queries cannot be referenced across reports – I have asked for this feature many times!).
When the report is run the user sees our standard product prompt.
Whilst we have a prompt, this won’t actually filter the data in the report. We want to provide a similar level of abstraction to the filtering of the report.
Data Module Filter
Within the Data Module we will create a filter that applies all the filters of our product prompt. We will do this using a macro. This will look more complicated than a simple report filter but has the advantage of having multiple optional filters within a single filter.
Within the data module I have created a filter called stdPromptProduct. This ties in with the developer comment seen in the prompt. This filter uses the parameters set by our product prompt and filters the product table appropriately.
#
//Filters the Product dimension.All parameters are optional.
let pProductLine = promptmany( "pProductLine", "String", "NotUsed" ) ;
let pProductType = promptmany( "pProductType", "String", "NotUsed" ) ;
let pProduct = promptmany( "pProduct", "String", "NotUsed" ) ;
//Build the Product Line filter
let pProductLineFilter =
case pProductLine
when "NotUsed" then " 1 = 1 "
else " page_3.Product_line in ( " + pProductLine + " ) "
end;
//Build the Product Type filter
let pProductTypeFilter =
case pProductType
when "NotUsed" then " 1 = 1 "
else " page_3.Product_type in ( " + pProductType + " ) "
end;
//Build the Product filter
let pProductFilter =
case pProduct
when "NotUsed" then " 1 = 1 "
else " page_3.Product in ( " + pProduct + " ) "
end;
//overall filter
pProductLineFilter + "and " + pProductTypeFilter + " and " + pProductFilter
#
The default set by the promptmany function to the three parameters is “NotUsed”. If the prompts do not set the parameters then the parameters will be “NotUsed”. We can check for this value and use it to build up the three appropriate filter string. If the value of a parameter is “NotUsed” the filter is “ 1=1” which is effectively no filter. We concatenate the filters together to give us an overall filter.
The filter is a standalone filter within the data module. Normally I would put these library filters in their own folder.
Report Filter
The report author has added the standard prompt to their report. Looking at the developer comments they note that they also need to add the stdPromptProduct filter to their data queries. And this is shown here.
By adding this single filter their report will respect all three prompts. The author has no idea of what is going on inside the filter, or how simple or complicated it can be.
Resulting in
From an MI system perspective, we have completely abstracted the complicated process of adding prompts to a report. A report author simply needs to add the relevant prompt LCR, query and filter and they have filtered their reports. If we do this for every prompt – Time, Store etc then adding prompts becomes a quick simple process.
From a testing point of view if the prompt works in one report it works in all reports. We have drastically reduced the potential of code errors and the time to test. Only report specific prompts need to be tested in detail. Any prompts that come from the library would have already been tested.
Report Customisation
Our standard prompt has three prompts with no defaults set. What would an author do if they wanted a product prompt but only for Product Line and Product Type? What if they want to set a default for Product Line rather than have no entry set?
These two problems are easily dealt with using our LCR overrides. If the report author looks at the overrides list for the Product prompt they can override the SubPromptProduct.
This effectively removes this prompt.
We can see the drop zone where Product prompt previously was. We leave the drop zone empty. There is no need to change the report filter as the data module macro does not expect a parameter to be answered.
To set a default value for the product type prompt the user overrides the DefaultpProductType object leaving us with another drop zone.
The author then adds a text prompt into the location with the pProductLine parameter and the appropriate default value.
The new prompts default selections are set.
Remember that anything placed within the comments table is automatically hidden by the application of the Comment class. Report consumers will never see this new additional prompt.
We now have two prompts that effectively set the Product Line parameter.
When the report is now run we see there is no Product prompt and the product line now has a default.
We have a standard Product prompt. But we also give our report authors flexibility in how that prompt appears and is used within their reports.
Prompt Maintenance
If any changes are needed to the product prompt we only need to update the library prompt and the data module filter. All reports that use the standard prompt will then automatically benefit from those prompt updates.
For example, within the library report I could update the library product prompt to include a new product name pattern filter.
I update the Data Module filter to include appropriate code for the Product Pattern and now all of the reports that use the standard prompt now let users filter on a product name pattern without having to update any of the reports individually.
Wrap Up
To be concise I have simplified this post in how I actually build an enterprise system but I hope it gets across the key concepts about standard prompts with supporting data module filters. The same approach works equally well for Framework Manager.
Using this approach the time to build the first report becomes longer than if we built simple reports. We need to build up the standard library of reusable components. But the time taken to build the second, third reports is greatly reduced.
We have abstracted complicated prompting for our report developers allowing junior developers to benefit from a standard library of prompts.
One thing I have not demonstrated that in reports I usually have a prompt answers page showing the users what prompts have been set. This too should be an component within the library report. For every prompt there should be a prompt answer component that a report author can add into their report.
I use the library report to hold all classes used by enterprise reports ensuring a consistent look and feel across all reports. Changing the look of items across hundreds of reports becomes a single updated to a class in the library report.
And a Word on AI…
I think this blog demonstrates how as a system architect you should think of an MI system as a whole. As a system architect you should be looking for common components across all reports that can be moved from the reports into the library report or the data module. Looking for opportunities to simplify and reuse. This is just one example of simplifying a system, there are many others for example looking at expanding a report capability instead of new reports - all require the human touch.
This is what AI report authoring a single report misses. The worst case scenario here is that AI easily allows novice report authors to all have their own standard bespoke prompts with no code reuse. Not only does this give a very poor user experience, but it drastically adds to the volume of code that needs to be maintained within an enterprise environment.