I have created several queries over recent days. One will serve to allow us to accrue and calculate a commission based on information entered on the sales order. Another is a log of daily shipments cross referenced with the sales order terms. This latter query cross references information from the SHIPMASTER,SHIPDETL, and JOBS tables.
I have found several SQL query commands not listed in the DBA tutorial that actually are supported in the system, or I guess I should say, are supported by Firebird. This includes "group by" and features to extract and use dates. I have also created column headers which I can't remember whether that was documented.
Still, while queries are useful, the average user doesn't want to have to fiddle within the structure of the SQL query. It would be better if I could create a report that allowed users to specify a date range from a table and out comes the results ina user friendly format, along the lines of how the spreadsheet generators work in the utilities menu. A report would also allow the query to extract the information into a customized format rather than into a simple spreadsheet format. I am not sure if DBA has any built in facility to accomplish this (add spreadsheet generators), but I haven't seen it. My only thought is to add an ODBC driver and create reports outside the system that will extract the information, using Excel or Crystal reports. I'm not now using and not looking forward to having to purchase and use Crystal reports or setting up special spreadsheets for this purpose. I have become quite familiar with the report writer supplied with DBA, but this seems like a rough cut tool to use for the purposes outlined above, and again, I don't see any ready way in the system to utilize the report writer for this purpose. Perhaps time and imagination will provide some ideas or inspiration, or perhaps a poster here.
Subscribe to:
Post Comments (Atom)
14 comments:
I'm not familiar enough with SQL to use it well, though I have an issue now where I may have to. I'm using Access through a desktop link for my outside reporting. Built-in reporter is too clunky and restrictive for me - going out gives me better table access and tools. Tom H is the guy to talk to regarding reporting - he's got an intranet set-up going that sounds very impressive.
I am glad someone finally started their own DBA forum. I just hope it shows up in google's search when people are looking for help, the more the merrier.
As for SQL.... Every Firebird sql command and function works when accessing the database from outside of DBA. Maybe you saw my posts on the old forum about DBA, but I cannot stress how much my life has been made easier by using ODBC, PHP and HTML. It simplified inputs and outputs so easily for getting info out of the system. For instance, we can't stand the way DBA reports labor. Their reports are clunkly and not user friendly/detailed/etc... So I wrote a web page that has two input boxes, START DATE and END DATE. Enter the dates, and PRESTO MAGIC, you have a table that reports all the employees that worked in that date range. As you might suspect, I added lots of other search features, like type of job, part number filters, etc...
Another great work around that saved our butts here was a web page that displays all the assemblies that have their underlying linked jobs complete. For intance, MJ200 is waiting for MJ190, MJ191 and MJ192 to be complete before it can be worked on. Previously I have a guy running around all the time trying to figure out if parts are complete. NOW, as soon the underlying jobs are complete for the MJ200 job, the job shows up on the intranet as ready to go. My next step is to implement the email function so people are emailed or texted when ready. The email works great actually, except for some small bugs that send crazy info. But I am working on that.
In summary.. spend some time learning simple php and html and your life becomes much better, I have a giant intranet, that is only DBA info.
Update: I contacted DBA on the ability to create my own reports or "spreadsheet generators." They said they had in the roadmap an ability to add the ability to do this. They suggested using crystal reports with an ODBC driver and provided some suggestions on choices of ODBC drivers.
I think I could add a report created to my menu links and use existing tools to create other reports, but can't see how to accomplish it at the moment.
One particular need is financial statements that compare to budget and/or prior year, calculating variances and variance percentages.
There is a custom method for putting the link to a report generated by report writer in any of your menus, when I get back to the office I will look it up. Pretty simple. I don't know if it will work with crystal?
I use the OBDC driver provided by the firebird groups, IBPhoenix. Took one minute to set up on my windows 2000 server. Never a problem and lighting fast. Makes the Database queries method look like a 3 legged dog.
In the user define menu items, the format is this
{APPPATH}ejrw.exe [{DBPATH} {USER} {PASSWORD} BaseUnitReport]
Base unit report must be typed exactly as it is in reportwriter and the report must be in the root director of the report writer folder/prgram window. The APPPATH DBAPATH USER and PASSWORD are variables that you leave as is.
Is this what you were writing about?
hmmm, never tried that.
I just have a list of links (to help docs that never get read and my reporting database) down the side of my desktop. Dirt simple to set up, no arcane hieroglyphics to remember.
Tom,
yes, thanks, user defined menus was what I had in mind. Its seems pretty straightforward. Unfortunately, I haven't created any useful reports to link.
It would be helpful to be able to add a data table to reportbuilder rather than have to rely on their pre-defined reports and the tables already provided in each report.
Users here are complaining there was no ready way to review activity on a PO. I do see from the PO receipts menu the Print receipts has some useful information, but does not compare the requested date to the date received, and does not include the PO line number. Neither field is available in the table provided however, so SOL.
That's exactly why I started using something else - reportwriter is very restrictive.
However, it sounds like you're missing out on something. You are not restricted to the in-built reports, as you seem to think. Using EJRW outside NG, you can log on to the database, create your own queries from whatever tables you choose, and write your own reports that way. Have a look.
(log on as NG user, not firebird user)
xeyes,
thanks, I had not realized that access existed. I was able to log in and will take a further look.
Can I access DBA's reports as a starting point? There were report folders with familiar names but nothing seemed to be in them.
I haven't been able to, but that doesn't necessarily mean you can't. I haven't spent enough time in reportwriter to figure out its ins and outs. Sounds like Tom has a better handle on what it can do and how. It sounds like he's created a new report in EJRW and saved it in the root folder in the reportwriter directory screen, and it is this report he's calling through his custom menu item. How or where that report definition is actually saved on disk, I don't know.
I'm going to echo what he said (kind of) and suggest you use something else through ODBC. You'll get better results faster.
I created an alternative BOM report for our users using the EJRW.exe. It takes a while to get things to appear the way you want, and my report lacks any subreports or other complicating features, but it gets us a BOM report with the manufacturer cross reference information we wanted. The tool isn't super intuitive, but its worth a shot versus buying crystal reports I think, especially if you don't already know crystal.
The main problem is unlike with the canned reports contained within DBA , there is no tool from within DBA to control data selection. Users have to boot EJRW, click on the report name and select design, go to the data tab and click on the magnifying glass to control the search functions. There they can select, for my report, effectivity dates, BOM type, and most importantly, the parent item for the BOM they want. Steve at DBA says he has referred to programmers a feature request for a data selection interface/tool for user designed reports. I figure they can do something so that users can create those interfaces in the same way DBA's report writers can, even if the interface is module specific and limited to certain modules at first.
I forgot to mention, if you use EJRW, watch out for the setting on the data tab within design, do not leave the box "use DBA data dictionary" checked. It is not particularly noticable, but many fields in the database are not available/accessable when this box is checked. It caused me a lot of aggravation when I could not access the Mtitle field in the item table, and it was only because that box for using DBA data dicitonary was checked, as it is by default.
DBA has not been keeping the data dictionary current and its unclear that checking this tab does anything useful anyway, other than making fields not referenced in the dictionary not available to SQL.
I admire your guts and persistance. It is certainly useful for us to learn more about how things (like EJRW) function.
I recently got a report done that I've been wanting for years - a consolidating BoM - that would not be possible in EJRW. Still want to adapt it to the various orders (the real prize) or other arbitrary listing, but that'll have to wait till I have more time/insight.
Nice to hear DBA may do something useful in this regard.
I am trying to generate cut lists and final assembly kit lists for the shop in a small manufacturing company. I have almost no experience with DBA but I understand how a database works. I have a fairly accurate BOM. I would like to generate a report or construct a query to do this. A lot of the material that needs to be cut exists only in phantom assemblies.
Post a Comment