13 Month Crosstab

[databuilt]

I need to create a crosstab table that will take input parameters of {?BeginDate} and {?EndDate} for a 13 month period and display colum values for each month starting with the first column representing the month including the BeginDate. The date values coming out of the database are hybrid Julian dates (thanks to JD Edwards). Row data is summary period data (sales by customer).

I'm not sure if I need an additional lookup table or, for that matter, how to start this project. I do have a finished report, but it is accomplished with 13 date-ranged CASE functions in the SELECT statment which have to be manually tweaked each month.

Any and all suggestions will be appreciated.

 

[synapsevampire]

It seems that having bot a start and end date can only serve to complicate if it's always 13 months...

The start could compute the end, or vice versa using the dateadd() function.

To create a crosstab for a monthly period, insert the date field (which you'll create using the formula to follow) into the crosstab column or row (you gave no indication of how this is supposed to look), select group options, and select A Column Will Be Printed For Each Month.

The only trick is to convert the JD Edwards Julian date to a real date, and there are different approaches for this.

Here's the CD whitepaper on this:

http://support.crystaldecisions.com/library/kbase/articles/c2012088.asp

-k

 

[databuilt]

Thank you for your help. I can't get to this now but will tomorrow.

-D

 

[databuilt]

I finally am getting back to this. Thanks for the info on the whitepaper, I have already put this to use on several reports. It's a good solution when you have results back and are formatting for display. However, I am at a loss as to how to convert the JDE date during the SQL query such that the results returned include a date value I can use in a column of the CrossTab report. I'd like to do as much processing on the server as possible.

My display is intended to look somthing like the following:

Column for each month period
Rows for each Customer
Data: sum(sales)

JAN FEB MAR APR
Customer1 100 150 - 300
Customer2 50 75 100 43
etc.

-D

 

[synapsevampire]

Consider using a SQL Expression to convert it to a date, this is dependent upon the database used.

Otherwise you'll need to convert the date in Crystal, which really isn't much overhead.

As for limiting the data in JD Edwards to the past 13 months, convert the dates (start and end for the 13 months) to JD Edwards format, and use that in the record selection formula.

Here's some help with JD Edwards dates, you'll have to change the formula, but the logic is there I think:

http://www.midrangeserver.com/mgo/mgo020503-story01.html

-k

 

[databuilt]

I tried using a formula in CrystalReports and it worked - to some degree. The SQL looks like this:
SELECT
sdslsm
, sum(sdaexp)/100 netsales
, sdivd invDate

FROM proddta.f4211

WHERE
sdivd>={?BeginDateJDE} and sdivd<={?EndDateJDE}
and sddct='RI'
and sdktln=0
and sdsocn=0
and not sdlnty in ('F','M','RS')

GROUP BY
sdslsm
,sdivd

Can I put a function inside this SQL? Using the Command SQL capability, I loose the CR SQL Expression capability.

-D