Parent-Child Hierarchy but reversed(!)

Hi!

I have a report that uses a parent-child hierarchy in a table. It is hidden with the toggle set to itself, and that works perfectly.

However, instead of having it look like this(simple example):

+ Profit

After the + has been clicked:

-Profit
+Sales
+Cost

I would like it to look like this:

+Sales
+Cost
-Profit

Is this possible with when using a parent-child hierarchy?

http://ssasfreak.spaces.live.com/

Have you tried using the group footer row instead of the group header row to control visibility?|||

Yes. That only works when you have the diffrent levels in different fields. Like this:

Level1 Level2 Level2

I use the parentgroup property(for the grouping) with a recordset that looks like this:

LevelID LevelName ParentLevelID

Any ideas?

Parent-child hierarchy & fact data?

I'm new to SSAS & would really appreciate any help!

we have an Employee dimension that contains oranizational structure & has parent-child relationship (employee_id as primary surrogate key & parent_id self referencing employee_id) as Employees hierarchy.

there's a number of linked fact tables that link back to dimEmployee.

here's the problem - when I use MS excel & pivot table & link employee id or name or any attributte of the dimEmployee to my fact tables everything is good, but when I use Employees hierarchy (so that it displays the tree on the left etc) then the linking isn't happening & instead I see only global total for each line.

how can I fix this?!

Thanks!

I'd like to know it too...

I had similar case where I had to add additional column for an operator (+/-) & use accounting intelligence & it did the trick for me.

Parent Child hierarchy view

Is it possible with a datagrid to display a hierarchy view of a dataset
which is held in a single table that have an ID and Parent ID field
giving multiple levels of a hierarchy?
If it is possible how do we do it?
Regards
<<<Bryan>>Found a very useful url
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_structure_objects_v1_3cok.asp

Parent Child Dimension Question

I have a simple parent child dimension set up where the root level records have a ParentID of <null>.

When I browse the hierarchy the root level appear correctly, but when I drill into one of the levels of the hierarchy, the root level element is duplicated at the next level even though there is no such relationship in the dimension.

Here is a simplified example of records in the dimension:
ID Name ParentID
1 Root1 <NULL>
2 Level1 1
3 Root2 <NULL>
4 Level2 3

The hierarchy looks like this when I browse:
All
- Root1
- Root1
- Level1
- Root2
- Root2
- Level2

How can I keep the root elements from showing up at the 2nd level?

Did you set the "MembersWithData" property of the Parent attribute to "NonLeafDataHidden"?

http://msdn2.microsoft.com/en-us/library/ms174919(SQL.90).aspx

>>

MembersWithData

Used by parent attributes to determine whether to display data members for non-leaf members in the parent attribute. This property value is only used when the value of the Usage property is set to Parent, meaning that a parent-child hierarchy has been defined.

>>

|||

I didnt think that was the problem, but I did change the "MembersWithData" property from "NonLeafDataVisible" to "NonLeafDataHidden" and I still have the same problem.

I also noticed that the problem is not isolated to the root level members either, but each level repeats it's parent.

So my hierarchy really looks like:

All
- Root1
+ Root1 (leaf)
- Level1
- Level1 (leaf)
- Root2
+ Root2 (leaf)
- Level2
- Level2 (leaf)

When it should look like

All
- Root1
- Level1
- Leaf1
- Root2
- Level2
- Leaf2

I also have the appropriate primary and foreign key relationships defined in this dimension table.

Any other ideas?

Thanks in advance.

|||

Well, in my case, changing the "MembersWithData" property to "NonLeafDataHidden" fixed this issue (it is "NonLeafDataVisible" by default, according to BOL). So, do you find that switching this property on the Parent attribute has no effect on the visible hierarchy at all - which would be surprising?

PS: you can confirm this in the Adventure Works Employee dimension:

- on the Employees attribute, change the "MembersWithData" property to "NonLeafDataVisible"

- deploy the Employee dimension change and then browse the Employees parent-child hierarchy:

- you should now see each non-leaf node (starting with the root) also listed as a leaf child of itself

- switch the property back to "NonLeafDataHidden", re-deploy the dimension, and check by browsing

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.

Parent Attribute Hierarchy

Does anyone know how to create a parent-child hierarchy that can be expanded in reporting services as well as analysis services? Currently, I have a dimension with three attributes: parent, child, and key. Everything seems to work fine in Analysis Services. However, reporting services doesn't seem to recognize the parent hierarchy as being a true hierarchy.

Any help? Thank you.

Does anyone have an idea or just need some more clarification?

Thanks

|||

There is only sinlge way to create parent-child hierarchy in Analysis Services. You dont have any choice here.

Not sure about Reporting Services and how it is displaying parent-child hierachy. Moving thread to Reporting Services forum.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Parameters...Null as an available value?!?!?

I have a hierarchy of organizations that I need to be able to filter by
in the reports...
For example, an Agency can have many Applications associated with it.
I have three stored procedures, one that gets a list of Agencies, the
other that gets a list of Applications based on the Agency that was
selected, and the third takes a bunch of other parameters and gets all
the matching orders (or whatever) associated with the Agency and
Application (Agency and Application are both input params to the third
stored proc).
What I really want is for the user to be able to not select an Agency,
basically setting it to Null, and letting the stored procedure that
does the query for the report ignore that parameter and get results for
all agencies.
If I set the parameter in the report to null, then I can't have a drop
down list with the Agency names if the user doesn't want to set the
Agency to null. But I can't have both!!!
What is the best procedure for doing this? Returning a -1 record in
the list of Agencies and using that to indicate null within the stored
proc? Any other ideas? I can't return a null record in the stored
proc because the report throws an exception. Does this make sense?
Any help would be appreciated!!! Thanks, BrianI had a similiar problem, I solved it by using 'All' as below:
Select * from MainTable
Where (Agency_Name = @.AgencyName OR @.AgencyName = 'All') AND .....
@.AgencyName is a parameter, you can set the parameter's default value
to All( you can make one dataset for this parameter as below:
Select ID, Name from AgencyTable UNION Select 0,'All'. Then In the
Report Parameters dialogue,Choose available value from query,make All
as default value), then the first part of where clause will always
automatically satisfied unless you choose a different value.
This works great for me, Hope this helps.
Good luck.
Henry
Brian wrote:
> I have a hierarchy of organizations that I need to be able to filter
by
> in the reports...
> For example, an Agency can have many Applications associated with it.
> I have three stored procedures, one that gets a list of Agencies, the
> other that gets a list of Applications based on the Agency that was
> selected, and the third takes a bunch of other parameters and gets
all
> the matching orders (or whatever) associated with the Agency and
> Application (Agency and Application are both input params to the
third
> stored proc).
> What I really want is for the user to be able to not select an
Agency,
> basically setting it to Null, and letting the stored procedure that
> does the query for the report ignore that parameter and get results
for
> all agencies.
> If I set the parameter in the report to null, then I can't have a
drop
> down list with the Agency names if the user doesn't want to set the
> Agency to null. But I can't have both!!!
> What is the best procedure for doing this? Returning a -1 record in
> the list of Agencies and using that to indicate null within the
stored
> proc? Any other ideas? I can't return a null record in the stored
> proc because the report throws an exception. Does this make sense?
> Any help would be appreciated!!! Thanks, Brian|||That's exactly what I ended up doing this morning. Works great so far!
Thanks!
Brian
fanh@.tycoelectronics.com wrote:
> I had a similiar problem, I solved it by using 'All' as below:
> Select * from MainTable
> Where (Agency_Name = @.AgencyName OR @.AgencyName = 'All') AND .....
> @.AgencyName is a parameter, you can set the parameter's default value
> to All( you can make one dataset for this parameter as below:
> Select ID, Name from AgencyTable UNION Select 0,'All'. Then In the
> Report Parameters dialogue,Choose available value from query,make All
> as default value), then the first part of where clause will always
> automatically satisfied unless you choose a different value.
> This works great for me, Hope this helps.
> Good luck.
> Henry
>
> Brian wrote:
> > I have a hierarchy of organizations that I need to be able to
filter
> by
> > in the reports...
> >
> > For example, an Agency can have many Applications associated with
it.
> >
> > I have three stored procedures, one that gets a list of Agencies,
the
> > other that gets a list of Applications based on the Agency that was
> > selected, and the third takes a bunch of other parameters and gets
> all
> > the matching orders (or whatever) associated with the Agency and
> > Application (Agency and Application are both input params to the
> third
> > stored proc).
> >
> > What I really want is for the user to be able to not select an
> Agency,
> > basically setting it to Null, and letting the stored procedure that
> > does the query for the report ignore that parameter and get results
> for
> > all agencies.
> >
> > If I set the parameter in the report to null, then I can't have a
> drop
> > down list with the Agency names if the user doesn't want to set the
> > Agency to null. But I can't have both!!!
> >
> > What is the best procedure for doing this? Returning a -1 record
in
> > the list of Agencies and using that to indicate null within the
> stored
> > proc? Any other ideas? I can't return a null record in the stored
> > proc because the report throws an exception. Does this make sense?
> > Any help would be appreciated!!! Thanks, Brian