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

command scripts

I have created some stored procedures (SP) containing DBCC commands for
system maintenance and status checks. Does anybody know where to get sample
windows command scripts that will wrap all of my SPs, create logs, and also
where the windows scheduler could run at specific times. The idea is to
automate the maintenance instead of running individual SPs or DBCCs in Query
Analyzer.
Thanks...Schedule them using SQLServerAgent - Look up the service features in Books
online.
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:3AC72F6E-B0A3-4238-9021-DA964E4A8C06@.microsoft.com...
>I have created some stored procedures (SP) containing DBCC commands for
> system maintenance and status checks. Does anybody know where to get
> sample
> windows command scripts that will wrap all of my SPs, create logs, and
> also
> where the windows scheduler could run at specific times. The idea is to
> automate the maintenance instead of running individual SPs or DBCCs in
> Query
> Analyzer.
> Thanks...|||Create a job and use the SQL Server Agent for scheduling it.
Maybe you can check this link
http://www.sql-server-performance.c...erver_agent.asp
Hope this helps.|||Thanks..
"Omnibuzz" wrote:

> Create a job and use the SQL Server Agent for scheduling it.
> Maybe you can check this link
> http://www.sql-server-performance.c...erver_agent.asp
>
> Hope this helps.

Command delivery status in MSrepl_commands

I need to build some custom replication trouble shooter and I can't find
answers on couple of questions.
- How to determine status of command in MSrepl_commands? I mean status is
delivered this command to subscriber or not.
- How long delivered transactions reside in MSrepl_commands?
Have a look at the view msdistribution_status, which should be what you are
looking for. The code of the view is not encrypted and you could use it to
not do the group by and create your own version. If you want the actual
commands, then have a look at sp_browsereplcmds.
How long do commands reside in this table? If they have been read by all the
distribution agents involved and you don't have anonymous subscribers, the
commands will be removed by the cleanup agent. If a distribution agent isn't
synchronized or you have anonymous subscribers, they'll stay there until the
retention period is reached (72 hourd by default), and are then removed by
the cleanup agent.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)