I have a graph, which shows one level below the selected level, if lowest level

[xyz]

Hi Gurus,

I have a graph report in Cognos 10.2, which is against SSAS cube datasource. Requirement is graph report has to show one level below the selected level from the Organization Hierarchy, say suppose, if I choose lowest level in the graph, then the graph has to show single member from the lowest level instead of showing blank, if the hierarchy doesn't have data.

Example:

Organization Hierarchy Data Looks like below

Country
  State
    District
       Region
     
Suppose, my selection is 'Country' then the graph will show 'State' level information in the graph.

If I choose, 'Region' level then the graph has to show, only one member from 'Region' level as the this the last level and after that there is no other level.


Thanks & Regards,
XYZ

[MFGF]

Hi Gurus,

I have a graph report in Cognos 10.2, which is against SSAS cube datasource. Requirement is graph report has to show one level below the selected level from the Organization Hierarchy, say suppose, if I choose lowest level in the graph, then the graph has to show single member from the lowest level instead of showing blank, if the hierarchy doesn't have data.

Example:

Organization Hierarchy Data Looks like below

Country
  State
    District
       Region
     
Suppose, my selection is 'Country' then the graph will show 'State' level information in the graph.

If I choose, 'Region' level then the graph has to show, only one member from 'Region' level as the this the last level and after that there is no other level.


Thanks & Regards,
XYZ

Hi,

A little clarification is required here. When you refer to the user choosing a level, what is the mechanism? Are they selecting the level by name, or are they selecting a member from the level?

If they are selecting a member, and they choose (say) a country member, what should be displayed - the children of the country, or all the members from the State level?

If they are choosing an entire level by name, why not just have them choose the level they require? If they choose the Region level, which member should be displayed?

MF.

[xyz]

Hi MFGF,

Thanks for reply MFGF, basically user is selecting a member from the level, where 'Organization Hierarchy' is a tree prompt.

If I am selecting country level member, then report will show all the members from State level.

Say suppose, If I am choosing a member from Region level, which is a last level, then obviously there is no data further, in such case user wanted to show the Region selected member.

Example:

All                            - Country
  Arizona                  - State
    XX                       -  District
      AA                     -  Region
      BB                     -  Region
      CC                     - Region
  Wisconsin             - Country

With the above example, if 'All' is selected by default, in this case graph will display 'Arizona' and 'Wisconsin' children, which are 'State' level.
If I chose 'XX', which is 'District' level data, then graph will show 'AA', 'BB' and 'CC', children which are Region level data.

If I choose 'AA', which is 'Region' level data then graph should display 'AA' only instead of blank.

I have written the expression like below to get the children

children( (#prompt('p_Org','mun','[EM].[Org Hierarchy].[Hierarchy - Org Hierarchy Tree].[(All)]->:[M8].[Org Hierarchy].[Hierarchy - Org Hierarchy Tree].[All]')#)

How to handle the above expression, your help will be much appreciated :)

Thanks & Regards,
XYZ

[MFGF]

Hi MFGF,

Thanks for reply MFGF, basically user is selecting a member from the level, where 'Organization Hierarchy' is a tree prompt.

If I am selecting country level member, then report will show all the members from State level.

Say suppose, If I am choosing a member from Region level, which is a last level, then obviously there is no data further, in such case user wanted to show the Region selected member.

Example:

All                            - Country
  Arizona                  - State
    XX                       -  District
      AA                     -  Region
      BB                     -  Region
      CC                     - Region
  Wisconsin             - Country

With the above example, if 'All' is selected by default, in this case graph will display 'Arizona' and 'Wisconsin' children, which are 'State' level.
If I chose 'XX', which is 'District' level data, then graph will show 'AA', 'BB' and 'CC', children which are Region level data.

If I choose 'AA', which is 'Region' level data then graph should display 'AA' only instead of blank.

I have written the expression like below to get the children

children( (#prompt('p_Org','mun','[EM].[Org Hierarchy].[Hierarchy - Org Hierarchy Tree].[(All)]->:[M8].[Org Hierarchy].[Hierarchy - Org Hierarchy Tree].[All]')#)

How to handle the above expression, your help will be much appreciated :)

Thanks & Regards,
XYZ

Hi,

Ok - I see the technical requirement but I'm struggling to comprehend how this could be at all intuitive for a user. If they select Arizona, the report shows all districts from all states? I imagine that would bamboozle even the most technically savvy of users! I'd normally start trying to figure out a solution at this point, but in this case I really think you ought to go back to your client and make sure that what they are asking for is what they really want. if I were you I would explain how confusing this would be for anyone to use, and see if they can justify why it's needed. My guess is they might have miscommunicated what they want, or else not thought through the implications of their requirement.

MF.

[xyz]

Hi MFGF,

Thanks for the reply. I have put forwarded the message communicated by you to our onsite counterparts, they were saying if possible, it will look better to display the single member of the last selected level. I have communicated to him, if there is no children after the last selected level member, then report will show blank. Onsite counterpart is asking, if possible. I would suggest him, we will show blank only, if there are no children available. What would you suggest?


Thanks & Regards,
XYZ

[MFGF]

Hi MFGF,

Thanks for the reply. I have put forwarded the message communicated by you to our onsite counterparts, they were saying if possible, it will look better to display the single member of the last selected level. I have communicated to him, if there is no children after the last selected level member, then report will show blank. Onsite counterpart is asking, if possible. I would suggest him, we will show blank only, if there are no children available. What would you suggest?


Thanks & Regards,
XYZ

Hi,

Sorry - I'm not quite following your reply here. What is the client saying about the bizarre requirement to select a member then to show the entire level below (ie select Arizona and display all districts from all states)? This is the confusing part. Why not just allow the user to select the members they want to see? What is the business requirement here? Why would a user want to select a state and end up seeing every single district from the entire dimension? It makes no logical sense to me :)

MF.

MF.

[xyz]

Hi MFGF,

Sorry for the confusion, may be the way I have explained might be confusing or incorrect.

Basically If I am selecting the leaf level member from a hierarchy, and If there are no members after that level, I wanted to display the last selected leaf level member, otherwise the report has to display the children's of the selected level member? Am I clear now?

Can you please help me with the expression to be written to meet my desired output?


Thanks for understanding,
XYZ

[MFGF]

Hi MFGF,

Sorry for the confusion, may be the way I have explained might be confusing or incorrect.

Basically If I am selecting the leaf level member from a hierarchy, and If there are no members after that level, I wanted to display the last selected leaf level member, otherwise the report has to display the children's of the selected level member? Am I clear now?

Can you please help me with the expression to be written to meet my desired output?


Thanks for understanding,
XYZ

Do the children members require a summary too? If so, you could use the following expression:

union(children([Your Organization Hierarchy] -> ?p_Org?), [Your Organization Hierarchy] -> ?p_Org?)

Cheers!

MF.

[xyz]

Hi MFGF,

Thanks for reply, I didn't get do the children memebers require a summary too?

union(children([Your Organization Hierarchy] -> ?p_Org?), [Your Organization Hierarchy] -> ?p_Org?)

The above expression gives me a output of the selected member along with the children of the selected member, what I want is to display only the children of the selected value, if there are no childrens for the selected member then I want to display the selected member?

Is it achievable?

Thanks in advance


Thanks & Regards,
XYZ

[MFGF]

Hi MFGF,

Thanks for reply, I didn't get do the children memebers require a summary too?

union(children([Your Organization Hierarchy] -> ?p_Org?), [Your Organization Hierarchy] -> ?p_Org?)

The above expression gives me a output of the selected member along with the children of the selected member, what I want is to display only the children of the selected value, if there are no childrens for the selected member then I want to display the selected member?

Is it achievable?

Thanks in advance


Thanks & Regards,
XYZ

Hi,

Yes - the selected member is the parent of it's children, and therefore the measure values for all the children are summarised into a single value by including the member also.  Eg - the XX district has children of AA, BB and CC. Often it's a requirement to display the children (AA, BB and CC) and also the summarised measure(s) for all of these. You can get the summarised measure(s) by including the XX member. This is what my expression was doing.

Cheers!

MF.

[xyz]

Hi MFGF,

Basically my requirement is display children level information in the chart, not the summarised information of the parent.

In the example provided by you, I don't want XX member information to be displayed in the graph. I want AA, BB and CC to be displayed in the chart. If I select AA, as AA member doesn't have any children below it. I want to display AA memeber only in the chart.

Is it feasible.

Thanks for your patience and guidance.

Thanks & Regards,
XYZ