help with mdx - 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 2005 Forums

Transact-SQL (2005)

help with mdx

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-01-12 : 05:34:29

how i convert it to mdx?

SELECT Count(DISTINCT CustomerNumber)
FROM FACT
GROUP BY YEAR

thanks

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-01-12 : 06:38:18

I am not getting u ? Can u explain it...

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-01-12 : 07:07:15

i want to create calculated member,how i wrote this query?

(it count all DISTINCT customers by years)

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-01-12 : 07:24:31

could you explain bit more..?

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-01-12 : 07:33:52

i have this results from cubes

    
         2006     2007    2008
product
aa        2        4        3
bb        2        5        3 
cc        2
total     4         8        5

i have a measure that distinct count in the properties-aggreation
i just want to make the total (like in the quuery)
thanks

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-01-12 : 07:45:59

something like that, i have wrong with that:

WITH SET MySet AS {
[Measures].[CutomerNo]}

MEMBER MEASURES.SETDISTINCTCOUNT AS
DISTINCTCOUNT(MySet)

MEMBER MEASURES.SETDISTINCTCOUNT AS

DISTINCTCOUNT(MySet)

SELECT {MEASURES.SETDISTINCTCOUNT} ON 0

FROM [Fact]

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-01-12 : 08:16:21

or something like that

SELECT
[DATE].[Year].Dimension On columns,
Distinctcount([Measures].[CustomerNumber]) on Rows
FROM Fact

OR Just --------------------------------------------------
Distinctcount([Measures].[CustomerNumber])

but the second option give me 0 valuse.

i just want to write in correct mdx the query
SELECT Count(DISTINCT CustomerNumber)
FROM FACT
GROUP BY YEAR

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-01-12 : 08:26:17

Are you using Microsoft SQL SERVER? Or something else?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-01-12 : 08:37:32

sql server 2000,and builts the cubes in analysis manager

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 09:04:34

may be this


WITH SET MySet AS {
[Measures].[CustomerNo].MEMBERS}

MEMBER MEASURES.SETDISTINCTCOUNT AS 
DISTINCTCOUNT(MySet)

SELECT [DATE].[Year].MEMBERS On columns,
MEASURES.SETDISTINCTCOUNT ON ROWS
FROM YourCube