In vs Out
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 2014||Feb 2014||Mar 2014||Apr 2014||Jun 2014|
Being able to show the difference would be create as well.
Dell Stinnett-Christy replied
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:
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.