SAP Crystal Reports - Divide data into Months following Current Date

[former_member989218]

Hello,

I have Sales Order report with Parts, open QTY and ship date of such parts.

In Crystal, I'd like to divide this data into the next 6 months from current date.

So basically...

Current Date - 3/27/19 April 19 May 19 June 19

Item A

Ship date 4/15/19 50 0 0

Ship Date 6/2/19 0 0 15

Is there anyway to do this? I'd like to do it without a date parameter since I need to capture all Open Orders (any ship dates prior to current date I'd just label as Overdue)

Also I need header to reflect the correct month.

So basically I'm looking for formulas month by month which would retrieve data for following month of current data, and then following month of prior montj and so on. Then using ShipDate populate any QTY for such months.

Thank you

[DellSC]

That's a much more complicated question. Based on the description you've given, you won't be able to use a cross-tab for this even though the data will appear similar to a cross-tab. The only way I can think of to do this would be to write a query that will put the data in the format you need to display it and use that query in a Command in Crystal. How good are your SQL skills?

If you need help with this, please let me know the type of database you're connecting to and then go to Database >> Show Query... in Crystal, copy the query that Crystal built and paste it here so I can play with it.

If you can get it to me this morning, I should have something for you early this afternoon. If not, I'm headed out of town for the next week to take care of some family business, so I wouldn't be able to get to it until I get back.

-Dell

[DellSC]

From your example, it looks like you're trying to show the ship dates for each item. If that's correct, then here's what you'll do:

1. Group by Item - either name or ID depending on what order you want it in.

2. Group by Ship Date. In the group options, set it to show by month.

3. Suppress the Ship Date group header and footer and put your data in the Details section.

4. To get the correct dates in the filter, you'll first create a formula that will calculate the end date based on the current date. It might look like this:

{@EndDate}
//Get the first day of the month that is 7 months from now
DateVar plus7 := DateAdd("m", 7, CurrentDate);
Date(year(plus7), month(plus7), 1)

5. In the Select Expert, edit the formula. Add the following to whatever other criteria that you have:

and {table.ShipDate} < {@EndDate}

-Dell

[former_member989218]

Sorry,

I did not realized that after sending my first answer all the spaces got taken off.

What I'm looking for is something different.

-----

Excel

File will have the following for a given Row - Example

Item A - ShipDate 4/15/19 - Qty 5

Item A - ShipDate 4/29/19 - Qty 3

Item A - ShipDate 5/10/19 - Qty 22

Now In Crystal

I'll have a CurrentDate formula somewhere saying it's 3/28/19 for this example

Data will be Grouped by Item Part

Column A will be Ship Dates for that Item.

Then, I'd have 6 more columns each with the formula representing the following months from current date.

So from my example... Column B would be April 2019, Column C would be May 2019, Column D would be June 2019, and so on.

So Having Said that it'd look something like this...

Item A

Column A (ShipDates)

4/15/19

4/29/19

5/10/19

Column B (Header Formula updates to following Month of current date (April)?)

For 4/15/19 5

For 4/29/19 3

Total 8

Column C (Header Formula updates to 2nd Month from current date (May)?

For 5/10/19 22

Total 22

And so on...

Maybe with that detail it makes a bit more sense. I can't do this with parameters since I need to account for all Open Sales Orders within Excel file in order to mark the ones past due.

Thanks again !

[former_member989218]

Sorry I sent a reply and never submitted.

Well, I'm looking for something different. See following... for Excel file and what I need in Crystal

Excel File

Item A / ShipDate 4/15/19 / QTY Open 5

Item A / ShipDate 4/21/19 / QTY Open 3

Item A / ShipDate 5/3/19 / QTY Open 15

Crystal Reports

Current Date Formula

Group by Item

1st Column - ShipDate (Header)

4/15/19

4/21/19

5/3/19

2nd Column - Following Month from Current Date (This Case April - Someway for Header to fill in next Month?)

5 (Qty open within April)

3 (Qty open within April)

3rd Column - 2months from Current Date (This Case May - Someway for Header to fill in next Month?)

15 (Qty open within May)

And so on.... I'd like to forecast 6 months like this from Current Date.

Also, I can't do this with date parameter since I need to see ALL data in order to know what's past due.

Thank you,

Federico