displaying parameter
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22518
Printed Date: 03 May 2024 at 5:31am
Topic: displaying parameter
Posted By: aqrnl
Subject: displaying parameter
Date 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?
|
Replies:
Posted By: kevlray
Date 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'
|
Posted By: Valert16
Date 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},", ")
|
Posted By: aqrnl
Date 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.
|
Posted By: kevlray
Date 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.
|
Posted By: DBlank
Date 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.
|
Posted By: aqrnl
Date Posted: 10 Jan 2018 at 8:45am
Kevlray,
Codes are added over time so values are dynamic. Thank you for your effort.
|
Posted By: kevlray
Date 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.
|
Posted By: Valert16
Date 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
|
Posted By: lockwelle
Date 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(...)
|
Posted By: aqrnl
Date Posted: 12 Jan 2018 at 5:10am
|
DBlank, your solution worked!! Thank you very much and everyone else who participated.
|
|