Crystal Reports





Crystal Reports is a business intelligence application from SAP SE. It is used to graphically design data connections and report layouts from a wide range of data sources including Excel spreadsheets, Oracle, SQL Server databases and Access databases, BusinessObjects Enterprise business views, and local file-system information. Report designers can place fields from these sources on the report design surface, and can also deploy them in custom formulas (using either BASIC or Crystal's own syntax), which are then placed on the design surface. Crystal Reports also supports subreports, graphing, and a limited amount of GIS functionality.

Share tech news, updates, or what's on your mind.

Sign up to Post


I’m wondering if I could please get some help with my Crystal Report?
The 5th column in from the left called “LYSales” is not calculating the correct last year sales values and I am unsure why.

The sales financial period in Australia is from July to June.

I have saved the report with data.

File attached.
Introduction to R
LVL 12
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

I am running sql server 2016 on a new server.  I have views that convert binary blob fields to text that I can use in Crystal Reports.  For some reason, when I bring them into my crystal report, it only show the very first character of the converted field.  If I look at it in SQL Server, it show the entire text in the converted field.  Anyone have any ideas why it's doing this and how to fix it???
Hi there,

I have a  report that has several columns but have just been asked to add a notes field which means that the lines used to create columns will appear in the notes as well, dividing the text.  Is there a way to retain the clarity provided by the lines but if there is a note for the lines to "suppress" or "disappear" when a note is present? - Perhaps overlay? But I can't figure this out - thanks so much in advance,


 *Crystal 11 is the product I am using
I have 4 simple formulas that evaluate different statuses from a single table/field and display in columns client names depending on what status they are currently in. Currently my report displays as shown in the attachment. I want to have the results line up in a single row across the four columns without the blank areas in the report. I can do it in a text object using database fields, but that method does not seem to work with formulas. I need to use formulas to get the right results.
How can I write a conditional suppression formula to suppress the repeated word "Color"? I can't use the suppress duplicate feature for this field since we use it for other things that must be repeated. I have the Test#200 suppressing because that one has to be suppressed all the time. In some cases Test#177 has to show the word "Color". In this case, there is a Test #116 present and when this test is present along with #177 and #200 the word "Color" field = {LabMethods_ReportedName} should only show for Test ID#116 and suppress for #200 and #177. I tried to do a conditional suppression on the Duplicate Suppression but it didn't work for me. Maybe I did that incorrectly.
I have a Crystal Report and in it a File Path that links all of a patients records to scanned in PDFs. This will be used in a secure environment, so not a risk. What i need to have happen is when I click on the file path in the report, it opens the document. Is this possible, click on a file path and open the PDF associated to that file path address?
I wish to format currency in my report to make large amounts appear as following:
$1,200,000   would appear as $1.2M
$400,000 would appear as $400K
$10,000 would appear as 10K

My numbers do not get into billions  :)
Need a crosstab of % of payments to charges with discharge date on vertical and payment date on horizontal.  See attached sample.

In crystal, I have created a formula for total charges, total payments, and %_Paid (Charges/Payments), but they don't seem to work in the crosstab the way I need them to.

Any help would be appreciated.  I must be missing multiple steps.

I have attached an excel sample file of the way it should look when completed
and a screenshot of the way I have my crosstab set up currently.

I have a sequel query that has fields for payment month and year, discharge month and year.  Attached is a word document which shows the crosstab setup - not the way I like it - so hoping someone knows how to set this up the way I need it.
I am trying to create a Parameter in Crystal Reports that lets users enter a {numeric value} that shows all values both Positive and Negative or could show only positive values or of course only negative values. Using a number entry only. Is this possible or do I have to use string values to enforce the greater than 0 (or some unique value) , less than 0 (or some unique value), equal to 0 (or some value) , or all values? I am drawing a blank here and cannot remember how to do that or if it is possible?
How to convert SubQueries in Crystal Reports:  I have a long SQL query that has several sub-queries that needs to be converted into a Crystal report using Tables.  The query takes forever to run and we are hoping by converting to Crystal it will be easier to
1)  allow others to use the query
2) allow for multiple, frequent additions and subtractions to the report.
3) This report is then pulled into a medical record system that is a complicated process, so by making it a table run query this simplifies the process.

Question:  How do subqueries convert in Crystal?  Do they become sub-reports?

Using Crystal Reports 2013 and SQL Server Mnagement Studio V17.4
Starting with Angular 5
LVL 12
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

I had this question after viewing Calculate Percentage in Crystal Report Formula.

I have a percentage calculation which calculate percentage based on a variable dao.t47:

if isNumeric({dao.T47}) then
     (if toNumber({dao.T47}) in 91 to 100 then "A1" else
     if toNumber({dao.T47}) in 81 to 90 then "A2" else
     if toNumber({dao.T47}) in 71 to 80 then "B1" else
     if toNumber({dao.T47}) in 61 to 70 then "B2" else
     if toNumber({dao.T47}) in 51 to 60 then "C1" else
     if toNumber({dao.T47}) in 41 to 50 then "C2" else
     if toNumber({dao.T47}) in 33 to 40 then "D" else
     if toNumber({dao.T47}) < 33 then "E")
else {dao.T47}

Now I had changed the variable to a custom made which doing sum for some variables so I have @gtotal now. How to set percentage like above for this, when I wrote it, it was givign me error:

if isNumeric({@gtotal}) then
     (if toNumber({@gtotal}) in 91 to 100 then "A1" else
     if toNumber({@gtotal}) in 81 to 90 then "A2" else
     if toNumber({@gtotal}) in 71 to 80 then "B1" else
     if toNumber({@gtotal}) in 61 to 70 then "B2" else
     if toNumber({@gtotal}) in 51 to 60 then "C1" else
     if toNumber({@gtotal}) in 41 to 50 then "C2" else
     if toNumber({@gtotal}) in 33 to 40 then "D" else
     if toNumber({@gtotal}) < 33 then "E")
else {@gtotal}

Please help...
      I am attempting to write a report using an Excel file as a source. I have attached an example of the file I will be accessing. I have highlighted the data that I would like the report to return. I need to only extract records where the “WO-Rel#” has a matching entry where the data ends with an alpha character. Is this possible?

Thank you

I wish to create a custom formula field in Crystal Reports to display data from a field. The data is:


I want to remove the CO away and the anything pass < xxxxxxxx

So only leaving SS00537.


I am converting Crystal Reports to BI Publisher.  BI Publisher uses MS Word for the visual display.  I am extremely inept at look and feel.  Attached you will find 2 samples of text. The crystal uses Arial 8 for these labels.  When I use Arial 8, in word it does not display the same amount of text.  

What font would you suggest to get the same look and quantity of letters in .

Thanks in advance,

I have a Crystal Report Formula that I am trying to get converted into a SQL expression that I can add as a calculated field in my report.   The Crystal Reports formula is:

if{p21_view_process_x_transaction_detail.qty_in} >0  and
{p21_view_process_x_transaction_detail.qty_in} <> {p21_view_process_x_transaction_detail.qty_out}then
    "Future Jobs"

I'm always having problems with the syntax in SQL.  I have even tried just dealing with one criteria and can't get that right either.  My last attempt looked like the following:

=iif(Fields!qty_in.Value >0  and
(Fields!qty_in.Value <> (Fields!qty_out.Value,"CURRENT JOBS","Future Jobs"
Crystal Reports 2011

I have a parameter in my report that accepts multiple values.

So the user might select (examples below), there are many combinations

I want to group on the parameter values but am unsure how - so in my examples group heading would be
GH1 A1
GH1 C1
GH1 D1

I need to create an MS SQL query that shows the date-time difference involving Vehicle Stop & Vehicle Start times, for a given Operator using a particular Vehicle.
Then I need to SUM these values, so we have a total running time for an Operator for ALL Vehicles.
Here is the SQL I've used to come up with all the records needed.

                             SELECT [EventLog].[Date Time], [Events].[Description], [EventLog].[Operator Name],
                                           [EventLog].[Operator Code], [Vehicles].[Truck Type] AS 'Forklift ID'
                                           FROM EventLog INNER JOIN Events ON [EventLog].[Event ID] = [Events].[EventLog Number]
                                           INNER JOIN Vehicles on [Vehicles].[Serial] = [EventLog].[Serial Number]
                                           WHERE (([Date Time] between '" & StartDateTime & "' AND '" & EndDateTime & "') AND
                                            ([EventLog].[Event ID] = '3' OR [EventLog].[Event ID] = '4')) ORDER BY
                                            [EventLog].[Operator Name], [EventLog].[Date Time], [EventLog].[Serial Number] ASC;

I'm using data / database structure from a 3rd party, that can't be altered.
Here is the Database Table Structures:

Key - ID
Event ID - (3=Vehicle Start & 4=Vehicle Stop)
Operator Code - ID
Operator Name
Serial Number - (Vehicle serial number)

Crystal Report shows formatting rules before and after field text.
How can I get it to show only text from targeted field?

Formula I have is this:


Attached file shows what prints (should show only text in All CAPS)

When using a union as a command object in CRXI i'm getting an error 'FROM keyword not found where expected'. Oracle error 923

SELECT *, 'H2' AS Type  FROM db.table1
SELECT *, 'H3' AS Type FROM db.table2
SELECT *, 'H4' AS Type FROM db.table3
SELECT *, 'H5' AS Type FROM db.table4
SELECT *, 'H6' AS Type FROM db.table5

Open in new window

CompTIA Security+
LVL 12
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

When trying to create a union in crystal reports XI i'm getting the error 'SQL command not properly ended'

Here's my sql, to me it looks correct? Any ideas?.

SELECT * FROM db.table1
SELECT * FROM db.table2
SELECT * FROM db.table3
SELECT * FROM db.table4
SELECT * FROM db.table5;

Open in new window

I need to verify the driver that is being used for a crystal report. I just want to make sure that the way I am doing it is correct. In looking at all the screenshots I made I thought this might be helpful to those that may not know. This image may also be too cluttered but when taken in pieces might be useful to others. I did not install the drivers but came in after that was done and was told where the data was located. Can someone verify that my description is spot on or want to add anything I would appreciate it. Thank you.

The point of this is to show the commonalities that are present when navigating through the various sections of menus and windows.
Helping a user identify the name of the database, the driver used in the database and the User who has privileges.

1. When in your active Crystal Report go to the Database Menu See fig 1 Click on Set Datasource Location
Here you will find the Current Data Source Section, the Report, the connection (DatabaseName) and the Properties of the connection Expand them all.

Figures 1 2 3
2. Launch the ODBC Administrator of choice. 32 or 64 bit as the case may be. Choose whether the DSN = Data Source Name is either a User DSN, System DSN or File DSN.
User DSN = Data is only accessible by a specific user(s), System DSN = Data is accessible by anyone who has access to the system specified , File DSN = Data anyone who has access to the File specified. See …
I am trying to install just the Microsoft ODBC for Oracle 6.01.7601.23391 MSORCL32.DLL on a
Windows 2012 Server It needs to be able to be installed on the 64 bit ODBC Admin.
I already have one installed on a Windows 7 machine but I did not install it.
When I go to the 2012 Server I notice it does not have it listed in the Drivers Tab of the ODBC Administrator tools.
Since this is going to be used with Crystal Reports and an Oracle Database. I assume it does not matter if it is a 32 bit version?
Currently the report is working and running just fine installed in ODBC Admin 64 bit version on another machine. Windows 7 Pro

I have searched most of today and cannot seem to get the correct file on the correct machine. Is there something unusual I have to do since it is a 32 bit and I need it on the 64 bit ODBC Admin location. Please advise. Thank you.
I have a nested IF in Record selection criteria.
I have two parameters, first parameter can be "" or some value.
My 2nd parameter can be one of 3 values. (SS/CR/COM).  If I comment out the code for 2nd parameter, I am getting results.  If I uncomment the code for 2nd parameter, I am not getting any records.  So, I thought that my NestedIF is incorrect.
Can you please help? Thanks in advance.

My conditions are as below.

({@p_package}="" OR {cmdSO.Parent_package}={@p_package})    //  first parameter

//second parameter below.

   IF {@p_PackType}='SS' Then {cmdSO.Cat_Type_Code} = 'SS'

    ELSE IF {@p_PackType}='CR' Then {cmdSO.Cat_Type_Code} <> 'SS'

         ELSE IF {@p_PackType}='COM' Then ({cmdSO.Cat_Type_Code} <> 'SS' AND {cmdSO.Pub_Cat_Cd}='PLM')

Hi, I have a parameter that's a number which is accepting a year (ex: 2018). But it displays as 2,018.00 when I printed on the report.
I need to pass 2018 to the database rather than 2,018.00 in Record Selection Criteria
I don't want to convert into string because my database field is smallint.
I tried truncate, Round.  It is still displaying as 2,018.00
How can I make this parameter to display as 2018
Please help.
I have an issue with Crystal Report Viewer 2013 on how it runs under IE11.
I installed SAP Crystal Reports runtime engine for .NET framework on our web server (Win 2008 64-bit) and created simple web test (Login.asp, RunReport.aspx).
Login.asp form submits parameters and pass them in RunReport.aspx where Crystal Viewer is called.
Test works, but when report generates multiple pages, page navigation (clicking "Next page" or "Previous page") courses browser history to grow.
As a result to get back to Login.asp using browser back button as many clicking required as how many pages you visited in the report.
Changing Zoom also generates extra page in history.
I tried to run this test under Chrome and experienced performance I need, visited pages were not added to browser history.
Will appreciate any advice on how to achieve the same effect when using IE.

Crystal Reports





Crystal Reports is a business intelligence application from SAP SE. It is used to graphically design data connections and report layouts from a wide range of data sources including Excel spreadsheets, Oracle, SQL Server databases and Access databases, BusinessObjects Enterprise business views, and local file-system information. Report designers can place fields from these sources on the report design surface, and can also deploy them in custom formulas (using either BASIC or Crystal's own syntax), which are then placed on the design surface. Crystal Reports also supports subreports, graphing, and a limited amount of GIS functionality.

Top Experts In
Crystal Reports