Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: crosstab multiple fields in one row Post Reply Post New Topic
Author Message
draven422
Newbie
Newbie


Joined: 21 Apr 2009
Online Status: Offline
Posts: 11
Quote draven422 Replybullet Topic: crosstab multiple fields in one row
    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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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?
 
IP IP Logged
draven422
Newbie
Newbie


Joined: 21 Apr 2009
Online Status: Offline
Posts: 11
Quote draven422 Replybullet 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.


Edited by draven422 - 10 Aug 2009 at 1:57pm
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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?
IP IP Logged
draven422
Newbie
Newbie


Joined: 21 Apr 2009
Online Status: Offline
Posts: 11
Quote draven422 Replybullet 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!!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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?
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.