Adding Description Fields to PMF’s Measure Detail Report

Taken from a Focal Point discussion, Joey Baloun explains how to add Description Fields to PMF’s Measure Detail Report.

Performance Management Framework (PMF) is my favourite Information Builders product, because it is powerful, quick to implement and very extensible. Since it is built with WebFOCUS, JavaScript / AJAX, and HTML, it can be sustainably tweaked behind the scenes to solve client requirements beyond the initial framework. Its modular nature means that a change made in one file will often cascade throughout the entire system. Here is an example that shows how PMF can basically be made to jump through flaming circus hoops.

Problem:

Dimension values are limited to 30 characters in length, which is fine for most cases. However, my retail client has a field that contains a 19 character code that corresponds to a 50 character description field. The client could drill-down through the dimension levels to see the code, but had to look in a different system to see the description.

Solution:

The Measure Detail report (a_meas_detail01.fex) gets called when you drill-down on most measure values in PMF. Whether you start from the Rolling 5 Period report, the Previous VS Current report or a custom Gadget on a dashboard, you will likely end up at the Measure Detail report within a click or two. By modifying this report only, the description field will be available to the user no matter how they choose to consume measures.

This is what the report looks like before any modifications; measure values have been zeroed to protect the guilty:
PMF01

This is the report after being modified. The Style Description is shown next to the Style Code, seamlessly integrated into PMF:
PMF02

Things to consider before making any changes to a_meas_detail01.fex:

* If you make changes to the file while it resides in the mainstreet app folder, they will be overwritten during the next version upgrade.
* You should make a copy of a_meas_detail01.fex into a pmf_custom app folder, and then rename the original file in the mainstreet folder. Make sure pmf_custom is in your app path.
* Since this description only applies at one level of the dimension, any changes will need to be conditional depending on where the user is in the drill-down path.

Using a JOIN that only kicks in when the user is at the desired level of the dimension, I was able to show the description in PMF and save the day:

Edit the a_meas_detail01.fex and find where it says "DEFINE FILE PASS1"

Alter the fex like so:

-******************************
-* CCCCCCCCCCCCCCCCCCCCCCCCCCCC
-* This piece of code will bring in a description field from the data warehouse 
-* when the report is at a particular drill-down level:
-IF &SORTBY1_DRILL_FIELD NE 'HUB_1_LEVEL04_VALUE' GOTO SKP_SJOIN1;
JOIN
&SORTBY1 IN PASS1 TO DWD_PRODUCTS_STYLE_CODE IN DWD_PRODUCTS AS JB01
END
-SKP_SJOIN1
DEFINE FILE PASS1

-IF &SORTBY1_DRILL_FIELD NE 'HUB_1_LEVEL04_VALUE' GOTO SKP_STYLED1;
-* LCWORD is used to format the description into ‘proper’ case
STYLE_DESC/A50 = LCWORD(50,DWD_PRODUCTS_STYLE_EN , STYLE_DESC);
-SKP_STYLED1
-* CCCCCCCCCCCCCCCCCCCCCCCCCCCC
-******************************

-* Down below this, find the &BY_SORTBY1_FIELD_NOPRINT and add this right below:

-*************************************
-* CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
-IF &SORTBY1_DRILL_FIELD NE 'HUB_1_LEVEL04_VALUE' GOTO SKP_STYLED2;
BY STYLE_DESC AS 'Style Description'
-SKP_STYLED2
-* CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
-*************************************

When next you drill to the Measure Detail report, and then drill to the fourth level of your dimension (based on my example code), you will see the description next to the dimension values.

Obviously you will need to adapt the code to suit your dimensions and pick the level(s) where you wish to show descriptions. Here are the key pieces of information you will need to understand first:

* PASS1 is the hold file that gets generated right before the measure detail report does the DEFINE and TABLE requests.
* &SORTBY1 contains the fieldname that you will join from.
* &SORTBY1_DRILL_FIELD contains the dimension level that you are currently drilled to.

Good luck and I hope you enjoy PMF like I enjoy PMF.

This technique is also posted on the PMF forum, [Technique] Adding a description field to Measure Detail report.