Solved

Business Reporter -Using wildcard in a report parrameter

  • 11 January 2023
  • 6 replies
  • 143 views
C
Rank
Badge +2

Has anyone come across a way of using a wildcard (‘%’ OR ‘*’) in the report Parameters so the user can select all without having to type the individual values? 

 

They may need to run the report with one value in the parameter or  with no filter so it gets everything pulling through.

 

Any advice is appreciated. 

Cheers

icon

Best answer by 12Guide_PaulB 11 January 2023, 15:08

View original

6 replies

12Guide_PaulB
Rank
Userlevel 3
Badge +8

Hi,

 

I normally select ‘%’ as default value in the parameter defintion and then apply condition 'LIKE &Parameter’.

 

Best regards, Paul

M
Rank
Userlevel 4
Badge +10

-- Same answer already given 😀

C
Rank
Badge +2

Hi,

 

I normally select ‘%’ as default value in the parameter definition and then apply condition 'LIKE &Parameter’.

 

Best regards, Paul

Cheers Paul, twice you have helped me! Does the like statement still allow for multi values if I enable it in the Parameter area ? 

 

I had for got to say there could be 3 scenarios

  1. single value
  2. Multi selected values 
  3. everything 

As state above I'd imagine it will work. 

 

Thanks for all your help! 

12Guide_PaulB
Rank
Userlevel 3
Badge +8

Hi,

No, you cannot use multiple values in combination with the like statement. If you use multiple values, you can use the IN condition instead. Combining both conditions (LIKE and IN) would be very useful but  isn’t possible as far as I know.

Cheers, Paul

C
Rank
Badge +2

Hi,

No, you cannot use multiple values in combination with the like statement. If you use multiple values, you can use the IN condition instead. Combining both conditions (LIKE and IN) would be very useful but  isn’t possible as far as I know.

Cheers, Paul

Ah that’s a shame, Maybe a future feature. Thanks for your help!

S
Rank
Userlevel 2
Badge +4

Hi!

I think you should be careful with LIKE ‘%’ since LIKE can be very bad from a performance perspective.

BETWEEN can also be bad from a performance perspective. You shoud try to stick with = and IN.

Instead of LIKE &parameter and default value beeing ‘%’ I would use the “ALL” functionality.

The filter would be IN &parameter and the default value for the parameter would be ALL.

When using the “ALL” functionality and value the WHERE statement would not be added to the final SQL -statement. There could be a big difference in the performance when leaving out the WHERE statement for the filter instead of having a WHERE statement with LIKE ‘%’.

/Stefan 

Reply


Terms & ConditionsCookie settings