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 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.

Shorts Report Designer Release 1.5.4

pens128We released version 1.5.4 of the Report Designer today.  One of the bigger changes is it ships with a web example of how to take a report definition and display it for a web app.  This affects a significant amount of methods and properties throughout the project to make them work on desktop and web but seems to work well.

BKeeney Shorts (with report designer) is 100% Xojo code (DynaPDF Starter kit required to export to PDF) and comes with a drop in Report Designer and Report Viewer component for both desktop and web apps.

For purchasing information please visit http://www.bkeeney.com/allproducts/bkeeney-shorts/

Version 1.5.4 change list:

  • Major code changes to allow most classes to work in web apps too.
    • Simply copy BKS_Shorts_ReportDesigner folder into your existing web project.
    • Delete PAF_PrintKit.DesignCanvas (desktop ScrollCanvas subclass)
    • Create a new PAF_PrintKit.DesignCanvas that is a WebCanvas subclass
  • Changing text values in the Properties List is now Case Sensitive
  • Added Portugese Localization
  • Added a commented out example of how to connect to MySQL without using the winDBConnection Window.
    • See winRPTViewer.Display
  • Fixed an issue that would cause the SQL statement to not be saved properly in the JSON string
  • Added a Default Style if none is in the local dictionary
  • How reports are saved so they can be viewed without first having to be in the designer
    • WARNING! YOU WILL NEED TO RESAVE ALL OF YOUR REPORTS
  • Cleaned up some localizations and made some more strings dynamic.
  • Made the Report Designer the default pushbutton in Demo Window
  • New Report opens a new Report Designer Window instead of copying the current connection. (this affected menu handlers in odd ways one wouldn’t expect)
  • Created a Web Example

Shorts Report Designer 1.5.3

We released version 1.5.3 of BKS Shorts today.  A number of bug fixes, changes, and additions were added.  The change list below.

I will be the showing off Shorts at the next Xojo webinar on February 2nd at at 1:00 PM (GMT-5:00) Eastern Time (US and Canada).   Signup at https://zoom.us/webinar/register/7a19681a9a0c3f5f7c24e00bf0acd2b8

One of the new items is the ability to show a Row Number on any band using the new SC_GetCount XojoScript.  Add this to any Band Script to modify a TextItem.

We added a couple of new examples based on user feedback.  The first is using the SC_GetCount Band Script to set row numbers.  The second, is an example of how to print directly to a printer without having to go through the viewer.

Change List:

  • Added SC_GetCount in the Band XojoScript editor. This lets you get how many times this band has been shown.  Example of use is to have line numbers on your report without having to do it in SQL.
  • Updated German localization
  • Rearranged UI on ccPAF_Filter (Filter Data) to make it a bit more obvious
  • As a Text Item or Field Item are put on a report it will automatically use the “Default Style”
  • Fixed an issue where the Default Style wasn’t getting passed to the generated report.
  • The Styles Editor now allows you to delete multiple Styles at a time.
  • Added a new example of how to print directly to printer without having to use the viewer.
  • Reconfigured Demo window to break between Report Designer stuff and older code-only stuff.
  • Added SC_GetCount Demo
  • Fixed some items in the HTML Renderer
  • In PAF_PrintKit.PrintText.constructor if there is no DefaultStyle we create one.
  • ReportPF will now extract Styles from the report definition file.

BKS Shorts with the Report Designer is $300 and you get the full source code.  More information can be found at http://www.bkeeney.com/allproducts/bkeeney-shorts/

 

BKS Shorts Report Designer

picAppIcon256BKeeney Software is pleased to announce version 1.5 of BKeeney Shorts, our reporting classes for Xojo.  This free update for Short Professional users is a major update to the tool and includes a set of classes that allow developers to embed a report designer into their Xojo applications.

Since its release in 2013 BKeeney Shorts has been used by Xojo programmers around the world to create complex reports using nothing but Xojo code.  The number one request from users was to have an external reporting tool that let them create reports quickly and easily without the need to code the entire report.  With the Report Designer we’ve removed the need to code all but the most complex of reports:  most reports can be created in minutes instead of hours.

Gems Report Designer

Included in the BKS Shorts demo project is a fully functioning Report Designer.  Developers can copy and paste the Xojo code into their own project and integrate the Designer in just a few minutes.

Reports are saved in a simple JSON string that allows the developer to have reports defined outside of their project.  The Demo project saves a report file but a Xojo programmer can easily save them as strings for use in their own document or database.

Reports Bands can run XojoScript code to do complex runtime actions such as change text, styles, concatenate text, show images and much more.  This allows end-user changes in a report without the developer coding it up front and having to recompile the executable.

Gems Report Rendered

BKeeney Shorts Professional costs $300 and comes with full, 100% unencrypted source code.  To export to PDF (without a watermark) a separate purchase of the MonkeyBread DynaPDF Starter plugin is required.  This is a free update to existing Shorts Professional users and a $75 update to existing Shorts Standard Users.

BKeeney Shorts requires Xojo 2014 R3 or better.

Homepage:  http://www.bkeeney.com/allproducts/bkeeney-shorts/

Video of Report Designer in Action:  http://www.bkeeney.com/allproducts/bkeeney-shorts/shorts-designer-in-action/

Video of Integrating the Report Designer:  http://www.bkeeney.com/allproducts/bkeeney-shorts/integrating-shorts-report-designer/

For questions regarding BKS Shorts, please contact Bob Keeney at support@bkeeney.com

RB Code Reports Updated

appicon64We updated RB Code Reports today after we realized that it wasn’t reporting properly against Web Edition projects.  The Statistics Report now shows Container Control, Web Container, and Web Page counts.  It did not before.

We also added a preference for the Signature Report so that you can now specify the scope of the signatures you want in the report.  For example if you wanted only Public, Protected, or Private methods/properties you can now filter on them.

It’s interesting.  Our big Web Edition project is now getting rather large.  228 Web Pages, 173 Web Containers and a little under 65,000 lines of actual code.  I estimate that we are less than 50% done.  We are definitely pushing the edge on Web Edition.  Fun, eh?

RB Code Reports Version 3.0.5 is a free update for all registered version 3 users.

For information at https://www.bkeeney.com/rb-code-reports/

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?