on 03-17-2014 4:01 PM
I have a column with type. Users who enter data in the database do not always put something in that column as they enter records. I have set up Crystal Reports with parameters so a user can select a certain type and display the records with the criteria chosen. But how do they have the option to select all records where the type field is blank? What we want to know is how many records are being inputted with the field left blank.
Hi Chantel,
You have two choices:
1) Assuming the prompt is dynamic, set the prompt as 'optional' and educate the users that Not selecting a value in the prompt will lead the report to show records where 'type' = Null.
The record selection will look something like this:
if Not(hasValue({?My Parameter}))
then
isnull({Type})
else
{Type} = {?My Parameter}
2) Use a Command Object to populate the List of Values and add a wildcard entry, for e.g: *Nulls*.
The query would look like this:
Select table.type from table
UNION
Select *Nulls* from table
Add this query using the 'Add Command' option and Don't Join it to the other tables.
Create a Dynamic prompt against the field in the Command Object and modify the record selection formula to:
If {?Prompt_Name} = "*Nulls*" then
isnull({Type})
else
{Type} = {?Prompt_Name}
-Abhilash
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
It's easier when the list of Static.
Just add a new Value called "Nulls" and modify the Record Selection Formula to:
If {?Prompt_Name} = "Nulls" then
isnull({Type})
else
{Type} IN {?Prompt_Name}
This will return records where Type is null when a users selects 'Nulls' otherwise it returns whatever 'types' the user chooses.
-Abhilash
If you wish to return Null values 'within the date range', then use this:
(
If {?Prompt_Name} = "Nulls" then
isnull({Type})
else
{Type} IN {?Prompt_Name}
)
AND
{Date_field} IN {?DateRange}
If you wish to return all Null values in the table, in other words ignore the Date Range filter and return all nulls, use this:
(
If {?Prompt_Name} = "Nulls" then
isnull({Type})
else
(
{Type} IN {?Prompt_Name}
AND
{Date_field} IN {?DateRange}
)
-Abhilash
Using IF statements in the selection criteria can slow down a report that runs against a large data set because Crystal will pull all of the data into memory and filter it there instead of sending the filter to the database. If this is the case in your situation, try doing the following instead:
(
({?Prompt_Name} = "Nulls" and isNull({Type})) or
{Type} in {?Prompt_Name}
)
Note where I've put the parentheses - these are very important for this to work correctly.
-Dell
My date range is anything greater than 1/1/12. This is what I have so far. It keeps giving me errors to fix with no luck when I try.
{TASKS.REQDATE} >= DateTime (2012, 01, 01, 00, 00, 00) and
(
If {?type} = "Nulls" then
isnull({TASKS.TYPE})
else
{TASKS.TYPE} IN {?Type}
)
AND
{TASKS.REQDATE} IN {TASKS.REQDATE} >= DateTime (2012, 01, 01, 00, 00, 00)
When I use what you just corrected the formula doesn't have errors, but when I try to run the report the parameter, I get an error. This is when I try to do get the null results.
It says:Parameter has no value.
Then it gives me the formula editor. The bold item is what it has highlighted.
{TASKS.REQDATE} >= DateTime (2012, 01, 01, 00, 00, 00) and
(
If {?type} = "Nulls" then
isnull({TASKS.TYPE})
else
{TASKS.TYPE} IN {?Type}
I may need to explain better.
I run a parameter with a type that does have something in that column such as type test. I get the results I want. When I want to get a type that doesn't have something for that column, I don't. What we are finding is there are too many records with the type being left blank.
I want only records after 1/1/12 no matter what parameter I use.
My parameter is static with the ability for the user to enter their own (in case I forgot a type or a new type is added). If it were not static the user's list would be long with outdated types we no longer use. We stopped using many types at the end of 2012.
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.