Subreport Help

[bstafford21]

I am using CR 9 and I need to create a new report from 2 other reports.

I have created a Sales Order Report and a Work Order Report.

But Production wants a Report that has both Open Sales Orders and Open Work Orders. The way the database is set up in magic, I am unable to create 1 report that has both and I do not understand how to create a subreport for each and make a new report to add both together.#

Can someone help me understand this please.

Thanks

Bill

 

[lbass]

Do you have some table that relates both sales order and work order to an account or something? I'm just guessing that you want the sales and work orders related to something like that, although your subreports don't in fact have to be linked to the main report, and you could just place them in the report header.

But let's say you want to relate the orders to account numbers. Create the container report using the table containing account number, and insert a group on account number. Insert a second group header section GH#1_b. Insert your subreports by going to insert->choose a report->select your completed reports, one at a time. After selecting the reports you could go to the link tab while in the insert subreport screen. At the top left, choose {table.acctno} from the main report as the link field, and then in the bottom, again choose {table.acctno} as the field to link on from the subreport. Place one subreport in GH#1_a, and one in Gh#1_b.

Another option would be to use one "subreport" as the main report, and then place the other report in the report header or report footer.

If you want to do calculations on numbers from the subreports within the main report, you will need to use shared variables. Let us know whether that is the case, and provide a sample calculation you would like to do.

-LB

 

[bstafford21]

Hi

What I would like to do is show all OPEN SALES ORDERS and pull in the WO when it matches the Part that it goes to.

The Part No used is in both WO & SO Table, but I have been unable to get the relations to work in the links. The idea you have using Account No's is probably the same as me using Part No's.

I am trying to understand what you have told me here, but I am unfamilar with some of it, so it will take some study time for this. If you have any other ideas on how to link these relations let me know. Also I do not have any calculations needed for this, just to combine the 2 tables from Magic somehow and get the correct results.

 

[lbass]

Is your Open Sales Order report grouped by part number? Then you would insert the Work Order subreport into the part number group header or footer, and link the reports by using the part number fields from each.

If this isn't what you are looking for, it would help to know the structure of each report.

-LB

 

[bstafford21]

The OPEN SALES ORDER REPORT is not grouped at this time, due to the fact that they want the report to be sorted by date.

Would this matter for the report to have a sub inserted?

Thanks

Bill

 

[bstafford21]

Hi,

Forgot to add the structure of each report, The WO & SO reports have basically the same data structure which includes the following:

Due Date
Sales Order
Part No.
Customer No.
Box Qty
Inv Qty
Customer Order Qty

The WO is similar but replace WO with SO of course.

Thanks

 

[lbass]

I was asking about report structure. Are there no groups? what are the groups and/or sort order in each report? What are the record selection formulas in each?

If you haven't inserted any groups, then you need to figure out how these reports will be used, so that you can determine how best to group the records.

-LB

 

[bstafford21]

The OPEN SO Report is Grouped by Due Date and the WO report does not have any Groups, only sorted by Part No.

The best way for the WO report is by Part No because the SO & WO dates will not be the same.

As for the formulas, there is for WO Reports: {WO.No_of_Ops_Completed} < 1
and the SO Reports:{Part.Item Type} < "2" and
{SO_Items.Qty_Del} < 1.00

Bill

 

[lbass]

Assuming that the Open SO report is your main report, insert a group on Part Number (let's call it {SO.PartNo}). Then insert your WO report by going to insert->choose a report->your WO Report. Then click on the link tab and link the subreport to the main report by choosing {SO.PartNo} for the main report linking field and {WO.PartNo} for the subreport linking field. Then place the subreport in the part number group header or footer.

This will give you a display of all WO records for the SO part numbers. If the SO part numbers repeat on different dates, then the same WO data will display again. If you want a display of WO part information only once per part number, then in the main report, go to report->change group expert and use the arrow keys to move the part group above the date group.

You might want to link on additional fields, e.g., customer No, to narrow the display, but I don't know enough about the intended use to make a recommendation. I would think you would have some ID number that was unique to a work order and a sales order that would link them based on some kind of transaction field, but I don't see anything like that in your field list.

-LB

 

[bstafford21]

Hi,

Thanks lbass for the help and I have been able to complete this report as you described.

This is my first attemp at using subreports and it has given me the information I need. It allows me to review Open Sales Orders and what Open Work Orders there are for those parts on the same report, which can save me time and less confusion.

Is there any way to get crystal to link the dta files to correctly pull them into one report? I have tried a number of variations on the join options but nothing seems to pull in the data. For now I will use the subreport until I get another suggestion.

Thanks again for the help and showing me the way to these subreports.

Bill

 

[lbass]

There is probably a way to do it without subreports, but you would have to describe very specifically how you want the sales and work orders to relate to each other, and also what criteria you would want to use to limit data from each table. For example, you might decide you want to show the report grouped on customer, with all Open Sales Orders with date greater than Jan. 1, and that you want all work orders related to these Sales Orders.

It never became clear in the current post what fields could be used to specify the relationship, i.e., you could probably link on customer and part number, but there are probably multiple work orders per customer and part number, so there would need to be some way of identifying how that work order related specified to a sales orders. But I'm making a lot of assumptions about the way your business operates. You would have to spell these out.

-LB

 

[bstafford21]

I will try and explain what I have to work with:

WO Data Contains: (Only 1 WO per part)
1.Due Date
2.Part No.
3.WO No.
4.WO Qty

SO Data Contains: (More than 1 SO per Part)
1.Due Date
2.Part No.
3.SO No.
4.Order Qty

Material Data Contains: (Only 1 per Part)
1.Material Part No.
2.Material Qty Req
3.Material Qty Issued

I would like to see that each WO would show all SO for that part regardless of Due dates so I could decide to make possibly 3 SO Qty together to improve efficiency, like the MRP system.

Possibly the easiest way would be to show a screen shot of the data I can pull and link. I am not sure if I could describe it well enough. Could I e-mail a screen shot to you?

All 3 dta files from Magic have other options to choose from and that can help me decide how to write a formula to show only SO that have Delivered Qty <1, this shows me all Open Sales Orders.

The otption from WO that have WO Qty Stored <1 shows me all Open WO's.

Thanks for the help, other info I missed telling you let me know.

Bill

 

[lbass]

Could there be work orders without open sales orders? If you want to show work orders even if there are no sales orders, create a left join FROM WO TO SO on part number, and then use NO selection criteria on the SO table. You would also create a link (equal join) from {WO.partno} to {Material.MaterialPartNo}. Then to limit the display (not the underlying records) to records you want in SO, you would use a formula like:

if isnull({SO.DelivQty}) or
{SO.DelivQty} < 1 then < add the relevant field here, e.g., {SO.amount}> else 0

This would allow all the SO records to come into the report, so that all WO records would be returned, without using all SO records in your calculation.

If there is always an open sales order if there is a work order, then use an equal join between those two tables, and go ahead and add selection criteria on the SO table.

You might want to do some testing on this, verifying as you go. If subreports are working for you without slowing your report too much, you could also just stick with that approach.

-LB

 

[bstafford21]

I will start with the Open SO Report as the Primary because I want to list all SO to show me the demand, the time period is not important right now.

Next I want to bring in the Open WO's and place them with the correct Part No that has a SO.

There will always be a SO with a WO. This should be 99.9 % of the time, very rare to not have a SO on a WO.

I then want to put this in Date Order to show me every day what Sales Orders are due and what WO is on that Part.

This will allow us to catch any Parts we missed in the morning shift if they are still running that part on the machine and shipping are looking for that part.

I am not sure about the formula you have shown me but I will test these ideas and let you know.

Thanks for the help.

Bill

 

[bstafford21]

I have created a Primary Open Sales Order Report and the selection criteria formula used:

{SO_Items.Qty_Del} < 1.00

I now have 569 Open Sales Orders, which is correct as well.

Now I have brought in WO data and used WO.WO_No and the slection criteria formula used:

{WO.Qty_Stored} = 0.00

I now only have 173 Open Sales Orders showing.

The Table Database Links are all INNER JOIN & =

Where are the other Orders? What should I change?

Bill

 

[lbass]

You've reversed your requirements, but okay.

One of the selection criteria is limiting the results, or you don't have matching part numbers in both tables. First I would do a little test to understand what your records are. Start a new report, just with the WO table, add the {WO.partno} as detail field and insert a distinctcount on it. Note the result. Then add the SO table, link on part number using an equal join, and then note the distinctcount on {WO.partno}. If it is reduced, change the link FROM WO to SO to a left join. If the correct WO result returns, then you need a left join from WO to SO. Now, with the left join in place, insert a distinctcount on {SO.partno} and note the results.

Then do the same exercise, only starting with the SO table. If the distinctcount of {SO.partno} is the same as that with the other report after using the left join, then use the left join from WO to SO. You can still organize your report around SO fields if you wish, even though you are driving the records by the WO table.

Please report back with results and then we can tackle the issue of adding selection criteria.

-LB

 

[bstafford21]

Will try this tomorrow, but I think I may have done this before by changing to Left Join.

Also I am not sure what you mean by distinctcount?

Thanks for the help and will report back tomorrow.

 

[lbass]

Distinctcount is one of the summary options. Right click on your {WO.partno} field in the detail section->insert grand total->distinctcount. This will count each distinct part number only once.

-LB

 

[bstafford21]

Hi,

Thanks for the explanation on the distinctcount, this has helped me.

Also have done a trial and testing of the Open SO & WO report.

1. Started with the WO Order Report and made a formula to only show {WO.Qty_Stored} < 1.00 (ALL OPEN WO's Not completed)

2. Put in SO Order Report and made a formula to only show {SO_Items.Qty_Del} < 1.00 (ALL OPEN SO's Not Delivered Fully)

My first problem I encountered was when the SO information tried to input it would not show all the data and was incorrect. When I removed the WO data input it works fine and shows all the Open SO's as required.

I then brought back the WO data and left out the formula I used to tell me {WO.Qty_Stored} < 1.00

The report then worked showing me all OPEN SO's and all the correct OPEN WO's, the data has been validated and is correct. I also had to change the Link between WO & SO to be LEFT OUTER JOIN.

Now I will continue to add the items I need as well which includes the DATE DUE and other relevant info. I will do these 1 by 1 so to see any problems easier.

Thanks for your help IBASS and if any other questions I will ask.

Cheerio
Bill
Kyoshin Man
Notts, UK