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
504 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

IT, Stop Being Called Into Every Meeting

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!

Join & Write a Comment

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 …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 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

22 Experts available now in Live!

Get 1:1 Help Now