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

Hi,

Im new to Crystal.

I have an excel file that i'm using as my datasource file.

In the excel file, there is a column I (Customer Search Term)

I want to create a Formula Field to view only the first word of column I.

For example,  the below Customer Search Term, i want to extra only the first word "anysharp".


anysharp pro knife sharpener

But, there are first word or number begin with 20 or 3 in the Customer Search Term (column I), i want those too to be extracted like shown below

7 degree knife sharpener

I want to extract "7" only

7

Hope i have explain my problem above and thanks for your help.

Leonard
0
Bootstrap 4: Exploring New Features
LVL 13
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

I have a web application running in classic asp and I am currently using the crystal reports viewer to allow users to generate and download reports. My problem is I need to disable the IIS sessions and switch over to using cookies in order to handle a problem with query overlap on the web application. That is all working fine but the crystal reports viewer pages use sessions. Is there a way to run the crystal reports viewer without using sessions. Below is a sample of one of the crystal reports pages that  generate the reports.

<%@ Language=VBScript %>
<%
Dim Report_Template
Dim basepath, baseVirtualPath, iLen
Dim Param1, Param2, ParamValue2, P2, P2R, ParamValueEndRange2
Dim curValue
Dim viewer
 
basePath = Request.ServerVariables("PATH_TRANSLATED")
While (Right(basePath, 1) <> "\" And Len(basePath) <> 0)
    iLen = Len(basePath) - 1
    basePath = Left(basePath, iLen)
Wend
Response.Write basePath
baseVirtualPath = Request.ServerVariables("PATH_INFO")
While (Right(baseVirtualPath, 1) <> "/" And Len(baseVirtualPath) <> 0)
    iLen = Len(baseVirtualPath) - 1
    baseVirtualPath = Left(baseVirtualPath, iLen)
Wend

If Not IsObject(session("oApp")) Then
    Set session("oApp") = Server.CreateObject("CrystalRuntime.Application.11")
    If Not IsObject(session("oApp")) Then
        response.write "Error:  Could not instantiate the Crystal Reports automation server.  Please check to see that Crystal Reports or ReCrystallize has been properly installed on the web server PC.  

Open in new window

0
Hi Experts!
I have a crystal report that has a mock crosstab with 48 spaces.
Techs use this as a plate map for testing samples.  Each batch can have up to 47 samples (first cell is always a control).  
I need to be able to put the sample ID in the correct space but have not been able to do that so far because the batch does not always start on cell #2.  If the plate has cells that are not suitable for testing, they can start a batch on the next available cell, like cell number 22, for instance.
How can I possibly place the correct ID's in the correct cells whey they're starting at random numbers?  I created a table with 48 numbers as "cell addresses" and have a parameter called "First Cell" for the tech to enter the first cell they are using but I can't seem to get any further - how do I link the two??
Any attempt I've made to use *next* function works great on first and second values, but doesn't go anymore.  The ID's are alphanumeric so i can't really use +1.  I've also tried creating arrays and then calling each element out one by one but that's not working well either.
This mock table is in the report footer.
Any help is much appreciated!
0
In crystal report 2008 how you map old command to new  command they have the same filed but some selection criteria are different. What are the steps. Do I need to have old command in report then add new command or I remove old then add new?? Both of them working on same tables from the same database.
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
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
Build an E-Commerce Site with Angular 5
LVL 13
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

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
Introduction to R
LVL 13
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.

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.