Including "ALL" in dynamic cascaded LOV's

building reports crystal reports
1

I’m developing a report which has dynamic cascaded prompts based of business views. How can we include “ALL” in cascaded LOV’s in the prompts?

rollings007 :poland: (BOB member since 2007-02-15)

2

Does anyone have an answer to this? I have the same issue. I have a cascading dynamic prompt and I need to be able to say all on the top level. I tried creating ‘null’ logic in the Oracle Procedure, but if I check the “null” box on the top level when I run the report it just comes back tot he prompts again.

dmnorman (BOB member since 2005-03-03)

3

I covered how I solved this in a conference presentation a while back. It’s not pretty, and you have to understand (or at least your users have to understand) that selecting “All” at the top level isn’t enough. You still have to go all of the way down to the bottom of the cascading LOV and pick the final answer.

I don’t remember if it’s part one or part two but you can find it in the “Ninja” conference presentations on my blog.

Dave Rathbun :us: (BOB member since 2002-06-06)

4

We created a command in business view to populate the LOV with the “ALL” option. In this case, it is a 2 Level DCP. Here is the SQL.


select distinct
TARGET.NAME,
TARGET_REVISION.REVISION
from
TARGET,
TARGET_REVISION
WHERE
TARGET_REVISION.TARGET_ID=TARGET.TARGET_ID
UNION
SELECT
' *All', ' *All' FROM DUAL
UNION
SELECT DISTINCT ' *All',TARGET_REVISION.REVISION FROM TARGET_REVISION 
UNION
SELECT DISTINCT TARGET.NAME, ' *All' FROM TARGET
order by 1,2

After you have populated the DCP, you need to handle the record selection in CR. Go into Designer > Select Expert and see the logic below.
This will effectively handle the filtering of records if users selected either the ALL option or a particular value from the DCP.


({CountOfCRs.Target} = {?Target} or ' *All' = {?Target}) and
({CountOfCRs.Target Revision} = {?Target Revision}) or ' *All' = {?Target Revision})

AnthonyJ :philippines: (BOB member since 2006-06-30)

5

I can’t help you with this, seeing that you’re using Cascading prompt.
I’ve done something familiar with “Optional prompt” + built in function called HasValue(), maybe someone can find this feature useful somehow.
Take your attention that this method is available staring with Crystal 2008


(not HasValue({?Choose regions}) OR
{Region.Region Name} = {?Choose regions})

Antremons (BOB member since 2006-03-21)