How to display data in a hierarchy form in crosstab in report studio

[Shruthi]

Hello Everyone

My requirement is to display the data in a hierarchy way in a crosstab in report studio.I am using the dimensional model in reports.The Data which is selected in a value prompt should display in a hierarchy in crosstab.
For example,if Root Level is Selected then its Level1 data with root level data should display.same if level1 is selected then level2 data should display.(both drill up & drill down should happen).

Thanks in advance for your help.

Regards,
Shrut

[cognostechie]

Shruthi -

I made a report like this with a Tree Prompt, not a Value Prompt. If you select Root Level from the Tree prompt, the report opens up with the Root Level data and from there on, you can drill-down and drill-up.
If you select Level3 from the Promot, the report opens up with the 3rd Level and you can drill-down and drill-up after that.

[Shruthi]

Do We have any other options other than using tree prompt?

If tree prompt is the only options can you brief a bit about the same?

Regards,
Shruthi

[ksvchowdary]

Select the data item , under the Data menu  choose the Drill Behaviour option.Just try the given option there.You get it.

[cognostechie]

Here are the steps:

1> Create a Tree Prompt and point it to use the Root level Data from any heirarchy.
2> Create a Crosstab. Insert a Data Item in the Rows. Double-click the Data Item and edit
the expression as - set([General Ledger Cube].[Accounts].[Functional]->?pAccounts?)
3> Insert some Measure in the Crosstab and something in the Columns.
3>  From 'Data' menu, select 'Drill Behaviour' and check 'Allow Drill-Up and Dril-Down'

The 'set' function will create a set of Members and ?pAccounts? in this case is the
name of the Tree Prompt and also the name of the Parameter which you can specify
while creating the Tree Prompt.

In this case, the Crosstab shows the members of level chosen from the Tree Prompt.
If you want to show the next level too, you can create another Data Item and nest
it next to the 1st Data Item. In the 2nd Data Item you can use the same expression but
put it within another function called 'children'. This will give you the next Level members.

[Shruthi]

Hi

Thanks for the reply.
But my requirement is different.
Example:
I have a value prompt and it contains 2 values i.e product and order.
If i select the product,the product hierarchy should display in the crosstab. (Product hierarchy with some other measures have to display).
Hierarchy should have drillup & drill down capability on the product ,so that the selected level values & other measures should display.

If I am using the drill up & drill down capability root level is replacing with children level.But my requirement is both root & children should display.

Is any way to do this.

Thanks...
Shruthi

[cognostechie]

Ok. In that case, you can do this:

1> Create TWO Crosstabs, the first one for Products and it's measure, the 2nd one for Orders and it's measure.
2> Use Conditional layout to create a variable and use this kind of expression:

    If (ParamDisplayValue(?PromptName?) = 'Orders') then ('Orders') else ('Products')
    Create two values called 'Orders' and 'Products'.

3> Associate each Crosstab with this variable thru a 'Render Variable'. The 1st Crosstab will have Value 'Products' and for 'Oders', make the Box Type to 'None'.
4> The 2nd Crosstab will have Value 'Orders' and for 'Products', make the Box Type to 'None'.

This way, when you choose 'Orders' from the Value Prompt, the 2nd Crosstab will show and the 1st Crosstab will be hidden and vice-versa if you choose 'Products'.

For showing the Root level as well as Children , in the Data menu, there are various options for
the kind of drill-down you want. I think it is replacing , that's why it is showing Root or Children, not both. Play around with those options and you will find the right one.

[Shruthi]

Hi, Thanks for the reply.
Could you please eloborate me on which option will help me to display both root and children while using drill ups and drill downs on the Data item.

Also could you please help me on how to display the hirearchical structure of a Dimension in the Crosstab Rows.

[cognostechie]

I will try it one more time.

Your original post says that you can select not only the Heirarchy but also which level to display.
How would you select Level from a Value Prompt ??

Your later post says you will select Orders/Products from a Value Prompt and the report.

What is the requirement?

If you want to select the beginning Level of a Heirarchy, you will have to use a Tree prompt and use the 'set' function I mentioned in the earlier reply. After that, nest another column in the rows of thr Crosstab and use the 'Children' or 'Descendants' function in conjunction with the 'set' function.

Be clear about the requirements and I will still try to write down the steps.