Report Design: Parameter if not entered

Print Page | Close Window

Parameter if not entered

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=20011
Printed Date: 29 Apr 2024 at 12:27am

Topic: Parameter if not entered

Posted By: Minco
Subject: Parameter if not entered
Date Posted: 12 Sep 2013 at 4:58am

I have a parameter that selects a certain customer number, but if the user does not put a value in the parameter [leaves it blank] - I need the report to return ALL customers.

How do I accomplish this?

Thanks-

-------------
Be kind to those less fortunate.

Replies:

Posted By: DBlank
Date Posted: 12 Sep 2013 at 5:34am

are you using 2008?

Posted By: Minco
Date Posted: 12 Sep 2013 at 5:48am

Yes I am....

-------------
Be kind to those less fortunate.

Posted By: DBlank
Date Posted: 12 Sep 2013 at 6:24am

(not(hasvalue(param))

(hasvalue(param) and param=field))

Posted By: Minco
Date Posted: 12 Sep 2013 at 6:26am

This statement goes into the select record formula, I assume?

-------------
Be kind to those less fortunate.

Posted By: DBlank
Date Posted: 12 Sep 2013 at 6:28am

yes

Posted By: DBlank
Date Posted: 12 Sep 2013 at 6:29am

if you have other conditions parenth the whole statement above to keep it together

Posted By: Minco
Date Posted: 12 Sep 2013 at 6:33am

Thanks DBlank, I will try this -- I do have another parameter to consider... the user has the choice of running the report by customer (or) by State, and if either param is blank, the report runs for everyone. How would I specify the State part of it?

-------------
Be kind to those less fortunate.

Posted By: DBlank
Date Posted: 12 Sep 2013 at 6:35am

what happens if they enter both? does one trump the other or do they work in concert?

Posted By: DBlank
Date Posted: 12 Sep 2013 at 6:35am

do they have to enter at least one in either?

Posted By: Minco
Date Posted: 12 Sep 2013 at 6:38am

They would not enter both, and they do not need to enter at least one - in fact, most of the time this runs, there is no specification other than the date range. But, there are a few times when the report needs to be more specific with EITHER a customer OR a state choice.

-------------
Be kind to those less fortunate.

Posted By: DBlank
Date Posted: 12 Sep 2013 at 6:54am

since I assume that they can enter both which do you want to trump the other? Or do you want it to return a warning that they need to fix their selection?

Posted By: Minco
Date Posted: 12 Sep 2013 at 6:59am

I hadn't thought of that, but it would be nice to return a warning that "You cannot select both Customer and State"

-------------
Be kind to those less fortunate.

Posted By: DBlank
Date Posted: 12 Sep 2013 at 7:26am

(

//runs for all records if both are blank

(not(hasvalue(?state) and hasvalue(?client))

//run for only state

(hasvalue(?state) and ?state=table.statefield and not(hasvalue(?client)))

//run for only client

(hasvalue(?client) and ?client=table.clientfield and not(hasvalue(?state)))

)

and any other conditions here

in the reprot you can create a formula as something like:

if hasvalue(?state) and hasvalue(?client) then "Please do not select both a client and state, only select one of the parameters" else ""

and dispaly it at th top of your report with a condition to hide it

Posted By: Minco
Date Posted: 12 Sep 2013 at 7:38am

Thanks again DB - I'll try this out!

-------------
Be kind to those less fortunate.

Posted By: Minco
Date Posted: 12 Sep 2013 at 7:55am

{SERREQUEST.CREDAT_0} = {?Date Range} and
(
//runs for all records if both are blank
(not(hasvalue({?GroupBy BPC}) and not(hasvalue({?Customer BPC}))
or
//runs for only GroupBy
(hasvalue({?GroupBy BPC}) and {?GroupBy BPC} = {BPCUSTOMER.BPCGRU_0} and not(hasvalue({?Customer BPC})))
or
//run for only Customer
(hasvalue({?Customer BPC}) and {?Customer BPC} = {BPCUSTOMER.BPCNUM_0} and not(hasvalue({?GroupBy BPC})))
)

This tells me there is ) missing.... I'm not using the state field, instead I am using a Grouping field that serves as a field to group like-type customers together.

-------------
Be kind to those less fortunate.

Posted By: DBlank
Date Posted: 12 Sep 2013 at 8:03am

{SERREQUEST.CREDAT_0} = {?Date Range} and
(
(not(hasvalue({?GroupBy BPC}) and not(hasvalue({?Customer BPC})))
or
(hasvalue({?GroupBy BPC}) and {?GroupBy BPC} = {BPCUSTOMER.BPCGRU_0} and not(hasvalue({?Customer BPC})))
or
(hasvalue({?Customer BPC}) and {?Customer BPC} = {BPCUSTOMER.BPCNUM_0} and not(hasvalue({?GroupBy BPC})))
)

Posted By: Minco
Date Posted: 12 Sep 2013 at 8:06am

Yes, I did see that after I looked it over for the 8th time.. :)

I made the formula for the warning but I'm having a hard time suppressing it in the report header. You mentioned "hide it" but I'm not familar with hiding.

-------------
Be kind to those less fortunate.

Posted By: DBlank
Date Posted: 12 Sep 2013 at 8:24am

create a second report header

place the formula field in it

select the section expert

select the new report header

click on the formula box (x-2) nex to "suppress(no drill down)"

add your conditional formula here

if your formula evlautes to TRUE it suppresses the section so use

NOT(hasvalue(?state) and hasvalue(?client))

Posted By: Minco
Date Posted: 13 Sep 2013 at 4:07am

My selection criteria is not working as it should. When I put in a customer code or a group customer code, the report returns ALL customers.... no matter what I key in. The top part of this selection is defining the miscellaneous tables that I'm linking to, in order to get descriptions for codes, but the later part is the code that is supposed to single out customer codes when required.

{ATEXTRA2.IDENT1_0} = "447" and
{ATEXTRA2.LANGUE_0} = "ENG" and
{ATEXTRA2.ZONE_0} = "LNGDES" and
{ATEXTRA2.CODFIC_0} = "ATABDIV" and
{ATEXTRA1.IDENT1_0} = "446" and
{ATEXTRA1.LANGUE_0} = "ENG" and
{ATEXTRA1.ZONE_0} = "LNGDES" and
{ATEXTRA1.CODFIC_0} = "ATABDIV" and
{ATEXTRA0.IDENT1_0} = "445" and
{ATEXTRA0.LANGUE_0} = "ENG" and
{ATEXTRA0.ZONE_0} = "LNGDES" and
{ATEXTRA0.CODFIC_0} = "ATABDIV" and
(
//runs for the date range parameter
{SERREQUEST.CREDAT_0} = {?Date Range}
and
//runs for all records if both are blank
(not(hasvalue({?GroupBy BPC}) and not(hasvalue({?Customer BPC}))))
or
//runs for only GroupBy
(hasvalue({?GroupBy BPC}) and {?GroupBy BPC} = {BPCUSTOMER.BPCGRU_0} and not(hasvalue({?Customer BPC})))
or
//run for only Customer
(hasvalue({?Customer BPC}) and {?Customer BPC} = {SERREQUEST.SREBPC_0} and not(hasvalue({?GroupBy BPC})))
)

Am I doing this correctly?

Thanks for your help!

-------------
Be kind to those less fortunate.

Posted By: DBlank
Date Posted: 13 Sep 2013 at 4:22am

try

(

not(hasvalue({?GroupBy BPC}) and hasvalue({?Customer BPC}))

)

or
(

hasvalue({?GroupBy BPC}) and {?GroupBy BPC} = {BPCUSTOMER.BPCGRU_0} and not(hasvalue({?Customer BPC}))

)

(

hasvalue({?Customer BPC}) and {?Customer BPC} = {SERREQUEST.SREBPC_0} and not(hasvalue({?GroupBy BPC}))

)

Posted By: Minco
Date Posted: 13 Sep 2013 at 5:01am

I do not have any records on the report when I refreshed..... and I left the Customer and Group parameters blank - I also tried to select one customer and the report had no records on it (I picked one that I knew should be there).

-------------
Be kind to those less fortunate.

Posted By: DBlank
Date Posted: 13 Sep 2013 at 5:16am

I suspect that the other values are the issue.

change your report to only use the two param conditions and see what you get

{SERREQUEST.CREDAT_0} = {?Date Range}
and
(

(

not(hasvalue({?GroupBy BPC}) and hasvalue({?Customer BPC}))

)

or
(

hasvalue({?GroupBy BPC}) and {?GroupBy BPC} = {BPCUSTOMER.BPCGRU_0} and not(hasvalue({?Customer BPC}))

)

(

hasvalue({?Customer BPC}) and {?Customer BPC} = {SERREQUEST.SREBPC_0} and not(hasvalue({?GroupBy BPC}))

)

Posted By: Minco
Date Posted: 13 Sep 2013 at 5:56am

(
    (
    hasvalue({?Customer BPC})
    )
                    or
    (
    hasvalue({?Customer BPC}) and {?Customer BPC} = {SERREQUEST.SREBPC_0}
    )
)

I removed the other values and then put them back in because it wasn't working without them. I decided to remove any reference to the parameter GroupBy, and the above is my result. This selection give me ALL records even if I specify a customer number.

When I put NOT(hasvalue.... in the first line, I can specify a single customer and get that customer to return on the report, but if I Don't specify a customer, I get nothing on the report.

-------------
Be kind to those less fortunate.

Posted By: DBlank
Date Posted: 13 Sep 2013 at 6:04am

test each piece like you were doing

the first part should only return all values when both params are blank

if either has a value nothing would be returned

(not(hasvalue({?GroupBy BPC}) and hasvalue({?Customer BPC})))

The second part only returns one selected "group bpc" if only the group param has a value and the other is blank

(hasvalue({?GroupBy BPC}) and {?GroupBy BPC} = {BPCUSTOMER.BPCGRU_0} and not(hasvalue({?Customer BPC})))

The last part only returns one selected "customer" if only the customer param has a value and the other is blank

(

hasvalue({?Customer BPC}) and {?Customer BPC} = {SERREQUEST.SREBPC_0} and not(hasvalue({?GroupBy BPC}))

)

Posted By: Minco
Date Posted: 13 Sep 2013 at 6:14am

The first part

(not(hasvalue({?GroupBy BPC}) and hasvalue({?Customer BPC})))

This looks to me like it needs another NOT in the 2nd half if it's looking to see if both values are blank?

When I put the not(hasvalue in the first line, I don't get any records unless I do specify a customer.

-------------
Be kind to those less fortunate.

Posted By: DBlank
Date Posted: 13 Sep 2013 at 7:49am

the NOT on this should apply to both based on the way the pareth is set up

(not(hasvalue({?GroupBy BPC}) and hasvalue({?Customer BPC})))

Posted By: DBlank
Date Posted: 13 Sep 2013 at 7:50am

test it out.

make a formula field and insert that code

place it on the report canvas report header

it should return a result of TRUE if both are blank and FALSE it either has a value

Posted By: Minco
Date Posted: 13 Sep 2013 at 7:58am

Oh, now I see the logic.... but even when I take out the 2nd parameter, I can't get the single parameter to work right. If I could get the 1st one to work OK, I could put the 2nd parameter in.

-------------
Be kind to those less fortunate.

Posted By: DBlank
Date Posted: 13 Sep 2013 at 8:12am

The select statment is just a row evaluation

if it evlauate to TRUE it is included

if False it excludes it.

with the OR statements, as soon as it it finds a TRUE it includes the row and stops reading the rest of the OR options.

To debug your formula create 3 formula fields to test each part separately.

place each of the formula fields in the report header and run different combos of your params to see if they get TRUE and FALSE as expected.

I still think you might have a join issue with your other criteria.

Posted By: Minco
Date Posted: 13 Sep 2013 at 8:20am

OK, I'll try to debug... thank you so much for your input DB - you're always a big help.

-------------
Be kind to those less fortunate.

Posted By: Minco
Date Posted: 18 Sep 2013 at 3:26am

{SERREQUEST.CREDAT_0} = {?Date Range} and
(
//runs for all records if both are blank
(not(hasvalue({?Group Customer}) and not(hasvalue({?Customer BPC}))))
or
//run for only Group
(hasvalue({?Group Customer}) and {?Group Customer}={BPCUSTOMER.BPCGRU_0} and not(hasvalue({?Customer BPC})))
or
//run for only Customer
(hasvalue({?Customer BPC}) and {?Customer BPC}={SERREQUEST.SRENUM_0} and not(hasvalue({?Group Customer})))
)

So I decided to ask IT to give me views for the miscellaneous tables I had to clarify in my previous select statement, so I don't need that part anymore. I've used this select statement and it returns ALL customer records regardless of whether I enter a customer number or a group number specifically. As another note: I have this report grouped by sales SITE so that the records are grouped by the site in which the product was sold. Is this grouping what is causing the problem?

-------------
Be kind to those less fortunate.

Posted By: DBlank
Date Posted: 19 Sep 2013 at 3:26am

You keep trying to add to "Not" conditions in the first evaluation

if you parenth the 2 portions correctly the NOT applies to both parts

I think yours is doing a double negative

try it with only one NOT

{SERREQUEST.CREDAT_0} = {?Date Range} and
(
//runs for all records if both are blank
(not(hasvalue({?Group Customer}) and hasvalue({?Customer BPC})))
or
//run for only Group
(hasvalue({?Group Customer}) and {?Group Customer}={BPCUSTOMER.BPCGRU_0} and not(hasvalue({?Customer BPC})))
or
//run for only Customer
(hasvalue({?Customer BPC}) and {?Customer BPC}={SERREQUEST.SRENUM_0} and not(hasvalue({?Group Customer})))
)

Posted By: Minco
Date Posted: 19 Sep 2013 at 3:31am

When I take out the "Not" condition is the second part of the first evaluation, I don't get any data at all. The IT guy here, believes that the grouping by the site isn't handled well by CR and I've had to make 3 reports: 1 for returning all customers, 1 for returning just information for 1 customer and the last one for returning information for 1 group of customers.

-------------
Be kind to those less fortunate.

Print Page | Close Window