Crystal Reports Help

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.

3 Comments

HTML5 and CSS3 Fundamentals

LVL 13

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

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.

Crystal Reports

1 Comment

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

9 Comments

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

Crystal Reports

6 Comments

Question by

Gordon Hughes

2019-03-04Solved

Formula Help

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

Crystal Reports

6 Comments

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

Crystal Reports

13 Comments

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)

Crystal Reports

3 Comments

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

Crystal Reports

5 Comments

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?

8 Comments

LVL 1

Question by

Tina K

2019-02-21Solved

Crystal Reports Sorting Challenge

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!

Crystal Reports

2 Comments

Learn SQL Server Core 2016

LVL 13

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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 …

Crystal Reports

4 Comments

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?

47 Comments

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

Crystal Reports

9 Comments

LVL 2

Question by

CipherIS

2019-02-07Solved

.NET 2008 Can't view Crystal Reports

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

13 Comments

LVL 1

Question by

daskas27

2019-02-05Solved

Only Return Weekdays

Hi, I have a labor report that I run using "FROM" and "TO" dates as parameters. Is it possible to eliminate weekends?

Crystal Reports

2 Comments

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!

7 Comments

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

Crystal Reports

3 Comments

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)

Crystal Reports

1 Comment

A Crystal Report no longer prints from a VB6 program running on Windows 7 after the most recent Windows Update. The error = 20534 Error detected by database DLL.

Another VB6 program running on the same computer with similar type reports prints fine.

The Windows Update was KB4480970.

Does anyone have a recommendation to correct the issue?

10 Comments

Build an E-Commerce Site with Angular 5

LVL 13

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 created a formula to include a full address, which works ok
Have the added it to a group called @fulladdress and done a sort on this, seems to work ok, but:

For some reason it is only showing unique @fulladdress records rather than duplicates
The suppress duplicates is not ticked
I guess it has something to do with The section will be printed on change of address, in the Change Group Options
Not what I am looking for, need to see the duplicated records

Any Ideas

Gordon

Crystal Reports

5 Comments

I have a report where the print order as determined by the A-Z criteria says:

Group#1:@Full Name -A
A-@FullAddress

I would like to change this to read with the @FullAddress first
But it will not allow me to change the sort order

On the report I have created Group Header#1a with the @Fulladdress in
and Group Header#1b contains the @Full Name

How do I do this?

Gordon

Crystal Reports

2 Comments

I'm working with a VB6 program that uses Crystal Reports 8.5 on various Windows Operating Systems. The program and reports have been operating for many years until a Windows Update this past week. The error occurs when trying to open a report to view or print "Error = 20534 Error Detected by Database DLL". This only happens with Windows 7. Same program on Windows 10 is not affected.

Removing updates KB4480960 and KB4480970 corrects the issue. Our concern is these Windows Updates will update again shortly. So, my two questions are:

1) Is there a way to not have these specific Windows Updates update the computers?

2) Do you have an idea on what the conflict is where we can update something with the program or an associated file?

5 Comments

LVL 7

Question by

Jeff S

2019-01-23SolvedPrivate

Crystal Reports question

I have a crystal report that does not contain any groupings.

I am trying to add in a field to my report that is causing the report to print numerous additional pages. Is it possible to add to concatenate the values into a string at report print time and have it not grouped? I hope this makes sense and based on the responses, I can provide more details.

Crystal Reports

4 Comments

I want to be able to group the unitCost value by increments of amount value in 5 in Crystal Reports 2008.
How is this accomplished?

Example:

Group 1
Item ID Unitcost
AB123 1
AB124 2
AB125 3
AB126 5

Group 2
Item ID Unitcost
CD123 6
CD124 6
CD125 7
CD126 10

Group 3
Item ID Unitcost
CE123 11
CD124 12
CD127 14
CD128 15

Please help. Thanks.

Crystal Reports

7 Comments

Hi
When I export a report as a PDF document it gives me an extre “t” where there is a t in the text
An example would
The word text would then read ttextt
Any ideas?

Gordon

Crystal Reports

5 Comments

Crystal Reports

Related Topics

Crystal Reports

Related Topics