Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Crystal Reports: Crystal syntax query issue

Posted on 2012-08-21
14
Medium Priority
?
477 Views
Last Modified: 2012-08-21
Experts,

I have a report where I want a single formula that will list multiple records listed vertically.

This formula will only list the 1st record.

if (
{Command.CAB_APPROVED} = "Yes-National"
   )
then

"Ticket ID:  " & {Command.TICKET_ID} & "  (" &{Command.STATUS}& ")"& chr(10)
& "MNT Category:  " &{Command.MAINTENANCE_CATEGORY} & chr(10)
& "Start/Date:  " &{Command.SCHEDULED_START_DATE_TIME} & chr(10)
& "End/Date:  " &{Command.SCHEDULED_END_DATE_TIME} & chr(10)
& "Group:  " & {Command.COMBINED_GROUP_NAME} & chr(10)
& "Contact at Start:  " &{@CALLED_START_TIME} & chr(10)
& "Contact at End:  " &{@CALLED_END_TIME} & chr(10)
& "-----------------------------------------------------" & chr(10)
ELSE
"No pending tickets."& chr(10)
;
---------------------------------------------------------

Output desired:
Ticket ID:  MNT-753962  (Assigned)
MNT Category:  Video-GQAM-Add GQAM (2804)
Start/Date:  8/21/2012   3:00:00PM
End/Date:  8/21/2012   5:00:00PM
Group:  XXXX
Contact at Start:  No
Contact at End:  No
-----------------------------------------------------
Ticket ID:  MNT-750424  (Assigned)
MNT Category:  Voice-Other-General Platform Maintenance (2740)
Start/Date:  8/21/2012   9:00:00AM
End/Date:  8/21/2012   3:00:00PM
Group:  XXXX
Contact at Start:  No
Contact at End:  No
-----------------------------------------------------
Ticket ID:  MNT-744805  (Completed)
MNT Category:  Video-Analog-FCC Testing / Fly Over Preparation (2839)
Start/Date:  8/21/2012   1:00:00AM
End/Date:  8/21/2012   6:00:00AM
Group:  XXXX
Contact at Start:  Yes
Contact at End:  No


I thought of adding a stringVar but not sure why it only works when I put in in the details section, but not in any other report section.

Here is the formula with string var.

Global StringVar strNamesConCatTktID;

if (
{Command.CAB_APPROVED} = "Yes-National"
   )
then

strNamesConCatTktID :=  strNamesConCatTktID & "Ticket ID:  " & {Command.TICKET_ID} & "  (" &{Command.STATUS}& ")"& chr(10)
& "MNT Category:  " &{Command.MAINTENANCE_CATEGORY} & chr(10)
& "Start/Date:  " &{Command.SCHEDULED_START_DATE_TIME} & chr(10)
& "End/Date:  " &{Command.SCHEDULED_END_DATE_TIME} & chr(10)
& "Group:  " & {Command.COMBINED_GROUP_NAME} & chr(10)
& "Contact at Start:  " &{@CALLED_START_TIME} & chr(10)
& "Contact at End:  " &{@CALLED_END_TIME} & chr(10)
& "-----------------------------------------------------" & chr(10)
ELSE
"No pending tickets."& chr(10)
;
0
Comment
Question by:Maliki Hassani
  • 9
  • 3
  • 2
14 Comments
 
LVL 18

Assisted Solution

by:UnifiedIS
UnifiedIS earned 400 total points
ID: 38316612
I would build 2 sections:
1st section will have a text box for each field you are showing
The text boxes will be like this "Ticket ID:" {Command.TicketID}
The other section will have the text "No pending tickets."

Now use your formula ({Command.CAB_APPROVED} = "Yes-National") to suppress one section or the other
0
 

Author Comment

by:Maliki Hassani
ID: 38316631
Hmm..  The problem is that I am using this formula  for the email sent from a server.
0
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 38316758
I don't understand what you are saying.  How is email involved?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:Maliki Hassani
ID: 38316774
The formula field will be used to populate the body of the email.
0
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 38316862
So you need all the records to end up in a single string?
0
 

Author Comment

by:Maliki Hassani
ID: 38316873
yes, that is correct.
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 1600 total points
ID: 38317333
Try this

In the report header add a formula to declare the global variable
WhilePrintingRecords;
Global StringVar strNamesConCatTktID;

Open in new window



In the detail section  add a formula to build the string but not produce any output
WhilePrintingRecords;
Global StringVar strNamesConCatTktID;
if  {Command.CAB_APPROVED} = "Yes-National" then

  strNamesConCatTktID :=  strNamesConCatTktID & "Ticket ID:  " 
  & {Command.TICKET_ID} & "  (" &{Command.STATUS}& ")"& chr(10) 
  &  "MNT Category:  " &{Command.MAINTENANCE_CATEGORY} & chr(10) 
  &  "Start/Date:  " &{Command.SCHEDULED_START_DATE_TIME} & chr(10)
  &  "End/Date:  " &{Command.SCHEDULED_END_DATE_TIME} & chr(10)
  &  "Group:  " & {Command.COMBINED_GROUP_NAME} & chr(10) 
  &  "Contact at Start:  " &{@CALLED_START_TIME} & chr(10)
  &  "Contact at End:  " &{@CALLED_END_TIME} & chr(10) 
  &  "-----------------------------------------------------" & chr(10) 
ELSE
     strNamesConCatTktID :=  strNamesConCatTktID & "No pending tickets."& chr(10);
""

Open in new window


In the report footer add a formula to display the string
WhilePrintingRecords;
Global StringVar strNamesConCatTktID;
strNamesConCatTktID

Open in new window


mlmcc
0
 

Author Comment

by:Maliki Hassani
ID: 38317394
Looks like it is working...  I am setting it up on the server now. ;)
0
 

Author Comment

by:Maliki Hassani
ID: 38317422
I was just informed that the server scheduler software "CRD Seven" will not process any formula fields in the body of the email with WhilePrintingRecords;

Any way around this?
0
 

Author Comment

by:Maliki Hassani
ID: 38317485
The formula works just fine..  I need to another way to evaluate the records without using WhilePrintingRecords;

I have seen this done but can seem to think of where.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38317556
What do you mean?

Variables do not retain their values during the othe phases.
If you need to concatenate all the fields for all records the WHilePrintingRecords is needed.

If you want to pull 1 record at a time then you can leave it off.

mlmcc
0
 

Author Comment

by:Maliki Hassani
ID: 38317597
So I am trying to use two fields get my results. (in progress)

In the details section:
Shared StringVar srqOpen;

if  {Command.CAB_APPROVED} = "Yes-National"  then
 srqOpen := srqOpen + {Command.TICKET_ID}& chr(10) ;

srqOpen


In the report footer:
Global StringVar srqOpen;
{@EVAL_SHARED_SUMMARY_FIELDS}


I am seeing if this would work some how.  Feel free to help me structure the output. lol  The result so far is correct:  Produces two ticket id's vertically
0
 

Author Comment

by:Maliki Hassani
ID: 38317680
Okay the server will use this formula format.  I will add the rest of the details now.

Thanks!
0
 

Author Closing Comment

by:Maliki Hassani
ID: 38317776
Thank you!  Works perfectly!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question