count records based on parameter

[rrmcguire]

Hello,


I have a series of records, each with a field 'recno' for a series of users. The users are grouped by teams, West, Central, and East.

Im trying to count the number of recnos based on a parameter of team selection(West, Central, East) for a certain date range, field pickupdate(which the data is grouped by)

So, I have a group by pickup date for 3 months which I want to count the number of recnos based on which team is selected.

Any help would be appreciated.

thanks

 

[IanWaterman]

You can use a formula

// Team

If {teamfield} = {?teamparam} then 1 else 0

Then do a simple sum summary on this formula.

Ian

 

[rrmcguire]

which field are you meaning {teamfield}. What its supposed to do is I have a3 parameter fields which is = a formula, teamselection

Team selection looks like this:

if {SHIPMENTS_TTX.ENTEREDBY} = 'STH' or
{SHIPMENTS_TTX.ENTEREDBY} = 'EMG' or
{SHIPMENTS_TTX.ENTEREDBY} = 'ROX' or
{SHIPMENTS_TTX.ENTEREDBY} = 'SSR' then 'West CSR'

else if {SHIPMENTS_TTX.ENTEREDBY} = 'SAH' or
{SHIPMENTS_TTX.ENTEREDBY} = 'KAG' or
{SHIPMENTS_TTX.ENTEREDBY} = 'CRS' or
{SHIPMENTS_TTX.ENTEREDBY} = 'SSA' or
{SHIPMENTS_TTX.ENTEREDBY} = 'TEG' then 'East CSR'


else if {SHIPMENTS_TTX.ENTEREDBY} = 'CEJ' or
{SHIPMENTS_TTX.ENTEREDBY} = 'MAL' or
{SHIPMENTS_TTX.ENTEREDBY} = 'JMM' then 'Single Source'


else if {SHIPMENTS_TTX.ENTEREDBY} = 'JJN' or
{SHIPMENTS_TTX.ENTEREDBY} = 'RAH' or
{SHIPMENTS_TTX.ENTEREDBY} = 'LKD' then 'Central CSR'

for each group West CSR, East CSR, Single Source, and Central CSR it should total the number of recnos for each user based on the team selected and give a team total.

Then display this total for each month. I already have a group, which is grouped by pickupdate for each month

More explanation, ex. user JJN enters a "RECNO" what Im wanting is a count of those recnos but for every member of that group in this case being Central CSR.

thanks

 

[IanWaterman]

Create 4 formula

//@WestCSR

If {@teamselectionformula} = 'West CSR' then 1 else 0

repeat for each team and then add a sum summary for each of this formula in the month group footer.

Ian

 

[rrmcguire]

the problem with this is for instance if {@teamselectionformula} = 'West CSR' which would mean they chose the parameter for west CSR it would only show 1 for each month, where I want the total for each user in the West CSR group totalled together

 

[IanWaterman]

You keep mentioning parameter but I can not see where this is being used.

I only gave one example of a formula you will need to create one for each team, summing them will only return 1 if there was only one recno in a month for that team.

Ian

 

[rrmcguire]

When the report is run there is a Pm-Team Selection which the user then selects the team they want to view, West, East, or Central then in my select expert I have

({?Pm-Team Selection} = 'East' and {@team selection} = 'East CSR') or
({?Pm-Team Selection} = 'West' and {@team selection} = 'West CSR') or
({?Pm-Team Selection} = 'Single Source' and {@team selection} = 'Single Source')or
({?Pm-Team Selection} = 'Central' and {@team selection} = 'Central CSR')

where @team selection is equal to:

if {SHIPMENTS_TTX.ENTEREDBY} = 'STH' or
{SHIPMENTS_TTX.ENTEREDBY} = 'EMG' or
{SHIPMENTS_TTX.ENTEREDBY} = 'ROX' or
{SHIPMENTS_TTX.ENTEREDBY} = 'SSR' then 'West CSR'

else if {SHIPMENTS_TTX.ENTEREDBY} = 'SAH' or
{SHIPMENTS_TTX.ENTEREDBY} = 'KAG' or
{SHIPMENTS_TTX.ENTEREDBY} = 'CRS' or
{SHIPMENTS_TTX.ENTEREDBY} = 'SSA' or
{SHIPMENTS_TTX.ENTEREDBY} = 'TEG' then 'East CSR'


else if {SHIPMENTS_TTX.ENTEREDBY} = 'CEJ' or
{SHIPMENTS_TTX.ENTEREDBY} = 'MAL' or
{SHIPMENTS_TTX.ENTEREDBY} = 'JMM' then 'Single Source'


else if {SHIPMENTS_TTX.ENTEREDBY} = 'JJN' or
{SHIPMENTS_TTX.ENTEREDBY} = 'RAH' or
{SHIPMENTS_TTX.ENTEREDBY} = 'LKD' then 'Central CSR'



So when the user runs the report it should grab the data for the users in that specified team.

 

[IanWaterman]

But if you have filtered the data by a specific Team then the count of recno will be for that team.

I am not not really sure what you want to see, can you show some sample data and indicate wht total you want to see and where it should appear.

Ian

 

[rrmcguire]

ok, for example, for the parameter field, the user picks 'Central CSR', Central CSR consists of users, JJN, LKD, and RAH

each of these users have records which they enter in a shipments table. Their initials, JJN, as an example are recorded in the field 'enteredby'. What Im trying to do is get a count of the records for those users in the group selected, then have a total for the group based on the parameter selected and then Im assuming it would total per month based on my grouping by the field 'pickupdate' every month where 'pickupdate' is another field on a record the user enters.

 

[rrmcguire]

ok, so I got them to total for all months combined, and have placed the final formula in my grouping by field pickupdate by month:

have field:

@loadcount = if {shipments.status} <> 'X' then 1 else 0,

then

@dispatch load count = sum(@loadcount),

I then put this field in my group and it gives me a total for all months combined for the users in the parameter group selected, not broken down by month

 

[lbass]

If you want it broken down by month, you have to add the group condition to the summary field:

sum({@loadcount},{table.pickupdate},"Monthly")

-LB