Views are Your Friend

One of the challenges I see regularly in my work as a database administrator is finding a way to bridge the gap between the well-normalized physical implementation of a database behind an application and providing a way to make the data available in an easy-to-use (and understand) fashion for the end user.  For many clients this means letting them design their own reports.  Database views are my go-to method for making a complex database structure easy to use for end-users and even developers.

Views have a number of advantages.  For one, they are available in all SQL databases, including SQLite.  Views allow you to reduce the complexity of the end user experience by managing and limiting the data presented.  This might mean taking a very complex query joining multiple tables and presenting it in a single table with the end user (or developer) not needing to know the gory details of the SQL behind it.

This last point is important for the developers too.  Having the programmers figure out the complex joins for a query pulling from multiple tables is sometimes challenging and not always a good use of their time.  Having the Xojo application do all that work for a complex query is sometimes painfully slow.  Views not only speed up the development process but make the Xojo application more efficient since the database has already created the view and optimized it internally.

Views are more secure since you can limit the data shown and control who has the rights to view it.  By giving users and developers access to the underlying tables you may be exposing sensitive data.  Views are an easy way of sanitizing data.  We use views as read-only constructs so the user cannot update the data (note:  not all databases treat views as read-only).

Views can be created, modified and deleted via simple SQL statements.  The syntax of the statements (in particular for modifying views) varies a bit from database to database but the details can be easily found.  To create a view, the syntax is:

    CREATE VIEW viewname AS

SELECT (fill in your query here)

We recently used views implementing a large customer records management (CRM) system for an insurance broker.  Their database has around 100 tables containing information on customers, policies, related products, agent and commission data.  The data is normalized and it isn’t always straightforward to get related data.  The client does not have a dedicated IT staff and reporting is the primary responsibility of a part-time administrative employee, “Jane”.

Our goal was to provide Jane with the easiest means possible to write any report that was requested by management.  While Jane has some knowledge of databases and foreign keys, she isn’t technical and doesn’t have any training on the SQL language.

Our answer was to use our desktop reporting tool, BKeeney Shorts, customized to only show her views created solely for reporting.  (We did this via a simple naming standard in the database.)  She is presented with options such as:  Agent Commission, Prospect Contact and Client Policy Details.  If she needs an additional view, it’s easy to add it to the database and not impact the Xojo code behind the application. (It doesn’t require a recompile and redeployment of the software).

The view incorporates the calculations behind the data, so the reports are always consistent in terms of things such as how commission checks are calculated or who is the primary agent for an account.  By using views we ensure that consistent results are shown to the end user even if someone else is assigned to write reports

Views provide a level of abstraction between the database and the end user and we find  them very useful.  For the developers, it means they don’t have to figure out the complex queries and have the Xojo application create inefficient and complex queries.  Views are your friend!

Shorts Report Designer 1.6.2

pens128BKeeney Software is proud to announce Version 1.6.2 of BKS Shorts with Report Designer, our reporting classes and tool for Xojo.  Shorts allows you to integrate a report designer into your own Xojo desktop application.  Desktop and Web applications can generate reports.  Both versions can export to PDF if they have the MonkeyBread DynaPDF plugin.

This is a free update to all existing users.  This version fixes a couple of important bugs and is recommended for all users.  In this version we also added a CSV Renderer that allows you to export your reports to CSV in addition to HTML and PDF.

Product Home Page:  http://www.bkeeney.com/allproducts/bkeeney-shorts/

New:

  • Added a CSV Renderer that can export reports to CSV format ignoring page headers and footers.  (Note: CSV Export isn’t perfect. We’re just taking the text and putting it in a file.)
  • Added CSV Export into the File->Export Menu to give example.
  • Added Chinook Invoice Database Example.
  • Added ability to get exception messages back from the Report Thread.

Changes:

  • Tightened up the spacing in the dynamic DateTime container.
  • Did some prep work in the Designer and in ReportPF to allow fields in the header in a future release.

Bug Fixes:

  • Fixed Formatting of page numbers so it can go up to 4 digits.
  • Fixed loading of Schema and Views in the report viewer.
  • When opening a report the view switches automatically to the Design View.
  • Fixed an issue where adding ? to the Filter Data (thus making it a dynamic runtime variable) would not get saved without unchecking and checking the dynamic checkbox in the list.
  • Fixed issue with Lines line staying put in proper location in the Designer.
  • Fixed issue where adding a Field in the Header of the report would cause what appeared to be an infinite loop (really was an exception in the thread). Now it will finish the report.

Shorts Report Designer 1.6.1

pens128BKeeney Software is proud to announce Version 1.6.1 of BKS Shorts with Report Designer, our reporting classes and tool for Xojo.  Shorts allows you to integrate a report designer into your own Xojo desktop application.  Desktop and Web applications can generate reports.  Both versions can export to PDF if they have the MonkeyBread DynaPDF plugin.

This is a free update to all existing users.  This version is mostly a bug fix release and is recommended for all users.

Change List:

  • The Footer Constants can now also be used in the header
  • Refactored DesignCanvas and ReportPF and moved some of that code into PAF_DatabaseKit.DBWrapper where it makes more sense
  • Loading a report into the designer, or for rendering, will no longer re-read the schema and overwrite any manually created relationships
  • Added Tables and Views section into the report definition file
  • Fixed an issue with report width/height not being remembered correctly. Changed from using PrinterSetup string which is not cross platform safe to use the dimentions instead.
    • YOU NEED TO RESAVE YOUR REPORTS TO TAKE ADVANTAGE OF CHANGE
  • Fixed issue where landscape reports weren’t being exported properly to HTML and PDF.
  • Added breaks in the PreparedStatement creation to help in debugging.
  • Added some missing field handling to ODBC
  • DBWrapper will no longer create a missing SQLite Database.
  • Fixed UI in winDBRelations

Shorts Report Designer Release 1.6.0

pens128

BKeeney Software is proud to announce Version 1.6.0 of BKS Shorts with Report Designer, our reporting classes and tool for Xojo.  Shorts allows you to integrate a report designer into your own Xojo desktop application.  Desktop and Web applications can generate reports.  Both versions can export to PDF if they have the MonkeyBread DynaPDF plugin.

This is a recommended update for all registered users and is a free upgrade.  Besides a number of important bug fixes there are also some big new features

Runtime DateTimeThe first is the ability to ask the user for runtime parameters for a query.  For example, if you created a report based on a fiscal year you can now ask the user which fiscal year they want.  The dynamic runtime query lets you ask the user for a number of different things.  Strings, numbers, user supplied lists, and even a list based on a database query are possible.
Drilldown EventThe second thing we added is the ability to get an event back from the generated report on what row was clicked.  This event gives you the information for all objects in that row so you could implement a drill-down report.  Along with this event we now allow invisible objects to be created to provide additional information.

Our product page is at http://www.bkeeney.com/allproducts/bkeeney-shorts/

Full change list:

• Dynamic Runtime Query variables. Allows the report designer to ask the user what values they want at runtime. Current paramters types::
– Boolean value using a Checkbox
– String value
– Numeric (integer and double values)
– User List (popup)
– Query List (popup)
– Date, DateTime, or Time

• Moving items via the mouse will immediately update the save status on the Toolbar

• Changing Filter Data will update the save status and undo status

• Undo now works for changing of filter data.

• Added Filter Data to report designer Toolbar

• Added an ObjectSelected event on the BKS_ShortsBaseViewer that will pass back the clicked BKS_Shorts.Item and if there is one, the BKS_Shorts.GroupItem. This will allow you to implement a drill down report.

• Invisible TextItems are rendered in the background color to allow the MouseHit to bring back additional information in the group

• Added a FieldName property to BKS_Shorts.Item and reports run through the Report Designer will fill that in at generation time. This allows the user to query what the field (if there is one) from the selected item.

• Added OK/Cancel generic container for all dialogs

• Bug Fix [Windows Only]: Now respect the number of copies specified for a print. Note that this is not in Shorts classes itself but how the supporting code prints.

• Bug Fix: Fixed another instance where it was possible to NOT have a Default Style when generating a report.

• Improved German localizations.

• Moved About Shorts Designer Menu Item out of winReportDesigner

• Bands that are marked as invisible no longer get rendered on the report.
Known Limitations:
• Dynamic Queries do not work with IN clause in where statements.

BKeeney Shorts Progress

picAppIcon256It’s been a little over a month since BKeeney Shorts, our Real Studio/Xojo reporting classes, were released to the general public.  All I can say is thank you!  The response has been tremendous and has exceeded my wildest explanations.

We’ve been working on a few updates this week.  First, we can now search a report for specific instances of a string.  This wasn’t on our radar but during the Xojo Developers Conference someone blurted it out during our training day and it got me thinking.  It turns out it was really easy to implement.

One of the things that Shorts does is has all of the pages available to the document and all of the objects on the page available to search so it ended up being an incredibly easy thing to do.  We don’t really ‘render’ the page until it’s called for.  In the demo app we added the search function and also created a list to show which pages the instance is on.  I think you’ll be really happy with it.

The second thing we accomplished this week was to get RTF Text blocks mostly working.  RTF is a particularly troublesome format to deal with because you have to convert from RTF into something that Xojo understands (StyledText) and then figure out how to draw it because we can’t use the standard TextArea.DrawInto command because our Page display can zoom in and out and also deal with Retina display on Mac OS X.  What a pain and I’ve already been able to get it to fail if I make the RTF even remotely complicated.

The conversion to HTML and PDF has gone relatively smooth but there are some minor glitches to work out there too.  Few conversions are 100% perfect and the trick is to get it close enough so most people are happy with it (or at least don’t send angry emails).

So what else is coming up?  That’s always a tough question to answer.  One of the things we really need to get into the product is automatic text block height calculations.  You provide the string, block width, and characteristics and it returns the height.  You can do this on your own now but it’s not nearly as easy as it could be.

The second thing that I really think would be cool for a Xojo application is runtime interaction with your report and receive events when a user clicks on a report object.  Imagine if you will an account balance report.  You see that account 10100 has an unusually high balance and rather than go back to the report screen to create a report on that account you simply click on the account.  The Shorts viewer receives an event, and the smart developer responds to the event and automatically creates a drill down report on account 10100.  I know of no other reporting tool in the Xojo world that is doing that.

Then there are some of the obvious needs.  We really want to add a designer control allowing you to visually create reports and then save that format into an external file so that in the long run reports don’t have to be compiled into your application.  Client wants a tweak to a report?  Find, send them the new report definition file and voila, it’s done.

I’m sure I can come up with a half a dozen other things I want to see in the product.

Some of the comments we’ve received about BKeeney Shorts so far have been awesome.  Things like it being easy to import into an existing project, fast in creating reports (about 300 pages a second!), and thanking us for wonderful support are all really cool things to get in your email in box.

That’s my quick update for BKeeney Shorts.  What sorts of things do you want to see in a reporting tool for Xojo?

BKeeney Shorts

picAppIcon256Reporting for many developers is a big deal.  When I first came to Real Studio (then REALbasic) there was really only one commercial option – On Target Reports.  Eventually Real Software released a built-in reporting tool.  Not too much later than that RothSoft released RSReport.

On-Target Reports now appears to be defunct.  Its domain has been hijacked and as far as I can tell there is no way to purchase or get support.  The built-in reporting tool is okay but lacks some features that would make it a good reporting tool.  We’ve used RSReport for many years but we’ve increasingly found it lacking for some of our more advanced projects – including those that are Web Edition based.  The fact that we can’t change the source (because it only comes encrypted) is also a major drawback to us.

Today we are announcing BKeeney Shorts our own set of reporting classes for Real Studio.  Using Shorts you can create practically any type of report you need.  Shorts is a set of classes that allows you to place your report objects (text, line, rectangle, image, etc) on the page in practically whatever placement you need.  This leads to some incredibly rich and detailed reports that would be very hard (if not impossible) in a typical banded reporting tool that uses the concept of ‘rows’.  Shorts has no rows, only objects.

Shorts render to multiple formats.  If you are in a desktop application you can render it to the graphics object for display or printing.  If you are in a Web Edition application you can render to an HTML file/page.  And, if you are a user of the Monkeybread Software DynaPDF plugin you can render to a PDF document.

BKeeney Shorts is Retina display ready (Mac Cocoa only) and the only thing required is to add the appropriate plist settings in your application.  It automatically scales depending upon the printer setting so your printed output should always be crisp and bold.

Shorts Viewer

BKeeney Shorts is fast.  In a recent test I ran a 24,000+ page report on the main thread (I would not recommend doing this as it froze the UI for a while) but it did it at a respectable rate of about 300 pages a second.  I’m sure a more complex report would be slower but the point is that for most developers a 300 page report in a second is fairly fast.  Since you control the paging I would certainly recommend bailing out in a report that had more than 100 pages (or at least asking the user if they want to continue).

This is a 1.0 product and we’re just getting started.  We have a lot of plans for it.  Eventually we’d like to come up with an external format and designer so you could create your own report in a designer and then simply load the report into your application and it would create the report for you.  This feature would be nice since you wouldn’t have to recompile your apps if you have new reports or have changes to your reports.

Another high want feature is to have a runtime interaction with the report output.  In many financial reports it’s quite common to have a type of report that shows a list of accounts.  Currently if you wanted to drill down into a specific account you’d have to go back to where the report was created and pick the account and rerun it.  In the future with BKeeney Shorts you’ll be able to click on the account, capture the event, and you, as the developer, would know which account they clicked.  As far as I know, no one in the Real Studio world has anything like that.

ShortsCode1BKeeney Shorts has two licensing options.  The first is the Standard License for $150 where you get a set of encrypted classes.  We know that many developers like having the full source code to their products they buy so we also offer a Professional License for $300 that comes entirely unencrypted.  In either option we will support to the fullest extent we can.  Chances are good that if you have a feature request or find a bug it’s affecting us too.

I invite you to take a look at BKeeney Shorts at http://www.bkeeney.com/allproducts/bkeeney-shorts/ and try out the demo application.  It has a sample report for you to modify and change to your hearts content.  The only restriction is that the encrypted classes cannot be used in a compiled (non-debug) application.  If you use any of the classes it will immediately quit with a nag message.

As always, I appreciate your support and good wishes.  Happy coding!