In this blogpost i'll explain how to add a date to your report when a cube is processed. I've a requirement of my customer that they want to show a date on the report when the cube is processed. In this blogpost i'll use a query that can be used for determining the date when the cube is processed.
The problem is that using the query :
SELECT LAST_DATA_UPDATE FROM $System.MDSCHEMA_CUBES WHERE CUBE_NAME = '<cube>'
can't be done in a standard manner, like using MDX queries in the MDX query window in reporting services
Below i'll show the failed attemps and a successful attempt.
Attempt 1 : Query designer
I'll show what happens when i try to add the query to the MDX query designer. First step is to open the query designer in the report and press the designbutton:
So this a blind alley!
Attempt 2 : Expression
The next attempt i tried was using the expression button and that seems to work
And resulting in a value:
But when i try to open de expression designer again:
An error occurs:
And i have to select a cube:
And again an error occurs
Again a blind alley....
An solution i've found is descibed below. Go to the query designer and press the DMX editor:
Go to the designer button and press on it:
Copy the query in it and press OK.
And we have a working last cube processed date:
I don't understand how and why but it seems to work and i've implemented this in my reports. But i've to say it's a bit strange.