Dynamic Cascading Prompts

21 Views

Hi ALL,

I am trying to create a Dynamic Cascading LOV:

Environments

Sun Solaris 10

Business Objects XI R2

Crystal Reports XI R2

Business View XI R2

Weblogic 9.2

Prompt needed

(OrganizationActive) -> Valid Values are ...ALL, Active and InActive

(Organization) Valid Values ...ALL and any relation to OrganizationActive

(LocationActive) -> Valid Values are ...ALL, Active and InActive

(Location) Valid Values ...ALL and any relation to LocationActive

(SurveyActive) -> Valid Values are ...ALL, Active and InActive

(Survey_ID) Values ...ALL and any relation to SurveyActive

The Crystal Report will select the correct data based upon the Survey_ID(s)

Here is the Code I am using to create a View with all of the data I need.

SELECT distinct 
  Case When (Select Inactive From MEDGATE_V60_APP.M_TBLOrganization Q Where Q.Organization = IHSV.Organization) = 0 then 'Active' 
       Else  'InActive' 
       End as OrganizationActive, 
  IHSV.Organization, 
  (Select OrganizationName From MEDGATE_V60_APP.M_TBLOrganization Q Where Q.Organization = IHSV.Organization) as OrganizationName, 
  Case When (Select TO_CHAR(Inactive) From MEDGATE_V60_APP.M_TBLLOCATION Q Where Q.Location = IHSV.Location) = 0 then 'Active' 
       Else  'InActive' 
        End as LocationInactive, 
   IHSV.Location, 
  (Select LocationName From MEDGATE_V60_APP.M_TBLLOCATION Q Where Q.Location = IHSV.Location) as LocationName, 
  Case When TO_CHAR(IHSV.Inactive) = '0' then 'Active' 
       Else  'InActive' 
        End as SurveyInactive, 
       IHSV.Survey_ID, IHSV.SurveyName    
From MEDGATE_V60_APP.IHSurvey IHSV 
UNION ALL 
Select distinct 'u2026ALL', 'u2026ALL', 'u2026ALL', 'u2026ALL', 'u2026ALL', 'u2026ALL', 'u2026ALL', 'u2026ALL', 'u2026ALL' 
From MEDGATE_V60_APP.IHSurvey IHSV 
Order by OrganizationActive

I am not sure if I am building this correctly.

I am trying to create a meaningfull cascasding prompt(s)

Thanks,

Rick