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, “”)
else

totext({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 – No Records Error / Splash Screen

There isn’t much more confusing for an end user than facing a blank screen, with no idea if the screen has loaded, caused an error, or frozen.

When writing crystal reports its important to understand that returning no records is a possibility in a lot of cases and this must be effectively communicated with the user on-screen.

No Records Splash Scren Crystal Reports

By default, crystal will not display a no records splash screen or display any error messages. It will simply display your Report Header and page headers and the details will be left blank. This could possibly cause issues for the users as they will no be sure as to whether no records have been returned on if there was some sort of error.

In order to create a crystal Report No Records Splash screen, we need to create a report header which will display a message, should no records be returned by the query.

If you have an existing report header such as our fictional “No. Of Products Per Site Report” –  you simply create a second report header.

Right Click on Report Header in design view and select “insert section below”.

This will create a section called “report header b”. This will contain the message you wish the user to see should no records be returned.

We now want to suppress this section of report so that it only displays when we have no records.

Right Click on our “Report Header b” and select “selection expert”. On the common tab, select the formula editor for Suppress (No Drilldown).

In the formula put the following formula

count({Table1.Field1}) <> 0

Ensure that the suppress checkbox is not checked. See image below.
The red Mark on the forumla box shows that a forumla is enetered in the supression criteria.

This formula will mean that the header will not be displayed if the count of your record field does not equal 0 records.

Page Header.

You will also probably want to hide your column headings if there are no records.

To do this, again go to the “selection expert” and tick the “Suppress (No Drill-Down) checkbox and then click on the formula button and enter.

            if isnull({Table1.Field1}) then true

Page Header Suppression

Thats It!

Now when no records are returned you should be able to see the splash screen telling the user that no records are available.

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.

Email us now if you want more information.

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)

 

Crystal Reports Export To Excel Column Alignment

Ensuring that data can be successfully exported to excel is often a key requirement for business users.

This can be a tricky task inCrystalreports and often involves a lot of painstaking movement of columns, fields and alignments.

The export to excel data-only feature is often utilised by users to get data from a crystal report and allow them to manipulate the data as they require in excel.

Often exported data can have incorrect column and header alignment.
However there is a simple solution in the export options which will keep the alignment the same as displayed in the report.

“Maintain Column alignment” in the File>Export>Report Export options> menu should be selected.

An Alternative, if this option does not resolve the issue, is to insert a blank text box in any gaps in your header columns. This will force excel to populate the cell and maintain alignment from the crystal report.

Crystal Reports Previous Month Calculation

I needed to get a report to run via Business Objects scheduler which would return data for the previous full month. So a Report run on 1st Feb, would return all data within a date range of 1st Jan – 31st Jan.

But the report would also need to be able to run on specific dates if the user required.

Step 1 – Create “Previous Month” Parameter”.

So I created a parameter which was a simple Y/N drop down. If “Y” was selected it would return the data for the previous Month.

{?CurMonth}

If N was selected, the report would use a Start Month and End Month Parameter values to return the required data.

Step 2. – Create Forumla to work out Previous Full Month Start/End Date 

Create forumla @strFDMonth
This will calculate the First Day of the Previous Full Month.

minimum(Lastfullmonth)

Create forumla @strLDMonth
This will calculate the LadDay of the Previous Full Month.

maximum(Lastfullmonth)

Step 3 – Enter Selection Criteria

If {?CurMonth} = ‘Y’ Then
{DATE_FIELD} >= {@StrFDMonth} and {DATE_FIELD}} <= {@StrLDMonth}
else
{DATE_FIELD}} >= cdate({?STARTDATE}) and {DATE_FIELD} <= cdate({?ENDDATE})