About MEL and Conditional Containers

One of the advanced features of dashboard editing allows you to conditionally display objects based on certain criteria.  This means you can chose to have an object display based on the results of a true/false test.

For example, you could have an object which displays the AR balance for a customer.  But if their balance is less than a certain threshold ( less than $1000 over 90 days) then the object won't display on the scorecard.  It will only show up with the customer has an AR balance over $1000 for more than 90 days.  In effect, this only brings the object up and to the viewer's attention when there is an outstanding AR balance which needs attention.

This is done with the use do two tools available when editing a dashboard.

• The Conditional Container object - CONDITIONAL.CONTAINER
• MEL Expressions

These two concepts work together to determine whether the content in the Conditional Container will be displayed.  The Conditional Container has an attribute called SHOULD.RENDER. This attribute contains a MEL expression. If the MEL expression results in 'true' then the contents of the contain are shown. If the MEL expression results in 'false' then the contents is not shown.

Create Conditional Container

A Conditional Container is made by clicking the Conditional Container button while in the Visual Edit mode for a dashboard or scorecard. This creates a new Conditional Container in the dashboard. It will  not ask you for the MEL Expression initially.

When you edit the Conditional Container, you will find that it already has a MEL Expression in there. Just the word 'true'. At this point the contents of the Conditional Container will always show. You can modify this to add in a MEL Expression to make it actually do something.

Let's put something more exciting in there, like is the TEST cube reportable (built).

That is the basics of the Conditional Container object itself. Now we'll take a look at the MEL Expressions.

A Basic Conditional Container:

Let's look at a basic Conditional Container in manual edit mode.  You will notice some additional encoding is required.  This is (in part) why it is better to use the visual editor.

{CONDITIONAL.CONTAINER SHOULD.RENDER="#{cubeExists("TEST")}"}
 {CELL
 FLASH='FLASH TEST {RootPath} SALES.M'
 HREF='FLASH TEST {RootPath} SALES.M COST.M PROFIT.M UNITS.M GP.PCT.M'
 TEXT.BEFORE="Sales MTD : "
 TEXT.AFTER=""
 }
 {CONDITIONAL.CONTAINER.END}

Here is the same object as created by the visual editor.

{CONDITIONAL.CONTAINER SHOULD.RENDER="#{cubeExists("TEST")}"}
{CELL
FLASH='FLASH TEST {RootPath} SALES.M'
HREF='FLASH TEST {RootPath} SALES.M COST.M PROFIT.M UNITS.M GP.PCT.M'
TEXT.BEFORE="Sales MTD : "
TEXT.AFTER=""
}
{CONDITIONAL.CONTAINER.END}

From the above you can see why some of the characters in the SHOULD.RENDER attribute need to be encoded. You can't have double quotes inside of double quotes.

In the UI it looks like this:

mel2

For this Conditional Container, if the cube TEST exists then the CELL object contained in the Conditional Container shall display when viewing a dashboard.

Things to know:

• If the Conditional Container is in a SECTION dashboard object, and resolves to false and so doesn't show, the SECTION will still persist since it's not erroring.
• You can nest Conditional Containers to make it very dynamic

BEST PRACTICE
Put SECTIONS inside Conditional Containers to ensure you do not
Dirty up your dashboard when things disappear.

Visually Modifying a Dashboard

When visually modifying a dashboard, you can tell where the Conditional Container are by looking for the grey bars with the …. In there:

 

While you are creating Conditional Container objects, the visually modify dashboard screen will resolve the MEL Expressions and then if the current user will see the contents of the Conditional Container when viewing the dashboard, it will have a green border. If the current user cannot see contents, then the border is dashed red.

If the dashboard is loading or refreshing, the Conditional Container objects will have an orange border until they are loaded:

MEL Expressions

MEL Expressions is a language we use to query the system. I'm going to be going over the very basics here. There is a lot more information in the MEL - Function Cheat Sheet.

Format

MEL Expressions have 2 ways you can use. One starts with a pound '#' and the other starts with a dollar sign '$'.

#{<MEL Expression>}

${<MEL Expression>}

BEST PRACTICE:
it is best practice to only use the pound '#' character.
This is due to the fact that we use the ${ in deployment properties.
If you use $ and then do a refactor, it will change it to #.

Where they can be used

MEL Expressions currently can be used in two places:

• The SHOULD.RENDER attribute of the CONDITIONAL.CONTAINER dashboard object
• The TEXT.BEFORE attribute of the CELL object

The CELL Object

The CELL object TEXT.BEFORE field is a great way to find out things about your MEL Expression. First, what does it resolve to, and second what is the value you are testing. This is especially useful when you want to make sure your MEL Expression is working like you think it is. The CELL object can help you debug that.

Let's look at the following two containers (each inside a Section with the MEL Expression as the heading):

 

Now if you're Karin, you might get it into your head that the answer to both of these MEL Expressions are 3 with a remainder of 1. I wonder hum….what is actually being returned here. I'm especially concerned about the dashed red border there. So I create 2 CELL objects and put the following in each:

#{10 div 3}

 

#{10 div 3 <= 3}

I do the same for the green border version but use >= and I end up with this:

Now I see that the top container is dashed red because the value is actually greater than 3 not <= 3. And so I've found my answer.

MEL Expressions

Basic Rules:

• You cannot put a MEL Expression inside a MEL Expression
• It has to be formatted properly
• MITS MEL Functions must be formatted properly
• You are able to use {RootPath} in the MITS MEL Function
• You are able to use {<placeholder>} in the MITS MEL Function
• NOTE: if you do a refactor, it will change he single quote to an apostrophe. (bug 55200)

What can you do with MEL Expressions:

• Math
• Comparing values
• Binary functions
• IF statements
• MITS MEL Expressions

All of these are covered in the MEL - Function Cheat Sheet. What I want to focus on are the Use Cases for this feature and give examples how to solve those Use Cases using mainly the MITS MEL Expressions.

Use Cases and MITS MEL Functions

You will see that many of these exist as Booleans. If the expression is true, the Conditional Container will show the data, if false it won't. Other expression do a comparison on the MEL Expression and still it resolves to true or false.

• I only want to see the data if the cube exists (Discover)

#{cubeExists{"AR")}

• I only want to see the data if the Report Source exists (Report)

#{reportSourceExists("OPEN.ORDERS.REPORT")}

NOTE: use the Report Source External ID

• I only want to see the data if the cube is built and is reportable (has columns) (Discover)

#{cubeReportable("SALES")}

• I only want to see the data if the Report Source is updated and reportable (Report

#{reportSourceReportable("OPEN.ORDERS.REPORT")}

NOTE: use the Report Source External ID

• I only want to see the data if there is 1 or more rows in the Report (I don't want to see the data if there is nothing actionable) (Discover)

#{lowestLevelRowCount("FLASH OPEN.ORDERS O OPEN.AMT.M") > 0}

#{lowestLevelRowCount("FLASH OPEN.ORDERS {RootPath} O OPEN.AMT.M") > 0}

#{lowestLevelRowCount("FLASH OPEN.ORDERS WITH R = {Sales Rep} O OPEN.AMT.M") > 0}

• I only want to see the data if there is 1 or more rows ub the Report (I don't want to see the data if there is nothing actionable) (Report)

#{rowCount("REPORT ORDER.NUMBER FROM OPEN.ORDERS.FILE") > 0}

#{rowCount("REPORT ORDER.NUMBER FROM OPEN.ORDERS.FILE WITH {RootPath}") > 0}

#{rowCount("REPORT ORDER.NUMBER FROM OPEN.ORDERS.FILE WITH R = {Sales Rep}") > 0}

• I don't want to show the data to a user who has a root restriction (POT - since the user is unable to see the top row. Or a C4 designed grid that the user won't be able to see the pertinent data)

#{!userHasRootRestriction("SALES")}

NOTE: in order to satisfy this Use Case, we needed to use a 'not' operator !

• I don't want to show data that is not built into the cube yet (new customers may not have enough data for Last Month Year To Date)

#{cubeHasEon("SALES","M-13")}

• I only want to show data if a value is above/below a certain threshold (Don't show AR data for customers that don't have an outstanding balance)

#{evalCellNumeric("FLASH AR WITH C = '666' CURRENT.BALANCE.M") > 0}

#{evalCellNumeric("FLASH AR WITH C = {Customer} CURRENT.BALANCE.M") > 0}

#{evalCellNumeric("FLASH AR {RootPath} CURRENT.BALANCE.M") > 0}

OR for Report:

#{evalCellNumeric("REPORT ORDER.NUMBER OPEN.VALUE FROM OPEN.ORDERS.FILE") > 0}

#{evalCellNumeric("REPORT ORDER.NUMBER OPEN.VALUE FROM OPEN.ORDERS.FILE WITH {RootPath}") > 0}

#{evalCellNumeric("REPORT ORDER.NUMBER OPEN.VALUE FROM OPEN.ORDERS.FILE WITH C = {Customer}") > 0}

Example:

Let's start with a MEL Expression:

#{evalCellNumeric("FLASH TEST W SALES.Y") > 0}

This says that only show the Conditional Container if the bottom right cell of the FLASH statement given is greater than 0. Let's see what it does. Notice the CELL object I added. It shows the bottom right value!



NOTE: Using evalCellNumber will take the bottom right cell of what is returned in the FLASH/REPORT statement.

• I only want to show data if a particular identifier exists in a cube (ProServe may have removed it)

#{identExists("SALES","PT")}

• For some odd reason I want to know if a user has a root restriction on a particular identifier. I have no idea what the Use Case actually is

#{userHasRootRestrictionOnIdent("SALES","PT")}


Error Handling:

If there is a problem with the MEL statement, you'll see an error like this:

 

It doesn't tell you what the problem is. Here is a list of problems to look for:

• Cube not accessible
• Cube doesn't exist
• Malformed MQL/MRQL
• Uses a different cube and identifiers than the cube used in the dashboard, and there's no XREF

Another error you might see is:

It shows up when:

• User has an accumulator restriction and all columns are dropped

There are other errors as well