count based on charge level

[pblazeppd]

Hi all,

Need help,

Using CR11/w ODBC connection to Mysql database

Left outer join ARS_Master.Mas_Rel to Arrest.Mas_REL. Then left outer Join Arrest.Book_No to JailCHG.Book_No. Grouped by ARS_Master.Assignment. I need to be able to count as in the below data the group is DART. I need to do a hierarchical count based on the Chg_LVL on change of the Book_No. IE. On the first two rows I want to count the Felony but not the Other.

case# Book_No Chg_LVL
DART
100013151 DART 0000 1005005 Felony
100013151 DART 0000 1005005 Other
100013151 DART 0000 1005006 Felony
100013151 DART 0000 1005006 Other
100008297 DART 0002 1005204 Felony
100008297 DART 0002 1005206 Felony
100013825 DART 0000 1005251 City
100013836 DART 0000 1005256 City
100013914 DART 0000 1005287 City
100013914 DART 0000 1005287 Other
100013914 DART 0000 1005288 Other
100013810 DART 0003 1005314 Other

Any or all help would be appreciated.

 

[lbass]

So what do you expect the result to be for this sample data?

If you just want to do a count on change of book_no, insert a running total that does a count of a recurring field, evaluate on change of field: book_no, reset on change of group. Place the running total in the group footer.

-LB

 

[pblazeppd]

I was hoping to get something like a crosstab where their assignment is along the side then across the top group by JLChg.Arrest_Date for each year and then Chg_LVL.

2009 2010
Felony A/B Misd City Total Felony A/B Misd City Total
Auto 1 0 6 7 2 5 1 8
DART 5 2 2 9 2 2 6 10

ETC.

The problem I've been getting is in a crosstab if say there was 5 charges (1 felony, 2 A/B Misd and 2 City) on one Book_No it counts all five. I need it to count just the Felony then continue.

 

[lbass]

What are you using as your summary field and summary?

-LB