If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Show content of parent node

Started by khoinh, 04 Aug 2010 10:54:02 PM

Previous topic - Next topic

khoinh

Hi,

   I had the tree prompt with structure Level1 - Level 2 - Level 3 - Level 4. When I selected one node of level 4, I would like to show this text "name of its parent in level 2 - name of its parent in level 3 - name of node in level 4" in detail page. Could you help me how to resolve this ?  ::)


Thanks

CognosPaul

I once wrote an article for SupportLink for something like this, it never got approved because I was too busy to make screenshots for it. ::)

Here's the article in full:

Displaying full path of current member in hierarchy

When using an interactive drilldown report or a report with a tree prompt, users can sometimes get lost in the data. For example, if a user were to drill down from 2007 to Q1 to June, he can easily forget which year he is looking at. The problem is further exacerbated if the report output is saved to be later referenced. The solution, therefore, is to build a path to remind the users exactly where they are.

This method will only work for single select prompts.

In the following example, the Sales and Marketing package will be used. The report will have both tree prompts, and have drilling enabled. 

The user specification is a simple revenue crosstab, with the time dimension in the columns, and the retailers in the rows. The report should have a tree prompt for both time and retailers, and he will drill as necessary. 

   1. Begin by creating a new blank report.
   2. Go to query explorer, and drag in a new Query.
   3. Name the query "Prompts", and set Auto Group & Summarize to "No"
   4. Enter the Prompts query and drag in the Year level from the Time hierarchy and Region from the Retailers hierarchy.
   5. Go to Page 1 and drag in a 2 column, 2 row table.
   6. Drag a tree prompt to the upper left cell, create a new parameter Time, and click Finish.
   7. In the properties pane, set the query to Prompts and the Use Value to Year
   8. Drag a tree prompt to the upper right cell, create a new parameter Retailer
   9. In the properties pane, set the query to Prompts and the Use Value to Region

Now that the prompts are set up, the crosstab can now be built.

  10. Drag a crosstab below the table
  11. Drag Revenue into the measure of the crosstab
  12. Drag a Query Calculation into the rows, name it Retailer, and paste in the following prompt:
         1. #prompt('Retailer','memberuniquename','[sales_and_marketing].[Retailers].[Retailers].[Retailers]->:[PC].[@MEMBER].[Retailers]')#
  13. Drag a Query Calculation into the columns, name it Time, and paste in the following prompt:
         1. #prompt('Time','memberuniquename','[sales_and_marketing].[Time].[Time].[Time]->:[PC].[@MEMBER].[Time]')#
  14. Go to Data in the menu bar and click on Drill Behavior. Check "Allow drill-up and drill-down" and click OK
  15. Drag a prompt button under the crosstab, and change the type to Reprompt.  When items are selected in the tree prompt, click on the Reprompt button to show results in the crosstab.

Now would be a good time to run the report to make sure everything works correctly. The user should be able to drill down on both the rows and the columns, or select a specific member in the tree prompt.

Once the report works as expected, the path can be built.

  16. Go to Query 1, which feeds the crosstab, Drag in a new Data Item and paste the following code:
         1. parent(currentmember([sales_and_marketing].[Time].[Time]))
  17. Name the data item TimeParent
  18. Copy/paste the TimeParent twice
  19. You should now have TimeParent, TimeParent1, TimeParent2



  20. Create a new Data item (named RetailerParent) with the following code:
         1. parent(currentmember([sales_and_marketing].[Retailers].[Retailers]))
  21. Copy/paste the RetailerParent three times
  22. You should now have RetailerParent, RetailerParent1, RetailerParent2, RetailerParent3



  23. Go back to page1 and drag in a crosstab into the lower left corner of the table (under the Time tree prompt)
  24. Attach the crosstab to Query1 within the properties pane.
  25. Drag Time into the rows
  26. Drag TimeParent to the right of Time
  27. Drag TimeParent1 to the right of TimeParent
  28. Drag TimeParent2 to the right of TimeParent
  29. Click on one of the rows, and select ancestor à Crosstab Rows, then select Box Type:None
  30. Drag a text item to crosstab columns and click ok
  31. Change the source type to report expression, and enter the following code:
         1. [Query1].[ TimeParent]
  32. Drag another text item to the left of that, change the source type to report expression, and enter the following code:
         1. [Query1].[TimeParent1] + ' => '
  33. Repeat step 32 for  the remaining TimeParent2
  34. Select all of the crosstab spaces and delete the class
  35. Click on the crosstab corner and select Box Type:None
  36. Right click on the crosstab, select fact cells and set the box type to none.

Now would be a good time to run the report to see if the path for time works. If the report runs as expected, a path should appear every time the user drills into a time level.

  37. Repeat the previous section.  but this time create the crosstab in the lower right cell of the table, under the Retailer tree prompt.