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

How to add these conditions as filters in SSRS Expand / Collapse
Author
Message
Posted Tuesday, April 16, 2013 9:57 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 04, 2014 8:11 AM
Points: 78, Visits: 233
HI All,

I have modified existing SQL based report to Mdx except below conditions.
please suggest something on how to convert this or how can we add below conditions as filters in mdx.

------------------------------------------------------------------------------------------------
where
(([Sales Margin Local Currency]/[Sales Without Tax Local Currency])>=0.9 or ([Sales Margin Local Currency]/[Sales Without Tax Local Currency])<0.1)
and [Sales Without Tax Local Currency]!=0
and ri.BillingRule not in ('5','6')
and right(p.productID,9) not in ('900002181','900002182')
------------------------------------------------------------------------------------------------
MDX QUERY IS AS FOLLOWS:
select
--NON EMPTY
{
[Measures].[Sales Margin With VAT],
[Measures].[Sales margin %],
[Measures].[Discount %],
[Measures].[Cost With VAT],
[Measures].[Sales price before discounts with VAT],
[Measures].[Sales BUn]} on columns,
NON EMPTY
{([Sales Order Row DIM].[Sales Order Type].AllMembers *
[Sales Order Row DIM].[Sales Order Creator].AllMembers *
[Sales Order Date DIM].[Date].&[2011-09-25T00:00:00] *
[Billing Date DIM].[Date].AllMembers *
[Product DIM].[Material FI].&[500800505 - FAZER MAITOSUKLAAPATUKKA 45G 401775] *
[Product DIM].[Material ID].&[500800505]
-- * [Product DIM].[Material ID].AllMembers *
--[Product DIM].[Material FI].AllMembers
)}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT (
STRTOMEMBER(@StartDate, CONSTRAINED) :
STRTOMEMBER(@EndDate, CONSTRAINED) ) ON COLUMNS FROM ( SELECT (
STRTOSET(@StoreDIMStore, CONSTRAINED) ) ON COLUMNS FROM [Store Sales])) WHERE (
IIF(
STRTOSET(@StoreDIMStore, CONSTRAINED).Count = 1,
STRTOSET(@StoreDIMStore, CONSTRAINED),
[Store DIM].[Store].currentmember ) )
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
---------------------------------------------------------------------------------------
Thanks,
Niha
Post #1443089
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse