Crystal Reports

34K

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 have a report parameter that accepts multiple, user entered items.  User would like to see a list of any entered parameter values that do NOT get a record from the database table.  This is a char field in the database.

It's a quick query, so I could use a subreport. The report outputs as a crosstab with store #'s for rows and items for column and is exported to Excel.  The list of not found items could go in report footer or header or somewhere......
0
Free Tool: Subnet Calculator
LVL 12
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

OK this is a tricky one (at least for me!).

I have data from table1, table2, and table3 and each table has a date_modified field which I am using in my select statement = date range parameter.

The data contains customers and I want to perform a count of customers in a date range which looks at all three tables (which I have working now using 2 subreports which each use a date range parameter on the respective date_modified date fields). The part I need help on is, each customer has a lead_source field, and I want to count how many customers came from each lead_source. I currently have the total customers from all three tables in a date range, but do not have them broken down by lead_source and then combined from all three tables to show the total from each lead_source from all three tables.

I have the main report and 2 subreports grouped by lead_source and can get the those sub-totals on each report, but don't know how to combine those lead_source subtotals together for a final count which includes all three tables.

Here are the formulas I'm using to get the grand totals:

Main Report Formula:

    WhilePrintingRecords;
    Shared numbervar total_leads_main_report:= Count ({table1.id})

First SubReport Formula:

     WhilePrintingRecords;
     Shared numbervar archive_sub_total_leads:= Count ({table2.id})

Second SubReport Formula:

    WhilePrintingRecords;
    Shared numbervar total_leads_main_report +
    Shared numbervar …
0
I have a subreport to display data supplied by a SQL view. I need to get fields to display on the same line, so I created the following formula and inserted it into a detail section of the subreport:

stringvar PhoneStr;
stringvar TollFreeStr;
stringvar BothStr;

if {uvw_InvoiceListingDetails.LineDescription} = "Phone" then
    PhoneStr := "Phone: " + {uvw_InvoiceListingDetails.LineData} + "   ";
if {uvw_InvoiceListingDetails.LineDescription} = "Toll Free" then
    TollFreeStr := "Toll Free: " + {uvw_InvoiceListingDetails.LineData};

BothStr := PhoneStr + TollFreeStr;
totext(BothStr);

Open in new window


In the section expert, I use the following suppression formula so that there is no conflict with the other listing details in the subreport:
if {uvw_InvoiceListingDetails.LineDescription} = "Phone" then
    false
else if {uvw_InvoiceListingDetails.LineDescription} = "Toll Free" then
    false
else
    true

Open in new window


What's happening is that if the toll-free number is present in the dataset, the phone number is printed twice (once on it's own line, then again on the next line with the toll-free number).

And if the toll-free number is not present in the dataset, the phone number is printed only once, which is expected and desired.

What am I doing wrong here? I've been battling this for 1.5 days and I've tried pretty much everything I can think of. Any help would be greatly appreciated.
0
Hi

I am converting seconds to hh:mm:ss from seconds (CR2016)

I usually use the formula below which works fine for anything under 24 hours

CStr(CTime(currentfieldvalue/ 86400), "hh:mm:ss")

I have a few fields that go above 24 hours, so for 25:00:06 - this reads 01:00:06.

I have tried

 CStr(CTime(currentfieldvalue/ 3600), "hh:mm:ss") - doesnt work

Any ideas on how this can be used for anything over 24 hours

Thanks
0
Hello,

I need to convert a decimal time to hh:mm (that format) from a sql database field in a Production Utilisation Report. The report is written in Crystal.

I have already the following but I'm struggling with the minutes as the code below seems to be adding to many hours calculated from the decimal value after the zero

3.75  = 4.15 after conversion. The structure of the time isn't correct as yet (hh:mm) but I'm first want the value to be right.

See code below:

//(NumberVar OldTime := {rep_v_ProductionAnalysis.OperationLength};) = actual field. Replaced the field so I can check the calculation


NumberVar OldTime := 3.75;
NumberVar NewTime;
NumberVar Hours;
NumberVar Minutes;
Hours := Int(OldTime);
Minutes := Remainder(OldTime, 1) * 100;
Hours := Hours + Int(Minutes / 60);
Minutes := Remainder(Minutes, 60);
NewTime := Hours + (Minutes / 100);

Any help would be useful

Thanks,
Gerd
0
I know I can change font size of a field with a formula in the format text area based on length of string, but is there a way to change font size based on how many lines/carriage returns the memo field contains?

For example:
If memo field contains 3 carriage returns, then font size 10
If memo field contains 4 carriage returns, then font size 9
If memo field contains 4 carriage returns, then font size 8
etc.

I have this field in a report, but I have limited vertical space to fit it and the field can vary from 2 lines up to 8 lines, so I need to shrink the text so it always fits in the same space of the textbox that the field is nested in.
0
I am trying to parse a Full Name field into First, Last, & Middle Initial in Crystal Reports.  I am able to get the Last name, but am having issues getting the first & middle initial.  The issue lies in the data. (And my lack of knowledge).  The data looks like:

Smith; John
Smith; John D.
Smith; John D
Smith; John David

I was able to parse the last name, but I am unsure how to get the first & middle separated.  I can get them both, but I can't figure out how to get them separated when the data is inconsistent.  
Any help would be greatly appreciated!  Thanks!
0
I have a report where it shows all the clients that I sold something.

Is there anyway to show all the clients just once so I can make a grand total of each one?

Ex:

CLIENTS     DOCUMENT     VALUE
Client A      1                         10000
Client A      2                         30000
Client A      3                         40000
Client B      1                         500
Clinte B      2                         2000

Grand Total Per Client
CLIENT      N° OF DOCS      TOTAL
Client A     3                          80000
Client B     2                          2500
0
Need assistance again with ( ) Parentheses.


WHERE            
AP.SERV_DATE BETWEEN '01/01/2017' AND '01/31/2017'
AND ap.status_c = '4'  
AND ((PX1.POS_TYPE_C IN ('11','15','17','49','50','71','72')  

AND eap1.proc_code IN ('90901','90911','92506','92507','92521','92522','92523','92524','92526','92601','92602','92603','92604','92605','92606','92607','92609','92610','92611','92612','92614','92616',' 95831','95832','95833','95834','95851','95852','96105','97001','97002','97003','97004','97005','97006','97010','97012','97014','97016','97018','97022','97024','97026','97028','97032','97033','97034',' 97035','97036','97039','97110','97112','97113','97116','97124','97139','97140','97161','97162','97163','97164','97165','97166','97167','97168','97530','97532','97533','97535','97537','97542','97545','97750','97755','97760','97761','97762','97799','0019T','0101T','0102T'))

I am getting the needed Proc_codes but POS_TYPE_C i am  picking up 11, 12, 27, so picking up stuff not in my criteria.  I am also picking up some 2016 dates in my date range.

Basically want to pull back any PRoc_codes with a POS_TYPE_C of 11, 15,17,49,50,71 and 72 in the date range listed
0
Hi All, today I encountered an error on my crystal report saying "database logon failed". This report has been using for all the staff for a couple of months already and no errors were encountered. For today when I tried to access the report I got this "Database Logon Failed". Please help why I'm getting this kind of error.
0
[Webinar] Kill tickets & tabs using PowerShell
LVL 12
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

What is the best way to configure my database connections to maximize the speed when refreshing the report. currently, to return 15 records it is taking 15 seconds. I expect this should take much less time like many other reports I have.

I am connecting to 3 dbf tables using the odbc (rdo) connection. see attachment showing the database expert configs in crystal reports 2013.

How else can I organize my connections to make the report run faster?DB-expert-configs.docx
0
Unable to successfully locate step by step instructions to create a publication or Burst Report in Business Objects Launch pad 4.1
Have tried numerous times (using the previous set up on 3.1 which is successful as a guide).  The Burst report in 4.1 continues to fail.
I'm not able to understand the log file completely in order to troubleshoot to resolution.  The crystal report runs successfully, the parameters are correct.  

I would be grateful to receive assistance in interpreting the error log file.  Step by step instructions to set up a Publication Burst report from scratch would be helpful too please. I am not the system administrator but working closely with the System Administrator to successfully set this up.  When the System administrator executes the burst report, they receive a failed run also.  The burst report is extremely important and without it, I would not know how to make it happen.  Thank you so much in advance.  I have been working with the System Administrator for months and I'm hoping to be steered in the right direction.  I have been able to create a Burst report from scratch using a working version in 3.1.  The System Administrator was able to create a server group where we thought that was needed and after referencing the server group, we still have failures.  Any and all assistance is appreciated.  Thank you.

 Publication-Admin-Log-1---618106.logRecent-run_Expert.docx
0
In Crystal Reports I'm wanting to make a report which pulls different information from are database, I needs to use two dynamic parameters, but I'm having trouble. For example, I'm wanting the fist parameter ask for a Job number, then when the user enters the Job number they are shown a selection of Sub jobs linked to that job number which they then choose the correct one and the information is placed on the report.

Is this possible and how would I do it.

Thanks
0
Crystal report custom links?

Hello,
I have a two sql tables.

1: thickness (id, id, thickness_up_to)

id     thickness_up_to:
1       5
2      10
3      15
4      20

2: price (id, thickness, price)

id  thickness  price
1   3                 20
2   5                 25
3   10               50

Problem is that I cannot make a link directly to thickness_id. I must create multiple criteria
if real thickness from table 2 is lower than thickness from table 1 then use price for 3mm thickness (20)
it real  thickness from table 2 is somewhere between fields from table 1 then use price for that period.
if it real  thickness from table 2 is above all fields from table 1 then use price for maximum thickness.

I think you understand what I need?

I know how to do it in VB net but problem is that I must do it in crystal report report directly.
I am also thinking about views in sql server directly to create new view and use it in crystal report?
I would like to do it just for this custom report, not to change anything in the database.

While linking table there is no option between in crystal reports?

Is this possible?
Tnx
0
I have a field which has first and second names (OR the field may only contain the first name)
How can I create a formula which only show the first name
0
Need help with the correct placement of Parentheses.



AND eap1.proc_code IN ('98966','98967','98968','99441','99442','99443','99201','99202','99203','99204','99205','99211','99212','99213','99214','99215') AND EAP.POS_TYPE_C = '2' and px1.MODIFIERS like '%95%'

AND prc.prc_abbr LIKE ANY
(    
 'CDTAV%',
'LANGTAV%',
'MHTAV%',
'PHONE%',
'PNTAV%',
'TAV%',
'ESVV%',
'LANGVID%',
'OBVV%',
'VAV%'
)

OR eap1.proc_code IN ('98966','98967','98968','99441','99442','99443','99201','99202','99203','99204','99205','99211','99212','99213','99214','99215') AND EAP.POS_TYPE_C = '11' AND px1.MODIFIERS LIKE '%GT%'

I believe "prc.prc_abbr LIKE ANY" where I am using the wild cards could also apply to the OR portion of the code.
0
How would I format this so I can use wildcards in the criteria I am searching for. If want to be able to use % at the end

,CASE  prc.prc_abbr                                                
WHEN 'CDTAV%' THEN  prc.prc_name || ' ---> ' || prc.prc_abbr
WHEN 'LANGTAV%' THEN  prc.prc_name || ' ---> ' || prc.prc_abbr
WHEN 'MHTAV%' THEN  prc.prc_name || ' ---> ' || prc.prc_abbr
WHEN 'PHONE%' THEN  prc.prc_name || ' ---> ' || prc.prc_abbr
WHEN 'PNTAV%' THEN  prc.prc_name || ' ---> ' || prc.prc_abbr
WHEN 'TAV%' THEN  prc.prc_name || ' ---> ' || prc.prc_abbr
WHEN 'ESVV%' THEN  prc.prc_name || ' ---> ' || prc.prc_abbr
WHEN 'LANGVID%' THEN  prc.prc_name || ' ---> ' || prc.prc_abbr
WHEN 'OBVV%' THEN  prc.prc_name || ' ---> ' || prc.prc_abbr
WHEN 'VAV%' THEN  prc.prc_name || ' ---> ' || prc.prc_abbr
ELSE 'No Visit Type'
END AS "Enc_Name-Visit_Type"
0
I am trying to calculate the percentage of each security in a given portfolio. The calculation should be value of the security divided by portfolio total. For example, in account 1, first portfolio, the percentage for Select Financial Svcs should be 400/1500 = 0.267.

I tried using a following manual formula ({@Secur_Value} / {@Acct_Sum}), but it does not calculate the correct percentage.

The report is attached. Please help solve the problem.
Report
0
How to have a total for each year on a Crosstab with Year({Date.field}) and Month({Date.Field}) with 2 Years of Data?  example.. 2016 Totals and 2017 Totals.  Currently 2016 and 2017 is added together.
crystal.PNG
0
Free Tool: IP Lookup
LVL 12
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Getting error 'A String is required here'

The Parameter (?StandardComments) is setup as a string.. with multiple values

1.Line1 has been chosen
2.Line2 has been chosen
3.Line3 has been chosen
4.Line4 has been chosen

I have created a Formulas based on the parameter

if {?StandardComments}
startswith '1.' then cstr(mid({?StandardComments},3,100))
else if {?StandardComments}
startswith '2.' then cstr(mid({?StandardComments},3,100))
else if {?StandardComments}
startswith '3.' then cstr(mid({?StandardComments},3,100))
else if {?StandardComments}
startswith '4.' then cstr(mid({?StandardComments},3,100))

Why won't this work.. It will work without multiple values
0
Crystal Reports - I am trying to total a formula (GroupPayrollHours) in a group and as a grand total on a report.  Below is the formula I am using in the detail section of the report that needs to be totaled.

if Not OnFirstRecord
AND
{Attendance.Work_Date} = previous({Attendance.Work_Date})
and {Attendance.Regular_Minutes} = previous({Attendance.Regular_Minutes})
then 0
else {@Payroll Time}

Any help would be appreciated.

Thank you
0
I have a Crystal report that I need to copy a parameter field from one report to the other.  They are almost identical.  Not sure how to do this.  I will attach screenshots that will show what I mean.  I will label each screenshot with what is going on.  I'm not an expert on Crystal so bear with me.
Screnshot of the report launcher in our accounting systemScreenshot of parameter that worksScreenshot of the edit window of the parameterScreenshot of FIND IN FORMULAS for the parameterScreenshot of the parameter I copied for the report I want to use the parameter onScreenshot of windows that shows up when I click on FIND IN FORMULAS  (how do I add the formula?)
0
I haven't worked with Crystal Reports for several years.   I wanted to ask a general question regarding table joins within the query for a crystal report.   I have 5 tables, with identical fields that I query using a UNION ALL statement.  I group the data by the fields and summarize.   Pretty straight forward.   However, I was wondering if there was a way to identify (without separating in separate reports) the tables... basically grouping on this information and then drilling down to group on the field information.     If you need to see code examples, please let me know.
0
Crystal 2013, this one I'm just drawing blanks on today,

I have a field (customer) it is a number and I need to burst these reports but the software wants to strip out duplicates  because of my grouping.  So if i could add Groupnumber to the customer number that should allow me to burst based on this  generated number..that's my thought.  I tried {customer}+Groupnumber but get a string error so my thought was I've got the syntax wrong or just brain dead today .
0
Hi, my report is showing incomplete total.  I have a Budget assigned to a control account and I have PO assigned by Package:

The po VALUE shows correct account, and the budget has with two control account (see attached) as follows:

P31.9140.2C00 - 9,000
P31.9140.2C00 - 190,800

In the PO the budget used is only P31.9140.2C00 the other was not used.

On the attached form, I should show the total budget of 199,800  and it doesn't matter if I assign or not assign the budget in the PO.  But I can't show the total of 199,800 it only show the amount of the account code I assign, and even if I include the other code with the 9,000 budget it will not show the full budget amount.  I suspect I missing additional Link.  I can't use the sum ((Account.Cost_BAC Approved})  it will just show weird total $1,335,600.

The above data comes from table Account and Field Cost_BAC_Approved

Current Commitment and Total Commitment comes for PO_ITEM.Cost which is assigned by PO_ID coming from table PO_item.

Please let me know if I am missing some links or I need another formula or groupings.

and in relation to the above,

I am getting this result in a table report how do I correct this (see attached file "Table"):
Crystal-Capture.PNG
Table.PNG
0

Crystal Reports

34K

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.