I’m fairly new to Crystal, and using version 8.5.0.217.  All learning has been on my own and using “Crystal Reports: A Beginner’s Guide” by David McAmis.  I’m trying to write a report that I think is possible, but am not sure where to begin. 

 

My first question is to get advice on whether or not the below is possible.  If it is, suggestions of how to approach & recommended formulas are appreciated.

 

I am trying to create historical monthly (or weekly) data from a transaction table.  Fields available:

1.       Item

2.       Date of transaction

3.       Time of transaction

4.       Inventory on hand at the date & time of the transaction

 

What I’d like to do:

a.       Create separate fields for the last 16 months.  Eg… current month = m, last month = m-1, etc…  (I do know how to create a new formula field… the problem is in how to write the formula that goes into it)

b.      Current month would show the inventory on hand, for each distinct item, with the most recent transaction date and time, within the current month.  If no transactions happened for the given part in the current month, then take inventory on hand for the most recent date and time.

c.       Last month (m-1) = show the inventory on hand, for each distinct item, with the most recent transaction date and time, within month (m-1).  If no transactions happened for the given part in the given month, then take inventory on hand for the most recent date and time.

d.      Continue on for x months of history

 

Hope that made sense.  Any insights are appreciated.

 

One last question.... knowing that I'm in an old version of Crystal, is there still enough benefit to justify  buying the encyclopedia, or are there other books that you would recommend?  I do enough Crystal reporting that the beginners book I have is too basic. 

Basically yes.

Example to clarify a little bit... if the part only had 3 months of data, but it was OLD data in m-6, m-9, and m-12), then the result would show (all on the same row):

part #1

Sorry for the confusion.... I think I misspoke in my initial post.

Gotcha. Well, I'm going to write this off the top of my head so you will have to tweak/debug it. If you understand the general idea then you should be able to work it out...

First off, you need to group by Item. This will let you put formulas in the group header and footer.

Next you need a formula which declares the global variables and zeros them out. Something like

Global NumberVar m1:=0;
Global NumberVar m2:=0;
etc.

Put this formula in the Group Header. Now, every time a new item is ready to print, all the variable will be reset.

Now for the tricky part. You need to store the historical data for each month in the appropriate variable. I would do this by finding out how many months are between the today and the current record's month. Then use the result to store to figure out which variable to store the value in.

Global NumberVar m1;

Global NumberVar m2;
....

NumberVar NumMonths;
NumMonths = DateDiff(CurrentDate, {yourtable.yourtransactionfield}, "M");
Select NumMonths
    Case 0:
        m1 := {yourtable.yourinventoryonhand}
    Case 1:
       m2 :=  {yourtable.yourinventoryonhand}
    ....
    Case 15:
       m16 =  {yourtable.yourinventoryonhand};;

Put this in the Details section. Everytime a record is ready is going to print, the appropriate variable will be populated.
You also need to Suppress the Details section because you don't want to see all that data as it's being calculated.
Once you get to the group footer, all the variables will have their data popluated. So create a formula that simply outputs each variable. One formula per variable.

Global NumberVar m1;
m1;

Global NumberVar m2;

m2;

etc.
Put each one of these formulas in the Group Footer. When the report runs, the Details section will do the work of calculating where the on hand numbers belong and the group footer will display them.

Give that a whirl and see what you come up with!

Brian