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

Tree prompt

Started by szidane, 25 Jul 2007 11:40:04 PM

Previous topic - Next topic

szidane

Can someone please send me a document of how i create a Tree prompt in Report Studio.

For eg: I want to create a prompt that has 2 levels of hirerarchy: Region & sub-region....something like what is listed below:

East
    NY
    NJ
West
    LA
South
    FL

thanx


sir_jeroen

In ReportNet a tree prompt isn't possible...

bdybldr

#2
It can be done.  You have to create a query subject in Framework Manager that will hold the values.  Then create a prompt from that query subject.  You have to be a little creative but it can be done.  Here's the SQL from one query subject that I used as a "tree" prompt.  Also, see attached screenshot.

select
       MASD_SALES_ORG.L1_SALES_ID  as  SalesID,
       '- ' || 'Global User - Global User' as  SalesDescription,
        1 AS SalesLevel
from
       [MSDCOGN].MASD_SALES_ORG MASD_SALES_ORG
where
   MASD_SALES_ORG.SALES_FORCE_ID = 'S002.10.1007.US001' and MASD_SALES_ORG.CURRENT_FLAG = 'Y'
UNION
select
       MASD_SALES_ORG.L2_SALES_ID  as  SalesID,
       '---- ' || rtrim(MASD_SALES_ORG.L2_MGR_NAME) || ' / ' || rtrim(MASD_SALES_ORG.L2_SALES_DESC)  as  SalesDescription,
        2 AS SalesLevel
from
       [MSDCOGN].MASD_SALES_ORG MASD_SALES_ORG
where
   MASD_SALES_ORG.SALES_FORCE_ID = 'S002.10.1007.US001' and MASD_SALES_ORG.CURRENT_FLAG = 'Y'


UNION
select
       MASD_SALES_ORG.L3_SALES_ID  as  SalesID,
       '------- ' || rtrim(MASD_SALES_ORG.L3_MGR_NAME) || ' / ' || rtrim(MASD_SALES_ORG.L3_SALES_DESC)  as  SalesDescription,
       3 AS SalesLevel
from
       [MSDCOGN].MASD_SALES_ORG MASD_SALES_ORG
where
   MASD_SALES_ORG.SALES_FORCE_ID = 'S002.10.1007.US001' and MASD_SALES_ORG.CURRENT_FLAG = 'Y'

UNION
select
       MASD_SALES_ORG.L4_SALES_ID  as  SalesID,
       '---------- ' || rtrim(MASD_SALES_ORG.L4_MGR_NAME) || ' / ' || rtrim(MASD_SALES_ORG.L4_SALES_DESC)  as  SalesDescription,
        4 AS SalesLevel
from
       [MSDCOGN].MASD_SALES_ORG MASD_SALES_ORG
where
   MASD_SALES_ORG.SALES_FORCE_ID = 'S002.10.1007.US001' and MASD_SALES_ORG.CURRENT_FLAG = 'Y'

UNION
select
       MASD_SALES_ORG.L5_SALES_ID  as  SalesID,
       '------------- ' || rtrim(MASD_SALES_ORG.L5_MGR_NAME) || ' / ' || rtrim(MASD_SALES_ORG.L5_SALES_DESC)  as  SalesDescription,
       5 AS SalesLevel
from
       [MSDCOGN].MASD_SALES_ORG MASD_SALES_ORG
where
   MASD_SALES_ORG.SALES_FORCE_ID = 'S002.10.1007.US001' and MASD_SALES_ORG.CURRENT_FLAG = 'Y'

rajvivan

Search in the IBM website, They have described it very beautifully.