MS SQL Data Warehousing - MDX Query - Help needed from experienced

Author

MDX Query - Help needed from experienced

saiser

2006-03-29, 3:23 am

Ingen
=====
GA - Georgia
--County1
--- SharedFlag
--County2
--- SharedFlag
NC - North Carolina
--County1
--- SharedFlag
--County2
--- SharedFlag
MD - Maryland
--County1
--- SharedFlag
--County2
--- SharedFlag

Query 1:
Select Non Empty [InGen].[State Name].Members on Columns from [Firm
Reporting]
............................................
Could get all states as columns

Query 2:
with
set [SLA] as 'Filter( [InGen].[County Name].Members,
[InGen].CurrentMember.Properties("SharedFlag") = "A" OR
[InGen].CurrentMember.Properties("SharedFlag") = "Y" )'
Select Non Empty {SLA} On Columns From [Firm Reporting]
......................
Could get all county names those are having SharedProperty of A or Y

Question: How can I get states on columns if any one of their counties has a
Shared Property of A or Y ?

I do not have much experience but I tried hard to get this query
(essentially it is a clubbing the above two queries) but with no luck. Can
any body help me ? Thanks in advance.

Darren Gosbell

2006-04-07, 7:24 am

I think the following should work, drilling up from the counties to the
State Name level.

eg.

with
set [SLA] as 'DrillUpLevel(Filter
( [InGen].[County Name].Members,
[InGen].CurrentMember.Properties("SharedFlag") = "A" OR
[InGen].CurrentMember.Properties("SharedFlag") = "Y" ),[Ingen].[State
Name])'
Select Non Empty {SLA} On Columns From [Firm Reporting]

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <emQQiftUGHA.224@TK2MSFTNGP10.phx.gbl>, "saiser" <norep>
says...[color=darkred]
> Ingen
> =====
> GA - Georgia
> --County1
> --- SharedFlag
> --County2
> --- SharedFlag
> NC - North Carolina
> --County1
> --- SharedFlag
> --County2
> --- SharedFlag
> MD - Maryland
> --County1
> --- SharedFlag
> --County2
> --- SharedFlag
>
> Query 1:
> Select Non Empty [InGen].[State Name].Members on Columns from [Firm
> Reporting]
> ...........................................
> Could get all states as columns
>
>
> Query 2:
> with
> set [SLA] as 'Filter( [InGen].[County Name].Members,
> [InGen].CurrentMember.Properties("SharedFlag") = "A" OR
> [InGen].CurrentMember.Properties("SharedFlag") = "Y" )'
> Select Non Empty {SLA} On Columns From [Firm Reporting]
> .....................
> Could get all county names those are having SharedProperty of A or Y
>
>
> Question: How can I get states on columns if any one of their counties has a
> Shared Property of A or Y ?
>
> I do not have much experience but I tried hard to get this query
> (essentially it is a clubbing the above two queries) but with no luck. Can
> any body help me ? Thanks in advance.
>
>
>

saiser

2006-04-07, 8:23 pm

Darren

The following query helped me (See Deepak's posting at the link below)

........
.......

So based on the above, the flg query tested well and solved my problem.

with
set [SLA] as 'FILTER([InGen].[State Name].Members,
Count(Filter(Descend
ants([InGen].CurrentMember, [InGen].[County Name]),
[InGen].CurrentMember.Properties("SharedFlag") = "A" OR
[InGen].CurrentMember.Properties("SharedFlag") = "Y")) > 0)'
Select Non Empty (SLA) On Columns From [Firm Reporting]

But my initial trials are with drill down and drill up queries. Somehow I
erred in my testing. Anyway many thanks for the hint and I will test with
DrillUpLevel also and can post results soon here.

---saiser [MCSD]

"Darren Gosbell" <jam@newsgroups.nospam> wrote in message
news:MPG. 1ea0dbcf8bc7468e9898
e2@news.microsoft.com...[color=darkred]
>I think the following should work, drilling up from the counties to the
> State Name level.
>
> eg.
>
> with
> set [SLA] as 'DrillUpLevel(Filter
( [InGen].[County Name].Members,
> [InGen].CurrentMember.Properties("SharedFlag") = "A" OR
> [InGen].CurrentMember.Properties("SharedFlag") = "Y" ),[Ingen].[State
> Name])'
> Select Non Empty {SLA} On Columns From [Firm Reporting]
>
> --
> Regards
> Darren Gosbell [MCSD]
> Blog: http://www.geekswithblogs.net/darrengosbell
>
> In article <emQQiftUGHA.224@TK2MSFTNGP10.phx.gbl>, "saiser" <norep>
> says...
>

Sponsored Links