|
Writing IFS Quick Reports - An Introduction
by: Camden Spiller April 13, 2001 summary Quick reports are a great feature that the IFS Applications provide for easy customized access to your data. Copyright notice: All reader-written material on ifsconnect.com is the property and responsibility of its author; for reprint rights, please contact the author directly. Since quick reports are composed of pure SQL (Structured Query Language), chances are you may already possess the skills required to start building your first report. This article has a lot of references to the IFS Applications, so it might be an easier read if you’re sitting in front of the applications and you can “follow along”. For the sake of this article we are going to create a report that will show us all the customer order lines from the site "NY". We would like these fields to show on the report: Order Number, Customer Name, Line Number, Release Number, Line State (Invoiced/Closed, Cancelled etc.), Part Number, Quantity on order and last but not least: Part Product Family. And we only want to show the order lines that are from the "NY" site. In the Distribution application’s Customer Orders > Customer Orders > Overview - Customer Order Lines screen we can see that all of the fields we want to include on our report except the inventory part product family are already there. Now start tracking down the field names for the report. Activate the debugger by holding down Crtl and the "0" key on the numeric keypad while wiggling your nose and right click the grey background of the IFS windows application (it may also work if you don't wiggle your nose... tests have been inconclusive so far). This will bring up the debugger settings window where you can click the "Start" to activate the debugger console. Now that the debugger is activated you can right click on the Overview - Customer Order Lines screen and click on properties and you will see a new tab labeled "Debug". This tab will give us the name of the view containing the fields displayed on the Overview screen. In this case the view is named "IFSAPP.CUSTOMER_ORDER_JOIN". With this information we can goahead and open up the IFS Deployment Administrator application and begin comoposing the SQL for the report. Open up Installation > SQL Query Tool and you find a list of each and every data view that contains IFS related data. When you click on one of the views all of it's columns will be visable in the columns window in the center of the screen. You can click on the view again and drag it into the SQL statement section of the screen to create a SQL select statement that will look like similar to this: select * from ifsapp.customer_order_join When you click the "Execute" button you should see the results that should be pretty similar to what you've seen in the Overview - Customer Order Lines. Since you don't need all the fields shown here you can select the fields you do need from the columns list and drag them into the SQL Statement area. When you've selected all the fields that you need from this view your SQL will look similar to this: select customer_name, line_no, line_state, order_no, part_no, qty_on_order, rel_no from ifsapp.customer_order_join Now the only field remaining to be added is the Part Product Family field. We find this field in Inventory > Inventory Part > Overview - Inventory Parts, so once again we turn on the debugger, right click on the overview, click on properties, go to the Debug tab and this time we discover that the view we are looking for is IFSAPP.INVENTORY_PART. Because this field is in a different view we will have to join this two views together to get the records we want. This is where the SQL get's a little more complex. When we go to the SQL Query Tool and execute the code "select * from ifsapp.inventory_part" you'll see that the first two fields are (contract and part_no) are also present in the IFSPP.CUSTOMER_ORDER_JOIN. These are the fields we will use to join on. Here's what the SQL join should like: select coj.customer_name, coj.line_no, coj.line_state, coj.order_no, coj.part_no, coj.qty_on_order, coj.rel_no, ip.part_product_family from ifsapp.customer_order_join coj, ifsapp.inventory_part ip where coj.part_no = ip.part_no and coj.contract = ip.contract The only thing remaining to do now is to add the filter that will only show us records with the site of "NY". select coj.customer_name, coj.line_no, coj.line_state, coj.order_no, coj.part_no, coj.qty_on_order, coj.rel_no, ip.part_product_family from ifsapp.customer_order_join coj, ifsapp.inventory_part ip where coj.part_no = ip.part_no and coj.contract = ip.contract and coj.contract = 'NY' One thing to notice about the site field is that it is actually named "contract". Most of the fields in the IFS views are similar to their descriptions on the overview screen, but as this field demonstrates there are exceptions. Now that the SQL for our report is complete it's time to put it into a quick report. Quick reports themselves are organized into Report Categories. You view/edit these categories from Foundation1 Administration > Quick Report > Report Category. Aside from organizational grouping of reports these categories provide security. You can populate and add new records to the Report Category screen just like you would from any other similar IFS screen. To turn your SQL code into a quick report navigate to Foundation1 Administration > Quick Report > Quick Report from the Deployment Administrator application and add a new record. The Category Description field can contain a name of a category you've already created or "Default" (click in the field and press the F8 key for a list of options). The value you put in the next field (Description) will be used as the name of the quick report. The type field should be set to "SQL Statement". The next field is where you will put the SQL code. Double click in the SQL Expression field and a text box will come up that will let you paste your SQL into it. After you've pasted your code into the text box and hit "Ok" you can goahead and save the Quick Reports screen and you're done! You've just created a quick report. Camden Spiller works as a Project Manager for Arrowtech, Inc. He can be reached at: camden@arrowtech.net formatted view |