vrijdag 11 november 2011

SSRS : Adding process date of a cube to your report

Introduction 
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
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:

The next step is going to Query mode and add the query to window. When Ok is pressed the following error occurs:


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....


The solution 
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:




Conclusion
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.

Greetz,

Hennie

Geen opmerkingen:

Een reactie plaatsen