Custom group based on an employees assignment dates

[PDAnalyst]

Hi,

I am using CR 2013 connecting to MS SQL 2008R2 database.

I am running yearly reports for cases assigned to our investigations.

I have created groups based on the assignment type and number of different officers are in these assignments.

Problem that I am facing is that an officer can be moved from one assignment to another one during the year. I need to create a custom group or a specific date selection criteria inside the group to partially assign an officer to different groups.

As an example:

Ofc. Joe - assigned for the whole year to Robbery (14 cases)
Ofc. Adam - assigned for the whole year to Fraud (25 cases)
Ofc. White - from january to june, assigned to Robbery (4 cases) then rest of the year to Fraud (10 cases)


What I would like to see is:

Robbery assignemt
Ofc. Joe (14 cases)
Ofc. White (4 cases)

Total: 18 cases

Fraud assignment
Ofc. Adam (25 cases)
Ofc. White (10 cases)

Total: 35 cases


Any idea is appreciated,

Thanks

 

[PeterDimitrov]

What is your data structure ? Do you have an existing SQL , which you would like to modify ? What is the total number of officers and groups ?

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

 

[PDAnalyst]

Currently, there are groups for each of the assignment types and under these groups, there is a group for each investigator. The details for each case is listed under this. There is no SQL grouping done at the server. All of the grouping is done at the workstation.

There are 10 assignment groups and I have over 25 investigators. Some of the assignment groups have only one or two investigatoirs and some has 5-6.

This is how the current report looks like as an example:

Robbery assignemt
Ofc. Joe
14-0001
14-0215
14-0222
etc

Ofc. White
14-1112
14-1235
14-2563
14-6665 (this is actually a Fraud Case)
14-5564 (this is actually a Fraud Case)
14-2345 (this is actually a Fraud Case)
etc.


Fraud assignment
Ofc. Adam
14-5645
14-5987
14-2111
14-8945
etc

Ofc. White
14-1112 (this is actually a Robbery)
14-1235 (this is actually a Robbery)
14-2563 (this is actually a Robbery)
14-6665
14-5564
14-2345
etc.


as you can see, ofc. White has to be listed in both of the sections currently because half of the year he was at the Fraud and half he was at the Robbery.

I need to find a way so that his fraud cases only show up in fraud assignment group and his robbery cases show in the Robbery assigment, I have the specific dates that he was assigned to each section so I need to find a way to seperate his time in between these two groups.

Thanks

 

[PeterDimitrov]

If you have separate fields for assignment group and officer in your datasource the grouping should work properly. Do you use a formula for the assignment group or it is a field ?

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

 

[PDAnalyst]

I place each investigator to the assigment groups based on their employee number which is field in the database.

 

[PeterDimitrov]

Do you have links between case (14-0001, 14-0215 etc) and assignment group and case and investigator ? If you do you need to use these links instead of the link between assignment group and investigators.

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

 

[PDAnalyst]

Yes,

I have link between the case info and the assignment group (detail) as wel as the investigators. The problem is that the assignment group (detail) in the database is not reliable due to the fact that the secretaries that are supposed to update each of the investigators assignment changes do not due them in a timely fashion so when the detectives assignment has been changed from Fraud to robbery etc. and if the update is not done in a timely manner, the new case will show up in the wrong assignment (details) queue which causes the error in relying on that detail table.

(see the attachment)

 

[PeterDimitrov]

As far as I understand your model you have links:

case -> assignment group
case -> investigator
investigator -> assignment group

if "case -> assignment" group is not reliable do you have dates when the case was assigned to an investigator and when the investigator was moved from one assignment group to another ?

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

 

[PDAnalyst]

Yes, I have the dates when the case is assigned to the investigator but the dates when an investigator is moved from one assignment group to another is in an access database since the one on the SQL database is not correct

 

[PeterDimitrov]

This looks more like a database question. You can link the access database as a linked server in SQLServer and use it to create a view , which later will be used in the report. If you do not have permissions to do this you can go the other way adding the tables from SQL Server as linked tables in the Access database. The first goal is to have all the data in one database. The next goal to create a view, which provides assignment group and investigator for each case.
It is hard to give you an advice not knowing the database structure

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

 

[PDAnalyst]

Ok,

Thanks for the advice,

will try it that way and see if it works