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.
Creating report based on parent-child dimension
Hot Topic
- SQL_NO_DATA with SQLFetch
- The updated BOL can be slipstreamed.
- Install Visual Studio 2005 Rights Issue
- Error: The task with the name "Data Flow Task" and the creation name "DTS.Pipeline.1" is not registered for use on this computer
- *.msg files cannot be searched in SQL 2005 without extended MAPI
- quick transaction inside a stored procedure question
- cannot generate ResultSet code
- SQL express ?
- export/data dump to flat file is too slow
- Different indexes - performance analysis question
SQL Server
- Copy Table from another table
- Attach Database problem
- Unit Testing
- SQL Server 2005 Conflict with Outlook BCM
- Avoid pop up RS reports on sharepoint
- Schema and principals (Sql Server 2005)
- 4 GB Size limitation
- SQL Server 2005 Installation does not install sqldmo.dll
- TEMPDB System Database
- dimensions...how to
Creating report based on parent-child dimension
Masarius
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 ...
melcom
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 voila... 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
Jeff Lundstrom
MikeWakefern
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
gowrishankar
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
tehlike
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
Full_Metal
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,
Haake
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
GunasekaranT
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 Razao” is a parent-child dimension)
WITH
MEMBER [Measures].[Conta Razao Parent Label] as '[ContaRazao].[Default Hierarchy].CurrentMember.Parent.MEMBER_CAPTION'
SELECT
{ [Measures].[Real], [Measures].[Conta Razao Parent Label]} ON COLUMNS,
NON EMPTY {DESCENDANTS([Conta Razao].[Default Hierarchy])} ON ROWS
FROM [Controlo Orcamental]
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_Razao_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
Work2
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 Razao Parent Unique Name] as '[ContaRazao].[Default Hierarchy].CurrentMember.Parent.UniqueName'
MEMBER [Measures].[Conta Razao Current Unique Name] as '[ContaRazao].[Default Hierarchy].CurrentMember.UniqueName'
SELECT
{ [Measures].[Real], [Measures].[Conta Razao Current Unique Name], [Measures].[Conta Razao Parent Unique Name]} ON COLUMNS,
NON EMPTY {DESCENDANTS([Conta Razao].[Default Hierarchy])} ON ROWS
FROM [Controlo Orcamental]
I hope this solves your problem.
Best regards,
Telmo Moreira
Dusty21269
Hello Telmo,
Your Answer solved my problem!! Your great. Thanks for your answer.
Best regards,
Robbert
BijuP
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
ssundan
Thx
wsauer
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