Parameters - defaulting to all 3

[Hillary]

I have a report which has fours parameters; School, Grade, Gender and Ethnicity.

If a user does not select any criteria for the parameters, the report will automatically pick the first School(304), the first Grade(Kindergarten), the first Gender(female) and the first Ethnicity(Asian) and return only those students (Female Asian Kindergarteners in School 304).

Is there a way to have the report default to all students and then only filter if the parameter is used?

For example, if I ran the report and the only parameter I used was Grades 9, 10 & 11, is there a way to have all students in Grades 9, 10 & 11 return regardless of their School, Gender or Ethnicity?

Thanks!

Hillary

Hillary

 

[synapsevampire]

Sure, set the default value to "All".

Then in your Report->Edit selection formula->Record place:

(
if {?MyState} <> "All" then
{table.state} = {?MyState}
else
if {?MyState} = "All" then
true
)
and
...do the same for the other parameters...

Note that I intentionally construct it this way to try to assure SQL pass through to the database.

There are other ways that work depending upon your version of Crystal, please remember to always post your software version.

-k

 

[Hillary]

Sorry, I forgot to include our Crystal version...again : ) Thanks for the reminder.

CR 9.0

This kinda works. I am just starting with Ethnicity. My formula is...

{CSL_STD_DEMO_DISTRICT.GENDER} = {?Gender} and
{CSL_SET_SCHOOL_INFO.SCHOOL_CODE} = {?School Code} and
{CSL_STD_DEMO_SCHOOL.ACTIVE_STATUS} = "Y" and
{@Grade} = {?Grade} and
(
If {?Ethnicity} <> ["N/A", "White", "Native American", "Hispanic", "Hawaiian or Pacific Islander", "Black", "Asian"] then
{CSL_SET_ETHNIC_CATEGORY.ETHNIC_CATEGORY_DESC} = {?Ethnicity}
else
If {?Ethnicity} = ["N/A", "White", "Native American", "Hispanic", "Hawaiian or Pacific Islander", "Black", "Asian"] then
true
)

When I run the report without selecting a parameter, all ethnicities return on the report : ) However, I went into the parameter and checked Allow multiple values, and then Crystal balked at my formula. Crystal returned the error, This array must be subscripted. For example: Array. Crystal also highlights the information in the brackets...["N/A", "White", "Native American", "Hispanic", "Hawaiian or Pacific Islander", "Black", "Asian"]

When I have gotten that error in the past I just remove the brackets but I can't do that in this case because there is a list.

Thanks for your continued help!

Hillary

 

[synapsevampire]

That's because you're altering the parameter to an array when it becomes a multiple values.

I gave you a suggestion and you went with something else, not sure why.

Why not just add in a default value of All as the first choice and use my solution?

-k

 

[Hillary]

Hi, I tried to exactly what you said. Please tell me what's wrong. I think when you stated "All" that ment ["N/A", "White", "Native American", "Hispanic", "Hawaiian or Pacific Islander", "Black", "Asian"]...all the Ethnicities but maybe I misinterpreted you.

The only reason I went in and checked multi values is because I don't always need the report by all Ethnicities (hence the parameter) but I will sometimes need the report by more than one ethnicity (for example all black and hispanic students). I stated in the first post that I would need to run this by multiple grades. If I don't go in and check Allow multiple values I don't know how to get more than one value without getting them all.

Again, thank you. The information you have provided thus far has been very helpful : )



Hillary

 

[ianoctdec]

Hi Hilary,
Why do you need to compare the parameter with all the default values in the record selection formula?
Just write the formula as Synapsevampire sugested, and it won't complain if about multiple values.
Another way to write this record selection formula:
({CSL_SET_ETHNIC_CATEGORY.ETHNIC_CATEGORY_DESC} = {?Ethnicity} or {?Ethnicity} = 'ALL')

Daniela

 

[Hillary]

It didn't work at first then I went into the Parameter>Edit Default Values and added "All" (which Synapsevampire also said to do - I was just too focused on the formula part) to the list and moved "All" to the top of the default list. That, along with Synapsevampire's formula worked!

I never knew Crystal would process "All" as all records and not just a string.

Thanks!



Hillary

 

[synapsevampire]

Crystal doesn't process 'All' as all.

You need to make it a choice, and code as I'd described.

Here's my record selection FAQ (a bit out of date), should prove useful to scan it:

faq767-3825

-k