MDX Query - Help needed from experienced

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 [F
irm
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.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...[vbcol=seagreen]
> 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
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(Descendants([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.1ea0dbcf8bc7468e9898e2@.news.microsoft.com...[vbcol=seagreen]
>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].Membe
rs,
> [InGen].CurrentMember.Properties("SharedFlag") = "A" OR
> [InGen].CurrentMember.Properties("SharedFlag") = "Y" ),[Ingen].
1;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...
>