Compare 2 records in the same group

Hi all!
I have a staff Turnover report with table that has a group for
"Status". The value of this group is either Active or Terminated, so
here's a very simple description of how the table looks when
previewed:
_________________________________
TURNOVER REPORT
Active (5 associates)
Luke Skywalker
Leah Organa
Han Lolo
Chewbacca
Yoda
Terminated (2 associates)
Obi Wan Kenobi
Anakin Skywalker
_________________________________
As you can see I have a "Count" function that gets the number of
associates with each status (5 active, 2 terminated). I need a
function to divide those two values (2/5) to get the turnover percent,
but try as I may I cannot seem to get it to perform a calculation on
members of the same group. I have tried various methods to get around
this with no success. Does anyone have any suggestions?
I'd really like this to be calculated from within the report instead
of adding another dataset to return these numbers (i already have 5
datasets - most are used to populate data for the report parameters).
I appreciate your help!
MichellePut the values for count (2 and 5) in separate textboxes. For example lets
name these textboxes as tbCountActive and tbCountTerm.
Now in another textbox use following expression to display percent:
= ReportItems!tbCountTerm.Value / ReportItems!tbCountActive.Value
Cheers
AJ
"Michelle" <Michelle@.bwalk.com> wrote in message
news:92c953bf.0501251305.2a45486f@.posting.google.com...
> Hi all!
> I have a staff Turnover report with table that has a group for
> "Status". The value of this group is either Active or Terminated, so
> here's a very simple description of how the table looks when
> previewed:
> _________________________________
> TURNOVER REPORT
> Active (5 associates)
> Luke Skywalker
> Leah Organa
> Han Lolo
> Chewbacca
> Yoda
> Terminated (2 associates)
> Obi Wan Kenobi
> Anakin Skywalker
> _________________________________
> As you can see I have a "Count" function that gets the number of
> associates with each status (5 active, 2 terminated). I need a
> function to divide those two values (2/5) to get the turnover percent,
> but try as I may I cannot seem to get it to perform a calculation on
> members of the same group. I have tried various methods to get around
> this with no success. Does anyone have any suggestions?
> I'd really like this to be calculated from within the report instead
> of adding another dataset to return these numbers (i already have 5
> datasets - most are used to populate data for the report parameters).
>
> I appreciate your help!
> Michelle|||Hi AJ,
How to put them in seperate textboxes? The count is a single field in
a group - and it needs to remian in the group in order to get the
correct numbers.
Thanks!
Michelle|||Try
=Sum( iif(Fields!Status.Value = "Terminated", 1, 0), "DataSet1") / Sum(
iif(Fields!Status.Value = "Active", 1, 0), "DataSet1")
Where DataSet1 is the dataset bound to the table.
"Michelle@.bwalk.com" wrote:
> Hi AJ,
> How to put them in seperate textboxes? The count is a single field in
> a group - and it needs to remian in the group in order to get the
> correct numbers.
> Thanks!
> Michelle
>|||Hi Michelle,
Actually I assuming that you have combined the text and value in one textbox
like,
="Active (" & count(whatever) & "associates)"
Instead place the value of count in a single textbox like,
= count(whatever)
and then just for convenience sake give some meningful name to this textbox,
i.e. tbCountActive...
Then do the calculation based on the value contained in the textbox. You
can refer to a specific textbox by using ReportItems!textboxname.
I haven't seen this feature in Crystal Reports and this is something cool
with Reporting Services.
Hope this helps...
AJ
<Michelle@.bwalk.com> wrote in message
news:1106692227.587559.227350@.c13g2000cwb.googlegroups.com...
> Hi AJ,
> How to put them in seperate textboxes? The count is a single field in
> a group - and it needs to remian in the group in order to get the
> correct numbers.
> Thanks!
> Michelle
>|||This works beautifully - thank you!|||This may or may not work depending on where the output is desired due to the
scope limitations.
"AJ" wrote:
> Hi Michelle,
> Actually I assuming that you have combined the text and value in one textbox
> like,
> ="Active (" & count(whatever) & "associates)"
> Instead place the value of count in a single textbox like,
> = count(whatever)
> and then just for convenience sake give some meningful name to this textbox,
> i.e. tbCountActive...
> Then do the calculation based on the value contained in the textbox. You
> can refer to a specific textbox by using ReportItems!textboxname.
> I haven't seen this feature in Crystal Reports and this is something cool
> with Reporting Services.
> Hope this helps...
> AJ
>
> <Michelle@.bwalk.com> wrote in message
> news:1106692227.587559.227350@.c13g2000cwb.googlegroups.com...
> > Hi AJ,
> >
> > How to put them in seperate textboxes? The count is a single field in
> > a group - and it needs to remian in the group in order to get the
> > correct numbers.
> >
> > Thanks!
> > Michelle
> >
>
>