April 21, 2011 at 7:30 am
Hi,
I have a question (which I think must be rather simple), but as I am new to it for me it's rather hard.
My question is about the where clause, how do I define a statement which selects my Order count based on different dimensions?
Basically I want an order to be counted distinct if the (CustomerName = X or SupplierName = X or ImporterName = X or DistributorName = X)
This is one of my attempts:
SELECT {
([Date].[Year].&[2010]),
([Date].[Year].&[2011])
}*
{
([Measures].[Order Count])
} ON COLUMNS,
NON EMPTY ([Supplier].[Supplier Country Name].[Supplier Country Name])*
([Distributor].[Distributor Country Name].[Distributor Country Name]) ON ROWS
FROM
Cube
where
{
{[Customer].[CustomerName].&[X]}*
{[Supplier].[SupplierName].&[X]}*
{[Importer].[ImporterName].&[X]}*
{[Distributor].[DistributorName].&[X]}
}
Thank you for you help.
May 24, 2011 at 4:28 pm
Hi,
Pointing you into the right direction. You have to think in Sets (Set Theory). You need to OR the sets.. Using the sample below..
set 1 = { {all fruit members }, {all trees members}, {one bird member} }
set 2 = { {all fruit members }, {one tree member}, {all bird members} }
set 3 = { {one fruit member }, {all tree members}, {all bird members} }
These sets have the same dimensionality (fruit, trees, birds ) and can therefore easily OR'ed. ORing sets is uniting them.
IN MDX you unite bij using the UNION function or + sign.
set1 + set 2 + set3. I leave it to you to optimize the algebra (maths) and the MDX.
Hopes this helps.
Regards Kees
And the answer:
SELECT { ([Date].[Year].&[2010]),([Date].[Year].&[2011]) }
* { ([Measures].[Order Count]) }
ON COLUMNS,
NON EMPTY ([Supplier].[Supplier Country Name].[Supplier Country Name])
* ([Distributor].[Distributor Country Name].[Distributor Country Name]) ON ROWS
FROM Cube
where
{
{ {[Customer].[CustomerName].&[X]}
* {[Supplier].[SupplierName].members}
* {[Importer].[ImporterName].members}
* {[Distributor].[DistributorName].members }
}
+
{ {[Customer].[CustomerName].members}
* {[Supplier].[SupplierName].&[X]}
* {[Importer].[ImporterName].members}
* {[Distributor].[DistributorName].members }
}
+
{ {[Customer].[CustomerName].members}
* {[Supplier].[SupplierName].members}
* {[Importer].[ImporterName].&[X]}
* {[Distributor].[DistributorName].members }
}
+
{ {[Customer].[CustomerName].members}
* {[Supplier].[SupplierName].members}
* {[Importer].[ImporterName].members}
* {[Distributor].[DistributorName].&[X]}
}
}
May 30, 2011 at 1:23 am
Hi Kees,
Thank you for your post, your solution gives the correct output, which is great, thanks for that!
The only downside is the performance for it, so we'll have to try to optimize it.
Regards,
Andre
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply