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 .NET report  using Crystal Reports.

The 3 steps required  to create the report is as shown in the screen below

Crystal Report Generation Screen
The first step is to create the DataSource  as in the 2 steps below

STEP1  Defining the Report  Content

  private void CreateDataSet()
        {
            //Creates a new dataset, 2 datatables and adds data to them
            _dsData = new DataSet("Employee");

            byte[] arrBytes;
            System.IO.MemoryStream ms;

            using (DataTable dt = new DataTable("Employee"))
            {
                dt.Columns.Add("Staff_no", typeof(string));
                dt.Columns.Add("Surname", typeof(string));
                dt.Columns.Add("Firname", typeof(string));
                dt.Columns.Add("midinit", typeof(string));
                dt.Columns.Add("DepartmentID", typeof(string));
                dt.Columns.Add("Sex", typeof(string));
                dt.Columns.Add("GradeID", typeof(string));

                GlobalConfig.Connection.CreateEmployeeDataTable((DataTable)(dt));

                _dsData.Tables.Add(dt);


            }

            ////Creates a relation between the 2 DataTables
            //_dsData.Relations.Add("relCountryStates",
            //    _dsData.Tables["Countries"].Columns["ID"],
            //    _dsData.Tables["States"].Columns["IDParent"]);
        }

Open in new window


Using the debugger  Step1 above appears ok and I am able to go into the method below that actually
writes the dataset.

In STEP2 below the stored procedure  tested separately  works selecting 2 records from the database (see  records below)

Stored Procedure Execution extracting the 2 records  required
STEP2  Loading the DataSet


Open in new window

0
Introduction to R
LVL 19
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 have a report with a single description field in it. At the moment I need a way of controlling what gets printed from the field in the details section. The contents of which are details from a Job Number. Each line of details has a Sequence Number or a Detail_ID field which I think I can use to suppress those lines not needed.

One problem is I need to suppress all blank sections. I know this is under the Section Expert. The fields that I think can be used are Sequence & Detail _ID both are numbers. A "0" in the sequence field is an empty value as is the lowest number in Detail_ID can be an empty value but not always. Sometimes there are no empty values.
There is a group on Job Number and I think by using the Sequence or Detail_ID field I can create a formula that will suppress rows conditionally?
If the Sequence field is used they print in the details section of Crystal like this:
Detail        0                                                         0     suppress all zero value line    
Detail        1                                                         1   .... this would be the one to display if only 2 in a sequence
Detail        2  .... this would be the one to display if the 3 are in sequence
(Sometimes the order of values are ( 0, 2, 1 ),  ( 1, 0 ) or even just ( 1 ) from top to bottom stacked in the details section.)

or Detail_ID  field    ........with the Detail_ID it should be the largest number.

15401    ......... Suppress
15402    …
0
I am currently running Crystal Reports 2008 and this is the error that I am getting: "A subscript must be between 1 and the size of the array"

This is the data:
Last_Edit = ABERN,11/11/19,16:44,ABERNARDI

I want to just grab the last piece of information after the comma "," which is "ABERNARDI"

This is the code that I have that works but seems like its not perfect.

Split ({Contacts.LAST_EDIT}, ",") [4]

Please advise
0
Hello, I have a client using the 3 attached crystal report forms (Sales Orders).
When the data is entered in their accounting system the text is displayed in the correct way.
However, when the output is displayed on the forms the text is not in the same order.

Are you able to assist please?

I have attached the 3 files and a screen shots where you can see the data on the screen and the data on the forms (out of order)

Thanks.
SalesOrder.png
SalesOrderLandscapeAWW.rpt
SalesOrderLandscapePCI.rpt
SalesOrderLandscapeTWW.rpt
0
I am trying to create a formula that checks two date fields and calculate the dates.  The field will be null if no date was entered and a blank string if it is set then removed.

SQL Code snippet Used in a query:
CASE
           WHEN(bp.ReleaseTime IS NULL
                OR bp.ReleaseTime = '')
           THEN DATEDIFF(d, bp.BookedDateTime, GETDATE())
           WHEN bp.ReleaseTime IS NOT NULL
           THEN DATEDIFF(d, bp.BookedDateTime, bp.ReleaseTime)
           ELSE ''
       END AS 'Days Incarcerated'

Open in new window


Crystal Reports Formula:
SELECT {BookedPerson.ReleaseTime}
CASE ISNULL (({BookedPerson.ReleaseTime}) OR {BookedPerson.ReleaseTime} = ''): DATEDIFF(d, {BookedPerson.BookedDateTime}, GETDATE())
CASE IS {BookedPerson.ReleaseTime} IS NOT NULL: DATEDIFF(d, {BookedPerson.BookedDateTime}, {BookedPerson.ReleaseTime})
DEFAULT: ''

Open in new window

0
I have an older version of Crystal Reports (2008) on an Win 7 Pro machine.  However the data it is working with is being compiled in Excel 365. Which upon opening will not allow a connection in Crystal Reports 2008. Is there an available connector I can install or download for the ODBC admin so that it shows up and allows CR to read a newer version of an excel spreadsheet? I realize I could have the file saved in an older version of Excel however I have wondered if there was a newer driver that would work with CR that could be installed so that I do not have to keep doing that. Does Excel 365 have a driver that would work with Crystal Reports that can be added to the ODBC administrator tool?
0
I have this line of code where I need to find records that fall between two dates.  There is only one parameter that is supplied which is the starting date for the period.  I need to find the last date of the ending month.

Example:   Start Date = 07/01/2019   and the last date should calculate out to 09/30/2019

and "LicensePayments".paymentdate  between {?Start Date} and DATEADD(month,2,{?Start Date})

Open in new window


I'm using this as part of a command in Crystal Reports.
0
I have Crystal report 2008  Rpt_A that has data source Command_A . Command_A  executes a store procedure  spA against database  DB_Sales on SQL ServerA.
I need to create report Rpt_B that is identical as Rpt_A  with command as data source that excecute store procederue spB  that run against same database DB_Sales on another server -  SQL ServerB.

In need to change location of data source  and If it is possible not doing report from scratch again.
I was thinking to make copy of Rpt_A and  use command_B  to  execute store procedure spB against  DB_Sales on SQL_ServerB.
Store procedure spA and spB are the same, same table names, same parameter only their names are different in those two locations.
 I cannot change name of spB to SpA it has to stay as spB as it is used for other reports.
What I tried up to now:
I made a copy or report Rpt_A to Rpt_B.
 
Report  is composed of  main report and two sub reports.
I tried to Set new Data Source location with new database and update button ,  I was able to do that then I came to map screen and on that screen I can not do much.  I can not map or change anything, if I click ok it went through but I lost all my fields from report.
How to do this and not creating report again from beginning. I need detail steps if is possible.

Thank you
0
Hello,
I am attempting to pull data from an Excel file. There is a column that has values that start with both alpha and numeric. The report only pulls the data that starts with alpha. I have tried formatting the cells in that column every which way but I can't get the report to return the values that are numeric. I have attached a file as an example of the Excel file I am working with. Any help with this would be appreciated.
Example_1.xls
0
Hello Experts,
Here is my issue.  I have a MySQL database that a report is being derived from. There is a field in one of the tables that is stored as XML. Previously, an expert gave me some great advice and I was able to create a formula that broke out one of the 3 pieces of data that I need.

StringVar strTest := {_invoice_transaction1.Extra};
ExtractString (strTest,'"CheckDate">',"</v");

While this formula gives me the CheckDate, as I suspected, I could not create a parameter field from the formula.
Unless there is a workaround that I have not yet deduced, my latest attempt is to go to the SQL query and modify the query prior to the data reaching the report.

I know I need to go to: Database:database expert: odbc: add command... and there I can modify the SQL.

So, that leaves me 2 questions: Where and or how can I see the SQL statement output so I know if my modifications are going to have the desired results. If I was using straight SQL I would probably add a line like: ExtractString(_invoice_transaction1.extra, '"Checkdate">',"</") as checkdate     -  to my query which I have copied from Crystal below.

 SELECT _invoice_transaction1.TransactionDate, _customer1.LastName, _customer1.FirstName, _customer1.MiddleName, _insurancecompany1.Name, _invoice1.ID, _invoicedetails1.BillingCode, _user1.Login, _invoice_transaction1.Amount, _invoice_transaction1.Taxes, _invoicedetails1.ReviewCode, _invoice_transactiontype1.ID, _invoice_transaction1.Extra
 FROM   …
0
Exploring SharePoint 2016
LVL 19
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Hi Experts:
So I'm not super with Crystal Reports... let's start there.

I have a memo field that is stored as XML.
I need to parse the XML and pull 3 variables for one for Checknumber, one for checkdate, and one for paid.

Here is part of the problem. Some data sets contain the 3 variables, some don't, some data sets are null (no data at all).
Here are 2 sample pieces of data.

<values>
  <v n="Billable">50</v>
  <v n="CheckDate">04/29/2019</v>
  <v n="CheckNumber">12</v>
  <v n="Paid">35</v>
  <v n="PaymentMethod">Check</v>
</values>


<values>
  <v n="Billable">3617.85</v>
  <v n="Paid">500</v>
  <v n="PaymentMethod">Cash</v>
</values>

Ultimately, the goal is to have a formula and to be able to search for the CheckDate, CheckNumber, or Amount.
At this point, I am not certain that even If I can isolate the variables, that the variable can be made into parameters for searching...

I have researched and attempted various string variables but without success.
0
For years I have used Plantronics Supra binaural headsets along with the matching Plantronics headset amplifier/interface M10 or MX10. ( I am not at that location now.) I have the requirement that my phone audio be crystal clear at all times. I have never had any problem with clarity until yesterday. I never had considered VOIP because my internet speed was not ideal. Several months ago I got fiber and my up and down speed is 1 GIG with pings at 2ms. With this super speed I thought that VOIP would be an acceptable choice since it would save me more than 50% of my phone bill. Now that it is installed I am told that my transmitted voice is somewhat distorted and there is some sort of slight crackling in the background. I cannot live with this problem. I spoke to level one of tech support last night and he confirmed that I was indeed distorted. I have another line that utilizes the MagicJack. I phoned the tech on that line and the distortion was still present. I also switched from my headset and amp combo to a regular phone on the business phone system (Avaya Partner) and the distortion is still there. Level two is supposed to get back to me today and begin troubleshooting the problem. I was just wondering if any Expert has encountered this difficulty before? With such incredibly high isp speed the is the last thing that I had expected. If an Expert has any ideas please let me know.  

Configuration wise: On the isp's router there are two phone jacks. I go from jack One …
0
I have two tables with the same fields, I need to append those table into one. Is there a way to do it in Crystal report?
0
In Crystal report 2008 I have multivalued parameter that I want to pass to command field is string type. I created formula to put values in array and  and I am getting error.
Here is my formula:

WhilePrintingRecords;



Global StringVar Array DISP;
Local NumberVar i;
Local NumberVar x := UBound({?Supplier_List});
Redim DISP[x];
For i := 1 to x Do
DISP [i] := {?Supplier_List}[i];
Join (DISP, ', ')


I assume that something is wrong with line I know that I am missing something here -->> DISP[i] := {Supplier_List}[i];
Please help.
0
I created crystal report 2008 that is based on store procedure on SQL Server database.
 Store procedure has one parameter “StartDate” that is date type.

How to set up my Crystal Report to prompt user for date and pass that date to sp.
0
Hi!

After two hours in a support call, I could not figure out how to do something that every tech support article in cyberspace outlines to do in Crystal Reports, when you have to summarize a formula.  (Crystal won't let you do so, conventionally.)

I tried the variables where you conduct "x:= while printing records" but to no avail.

If anyone out there has had a challenge with this, please tell me what you did to overcome it.

As best as I can tell you and as best as I can remember from the support call, there is a formula in Group Footer #1 - not in Details - and I need to have a subtotal for this appear in Group Footer #2.

I created several "variable" formulas, I put them in this header, that header, this footer, that footer, the Report Footer, the Report Header, etc.

I did everything to the letter, but I still could not get a simple subtotal to appear.

Please help.

Thanks!

John
0
I have an Oracle report that if a record in a field is empty I have to suppress not only that value but the subreport it is in as well as another subreport that the record is not in but is linked to the primary report the same way as the other subreport.

I know Oracle handles null records, blank records or spaces I think like this. (I may be wrong)
I use the suppress feature on the Conditional Formula for the field
Then I use the suppress section in the Section Expert
for

If isnull({Field1}) then true else
if {Field1} = "" then true else
if {Field1} = " " then true else false.

How do I suppress two subreports if one subreport has a empty record in a field?
0
This is actually a follow up question to  an earlier post

answered by Mike McCracken

I have reviewed the answered proffered by Mike McCracken but seem to have
issues because i am not filling my dataset from a list on the console
but from a method that reads data from a postgre database

I have tried to replicate McCraken solution with the same sample data that he used
and i was able to get it to run

Now i am trying replicate the  major segments  but i have problems  with each section

I got my old CrystalReports Designer loaded  from my Visual basic Application and exported
the .rpt   as  CrystalReport1 rpt

I am dealing here with only one table "Employee"

SECTION 1  (Creating the dataset)   Please see the code below

  private void btnCreateDataSource_Click(object sender, EventArgs e)
        {
            CreateDataSet();
            MessageBox.Show("The dataset is created and filled");
        }

        private void CreateDataSet()
        {
            //Creates a new dataset, 2 datatables and adds data to them
            _dsData = new DataSet("Employee");

            byte[] arrBytes;
            System.IO.MemoryStream ms;




            using (DataTable dt = new DataTable("Employee"))
            {
                dt.Columns.Add("Staff_no", typeof(string));
                dt.Columns.Add("Surname", typeof(string));
                dt.Columns.Add("Firname", typeof(string));
                dt.Columns.Add("midinit", typeof(string));
    

Open in new window

0
I need a formula to look up the last cost date on a job then share the value on to a main report. However, I am unable to use the Share Numbervar because it's not a number but a date.

Maximum(MakeArray({field1}, {field 2}, {field 3}))
0
Learn Ruby Fundamentals
LVL 19
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

I am trying to drag  CrystalReportViewer to my form and i got the following error

CrystalReportViewer  Error
This my first time of using Crystal Report  within Visual Studio

Is there some setting or references  i should set withing visual studio which i have not done
0
How to I split or separate a field in Crystal Reports to return 10 different fields with information.
The field captures answers to 9 survey questions and a comment field.
The field called "ANSWERS" returns the following information:

<pairs>
  <pair name="Q1" value="1" ></pair>
  <pair name="Q2" value="5" ></pair>
  <pair name="Q3" value="4" ></pair>
  <pair name="Q4" value="3" ></pair>
  <pair name="Q5" value="5" ></pair>
  <pair name="Q6" value="5" ></pair>
  <pair name="Q7" value="5" ></pair>
  <pair name="Q8" value="4" ></pair>
  <pair name="Q9" value="5" ></pair>
  <pair name="comments" value="ycjgvhkb,n.m,/" ></pair>
</pairs>

I want to separate to have each questions answer in it's own field

field Q1 should display 1
field Q2 should display 5
field Q3 should display 4
field Q4 should display 3
field Q5 should display 5
field Q6 should display 5
field Q7 should display 5
field Q8 should display 4
field Q9 should display 5
field "comments" should display ycjgvhkb,n.m,/

Any help would be appreciated!
0
Hello,

I have a subreport that is using a sql server stored procedure as its source.   When the sp returns data, the report works fine.  If it has no data to return, the report fails with the message below.
How do I get around that ?  I tried page suppression via section expert but without any luck.  


Error - ExportReportToDisk: One or more fields could not be found in the result set.  Use Verify Database to update the report.
Failed to export the report.

Error in File brm_ecn 4224_5880_{0A81305B-6030-46E1-8C30-8C35C1AB4464}.rpt:
The rowset column could not be found.

Cheers
Bruno Campoli
0
I am converting my visual basic 6 application  to c#.
While in Visual basic 6  i wrote my reports with Crystal Report 11

Now i need to move these report to the crystal reports in my c# application which is
crystal report 13

I have all the forms and designer files ( including the ttx field definition file ) done under crystal report 11

QUESTIONS

1. Is there a path/tool  to migrate my old crystalreport11  file to crystalreport13  or do i have to rewrite all the reports again

2.  my app.config file in my c# application has the line:
<connectionStrings>
    <add name="Tournaments" connectionString="Host = 127.0.0.1; Database = Tournaments; Port = 5432; Username = postgres; Password = manager" />
  </connectionStrings>

How can i make my crystal report recognize this setting so that i do not have to be specifying connection string in
each report.
I just want to be able to get the designer, work on it and run .  it should recognize the app.config setting automatically

the same way my c#  forms behave

Thanks

Olukay
0
I am running a report in Crystal Reports and get the error "Divison By Zero" - the Formula Workshop Window opens and this formula is shown as causing the error - how can i fix the error in this formula

Sum ({@Loan Balance Balance}, {@Pool}) % Sum ({@Total Balance}, {@Pool})

Thanks
0
I am trying to integrate crysta reports into my c#  Application

I have visual studio 17 installed  and i have downloaded crystal reports  from SAP site and i am trying to follow

the instructions below:

EDIT: " To add Crystal Reports to Visual Studio you will first need to download the runtime from SAP. Most commom runtimes can be found here. Then you will have to add the CR to your toolbox in VS by choosing Tools -> choose Toolbox items. Make sure the .NET Frameworks Components tab is selected and scroll to the CrystalReport items. Check the items you want to add (CrystalReportViewer for sure) and click OK. After that the CR Viewer should be available to you in the Reporting section of your toolbox."

I just can not find where to add  Crystal Report Item ( in particular CrystalReportViewer  )

Below please find the screen where I am surpposed  to be adding CrystalReportViewer  from

Screen to Bring CrystalReportViewer into my ToolBox
It is  not Listed

How do i get CrystalReportViewer into my Visual Studio 17

Thanks

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