Solved

Adding a column to crystal reports 8.5

Posted on 2004-08-21
8
604 Views
Last Modified: 2010-05-18
I have a report that I am working that generates a total number of cases that are specific to different areas of operation.  For instance:

                             Total                       over 10                    under 10         percent ?
accounting                 20                            2                              18

marketing                 34                             4                               30

The cases relate to technical issues.  Each issues is assigned a case describing it and when the issue is resolved the case is closed.  For accounting their were 20 tech issues, 2 took over 10 (to close) and 18 were under 10 days.

The percent column is what I don't have, but I need.  I want the percent of days over 10, or 2/20, but I don't know how to get this in one extra column.  Everything I do creates 2 more columns. I tried to right click on the report in the preview view, and select format cross-tab and add a column with the formula, but it adds 2 more columns.  Do I need to add that 4th column(percent) in with my other formulas or can they all be separate and just added as a column in the format cross tab section?

I am new to crystal reports so I would appreciate it if your answers could be as specific as possible.

Thanks,

rgn
0
Comment
Question by:rgn2121
8 Comments
 
LVL 42

Expert Comment

by:frodoman
ID: 11859232
You don't want to use a crosstab for this report - instead create a standard / normal report.  In your report expert you want to group by department.

I'm assuming the records in your database are something like this:

accounting     5 ( <- days )
accounting    11
marketing      4

Once you've got your report grouped by department, create a new formula named "Over10" with this code:

if {table.daysfield} > 10 then 1 else 0;

Create another formula named "Under10" with this code:

if {table.daysfield} <= 10 then 1 else 0;

Drag both formula to your report so it should look something like this now:

Account Group Header    Over10   Under10
   accounting     5                0            1
   accounting    11               1            0
Marketing Group Header
   marketing      4                0            1
   ...etc...

Now rt-click on the over10 field and select Insert -> Summary and select Group#1 for the location and SUM for the type.  Do the same for the under10 field.  This should insert subtotals so your report looks like this now:

Account Group Header    Over10   Under10
   accounting     5                0            1
   accounting    11               1            0
                                       ----          ----
                                         1             1
Marketing Group Header
   marketing      4                0            1
   ...etc...

rt-click the over10 field again and insert another summary only this time use COUNT instead of SUM.  Drag this field to where you want it positioned:

Account Group Header    Over10   Under10
   accounting     5                0            1
   accounting    11               1            0
                                       ----          ----
                                         1             1              2

Now create a formula to divide the sum of the over10 by the count of the over10:  Sum({@Over10},{table.department}) / Count({@Over10},{table.department}).  Put this formula in the footer to give your percentage.

Now select report -> section expert.  Select the detail section and click "Suppress" to hide the details leaving only the summary that you need.

HTH

frodoman
0
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 11859251
Select the design view tab
Insert -> Field Object
Right Click Formula Fields
Select New
Type a name (like CasePct)
Enter the following formula (I've used your column headers as field names, substitute the actual names)

if {MyTable.Total} <> 0 then
  {MyTable.Over 10} / {MyTable.Total}
else
  0;

Close the formula editor box, then drag your formula field to the designer like any other field.

0
 
LVL 100

Expert Comment

by:mlmcc
ID: 11860861
Welcome back DrRyan.

mlmcc
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 12

Expert Comment

by:DRRYAN3
ID: 11870923
Just visiting - but thanks
0
 
LVL 12

Author Comment

by:rgn2121
ID: 11900848
frodoman

In having my report return 1's and 0's, I lose the information that I want to display.  ASre are your one's and zero's just arbitrary numbers.  I want it to return like what I have listed at the top.

Where it gives me the number of cases that are under 10 and the number that are over 10.

If this is what your report does I am sorry, I just didn't see it.
As I stated eariler, I am new to crystal reports.
0
 
LVL 12

Author Comment

by:rgn2121
ID: 11900914
DRRYAN

If doing what you suggest, 'mytable' is the name of my table?

Is 'Total' the name of my formula that calculates total?

And what if my over 10 formula is an if then formula.

""     if ....>10 then "over 10"
else "under 10"   ""

should that be changed?  Because for my percent I only want the portion that is over 10, but with that formula about it outputs 2 columns to my report; an 'over 10' that shows the number of cases open over 10 days and an 'under 10' that shows the number open under 10 days.

I think if I could get that fixed I might be ok, but I can't see how to only pull the 'over 10' from that formula above in quotes.
0
 
LVL 12

Accepted Solution

by:
DRRYAN3 earned 125 total points
ID: 11903991
Since you didn't provide field names, I made a few assumptions:

The numeric value in your column labeled "Total" I assumed came from a field named "Total" in the "mytable" table.  Similarly, I assumed the numeric value in the column labeld "Over 10" came from a like named field in the same table.  You'll have to substitute your actual field names.  In the formula builder, you will be able to double click on the appropriate field names to have them inserted into your formula with the curly braces and table names.

Regarding your question about multiple results from the same formula - unfortunately, it doesn't work that way.  If you want to consider only those results over 10 and ignore those under 10, then you'll need multiple formulas.  Remember that you can create and reference formulas (and their results) that do not print on your report.
0
 
LVL 12

Author Comment

by:rgn2121
ID: 11951007
Thank you
0

Featured Post

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question