Hi Guys,

I want to a make a custom report similar to the Sales (Detail) report that's standard on OE. I simply want to show the line items for each invoiced order that it shows. I have the report how I want it and it works well EXCEPT that I'm not sure how to select the records like the standard report does.

It seems that it only selects the invoiced orders from the current open month (all closed months are ignored). What field in what table tells me what the open month is so I can make a selection formula to do such a thing.

Please let me know if I haven't been clear enough on my request.

thanks!
Lucas

Hi Lucas

There is no standard OE report called Sales Report (Detail). What's the exact name of the report you are referring to?

Also, what are you using to create the custom report? If it is Crystal Reports, what tables have you selected?

How does OE know that a month is closed or open - that's a GL concept? Or are you thinking of some other meaning for open and closed?

Regards

Steve

Steve,

In OE, select Reports/Transaction Reports/Sales
This is the report I'm talking about. When the report comes up, it says "Adagio Order Entry - Sales(Detail)". I'm not sure what else (besides Sales Report) I'm supposed to call that?

Since this report is found in OE, I logically assumed the data comes from OE, so my Crystal Reports custom report that I'm making is pulling from the OE Header and Details tables. Now, this report only shows the invoiced orders from the current month. Maybe I'm not using the terms "Open" and "Closed" properly, but when we do our month-end ("Closing" the month), the invoiced orders in that month cease to appear on the report.

So, I guess to rephrase, am I pulling the data from the right tables and is there any easy way to select for current month items only?

I suspect you are using "OE Orders and Invoices Header and Detail files. This is orders and invoices in the current status. Order/Invoices/Credits get copied to history during the dayend when certain criteria is meet. Try working with OE Order History Header and Detail files. Use a date range or other criteria to select the data you want.

David

Edit the company profile. On the system defaults tab is a setting to "purge from current after days. Increase the number there so you can report on more days of data from the current file.

I suspect there is a misunderstanding in what I'm trying to do.

The time frame of the existing "Sales Report" is just fine. I simply want to replicate it on a custom report. There are other things about that report that we do not like, hence the reason for the custom report.

Using a date range on the custom report is insufficient. Sometimes our month-end goes over by a couple days and I need the date range to mimic the range of the invoices on the current month. A static date range will pick up invoices from different months.

On my custom report, I am indeed using the "OE Orders and Invoices Header and Detail" but it shows data for the current year, not the current month. There is one table I have found that shows on the current month. It's called "Sales Report Summary", but none of the fields are indexed so I cannot use it in Crystal Reports.

Hi Lucas

Sorry for my question about the name of the report. It turns out that the Sales report only appears on the menu when you have checked off the "Analyze sales data" box in Company Profile, and I was looking at a set of data where it was unchecked.

That particular report uses a temporary data table (OESALES.xxx) that gets cleared after you print the report (and say yes to the question "Do you want to clear the sales file"). So the report actually is printing all details from the date it was last run, which has nothing to do with whether a month is open or closed.

To answer your question about selecting date ranges in Crystal reports, this is done by creating a pair of parameter fields, one called "Beginning Date" and the other called "Ending Date". Then in the Edit Selection Formula, you add a filter to test for the invoice date >= Beginning Date and invoice date <= Ending Date (the syntax is not correct but you should get the idea). When running the report, it will prompt for the two dates and select the transactions accordingly.

Steve