Report Writer One-to-many 1

[Borvik]

I'm trying to create a custom report using the Report Writer.
I need to create a report that reports the following:
Item Number (easy enough - main table)
Item Description (same)
Qty Available (calculated - IV00102)
Current Cost (main table)
Extended Cost (calculated - previous two fields)
YTD Sales (QTYSOLD - IV00102)
Last Purchase Date (LSTORDDATE from IV00102)
---This is where it gets tricky---
Last Sold Date (IV30300 - DOCDATE - many relationship)
Avg. sales for the last three months (Calculation on IV30102 - many relationship)

Does anybody have any idea how to accomplish those last two fields? How about how to do a calculation on the Date fields that show "N/F" when the date is reported as "0/00/0000"?

Thanks.

 

[Luvsql]

The first thing I would recommend is to not use Report Writer. You are very restricted to what you can/cannot join on. YOu could create a sql view with the IV30300 table, group by ITEMNMBR, with Max(DOCDATE), to get the last sold date. THen when you link in Crystal, it's a one-to-one.

If you have to use Report Writer, create a header and footer on the Item Number from the IV00101. REstrict the report to just use one location only (or use the record type =). Create link to the IV30300 table by opening the Item Master, then realationships, then new and link on Item Number only.

In the layout, place the Doc Date into the header and change the type to Maximum. Place the info from the IV00102 in the header.

 

[Borvik]

Thanks Luvsql!

Unfortunately we don't have Crystal Reports =(.

I'm trying to do as you suggest, but am having some difficutly understanding it.

Does this sound right?

First create an extra header AND footer that has "Print when field changes" set to Item Master > Item Number.

Create a relationship between Item Master(IV_Item_MSTR) and Inventory Transaction Amounts History.
What Secondary Table Key should I be using?
I used IV_TRX_HIST_LINE_Key2
And only fill in Item Number.

Add the table to the report based from the Item Master table.

Create a report restriction on Inventory Transaction Amounts History where Document Type = (some integer constant).

In layout do something like this:

---------------------------------------
Report Header
---------------------------------------
H1                          ITAH.DocDate[Maximum]
---------------------------------------
Body
---------------------------------------
F1
---------------------------------------
Report Footer
---------------------------------------

And all of this:
Qty Available (calculated - IV00102)
Current Cost (main table)
Extended Cost (calculated - previous two fields)
YTD Sales (QTYSOLD - IV00102)
Last Purchase Date (LSTORDDATE from IV00102)

should be in H1 as well?


When trying what you suggested I started getting items that repeated many, many times - with same & different DocDates (which should only show one - Maximum).

And I noticed when reading some help on Report Writer, that it only allows one one-many relationship per report table. Shoot! I'll need two - looks like I'll have to find an alternative.

 

[Luvsql]

You can remove the body, as you don't need all that detail. Restrict the report from IV00102 Record Type=1, which is the summary of all sites.

 

[barbola]

You're not restricted to Crystal (I never use it - too much frustration). I prefer using either MS Query to get what I need into Excel or Access (carefully).



Thanks!
Barb E.

 

[Luvsql]

That's the first time I've heard that crystal is more frustrating than Access!

 

[stef315]

You can also use SQL Reporting Services which comes with SQL Server if you own that.