Creating a Row in a Crosstab that Calculates the Difference between the Columns

If you would like to create a crosstab that calculates the difference between columns as shown below:



Create a calculated Measure running-difference([Revenue]) where [Revenue] is the measure for which you wish to calculate the difference.

Some questions that I have with the above method is that running-difference is not available in the list of functions when you use a dimensional model. This is a relational function that we are using against a dimensional model, so I am not sure of the performance impact that this would have.

What other information that I have noticed is that no longer does the Native SQL gets generated. Only the Cognos SQL gets generated. Once the running-difference item is deleted then the Native MDX gets generated.

So now, my question is why doesn't Cognos include a lot many functions as part of the dimensional model function set? This causes a lot of issues for the developers.