Hi,

Here is something that I'm positive will be useful for many PMF customers. I've figured out a way to add long description fields to the Measure Detail report (a_meas_detail01.fex).

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. They would drill down through the dimension to see the code, but had to look in a different system to see the description.

Using a JOIN that only kicks in when they are at the right level of the dimension, I was able to show the description in PMF and save the day. Here's how:

-Copy a_meas_detail01.fex from your mainstreet folder into pmf_custom
-rename the original to something else, so that PMF only finds the file in pmf_custom
-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' OR 'HUB_2_LEVEL04_VALUE' OR 'HUB_ROLES_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' OR 'HUB_2_LEVEL04_VALUE' OR 'HUB_ROLES_LEVEL04_VALUE' GOTO SKP_STYLED1;
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' OR 'HUB_2_LEVEL04_VALUE' OR 'HUB_ROLES_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

Cheers,

Joey

Hi Joey,

I could see this being very helpful for longer description displays.

One thing I noticed was that you may want to use &SORTBY1_FIELD instead of &SORTBY1 for the join field in the last step.

JOIN
&SORTBY1_FIELD IN PASS1 TO DWD_PRODUCTS_STYLE_CODE IN DWD_PRODUCTS AS JB01
END

In PMF version 5.1.1 using SORTBY1 or SORTBY1_FIELD should work, but a more exacting join field moving forward in newer releases will be the SORTBY1_FIELD.

Good job!
Mike

Hello again,

In PMF versions > 5.1.1, the measure detail report has structurally changed. This enhancement needs to be added lower down in the a_meas_detail01.fex code. Look for the lines above and below my -* CCCCCCCCCC, to determine where to put this code:

-******************************************************************************************************************************
This is the final report display
-:S03_A
-******************************************************************************************************************************
-******************************
-* 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' OR 'HUB_2_LEVEL04_VALUE' OR 'HUB_ROLES_LEVEL04_VALUE' GOTO SKP_SJOIN1;
JOIN
&SORTBY1_FIELD IN DETAIL01 TO DWD_PRODUCTS_STYLE_CODE IN DWD_PRODUCTS AS JB01
END
-SKP_SJOIN1
DEFINE FILE DETAIL01
-*
-IF &SORTBY1_FIELD NE 'HUB_1_LEVEL04_VALUE' OR 'HUB_2_LEVEL04_VALUE' OR 'HUB_ROLES_LEVEL04_VALUE' GOTO SKP_STYLED1;
STYLE_DESC/A50 = LCWORD(50,DWD_PRODUCTS_STYLE_EN , STYLE_DESC);
-SKP_STYLED1
-*DEFINE FILE DETAIL01
-* CCCCCCCCCCCCCCCCCCCCCCCCCCCC
-******************************
-INCLUDE A_IMAGES_STANDARD

Now look a few lines lower:

&BY_SORTBY1_FIELD_AS
-*************************************
-* CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
-IF &SORTBY1_DRILL_FIELD NE 'HUB_1_LEVEL04_VALUE' OR 'HUB_2_LEVEL04_VALUE' OR 'HUB_ROLES_LEVEL04_VALUE' GOTO SKP_STYLED4;
BY STYLE_DESC AS 'Style Description'
-SKP_STYLED4
-* CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
-*************************************
-IF (&SORTBY1_TIME_AGG LE 1) THEN GOTO :S03_B_SK1 ;

Cheers,

Joey