Today I tried to filter out a row with no caption from a result set.
In MDX Sample Application, I landed on this solution:
select {[Measures].[invoiced hours],[Measures].[Total hours]} on 0,
non empty filter([Group].members,
(len([Group].currentmember.Properties("Caption")) > 0)) on 1
from [MyCube]
where ([TransactionType].[All TransactionTypes].[Hours],[Date].[200501])
Then I tried turning this into a parameter based query in Report Designer. And the " caused some problems.
If I tried running the query without the ", I got this error message:
An error has occured during report processing.
Query execution failed for data set PB_AxpCmpWMB_Project1'
Formula error - syntax error - token is not valid: "filter([Group].members, (len([Group].currentmember.Properties(Caption)^)^ > 0)"
If I tried running the query with the ", I got this error message:
x:\data\myReport.rdl The expression for the query 'PB_AxpCmpWMB_Project1' contains an error: [BC30004] Character constant must
contain exactly one character.
So, I had to find out how to escape the "
Turns out, it's quite simple: You add an additional " / quote. It's not exactly a new thing in programming, I know I've used it several times in SQL. (With the ' or single quote that is.)
My query turned out like this:
select {[Measures].[invoiced hours],[Measures].[Total hours]} on 0,
non empty filter([Group].members,
(len([Group].currentmember.Properties(""Caption"")) > 0)) on 1
from [MyCube]
where ([TransactionType].[All TransactionTypes].[Hours],[Date].[200501])
It returns all rows where [Group] has a caption.