Solved

Calculating Turnover ratios in CR 9.0 - How can I compare counts of two different groupings in one report?

Posted on 2004-08-11
20
505 Views
Last Modified: 2008-03-17
I am trying to pull statistics out of a SQL-based Work Order tracking system.  For each work order in the system, I track such things as Date Submitted, Requested Competion Date, Committed Completion Date, and Actual Completion Date, along with other header information and detail tables.  

I would like to report monthly turns as a ratio: i.e June 2004:   100 work orders submitted, 90 Work orders closed, hence a 90% ratio.

I am struggling with getting to this calculation.  I need to create 2 simultaneous groupings:  Date Submitted by month, and Actual Completion Date, by month.  I can only seem to get these hierarchically, but I want to calculate them in parallel.  Can this be done in CR, or do I need to build a query to create this data, then use the query as my source in CR?   An example would be quite helpful.  

A quick response would be appreciated.

Thanks,

Bill
0
Comment
Question by:bstarrs
  • 10
  • 9
20 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 11772608
Are you trying to get June submitted and closed/June submitted or June closed/June submitted?  The two are different.

ie A work order submitted May 30 and close June 5.  It obviously is in the May submitted but do you count it as a June closed?

If counted as a June closed then the ratio isn't a true ratio of june opened vs June closed.

If you want a Closed in Month regardless of when opened vs closed in the month I suspect you can use a cross-tab report with the columns being the months and the rows being number opened, number closed, then the ratio.

mlmcc
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11772637
Bill,

As a general approach, I would probably create a subreport that counts the number of orders submitted and closed for a given month (or given date range) and of course the ratio between them.  You can then put this subreport in the group header / group footer and pass the group fields into the subreport to define the date ranges.

If you can expand on what you want the final report to look like then I can try to be more specific.

frodoman
0
 

Author Comment

by:bstarrs
ID: 11773243
I want the numbers to be independent of each other.  If I were to run this report for only a one month period, my selection formulas would be:

(Assigned Group): DateAssigned in June 1, 2004 to June 30, 2004
(Closed Group): DateClosed in June 1, 2004 to June 30, 2004.

These work orders can take anywhere from one day to one month to complete, so I only want to know how many new orders were submitted in a given month, and how many work orders were closed in a given month, regardless of how old they may be.

I want the report to work like this:
  * Parameters in the main report for the Start and end of the Period I am interested in (1/1/04 - 6/30/04) to be passed to the subreports for record selection
  * Details listing the Count of Assigned Work Orders, Count of Closed Work Orders, and the ratio for each month in the above period
  * Monthly bar graphs of the counts and ratios.

                     Assigned Work Orders    Closed Work Orders      Turnover Ratio
Jan 2004
                     80                                75                                 0.94
Feb 2004
                     90                                92                                 1.02

etc.

Thanks,

Bill
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11773589
One approach would be to create two subreports.  The first one selects all records that are assigned during the date range and determines (and displays) the count.  You design the subreport to be narrow and position it on the left side of the page.  The key step is that the subreport would set the value of a shared variable in the main report.  

The second subreport would do the same thing for the closed records - positioned in the middle of the page.  Your main report could then contain a formula that divides the two shared variables to display the turnover ratio.

Another approach since you're using CR9 would be to simply create a subreport that uses a SQL Command as the datasource and just write the SQL to return the three values in question.

frodoman
0
 

Author Comment

by:bstarrs
ID: 11773926
I have created two subreports.  I am having to enter their selection criteria independently.  I would like to create parameter fields in the main report (StartDate and EndDate) and then feed them to the subreports, but am having trouble doing so.

I also cannot figure out how to access the subreport summary fields in a main report formula.  Very close, but not there yet.

Bill
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11774000
>>> I would like to create parameter fields in the main report (StartDate and EndDate) and then feed them to the subreports

Rt-click on the subreport and choose "Change Subreport Links".  In the top portion of the window select the parameter field from the main report.  In the bottom dropdown select the parameter field from the subreport.  This will link the values.  Repeat for the other subreport.  Now the main report will prompt you for the values and they'll be passed to each of the subreports.

Of course the parameter fields must all be the same datatype.

Let me know if anything else isn't working smoothly.

frodoman
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11774093
>>> I also cannot figure out how to access the subreport summary fields in a main report formula

In your main report, report header, create this formula:

shared numberVar OpenCount := 0;
shared numberVar ClosedCount := 0;


Now in the report footer of your first subreport create this formula:

shared numberVar OpenCount := Count({table.field});

(Similar for second subreport using ClosedCount variable)


Now back in the main report, at the appropriate position create this formula:

shared numberVar OpenCount / shared numberVar ClosedCount;


HTH

frodoman
0
 

Author Comment

by:bstarrs
ID: 11774438
Got the date parameters to link.  I had not created parameters in the subreport to link to.

Not sure how to create formulas directly in the header.  I can make formula fields, but have not coded directly into a report section.  I follow what you are doing but can't execute.

Bill
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11774471
Sorry, I should have been clearer.  What I should have said is create the formula normally and then drag it into the report header.  It's nothing special, just a matter of putting the formula at the beginning of the report so the variable is created before any subreports are called.

frodoman
0
 

Author Comment

by:bstarrs
ID: 11775665
I placed the formula in the main header, and the count formulas in the subreports footer.  All works well from there and I get an accurate count.  I tried putting the ratio formula in the report footer, but am getting a "division by zero" error.  When it throws out the formula editor it appears that it is calculating the OpenCount and ClosedCount variables to be zero again.

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 42

Expert Comment

by:frodoman
ID: 11775771
You should place the formula in the same section as your subreports.  Whichever section they're in it should look like this:

         Subreport1(open)   Subreport2(closed)    Formula(ratio)

That way the formula is being calculated right after the 2nd subreport.  Alternately you could insert a section and put it right below the subreports.

frodoman
0
 

Author Comment

by:bstarrs
ID: 11776066
This still isn't quite what I had hoped for.  I now have the 2 subreports in the report header, because if they are in the details they get repeated once for each work order.  The ratio is only calculated on the grand total for whatever period I choose, and not monthly.  
The report now looks like this:

Month   Assigned   Submitted
Jan
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11776103
I assume that your main report is grouped by month, is that correct?  If so try putting the subreports and formula into the GROUP FOOTER so that it will appear after each month.  At this point it'll still show you the total ratio for the priod choosen but if it looks like we're on the right track then we can fix the subreport to show the correct ratio each month...
0
 

Author Comment

by:bstarrs
ID: 11776122
OOops....
<Report Header a>
Month  Assigned   Submitted
Jan      61            49
Feb     65            59
Mar     62            60
Apr     41            39
Tot     229          207
<Report Header b>
Ratio: 0.93
no Details


I had wanted to somehow get:

<Group Header>
Month
<Details>
  Assigned    Submitted     Ratio
<Group Footer>
  Count        Count           Group Ratio
<Report Footer>
  Count        Count           Grand Ratio

Unless the main report details have the individual counts and ratios, I don't see how I can chart the data.


0
 

Author Comment

by:bstarrs
ID: 11776142
I don't have any main report, per se.  To group my main report by month, I have to choose a field, either date assigned or date complete, correct?  I don't want to choose either.  
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11776182
>>> To group my main report by month, I have to choose a field, either date assigned or date complete, correct?  I don't want to choose either.

I don't see how you can get the report you want w/out doing that...  but I do understand what you're saying.  Let me think on this one for a few minutes...

0
 
LVL 42

Accepted Solution

by:
frodoman earned 500 total points
ID: 11776368
I was working on the assumption that any given record would fall into either January or February, etc.  However the model you want really specifies that a single record can fit into BOTH months - January for open date and February for close date, etc.  

This presents a problem in that Crystal absolutely will not process the same record more than one time.  That won't work which leaves subreports as the option.  The problem there is you can't get the ratio of numbers between subreports except by using shared variables in the main report and then that only works at the grand total level.  Catch-22.

******************************************
*** I would start over and take a different approach. ***
******************************************

New approach:  Create a new report and create a SQL Command as your datasource.  Write the SQL to return 2 records to Crystal for each record in your table.  Something like this should do it:

select AssignedDate as TransDate, 1 as NumAssigned, 0 as NumClosed from table where...
union all
select ClosedDate as TransDate, 0 as NumAssigned, 1 as NumClosed from table where...

Now when you dump this onto your report if the first record was opened in Jan and closed in Feb you'd see something like:

01/15/2004   1    0
02/02/2004   0    1

If next record was opened and closed in Jan you'd see this:

01/15/2004   1    0
01/22/2004   0    1

etc.

Now it's simply - just add a group on the {TransDate} field, with summarize option set to monthly which would give you (for above two cases) something like this:

Jan
01/15/2004   1    0
01/15/2004   1    0
01/22/2004   0    1

Feb
02/02/2004   0    1


Now just insert a sum on each of the columns gives you your monthly subtotals.  Create a simply formula: {NumAssigned} / {NumClosed} and place it in your group footer.

Finally, suppress the details section and you should have (if I understood correctly) exactly what you want.

HTH

frodoman
0
 

Author Comment

by:bstarrs
ID: 11776952
Wonderful suggestion.  All looks good except for the ratio formula.  If I create the formula as NumAssigned / NumClosed and place in group footer, I get a div/0 error.  If I create the formula as Sum(NumAssigned) / Sum (NumClosed) I get only the grand total percentage, both in the group footer and report footer.  How can I calcluate the ratio for each group?
0
 

Author Comment

by:bstarrs
ID: 11776975
Never mind.  Figured it out.  Dragged group sums from the field chooser into the formula.

Thank you very much for your assistance on this one.  I learned a ton.
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11777775
Glad I could help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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 …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

911 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

23 Experts available now in Live!

Get 1:1 Help Now