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

In crystal report 2008 I wanted to edit command but could not do it as edit is not working in Database Expert (It looks like is working, I can select command then right click and go to Edit Command, it is available on menu but when I edit code and close Modified code window nothing is changed) So I went on and removed existing commad and added new command.

 I created new connection I added new command in report(copied old SQL query) It looks ok,  but command will not extend and show fields as previous command did. So  I got error,  can not find a field. I can not correct this as new command is not extending and allowing me to change field name. It behave as it is not connected with database table at all.

Any Idea.
0
Below is Crystal Report formula for Previous year three month range.
RunDate is parametar passed in crystal report and =  02/28/2019

Below is date formula from sQL which I use to cheeck data.

Should not those two date formula give me the same records?

In Crystal report I am missing records that have date in month December of 2017.
in SQL Management Studio i am getting those records.
What is wrong with this Crystal Report formula why it missed December of 2017.

NumberVar LastYearQrtAmount;



DateVar SMonth;
DateVar EMonth;


Datetimevar RunDate;
DateTimeVar StartDate;

RunDate:= {?Run_Date};
EMonth:= Date(Year(RunDate)-1,Month(RunDate),Day(RunDate)); // this is one year back date



StartDate:= DateAdd("m",-2,EMonth);
SMonth:= Date(Year(StartDate),Month(StartDate),1);// this is one year back then back 2 months and first day of month



If 
(
{@InvoiceDate}In (dateserial(year(EMonth),Month(EMonth) +1,1-1) to //this the end of run month 
dateserial(Year(sMonth),Month(sMonth),1))//this is begingin of three month back
)


Then

LastYearQrtAmount:={Command.TotalAmount}-{Command.TotalTaxes}

Open in new window


***************************************************************************
This part is from SQL Server Menagement Studio


Declare @St_D as date
Declare @En_D as Date

Set @St_D = '2017-12-01'
set @En_D = '2018-02-28'

Open in new window


..........
......
And
(
 InvoiceDate >= cast(convert(char(8),@St_D,112) as int)
 And
  InvoiceDate <= cast(convert(char(8), @En_D,112) as int)
)

Open in new window

0
Hello:

I am finishing building my custom Sales Order Report that I want to run with my ERP software automatically. The report will be scheduled every morning between 1a - 3a and needs to look at 'yesterday's' orders that was entered in and then e-mail it out to the correct folks.

I have literally spent over a week 'Googleing' around on the web different ways of explaining what I want like 'data parameters crystal report 2016', etc. and I have been A.) unable to find anything that is CR 2008, which has some what a different interface/options than the newer version (CR 2016 SP3) so I am having a hard time finding the information to at least point me in the right direction.

I know a Formula will need to be built then 'Parameter Field' I think but I am not achieving the result I am looking for.

Currently, I have a pop up box that appears and asks for a date, which is working if you manually enter in the dates. However as I previously mentioned I need the date to be set to 'yesterday's' date and not pop up a box so that my report can run automatically.

Thank you all!
0
Hi there,

Is there a way to trim data based on field content?

For example;

Other Campaign FYE 2019
Other Campaign FYE 2020

I need to group by Other Campaign but need to remove all information from the FYE and after so that is says only Other Campaign.

I can't wrap my head around it - any help would be appreciated,

Thanks - CS
0
Hi,
I have a Crystal report that I would like to show me elapsed days for a given group. For example, the group is a machine center and I am tracking labor dates for that machine center. If the earliest labor date is 11/03/2018 and the latest labor date is 11/06/2018 I would like the result to show 3. Is this possible? I have attached a sample report in case that helps.

Thank you,
Dave
example.rpt
0
I am trying to update a Crystal Form that came with our ERP system and add a barcode that represents the quantity shipped on a packing slip. This form already has barcodes on it and are working good but when I try to set a barcode with the quantity shipped it splits the barcode and places the quantity barcode below the field then places another barcode on a new line above the Quantity field. I think it is because the quantity is 6.00 and not just 6. In addition to adding this barcode I am changing the code on the existing barcodes from 3of9 to 128. All the previous barcodes converted fine it is the quantity field that is causing me issues. How can I fix this issue. I am using IDAutomation barcode/fonts and I have followed their instruction set but am still getting this issue/error.
0
Hello experts,
I created a report which is displaying 2 records for each student. The student ID, Name, DOB all these fields are repeated and is same, the only difference is there are other fields that are not similar, 'Submit Date', 'Approved by ID, 'Approved by'. I would like to merge this data into one record. Is this possible in Crystal report?  Thanks in advance.
This a dummy data for reference.

Current result:

StudentID Name 'Submit Date'                     'Approved by' 'Approved by ID'

1234,        SAM, '10/01/2018 4:04:18 PM',     Hary,              hry243

1234,        SAM, '10/02/2018 12:30:51 PM',   John,              jhn334

Open in new window


Expected result:

StudentID      Name     'Submit Date 1'               'Approved by 1' 'Approved by ID 1'             'Submit Date 2'             'Approved by 2' 'Approved by ID2'

1234           SAM      '10/01/2018 4:04:18 PM',           Hary,            hry243,                  '10/02/2018 12:30:51 PM'      John                  jhn334

Open in new window

0
We need to have the Group Footer to be on the bottom of the page.

Group 1A, 1B (Keep Together)
Group 1A, 1B (Print at Bottom of Page is selected)
Group 1C (New Page Before is selected)
Group 1A & 1B are not on the bottom. Sometimes it shows in the middle of the page.  Group 1C works fine.

Group 1A  & 1B - Are the Invoice Totals and need to be on the bottom.
Group 1C is on another page like it should be. That is the terms and conditions.

I noticed that if I take the Group 1C out of there then it works like it should.  Not sure on how to fix this.
0
I have a SQL table that contains a series of events that are in chronological order that I need to run a crystal report on that will calculate and then total the time between specific events.  The table fields that are used are the event date, event name. The problem is that the two events that I need to get the total time for may not be back to back events. Other events may happen in between the two events  For example. A sample of these events might be as follows.

2019-03-01 10:00:00 Logged In
2019-03-01 10:03:00 Paused
2019-03-01 10:05:00 Un-Paused
2019-03-01 10:05:10 Called
2019-03-01 10:05:10 Completed
2019-03-01 10:08:00 Logged Out
2019-03-01 10:09:10 Connected
2019-03-01 10:15:10 Complected
2019-03-01 10:21:00 Logged In
2019-03-01 10:30:00 Logged Out

I need to calculate the total Logged In Time based upon when the agent Logged In and Logged Out, which in this example was twice but there was a period of time between the first logout and the second login when the agent was not logged in. In the example above the Total Logged In Time would be 17 minutes. Basically when I find a Logged In Event then I need to find the next Logged Out event and use the date and time difference to get the duration but how do I find the next Logged Out event?
0
I am working on old report created by somebody else. Report is supposed to do rolling month, 3m, Year comparing with a previous Year month, 3m,Year.
I have a question regarding calculating dates and want to understand it better.
Client asked for setup like If run date is 28/03/2019 that rolling month range should be something as:
This year : 28/02/2019 – 28/03/2019.
And
Last Year: 28/02/2018 – 28/03/2018.
Similar patern for month and Year.

What is now in report for calculating This Year Month and Previous Year month is here:
//This Year Month
NumberVar Amount;
Amount:=0;

If Year({@InvoiceDate}) = Year({?Month Of Report}) And  Month({@InvoiceDate}) = Month({?Month Of Report}) then
Amount:= {Invoice.Amount}
Else
Amount:=0;
Amount;

//Last year month

NumberVar LastYearMonthAmount;

If Year({@InvoiceDate}) = Year({?Month Of Report})-1 And  Month({@InvoiceDate}) = Month({?Month Of Report}) then
LastYearMonthAmount:= {Invoice.Amount}
Else
LastYearMonthAmount:=0;
LastYearMonthAmount;

If I understand properly this formula will not compare the same range if report is run on 03/28/2019.

In this year it will take Date 03/01/2019 to 03/28/2019
But in Last Year it will take 03/01/2018 to 03/31/2018
Am I Right??


To meet client request I created something as this regarding rolling this year month and last Year month :
NumberVar Amount;
Amount:=0;
DateVar SMonth;
DateVar EMonth;
Datetimevar RunDate;
DateTimeVar StartDate;
RunDate:= {?Month …
0
I have a report with a group that passes a parameter value to a sub report. That group value can have multiple values based upon what the user selects and if there are more than one then a subreport is generated for each value. That all works fine. The problem that I have is that the user wants each subreport to be displayed based upon the order of the group value that they entered instead of ascending or descending. For Example, if the selected B , C, and A  for the group values in that order then the first subreport would be for group value B, the next one would be for group value C and the last one for group value A. If I choose ascending or descending for the group sort order then the order of the subreport changes accordingly but when I tried using "original sort order" it still sorted the subreports in ascending order. How can I sort the group based upon the order of the parameter values?
0
Need to have a pie chart in a Crystal Report reflect the FMV %. In other words, as the example attached, I need to show 21% of the pie chart out of 100%. I have already added the same field that FMV % is using but cannot figure out how to make it display in percentage within the Pie chart.

Any guidance will be greatly appreciated.
Capture.JPG
0
We are trying to hide a field in crystal report 2013 based on a formula.  We have added a formula called testcount as
local numberVar count:= 0;
local stringVar comma := {Ens_RptSingleCertNew;1.job};
Local numberVar strLen:=length(comma);
local numberVar I;
for i:=1 to strLen do

    If Mid(comma,i,1)= ","
        then count := count +1;
count;

We attempt to suppress the field based on the count being > 2 .   This has no effect yet when we display the count on the report it show as 3.00.  We only need to hide this one field any help would be appreciated.    We have the field suppression set as:
{@TESTCOUNT} > 2.00
0
I have been using Crystal Reports to report data from DB2 tables on an iseries for a long time, both using the database wizard and writing my own SQL commands. What I would now like to do is to call a utility program on the Power 9 and retrieve the output and incorporate it into a Select statement in CR to then display in a report. I will need to pass a number of values including both strings and numbers. My first thought was that maybe there is a way crystal can use a stored procedure syntax to call an rpgle program, but I have no idea how this will work, and I have not been able to find an example or two. Can you point me in the right direction? One of my goals is to utilize programs for calculating shipping & tax charges that already exist on the power 9 box by passing in customer numbers and shipping terms and methods, and order info.
0
what is the name of the 'management console' which is used to manage security/permissions/users in sap business objects? And where does all the configuration data reside relating to the security configuration, e.g. does business objects come with a standard database for storing configuration information, and out of interest is that typically oracle/mysql/MSSQL.
0
My report has a sub-report for Freight Hours using a formula as a lookup.  I get the hours, but need a Shared NumberVar so I can use the hours (which all works fine) and multiply by it's Unit Cost.

My main report needs to summarize the detail lines in a Group Footer using sum (without showing the detail lines which also contains the sub-report for the Freight Hours).  Since CR won't let you use Running Totals on Shared Variable I had to use create Formula Fields using WhilePrintingRecords for summing Freight Cost for the Item, Customer & Company (adding these to details section b with reset Formula Fields setting the Variable to 0 in its respective Header)

The problem is, if I hide or suppress the detail lines, WhilePrintingRecords will not evaluate so my Group sums correctly the Qty, Sale Price, Cost of Goods Sold, but shows the Freight Cost (the Sub-Report) as 0.  Therefore the Profit/Loss does not deduct the Freight Cost.

If I show all the details a, b, c, the report runs perfectly (but they only want to see the summary not every detail line)

Is there a way to hide the detail lines, but let CR calculate the Fromulas based on WhilePrintingRecords (I don't want to shrink the detail lines as some Items might have 50 detail lines others 1, so even minimal detail height will still make the report's look inconsistent)

Other than creating another Sub-Report to bring in the summary line, with a the current Sub-Report for the Freight Hours is there another way of …
0
Greetings

I have the following code:

CRReports.crRpt.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, True, "Title of the Report")
CloseCrystalReports()

.
.
.
.
Private Sub CloseCrystalReports()
        If CRReports.crRpt IsNot Nothing Then
            CloseReports(CRReports.crRpt)
            CRReports.crRpt.Close()
            CRReports.crRpt.Dispose()
            CRReports.crRpt = Nothing
        End If
        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
    End Sub

Now since the the CR is opened in a New window, the  CloseCrystalReports() never gets called. I thought I could add the close and dispose to the CRReport.vb but I don't know what I would be closing or if it is even possible.

Please advice.
0
I have a report with the following formula
if isnull ({MembersDetails.Email Usage}) or {MembersDetails.Email Usage} = ""  then 'Not recorded' else ({MembersDetails.Email Usage})

I have data in the database of Null , blank or with Yes or No

The formula on give a return of Not Recorded when there is a Yes or No

Gordon
0
Hi Experts

I need to be able to select only the records from the last completed hour of the current day.  So if it 8:49 am currently and the report runs, it would only select the records from 7 am until 7:59 am.  

I've tried a number of things but I am just not getting it right.  Your help is greatly appreciated as always.

A
0
Help with Formula

I have the following formula:-
{MembersDetails.Membership Main} in ["Junior Intmd", "Junior U12", "Junior U18", "Social TP", "Tennis", "Tennis 18 25", "Tennis Day", "Tennis Hon", "Tennis Ovr65"] and
{MembersDetails.ExpiryDate} >= {?Expiry Date is Greater Than} or isnull ({MembersDetails.ExpiryDate})

There are some of the {MembersDetails.Membership Main} with no expiry date so added or isnull ({MembersDetails.ExpiryDate}) to the formula but it give me all the members with no expiry date , but only want to see the membership main categories in the first part of the formula

How do I do this?
Gordon
0
Hi
I have a report with hours shown see attached
What I would like is a formula which where the WO Group is "Grouped" to divide the hour figure by the number in that group which may vary from 2 line to many, the criteria for the group is the task number and the next due date
Where the WO Group is "Separate" the hours figure remains as is

Gordon
Sample-report-data.docx
0
How do I get this to show all the records in the previous month?  This works to show all the records in the current month.

{TGC_Ship__Summary.ShipHead.ShipDate} >=dateserial(year(currentdate), month(currentdate) ,1) and
{TGC_Ship__Summary.ShipHead.ShipDate} < dateserial(year(currentdate), month(currentdate)+1 ,1)
0
Hope somebody can help with this one:
I have a field that is defined as having all seconds, how can I get the Time of Day out of that field.
field is 908022961 (seconds)
 to_date('19900101','yyyymmdd') + "starttime"/86400 as startDate,  (From SQL---but would like to use Crystal to get the Time of Day).
it will convert to this date 2018-10-10 with the above statement.  

Date is 10/10/2018,   but I need to find the time of day for this.  
Thanks
Terry
0
Greetings

I have inherited some code from a colleague and I noticed that the SAP Crystal Reports are not disconnecting from the SQL Server. The application does close and dispose of the crystal viewer instance. I have posted the code here:

Private Sub CloseCrystalReports()
        If CRReports.crRpt IsNot Nothing Then
            CloseReports(CRReports.crRpt)
            CRReports.crRpt.Close()
            CRReports.crRpt.Dispose()
            CRReports.crRpt = Nothing
        End If
        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
    End Sub

Open in new window


I my attempts to resolved the issue, I also added this:

Protected Sub Page_Unload(ByVal sender As Object, ByVal e As EventArgs)
        If CRReports.crRpt IsNot Nothing Then
            CloseReports(CRReports.crRpt)
            CRReports.crRpt.Close()
            CRReports.crRpt.Dispose()
            CRReports.crRpt = Nothing
        End If
        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
    End Sub

Open in new window


I also found that the issue may be with SQL Server and Connection Pooling, so I added this to the connection string:

Pooling=False

I do not see an increase in memory usage on the application server. But I do see that the connections to the database on the database server does not decrease after the application is closed.

Does anyone have any other suggestions?
0
I would like to sort the part number group by the minimum of a date field. Currently, the report sorts by part id, putting the records in order by Ship Date, oldest to newest.  Management would like to retain the sorting by part id, but then have the next group of parts display by the minimum of the ship date.
In the example shown, the first group displayed should be part id 12414286, since it has the oldest ship date of all the part groups.  Next should be part group 1241853DTNB with the min ship date of 2/26 - third should be 12417908 with the min ship date of 2/27, and so on.
Is this possible in CR?  I've tried everything I know, as well as hours of Googling to no avail.  Suggestions are greatly appreciated!cr example
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
>