Rounding when Exporting to Excel
When you use the rounding feature in Crystal Reports you generally do so, as follows:
(Right Click on the Field > Format Field > Number > Customize)
You can select the number of decimal places to which you wish to Display in the Report.
However, When you export this to excel you will still get the full Value.
For example. If the Value was 56.76999999
And you have select to round to 2 Decimal places.
The Report will display 56.77
However when you export, the field will be 56.76999999
If you would rather have the export to excel match the report.
Simply add a formula which performs the rounding.
Round({Table1.Fieldname1},2)
This will mean when you export. The Excel Field will match the Value Displayed in the Report.
BI4 OpenDocument Null Parameter
With the release of BI4 there have been a few changes to the structure of OpenDoc URLs in business Objects 4.0.
Crucially a lot of people would need to pass Null Value into a report for a required parameter. This used to be just a case of entering no value or the text Null, but that no longer seems to work in BI4.
To get around this, you can now pass the ASCII Value %00 instead of Null.
For Example
&lsSProductName=%00
Perhaps this will change in the future, but for now this has worked.
Display Plus(+) and Negative (-) signs before a number
If you wish to display a plus sign in front of a numeric value for a report. perhaps to ease the reading of positive and negative values, or show an increase in an amount or value, often the negative sign will be display automatically.
You will however need to display a plus(+) sign to positive values. Or nothing at all for a zero (0) amount.
The following code will append a Plus sign to the front of positive numbers and leave a Zero as it is.
If {table.value} > 0 then
‘+’ + totext({table.value}, 0, “”)
Else if {table.value} = 0 then
totext(table.value}, 0, “”)
elsetotext({table.value, 0, “”)
Last Day of Previous Year or First Day of Current Year Calculation in Crystal Reports
You have a requirement to use the last day of the previous year or the first day of the current year and you don’t want to be hard coding any values into the report.
Very simply.
For Last Day of Previous Year: Create a Formula @PrioYearEnd
date(Year(currentdate)-1,12,31)
For First Day of Current Year simply remove the -1 from current date;
date(Year(currentdate),01,01)
Subtract Month Crystal Report
If you have a date field and wish to derive the previous month from that date, use the formula below.
DateAdd ("m", -1, CurrentDate)
This Is particularly useful for Prior Month Calculation.
Obviously you can adjust the -1 value to calculate any number of months previous. i.e to identify 12 Months Prior from a particular date you would use the formula
DateAdd ("m", -12, CurrentDate)
CurrentDate can also be replaced by whichever date field you require.
Crystal Reports cell alignment and export issues
Are you experiencing issues with your crystal reports exporting?
Can’t get the cells to align correctly when exporting to excel?
We have explained a few simple things you can do to resolve these. Just view our article on Crystal Reports Cell alignment
Don’t have the time and want a helping hand? You can email us. We will do it for you.
We will get your crystal report looking exactly as you wish, and exporting to excel in perfect alignment. For only $50/£30/E30 per report.
Remove trailing character in Crystal Reports Formula
This is a relatively straight forward formula to help you remove the last character of a string.
It may be that you wish to remove a period from the end of a field name or an inverted comma. Here we will call our field {DBField1}
left({DBField1},len({DBField1})-1)
All we are doing here is using the left function to display all the characters to the left, excluding the last chacter, (which is determineted by the “Len” function)
NOT IN Syntax – Excluding records in Crystal
So you need to exclude a few records from your reports in Crystal.
Perhaps that stray erroneous record in the DB which has never been fixed, or a list or exclude customers for a certain country/state.
If you have a list of values and you want to exclude them, then NOT IN will help you do to this. This can be used for both record selection, or on the suppression criteria for groupings.
So lets say you want to exclude customer ID’s 1512, 1563 and 1765 from appearing in your report.
Simply add in your selection criteria;
NOT({CustomerID} IN [“1512″,”1563″,”1765”])
Crystal Reports combine a separate Date and time field into 1 field
If you have a situation where you have your date and time stored in 2 separate DB fields. You may wish to combine these, either for display, formatting or sort purposes.
To do so, you simply use the following code.
Where Field1, is your date field and Field2 is your Time Field
datetime(Field1,Field2)