Personalization, Customization and Productivity Tools

[Darkjedi]

Since the database changed from IM9 Item Sales Detail Whse to IM_ItemWhseHistrybyPeriod, I'm have a problem with my formulas. There used to be fields for each period in the IM_9 database but now there is one field FiscalCalPeriod. What I need is a formula that will give me 12 months of data of (Quantity Sold, Dollars Sold, Cost of Good Sold) on one detail line.

What I was thinking was current period -12periods = Quantity Sold and then another formula but -11period and so on.

                   Sold                      Sold                     Sold                     Sold                    Sold                   

Year  12 months ago    11 months ago    10 months ago   9 months ago   to current

2010            2                            5                            8                            6                         12

Thanks

Mark

[fastasset]

You could try one of the following;

1. Use a Crosstab report with the rows as  the year / part & the columns as the period. This is the easiest to create but format and functionality may be limited.

2. Customize the standard "Inventory Sale History Report" - (I assume that you are on v4.4) . When you run the report the data is processed into a worktable which allocates the data into the 12 month columns. An added added benefit for this approach is that the selection is processed into a work table which can improve the performance of the report.

3. Custom report from scratch. Group on the item code, create a conditional formula for each month (ie if period = 1 then FiscalCalPeriod else 0 ) & then sum this formulas in the item group footer.

[Darkjedi]

Thanks, that what I needed. Customizing the Inventory Sale History Report is a little over my head. I've never worked with the premade reports that come with MAS. I used option 3. I do have a slight problem. I have 4 databases: CI_Item, IM_ItemWhseHistoryByPeriod, IM_ItemVendor, IM_ItemWarehouse that I use. I have three databases linked to CI_Item with Inner Join, Not Endorced =. I ran a short report and noticed that a item wasn't showing up so after some more research the Item is not in IM_ItemWhseHistoryByPeriod database. So it doesn't show up at all. So I changed the link from CI_Item to IM_ItemWhseHistoryByPeriod to Left Outer Join, Not Enforced,= and then it showed up. But then when I ran it again with a different vendor the report froze. Any Ideas?

Thanks,

Mark

[Darkjedi]

To sum up what's going on from my last posting. The report is not showing new items with no history. (I assume because I'm using IM_ItemWhseHistoryByPeriod) I need it to show every item number that meets my criteria.

Thanks

[Darkjedi · Accepted solution]

I got it to work using left outer join, But I had to rebuild the report before that would work unfortunately.