Solved

Adding a column to crystal reports 8.5

Posted on 2004-08-21
8
609 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
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…

730 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