Wildcard in Record Selection option

[antiskeptic]

I'm very frustrated, and am not sure what I am doing wrong.
Below is my formula in the Record Selection of my report:

{PayPeriodEndDate} = {?Pay Period End Date}
and (
(if {?CommunityID} = "" then {CommunityID}like "*"
else {CommunityID}in ({?CommunityID}))

or
(if {?Community} = "" then {CommunityDesc}like "*"
else {CommunityDesc}in ({?Community}))
)

I thought this should give me what I want. Which is the ability to choose a {PayPeriodEndDate} (mandatory) and / or a Community ID or Description (optional). IF this is confusing and I have not made myself clear, please let me know and I will post more details.

Sincerely,
AntiSkeptic

 

[rhinok]

Here's somthing that should work for you. First, I'm assuming that you're allowing a wildcard so that you can return 'all' values, not partial searches.

If this is a text parameter, then add a new entry to your pick list for the {?CommunityId} parameter with a value of 'All' and move it to the top of the list. Uncheck "Allow editing of default values".

Once you've modified the parameter, then use the following record selection criteria:

{PayPeriodEndDate} = {?Pay Period End Date}
and  
(
If
  {?CommunityID} <> 'All'
Then
  {CommunityID} In {?CommunityID}
Else If
  {?CommunityID} = 'All'
Then
  True
)

The code above uses boolean logic. Basically, if the user picks one or more values from the list then those values will be passed to the database. If the user selects 'All', then the entire formula is ignored, thereby returning 'All' records because the CommunityID isn't filtered at all.


~Kurt

 

[antiskeptic]

Maybe I'm missing something...but this does not do what I need of it. When I type in "all"...leave it blank or type *, it brings back no records.
So maybe I should go into further depth to make sure I am addressing all my details.

This report I am working on has a record selection that is looking for a date (End Pay Period) always, no matter what the person who runs the report will always have to put in a date for which they want to see results for. However there are a couple other factors that they may or may not choose to filter by. They can either leave the parameter blank or put in a * for when they want all results for the whole state. OR, they can put in the 4 digit number to specify the community or communities which they would like to see.
OR they can put in the NAMES of the community or communities they wish to see. Anyone got any ideas for the best way to do this??

Thanks in advance,
Antiskeptic

 

[lisanguyen]

Hi,

The formula rhinok gave you should work

Here's a shorter way of writing it:
{PayPeriodEndDate} = {?Pay Period End Date}
and
(
{CommunityID} In {?CommunityID} or
{?CommunityID} = 'All'
)

What happens when you comment out the Pay Period criteria, does your report return anything? Is the CommunityID criteria working correctly?
If is it you might want to bring the CommunityID part of the record selection into a formula and check if you get a return of True for any of the records.
Also watch out for null values - what happens when you check on Convert Null Field Values to Default.

 

[rhinok]

Antiskeptic, please post your entire record selection criteria.

Lisa, your formula will be passed to the database, but you need to make sure the 'Convert NULL Fields to Default' option is checked. I created a simple report against the Northwinds Orders table, using PO# as the parameterized field. With my formula, 2192 records were returned (many PO#s were NULL). With your formula, only 1117 records were displayed until I checked the 'Convert NULL' option.

This is fine, but it may lead to other design issues with your report, so I generally recommend using the full formula.


~Kurt

 

[antiskeptic]

My original Record selection that works fine and how I need it to is as follows: (But of course this only works when people want to pick only one or a few communities, by name or number.)

{PayPeriodEndDate} = {?Pay Period End Date}
and
({CommunityDesc} = {?Community}
or
{CommunityID} = {?CommunityID})

with your selection formula: (since you only addressed one of the two parameters, I only tried that one...just to see if it would work)

{PayPeriodEndDate} = {?Pay Period End Date}
and
(
If
{?CommunityID} <> 'All'
Then
{CommunityID} In {?CommunityID}
Else If
{?CommunityID} = 'All'
Then
True
)

This code, continually brought back 0 records... trying "all", blank and *. I wish it would work for me the way it is working for you in Northwind. But it's just not. And besides that, I still need to take into consideration the other option the Community Description.
Thank you for the suggestions though, both of you. If you have any other ideas I would be grateful.

Sincerely,
Antiskeptic

 

[GJParker]

Antiskeptic

Try This :-

{PayPeriodEndDate} = {?Pay Period End Date}
and
(
If {?CommunityID} <> 'All' Then
{Table.CommunityID} In {?CommunityID}
Else
If {?Community} <> 'All' Then
{Table.Community} In {?Community]
Else
True
)

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[rhinok]

I'm sorry, I missed the second half of your criteria in the first post. Try the following code, I'll explain how it works:

{PayPeriodEndDate} = {?Pay Period End Date}
and  
(
If
  {?CommunityID} <> 'All'
Then
  {CommunityID} In {?CommunityID}
Else If
  {?CommunityID} = 'All'
Then
  True
)

and

(
If
  {?CommunityDesc} <> 'All'
Then
  {Community} In {?CommunityDesc}
Else If
  {?CommunityDesc} = 'All'
Then
  True
)

This code forces Pay Period End date, but allows the user to select 'All' Community IDs or specific Community IDs and 'All' Community Descriptions or specific Community Descriptions. If 'All' is selected for either parameter, then the parameter is basically ignored for the purpose of filtering the data (the same as leaving it blank or using '*', for your purposes).

In order for this to work, you need to have the default value of 'All' be the first value in your pick list. Don't leave the parameter blank and don't use '*'. By having 'All' as the default value, you enforce consistency and standardization. You can enforce this further by having all values listed in the pick list, rather than allowing users to key them in, but this may not be practical depending on the number of entries and how often they may change or be updated. If it is practical, I'd recommend locking down your list by unchecking the 'Allow editing of default values' option.


~Kurt

 

[antiskeptic]

Well, no matter which way I've tried this method, it simply does not work on my report. IF I put in "all" with the above criteria in the Record Selection I get back no records. So that may mean I have something underlying that prevents this from working. I really don't know...but I've fiddled with it for a bit, and found that this works for my needs.

If {?DEPT} = 'All' Then
({@PERIOD_END}={?PERIOD END DATE})

ELSE

({@PERIOD_END}={?PERIOD END DATE}
and {department.department_id} In {?DEPT})

Thanks for the attempts...at least it got me in the ballpark to find something that did work, just as easily.

Thank you,
Antiskeptic