Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Crosstab Help Post Reply Post New Topic
Author Message
WernerCD
Newbie
Newbie
Avatar

Joined: 01 Jul 2010
Location: United States
Online Status: Offline
Posts: 8
Quote WernerCD Replybullet Topic: Crosstab Help
     Posted: 13 Jul 2010 at 7:47am
I am trying to create a crosstab and I just can't seem to get it working...

Database fields: Submit Date, Finish Date, Priority.
Desire: Create a report that sums, for the start of a given month (as of Jan 1st... jun 1st... etc) the number of "Open" tickets (Submit date < given date... complete date >= given date OR no complete date thus still open)

So basically something like:
        Jan    Feb    Mar
2007     ###    ###    ###
2008     ###    ###    ###
2009     ###    ###    ###

As sub of open priority/month/year would be nice as well for completeness.

Been banging my head against desk trying to wrap my head around crosstabs. Still searchin for clues on how to do this... ugh
IP IP Logged
Emir_W
Senior Member
Senior Member
Avatar

Joined: 25 Apr 2010
Online Status: Offline
Posts: 228
Quote Emir_W Replybullet Posted: 13 Jul 2010 at 3:40pm
you need to have a formula which will check for each months.
where it calculate the 'open' ticket.
and put this formula as detail in your crosstab, monthname as column and year as row.
 
e.g.:
monthopn=
if month({tbl.date})=1 then
   if {submitdate}<{givendate} and {comptldate}>={givendate} or isnull{comptldate} then 1 else 0
else if month({tbl.date})=2 then .....
else if month({tbl.date})=3 then .....
...
else if month({tbl.date})=12 then
   if {submitdate}<{givendate} and {comptldate}>={givendate} or isnull{comptldate} then 1 else 0
 
 
hope it help.
 
 
Emir W
IP IP Logged
WernerCD
Newbie
Newbie
Avatar

Joined: 01 Jul 2010
Location: United States
Online Status: Offline
Posts: 8
Quote WernerCD Replybullet Posted: 14 Jul 2010 at 3:58am
If I understand correctly

For each record in the database
- then for each tab in the cross tab for each db record..

it looks at this formula...

If submit = cross-tab month then
- if open and not complete for "given date" add one to that total...

Only question I have then is... how do you set "given date" which should be the year/month/1st of the month... for given cross-tab position in give record.

Could you simply create a "local variable" that pulls the cross-tab info Year & month? So that for each db record then for each cross-tab position it calculates it?

What I have so far with givendate being my unknown:
     if month({command.submitdate})=1 then
   if {command.submitdate}<{givendate} and {command.completedate}>={givendate} or isnull{command.completedate} then 1 else 0
else if month({command.submitdate})=2 then
   if {command.submitdate}<{givendate} and {command.completedate}>={givendate} or isnull{command.completedate} then 1 else 0
.....
.....

Much appreciation :)I've been trying to wrap my head around cross-tabs but haven't had a good example that makes sense to me.


Edited by WernerCD - 14 Jul 2010 at 4:02am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Jul 2010 at 4:16am
Question.
When you say you want to count the number of open tickets for each month does that mean:
1. Count a ticket in the month if the ticket start date falls in the month (this is an easy crosstab)
or
2. count a ticket in each month that it was created and also not closed during (much harder)


Edited by DBlank - 14 Jul 2010 at 4:16am
IP IP Logged
WernerCD
Newbie
Newbie
Avatar

Joined: 01 Jul 2010
Location: United States
Online Status: Offline
Posts: 8
Quote WernerCD Replybullet Posted: 14 Jul 2010 at 7:52am
On the first day of each month I want to count the number of open tickets for that date.

So on April 1st, 2010 there were ### tickets open.

If there was a ticket that was opened at any point before today (Bleh 1st) and it was completed today, after today OR isn't complete, count it.

So... for this database I basically have two important database fields: SubmitDate and CompleteDate.

What i need is GivenDate. Aka the date for the particular crosstab junction we are counting. June, 1st 2010... july 1st 2009.

I think this is close to what I want, if I'm following it properly (or I'm miles away lol).

DateValue(Year,Month,Day) will give the correct 'givendate' just not sure now how to pull that from the crosstab portion into DateValue.

_________________________________________________

local datetimevar givendate = datevalue(2009,12,1);
if month({command.submitdate})=1 then
     if {command.submitdate}< givendate and
      ({command.completedate}>= givendate or isnull({command.completedate}))
      then 1
      else 0
else if month=2.....
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Jul 2010 at 8:21am
the problem is that one row of data can be counted multiple times (even over multiple years) and you have no source table that is the first of the month of each month and each year.
I am pretty sure you would have to create a unique formula for every year/month that yuo want to analyse which is ugly.
If you had a source table with each year/month it would make it a lot easier.


Edited by DBlank - 14 Jul 2010 at 8:21am
IP IP Logged
WernerCD
Newbie
Newbie
Avatar

Joined: 01 Jul 2010
Location: United States
Online Status: Offline
Posts: 8
Quote WernerCD Replybullet Posted: 14 Jul 2010 at 10:31am
Not sure I follow what your saying...

I've done a report with a slew of running totals and I was trying to stay away from that with this report heh :)

What I'd like to do... is use the column/row name/numbers in the equation...

Basically the table is setup with Year/Month combo... if I could pull that info into a local variable, I could use that to say "This month" as 'given month'.

So at this junction, if the record submit date is previous and complete date isn't, count it.

Everything else is looking stellar. I'm actually understanding a lot more about crosstabs than I knew before.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 14 Jul 2010 at 10:44am
maybe I am missing something but here is the problem I am seeing...
Issue 1 is a select statement to pull your data based on date param or params- this is pretty straightforward.
issue 2 is you want to count one row of data into multiple cells in the crosstab. This is the one I am not seeing a good solution for.
 
Crosstab summaries are Counts/Sums/DistnctCOunts/Averages etc. of the data.
I can easily give you a solution for counting when a ticket got opened per month as column and year as row using this but you need to evaluate a row multiple times for each column (year/month) which requires a Running Total.
Is that making more sense or am I missing something in your set up?


Edited by DBlank - 14 Jul 2010 at 10:45am
IP IP Logged
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.