Using cross-tab reports to print quantities for 2 date ranges

[css1015]

Hi,

This is an accounting report being created in Crystal 8.5.

The user enters the date range (begin and end date within the same year - I then use only the month and year parts for my report)
This is the format of the report

Grouping Fields:
Currency Id
Item Class
Customer Class

Details:
Customer No.,Customer Name,
Curr.Yr. Date Range Sales, Prev.Yr. Same Date Range Sales, Curr. Year To Date Sales, Prev. Year To Date Sales,
Sum(MonthlyValue)


I started a cross-tab, it worked fine for the curr. yr date range sales and prev. yr. same date range sales because I could set the report criteria based on the same begin and end month and use the report year for the cross-tab column. If the begin month is Feb - 2 and the end month is Jun - 6, it would look something like:

Cust No. Cust Name 2001 2002

AFIT0001 Aaron Fitz Electrical $ 519.25 $419.40

But how do I add a year-to-date sum if I have to retain the same cust no. and cust name displayed and all grouping details but use a diff. month range (in this example, Jan - 1 to Jun - 6). It is like plugging a bunch of unrelated values in an Excel spreadsheet and copying them over. I can have only one set of report criteria. Can this be done at all?

Eventually, it should look like:

Cust No. Cust Name 2001 2002

AFIT0001 Aaron Fitz Electrical $ 519.25 $419.40

2001 YTD Sales 2002 YTD Sales

$599.25 $468.90

and this should all be in one line. As you can see, this is a report to look at trends.

I appreciate any input on this. I have examined using a stored procedure with Crystal 8.5 but a stored procedure still returns a rowset with only one set of criteria, here, with multiple date ranges, I have 2 sets of criteria with only the end month common. I couldn't figure out how to get subreports to work either. Please help.

Chad Sriram

 

[lbass]

The easiest approach would be to do a manual crosstab.
First create three discrete number parameters: {?EndYear},
{?StartMonth}, and {?EndMonth}. You should also be grouping on CustomerNo.

In your record select, you would need to use a formula like:

{sales.date} in Date({?EndYear}-1, 01, 01) to
Date({?EndYear},{?EndMonth}+1, 01)-1

Then create formulas like the following:

{@prevyearperiod}:
if month({sales.date}) in {?StartMonth} to
{?EndMonth} and year({sales.date}) = {?EndYear}-1 then {sales.amount}

{@yearperiod}:
if month({sales.date}) in {?StartMonth} to
{?EndMonth} and year({sales.date}) = {?EndYear} then {sales.amount}

{@previousYTD}:
if {sales.date} in Date({?EndYear}-1, 01, 01) to
Date({?EndYear}-1,{?EndMonth}+1, 01)-1 then {sales.amount}

{@YTD}:
if {sales.date} in Date({?EndYear}, 01, 01) to
Date({?EndYear},{?EndMonth}+1, 01)-1 then {sales.amount}

Then you would insert sums on these at the group level and suppress the details section.

-LB

 

[css1015]

Thanks, I am working on it right now. Will let you know if I could get it to work. I have never worked with manual cross-tabs, so please bear with me regarding this question.

i) Does a manual cross-tab mean that I start with a blank report and insert a cross-tab using the Insert menu? If I do that, do I use the same columns and summarized fields for my cross-tab like the original one that the report expert did for me? So far, the cross-tabs I have created using the REPORT EXPERT never gave options to insert formulated fields as summarized fields in the cross-tab. Is it different with a manual cross-tab?

Or

ii) Does a manual cross-tab mean that I start with a blank report, insert my fields in the report header section instead of the details section, then insert my groupings for currency id, item class, customer class and customer number, then create my parameters and formulas, and then insert the formulated fields in your reply besides the customer name?


Please advise.

Chad Sriram

 

[lbass]

You have the report mostly designed, so I would save it under a different name, and then delete the crosstab you were using. If you don't already have a group on Customer Number, insert one (I'm not sure what the Customer Class group is, but it sounds like a higher order group than Customer Number). Instead of the crosstab, create the parameters (if you want to use parameters for this) and the formulas above and then insert the formulas in the details section. If you then insert sums on the formulas you can suppress the details and display the data in either the group header or group footer for the customer number group.

You might also need to change your record select statement as I indicated in my earlier post, since it must include records over the two-year period.

A "manual" crosstab means you are creating a crosstab layout using formulas instead of inserting a crosstab or using the crosstab report (I've never figured out how that's different from using a blank report and inserting a crosstab). If you were doing this from scratch, you would just start with a standard blank report.

Please let me know if you have more questions.

-LB

 

[css1015]

Thanks LB, your suggestions worked great!!! Reporting is a different kind of animal compared to programming. It is always useful to learn quirks about it from an experienced user . Thanks again.

Chad Sriram