DMR Hirearchy should be cascaded along with other dimensions in Crossatb

[Kiran Kandavalli]

Team,

Could you please suggest me on the below scenario.

1) For Example I have Region Dimension and COUNTRY Dimension

   --> Region Dimension is Relation Model Query Subject

   --> COUNTRY Dimension is DMR Hierarchy it has hierarchy like Country, State, City, County


2) In my report I have dragged

   ---> Region Data item

   ---> COUNTRY Hirearchy Descendants Data item ( Descendants(set(#Promptmany('Parameter','MUN','[COUNTRY HIREARCHY-All]','')#,1,after)

3)  Now the report is like below.

      Region 1 - All Countries

                     All States
         
                         All Cities
                           
                           All Counties

      Product 2 - All Countries
                          All States
                           All cities
                            All Counties

4) The problem is countries should be cascaded based on Region

5) As I used descendants for Country Hierarchy - For Every Region it is displaying ALL the Countries

[MFGF]

Team,

Could you please suggest me on the below scenario.

1) For Example I have Region Dimension and COUNTRY Dimension

   --> Region Dimension is Relation Model Query Subject

   --> COUNTRY Dimension is DMR Hierarchy it has hierarchy like Country, State, City, County


2) In my report I have dragged

   ---> Region Data item

   ---> COUNTRY Hirearchy Descendants Data item ( Descendants(set(#Promptmany('Parameter','MUN','[COUNTRY HIREARCHY-All]','')#,1,after)

3)  Now the report is like below.

      Region 1 - All Countries

                     All States
         
                         All Cities
                           
                           All Counties

      Product 2 - All Countries
                          All States
                           All cities
                            All Counties

4) The problem is countries should be cascaded based on Region

5) As I used descendants for Country Hierarchy - For Every Region it is displaying ALL the Countries

Hi,

If I'm understanding correctly, you're saying you are mixing a relational query item and dimensional hierarchy members from DMR in the same crosstab?

If this is correct, can you explain why? It sounds like a recipe for disaster to me!

Cheers!

MF.

[Kiran Kandavalli]

Hi,

I changed my model to DMR model,

Even then - Descendants function is displaying all the children irrespective of other Dimensions when we drag to the left side in Crosstab.

ABC Hirearchy Query item

descendants(set(#promptmany('p_Tree','MUN','[ABC Layer].[ABC Hierarchy].[ABC Hierarchy].[ABC Hierarchy(All)]->[all]',' ')#),1)

Report Layout

Team Type
Team Name
ABC Hireachy


ISSUE

Descendants function is not showing based on Team Type and Team Name.

For Every Team Name it is showing all the Children underneath the selected value in Tree Prompt.


Thanks!
Kiran

[MFGF]

Hi,

I changed my model to DMR model,

Even then - Descendants function is displaying all the children irrespective of other Dimensions when we drag to the left side in Crosstab.

ABC Hirearchy Query item

descendants(set(#promptmany('p_Tree','MUN','[ABC Layer].[ABC Hierarchy].[ABC Hierarchy].[ABC Hierarchy(All)]->[all]',' ')#),1)

Report Layout

Team Type
Team Name
ABC Hireachy


ISSUE

Descendants function is not showing based on Team Type and Team Name.

For Every Team Name it is showing all the Children underneath the selected value in Tree Prompt.


Thanks!
Kiran

Hi,

Are the Team Type and Team Name levels in the same hierarchy as the item you are referring to? If not, how can your report know which Team Name members the ABC hierarchy members relate to? If they are different dimensions there is no intrinsic parent/child relationship for the members to use. What are you trying to achieve here?

MF.

[Kiran Kandavalli]

Hi,

Yes, Team Type and Team Name are in the same Hierarchy.

We need Report output like this.

If Level 2 is selected in Tree Prompt, I have given the example in the attached file- Comments column if for logic explanation.


Thanks!
Kiran

[MFGF]

Hi,

Yes, Team Type and Team Name are in the same Hierarchy.

Thanks!
Kiran

...and these two levels are in the ABC hierarchy? So the entire set of row members are from the same hierarchy? Can you advise on how the levels are organised?

MF.

[Kiran Kandavalli]

Team Type and Team Name are in One Regular Dimension.

ABC Hierarchy is in other Regular Dimension. I have attached the sample Report Output in my previous Thread. Thanks for looking into this.

[Kiran Kandavalli]

Do you want me to add Team Type and Team Name in ABC Hireachy, if so, in which Level I need in ABC Hireachy.

ABC hierarchy has 5 Levels

[MFGF]

Team Type and Team Name are in One Regular Dimension.

ABC Hierarchy is in other Regular Dimension. I have attached the sample Report Output in my previous Thread. Thanks for looking into this.

This is what I was asking previously. If ABC hierarchy is a different dimension, how can your report know which members from this dimension should or shouldn't be displayed below the Team Type / Team Name members? As they are in different dimensions there is no inherent parent/child relationship your report can use. How do you determine which "children" should be shown and which shouldn't?

Do you want me to add Team Type and Team Name in ABC Hireachy, if so, in which Level I need in ABC Hireachy.

ABC hierarchy has 5 Levels

That's the million-dollar question. I honestly don't know how you expect me to answer this. I don't know your data or your business structures. You haven't told us how members from the ABC dimension relate to members from the Team dimension - if they do at all. You're the only person here who can get access to this information and make the determination on whether the members belong in the same dimension/hierarchy.

Cheers!

MF.

[MFGF]

Kiran Kandavalli wrote in a personal message:

I feel its better to explain you clearly over phone, if you don't mind - can I contact you please

Sorry, Kiran, but that isn't appropriate. I see you work for a large financial institution in the USA, and involving someone from outside your business and outside your country without any form of senior level permission and NDA would probably be a dismissible offence on your part. I haven't disclosed who I work for, and for all you know it may be your employer's biggest competitor. Or not. It simply isn't the right thing to do, though. Discussing in private would also undermine the principles on which this forum is founded - open collaboration to help both members and others who may experience similar issues. Your best course of action is to explain clearly what you are trying to do (without posting up any confidential or sensitive information that may damage your employer).

Cheers!

MF.

[Kiran Kandavalli]

Okay thanks for the Suggestion. I thought of telling you an example and not the exact scenario which I am working for. Just need your expertise based on the example.

[MFGF]

Okay thanks for the Suggestion. I thought of telling you an example and not the exact scenario which I am working for. Just need your expertise based on the example.

Understood. We still don't know what the logic is to determine how members from ABC dimension relate to members from Team dimension though. Can you explain? It's nigh on impossible to suggest a solution without knowing how things like this work in your business and in your data.

MF.

[Kiran Kandavalli]

Sorry for not explain you clearly.

1) I have Table 1

2) I have Table 2

3) I have Table 3

4) I have Table 4

5) I have Table 5 - ABC Hierarchy (Which has level 1 to Level 5 in Columns)


All these above 5 Tables are joined by a Fact Table, Lets Say FACT TABLE 1


Report Design

Tree Prompt is Multi Select


Column 1             Column 2              Column 3            Column 4             Column 5

Table 1 Record 1   Table 2 Record 1   Table 3 Record 1  Table 4 Record 1  ABC Hireachy (First Selected Level )
                                                                                                             (All Levels Under First Selected Level one below other)


Table 1 Record 2   Table 2 Record 2   Table 3 Record 2  Table 4 Record 2  ABC Hireachy (First Selected Level )
                                                                                                              (All Levels Under First Selected Level one below other)


Table 1 Record 1   Table 2 Record 1   Table 3 Record 1  Table 4 Record 1  ABC Hireachy (Second Selected Level )
                                                                                                             (All Levels Under First Selected Level one below other)


Table 1 Record 2   Table 2 Record 2   Table 3 Record 2  Table 4 Record 2  ABC Hireachy (Second Selected Level )
                                                                                                             (All Levels Under First Selected Level one below other)

---
---
---

This data should be shown until all the levels selected are completed.

Please let me know how to design the DMR Hierarchy in Cognos Framework Model and how to handle this solution in Report Studio.

** The Tree Prompt should be option and levels need to be shown in Rows based on selection Criteria ****

Please do let me know if I am not Clear.

Thanks!
Kiran

---

[MFGF]

Sorry for not explain you clearly.

1) I have Table 1

2) I have Table 2

3) I have Table 3

4) I have Table 4

5) I have Table 5 - ABC Hierarchy (Which has level 1 to Level 5 in Columns)


All these above 5 Tables are joined by a Fact Table, Lets Say FACT TABLE 1


Report Design

Tree Prompt is Multi Select


Column 1             Column 2              Column 3            Column 4             Column 5

Table 1 Record 1   Table 2 Record 1   Table 3 Record 1  Table 4 Record 1  ABC Hireachy (First Selected Level )
                                                                                                             (All Levels Under First Selected Level one below other)


Table 1 Record 2   Table 2 Record 2   Table 3 Record 2  Table 4 Record 2  ABC Hireachy (First Selected Level )
                                                                                                              (All Levels Under First Selected Level one below other)


Table 1 Record 1   Table 2 Record 1   Table 3 Record 1  Table 4 Record 1  ABC Hireachy (Second Selected Level )
                                                                                                             (All Levels Under First Selected Level one below other)


Table 1 Record 2   Table 2 Record 2   Table 3 Record 2  Table 4 Record 2  ABC Hireachy (Second Selected Level )
                                                                                                             (All Levels Under First Selected Level one below other)

---
---
---

This data should be shown until all the levels selected are completed.

Please let me know how to design the DMR Hierarchy in Cognos Framework Model and how to handle this solution in Report Studio.

** The Tree Prompt should be option and levels need to be shown in Rows based on selection Criteria ****

Please do let me know if I am not Clear.

Thanks!
Kiran

---

Hi,

Sorry - not clear on the requirement here at all. Is this a list? Crosstab? I'm assuming if it's a crosstab and these are nested row headings, it's more complicated than just dragging in each level? You alluded earlier in the thread to getting more members in the ABC hierarchy than you desired. With different nested dimensions you will get all members by default. You haven't explained how you think members from ABC hierarchy should somehow belong to members from other hierarchies.

MF.

[Lynn]

Please let me know how to design the DMR Hierarchy in Cognos Framework Model and how to handle this solution in Report Studio.

I think you might be approaching this the wrong way around. Typically we design and build a framework model to satisfy a wide variety of report requirements that reflect how the business operates and measures their performance.

By thinking of a particular report first and then asking how to construct the framework model to support just that one report can potentially lead to a lot of models that are each only capable of a very limited degree of reporting. This leads to maintenance issues and potentially negates the "single version of the truth" objective when rules built into the model vary from one to the next.

I don't think you can get any advice on how to achieve a particular reporting requirement if you cannot articulate what your hierarchies look like and, ideally, provide some sample of how the members look within the various levels. I asked you this on a different thread and your response did not sufficiently address what I asked. I didn't respond further because you chose to send me a personal message asking me to "please please" respond which I found a bit pushy. Please review point 2 in the forum etiquette post.

[MFGF]

I didn't respond further because you chose to send me a personal message asking me to "please please" respond which I found a bit pushy. Please review point 2 in the forum etiquette post.

Lynn, thanks for bringing this to my attention. I'm sorry you were put in an awkward position like this. It's not appropriate for other members to send unsolicited personal messages asking for direct help - it puts the recipient (you in this case) in a situation where they feel either obliged to reply or taken advantage of for already trying to help.

Kiran this is the second time (I am aware of) that you have written directly to a member asking for their personal help. This is a community, and there is no obligation for anyone to reply or help if they are unable, unwilling or too pressed for time to respond. Writing to individuals begging them to answer your questions puts them in a situation where they may feel they have no choice but to respond, and that is simply unacceptable. Please do not put other members in this situation again. Nobody on these pages posts answers because they have to - they do it out of kindness and a willingness to help others if they can. To put someone under pressure personally to respond is pushy and takes for granted the kindness that person may already have shown in trying to help. It also might just result in the opposite of the outcome you desire - they decide not to reply to you this time or possibly in future because they feel pressured or taken for granted.

Please respect other members and treat them as you would like to be treated.

MF.

[Kiran Kandavalli]

Sooooo Sorry for the PM's - never repeat it again. Just need to get response from you quickly.

After you suggested I never did that.

Please guide me if the Hierarchy which I have build is wrong. so I can post this in Framework Manager Group.

If the Model is correct I need your expertise here in getting the solution in Report Studio.

Please find the attached file on the Issue which I am facing.

Hope this time I am clear.

Sorry again.
Kiran

[MFGF]

Please guide me if the Hierarchy which I have build is wrong. so I can post this in Framework Manager Group.

If the Model is correct I need your expertise here in getting the solution in Report Studio.

Please find the attached file on the Issue which I am facing.

Hope this time I am clear.

Kiran

Hi,

We're not in a position to advise you on whether your hierarchy is right or wrong. We don't know your business or the way it works or the high-level reporting and analysis requirements the model needs to fulfil or the data and how it is organised.

Did you read Lynn's post from before? This is what she told you:

I think you might be approaching this the wrong way around. Typically we design and build a framework model to satisfy a wide variety of report requirements that reflect how the business operates and measures their performance.

By thinking of a particular report first and then asking how to construct the framework model to support just that one report can potentially lead to a lot of models that are each only capable of a very limited degree of reporting. This leads to maintenance issues and potentially negates the "single version of the truth" objective when rules built into the model vary from one to the next.

I don't think you can get any advice on how to achieve a particular reporting requirement if you cannot articulate what your hierarchies look like and, ideally, provide some sample of how the members look within the various levels.

This is sound advice. You originally posted with an issue about seeing all children from ABC hierarchy below each Team Name, saying you should only see the ABC members based on Team Type and Team Name. We asked you a few times to explain this - how can you determine which children from ABC should or shouldn't be displayed for each Team Name? What is the logic? How can you tell which ABC members should and which shouldn't appear? So far you haven't answered this, and unless you do I can't see any way that someone from outside your business can help you solve this issue.

MF.

[Kiran Kandavalli]

Thanks All! I got the solution. I will post the answer this weekend.