Dynamic Parameter CR2013 1

[IanWaterman]

I am trying to create a dynamic parameter to show a list of Branches.

This subquery generates what I need including an ALL option and when I join to my data I can enter 0 for All or a specific branch number
Query built on Oracle 11

select ID BRANCHID, NAME BRANCHNAME, SHORTNAME
from fossil.LST_Branches br
where not(ID in (1, 14, 8,13, 15, 22, 24, 25))
--and pi_branch = 0 or ID = i_branch)
Union All
Select 0, 'ALL', 'ALL' from Dual
order by BRANCHNAME

I do not want to create a list of branches from the main report because I am using a Command and it is too slow

I can use the subquery as a factored table or CTE using a with statement but that does not seem to help.
We have crystal reports Server so I can build a parameter in the repository somewhere if required.

I am sure it can be done I just can't remember how!!

Thank you

Ian

 

[kray4660]

You should be able to build a separate command for your list of branches that then you can use as a LOV for a prompt. Crystal Reports will complain about an unlinked data source, but can ignore that message.

 

[IanWaterman]

I knew it was simple. Worked like a charm

Thanks for your help.

Ian