Report Design: Cascading prompt ??

Print Page | Close Window

Cascading prompt ??

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=10233
Printed Date: 28 Apr 2024 at 8:41pm

Topic: Cascading prompt ??

Posted By: flazaro28
Subject: Cascading prompt ??
Date Posted: 11 Jun 2010 at 11:49am

I have created Dynamic Cascaded prompt display in the Business view Manager.

FOr example:

Deleted is the Top prompt (values --> *ALL/Deleted/Not deleted)

Program is its child prompt

Rotation is its sub-Child prompt to program

Based on my selection *ALL or Deleted or Not-Deleted, it would populated the LOV's in my report.

Now , how can I write a formula in record selection box in crystal report, such that when I select appropriate LOV's it would display the corresponding data?

I used this formula, but its not working

If {?Program} = ' *ALL' then true

else if {?Rotation} = ' *ALL' then true

else if {?Deletion} = ' *ALL' then

{Command.PROGRAMTITLE} = {?Program}

else ( {Command.PROGRAMTITLE} = {?Program}

and {Command.ROTATIONTITLE} = {?Rotation} )

Replies:

Posted By: flazaro28
Date Posted: 14 Jun 2010 at 4:35am

Can anyone help me out?

Posted By: flazaro28
Date Posted: 15 Jun 2010 at 9:24am

Can someone help me out?

Posted By: DBlank
Date Posted: 15 Jun 2010 at 9:45am

Sorry, As far as I know you will not be able to do this.

Cascading priompts require the data to exist before the report is generated so your formula will not help.

Selecting a value of ALL (assuming it actually existing in your DB values) will likely make your lower level LOVS null as their is no related fields for ALL.

Posted By: DBlank
Date Posted: 15 Jun 2010 at 9:46am

You can use ALL options in your select statement but not in cascading params.

Posted By: flazaro28
Date Posted: 15 Jun 2010 at 10:42am

Originally posted by DBlank

You can use ALL options in your select statement but not in cascading params.

Thanks DBLANK...Is there any other way we can implement CASCADING "ALL" ?

Posted By: DBlank
Date Posted: 15 Jun 2010 at 10:46am

What exactly do you want your report to do for your end users?

Posted By: flazaro28
Date Posted: 15 Jun 2010 at 10:51am

Originally posted by DBlank

What exactly do you want your report to do for your end users?

Here is the scenario:

Master prompt 1 --> "DELETION_STATUS" has the following Static values

ALL

Deleted

Not-Deleted

When users select ALL, it populates the LOV's in its child parameter "program"

eg: ALL,prog1,prog2,prog3,prog4,prog5

Now, When users select any LOV's listed in Program parameter, it populates the LOV"s in its child parameter "ROTATION"

eg: ALL,Rot1,ROt2,ROt3,ROt4,Rot5,ROt6,ROt7,Rot8,Rot9.

The report should filter the data based on the user selection on the Parameter screen.

Posted By: flazaro28
Date Posted: 15 Jun 2010 at 10:53am

I have already written command SQL in my Union statement.

Now, I want to use some sort of record selection formula to filter the data.

I can't get the formula to work correctly

Posted By: DBlank
Date Posted: 15 Jun 2010 at 11:23am

The record selection is pretty easy using actual cascading prompts is not in this case. If you just use regular params (not a cascading one) it would be easy fairly straight foreward.

Her is what your select statement would look like:

({?Deletion status}='All' or {?Deletion status}={table.deletion_status})

and

({?program}='All' or {?program}={table.Programfield})

and

({?Rotation}='All' or {?Rotation}={table.Rotationfield})

Posted By: flazaro28
Date Posted: 15 Jun 2010 at 12:08pm

Originally posted by DBlank

The record selection is pretty easy using actual cascading prompts is not in this case. If you just use regular params (not a cascading one) it would be easy fairly straight foreward.

Her is what your select statement would look like:

({?Deletion status}='All' or {?Deletion status}={table.deletion_status})

and

({?program}='All' or {?program}={table.Programfield})

and

({?Rotation}='All' or {?Rotation}={table.Rotationfield})

Thanks DBLANK! But there is no such column called Table.deletion_status...I'm populating the static values for deletion_status in business view manager....

Posted By: DBlank
Date Posted: 16 Jun 2010 at 4:11am

Use whatever your data manipulation process is for that in the select expert.

Posted By: flazaro28
Date Posted: 18 Jun 2010 at 7:01am

Originally posted by DBlank

Use whatever your data manipulation process is for that in the select expert.

Hi Dblank...When I select 'Deleted' value from Deleted prompt

& select all from program child prompt

& select specific rotation, it fails to display the resultset in the report..

It is working fine upto 2 level hierarchy...but it fails to display the right data when I select a specific value from the rotation subchild dropdown...

I have used this formula

{?Deletion}=' *All' or {?Deletion} = {Command.DELETION_STATUS})
and
{?program}=' *All' or {?program}={Command.PROGRAMTITLE}
and
{?Rotation}=' *All' or {?Rotation}={Command.ROTATIONTITLE}

Posted By: DBlank
Date Posted: 19 Jun 2010 at 4:00am

You need parenths around each or statement

({?Deletion}=' *All' or {?Deletion} = {Command.DELETION_STATUS})
and
({?program}=' *All' or {?program}={Command.PROGRAMTITLE})
and
({?Rotation}=' *All' or {?Rotation}={Command.ROTATIONTITLE})

Print Page | Close Window