| Author |
Message |
jester1470
Newbie
Joined: 25 Mar 2012
Location: United Kingdom
Online Status: Offline
Posts: 6
|
  
Topic: Comparing Data formula
Posted: 02 Aug 2012 at 3:27am |
|
Apologies if this is in the wrong section. I am not a hugely experienced Crystal User and have hit a wall.
As part of our systems we run work orders through Crystal, does anyone know if it's possible to create a report/formula that will allow me to compare two work orders. Each work order can have hundreds of items on it and we would like a way to put in two work order numbers and then see what the difference between them. I'm not even sure if this is possible, we're using CXrystal Reports 2008, sorry I'm not being more specific, I'm not sure what other information you might require, thanks for looking and hope someone has some ideas.
Thanks
Stuart
|
 IP Logged |
|
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 02 Aug 2012 at 8:50am |
Are there specific fields you would use to compare the two? How good are your SQL skills? I would recommend creating a SQL query that will do the comparison in the database and then use that query in a Command in Crystal. The query should contain ALL of the fields that you want to use in the report. You would use the Command instead of selecting tables in Crystal. Another possible option would be to add the table(s) that contain your workorder data to the report twice. When you add a table to your report that is already included in the report, Crystal will ask if you want to "Alias" it. At that point Crystal will add the table with "_1" at the end of the table name. You would then set the filter so that the original table is filtered by the first workorder number and the aliased table is filtered by the second workorder number. -Dell -Dell
|
|
|
| IP Logged |
|
jester1470
Newbie
Joined: 25 Mar 2012
Location: United Kingdom
Online Status: Offline
Posts: 6
|
Posted: 02 Aug 2012 at 9:56pm |
|
Thanks Hilfy, much appreciated, i've managed to set u[p the dual fields using the method of adding the Aliased link, which i didn't think i could do so thats much appreciated, is there an easy way to show what the two differences between the works order will be, ie highlighting something if it's on one and not the other and vice versa, sorry top be such a pain, I'm still learning how this all works. The only things i need to highlight are the part numbers within the work order, but that probably doesnt make too much sense if you dont know how we set things out.
Another question is how is best to link in the duplicate tables, do you have 2 sets of links together exactly the same for the 1st set and then the second with maybe only the W/O linked in or is there a senseible way of doing it, sorry for asking so much, it's being an interesting learning experience.
thanks
Stuart
Edited by jester1470 - 02 Aug 2012 at 10:29pm
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 03 Aug 2012 at 7:33am |
I would do a Full Outer join between the two work order tables based on Part Number - if Crystal won't let you do a full outer join, let me know and I'll give you a formula for the Select Expert that will handle it. This will show all of the records in both tables, including those that are in either one but not the other. I would then set up a formula for either grouping or sorting by part number. It would look something like this: if IsNull({work_order.part_number}) then {work_order_1.part_number} else {work_order.part_number} This will give you all of the part numbers in order, regardless of the table they're from. If you show the part numbers in two columns, it should be fairly obvious which are different because they'll be blank in the other column. If you want to show just one list of all of the parts numbers regardless of the workorder, use the formula I gave you for the sort. Once you put it on the report, right-click on it, Format Object and go to the Border tab. Under color, check the Background checkbox and click on the formula button to the right of the color drop-down. You'll then enter a formula that looks something like this: if IsNull({work_order.part_number}) then crYellow else if IsNull({work_order_1.part_number}) then crOrange else crNoColor This will set the color to yellow when the part number is in the first work order but not the second, orange if it's in the second and not the first, and leave the color blank if it's in both. -Dell
|
|
|
| IP Logged |
|
jester1470
Newbie
Joined: 25 Mar 2012
Location: United Kingdom
Online Status: Offline
Posts: 6
|
Posted: 06 Aug 2012 at 12:59am |
|
Hi Hilfy, i'm finding this a lot tougher than i expected I would. I cannot get a formula/parameter to work that will allow me to add in two work order numbers to start will, if i try that all I'm getting is blank sheet, I'm not even trying the comparison at the moment, all i want is the two equipment titles and Assembly No that are assigned to each work order and i can't even get that to work I have tried various ways of linking in the tables and none of them seem to give me anything. I'm not sure if it's partially down to how our system is setup but it really doesnt like me trying to link these tables together and getting them to work.
I've never tried anything this complicated before and it seems to be a tough one, tbh i hoped it wouldnt be too tough and i could have used one of our existing W/O reports and add in another to go alongside for comparison, but it doesnt seem to be as simple as that.
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 06 Aug 2012 at 3:44am |
Not knowing your table structure, I'm going to describe the basic logic here - you'll have to translate to use your exact tables and fields. I'm going to assume that you have two tables for a work-order - a Master and a Detail. First I would try adding just the table that has base work-order information - so you'll have two copies of just the work-order master table. DO NOT LINK THEM! Add two parameters to the report - WorkOrder1 and WorkOrder2. Filter on something like this: {work_order_master.WorkOrderID} = {?WorkOrder1} and {work_order_master_1.WorkOrderID} = {?WorkOrder2} Make sure that you can get your title and assembly number to appear on the report. If that works (and it should!) add a copy of the detail table for each master table. Link them to the corresponding master table only - you'll end up with two unconnected sets of tables. In the Select Expert, edit the selection formula (you can't do this by just using functionality that the Expert provides.) You'll add something like this: and (IsNull({work_order_detail.WorkOrderID}) OR IsNull({work_order_detail_1.WorkOrderID}) OR {work_order_detail.WorkOrderID} = {work_order_detail_1.WorkOrderID}) Note the parentheses I put in red - these are required and the filter won't work without them! Group on the formula that I included in one of my previous posts - you should be able to see your data. The trick to this is going to be having the two separate sets of linked tables that have joins internally but the two sets are not linked together. -Dell
|
|
|
| IP Logged |
|
jester1470
Newbie
Joined: 25 Mar 2012
Location: United Kingdom
Online Status: Offline
Posts: 6
|
Posted: 06 Aug 2012 at 9:36pm |
|
Hi Hilfy, thanks again, I get the actual logic, but when i put them it I can't get it to work, when i start adding other tables, even the ones that should link in no problem I put in just he top tables and it's fine, when I try to link in the parts table it works for one of the 2 W/O numbers OK but crashes when it does the second saying "Failed to retrieve data from database" "Cannot determine the queries necessary for this report", this is despite them both being set up in exactly the same way. as far as i can see.
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 07 Aug 2012 at 3:03am |
Ok, it looks like Crystal is not going to cooperate. How are you SQL skills? Or can you have a view created in the database that includes all of the data you need for one work order? -Dell
|
|
|
| IP Logged |
|
jester1470
Newbie
Joined: 25 Mar 2012
Location: United Kingdom
Online Status: Offline
Posts: 6
|
Posted: 07 Aug 2012 at 3:16am |
Originally posted by hilfy
Ok, it looks like Crystal is not going to cooperate. How are you SQL skills? Or can you have a view created in the database that includes all of the data you need for one work order? -Dell I don't think I can get a view created as the works order info woul;d be quite complicated and my SQL skills are pretty much nonexistent so i'm guessing i might have to put this one down as not possible with things the way they are set up, i can't really understand why the report isn't working but i don;t really havwe the in depth skills to realise why, sadly. Thanks and if you have any other ideas I'd be appreciative. Thanks Stuart
|
| IP Logged |
|
jester1470
Newbie
Joined: 25 Mar 2012
Location: United Kingdom
Online Status: Offline
Posts: 6
|
Posted: 08 Aug 2012 at 5:22am |
|
I've been playing about with this and managed to realise I can get all the information from one table so i dont need to link in other tables which has made it easier, now the problem i have is because the 2 tables arent linked I'm gewtting duplicates, what I'm not having trouble with is getting the 2 work orders to present side by side, at the moment, all i get it one which is correct and another which is correct x the number of items in the first if that makes sense, I've tried groups but I can't get them to present the second colum properly, all it ever does is present the top number which means i get the correctinfo in one column and the same part in the other, which is because it is only ever showing me the first part of the list against each one.
not sure if I'm explaining this very well, does anyone know how i can get the second colum to present ptoperly and then the best way to highlight the same parts in both colums, i tried a subreport but that doesnt seem to work well either and would limit the comparison.
Thanks again
Stuart
|
| IP Logged |
|
|
|
