Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: displaying parameter Post Reply Post New Topic
Page  of 2 Next >>
Author Message
aqrnl
Newbie
Newbie


Joined: 23 Nov 2015
Location: United States
Online Status: Offline
Posts: 6
Quote aqrnl Replybullet Topic: displaying parameter
    Posted: 08 Jan 2018 at 6:55am
I know you can create a formula like the following to list the parameter items chosen.

Join({?Problem Codes},", ")

The problem is that there are a log of codes and if the users chooses all of them there is not enough room to display them. In situations where the user chooses all can Crystal just display "ALL'? If so, how would I do that?
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 08 Jan 2018 at 8:28am
Basically I normally create a value for the parameter list of values called All, but I am not sure (have not tried it) for a dynamic list.

For the All, I use a value of *, then in the selection formula, I use the 'is like' comparison.  Then for display formula, look for the prompt value of 'All'
IP IP Logged
Valert16
Groupie
Groupie
Avatar

Joined: 21 Mar 2016
Location: Spain
Online Status: Offline
Posts: 57
Quote Valert16 Replybullet Posted: 09 Jan 2018 at 12:56am
If you know the maximum number of values (all), you can use a formula to compare the number of chosen values with this maximum.
Let's say there are 7 values to choose. The formula can be:

If Ubound({?Problem Codes}) = 7 Then
    "ALL"
Else
    Join({?Problem Codes},", ")
IP IP Logged
aqrnl
Newbie
Newbie


Joined: 23 Nov 2015
Location: United States
Online Status: Offline
Posts: 6
Quote aqrnl Replybullet Posted: 09 Jan 2018 at 9:36am
Valert16: Your idea worked but is awkward in the long run because codes are added over time. Thank you.

Keviray: Your idea looks like it will work in the long run but I was unable to follow it. Please be more specific as to the selection formula. Thank you.
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 10 Jan 2018 at 5:04am
First off, do you have a list of values for the parameter?  If so, are they static or dynamic? If they are dynamic, I will have to do some testing.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 10 Jan 2018 at 8:45am
MTC - for an 'all' option...
if you add an 'All' selection to the parameter (like from a union statement in a crystal command as a list source) the record selection should be a simple as
(?param= 'All' or ?param = table.field)
The first part meets the condition to not filter but is much more efficient than having to actually meet a LIKE condition.
IP IP Logged
aqrnl
Newbie
Newbie


Joined: 23 Nov 2015
Location: United States
Online Status: Offline
Posts: 6
Quote aqrnl Replybullet Posted: 10 Jan 2018 at 8:45am
Kevlray,

Codes are added over time so values are dynamic. Thank you for your effort.
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 10 Jan 2018 at 10:55am
The other way, which I just thought of.  If you make the parameter optional (I forget what version they made that possible).  Then if the user does not enter anything, then nothing is filtered.
IP IP Logged
Valert16
Groupie
Groupie
Avatar

Joined: 21 Mar 2016
Location: Spain
Online Status: Offline
Posts: 57
Quote Valert16 Replybullet Posted: 11 Jan 2018 at 1:26am
I think the solutions proposed above about adding an 'all' option or using an optional parameter shoud work. I simply add here another possibility, don't know if it'll be helpful.

It's seems clear the parameter must be dynamic because the codes can change. I propose a way to get the total number of codes (a little knowledge of SQL is needed)

Add a new command from database expert. This command must contain a sentence like 'Select Count (Distinct CodeField) as TotalCodes From...' (the syntax may vary with every database). The goal is getting a unique field wich contains the count of distinct codes every time the report is executed. This select statement should be based on the same query that fills the list of codes in the dynamic parameter. Leave this command unlinked from any other table or command you already have.
You may receive some warnings about your link configuration, ignore them.
Now you can detect if all values have been selected, using this formula (Basic syntax):

If Ubound({?Problem Codes}) = {NewCommand.TotalCodes} Then
    formula = "ALL"
Else
    formula = Join({?Problem Codes},", ")
End If
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 11 Jan 2018 at 12:51pm
you could also just cut off the list after x characters...or back track to the last comma and replace with an ellipse(...)
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.