I've been trying to figure out how to do a bit of quality checking through
either MDX or standard REporting Services tools for a few days, but I can't
figure out how to do this.

In a report, a user can query my Olap cube for any given ClientID. If this
client ID is not available in the OLAP cube (yet), my RS report returns a
nasty error message
"Formula error - cannot find dimension member
("[Client].[Custaccount].&[11]") - in a name-binding function."

So, what I want to do is to do a 2 step query:
1) Check if Client.Custaccount 11 is in the cube
2) If it is, do the query - if not, return "something" that I can do
something usefull with. Like setting all measure values to -1 or something,
that I can write some code for in my RS report designer.

It seems it's possible to do a bit of conditional statements, but I can't
figure out how to write it. All help is appreciated!

With regards,

Kaisa M. Lindahl

Hi Kaisa:

You can do this by using a statement like

CREATE SET CustomerSet as 'FILTER([Customer].members,
[Customer].currentmember.properties("KEY") = "11") '

SELECT CustomerSet on ROWS

...... and so on.

The downside of this approach is that you might have a performance problem
if your customer dimension is too large.

Cheers.

Suranjan
"Kaisa M. Lindahl" wrote:

This sample query for Foodmart 2000 Sales may help:

Member [Measures].[IsValidStore] as
'iif(IsError(StrToMember("[Store].&[" + CStr([Measures].[StoreID]) +
"]")),
"No", "Yes")'
Select {[Measures].[IsValidStore]} on columns
from Sales

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

I used your suggestion, and it works like a charm! THanks a lot!
Just needed to change "create" to "with" and figure out how to escape the "s
in Reporting Services.

Kaisa

"SS" <SS (AT) discussions (DOT) microsoft.com> wrote