April 16, 2013 at 9:57 pm
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
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply