Sage 100

Customer wants a Crystal Report where they key in a list of inventory item codes and a date range. From there they'd like to know the total sales for the range by item. That part I've got figured out. (Using the AR Invoice History Header/Detail) What's throwing me is they'd like to have any of the items that have NO sales for that period listed as zero. Since I don't have a record for those items I am having a hard time getting my arms around an easy way to display them. Example: Item ABC DEF GHI JKL If only ABC has a total of $10 of sales then how do I show the other three with zero? Do I need to throw CI_ITEM in there? (I'm thinking yes) and use that to pull up every match somehow?

There's a way but the one who knows it isn't here right now. I'll check with him tomorrow and email what I find to you.

@WayneSchulz, report on both the item master and the history tables using a left outer join so that all items are included. Those without activity will have a null value for a total. You will need to do some logic to replace the null with a zero value.

Yes but that will be slow. There is a better way - too bad I can't recall it.... We had to do an exception report for one of our clients similar to Wayne's. I'll find it...

@ThereseLogeais not is he uses MS Access as the underlying data source tied in with PTQ's linked to the underlying data files.

True @ShawnSlavin !!

The left out join query is the only way I know of to get a list of all items with the respective sales, including $0 sales.

Did someone say PTQ's? Oh the sweet sound of that music....

I was just going to do a call out to our PTQ expert @JeffSchwenk .

I found what we did and it was a little different. The customer wanted to only see items that were NOT sold. We used a sub-report in the report header to create a shared array of all items sold. In the main report, we used the item masterfile and suppressed the detail if the item appeared in the shared array. Probably not going to work for Wayne's needs but it was still kind of a cool report.

I don't think I can be described as an expert, probably more of an advocate for ways to improve the bottom line! With the PTQ, you could then create a select query would would do all of the heavy lifting for Crystal.

Thanks. I am forever perplexed why Sage does not include better sales reports. This type of report is pretty generic. Getting meaningful date range (not period) driven reports from IM in Sage 100 can be tricky without writing a custom report.

Agree. Have client looking at this software. http://www.afsi.com/ $50K to install, 5K per month licensing. Good for ONE MAS company........ I personally think we could do something with Access for a LOT less, but they seem to have swallowed the sales pitch hook, line and sinker. Vendor is promising write back capabilities to MAS. Where have I heard that before?

Note: Some of the syntax may be wrong as I am doing this off the top of my head. I would create the main report with just the CI_Item table. The item parameter will select the items. I would then add a subreport that has the AR History files. Pass in the date range (probably need to create two formulas, BegDate = Minimum({?DateRange}), EndDate = Maximum({?DateRange}), as subreports do not seem to accept range parameters when passed) to the subreport without linking them to a field. Also link the ItemCode field for the subreport. In the subreport, edit the record selection criteria by adding ""and {AR_InvoiceHistoryHeader.InvoiceDate} in {?@Pm-BegDate} to {?@Pm-EndDate}"". You may also need to use some shared variables to get your totals.

I would take the route @WilliamSchlageter describes. Done similar things myself with good results.

FYI - A trick when using left outere joins is to add to the select criteria when the value in the lookup table is null. I found this trick on Google about a year ago and it has worked a few times instead of doing a sub report. I still mostly use Sub reports.

@WilliamSchlageter I would think that using a sub report against an A/R history of any size for every row of the main report wouldn't complete in my life time, although I am rather old as Jeff notes.

I like your description @DanBurleson - ""wouldn't complete in my lifetime."" I know I've had that feeling... I can't really explain why but I have had subreports speed up report processing tremendously for me. I think it may be the way Crystal caches the left-outer records but I could be wrong. The first time I used them was as a last resort and I was stunned by the speed. Of course, I had to run it both ways to be sure the data was all correct and sure enough, it was. I've since used subreports in other very slow-running reports with the same success. And yes, I know, I could be using PTQ's.....

Just because you are old doesn't mean we are going to put you down anytime soon..............

Um, are you referring to @DanBurleson or me, @JeffSchwenk ??

Wellllll, I was assuring @DanBurleson that there was a lot of living left for him, but if the shoe fits.................

this seems like something sage intelligence should be able to do. I am disappointed that there are not a bunch of reports in this solution. My clients wants to buy a solution such as intelligence and just hit the ground running with the various insights of the business. But with Sage Intelligence you buy and you spend 3 months building the various reports. Many are probably the same reports everyone would want. It would be like if Sage started selling Sage Transportation- it is a car - you get the various components - engine, wheels, steering wheel and seat belt. But you get to put the car together and maybe in a few months it will be driveable.