Print Page | Close Window

crosstab multiple fields in one row

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7327
Printed Date: 29 Apr 2024 at 9:57pm


Topic: crosstab multiple fields in one row
Posted By: draven422
Subject: crosstab multiple fields in one row
Date Posted: 10 Aug 2009 at 12:00pm

My dilemma today is that I am trying to create a crosstab report that will show names down the crosstab row, and show multiple fields in one column. 

The data that I am grabbing has the possibility of a value in six different fields.  What it is, is that I am reporting on police officers and the citations they write.  Each citation can have up to six violations.  The crosstab will show the officer names, and the columns will have all the different violations that were written.  I can get the officers on the report, and only one of the six violation fields without a problem, but getting the other 5 violation fields is what is tripping me up.
 
In other words, an officer writes 10 citations in a week, but has 15 total violations.  How can I get the 15 violations listed in the crosstab in one row?
 
Thanks in advance.


Replies:
Posted By: DBlank
Date Posted: 10 Aug 2009 at 12:15pm
That's a lot of tickets...
I think you are looking for a SUM of six columns per row so you can display a SUM of that in your CT, correct?
Create a formula field and depending on the values in those fields add them up if it is an INT or use 6 if-then Statments to create a 1 or 0 and add those to get your total per row. Then use this Formula field in your CT.
Is that what you are looking to do?
 

Posted By: draven422
Date Posted: 10 Aug 2009 at 1:52pm
What I'm looking for is to show the violation description on the crosstab top row, such as "Speeding", "Seatbelt", "Stop Sign", etc. and then a count for each officer.  However, the descriptions would be dynamic on the CT, and wouldn't be the same everytime.  The fields I am using are "violation 1 description", and so on up to 6.  We have 1000+ possible violations, so hardcoding is out of the question.  (Also, in one citation speeding could be the first violation, but could be the third violation on another citation.)
 
I was thinking that a subreport would be the way to go.  If a citation has three violations, then return three records.  One for each violation, and then counted in the crosstab.
 
I hope that makes sense.  Thanks again for any assistance.

Posted By: DBlank
Date Posted: 10 Aug 2009 at 2:04pm

Hmmm, that is a bit messy. So you are trying tease out a few specific violation types (out of the 1000 possible ones) and count these per row where each violation type might be in 1 of the 6 fields on that row and each of these 6 fields use non-standardized text so "Speeding" might also be "speed" or "speedding" or "sped", then show the total count of these per officer?

Is that corect?

Posted By: draven422
Date Posted: 10 Aug 2009 at 2:22pm
Well, not really.  I want to have on the report all that is written within a timeframe.  (Every month in this case.)  I want "violation 1 description", "violation 2 description", "violation 3 description" and so on (up to 6) be considered one field that is on the top row of the CT.
 
In each citation record it will hold the officer name and all the six violations in seperate fields.  For example, one record has officer Smith (CT column) and violation 1 is speeding, violation 2 is seatbelt and violation 3 is stop sign.  Another record has Officer Jones that wrote a citation for seatbelt.  In the CT it would show:
            Speeding     Seatbelt     Stop Sign     Total
Smith    1                  1                1                  3
Jones    0                  1                0                  1
Total     1                  2                1                  4
 
Thanks again!!

Posted By: DBlank
Date Posted: 10 Aug 2009 at 2:32pm
Wouldn't really work well.
Can you write a stored proc or a view and use it as your source?
If you can convert the columns into rows and use the description (if it is standardized) as the field then you can do what you want.
Otherwise I think you are stuck with creating formual fields that ready each one and trying to uinsert those.
Also you can mimic the CT look by grouping on the officer and then doing 3 Running total to count a row if field 1='speeding or field2=speeding etc. for each of the 3 items (place the name and all 3 RT on the group footer, suppress everything else and draw lines aronud them for your "Cells").
Any of this reasonable for a solution for you?


Print Page | Close Window