Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

In vs Out

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

Former Member
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:

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

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question