Crystal Reports

35K

Solutions

15K

Contributors

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 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?
0
Cloud Class® Course: Python 3 Fundamentals
LVL 12
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

This is probably a strange request as most want to suppress duplicates, I on the other hand want to duplicate a record based on number of occurrences per year.  So if the activity is weekly I should see 52 records with a generated due date based on frequency code and the last  duplicated records calculated due date.

I am trying to forecast a preventive maintenance activity based on frequency code such a YR, MO, DY, WK, etc.

WhilePrintingRecords;
DateVar NextDt;
StringVar IntervalType;
NumberVar Interval;

Interval := ToNumber({FREQUENCY_UNIT});

if {FREQUENCY_UOM} = 'YR' then IntervalType := 'yyyy';
if {FREQUENCY_UOM} = 'MO' then IntervalType := 'm';
if {FREQUENCY_UOM} = 'WK' then IntervalType := 'ww';
if {FREQUENCY_UOM} = 'DY' then IntervalType := 'd';


NextDt := Date(DateAdd (IntervalType,Interval,{@Due Date}));

NextDt;

Hopefully this makes some sense.
0
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
0
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...
0
Hello,
      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
Example_Book1.xls
0
Hello,

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

COSS00537<XXXXXXXXXXXX

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

So only leaving SS00537.

Thanks.
0
Hello,

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,

Brock
BI-PUBLISHER-FONT.PNG
Crystal-Font.PNG
0
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
    "CURRENT JOBS"
else
    "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"
0
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
A1
C1
D1

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


Thanks
0
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:

EventLog
Key - ID
Date Time - YYYY-MM-DD HH:MM:SS
Event ID - (3=Vehicle Start & 4=Vehicle Stop)
Operator Code - ID
Operator Name
Serial Number - (Vehicle serial number)

Events
0
Introducing Cloud Class® training courses
LVL 12
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

I have an old version of Business Objects Crystal Report Server Software that we are trying to reinstate for Project Management.  My owners don't want to spend the money for the latest/greatest in similar software.

I have everything running except for the CMC.  As soon as I start it, it falls on its' face and goes right back to a stopped state.  When I go to check the connection for the CMC I get the following pop up:  CMC Generated Error
The event logs list the following, similar, error:  Eventvwr Error
The environment is a virtual Windows Server 2012 R2 with a SQL Server 2016.

I have deleted and added the DSN numerous times with different drivers to no avail. (In the 32 Bit ODBC)

It should be noted that when I set up the DSN, and the connection in the CMC, it successfully connects to the database.  The error only happens when I go back to the connections tab in the CMC.  

The only possible issue I thought about was that this is a 64 bit virtual and the software is 32.  However, I have never had an issue with this in the past on physical servers. (64 vs the CS 32 software)

Is there a way to see what the CMC is submitting for the connection string?
0
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:

whileprintingrecords;
trim({WMSalesLine.ProductionNote})

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

Thanks!!
Crystal-report-sample.JPG
0
Hi Experts,

i have to add some custom code to SAP gui scripting code, we have customized code like the script read the data from the excel.
but we want to add more customization in the code.

If Not IsObject(application) Then
   Set SapGuiAuto  = GetObject("SAPGUI")
   Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
   Set connection = application.Children(0)
End If
If Not IsObject(session) Then
   Set session    = connection.Children(0)
End If
If IsObject(WScript) Then
   WScript.ConnectObject session,     "on"
   WScript.ConnectObject application, "on"
End If

Dim cnnExcel, rstExcel
Dim strExcelFile
strExcelFile = "C:\Users\kt5775\Desktop\Engie work\SAP info\Tcodes input.xlsx"

Set cnnExcel = CreateObject("ADODB.Connection")
    cnnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & strExcelFile & ";" & _
               "Extended Properties=""Excel 8.0;HDR=NO;"""

    Set rstExcel = CreateObject("ADODB.Recordset")
    rstExcel.Open "Select * from [Sheet1$A1:G1000]", cnnExcel, adOpenStatic

session.findById("wnd[0]").maximize
Do Until rstExcel.EOF or UCASE(rstExcel.Fields(0).Value) = "LAST"


session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").text = "/npfcg"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtAGR_NAME_NEU").text = rstExcel.Fields(0).Value
session.findById("wnd[0]/usr/btn%#AUTOTEXT003").press
0
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
WHERE TLC_CURRENT_FLAG = '1'
UNION ALL
SELECT *, 'H3' AS Type FROM db.table2
WHERE TLC_CURRENT_FLAG = '1'
UNION ALL
SELECT *, 'H4' AS Type FROM db.table3
WHERE TLC_CURRENT_FLAG = '1'
UNION ALL
SELECT *, 'H5' AS Type FROM db.table4
WHERE TLC_CURRENT_FLAG = '1'
UNION ALL
SELECT *, 'H6' AS Type FROM db.table5
WHERE TLC_CURRENT_FLAG = '1'

Open in new window

0
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
UNION ALL
SELECT * FROM db.table2
UNION ALL
SELECT * FROM db.table3
UNION ALL
SELECT * FROM db.table4
UNION ALL
SELECT * FROM db.table5;

Open in new window

0
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 …
0
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.
0
Hi,
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.
B
===========================================

My conditions are as below.

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

//second parameter below.
AND
(

   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')
                  ELSE
                      TRUE

)
0
Hi, I Used Crystal Report in C# Desktop application. My database is in access and i set Jet Password there.Can you help me to set Jet Database Password dynamically in Crystal Report.
0
Cloud Class® Course: Microsoft Windows 7 Basic
LVL 12
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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.
0
I have a VB.Net program that uses CrystalReports to print an ID card (CR80).
The viewer shows a portion of a full-size letter format, with horizontal & vertical scroll bars.
What programming / controls are needed to:
1. Resize margins to CR80 ~ 2.13" x 3.38".
2. if necessary, remove scroll bars.

Thank you,

Russ
0
Hi experts,

I have the following issue

I need to have my data viewed as follows
GH1   Vegetables
D:Carrots
D:Celery
D:Tomato

GH1 Beef
D:Turkey
D:Chicken
D:Beef

GH1:Oils
D:Butter
D:Sunflower
D:Olive

I need the for Each GH1 to have the data displayed as follows in a subreport1
Carrots        Celery        Tomato

I need the for Each GH1 to have the data displayed as follows in a subreport2
Turkey       Chicken     Beef

I need the for Each GH1 to have the data displayed as follows in a subreport3
Butter        Sunflower       Olive

There could be dozens of records in each GH1

Any assistance would be appreciated
0
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.
Thanks.
0
I have 2 Windows Applications which already contains a CrystalReports1.rpt, CrystalReport11 and CrystalReportViewer1 in Form1 design.
Questions:
1. How do I merge the 2 into 1 Windows Application AND
2. What changes do I need to make (where), so there isn't any confusion between the 2 reports?

Both reports will be accessed via a separate  button for each.

Thank you,

Russ
0
I am trying to share a variable from a subreport with a main report in Crystal 2011. In the subreport we are using the following code;
WHILEPRINTINGRECORDS;
SHARED NUMBERVAR SAVAL := Sum ({PLS_POLICYSUBAGENT.PLS_AMOUNTT}, {PLS_POLICYSUBAGENT.PLS_VERNO})
The main report uses the following code;
WHILEPRINTINGRECORDS;
SHARED NUMBERVAR SAVAL; SAVAL;
 When we put "@SAVAL" in the main report to use in another formula the results are not being displayed. When we try to display "@SAVAL" it is not lining up with the data on the Details line. (See attached screenshot).

Can someone help me resolve this please?
Doc112.docx
0

Crystal Reports

35K

Solutions

15K

Contributors

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
<
Monthly
>