Print Page | Close Window

Formula with Select Statement

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=21511
Printed Date: 04 May 2024 at 1:56am


Topic: Formula with Select Statement
Posted By: kurt
Subject: Formula with Select Statement
Date Posted: 01 Jun 2015 at 6:24am
I have a datasource defined by sql command. It produces a table with 4 fields, ID, Category, Item, Checked.

In my report, the id is matched to a record using select expert. In my report I want to display "checked" which is 0 or 1 based on category and item. so I am needing a formula. I want to return chr(168) or chr(252) based on true/false. Here is what I started with, obviously broke.

Select {Command.Checked}
where {Command.category}="Hazards" and {Command.item}="Electric Shock"


Replies:
Posted By: DBlank
Date Posted: 01 Jun 2015 at 7:21am
not following your process here but for display purposes you can create a formula field from your true/false field as
if table.field then chr(252) else chr(168)
then use the wingdings font for that field

Posted By: kurt
Date Posted: 01 Jun 2015 at 7:40am
That helps, but I need to also specify which record to use. Each record will match like 10 categories in the view. I need to select the category which matches "xxx".

if
(
If table.category matches "xxx"
then select table.field
)
then chr(252)
else chr(168)

Posted By: DBlank
Date Posted: 01 Jun 2015 at 7:59am

this appears that you are attempting to replace a join with a formula...?

what is your raw data like?

Posted By: kurt
Date Posted: 01 Jun 2015 at 8:10am
Here is a screen cast which shows the data in SQL and then the crystal reports formula editor. Hopefully makes sense. http://screencast.com/t/Nu5tzRvPAYM5

Posted By: DBlank
Date Posted: 01 Jun 2015 at 8:32am
a formula field is an evaluation for that row of data from the dataset that you have altready defined via the Command object (or other data source) and any select critera (in the select expert).
 
Your
"if criteria then select ..."
does not really make any sense as it is only evaluating for that one row of data which already includes all fields in that row.
Are you trying to get a group level result? Like only a single check box displayed for for all of the one grouping of permitid = 2 ?
 
 

Posted By: kurt
Date Posted: 01 Jun 2015 at 8:48am
permitID = 2 does not produce a unique row in the command dataset. it will produce many matching rows. It is a one to many relationship in the database. The permit has core data (id, date, type, etc) and then the permit has these categories of hazards (electric shock, fire, flooding, etc). In the application the user selects a check box which marks the appropriate hazards. so on the crystal report I want to show a checkbox next to Electric Shock, Fire, etc. The way we have done it in past is to write a subreport for each checkbox on the report. And the subreport will have select expert to identify the exact row of data that applies. This could mean as many as 100 subreports on one crystal report. I was hoping to avoid that. Thanks for your help, I am guessing this is too confusing to handle here.

Posted By: DBlank
Date Posted: 01 Jun 2015 at 9:06am
I think you are way over complicating this.
You are trying to show if a field is checked or not, correct?
You may have to adjust a bit
Group on the permit ID to get keep each permit togther.
group on the Category field.
THis will keep all of the categorie fields for that ticket together
drag and drop the checked field onto the detail section
drag and drop the item field onto the detail section just to the right of the checked field
right click on the checked field
select format field
select common tab
select display string formual field
use
if currentfieldvalue then chr(252) else chr(168)
select Font tab
under font select WingDing
Preview
 

Posted By: kurt
Date Posted: 01 Jun 2015 at 9:51am
I understand where you are going. We have customer requirements that are very specific as to the data formatting on the page. So we insert the check boxes one by one. This is why I was trying to do a record select in a formula. As it is, I have to write a subreport that returns a wingding that I display on the form. this equals like 37 subreports on the one report I am currently working.

Again, thanks for being willing to discuss.

Posted By: DBlank
Date Posted: 01 Jun 2015 at 10:14am
The direction you were going will not work in Crystal.
I would either change your COMMAND object to use  PIVOT and convert your rows into columns (per permitid). You can then drag and drop the fields into placement on a group header footer or detail
 
or
 
You can do what you are trying to do as a group footer data as a ton of running totals using conditional evaluation formulas.
example.
create a new running total
name = HazardElectricShock
Field to summarize= checked
type= maximum
evaluate = use a formula
{Command.category}="Hazards" and {Command.item}="Electric Shock"
reset = on change of group 
place this in the permitid group footer (RTs do not work in headers)
apply the same check box formatting to this RT.
you can now move it around the footer wherever you want. You can add a texct field as the label next it.
you can create one RT per check box and have easy placement on the canvas in the same group footer.
 

Posted By: kurt
Date Posted: 01 Jun 2015 at 10:19am
I will give it a shot tomorrow, thanks so much.

Regards,
Kurt

Posted By: Erik
Date Posted: 05 Jun 2015 at 1:28pm
Originally posted by kurt

That helps, but I need to also specify which record to use. Each record will match like 10 categories in the view. I need to select the category which matches "xxx".

if
(
If table.category matches "xxx"
then select table.field
)
then chr(252)
else chr(168)

What if you Group By the record, and instead of displaying the Category you use a Formula where some sort of calculation occurs.

Formula 1: if Category = "xxx" set someGlobalVariable = true

Formula 2 (to replace Category in the report display): if someGlobalVarialbe = true then table.field + chr(252) else chr(168)

The part that eludes me at the moment is how to set the variable back to false for each iteration, or else somehow have the variable unique to the specific record grouping. This may be a pipe dream but I've done similar things before.


Print Page | Close Window