x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 524

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

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
bstarrs
• 10
• 9
1 Solution

Commented:
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

Commented:
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 Commented:
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

Commented:
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 Commented:
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

Commented:
>>> 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

Commented:
>>> I also cannot figure out how to access the subreport summary fields in a main report 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 Commented:
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

Commented:
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 Commented:
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

Commented:
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 Commented:
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

Commented:
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 Commented:
OOops....
Month  Assigned   Submitted
Jan      61            49
Feb     65            59
Mar     62            60
Apr     41            39
Tot     229          207
Ratio: 0.93
no Details

I had wanted to somehow get:

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 Commented:
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

Commented:
>>> 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

Commented:
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 Commented:
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 Commented:
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

Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.