Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Crosstab report and date range Post Reply Post New Topic
Page  of 2 Next >>
Author Message
crystalsonic
Groupie
Groupie


Joined: 26 Jan 2012
Online Status: Offline
Posts: 46
Quote crystalsonic Replybullet Topic: Crosstab report and date range
     Posted: 30 Jul 2013 at 11:29am
I have a crystal report in cross tab format that counts the number of episodes per day based on the day the episode was created. There are a couple of parameters defined FROM DATE - TO RANGE that is populated when the report runs automatically. The report works great, but I want to change the date for the column. Instead of using a date, I want to incorporate time and start from the previous day 7 am to today at 7 am. So if I enter a date range of 0722 - 0729, I would get 6 columns:
 
Column 1                  Columns 2                   Column 3               ETC...           
721 7AM-722 7AM     722 7AM-723 7AM      723 7AM-724 7AM 
# of Orders               # of Orders                # of Orders 
 
I got rid of the FROM DATE - TO RANGE and created a new formula:
(({Episodes.DateCreated} = CurrentDate-7 and {Episodes.TimeCreated} >= Time("07:00:00"))OR
({Episodes.DateCreated} = CurrentDate-6 AND {Episodes.TimeCreated} < Time("7:00:00")))
 
For testing purposes, I added the formula to the Column in the Cross TAB report with no luck. The report is going to run on Mondays and the first column should have SUnday 7am - Monday 7 am, second column should be Monday 7 am - Tuesday 7 am, etc...
 
I am not sure if I can do this, I know I need to tell the report when to go to the next column.  
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 30 Jul 2013 at 11:47am
consider using dateadd() to move the Episodes.DateCreated back 7 hours and group on that formula field
IP IP Logged
crystalsonic
Groupie
Groupie


Joined: 26 Jan 2012
Online Status: Offline
Posts: 46
Quote crystalsonic Replybullet Posted: 30 Jul 2013 at 12:33pm
Thank you!
 
I created a formula: DateAdd ("h",-7,{Episodes.DateCreated})
and I grouped on the formula for the column in the crosstab.
 
I entered FROM DATE-TO RANGE as 07-23 to 07-29. The first column says 7/22, second column says 7/23, etc... So I know, it is starting -7 hours.
 
The count for 7/22 is 1776. If I run a non cross tab report for the period of 07/22 7AM to 07/23 7AM I get a count of 1891.
 
I am not sure what it is using as the stop date and time for each interval. I think this is was is causing the count to be off. Any ideas?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 30 Jul 2013 at 1:37pm
You can also use the shifted date to group on as a day and then do all of your calculations using it.
Just use the original field for any display
IP IP Logged
crystalsonic
Groupie
Groupie


Joined: 26 Jan 2012
Online Status: Offline
Posts: 46
Quote crystalsonic Replybullet Posted: 01 Aug 2013 at 3:41am

I used the shifted day to group and I am displaying the original day. For a calculation, which I put in the Summarized field (distinct count):

If {Episodes.DateCreated} = DateAdd ("h",-7,{Episodes.DateCreated}) or {Episodes.DateCreated} <= DateAdd ("h",19,{Episodes.DateCreated})
then {Episodes.EpisodeNo}
 
It is giving me the same numbers as previously reported. I changed the formula to include a time:
 
If  (   {Episodes.DateCreated} = (Cdate(DateAdd("d",-1,{Episodes.DateCreated}))) and {Episodes.TimeCreated} >= Time("07:00:00AM")   )
OR
({Episodes.DateCreated} = {Episodes.DateCreated} and {Episodes.TimeCreated} < Time("07:00:00AM")) 
then
{Episodes.EpisodeNo}
 
The problem is that my numbers are cut in half.
 
IP IP Logged
crystalsonic
Groupie
Groupie


Joined: 26 Jan 2012
Online Status: Offline
Posts: 46
Quote crystalsonic Replybullet Posted: 01 Aug 2013 at 3:59am
SOrry, the distinct count additions are cut in half.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 01 Aug 2013 at 4:06am
If I understand your need correctly:
you have a formula field as
//shifted_DateCreate
DateAdd ("h",-7,{Episodes.DateCreated})
 
in the cross tab expert 
use this formula field (shifted_DateCreate) in the columns 
in the group options under the column group options set it to use 'For Each day'
under summarized fields add {Episodes.EpisodeNo} and set it to a distinct count
 
Other than the column headers is this what you are trying to accomplish?
 


Edited by DBlank - 01 Aug 2013 at 4:07am
IP IP Logged
crystalsonic
Groupie
Groupie


Joined: 26 Jan 2012
Online Status: Offline
Posts: 46
Quote crystalsonic Replybullet Posted: 01 Aug 2013 at 4:28am
The column header part works fine and that is how I have it set up. I need for the episode counts to follow the headers. The counts are based on the date the episode was created only for that day. I need the counts to go from COLUMN 1: Day 1 - 0700 AM to Day 2 - 0700 AM, COLUMN 2: DAY 2 7AM - DAY 3 0700AM and so on...
The counts are little different when you do distinct counts on COLUMN 1 -DAY 1 00:00 to  23:59, COLUMN2 DAY 2 00:00 - 23:59, etc... which is what is currently doing.
 
I hope this makes sense...
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 01 Aug 2013 at 4:40am
I am suggesting a different approach.
By shifting the data by 7 hours you make it match a regular 24 hour day and you can do all of your calculations and groupings using this "shifted date" and built in functions.
You can change the column headers later to make it match your  7-7 labels.
i just want to make sure the data is accurate.
Did you follow the above directions?
You can make a new criosstab to test it without losing your current efforts.
Did it give you the correct results other than just having one day name as the header.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 01 Aug 2013 at 4:42am
if it helps for you to "see" the information
create a group on the shifted_date formula set to a day
place your original date field in the detail section
note how all of your detail values run from 7am to 7 am inside the groups.
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.