How to replace NULL values in Dashboard Designer (MS Office PerformancePoint Server) ?


A few days ago I found an interesting thread on PerformancePoint Monitoring Technet forum.

One of users had a problem with replacing empty values on his dashboard, which were created in Dashboard Designer. As it later turned out, replacing empty values in Dashboard Designer is available only in Scorecard component. Unfortunately other components like Analytic Grid does not support this functionality. In this article I would like to present how to solve problem with NULL values by preparing respectively MDX queries or with using calculated measures in your queries.

 

But firstly let’s start with an easier case – the Scorecard component.

Replacing empty values in Scorecard component

PerformancePoint Server M&A allows to replace all empty values with text which is provided by user.

Here is an example. In Figure 1 is shown the Sale Scorecard with empty values in two columns (Bikes and Components). Let assume that we want to replace all empty cells with „Empty” caption.

To achieve this you need to configure the following options in Dashboard Designer.

While editing your scorecard object go to the Edit tab on the Ribbon. Then select View Options depicted in Figure 1 (this button is enabled only if you place your cursor in any of scorecard’s cells).

Figure 1.  The scorecard with empty values – View Options menu.

 

When you chose this option the View Options dialog box will appear (see Figure 2).

This dialog box enables you to specify how you want a scorecard to appear in a deployed dashboard.

The Messages section enables you to specify how empty cells and errors are displayed in the scorecard. The View section enables you to specify display options, such as whether a scorecard allows for comments, is hidden from view in the dashboard, includes row expansion options, or shows gridlines, details, and headers.

If you want to replace your empty values you need to check option Indicate empty cells and put your caption in textbox below (see Figure 2).

 

Figure 2.  View options in Dashboard Designer.

 

When you change these settings all empty values on your scorecard will be replaced with provided caption. The final result you can see on Figure 3.
In the same manner you can  provide message for cells with error data, e.g. beause of invalid formulas in KPIs or problems with retrieving actual data from the Data Source.

Figure 3.  Scorecard with replaced empty values.

 

 

So, in the scorecard component there is dedicated option for replacing empty values. But this option is unavailable for Analytic Grid. How to achieve this functionality ? Here is a little workaround.

 

 

Replacing empty values in Analytic Grid component

 

Unfortunately Dasboard Designer does not provide any option to replace empty cells in your Analytic Grid. So we need to use other clever way to solve problem with replacing empty values.

Below will be presented two equivalent ways:

– preparing calculated measures in your Analysis Services (SSAS) cube.

– preparing MDX query and use it in your Analytic Grid.

 

 

Preparing calculated measures in your Analysis Services (SSAS) cube.

 

In our example let assume that we want to replace all empty values of [Measures].[Order Count] measure.

The sample MDX query is presented below.

SELECT { [Product].[Product Categories].[All Products].CHILDREN } ON COLUMNS,

{ [Date].[Calendar].[Month].&[2004]&[7].CHILDREN } ON ROWS 

FROM [Adventure Works]

WHERE ( [Measures].[Order Count])

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

Result of executing this query is shown below in Figure  4.


Figure 4.  Analytic Grid with empty values.

 

 

As you can see, we have two columns (Bikes and Componets)  with empty cells. So, the goal is to replace these cells with other nonempty values. Let assume that we want to replace them with zero value.

We can achieve this by provide a new calculated measure directly in the cube.

Here is an example of MDX code, which you need to use in your MDX script in BIDS project:

CREATE MEMBER CURRENTCUBE.[Measures].[Order Count 2]
 AS IIF(ISEMPTY([Measures].[Order Count]), 
       0,
      [Measures].[Order Count]
    ) 

After deploying and processing the cube, we need to modify our MDX query by provide new measure in SELECT statement.

SELECT { [Product].[Product Categories].[All Products].CHILDREN } ON COLUMNS,

{ [Date].[Calendar].[Month].&[2004]&[7].CHILDREN } ON ROWS 

FROM [Adventure Works]

WHERE ( [Measures].[Order Count 2])

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

In Figure 5 is depicted the final result, where all empty cells are replaced with 0 value.

Figure 5.  Analytic Grid with replaced empty values.

 

 

 

Preparing MDX query and use it in your Analytic Grid

 

The same result we can achieve by preparing MDX query and without need to do any modifications in the source OLAP cube. Here is the sample MDX query with calculated measure [Order Count 2].

WITH
MEMBER [Measures].[Order Count 2] as IIF(ISEMPTY([Measures].[Order Count]), 0, [Measures].[Order Count]) 
SELECT { [Product].[Product Categories].[All Products].CHILDREN } ON COLUMNS ,
{ [Date].[Calendar].[Month].&[2004]&[7].CHILDREN } ON ROWS 
FROM [Sales Summary]
WHERE ( [Measures].[Order Count 2] )
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

After using this query in the Analytic Grid component, the result will be similar to that presented previously in Figure 5.

 

More details about handling null values in MDX queries you can find in this very good article written by Mosha Pasumansky:

http://www.sqljunkies.com/WebLog/mosha/archive/2005/06/30/mdx_is_empty.aspx

Replacing empty values with text value in MDX queries

 

In the previous example we were replacing empty values with numeric value. But in many cases we want to replace empty cell with some text value, like „EMPTY VALUE”. How to achieve this?

That is quite simple. We need to modify our MDX query by adding one technical measure which will provide string value, which will be used in replace operation.

Here is sample code of this MDX query.

WITH

MEMBER [Measures].[Empty value] AS 1

MEMBER [Measures].[Order Count 3]

AS IIF(isempty([Measures].[Order Count]), [Measures].[Empty value].Name,

[Measures].[Order Count]) 

SELECT { [Product].[Product Categories].[All Products].CHILDREN } ON COLUMNS ,

{ [Date].[Calendar].[Month].&[2004]&[7].CHILDREN } ON ROWS 

FROM [Adventure Works]

WHERE ([Measures].[Order Count 3] )

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

As you can see in the query is present one measure [Measures].[Empty value] with any value (in this case it is 1). We use only the name of this measure, which will be presented in our Analytic Grid.

Another measure [Measures].[Order Count 3] is similar to measures presented in previous part of this article (IIF statement which replaces empty value with [Measures].[Empty value].Name)

After using this query we will get the following report presented in Figure 6.


Figure 6. Empty values replaced with textual value

 

As you can see, all empty values are replaced with „Empty value” string.
So, in replacing empty values we can use not only numbers, but also textual values.

 

Advertisements
This entry was posted in PerformancePoint Server 2007 and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s