Parent Child Dimension Attribute Hierarchy

I hope I described that right!

I have a dimension attribute that describes a self referential hiearchy in the dimension. I have this setup and working with one exception. When I use this hierarchy I only want members which have children to be displayed at each level instead of all members regardless of whether they have children. How do I do this?

Thanks,

ChrisHey Chris,

If you don't display childless members at each level, where would they go? Aren't they leaf members, just at a higher level in the dimension based on their parents?

Not sure I understand why you would want to hide any members. Can you give an example or some more details?

Dave Fackler
|||I have a similar requirement.

I have a DIM_Customer with a Referred_By_Customer_Key, which stores a value if a particular customer happens to be referred by another customer, otherwise, NULL is stored.

Wizard auto detected that this is a Parent-Child hierarchy as it was a FK to its own PK.

When browsing the Dimension, it is showing
Parent Customer -> Referred Customer Level 1 -> Referred Customer Level 2...

However, a lot of customers havent referred anyone and are having 0 children in the hierarchy. How could these customers be hidden from view?

Using a regular attribute hierarchy seems easier to achieve this. I cannot figure out for Parent-Child.

Parent Child Dimension Attribute Hierarchy

I hope I described that right!

I have a dimension attribute that describes a self referential hiearchy in the dimension. I have this setup and working with one exception. When I use this hierarchy I only want members which have children to be displayed at each level instead of all members regardless of whether they have children. How do I do this?

Thanks,

ChrisHey Chris,

If you don't display childless members at each level, where would they go? Aren't they leaf members, just at a higher level in the dimension based on their parents?

Not sure I understand why you would want to hide any members. Can you give an example or some more details?

Dave Fackler
|||I have a similar requirement.

I have a DIM_Customer with a Referred_By_Customer_Key, which stores a value if a particular customer happens to be referred by another customer, otherwise, NULL is stored.

Wizard auto detected that this is a Parent-Child hierarchy as it was a FK to its own PK.

When browsing the Dimension, it is showing
Parent Customer -> Referred Customer Level 1 -> Referred Customer Level 2...

However, a lot of customers havent referred anyone and are having 0 children in the hierarchy. How could these customers be hidden from view?

Using a regular attribute hierarchy seems easier to achieve this. I cannot figure out for Parent-Child.

parameterized query on sql 2005,2000 strange, simple example

Hi,
Can somebody please explain why the bellow described difference happens when
executing the same query with parameters and without, db set to
compatibility level 2000, so the same happens on sql 2000:
This query with parameters returns some result, although it should return
none:
exec sp_executesql N'select distinct top 50 name from LOCATION where (name
like @.ParamVal) and ( name < @.ParamLess ) order by 1 desc',N'@.ParamVal
nvarchar(4000),@.ParamLess
nvarchar(4000)',@.ParamVal=N'A%',@.ParamLess=N'A-100'
If I substitute parameters with values it returns proper result, i.e.
doesn't return anything as it should be:
select distinct top 50 name from LOCATION where (name like 'A%') and ( name
< 'A-100' ) order by 1 desc
Thank you
Vadim
Figured this out myself,
Thank you
"Vadim" <vadim@.dontsend.com> wrote in message
news:uJIRy1mOHHA.2232@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Can somebody please explain why the bellow described difference happens
> when executing the same query with parameters and without, db set to
> compatibility level 2000, so the same happens on sql 2000:
> This query with parameters returns some result, although it should return
> none:
> exec sp_executesql N'select distinct top 50 name from LOCATION where (name
> like @.ParamVal) and ( name < @.ParamLess ) order by 1 desc',N'@.ParamVal
> nvarchar(4000),@.ParamLess
> nvarchar(4000)',@.ParamVal=N'A%',@.ParamLess=N'A-100'
> If I substitute parameters with values it returns proper result, i.e.
> doesn't return anything as it should be:
> select distinct top 50 name from LOCATION where (name like 'A%') and (
> name < 'A-100' ) order by 1 desc
> Thank you
> Vadim
>
>

parameterized query on sql 2005,2000 strange, simple example

Hi,
Can somebody please explain why the bellow described difference happens when
executing the same query with parameters and without, db set to
compatibility level 2000, so the same happens on sql 2000:
This query with parameters returns some result, although it should return
none:
exec sp_executesql N'select distinct top 50 name from LOCATION where (name
like @.ParamVal) and ( name < @.ParamLess ) order by 1 desc',N'@.ParamVal
nvarchar(4000),@.ParamLess
nvarchar(4000)',@.ParamVal=N'A%',@.ParamLe
ss=N'A-100'
If I substitute parameters with values it returns proper result, i.e.
doesn't return anything as it should be:
select distinct top 50 name from LOCATION where (name like 'A%') and ( name
< 'A-100' ) order by 1 desc
Thank you
VadimFigured this out myself,
Thank you
"Vadim" <vadim@.dontsend.com> wrote in message
news:uJIRy1mOHHA.2232@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Can somebody please explain why the bellow described difference happens
> when executing the same query with parameters and without, db set to
> compatibility level 2000, so the same happens on sql 2000:
> This query with parameters returns some result, although it should return
> none:
> exec sp_executesql N'select distinct top 50 name from LOCATION where (name
> like @.ParamVal) and ( name < @.ParamLess ) order by 1 desc',N'@.ParamVal
> nvarchar(4000),@.ParamLess
> nvarchar(4000)',@.ParamVal=N'A%',@.ParamLe
ss=N'A-100'
> If I substitute parameters with values it returns proper result, i.e.
> doesn't return anything as it should be:
> select distinct top 50 name from LOCATION where (name like 'A%') and (
> name < 'A-100' ) order by 1 desc
> Thank you
> Vadim
>
>

parameterized query on sql 2005,2000 strange, simple example

Hi,
Can somebody please explain why the bellow described difference happens when
executing the same query with parameters and without, db set to
compatibility level 2000, so the same happens on sql 2000:
This query with parameters returns some result, although it should return
none:
exec sp_executesql N'select distinct top 50 name from LOCATION where (name
like @.ParamVal) and ( name < @.ParamLess ) order by 1 desc',N'@.ParamVal
nvarchar(4000),@.ParamLess
nvarchar(4000)',@.ParamVal=N'A%',@.ParamLess=N'A-100'
If I substitute parameters with values it returns proper result, i.e.
doesn't return anything as it should be:
select distinct top 50 name from LOCATION where (name like 'A%') and ( name
< 'A-100' ) order by 1 desc
Thank you
VadimFigured this out myself,
Thank you
"Vadim" <vadim@.dontsend.com> wrote in message
news:uJIRy1mOHHA.2232@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Can somebody please explain why the bellow described difference happens
> when executing the same query with parameters and without, db set to
> compatibility level 2000, so the same happens on sql 2000:
> This query with parameters returns some result, although it should return
> none:
> exec sp_executesql N'select distinct top 50 name from LOCATION where (name
> like @.ParamVal) and ( name < @.ParamLess ) order by 1 desc',N'@.ParamVal
> nvarchar(4000),@.ParamLess
> nvarchar(4000)',@.ParamVal=N'A%',@.ParamLess=N'A-100'
> If I substitute parameters with values it returns proper result, i.e.
> doesn't return anything as it should be:
> select distinct top 50 name from LOCATION where (name like 'A%') and (
> name < 'A-100' ) order by 1 desc
> Thank you
> Vadim
>
>