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

Hello:

Below is the programming that the attached Crystal report is based on.  Also below is the Select Expert programming that I have tried and failed at.  I need to get the Select Expert to match the "where" of the programming.  Please help.

FYI ME97707.ME_User_Defined means "Editor".

Thanks!

TBSupport



ME97708.ME_User_Defined AS [Editor], cast(COALESCE (ME97705.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97708.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97705.ME_Breakdown_Code = 'SALES' THEN ME97705.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97705.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97708.ME_Job_Close_Date, '') AS [Close Date],*/ CASE WHEN cast(ME97708.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97708.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97708.ME_Job_Close_Date AS DATE) = '1900-01-01' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
FROM         ME97708 LEFT OUTER JOIN
                      ME97705 ON ME97705.ME_Job_ID = ME97708.ME_Job_ID LEFT OUTER JOIN
                      GL00105 ON ME97705.ACTINDX = GL00105.ACTINDX LEFT OUTER JOIN
                      ME97702 ON ME97702.MEuserdefined3 = ME97708.MEuserdefined3
                      where ME97705.DOCDATE BETWEEN '01/01/2010' and …
0
Hi,

is there a way to suppress a header section if other sections below it are suppressed?

Section header A (Suppress it if all 3 sections below are suppressed)
section b (Contains subreport 1) * Suppress section if subreport is blank
section c (Contains subreport 2) * Suppress section if subreport is blank
section d (Contains subreport 3) * Suppress section if subreport is blank
0
I recently reinstalled crystal reports 8.5, however even when I try to execute a report I get an error "missing pdssql.dll. "
I have installed the client for sql 2005.

Is there anyone who knows how to find this dll or solve this problem?
0
Hello:

Attached is my Crystal report.

At the very end of this posting is the T-SQL programming of the view, called "CARBON", that the report is based upon.

For some reason, the report is not picking up all of the data.  I think that it has to do with the Select Expert.  The data in the first half of this T-SQL statement, before the UNION ALL, is not being picked up.  Specifically, I think that it has to do with the CloseDate field.  When I use the parameters of the report, only the second half of the view is being picked up.

The following is the T-SQL view, if you add "where" clauses to it from what is supposed to be in the Select Expert.  (I hope that makes sense.):

SELECT     /*DISTINCT */ ME97708.ME_Job_ID AS [Project], ME97708.ME_User_Defined AS [Editor], cast(COALESCE (ME97705.DOCDATE, '') AS DATE) AS [DocumentDate],
                      ME97708.ME_Work_Scope AS [Product], ME97702.DSCRIPTN AS [Company], CASE WHEN ME97705.ME_Breakdown_Code = 'SALES' THEN ME97705.TRXAMNT ELSE 0 END AS [Sales],
                      COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97705.TRXAMNT ELSE 0 END AS [TransAmt],
                      /*COALESCE(ME97708.ME_Job_Close_Date, '') AS [Close Date],*/ CASE WHEN cast(ME97708.ME_Job_Close_Date AS date) <> '1900-01-01' THEN cast(ME97708.ME_Job_Close_Date AS DATE)
                      ELSE NULL END AS [CloseDate], CASE WHEN cast(ME97708.ME_Job_Close_Date AS …
0
Hello,

Is any way to put formula on regular sum or I need use running total ?

I need sum based on below formula:
if (tonumber({OrderDtl.OpenLine}) = {OrderDtl.KitParentLine}) then {OrderDtl.OrderQty} else 0

I was trying to use running total but show mi nothing or all records.
thanks

henry
0
I am trying to calculate totals hours worked in my report.  The fields that I am originally using are a string and I converted to time field by using the ctime formula.  So for my formula I was trying to use the datediff function and when I do an error checking it errors out and says I need the field to be a datetime or time field.  I also a changed the field format to a 24 hour clock.

This is what I want to accomplish   EndTime (ex.16:00) - StartTime (ex 08:00) = total hours worked (ex 8:00)
This is driving me crazy for something simple in theory has to  be so frustrating implementing it.

Any help is appreciated
0
I have a debtors statement that is grouped by a field called STMTGROUP and totals transactions within that group fine. I now want to have that total appear in a Summary Grid in the Group Header but don't know how to.
0
I have a collection of requests with a current status and status history.
the history table is linked to the request table using the request # as a key.

Data in the report is grouped by request # with the status history date/timestamp and status value as the detail records.

There are 12 possible statuses a request can have. Each request can enter a status an infinite number of times.

I need to have a distinctly store the most recent date timestamp when the request was in each status - so 12 fields in total. If a request has never been in a status i want to default it to 1/1/1900

From there each of these 12 fields will be stored in a group header (or group footer)..

Using the threads below as a reference point:
http://www.experts-exchange.com/Database/Reporting/Crystal_Reports/Q_23657542.html

I have the following formulas / variables

**variable date history - placed in detail section and in group header.
 //this is returning the first value returned in the detail section when in the group header...
 // the detial section is returning the correct value....
WhilePrintingrecords;
 Global stringVar itsr_history_date;
 Global stringVar itsr_history_date1;
 global stringVar itsr_history_status;
itsr_history_status := {ITSR_Scope_Def_Status_History_Status_admListValues.ListValue};


If itsr_history_date = totext(cdatetime(1900,01,01,01,01,01),"MM/dd/yyyy hh:mm:ss") and
itsr_history_status = "Business Approval Pending"

then …
0
Hello:

As you can see in the attached CARBON view and in the DocumentDate field Word attachment, I changed the DocumentDate in the view to strip out the time, so that the field would be date instead of DateTime.

But, Crystal will not let me map the DocumentDate field.

Why not?

TBSupport
DocumentDate-Field.docx
CARBON-View.docx
0
Hello:

Attached is my Crystal report and below is the T-SQL view that the report is based on.

How do I make 01/01/1900 be blank instead of a date/datetime in the Crystal interface?

 Thanks!

 TBSupport

 SELECT DISTINCT 
                       ME97708.ME_Job_ID AS [Project], ME97708.ME_User_Defined AS [Editor], COALESCE (ME97705.DOCDATE, '') AS [DocumentDate], ME97708.ME_Work_Scope AS [Product], 
                       ME97702.DSCRIPTN AS [Company], CASE WHEN ME97705.ME_Breakdown_Code = 'SALES' THEN ME97705.TRXAMNT ELSE 0 END AS [Sales], COALESCE (GL00105.ACTNUMBR_1, '') AS [Account], 
                       CASE WHEN GL00105.ACTNUMBR_1 NOT IN ('4001', '9150') THEN ME97705.TRXAMNT ELSE 0 END AS [TransAmt], 
                       /*COALESCE(ME97708.ME_Job_Close_Date, '') AS [Close Date],*/ CASE WHEN ME97708.ME_Job_Close_Date <> '1900-01-01 00:00:00.000' 
                       THEN ME97708.ME_Job_Close_Date ELSE NULL 
                       END AS [CloseDate], CASE WHEN ME97708.ME_Job_Close_Date = '1900-01-01 00:00:00.000' THEN 'OPEN' ELSE 'CLOSED' END AS [Status]
 FROM         ME97708 LEFT OUTER JOIN
                       ME97705 ON ME97705.ME_Job_ID = ME97708.ME_Job_ID LEFT OUTER JOIN
                       GL00105 ON ME97705.ACTINDX = GL00105.ACTINDX INNER JOIN
                       ME97702 ON ME97708.CUSTNMBR = ME97702.MEuserdefined3
 /*where ME97708.CUSTNMBR = 'BCFB'*/ UNION ALL
 SELECT DISTINCT 
                       ME97707.ME_Job_ID AS [Project],

Open in new window

0
Using the following formula I am getting the correct Job Type, just not the correct month. All months are showing as if it is not reading the last statement.

if {?Job Type}='Resi' then
{jb.jb_id} like 'RPMA*' else
if {?Job Type}= 'Comm' then
{jb.jb_id} like 'CPMA*' else
if {?Job Type}='Elite' then
{jb.jb_id} like 'EPMA*' and
{@Scheduled Month} = {?Month}

The (@Scheduled month) is referencing
(@Date Scheduled)  is Date(Sys_schdl_Dte)
(@Month Number) is Month(@Date Scheduled)
(@Scheduled Month) is MonthName(@Month Number)

If I just type that (@Scheduled Month)='July' it only returns July. If it is the 'only' select statement referencing the parameter it works.


Seems simple....any help is appreciated
0
Formula editor opens in full screen. When you try to minimize it disappears and everything is locked. You can get it back using alt space, then maximize. This is a real inconvenience. There were recently some virus issues on our system and they reinstalled my Crystal at this time, This is Version 11.5.12.1838. The problem started after the reinstall. Is there a setting that would control this?
0
Hello,

I am creating a report which includes a summary at the end.  The groups are based on product type.  In the summary I need to show the page number relating to a particular group, i.e


Product Group 1
              Date                      Qty                              Price
               12/12                   1                                   1.00
               13/12                   1                                   1.00
             Total                      2

Product Group 2
              Date                      Qty                              Price
               12/12                   2                                   1.00
               13/12                   2                                   1.00
             Total                     4

Summary Report
                   Group                                    Total                Page Number
               Product Group  1                         2                              1
               Product Group  2                        4                              2

Each group triggers a new page.  

I have produced the summary OK, it's just the page number which is the challenge. Please could anyone suggest an approach.

I am working in VB.

Thanks
0
Purpose: Create report showing "only" employees having both EARNINGS and DEDUCTIONS for the same pay period.

Data:       Each record in table (PRDT) contains fields with; PayDate, Employee#, RecordType, RecordCode, Amount, Hours.  

Below is my Select Query (tables in addition to PRDT are involved for inclusion of demographic data).
The WHERE CLAUSE has been formated here for purpose of clarity:


 SELECT "PRDT"."PRCo", "PRDT"."PREndDate", "PRDT"."Employee", "PRDT"."EDLType", "PRDT"."EDLCode", "PRDT"."Hours", "PRDT"."Amount", "PRPH"."PaidDate", "PRPC"."BeginDate"
 
 FROM   ("DataBase1"."dbo"."PRDT" "PRDT" LEFT OUTER JOIN "DataBase1"."dbo"."PRPH" "PRPH" ON (((("PRDT"."PRCo"="PRPH"."PRCo") AND ("PRDT"."PREndDate"="PRPH"."PREndDate")) AND ("PRDT"."Employee"="PRPH"."Employee")) AND ("PRDT"."PRGroup"="PRPH"."PRGroup")) AND ("PRDT"."PaySeq"="PRPH"."PaySeq")) LEFT OUTER JOIN "Viewpoint"."dbo"."PRPC" "PRPC" ON (("PRDT"."PRCo"="PRPC"."PRCo") AND ("PRDT"."PRGroup"="PRPC"."PRGroup")) AND ("PRDT"."PREndDate"="PRPC"."PREndDate")

 WHERE  ("PRDT"."PREndDate">={ts '2015-07-26 00:00:00'} AND "PRDT"."PREndDate"<{ts '2015-07-27 00:00:00'})
               AND  ("PRDT"."PRCo"=6 OR "PRDT"."PRCo"=28)

               AND  ((("PRDT"."EDLCode"=0 OR "PRDT"."EDLCode"=1 OR "PRDT"."EDLCode"=2 OR "PRDT"."EDLCode"=3)
                          AND "PRDT"."EDLType"='E')
                  OR  (("PRDT"."EDLCode"=401 OR "PRDT"."EDLCode"=402 OR "PRDT"."EDLCode"=403 OR "PRDT"."EDLCode"=410)
 …
0
I have a crystal report that I would like to reference the field in a formula.  How can this be done?
I have already tried {myfield} does not work
0
I have a formula that concatenates two text fields and puts a linefeed/carriage return wherever there is a semicolon.
The new line, after the line feed/carriage return, is indented one space. I want the text all aligned to the left with no indents. I checked the field format paragraph alignment and all indentation settings are 0.

Here’s the formula:
Replace({Command.T3_2215}+ ' '+ {Command.AISDCURRENTPROJSTATUS1},";",";"+Chrw(10))

Using Chrw(13) does the same thing.

Here’s the output:

Metal framing nears completion;
 Roof work is 40% complete;
 MEP rough-in is nearing completion;
 Driveway curbs and gutters are scheduled to pour 7/31/2015;
 Retaining wall at the pond excavation is
underway;
 Brick is onsite and mock up panel has
been approved.
0
Hello:

Attached is my Crystal report for version 10.0.0.533, and attached is the SQL view that represents the programming for the report called "CARBON".

The report forces the end user, for parameters, to choose DateTime values based on 12:00:00AM.  This is true, when creating the Default Values and when the end user is prompted to choose dates in the "Dates" parameter and in the "Close Date" parameter.

Is it possible in the programming of the view, or in Select Expert of the report preferably, to enter some formula that does not force the end user to worry about the time element?

Also, for NULL dates, the end user has to choose 01/01/1900.  Is there a way to allow the user to choose say "blanks" instead of "01/01/1900".

Thanks!

TBSupport
CARBON-COMMISSION.rpt
CARBON.docx
0
Hello:

Attached is my Crystal Report for version 10.0.0.533, and also attached is the programming for the report that is based on a stored procedure in a SQL database.  The name of this stored procedure is spCommissionSummary;1.

In the report, there is a parameter called "JobStatus".  The choices are either "Open" or "Closed".  I'm hoping that the parameter will, actually, allow the end user to choose both choices if need be.  That's my ultimate goal, here.

Also in the report is a formula called "JobStatus".  This formula says that if the parameter called "JobCloseDate" does not contain the date 01/01/1900, then the job is closed, else the job is open.

To that effect, this report parameter called "JobCloseDate" that pulls from a field in the spCommissionSummary;1 stored procedure (attached) called ME_Job_Close_Date.  This parameter offers "Discrete and a Range of Values".  

The Select Expert of the report states that the formula "JobStatus" equals the Parameter "JobStatus".  Here is the Select Expert's formula:

(
{@JobClosedDate} = {?JobCloseDate}
or {@IncludeOpenJobs} = 'True'
) and
{spCommissionSummary;1.ME_User_defined} <> "" and
not ({spCommissionSummary;1.ACTNUMBR_1} in ["5170", "5175"]) and
{@JobStatus} = {?JobStatus} and
{spCommissionSummary;1.DOCDATE} = {?Dates}

The following are my questions:

(1) Does the parameter "JobStatus" work, where the end user can choose both "Open" and "Closed" rather than one or the other if necessary?
(2)  …
0
Using Win 7 Enterprise and Crystal Report.
Crystal Reports are defined through an addon to MS Visual Studio
When generating a report it creates files with the extension .rpt
In File Explorer i can double click on it and MS Visual Studio kicks off an presents me the report.
So far so good, but
I would like to see a preview of the report in  the normal file explorer
What should the association in registry be ?? PerceivedType = ?
For a normal text file with just a funny extension it would say PerceivedType=text

Thanks for any suggestions
0
I have created Main Report - Outlet Performance to read the cost and Subreport to read the sales
This is the formula in main report :
WhilePrintingRecords;

Shared NumberVar ShareSales;
""
And this is the formula from subreport:
Name of Formula: ShareSales

WhilePrintingRecords;

Shared NumberVar ShareSales;

ShareSales := Sum ({_DemandDtl.BatlD}, {_OUTLET.CPU});

""
I tried to run subreport, it working fine with the sharesales figure, but the moment I add to the main report, the share figure jump to next record (next outlet). It do not follow the link set in the subreport link
Outlet-Performance.jpg
outlet-performance-report.pdf
0
I am working on a daily sales report that sets a sales goal for the current month based on the total sales of the past six months.  So for this month (August) I need to see the total sales from 2/1/2015 through 7/31/2015 to calculate the new goal for the month.  I need that range to change as the months go by,

Right now my formula looks like this month(currentdate)-6 would give me the start of the range by returning  a 2 for February.  However, if the current month was March the same formula returns -3 instead of 10 for October (previous year).

I'm also not sure how to get the end of the range (last day of previous month).
0
Hello:

In Crystal Reports 10.0.0.522, when I add more than one table in order to add a link, I get the following error message:
Crystal Reports has stopped working.  A problem caused the program to stop working correctly.  Windows will close the program and notify you if a solution is available.

In order to add this other table, I created a new USER DSN and added that DSN to the report.

I took the steps called for, from the following URL, but they did not work:

http://community.spiceworks.com/topic/611328-crystal-reports-crashes-when-trying-to-add-or-link-multiple-tables

Specifically, the reg key does not exist on the machine that I'm working on.

I, also, rebooted.  But, that did not solve the problem.

What is the fix, for this, then?

Thank you!

TBSupport
0
In Crystal report 2011 I have next tables: Invoice, customers and table suppliers
I am grouping on Customers and Suppliers.  Let say there is 5 suppliers.
Some customers have all 5 suppliers some have two or three.
I want to show for each customer all five suppliers does not matter if they do not buying from them or not.
How to get this.
0
I have an issue with a Crystal Report that is showing too many records based on the records selected!

I have 2 tables, my main STK_Stock table, keyed on STK_StockCode and my STK_Location table which contains multiple records per STK_StockCode linked to Loc_StockCode.

Within the STK_Location table there is a LOC_Physical column which I want to sum on STK_StockCode.

I have tried (or I think I have) all variations of Groups and joins but I am unable to acheive my goal of producing a single line per STK_StockCode that contains the sum of the LOC_Physical from the STK_Location table.

I hope that makes and sense and someone can help!

Many thanks

Steve
0
Here is the formula to give me a readable date a patient was seen:

CDATE({Archrg99.D_ServiceFrom}-18261)

I need to know the formula that will look for the first time the patient was seen and the last time the patient was seen during a certain time period of 01/01/2012 to current.  I can configure the date range OK.

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