vrijdag 11 november 2011

SSRS : Adding process date of a cube to your report

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 :


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:

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.



Geen opmerkingen:

Een reactie plaatsen