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 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
I am receiving error "Unable to find the report in the manifest resources.  Please build the project and try again".

I am trying to remove or select a report from report source on the CrystalReport1Viewer1.
ReportSource.JPG
0
Hi all.

I'm working on a Crystal Report (14.0) the datasource is one table (myTable). The table has the following fields:

ModelNumber
ReportColumn1
ReportColumn2
ReportColumn3
ItemNumber
ProductDescription
PackagingString
UnitOfMeasure
Color
Dimensions

The report will pull the ModelNumber and only the fields (ItemNumber, ProductDescription, PackagingString, UnitOfMeasure, Color and Dimensions) that are in the ReportColumn1, ReportColumn2 and ReportColumn3 fields. So, let's say we have ModelNumber = ABC and ReportColumn1 = ProductDescription, ReportColumn2 = UnitOfMeasure, ReportColumn3 = Color, so only the data for those 3 fields will appear on the report for ModelNumber ABC.

We could have another ModelNumber = DEF have ReportColumn1 = ItemNumber, ReportColumn2 = Dimensions and ReportColumn3 is blank. So the report would only show the data for the ItemNumber and Dimensions fields for ModelNumber DEF.

Data in myTable:
ModelNumber--ReportColumn1--ReportColumn2--ReportColumn3--ItemNumber--ProductDescription--PackagingString--UnitOfMeasure--Color--Dimensions
ABC--ProductDescription--UnitOfMeasure--Color--Item1--Yellow pencil--50 pencils per box--EA--Yellow--2" x 8"
DEF--ItemNumber--Dimensions--<blank>--Item2--Red crayon--12 crayons per box--EA--Red--1" x 4"

How it should display in the report:
ABC--Yellow pencil--EA--Yellow
DEF--Item2--1" x 4"

I would also need to display the column header based on the field that appears for each column.

Any …
0
I have a table of records where one of the fields is a date and time. I need to create a report in Crystal Reports XI that will show how many records there are where the date and time falls into each of a 24 hour time period for each day during a specific date range which will be determined using a parameter. i.e.

2/1/2019
00:00 - 00:59 = 0
01:00 - 01:59 = 5
02:00 - 02:59 = 2

2/2/2019
00:00 - 00:59 = 0
01:00 - 01:59 = 1
02:00 - 02:59 = 6

etc...

I need to show all 24 time periods for each day regardless of whether there are any matching records or not. I would like to show a list of the values for each time period as well as a chart that shows the counts graphically.

Any ideas how I should setup this up in Crystal reports?
0
Running Crystal Reports XI on a stand alone, Windows 10, 64 bit PC. I create a very simple report & try to save it. But I'm getting an error - File Not Found. Check the file name & try again.

I've tried doing a fresh install of Crystal on a different PC. I've tried using a different ODBC source. I've tried unticking Save Data With Report.

I get the same error whatever I call the report or wherever I try to save it.

Screenshot attached.
CrystalSave.PNG
0
I am working on a VS 2008 application.  It was copied from one server to another.  In the original server I was able to open up the Crystal Reports rpt file and make changes to it.  Now, when I open it I get below.

How do I fix .NET to view the rpt with Crystal Reports?

I've installed CRRedist2008_x86.msi and CRRedist2008_x64.msi
CrystalReports.JPG
0
Hi, I have a labor report that I run using "FROM" and "TO" dates as parameters. Is it possible to eliminate weekends?
0
Hi All.

Don't know if this is possible but going to give it a shot. We have a SQL table, myTable, that stores item numbers and descriptive information for item numbers:
Table fields: ItemNumber, ProductLine, ProductLineAppearOnReport, Height, HeightAppearOnReport, Width, WidthAppearOnReport, Color, ColorAppearOnReport, UnitOfMeasure, UnitOfMeasureAppearOnReport

As you can see each field has a "AppearOnReport" that will either be Yes or No.

We want to create a Crystal Report that will list the ItemNumber and only the fields that have the "AppearOnReport" equal to YES. The problem is that each item number will be different because some items we don't want to display the Height and Width on the Report while others we do. The problem is we don't want to display a blank where the field would display data if it was set to YES.

So for example, let's say we have item ABC (has ProductLineAppearOnReport = YES and ColorAppearOnReport = YES), item DEF (only has UnitOfMeasure = YES ) and item GHI (HeightAppearOnReport = YES and WidthAppearOnReport = YES) here's how the data should appear on the report:

ABC--ProductLineA--Blue
DEF--Box
GHI--12 feet--10 feet

As you can see there is no blank space where ProductLine or Color would be if it was set to YES for item DEF and the same for item GHI

Any idea if this can be done?

Thank you in advance!
0
I am working with Crystal and working with parameters definitions.  When I link the params together I only see 2 of the 10 values for the INVBWHSNCODE?  Not sure where to look and how to solve this issue.    
I pulled the data apart so I know the data is correct...   Would it be hitting a limit of some kind?
Please see screen copy.
Any help would be appreciated.
Thanks
Terry
CrystalParams.jpg
0
Crystal Reports Calculate and display date 9 days ago.    the formula below works but only within the same month.    Can't figure out why.     So if today minus 9 days stays within the current month it works.     But as in todays date of Feb 4th.    9 days ago is a date in January the formula does not work.

numbervar DATEFROM2;

DATEFROM2 := tonumber(totext(CurrentDateTime, 'yyyyMMdd'));

DATEFROM2 := DATEFROM2 - 9;

pwformatdate (DATEFROM2)
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
>