Creating unlimited Nested categories via Stored Procedure

I am trying to create a loop in stored procedure to forumulate a parent/chil
d
( unlimited relationships ). I do not know how to go about doing this in a
stored procedure. I can do it on the frontend but I rather not do it this
way. The following is a sample of the database table:
Categories
---
| CATID | NAME | ParentId |
---
1 Electronics 0
2 Computers 1
3 Cameras 1
4 Sony Cameras 3
5 Clothing 0
6 White TShirt 5
I would like the output to like the following
Electronics
Electronics -> Computers
Electronics -> Cameras
Electronics -> Cameras -> Sony Cameras
Clothing
Clothing -> White TShirt
How can i get this to work via a stored procedure?Something like this would work...
Drop Table #temp
Create Table #temp
(
CatID int,
[Name] varchar(20),
ParentID int
)
insert into #temp values (1, 'Electronics', 0)
insert into #temp values (2, 'Computers', 1)
insert into #temp values (3, 'Cameras', 1)
insert into #temp values (4, 'Sony Cameras', 3)
insert into #temp values (5, 'Clothing', 0)
insert into #temp values (6, 'White TShirt', 5)
Drop Table #temp2
Create Table #temp2
(
CatID int,
[Name] varchar(255),
ParentID int
)
Insert Into #temp2
Select CatID, [Name], ParentID
>From #temp
While @.@.Rowcount > 0
Update t
Set [Name] = t1.[Name] + ' -> ' + t.[Name],
ParentID = t1.ParentID
From #temp2 t Inner Join #temp t1
On t.ParentID = t1.CatID
Select * From #temp2
HTH
Jason|||Procedure nesting is limited to 32 levels in SQL Server (the same goes for
triggers). If your requirements exceed this limit, you'll have to create the
hierarchy in your application.
ML|||Loops!!' Procedural code' !! God! How non-relational!
Get a copy of TREES & HIERARCHIES IN SQL and look up the Nested sets
model.

Creating report based on parent-child dimension

Hi

I have a problem to create a report based on a parent child-dimension

When I go to reporting services, and I create a new report based on a cube, I drag my parent-child dimension to my data-layout. Then the dimension is immediatily ragged down.

Anyone an idea to solve this.

You need to group the data on the UniqueName property and set the parent group expression to the ParentUniqueName property.|||Does anyone have an example to create a report based on a parent-child dimensions

Thx|||Brian

I changed the report with the grouping on uniquename and the parent group on parentuniquename

But now I got only the top level

How can I drill down?
|||I have the same problem and didnt' find a solution for it. What I really want are the little +/- signs so i can open/close the children of the parent.
Didn't find anything ... what I do now is ident the children. This can be done using an expressing in the value property and using the LevelNumber to add spaces in front of the value ...
Not quit what I want ....
If you find a solution please share it here ...|||

hello,

I have a similar problem in RS2005:

In my report I see all levels in one column (How did g4rc manage to see only top level?...). However they are shown in a way that I think that if I only could get it to toggle correctly it will work out fine...

I would like my P/C dimensionto display just as it does when I use AS. Expanding top level (by pressing +), viewing next level down where I can "open" (by pressing +) next level and so on. Does anyone know how this is done or suggestions on how to solve it?

This is the kind of display I would like:

-A 100

-B 50
D 25
E 25

+C 50

Sincerely,

|||

Unfortunately, I’ve now these problems.

To solve these problems I followed these steps:

1. Alter the mdx query

2. Define the parent group in the report layout

3. Define the toggle item

1. Alter the mdx query

a. You should add a calculated member that gets the parent of your current member. Look to the following query (the dimension “Conta Raz?o” is a parent-child dimension)

WITH

MEMBER [Measures].[Conta Raz?o Parent Label] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.MEMBER_CAPTION'

SELECT

{ [Measures].[Real], [Measures].[Conta Raz?o Parent Label]} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

2. Define the parent group in the report layout

a) Select the group in your table, “edit group”, and in “parent group” property select the field that contains the parent of your dimension member (in my case, I select the “=Fields!Conta_Raz?o_Parent_Label.Value”; the query above has a calculated member that gets the parent of the current member )

3. Define the toggle item

a. Select the group in your table, and the “hidden property” of “visibility” should have “=IIF(Level()=0, False,True)” – this means that only the “first parent” is visible

b. In the “toggleItem property select the name of the textbox that contains “the members of the parent-child dimension”)

I hope this is useful for you.

Telmo Moreira

|||

Telmo Moreira wrote:

Unfortunately, I’ve now these problems.

To solve these problems I follow these steps:

1. Alter the mdx query

2. Define the parent group in the report layout

3. Define the toggle item

1. Alter the mdx query

a. You should add a calculated member that gets the parent of your current member. Look to the following query (the dimension “Conta Raz?o” is a parent-child dimension)

WITH

MEMBER [Measures].[Conta Raz?o Parent Label] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.MEMBER_CAPTION'

SELECT

{ [Measures].[Real} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

2. Define the parent group in the report layout

a) Select the group in your table, “edit group”, and in “parent group” property select the field that contains the parent of your dimension member (in my case, I select the “=Fields!Conta_Raz?o_Parent_Label.Value”; the query above has a calculated member that gets the parent of the current member )

3. Define the toggle item

a. Select the group in your table, and in the “properties window” the “hidden property” of “visibility” should have “=IIF(Level()=0, False,True)” – this means that only the “first parent” is visible

b. In the “toggleItem property select the name of the textbox that contains “the members of the parent-child dimension”)

I hope this is useful for you.

Telmo Moreira

Yes this is realy helpfull. But next when you collapse one its straight under the parent one. i want it more to the right. How can i do that for all levels under the parent level?

Kind regards,

Rob

|||

Hi,

In my previous post, I forgot to talk about the "padding property". So, in the textbox that contains “the members of the parent-child dimension”, select the "left property" of the "padding" in the "window properties" and write :
=Level() * 15 & "pt" (Note that this is an expression.)
This means that we indent each member with 15 * LevelN spaces, where LevelN is the current level of that member.

Have a nice day ,

Telmo Moreira

|||

Hello Telmo,

Thanks for your answer!! That works great. Now i have another question. I am trying to do your example with the employees parent child hierarchy in Dim employee from the adventure work cube. And it work also with the toggling. But when a parent name is the same as a child I don’t see the child and is it is being aggregate with the parent. So when you expand one and you sum the child’s you miss some data because the child with the same name as the parent is not showing in the report. I have tried a lot but how can i solve this problem? In the dataset when i run the mdx query i see the child which i miss in the report. I hope you can help me!

Thanks,

Robbert

|||

Hello Robbert,

Try this:

Modify your mdx query and instead of getting the "member caption" of the parent you should get the "unique name". Also you need to create a calculated member that gets the unique name of the current member. Then you must edit the group of your table and set the group expression to the dataset field that has the unique name of the current member; next set "the parent group expression" to the dataset field that has the unique name of the parent.

Take a look to the following query (the modifications are shown in bold)

WITH

MEMBER [Measures].[Conta Raz?o Parent Unique Name] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.UniqueName'

MEMBER [Measures].[Conta Raz?o Current Unique Name] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.UniqueName'

SELECT

{ [Measures].[Real], [Measures].[Conta Raz?o Current Unique Name], [Measures].[Conta Raz?o Parent Unique Name]} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

I hope this solves your problem.

Best regards,

Telmo Moreira

|||

Hello Telmo,

Your Answer solved my problem!! Your great. Thanks for your answer.

Best regards,

Robbert

|||

Hello Telmo,

Everything works fine and all children will be shown. But when a parent has a child there is a toggle item (+) before the parent name. But when a parent has no child there is no toggle item (+) before the parent name. This look weird because it is not easy to see which level is showing. So I want before all members a toggle item (+) also when there is no child. I think this looks much better. But how can I do that? I hope you will help me one more time.

Best regards,

Robbert

|||

Hello Robbert,

I think that It's not possible, because, in this case, the toggle item (+) exists if the member has any child, i.e., there is some kind of group. So, I think that It's not possible. If anyone knows how to do please let me know.

Best regards,

Telmo Moreira

|||

Hello Robbert,

I have good news for you!

You want that every member should have a toggle item (+), even that member has no child, right?

So try this:

1. Insert a new row below the table group

2. the row should have a small height (say 0,1cm)

3. Select the entire row and set the "hidden property" of "visibility" to True. The "toggleitem property" should have the name of the texbox that contains the members of the parent-child dimension.

4. Et voilá... when we expand a member that has any child, we also expand that row; if the member has no child, only the row is expanded. It means that always exist the (+) before a member.

I think this solves your problem.

Best regards,

Telmo Moreira

Creating report based on parent-child dimension

Hi

I have a problem to create a report based on a parent child-dimension

When I go to reporting services, and I create a new report based on a cube, I drag my parent-child dimension to my data-layout. Then the dimension is immediatily ragged down.

Anyone an idea to solve this.You need to group the data on the UniqueName property and set the parent group expression to the ParentUniqueName property.|||Does anyone have an example to create a report based on a parent-child dimensions

Thx|||Brian

I changed the report with the grouping on uniquename and the parent group on parentuniquename

But now I got only the top level

How can I drill down?|||I have the same problem and didnt' find a solution for it. What I really want are the little +/- signs so i can open/close the children of the parent.
Didn't find anything ... what I do now is ident the children. This can be done using an expressing in the value property and using the LevelNumber to add spaces in front of the value ...
Not quit what I want ....
If you find a solution please share it here ...|||

hello,

I have a similar problem in RS2005:

In my report I see all levels in one column (How did g4rc manage to see only top level?...). However they are shown in a way that I think that if I only could get it to toggle correctly it will work out fine...

I would like my P/C dimensionto display just as it does when I use AS. Expanding top level (by pressing +), viewing next level down where I can "open" (by pressing +) next level and so on. Does anyone know how this is done or suggestions on how to solve it?

This is the kind of display I would like:

-A 100

-B 50
D 25
E 25

+C 50

Sincerely,

|||

Unfortunately, I’ve now these problems.

To solve these problems I followed these steps:

1. Alter the mdx query

2. Define the parent group in the report layout

3. Define the toggle item

1. Alter the mdx query

a. You should add a calculated member that gets the parent of your current member. Look to the following query (the dimension “Conta Raz?o” is a parent-child dimension)

WITH

MEMBER [Measures].[Conta Raz?o Parent Label] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.MEMBER_CAPTION'

SELECT

{ [Measures].[Real], [Measures].[Conta Raz?o Parent Label]} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

2. Define the parent group in the report layout

a) Select the group in your table, “edit group”, and in “parent group” property select the field that contains the parent of your dimension member (in my case, I select the “=Fields!Conta_Raz?o_Parent_Label.Value”; the query above has a calculated member that gets the parent of the current member )

3. Define the toggle item

a. Select the group in your table, and the “hidden property” of “visibility” should have “=IIF(Level()=0, False,True)” – this means that only the “first parent” is visible

b. In the “toggleItem property select the name of the textbox that contains “the members of the parent-child dimension”)

I hope this is useful for you.

Telmo Moreira

|||

Telmo Moreira wrote:

Unfortunately, I’ve now these problems.

To solve these problems I follow these steps:

1. Alter the mdx query

2. Define the parent group in the report layout

3. Define the toggle item

1. Alter the mdx query

a. You should add a calculated member that gets the parent of your current member. Look to the following query (the dimension “Conta Raz?o” is a parent-child dimension)

WITH

MEMBER [Measures].[Conta Raz?o Parent Label] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.MEMBER_CAPTION'

SELECT

{ [Measures].[Real} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

2. Define the parent group in the report layout

a) Select the group in your table, “edit group”, and in “parent group” property select the field that contains the parent of your dimension member (in my case, I select the “=Fields!Conta_Raz?o_Parent_Label.Value”; the query above has a calculated member that gets the parent of the current member )

3. Define the toggle item

a. Select the group in your table, and in the “properties window” the “hidden property” of “visibility” should have “=IIF(Level()=0, False,True)” – this means that only the “first parent” is visible

b. In the “toggleItem property select the name of the textbox that contains “the members of the parent-child dimension”)

I hope this is useful for you.

Telmo Moreira

Yes this is realy helpfull. But next when you collapse one its straight under the parent one. i want it more to the right. How can i do that for all levels under the parent level?

Kind regards,

Rob

|||

Hi,

In my previous post, I forgot to talk about the "padding property". So, in the textbox that contains “the members of the parent-child dimension”, select the "left property" of the "padding" in the "window properties" and write :
=Level() * 15 & "pt" (Note that this is an expression.)
This means that we indent each member with 15 * LevelN spaces, where LevelN is the current level of that member.

Have a nice day ,

Telmo Moreira

|||

Hello Telmo,

Thanks for your answer!! That works great. Now i have another question. I am trying to do your example with the employees parent child hierarchy in Dim employee from the adventure work cube. And it work also with the toggling. But when a parent name is the same as a child I don’t see the child and is it is being aggregate with the parent. So when you expand one and you sum the child’s you miss some data because the child with the same name as the parent is not showing in the report. I have tried a lot but how can i solve this problem? In the dataset when i run the mdx query i see the child which i miss in the report. I hope you can help me!

Thanks,

Robbert

|||

Hello Robbert,

Try this:

Modify your mdx query and instead of getting the "member caption" of the parent you should get the "unique name". Also you need to create a calculated member that gets the unique name of the current member. Then you must edit the group of your table and set the group expression to the dataset field that has the unique name of the current member; next set "the parent group expression" to the dataset field that has the unique name of the parent.

Take a look to the following query (the modifications are shown in bold)

WITH

MEMBER [Measures].[Conta Raz?o Parent Unique Name] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.UniqueName'

MEMBER [Measures].[Conta Raz?o Current Unique Name] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.UniqueName'

SELECT

{ [Measures].[Real], [Measures].[Conta Raz?o Current Unique Name], [Measures].[Conta Raz?o Parent Unique Name]} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

I hope this solves your problem.

Best regards,

Telmo Moreira

|||

Hello Telmo,

Your Answer solved my problem!! Your great. Thanks for your answer.

Best regards,

Robbert

|||

Hello Telmo,

Everything works fine and all children will be shown. But when a parent has a child there is a toggle item (+) before the parent name. But when a parent has no child there is no toggle item (+) before the parent name. This look weird because it is not easy to see which level is showing. So I want before all members a toggle item (+) also when there is no child. I think this looks much better. But how can I do that? I hope you will help me one more time.

Best regards,

Robbert

|||

Hello Robbert,

I think that It's not possible, because, in this case, the toggle item (+) exists if the member has any child, i.e., there is some kind of group. So, I think that It's not possible. If anyone knows how to do please let me know.

Best regards,

Telmo Moreira

|||

Hello Robbert,

I have good news for you!

You want that every member should have a toggle item (+), even that member has no child, right?

So try this:

1. Insert a new row below the table group

2. the row should have a small height (say 0,1cm)

3. Select the entire row and set the "hidden property" of "visibility" to True. The "toggleitem property" should have the name of the texbox that contains the members of the parent-child dimension.

4. Et voilá... when we expand a member that has any child, we also expand that row; if the member has no child, only the row is expanded. It means that always exist the (+) before a member.

I think this solves your problem.

Best regards,

Telmo Moreira

|||Hy everybody,

just another question...

Following your instructions it all works fine except for this:
I have a Hierarchy like
[-]1
[-]2
[+]4
5
3
Note that 4 and 5 or 2 and 3 that shuld be at the same leve appear not padded at the same distance (the problem is that [+]or[-] takes sone pixels).

Any Idea to solve!?
|||Sorry ;)

the hierarchy

[-]1
___[-]2
_______[+]4
_______ 5
___ 3

Creating report based on parent-child dimension

Hi

I have a problem to create a report based on a parent child-dimension

When I go to reporting services, and I create a new report based on a cube, I drag my parent-child dimension to my data-layout. Then the dimension is immediatily ragged down.

Anyone an idea to solve this.You need to group the data on the UniqueName property and set the parent group expression to the ParentUniqueName property.|||Does anyone have an example to create a report based on a parent-child dimensions

Thx|||Brian

I changed the report with the grouping on uniquename and the parent group on parentuniquename

But now I got only the top level

How can I drill down?|||I have the same problem and didnt' find a solution for it. What I really want are the little +/- signs so i can open/close the children of the parent.
Didn't find anything ... what I do now is ident the children. This can be done using an expressing in the value property and using the LevelNumber to add spaces in front of the value ...
Not quit what I want ....
If you find a solution please share it here ...|||

hello,

I have a similar problem in RS2005:

In my report I see all levels in one column (How did g4rc manage to see only top level?...). However they are shown in a way that I think that if I only could get it to toggle correctly it will work out fine...

I would like my P/C dimensionto display just as it does when I use AS. Expanding top level (by pressing +), viewing next level down where I can "open" (by pressing +) next level and so on. Does anyone know how this is done or suggestions on how to solve it?

This is the kind of display I would like:

-A 100

-B 50
D 25
E 25

+C 50

Sincerely,

|||

Unfortunately, I’ve now these problems.

To solve these problems I followed these steps:

1. Alter the mdx query

2. Define the parent group in the report layout

3. Define the toggle item

1. Alter the mdx query

a. You should add a calculated member that gets the parent of your current member. Look to the following query (the dimension “Conta Raz?o” is a parent-child dimension)

WITH

MEMBER [Measures].[Conta Raz?o Parent Label] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.MEMBER_CAPTION'

SELECT

{ [Measures].[Real], [Measures].[Conta Raz?o Parent Label]} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

2. Define the parent group in the report layout

a) Select the group in your table, “edit group”, and in “parent group” property select the field that contains the parent of your dimension member (in my case, I select the “=Fields!Conta_Raz?o_Parent_Label.Value”; the query above has a calculated member that gets the parent of the current member )

3. Define the toggle item

a. Select the group in your table, and the “hidden property” of “visibility” should have “=IIF(Level()=0, False,True)” – this means that only the “first parent” is visible

b. In the “toggleItem property select the name of the textbox that contains “the members of the parent-child dimension”)

I hope this is useful for you.

Telmo Moreira

|||

Telmo Moreira wrote:

Unfortunately, I’ve now these problems.

To solve these problems I follow these steps:

1. Alter the mdx query

2. Define the parent group in the report layout

3. Define the toggle item

1. Alter the mdx query

a. You should add a calculated member that gets the parent of your current member. Look to the following query (the dimension “Conta Raz?o” is a parent-child dimension)

WITH

MEMBER [Measures].[Conta Raz?o Parent Label] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.MEMBER_CAPTION'

SELECT

{ [Measures].[Real} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

2. Define the parent group in the report layout

a) Select the group in your table, “edit group”, and in “parent group” property select the field that contains the parent of your dimension member (in my case, I select the “=Fields!Conta_Raz?o_Parent_Label.Value”; the query above has a calculated member that gets the parent of the current member )

3. Define the toggle item

a. Select the group in your table, and in the “properties window” the “hidden property” of “visibility” should have “=IIF(Level()=0, False,True)” – this means that only the “first parent” is visible

b. In the “toggleItem property select the name of the textbox that contains “the members of the parent-child dimension”)

I hope this is useful for you.

Telmo Moreira

Yes this is realy helpfull. But next when you collapse one its straight under the parent one. i want it more to the right. How can i do that for all levels under the parent level?

Kind regards,

Rob

|||

Hi,

In my previous post, I forgot to talk about the "padding property". So, in the textbox that contains “the members of the parent-child dimension”, select the "left property" of the "padding" in the "window properties" and write :
=Level() * 15 & "pt" (Note that this is an expression.)
This means that we indent each member with 15 * LevelN spaces, where LevelN is the current level of that member.

Have a nice day ,

Telmo Moreira

|||

Hello Telmo,

Thanks for your answer!! That works great. Now i have another question. I am trying to do your example with the employees parent child hierarchy in Dim employee from the adventure work cube. And it work also with the toggling. But when a parent name is the same as a child I don’t see the child and is it is being aggregate with the parent. So when you expand one and you sum the child’s you miss some data because the child with the same name as the parent is not showing in the report. I have tried a lot but how can i solve this problem? In the dataset when i run the mdx query i see the child which i miss in the report. I hope you can help me!

Thanks,

Robbert

|||

Hello Robbert,

Try this:

Modify your mdx query and instead of getting the "member caption" of the parent you should get the "unique name". Also you need to create a calculated member that gets the unique name of the current member. Then you must edit the group of your table and set the group expression to the dataset field that has the unique name of the current member; next set "the parent group expression" to the dataset field that has the unique name of the parent.

Take a look to the following query (the modifications are shown in bold)

WITH

MEMBER [Measures].[Conta Raz?o Parent Unique Name] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.UniqueName'

MEMBER [Measures].[Conta Raz?o Current Unique Name] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.UniqueName'

SELECT

{ [Measures].[Real], [Measures].[Conta Raz?o Current Unique Name], [Measures].[Conta Raz?o Parent Unique Name]} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

I hope this solves your problem.

Best regards,

Telmo Moreira

|||

Hello Telmo,

Your Answer solved my problem!! Your great. Thanks for your answer.

Best regards,

Robbert

|||

Hello Telmo,

Everything works fine and all children will be shown. But when a parent has a child there is a toggle item (+) before the parent name. But when a parent has no child there is no toggle item (+) before the parent name. This look weird because it is not easy to see which level is showing. So I want before all members a toggle item (+) also when there is no child. I think this looks much better. But how can I do that? I hope you will help me one more time.

Best regards,

Robbert

|||

Hello Robbert,

I think that It's not possible, because, in this case, the toggle item (+) exists if the member has any child, i.e., there is some kind of group. So, I think that It's not possible. If anyone knows how to do please let me know.

Best regards,

Telmo Moreira

|||

Hello Robbert,

I have good news for you!

You want that every member should have a toggle item (+), even that member has no child, right?

So try this:

1. Insert a new row below the table group

2. the row should have a small height (say 0,1cm)

3. Select the entire row and set the "hidden property" of "visibility" to True. The "toggleitem property" should have the name of the texbox that contains the members of the parent-child dimension.

4. Et voilá... when we expand a member that has any child, we also expand that row; if the member has no child, only the row is expanded. It means that always exist the (+) before a member.

I think this solves your problem.

Best regards,

Telmo Moreira

Creating report based on parent-child dimension

Hi

I have a problem to create a report based on a parent child-dimension

When I go to reporting services, and I create a new report based on a cube, I drag my parent-child dimension to my data-layout. Then the dimension is immediatily ragged down.

Anyone an idea to solve this.

You need to group the data on the UniqueName property and set the parent group expression to the ParentUniqueName property.|||Does anyone have an example to create a report based on a parent-child dimensions

Thx|||Brian

I changed the report with the grouping on uniquename and the parent group on parentuniquename

But now I got only the top level

How can I drill down?
|||I have the same problem and didnt' find a solution for it. What I really want are the little +/- signs so i can open/close the children of the parent.
Didn't find anything ... what I do now is ident the children. This can be done using an expressing in the value property and using the LevelNumber to add spaces in front of the value ...
Not quit what I want ....
If you find a solution please share it here ...|||

hello,

I have a similar problem in RS2005:

In my report I see all levels in one column (How did g4rc manage to see only top level?...). However they are shown in a way that I think that if I only could get it to toggle correctly it will work out fine...

I would like my P/C dimensionto display just as it does when I use AS. Expanding top level (by pressing +), viewing next level down where I can "open" (by pressing +) next level and so on. Does anyone know how this is done or suggestions on how to solve it?

This is the kind of display I would like:

-A 100

-B 50
D 25
E 25

+C 50

Sincerely,

|||

Unfortunately, I’ve now these problems.

To solve these problems I followed these steps:

1. Alter the mdx query

2. Define the parent group in the report layout

3. Define the toggle item

1. Alter the mdx query

a. You should add a calculated member that gets the parent of your current member. Look to the following query (the dimension “Conta Raz?o” is a parent-child dimension)

WITH

MEMBER [Measures].[Conta Raz?o Parent Label] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.MEMBER_CAPTION'

SELECT

{ [Measures].[Real], [Measures].[Conta Raz?o Parent Label]} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

2. Define the parent group in the report layout

a) Select the group in your table, “edit group”, and in “parent group” property select the field that contains the parent of your dimension member (in my case, I select the “=Fields!Conta_Raz?o_Parent_Label.Value”; the query above has a calculated member that gets the parent of the current member )

3. Define the toggle item

a. Select the group in your table, and the “hidden property” of “visibility” should have “=IIF(Level()=0, False,True)” – this means that only the “first parent” is visible

b. In the “toggleItem property select the name of the textbox that contains “the members of the parent-child dimension”)

I hope this is useful for you.

Telmo Moreira

|||

Telmo Moreira wrote:

Unfortunately, I’ve now these problems.

To solve these problems I follow these steps:

1. Alter the mdx query

2. Define the parent group in the report layout

3. Define the toggle item

1. Alter the mdx query

a. You should add a calculated member that gets the parent of your current member. Look to the following query (the dimension “Conta Raz?o” is a parent-child dimension)

WITH

MEMBER [Measures].[Conta Raz?o Parent Label] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.MEMBER_CAPTION'

SELECT

{ [Measures].[Real} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

2. Define the parent group in the report layout

a) Select the group in your table, “edit group”, and in “parent group” property select the field that contains the parent of your dimension member (in my case, I select the “=Fields!Conta_Raz?o_Parent_Label.Value”; the query above has a calculated member that gets the parent of the current member )

3. Define the toggle item

a. Select the group in your table, and in the “properties window” the “hidden property” of “visibility” should have “=IIF(Level()=0, False,True)” – this means that only the “first parent” is visible

b. In the “toggleItem property select the name of the textbox that contains “the members of the parent-child dimension”)

I hope this is useful for you.

Telmo Moreira

Yes this is realy helpfull. But next when you collapse one its straight under the parent one. i want it more to the right. How can i do that for all levels under the parent level?

Kind regards,

Rob

|||

Hi,

In my previous post, I forgot to talk about the "padding property". So, in the textbox that contains “the members of the parent-child dimension”, select the "left property" of the "padding" in the "window properties" and write :
=Level() * 15 & "pt" (Note that this is an expression.)
This means that we indent each member with 15 * LevelN spaces, where LevelN is the current level of that member.

Have a nice day ,

Telmo Moreira

|||

Hello Telmo,

Thanks for your answer!! That works great. Now i have another question. I am trying to do your example with the employees parent child hierarchy in Dim employee from the adventure work cube. And it work also with the toggling. But when a parent name is the same as a child I don’t see the child and is it is being aggregate with the parent. So when you expand one and you sum the child’s you miss some data because the child with the same name as the parent is not showing in the report. I have tried a lot but how can i solve this problem? In the dataset when i run the mdx query i see the child which i miss in the report. I hope you can help me!

Thanks,

Robbert

|||

Hello Robbert,

Try this:

Modify your mdx query and instead of getting the "member caption" of the parent you should get the "unique name". Also you need to create a calculated member that gets the unique name of the current member. Then you must edit the group of your table and set the group expression to the dataset field that has the unique name of the current member; next set "the parent group expression" to the dataset field that has the unique name of the parent.

Take a look to the following query (the modifications are shown in bold)

WITH

MEMBER [Measures].[Conta Raz?o Parent Unique Name] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.UniqueName'

MEMBER [Measures].[Conta Raz?o Current Unique Name] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.UniqueName'

SELECT

{ [Measures].[Real], [Measures].[Conta Raz?o Current Unique Name], [Measures].[Conta Raz?o Parent Unique Name]} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

I hope this solves your problem.

Best regards,

Telmo Moreira

|||

Hello Telmo,

Your Answer solved my problem!! Your great. Thanks for your answer.

Best regards,

Robbert

|||

Hello Telmo,

Everything works fine and all children will be shown. But when a parent has a child there is a toggle item (+) before the parent name. But when a parent has no child there is no toggle item (+) before the parent name. This look weird because it is not easy to see which level is showing. So I want before all members a toggle item (+) also when there is no child. I think this looks much better. But how can I do that? I hope you will help me one more time.

Best regards,

Robbert

|||

Hello Robbert,

I think that It's not possible, because, in this case, the toggle item (+) exists if the member has any child, i.e., there is some kind of group. So, I think that It's not possible. If anyone knows how to do please let me know.

Best regards,

Telmo Moreira

|||

Hello Robbert,

I have good news for you!

You want that every member should have a toggle item (+), even that member has no child, right?

So try this:

1. Insert a new row below the table group

2. the row should have a small height (say 0,1cm)

3. Select the entire row and set the "hidden property" of "visibility" to True. The "toggleitem property" should have the name of the texbox that contains the members of the parent-child dimension.

4. Et voilá... when we expand a member that has any child, we also expand that row; if the member has no child, only the row is expanded. It means that always exist the (+) before a member.

I think this solves your problem.

Best regards,

Telmo Moreira

Creating report based on parent-child dimension

Hi

I have a problem to create a report based on a parent child-dimension

When I go to reporting services, and I create a new report based on a cube, I drag my parent-child dimension to my data-layout. Then the dimension is immediatily ragged down.

Anyone an idea to solve this.

You need to group the data on the UniqueName property and set the parent group expression to the ParentUniqueName property.|||Does anyone have an example to create a report based on a parent-child dimensions

Thx|||Brian

I changed the report with the grouping on uniquename and the parent group on parentuniquename

But now I got only the top level

How can I drill down?
|||I have the same problem and didnt' find a solution for it. What I really want are the little +/- signs so i can open/close the children of the parent.
Didn't find anything ... what I do now is ident the children. This can be done using an expressing in the value property and using the LevelNumber to add spaces in front of the value ...
Not quit what I want ....
If you find a solution please share it here ...|||

hello,

I have a similar problem in RS2005:

In my report I see all levels in one column (How did g4rc manage to see only top level?...). However they are shown in a way that I think that if I only could get it to toggle correctly it will work out fine...

I would like my P/C dimensionto display just as it does when I use AS. Expanding top level (by pressing +), viewing next level down where I can "open" (by pressing +) next level and so on. Does anyone know how this is done or suggestions on how to solve it?

This is the kind of display I would like:

-A 100

-B 50
D 25
E 25

+C 50

Sincerely,

|||

Unfortunately, I’ve now these problems.

To solve these problems I followed these steps:

1. Alter the mdx query

2. Define the parent group in the report layout

3. Define the toggle item

1. Alter the mdx query

a. You should add a calculated member that gets the parent of your current member. Look to the following query (the dimension “Conta Raz?o” is a parent-child dimension)

WITH

MEMBER [Measures].[Conta Raz?o Parent Label]as'[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.MEMBER_CAPTION'

SELECT

{ [Measures].[Real], [Measures].[Conta Raz?o Parent Label]}ONCOLUMNS,

NONEMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])}ONROWS

FROM [Controlo Or?amental]

2. Define the parent group in the report layout

a) Select the group in your table, “edit group”, and in “parent group” property select the field that contains the parent of your dimension member (in my case, I select the “=Fields!Conta_Raz?o_Parent_Label.Value”; the query above has a calculated member that gets the parent of the current member )

3. Define the toggle item

a. Select the group in your table, and the “hidden property” of “visibility” should have “=IIF(Level()=0, False,True)” – this means that only the “first parent” is visible

b. In the “toggleItem property select the name of the textbox that contains “the members of the parent-child dimension”)

I hope this is useful for you.

Telmo Moreira

|||

Telmo Moreira wrote:

Unfortunately, I’ve now these problems.

To solve these problems I follow these steps:

1. Alter the mdx query

2. Define the parent group in the report layout

3. Define the toggle item

1. Alter the mdx query

a. You should add a calculated member that gets the parent of your current member. Look to the following query (the dimension “Conta Raz?o” is a parent-child dimension)

WITH

MEMBER [Measures].[Conta Raz?o Parent Label]as'[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.MEMBER_CAPTION'

SELECT

{ [Measures].[Real}ONCOLUMNS,

NONEMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])}ONROWS

FROM [Controlo Or?amental]

2. Define the parent group in the report layout

a) Select the group in your table, “edit group”, and in “parent group” property select the field that contains the parent of your dimension member (in my case, I select the “=Fields!Conta_Raz?o_Parent_Label.Value”; the query above has a calculated member that gets the parent of the current member )

3. Define the toggle item

a. Select the group in your table, and in the “properties window” the “hidden property” of “visibility” should have “=IIF(Level()=0, False,True)” – this means that only the “first parent” is visible

b. In the “toggleItem property select the name of the textbox that contains “the members of the parent-child dimension”)

I hope this is useful for you.

Telmo Moreira

Yes this is realy helpfull. But next when you collapse one its straight under the parent one. i want it more to the right. How can i do that for all levels under the parent level?

Kind regards,

Rob

|||

Hi,

In my previous post, I forgot to talk about the "padding property". So, in the textbox that contains “the members of the parent-child dimension”, select the "left property" of the "padding" in the "window properties" and write :
=Level() * 15 & "pt" (Note that this is an expression.)
This means that we indent each member with 15 * LevelN spaces, where LevelN is the current level of that member.

Have a nice day ,

Telmo Moreira

|||

Hello Telmo,

Thanks for your answer!! That works great. Now i have another question. I am trying to do your example with the employees parent child hierarchy in Dim employee from the adventure work cube. And it work also with the toggling. But when a parent name is the same as a child I don’t see the child and is it is being aggregate with the parent. So when you expand one and you sum the child’s you miss some data because the child with the same name as the parent is not showing in the report. I have tried a lot but how can i solve this problem? In the dataset when i run the mdx query i see the child which i miss in the report. I hope you can help me!

Thanks,

Robbert

|||

HelloRobbert,

Try this:

Modify your mdx query and instead of getting the "member caption" of the parent you should get the "unique name". Also you need to create a calculated member that gets the unique name of the current member. Then you must edit the group of your table and set the group expression to the dataset field that has the unique name of the current member; next set "the parent group expression" to the dataset field that has the unique name of the parent.

Take a look to the following query (the modifications are shown in bold)

WITH

MEMBER [Measures].[Conta Raz?o Parent Unique Name]as'[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.UniqueName'

MEMBER [Measures].[Conta Raz?o Current Unique Name]as'[ContaRaz?o].[Default Hierarchy].CurrentMember.UniqueName'

SELECT

{ [Measures].[Real], [Measures].[Conta Raz?o Current Unique Name], [Measures].[Conta Raz?o Parent Unique Name]} ONCOLUMNS,

NONEMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])}ONROWS

FROM [Controlo Or?amental]

I hope this solves your problem.

Best regards,

Telmo Moreira

|||

Hello Telmo,

Your Answer solved my problem!! Your great. Thanks for your answer.

Best regards,

Robbert

|||

Hello Telmo,

Everything works fine and all children will be shown. But when a parent has a child there is a toggle item (+) before the parent name. But when a parent has no child there is no toggle item (+) before the parent name. This look weird because it is not easy to see which level is showing. So I want before all members a toggle item (+) also when there is no child. I think this looks much better. But how can I do that? I hope you will help me one more time.

Best regards,

Robbert

|||

Hello Robbert,

I think that It's not possible, because, in this case, the toggle item (+) exists if the member has any child, i.e., there is some kind of group. So, I think that It's not possible. If anyone knows how to do please let me know.

Best regards,

Telmo Moreira

|||

Hello Robbert,

I have good news for you!

You want that every member should have a toggle item (+), even that member has no child, right?

So try this:

1. Insert a new row below the table group

2. the row should have a small height (say 0,1cm)

3. Select the entire row and set the "hidden property" of "visibility" to True. The "toggleitem property" should have the name of the texbox that contains the members of the parent-child dimension.

4. Et voilá... when we expand a member that has any child, we also expand that row; if the member has no child, only the row is expanded. It means that always exist the (+) before a member.

I think this solves your problem.

Best regards,

Telmo Moreira

Creating report based on parent-child dimension

Hi

I have a problem to create a report based on a parent child-dimension

When I go to reporting services, and I create a new report based on a cube, I drag my parent-child dimension to my data-layout. Then the dimension is immediatily ragged down.

Anyone an idea to solve this.

You need to group the data on the UniqueName property and set the parent group expression to the ParentUniqueName property.|||Does anyone have an example to create a report based on a parent-child dimensions

Thx|||Brian

I changed the report with the grouping on uniquename and the parent group on parentuniquename

But now I got only the top level

How can I drill down?
|||I have the same problem and didnt' find a solution for it. What I really want are the little +/- signs so i can open/close the children of the parent.
Didn't find anything ... what I do now is ident the children. This can be done using an expressing in the value property and using the LevelNumber to add spaces in front of the value ...
Not quit what I want ....
If you find a solution please share it here ...|||

hello,

I have a similar problem in RS2005:

In my report I see all levels in one column (How did g4rc manage to see only top level?...). However they are shown in a way that I think that if I only could get it to toggle correctly it will work out fine...

I would like my P/C dimensionto display just as it does when I use AS. Expanding top level (by pressing +), viewing next level down where I can "open" (by pressing +) next level and so on. Does anyone know how this is done or suggestions on how to solve it?

This is the kind of display I would like:

-A 100

-B 50
D 25
E 25

+C 50

Sincerely,

|||

Unfortunately, I’ve now these problems.

To solve these problems I followed these steps:

1. Alter the mdx query

2. Define the parent group in the report layout

3. Define the toggle item

1. Alter the mdx query

a. You should add a calculated member that gets the parent of your current member. Look to the following query (the dimension “Conta Raz?o” is a parent-child dimension)

WITH

MEMBER [Measures].[Conta Raz?o Parent Label] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.MEMBER_CAPTION'

SELECT

{ [Measures].[Real], [Measures].[Conta Raz?o Parent Label]} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

2. Define the parent group in the report layout

a) Select the group in your table, “edit group”, and in “parent group” property select the field that contains the parent of your dimension member (in my case, I select the “=Fields!Conta_Raz?o_Parent_Label.Value”; the query above has a calculated member that gets the parent of the current member )

3. Define the toggle item

a. Select the group in your table, and the “hidden property” of “visibility” should have “=IIF(Level()=0, False,True)” – this means that only the “first parent” is visible

b. In the “toggleItem property select the name of the textbox that contains “the members of the parent-child dimension”)

I hope this is useful for you.

Telmo Moreira

|||

Telmo Moreira wrote:

Unfortunately, I’ve now these problems.

To solve these problems I follow these steps:

1. Alter the mdx query

2. Define the parent group in the report layout

3. Define the toggle item

1. Alter the mdx query

a. You should add a calculated member that gets the parent of your current member. Look to the following query (the dimension “Conta Raz?o” is a parent-child dimension)

WITH

MEMBER [Measures].[Conta Raz?o Parent Label] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.MEMBER_CAPTION'

SELECT

{ [Measures].[Real} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

2. Define the parent group in the report layout

a) Select the group in your table, “edit group”, and in “parent group” property select the field that contains the parent of your dimension member (in my case, I select the “=Fields!Conta_Raz?o_Parent_Label.Value”; the query above has a calculated member that gets the parent of the current member )

3. Define the toggle item

a. Select the group in your table, and in the “properties window” the “hidden property” of “visibility” should have “=IIF(Level()=0, False,True)” – this means that only the “first parent” is visible

b. In the “toggleItem property select the name of the textbox that contains “the members of the parent-child dimension”)

I hope this is useful for you.

Telmo Moreira

Yes this is realy helpfull. But next when you collapse one its straight under the parent one. i want it more to the right. How can i do that for all levels under the parent level?

Kind regards,

Rob

|||

Hi,

In my previous post, I forgot to talk about the "padding property". So, in the textbox that contains “the members of the parent-child dimension”, select the "left property" of the "padding" in the "window properties" and write :
=Level() * 15 & "pt" (Note that this is an expression.)
This means that we indent each member with 15 * LevelN spaces, where LevelN is the current level of that member.

Have a nice day ,

Telmo Moreira

|||

Hello Telmo,

Thanks for your answer!! That works great. Now i have another question. I am trying to do your example with the employees parent child hierarchy in Dim employee from the adventure work cube. And it work also with the toggling. But when a parent name is the same as a child I don’t see the child and is it is being aggregate with the parent. So when you expand one and you sum the child’s you miss some data because the child with the same name as the parent is not showing in the report. I have tried a lot but how can i solve this problem? In the dataset when i run the mdx query i see the child which i miss in the report. I hope you can help me!

Thanks,

Robbert

|||

Hello Robbert,

Try this:

Modify your mdx query and instead of getting the "member caption" of the parent you should get the "unique name". Also you need to create a calculated member that gets the unique name of the current member. Then you must edit the group of your table and set the group expression to the dataset field that has the unique name of the current member; next set "the parent group expression" to the dataset field that has the unique name of the parent.

Take a look to the following query (the modifications are shown in bold)

WITH

MEMBER [Measures].[Conta Raz?o Parent Unique Name] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.UniqueName'

MEMBER [Measures].[Conta Raz?o Current Unique Name] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.UniqueName'

SELECT

{ [Measures].[Real], [Measures].[Conta Raz?o Current Unique Name], [Measures].[Conta Raz?o Parent Unique Name]} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

I hope this solves your problem.

Best regards,

Telmo Moreira

|||

Hello Telmo,

Your Answer solved my problem!! Your great. Thanks for your answer.

Best regards,

Robbert

|||

Hello Telmo,

Everything works fine and all children will be shown. But when a parent has a child there is a toggle item (+) before the parent name. But when a parent has no child there is no toggle item (+) before the parent name. This look weird because it is not easy to see which level is showing. So I want before all members a toggle item (+) also when there is no child. I think this looks much better. But how can I do that? I hope you will help me one more time.

Best regards,

Robbert

|||

Hello Robbert,

I think that It's not possible, because, in this case, the toggle item (+) exists if the member has any child, i.e., there is some kind of group. So, I think that It's not possible. If anyone knows how to do please let me know.

Best regards,

Telmo Moreira

|||

Hello Robbert,

I have good news for you!

You want that every member should have a toggle item (+), even that member has no child, right?

So try this:

1. Insert a new row below the table group

2. the row should have a small height (say 0,1cm)

3. Select the entire row and set the "hidden property" of "visibility" to True. The "toggleitem property" should have the name of the texbox that contains the members of the parent-child dimension.

4. Et voilá... when we expand a member that has any child, we also expand that row; if the member has no child, only the row is expanded. It means that always exist the (+) before a member.

I think this solves your problem.

Best regards,

Telmo Moreira

Creating Parent/Child table with Sum for each group

Hi,
i've been working on this report for the whole day now and have come
to the conclusion that this can't be accieved with Reporting Services
SP1.
Or maybe i'm wrong...
I'm trying to display a report like this:
-- Group 1
-- SubGroup 1,1
-- SubGroup 1,2
-- SubGroup 1,3
-- SubSubGroup 3,1
-- SubSubSubGroup 3,1,1
-- Totals for SubSubSubGroup 3,1,1
-- Total for SubSubGroup 3,1
-- Totals for SubGroup 1,3
-- Total for Group 1
My data looks like this:
Text, Amount, Group, ParentGroup
I have done it like described here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_structure_objects_v1_3cok.asp
The problem is getting the Total to display correctly. I have tried
adding a table footer but this will only display the Total for the
entire table and not for each group.
Anybody know a solution to making this kind of report?
-MartinI had a similar problem. In the end I got around the problem by
creating a query that returned the hierarchised sub totals in the
dataset...this is relatively easy if your datasource is OLAP -
admittedly not so ideal if you are using relational datasources ...|||I found a way to work around it, and almost getting the desired result.
The result looks like this:
-- Group # 1, Total Amount
-- SubGroup # 1.1, Total Amount
-- SubGroup # 1.1.1, Total Amount
-- Element # 1.1.1.1, Amount
-- Element # 1.1.1.2, Amount
-- SubGroup # 1.1.2, Total Amount
-- Element # 1.1.2.1, Amount
I have added a Columns, "Headline", which tells me if a Row is a group
or an Element.
Then when i want the Amount displayed i use this expression:
=IIF(Fields!Headline.Value = 0, Fields!Amount.Value,
(Sum(Fields!Amount.Value, "table2_Details_Group", recursive)))
This seems to be the only way to calculate the Totals for a recursive
table...
-Martin

Creating Parent Child Hierarchies

Hi
I have the following in my cube
Location Dimension containing LocationID and LocationName attributes.
Customer Dimension containing CustomerID,CustomerName....,LocationID
The LocationID in the Location Dimension and Customer Dimension are related to each other.
I want to create a hierarchy with the LocationName in the First Level and the CustomerName in the second level. How to I accomplish this? As i said I already have a relationship defined between the two tables.

Thanks and Regards
Guruprasad Karnik

This is not a recursive hierarchy from what you are describing.

You can find a recursive hierarchy in the employee table in the Adventure works sample data base that is a part of the SQL server 2005 installation. You use parent-child hierarchies with this type of primary key-foreign key relationship in the same table.

If you download the performance guide for SSAS2005 (see the first post in this forum) and have a look at page 82 and further(Referenced relationsships), you will find a lot of helpful information.

HTH

Thomas Ivarsson