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.

Excel – Quarter/Year from Date

So you want to display the Quarter Number, Q1,Q2 etc along with the Year in the format Q1-11 Q2-10 etc.

This is a handy formula which will allow you to group all your dates in the relevant quarter of the year.
I found this particularly useful for an Xcelsius dashboard, which needed to group and filters data into Quarters.

Enter your date field in A1 and place the forumla below in the require cell.

=”Q”&INT((MONTH(A1)-1)/3)+1&”-“&RIGHT(YEAR(A1),2)

1/1/2011  Q1-11

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})