Crystal Forecast Report

[AKPolarBear]

I have a report built for reviewing purchase/sales/forecasting by item for the current month. Our purchasing dept. uses this as part of the inventory control and ordering functions.
I have run into a (common) problem when using CRW and some of the Macola databases - more than one entry for an item. Sometimes this is a linking problem, other times it is related to the number of entries for that item. I.E. if it has 1 entry, it reports once. It there are 3 entries, then it repeats all the data 3 times. This is what I am fighting now with the Forecast order file.
Here is the formula I am using:

if month(currentdate) = month(numbertodate({MSSCHFIL_SQL.due_dt}))
then {MSSCHFIL_SQL.ms_qty}
else 0

Works great if only one FO for the item in the current month. It selects the current month and ignores the others in the data file.
In another part of the report, it shows the forecast for the following month as well, based on this same formula.

An example of this is if one item has two FO's per month with quantities that are 100 & 200. Total FO should be 300 for that item/month. What CRW reports shows are 4 entries: 100,200,100,200 for a total of 600.

Can anybody suggest a way to prevent this duplication? I have run into this before and am not always able to solve it. I suppose I could have CRW count the number of entries and divide the total by that number, but that does not appear to be the correct solution even if it does work!

 

[dgillz]

Are you using ODBC or a Native connection in Crystal? What type of join are you using?

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

 

[AKPolarBear]

ODBC to Sql database

No join used as this is a subreport based on the single data file MSSCHFIL_SQL.

Macola is 7.6.100a and CRW is ver 7 for Macola.

 

[dgillz]

What other tables are you using and how are you linking them?



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

 

[AKPolarBear]

In this subreport, only the one table is used. It is linked to the main report by item number & location.

The problem is wholly in using this particular table.

The driver we use is pdsodbc.dll.

 

[AKPolarBear]

I think a work around has been found. I will give it to a user for testing.

But I would still like to figure out how to handle this type of problem as it does come up. In other cases I used left outer join to solve most problems. That was not available to me in Btrieve before.

 

[kscheeler]

There are a few ways to handle it. Since you are running SQL, you could create views in SQL to summarize the data. In your case, you would create a view from the MSFRCFIL_SQL that would group by item_no, loc, and date. It would then sum the quantity. You can then add the view in Crystal just like another table. It would show up as just one record for item, location within a month.

Another option for more complex reports that need to preprocess alot of data is to create a stored procedure in SQL to do most of the work up front and just send crystal the final output.

Kevin Scheeler

Kevin Scheeler

 

[dgillz]

I agree with Kevin. A view or SP would be great for this.

In crystal, under file options, make sure that allow reporting on Views and Stored Procedures is turned on.

As a sidebar, you can actually use a stored procedure in a crystal report and WRITE BACK to the database.

I have actually done this on a pick ticket and used an SP in a crystal subreport to update the OEORDHDR_SQL.Ord_status to 4-Pick Ticket Printed and to update the OEORDLIN_SQL.picked_dt to the YYYYMMDD expression of today's date.

This is not for the faint of heart, but this is doable if you know a little SQL.

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

 

[AKPolarBear]

Thanks to you both. I will give this a try and let you know how it turns out.

 

[crystalreporting]

Just curious - has anyone developed a stored procedure to 'flatten' the BMPRDSTR_SQL table? I've toyed with this idea for some time as a way to make life easier for clients wanting to develop BOM reports. Any thoughts? Would anyone else be interested in this if I develop one?

Peter Shirley

http://www.linkcorp.com

 

[AKPolarBear]

Okay, I tried it and I like it. Worked almost perfectly for me. Using the MSFRCFIL_SQL table, I created the view as outlined above and summed the data. Then in CR, I then linked it to another table and that all worked just fine.

However, could you explain the absence of some of the data in the view? For the "test" item, the data for date 20041001 is missing. The data for 20041101, 20041201 is there.

But in my CRW, the data is plainly visible for 20041001. It happens that the answer is correct because both FO for months 10 & 11 are the same in this case.

I noticed in the view that none of my 01 dates are listed for all of items listed that are prior to the current date (day =14) but future 01 dates are (20041101, etc) listed there.

A second question. When the CR is run will the SQL View I created be automatically updated?

 

[kscheeler]

For the first question, copy in the code for the view. It could be how you are grouping within the view.

For the second question, the view simply reads the data in the Macola database. The view doesn't store any data so when Crystal is running against the view, the report reads the view and the view reads the actual Macola tables.

Kevin Scheeler

 

[AKPolarBear]

Clarification: When I said I looked into the CR and could see the missing dates, I meant to say : When I looked at my current CR and not the new one, I could see the missing data. In the new one I created, I could not see the data.

Here is the code:

CREATE VIEW dbo.MSFRCFIL_SQL_VIEW
AS
SELECT item_no, loc, due_dt, SUM(qty) AS qty
FROM dbo.MSFRCFIL_SQL
GROUP BY item_no, loc, due_dt

 

[kscheeler]

Your view looks fine. What is your selection criteria in the CR.

I would create a CR with just the view in it. Then select everything and see if the data that you are looking for shows up. Since you are not limiting it in any way, it should. My thinking is it is being limited either with your join to another table within your CR or in the selection criteria.

If you list both, I think we can figure it out.



Kevin Scheeler

 

[maccrystal]

Try this to aggregate the info into one record for each item for the year and month. Replace this into your view or stored procedure.

Select Item_no, (SUBSTRING(convert(char(20),Due_dt),1,4)),
(SUBSTRING(convert(char(20),Due_dt),5,2)),
Sum(Qty) from MSFRCFIL_SQL
group by (SUBSTRING(convert(char(20),Due_dt),1,4)),
(SUBSTRING(convert(char(20),Due_dt),5,2)), Item_no

http://www.appstratinc.com

 

[kbieber]

Referring back to dgillz comment about updating SQL tables with an SP from within a Crystal subreport.... i'm not sure i understand why this method would be used. Can you explain the circumstances a bit?

Thanks,
KB

 

[dgillz]

In my example, if you run a the Macola crystal report for a pick ticket it does not update the database and set the order status to 4 - pick ticket printed, nor does it populate the order lines' date picked field.

Using and SP that writes back to the database, a crystal report can do this.

Let me know if you have any questions.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[AKPolarBear]

Just an update. Here is the output from the view I posted above, limited to 2 items:

item_no loc due_dt qty

5700-20501 A00 20041101 600
5700-20501 A00 20041201 240
5700-30601 A00 20041101 210
5700-30601 A00 20041201 120

Here is the output from your suggested view:

item_no Expr1 Expr2 Expr3
5700-20501 2004 11 600
5700-30601 2004 11 210
5700-20501 2004 12 240
5700-30601 2004 12 120

Here is a partial view of the CR (a lot of other stuff was cut out to fit this screen):

Item Forecast Forecast
Number Current Month Next Month

5700-20501 0 600
5700-30601 0 120

In the CR, I am using the view I created. I don't see any real difference of one over the other. As far as the missing data, it's gone from the report now as they must have recently merged or purged. Maybe next month I will see how it works out. Thanks for the help!

 

[kbieber]

dgillz,

How does the SP know which rows to update - are you passing a Crystal variable to the SP? Can you update multiple rows?

 

[dgillz]

The fact that you use the SP in a subreport means that you only update the records you want to update -- i.e. the records selected in the main report. The subreport does not even have to return any data, it just updates the database.

I can send you an example if you wish.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com