Solved

Adding a column to crystal reports 8.5

Posted on 2004-08-21
8
594 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
Comment Utility
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
Comment Utility
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
Comment Utility
Welcome back DrRyan.

mlmcc
0
 
LVL 12

Expert Comment

by:DRRYAN3
Comment Utility
Just visiting - but thanks
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 12

Author Comment

by:rgn2121
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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 this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now