Dynamic Measure in Your Universe

by: Joe Roberts, Principal Consultant, BECP, SLE, MCP

One of the more common user requests for reporting is to be able to display year over year comparisons that are semantically dynamic. “I want a report that shows me this year’s sales today alongside last year’s sales and the previous year’s sales! I want to be able look at that by Product, Department, Salesperson or Region.” This presents a challenge for Universe designers. We are use to summing sales and displaying it based on a dimension like Product, Department, Salesperson or Region, but here we have a bit different requirement. How can we display sales for a particular year within the measure and still allow that measure to be semantically dynamic?

The first angle most people take is to add a WHERE statement to the measure like this:

There are two problems with this methodology:

1. The date is “hard-coded” meaning you will have to create a separate specific measure for each year.

2. The inferred WHERE clause will be added to the overall WHERE clause of the query; when you try to add another measure filtering on a different year (for instance 2007) the WHERE clause of the overall query will be: WHERE Year(SALE.SALE_DATE) = 2008 and Year(SALE.SALE_DATE) = 2007 resulting in zero records returned.

The real answer to this dilemma is to use a Case statement in the Select portion of the measure object. We will look at this from two angles. First we will fix issue 2 above to ensure that records are returned. Then we’ll fix issue 1 so that we don’t have to create a different measure for each year.

Using a Case Statement in the Select clause

Using a Case statement will allow the measure to look at each record and determine if it qualifies as a return value and removes the necessity to add a WHERE statement. Let’s look at what that same measure would look like using a Case statement.

Notice two important things about the above measure. First the sum statement must go outside the Case statement since it will look at each record to determine the year of the sale_date. Second, since not every record will match we have to provide a number to sum when it doesn’t match so put a zero in the else portion of the Case statement.

Dynamic Date Measures

Now solving issue 1 above might be easier than you think. Here we will create a measure for the current year, the previous year and two years ago. The Case statement will use the SQL GetDate() function instead of a hard coded year. This will make the measure completely dynamic. As we move from 2008 to 2009 the current year will dynamically migrate as well. The Sales Revenue Current Year measure would look like this:

The measure to return Sales Revenue for the previous year and two years ago would look like this:

Using this method your only real decision is how many years to you need create.

In Conclusion

By using a combination of SQL Case statements and SQL functions you can create measures that are both semantically dynamic and do not require constant updating of the universe. Remember, anything you might put in the select portion of a SQL statement can go into the Select clause of your Universe objects.

Go to Top | Return to Newsletter Contents