Group on calculated date

[chvchk]

Hi all,
I have a report with a subreport passing a manufacturing lead time shared variable to the main report (mfglt). I'm then using the shared variable to determine the date a part needs to start production.

Here's my formula to achieve the date and return a date during the week when production is to start:
whileprintingrecords;
shared numbervar mfglt;
shared datevar flrdate:= {Order Master.CURDUE_10}-mfglt;

If DayOfWeek (flrdate) = 1 then
flrdate := flrdate - 2 else
if DayOfWeek (flrdate) = 7 then
flrdate := flrdate - 1
else
flrdate

I would then like to group on this formula, but it isn't listed as an available field in my group list. What are my options?
My grouping will need to be by week, with all the data in the group in ascending date order.

Here are my specs:
Crystal version 8.0
Pervasive Database using index.ddf to connect

Pertinent Report layout:
GH1 group on order number
GH1a - suppressed
@reset
whileprintingrecords;
shared numbervar mfglt:= 0;
shared stringvar pmdesc := "";

GH1b
Subreport

GH1c
This contains the field with the production start date formula along with other fields related to the order number

Detail section - suppressed
contains part specific information to base summaries off of



Thanks in advance,
Heather

 

[chvchk]

I should probably state that I've searched this forum extensively and have tried multiple methods only to hit a wall.

The last method I started to try was from this post:

http://www.tek-tips.com/viewthread.cfm?qid=1125309

but I was hoping there might be something simpler maybe a different way to write the formula getting the date so the data is available during first pass???

Oh and just for information sake, I'm fairly new at crystal and know just enough to get me in trouble....

 

[synapsevampire]

Use advanced SQL in either a SP, View, or in Crystal a SQL Expression or Add Command.

You'd need to share technical information to allow us to assist you.

Crystal version
Database/connectivity used
Sample data
Expected output

-k

 

[chvchk]

Thank you synapsevampire, but could you expand on this statement?
"Use advanced SQL in either a SP, View, or in Crystal a SQL Expression or Add Command."

Not to be disrespectful as I do appreciate all the help you've been to the board through all the postings I've read, BUT I already shared my technical information in my first post....

As stated above in my original post,

My crystal version is 8.0
Database/connectivity is Pervasive using index.ddf to connect

Here's a little more detail of what I expect to see although this is in a row format across the page:

Week 24 (group by floor date, output by week)

WO
333222

P/N
004-810-2704

Line Items
89

W/O Qty
25

Lead Time(mfglt)
12 days

Floor Date({Order Master.CURDUE_10}-mfglt)
6/20/06

Ship Date ({Order Master.CURDUE_10})
7/2/06


Please note that I already have the report giving me the desired output, I'm just not able to group on the Floor Date field that is obtained by the formula I posted in my first post.

 

[synapsevampire]

Your formula that creates the date can't be used in the main report for several reasons, and if you elect to go with that method, I suggest that you use the subreport as the main report in which case a slight rewrite of your grouping formula might work OK in the new main report, depending upon WHERE in the subreport the formula is located.

I still suggest that you show example data (not just output, show what is in the tables involved).

-k

 

[dgillz]

The reason you cannot group on that formula because it is evaluated WhilePrintingRecords. To dig a little deeper, you are obviously passing a value from a subreport with your shared variable. Is there some reason this value is not available in the main report?


Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"making predictions is tough, especially about the future" - Yogi Berra

 

[chvchk]

Hmmmm...Ok...
Most of my problem with this report is that it was originally designed by someone else that no longer works here and wasn't very experienced in understanding table linking and how it can greatly effect report performance. The main table in this report is one with no primary key. In my experience and training, this is a no-no. I've tried to start over, but I continue hitting walls each time I do. I'll end up with a completely different problem than the one I'm having right now. At least right now, I'm getting the correct data....however, last week my patience was running really thin....
But what I really think I'm dealing with is a crappy report design. I'm going to give it another shot with a blank report and see how far I can get. If I hit a wall this time I'll start another thread if I can't resolve my problem by searching the forum first.
Thank you for your help so far. Hopefully the break over the weekends was what I needed to look at this from a different angle...