MDX help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

MDX help

Author

Topic

nicklarge
Starting Member

40 Posts

Posted - 2011-08-11 : 23:13:58

Hello SqlTeam.

I am a newbie to MDX and need help. Here is my current MDX query:

SELECT {[Measures].[Transaction Count]
* { [Process Date Fiscal Calendar].[Standard Date].&[2011-07-11T00:00:00] :
[Process Date Fiscal Calendar].[Standard Date].&[2011-07-21T00:00:00] } } ON COLUMNS,
NON EMPTY
{ [Location].[Territory Manager Reference].[Territory Manager Reference].ALLMEMBERS
* [Location].[Territory Manager Name].[Territory Manager Name].ALLMEMBERS } ON ROWS
FROM [SalesCube];

This provides the total transaction counts for all territory managers individually over the given period. What I am looking for is how do I get a single row containing just the counts of TMs for each date that have transaction counts > 0 ? I need to know how many Territory managers sold products, versus how much each TM sold.

Any help would be great right now - need some direction.

Thanks, Nick.

ssdeveloper
Starting Member

37 Posts

Posted - 2011-08-15 : 17:52:58

Hi Nick,

If you send the result set, that would be of big help to understand your question in more detail.

For now, I think you can use the where clause for the transactions>0 part, and if you just put the managers on the grid(I mean on the rows), that should do it.
Like:
{[Location].[Territory Manager Name].[Territory Manager Name].ALLMEMBERS} ON Rows

I am not an expert but I just want to give an idea with what I know.

Hope this helps!

ssdeveloper
Starting Member

37 Posts

Posted - 2011-08-15 : 18:58:44

Ok, I think after reading your question couple of times more, I understand a little bit more, You need to put the managers and the dates you wanted on the rows (you can use the range of the dates as you used in the columns given in your query)and just the transactions measures on columns. You can use the >0 condition in where clause. Try and let me know if it worked.

Thanks.

nicklarge
Starting Member

40 Posts

Posted - 2011-08-16 : 14:01:57

hello ssdeveloper.

Thanks for your help, it moved me in the right direction, although I had trouble with the >0 in the where clause:

WITH MEMBER Measures.CountTMs
AS 'COUNT( { [Location].[Territory Manager Reference].MEMBERS }
* { [Measures].[Transaction Count] },
ExcludeEMPTY ) '
SELECT ((Measures.CountTMs) * [Process Date Fiscal Calendar].[Date Name].[Date Name]) ON COLUMNS,
NON EMPTY
FILTER(
FILTER( {
{ [Product].[Product Description].[Product Description].&[ADAPTOR BAR, SPR 20] },
{ [Product].[Product Description].[Product Description].&[ADAPTOR BAR, SPR PAN 12] }
},
(Measures.CountTMs) > 0 ),
[Process Date Fiscal Calendar].[Standard Date].&[2011-07-13T00:00:00]
) ON ROWS
FROM [USF Sales]
WHERE ({[Process Date Fiscal Calendar].[Standard Date].&[2011-07-13T00:00:00]})
AND (Measures.CountTMs) > 0

The AND (Measures.CountTMs) > 0 gave me the error:

"The AND function expects a string or numeric expression for the 1 argument. A tuple set expression was used."

... but I think I am heading in the right direction.

I tried the query on all products () and it took an excessively long time (something like an hour or so) so I quit the query:

WITH MEMBER Measures.CountTMs
AS 'COUNT( { [Location].[Territory Manager Reference].MEMBERS }
* { [Measures].[Transaction Count] },
ExcludeEMPTY ) '
SELECT ((Measures.CountTMs) * [Process Date Fiscal Calendar].[Date Name].[Date Name]) ON COLUMNS,
NON EMPTY
FILTER(
FILTER( {
[Product].[Product Description].[Product Description].ALLMEMBERS
},
(Measures.CountTMs) > 0 ),
[Process Date Fiscal Calendar].[Standard Date].&[2011-07-13T00:00:00]
) ON ROWS
FROM [USF Sales]
WHERE ({[Process Date Fiscal Calendar].[Standard Date].&[2011-07-13T00:00:00]})

Do you know how I should modify the AND to incorporate the >0 transactions as you suggested?

Thanks, Nick.

ssdeveloper
Starting Member

37 Posts

Posted - 2011-08-16 : 14:36:13

Glad I could help.
I dont think you need to put this part in the where clause at all:\({[Process Date Fiscal Calendar].[Standard Date].&[2011-07-13T00:00:00]})

Just (Measures.CountTMs) > 0 in the where clause should do it. When you put the date on the rows its automatically filtered by the dates. you need not filter it again and also I don't think you need to add the filter function. Just add the
{ [Product].[Product Description].[Product Description].&[ADAPTOR BAR, SPR 20] },
{ [Product].[Product Description].[Product Description].&[ADAPTOR BAR, SPR PAN 12] }
},
and non empty the dates and I think it should work. Try not adding the functions and see, It should improve the performance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources