cancel
Showing results for 
Search instead for 
Did you mean: 

In vs Out

Former Member
0 Kudos

Hello,

I am looking for the best way to who In vs Out as a Chart or CrossTab in Crystal Reports based on Date.

Each record has three columns.  Request ID, Date Created, Date Closed.  Request ID is a integer and both dates are Date Time.

The problem I am having is showing this neatly in one Chart or Cross Tab.  Below is an example of how I'm looking for the cross tab to look like

Jan 2014Feb 2014Mar 2014Apr 2014Jun 2014
In76589
Out98567

Being able to show the difference would be create as well.

Thanks,

Kevin

Accepted Solutions (1)

Accepted Solutions (1)

DellSC
Active Contributor
0 Kudos

There are a couple of ways that I know of to do this.  Both require either using a command or a stored procedure, so you'll need to have fairly good SQL skills.  Here's the basic logic for both:

1.  Create a view (or use a "With" clause in your query) that returns the date of first day of the month for the 12 months in the report.  I'll call this "Calendar" and the field name "FirstOfMonth".  Join from this to your data on Month(Calendar.FirstOfMonth) = Month(MyTable.DateCreated)  Month(Calendar.FirstOfMonth) = Month(MyTable.DateClosed).  In the Select part of your query, include a case statement like this:

case

     when Month(Calendar.FirstOfMonth) = Month(MyTable.DateCreated) then 'In'

     when Month(Calendar.FirstOfMonth) = Month(MyTable.DateCreated) then 'Out'

end as status

This will pull all of the data for you to use in your crosstab.  The "status" field from this case statement becomes the "row" for your cross tab, Calendar.FirstOfMonth is the column (you can set the format of the date in the cross tab to show just the month and year), and count of Request ID is the summary.

2.  Once again using a view or "with" clause that returns the first date of every month, pivot the data in a query.  This will return far fewer records and they'll be in a format where the fields from a single record can be put in a details section to get you all of the data.  All of the actual number crunching gets passed to the database instead of being done in Crystal, which will make the report run faster.  This SQL is a bit complex though.  If you're interested in going this route, please let me know what type of database you're connecting to (Oracle, MS SQL, etc.) and I might be able to provide a sample for how I usually do this.

-Dell

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Kevin,

One clarification regarding this In vs Out..

What based on calculate the values In and Out  i.e For Ex

             Jan 2014

In          7

Out       9

Can you please clarify and add more information?

Thanks,

DJ