I need to run a report that states the following:
Date Range between 2 to 12 months
Lists Customer number and name
Lists what products the customer has purchased in this date range
and the last unit price he paid for these items.
I cannot find a report to give me the actual price per unit of the item the customer purchased.
Is there any way to do this??

Hi Gail,

Which database are you wishing to generate the report from. Order Entry? Sales Analysis?

If there isn't a standard built-in report that gives you this information, it seems pretty reasonable to use either GridView or Crystal Reports for Adagio to generate this, depending on whether the information is to be a presentation-quality report (use Crystal) or for speedy access to information (GridView).

Whichever reporting tool you use, base the report on the sales header and detail files, which will give you all the information you requested. Include a filter for the date range, listing only sales for that time period. Summarize the report by customer and item.

If you are using Crystal Reports, a trick is to sort the details by date (oldest to newest) and insert the Unit Price into the sub-total by item section. This will print the Unit Price for the last detail, which will guaranteed to be the newest invoice for each item, because the details are sorted by date.

I am trying to do this on Gridview but with no success. Can you please go thru the steps to see if I am picking the right things.
Thank you

Hi Gail

GridView is not the answer to tech support's question above.

The question is:
Which database are you wishing to generate the report from. Order Entry? Sales Analysis?

Most likely GridView is not the right tool for such a report. Crystal Reports is. GridView is simple and easy to use, but your report requirements go beyond simple.

If you are using Sales Analysis, which has an easy to use report write (called "online inquiry"), then you can get most of the way towards creating your report right in that module.

Steve

Thanks for your reply.
I am trying to use Sales analysis.
I have used the online inquiry and the closest I can come to doing this report is to get an average price.
Why does this have to be such a complicated process. All I want is a report to show what the last price a customer paid for his products? As for using Crystal Reports I am not good with this program at all.
Thanks for trying to help. I will just do it all manually.

Hi Gail

I can understand your frustration. Believe it or not, "last price paid" is coming from a different place than all of the other fields you want.

You can't blame Softrak just because you are not good at Crystal Reports, if it turns out that Crystal Reports is the right tool. You don't have to be good at Crystal; you just have to pay someone who is. If everybody were good at everything, what would they need me for?!?

Steve

Hi Gail,

The reason this seems complicated is because the amount of the last unit price for an item for a customer is not stored in the database. Thus whatever report you create, this value needs to be calculated manually. It isn't as easy as it sounds.

Essentially, you are requiring a process to find all the sales for a particular customer in a date range, and the unit price for the most recent sale of each item. It is not important how many times the customer purchased the item, just that it was purchased at least once in the date range.

The Online Inquiry can show all the sales for a customer, but there are no selection filters for only the most recent detail of each item. This is why you are getting an 'average' price rather than the 'most recent' price.

Because the report you want to generate requires determining how many times an item has been sold to the same customer in a date range, you need a report tool that can filter out all the item sales except for the most recent one. Or a way to determine which item sale was the most recent and read the Unit Price from that.

This would be a fairly straight-forward thing to do with Crystal Reports, and no so straight-forward (if not impossible) to do with GridView or the Online Inquiry. The reason this is hard to do in GridView is that there are no formulas or functions to look at other records in the grid to determine which item sale was the most recent when there are more than one. And the available statistical operations in GridView are Total, Average, Min and Max. There is no First or Last statistical operation.

Thank you for your help.
Can you please remove my questions from the forum as I cannot figure out how this works.

Gail

Hi Gail,

No need to remove the thread - others may be looking for the same kind of report as this. We have discussed tracking last price paid by customer, but it's not there at the moment. (Also, this is potentially a very large database - think #customers x #items).