Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS report - multi-dimensional cube as a backend – unable to filter measure with not equals to 0 condition Expand / Collapse
Author
Message
Posted Friday, April 11, 2014 1:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 20, 2014 7:06 AM
Points: 17, Visits: 236
Hello Everyone,

I am working on SSRS report in which a multi-dimensional cube is being used as a backend.

We have a requirement to consider the data where we would like to put a condition on a measure say "TS" does not equal to 0. (Measure.TS != 0).

I tried to achieve it by adding a calculated measure i.e. "NonZero_TS" in a report query but the NON EMPTY condition does not work when the other members of the SET contains data.

Hence, indirectly the row is being displayed on a report though the value of TS one of the member of a set equals “0”.

Following is the query:

WITH MEMBER [Measures].[PT] AS iif([Measures].[PT] = 0 , null ,[Measures].[GS]/[Measures].[TS])

MEMBER [Measures].[NonZero_TS] AS iif([Measures].[TS] = 0,null,[Measures].[TS])

SELECT

NON EMPTY { [Measures].[NonZero_TS], [Measures].[GS], [Measures].[PT] } ON COLUMNS,

NON EMPTY { ([BTC].[CBN].[CBN].ALLMEMBERS *

[IFD].[Date].[Date].ALLMEMBERS ) }

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM ( [TESTCUBE])

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Any help would be much appreciated.


-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post.
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia
Post #1560724
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse