Print Page | Close Window

Selection within a Formula

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=3191
Printed Date: 05 May 2024 at 12:45am


Topic: Selection within a Formula
Posted By: bwagner
Subject: Selection within a Formula
Date Posted: 15 May 2008 at 2:05pm

I'm working on some reporting for a call center and I'm trying to write a report for the logged in time of our agents.  Problem is, they are on anywhere from 2-7 skillsets, and sometimes they change.  Our SQL server records time logged in for each skillset and adds it together, so if they are on 3 skillsets, and logged in for 1 hour, it returns logged in time as 3 hours.

I had tried doing a distinctCount, but if people change skillsets during the period I'm running it all gets thrown in a loop.  So, there is one skillset that everyone has as a base skillset, I'm trying to figure out how to best select that skillset, but within the formula for logged in time only.  Here is what I have so far.

NumberVar loggedon1;
Select {t_Skill_Group.SkillTargetID} = 5000;
loggedon1:=Sum ({t_Agent_Skill_Group_Half_Hour.LoggedOnTimeToHalf},{t_Agent.PersonID});
 
5000, is the default skillset.  Is there a different way I should be selecting the target ID?  I hope this makes sense to someone.


Replies:
Posted By: Lugh
Date Posted: 16 May 2008 at 5:26am
Yes.  It is a pretty classic problem within Crystal.  There are a couple possible solutions.

One is to group your report based on skill set.  You can then run summaries on just the current group.  This is the simplest solution, but frequently doesn't meet other demands of the report.

Another is to do a running total.  You can do a running total to add the hours if the skill set is 5000, and add 0 otherwise.  This has the downside that it is tricky at best to reference a running total with any other formulas.  So, it pretty much is only useful if you just want to display the total in the footer.

Another option is to use a formula to zero out any values you don't want to see.  So, put a formula in the details section that looks like:

WhileReadingRecords

IF {t_Skill_Group.SkillTargetID} = 5000 THEN
    {t_Agent_Skill_Group_Half_Hour.LoggedOnTimeToHalf}
ELSE
    0

You can then do your SUM on this formula, and the zero values are effectively ignored.  Note that this does not affect COUNT, and skews AVERAGE.

The most complex and flexible solution is to use a global variable.  Create a formula that looks like:

WhileReadingRecords

Global NumberVar loggedon1;

IF {t_Skill_Group.SkillTargetID} = 5000 THEN
    loggedon1 := loggedon1 + {t_Agent_Skill_Group_Half_Hour.LoggedOnTimeToHalf}

Then, you can just write a formula to return the final value of loggedon1 in the report footer.  This is typically used for more complex scenarios than yours.  Also, note that, as written, this formula does not distinguish between Agents.  I'll leave that as an exercise for the reader.  Wink


Print Page | Close Window