Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Crosstab multiple queries? Post Reply Post New Topic
Page  of 2 Next >>
Author Message
KanadianKevin
Newbie
Newbie


Joined: 19 Aug 2013
Online Status: Offline
Posts: 12
Quote KanadianKevin Replybullet Topic: Crosstab multiple queries?
     Posted: 26 Aug 2013 at 7:14am
So I've been asked to create a report that summarizes our monthly incidents as follows:

                                      Month1 Month2 Month3
Category Subcategory Priority #opened   99     99     99
                             .#closed   99     99     99

(note: ignore the period before #closed - used just for formatting this message)

Is this possible to do with a crosstab? I've been able to get it to work with #closed incidents but I can't figure out how to get it to add the additional #opened row and summary data. How would I accomplish this?

By the way I'm a complete beginner with CR, learning as I go, so sorry if this is an obvious question.

Thanks!!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 26 Aug 2013 at 10:46am
can you explain your raw data set more or show sample row level data?
It appears you choose to use Running Totals to get your numbers but without understanding your data set it is very difficult to offer much help.
IP IP Logged
KanadianKevin
Newbie
Newbie


Joined: 19 Aug 2013
Online Status: Offline
Posts: 12
Quote KanadianKevin Replybullet Posted: 26 Aug 2013 at 11:07am
Thanks for replying! I'll create some sample data and hopefully it will make sense:

So the rows would contain incident data and look something like this (again ignore the random periods included for formatting):


Category   Subcategory   Priority Opened          Closed

Cat1       Subcat1       Medium    5/1/13 09:45   5/1/13 12:30
Cat1       Subcat2       High      6/28/13 01:23 .7/1/13 13:22
Cat2       Subcat1       Medium    7/26/13 8:23   null


So the way this would hopefully look in the end is:


                                          5/2013 6/2013 7/2013
Cat1       Subcat1   Medium .#opened      1       0      0
                             #closed      1       0      0
           Subcat2   High    #opened      0       1      0
                             #closed      0       0      1
Cat2       Subcat1   Medium .#opened      0       0      1
                             #closed      0       0      1
                  


Using crosstabs I've been able to get this working with only #closed incidents. I have yet to figure out how to get the opened ones in there as well.

Thanks!!!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 26 Aug 2013 at 11:23am

can you write a command or some other external source to alter the way you pull the data in?

Counting teh same row in two different months will be tricky as is but super easy if you can change your source.
Also I asusme each row has a primaryKey field that is unique.
IP IP Logged
KanadianKevin
Newbie
Newbie


Joined: 19 Aug 2013
Online Status: Offline
Posts: 12
Quote KanadianKevin Replybullet Posted: 26 Aug 2013 at 11:29am
Yes - each row has an incident number field that is unique. I'm not sure that we can change the source - as far as I know the row data is only found in one table. And I can't alter any of the tables or anything - I don't have admin rights on the database.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 26 Aug 2013 at 12:05pm
can you write a command object in Crystal?
IP IP Logged
KanadianKevin
Newbie
Newbie


Joined: 19 Aug 2013
Online Status: Offline
Posts: 12
Quote KanadianKevin Replybullet Posted: 26 Aug 2013 at 12:08pm
I've never done that before but I think I have the access to do it...
IP IP Logged
KanadianKevin
Newbie
Newbie


Joined: 19 Aug 2013
Online Status: Offline
Posts: 12
Quote KanadianKevin Replybullet Posted: 27 Aug 2013 at 5:23am
So I've done a little bit of research on command objects... still not entirely sure how to do it but in theory I'd need to somehow create a temp table like:

incident# cat   subcat priority Status Statusmonth

Where status is opened or closed and status month is the month in which that status was set. Then you split out the incidents so that each one potentially has 2 rows
then you'd crosstab it so that you do a count on Status for each statusmonth

does that make sense? If so - how do i do that??
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 Aug 2013 at 5:37am
I was thinking just querying the data twice with a union statement
 
would give you two rows per item each labels as an open or closed and making the open and close dates into the same column.
you an then do a disctinct count of the primary key (PkID) for your numbers based on the date grouped as a month
 
select PKID,Cat, Subcat, Priority, OpenDate as NewDate,'Open' as countype
from table
union
select PKID,Cat, Subcat, Priority, CloseDate as NewDate,'Closed' as countype
IP IP Logged
KanadianKevin
Newbie
Newbie


Joined: 19 Aug 2013
Online Status: Offline
Posts: 12
Quote KanadianKevin Replybullet Posted: 27 Aug 2013 at 5:41am
Okay I can give that a try...

Now keeping in mind I'm completely new at crystal and am learning as I go, where would I create and execute that statement?
IP IP Logged
Page  of 2 Next >>
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.