Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Format fields in Crystal Report

Posted on 2013-06-05
16
Medium Priority
?
875 Views
Last Modified: 2013-06-05
Hi,

I'm fairly new to C# programming, and I'm using Visual Studio 2008 and Crystal Report to generate some reports. Problem is:

I've written a program to give me an xml file from a PDA.
The xml fields/nodes get imported to my Crystal Report, however, all the fields are "string" format. I have a date field, but it shows as "2013-06-05T00:00:00-07:00"

What should I do to get this displayed as a proper date field, i.e. "5 June 2013"
Do I need to create a parameter or formula field?

Also, I got a field with numbers. Example: "39.2857142857143"
I want this to be in percentage with and only 2 decimal places.

These are the two issues I'm stuck with at the moment.

If you can help me with the steps I need to take in order to achieve this, I would appreciate it very much.
0
Comment
Question by:Benny Theunis
  • 7
  • 4
  • 3
  • +2
16 Comments
 
LVL 45

Expert Comment

by:AndyAinscow
ID: 39221360
I'd suggest you export from your program to the intermediate XML file in the correct format you want.
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 1200 total points
ID: 39221428
In Crystal you would get a date from your string using a formula field of...

cdate(left({table.datefield}, 10))

You then use standard right-click formatting to set the date format you want to see.

Are your numbers actual number fields or are they strings as well?
0
 
LVL 42

Expert Comment

by:Meir Rivkin
ID: 39221430
U have 2 options:
1. Format the values in your program before generating the xml.
2. Format the values before bind to crystal report fields.

The 2nd option is better when u want to consider different formatting.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

Author Comment

by:Benny Theunis
ID: 39221500
I was afraid of these answers.

However, peter57r, THAT WORKS 100%! Thank you!!! I knew it could somehow be solved in CR.

My number fields are also in a "string" format.
0
 

Author Comment

by:Benny Theunis
ID: 39221565
Looks like I got the value field sorted before I write the information to the xml file with: ValueField.ToString("#.##")

Just going to put them all together and check the outcome.

Keep you all posted. Thanks for your speedy responses to my issues.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 39221592
Example: "39.2857142857143"

How do you expect to see this as a percentage?
39.29%

To do the first , use..
ToNumber({table.fieldname})
and then use the right-click formatting to set a custom format, rounding to 2 dp (assuming you do want the number rounded)  and also set the display to 2dp.
 To get the % just click the % format symbol on the formatting toolbar
0
 

Author Comment

by:Benny Theunis
ID: 39221595
Yep.

Sorted.

@peter57r - If I want to include the time of my date field also, what should the code snippet then be?
0
 
LVL 45

Assisted Solution

by:AndyAinscow
AndyAinscow earned 800 total points
ID: 39221602
Just bear one thing in mind (re my first comment).
You are taking the time (cpu resources) to output the values, then you are reading in those values as strings, converting, throwing away information, using more resources to format this intermediate value again...  Why not do it right the first time ?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 39221619
"If I want to include the time of my date field also"

I don't know what the time part of your string means ( T00:00:00-07:00)
0
 

Author Comment

by:Benny Theunis
ID: 39221662
Now I'm also confused by the format.
I have to idea what that 07:00 is.

I just test my program and the code behind my DateTime field is:
newRow["AssessmentStartTime"] = DateTime.Now;

This throws the following to my xml file : "2013-05-20T12:18:05-07:00"
0
 

Author Comment

by:Benny Theunis
ID: 39221677
Hi AndyAinscow

I understand what you are saying.
Like I said, I'm fairly new to programming. Thank you for the guidance.
0
 
LVL 77

Accepted Solution

by:
peter57r earned 1200 total points
ID: 39221713
Excluding the -7 bit you can do...

cdatetime(left({table.field}, 10) & " " &  mid({table.field}, 12,8))
0
 

Author Closing Comment

by:Benny Theunis
ID: 39221775
Excellent!

Thank you for your help!
0
 
LVL 45

Expert Comment

by:AndyAinscow
ID: 39221803
Just a guess.  Could the -7:00 be related to how much off from a standard (eg. GMT) your time zone is ?
0
 
LVL 35

Expert Comment

by:James0628
ID: 39221835
The obvious guess would be that the end of the string is a time zone adjustment for local time, like UTC - 7 hours.  FWIW, if you're in the Western US, the time there now is UTC - 7 hours.

 IAC, if an accurate time is important, you should pick some data with a known time and then check the string in your data and see if the time in the string is the time that you're looking for (or maybe some hours off).  Keep in mind that if the time is "off", the date could be too.  For example, assuming a 7 hour difference, a date and time of 06/05 4:00 AM in your data could really represent 06/04 9:00 PM local time.

 James
0
 

Author Comment

by:Benny Theunis
ID: 39222479
I'm in South Africa, GMT+2.
I checked my VS2008 emulator running WM6 and the timezone settings on there were GMT-8 Pacific US.

So yes, the timezone is also included in the original DateTime format.

Luckily we make sure that all our PDA's times & dates are up to date & correct.

Thanks guys.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses
Course of the Month21 days, 3 hours left to enroll

810 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