Introduction

Layout

Description

Getting Started

Overview

If you already have a DBISAM 4 Database, then getting started should be fairly simple. In many ways, this tool is quite similar to the System Utility that can be downloaded from ElevateSoft. The primary difference is that I focused on making a query/reporting system to generate reports from the various DBISAM databases that I use daily to make my life easier. In order to further improve the reporting capabilities, I have created a simple method of exporting the tables to excel format. If you do not have a database to work with, I have included a Demo Database that you can use with the tutorial.

Interface

Installation

To install Daboo DBISAM 4 Query Manager, simply execute the install routine. In order to take advantage of the tutorial, without first changing all of the scripts to match the new path, you should install to the default location.
* Note: The installer contains no intelligence, and will overwrite a previous install. So if you have queries you would like to keep, you should back them up before you perform a reinstall.

Query information

New Query

Click the "?" button, and type in the information related to your query.
Query Name: Descriptive name, I like to use this in my excel reports
Query Description: In Many cases, I have a lot of reports that are quite similar, I like to add info to remind me what each query is for.
Revision: Numbering system to track versions of a report.
Category: Use an existing category or type in a new one. It will automatically be added to the pulldown and the filter for future use.
Excel Template: If you plan to export some queries to excel, You will need to create an excel template. I have included default.xls which can be used for anything, but if you want something a little more customized, you will need to create your own. There are additional examples in the tutorial.

Delete Query

Click the "-" button. You will be prompted with a Dialog Box asking "Delete Record?". If you are sure you want to delete the entry click "Ok", otherwise, click "Cancel".

Edit Query

Click the "^" button. You may now edit your query. You can also enter into edit mode by making a change to the query directly.

Save Query

Click the "check" button. After Editing, it is good practice to click the save button to prevent data loss.

Cancel Changes to Query

Click the "x" button to cancel changes made to the query and revert to previous save.

SQL Query

Type your query in the syntax highlighted editor.
When your finished, click the " Run Query" Button.
To export your results to excel, click the "Export to Excel" button. You do not need to have excel installed on your machine to take advantage of the export feature. Excel Reports typically work equally as well with the OpenOffice.org Calc application as they do with MS Excel

Query Results

For simple queries like "select * from...", the query results are editable. This is not so, for more complex queries such as joining tables or using group by. The border or the results table is red for "Read-Only" results. In the lower left corner of the status bar, there is also an indication as to whether the results are editable.
The Results can easily be navigated and edited by using the "Navigation Bar".

Query List Filter

Click on the Category to filter on.
To Select Multiple Categories to show, hold down the "ctrl" key and click on additional categories.
To disable the filter, hold down your "ctrl" key and click the categories until none of them are highlighted. You could also do the exact opposite, and highlight them all.

Query List

List of all of the reports/queries contained in the filter category(s) that were selected in the List Filter. Contains the name of the Report and the Revision. The list is sorted Alphabetically.

Main Menu

• File
  • Import SQL: Import SQL files (*.sql)
  • Export SQL: Export SQL files (*.sql)
  • Exit: Exit Application
• Edit
  • Cut: Cut highlighted text in SQL Query area
  • Copy: Copy highlighted text in SQL Query area
  • Paste: Paste clipboard text in SQL Query area
  • Delete: Delete selected text in SQL Query area
  • Select All: Select All text in SQL Query area
• Admin
  • Backup Query Database: Backup your queries
  • Restore Query Database: Restore your queries
• Help
  • About: Version information
  • Contents: Help File

Right Click

• In many cases, the standard copy, paste... functions are available.
• Extended functions are available when working in the SQL Query. They are:
  • Undo: Allows you to undo changes made to the SQL Query.
  • Redo: Allows you to redo undone changes made to the SQL Query.

Tutorial

Overview

You should have a Demo directory with the DDemoDB table. This table contains some nice complexities that will allow us to dig into some more advanced development and excel reporting capabilities. I will provide explanation for what is included. I will then go into detail on how to create queries and reports.

Queries

I have included a nice group of example queries that you can run against the included Demo Database.

These are by all means, not the only possibilities, just examples of some of the possibilities. The "DBISAM Version 4 Manual" from Elevatesoft contains a much more comprehensive list of available functions, operators, clauses, statements and overall capabilities of DBISAM4.

clause - EXCEPT: Returns rows that are not present in second query result set.
clause - INTERSECT: Returns rows that are present in two query result sets.
clause - ORDER BY: Sort order in which results are returned.
clause - UNION: Concatenates query results from two or more query result sets.
clause - WHERE: Filtering conditions for a statement.
function - CAST: Convert one data type into another data type.
function - EXTRACT: Returns values for specified elements as an integer.
function - IF: IF, ELSE expression handling
function - OCCURS: Returns rows that searched string is contained within.
function - REPLACE: Find and replace string within a column.
function - RUNSUM: Running total for a column.
function - TEXTSEARCH: Returns rows that searched strings are contained within.
operator - CASE value: Nested boolean expressions
statement - DELETE: Delete one more more rows from a table.
statement - EXPORT TABLE: Export query results to a delimited ASCII text file.
statement - IMPORT TABLE: Import delimited ASCII text file into a table.
statement - SELECT: Retrieve data from tables.
statement - UPDATE: Update one or more rows in a table.
Most of the queries may be run, and exported to Excel using the default excel template.

Excel Reports

Why did I implement excel reports? Well the answer is quite simple. Most of my customers use excel, and the manual reports I was creating automation for were in excel. I changed the way I worked, but I did not need to change the way everyone else did.

I will not go into extensive detail about how every facet of the reporting system works, if you want that, you can go download the flexcel demos at tmssoftware.com and look through their more elaborate examples. I will go into enough detail here to show you how to create nicely generated reports with relative ease. And, as always, feel free to post questions in the newsgroup at dienhart.com for anything I may not have covered in the documentation.

Standard Variables

• Report Information
  • ##EditReportT##Report_Name: Report name. "Query Name" in "Query Information" section.
  • ##EditReportT##Revision: Report Revision. "Revision" in "Query Information" section.
  • ##EditReportT##Comments: Report Comments. "Query Description" in "Query Information" section.
  • ##EditReportT##Report_SQL: Report SQL. "SQL Query" section.
  • ##EditReportT##Excel_Template: "Excel Template" in "Query Information" section.
  • ##EditReportT##Category: "Category" in "Query Information" section.
• Other Information
• #.Current_Date: Current Date/Time stamp that the report was run.
• Query Result Information
• ##ReportsQ##*: All Fields returned from a query.
• Daboo Demo DB Information
  • ##ReportsQ##Date
  • ##ReportsQ##Project Number Prefix
  • ##ReportsQ##Project Number
  • ##ReportsQ##Project Title
  • ##ReportsQ##Tools and Talents Used 1
  • ##ReportsQ##Tools and Talents Used 2
  • ##ReportsQ##Tools and Talents Used 3
  • ##ReportsQ##Tools and Talents Used 4
  • ##ReportsQ##Tools and Talents Used 5
  • ##ReportsQ##Tools and Talents Used 6
  • ##ReportsQ##Hours Overtime
  • ##ReportsQ##Travel
  • ##ReportsQ##Travel Destination
  • ##ReportsQ##Travel Comments
  • ##ReportsQ##Daily Summary
  • ##ReportsQ##UniqueID
  • ##ReportsQ##WDay

Creating your own Queries

Overview

This version of DDQM is designed to connect to Local databases. It is not currently configured to allow connection to remote databases. For all database connections, you will need to use the full path. There are some cases where it will work ok to use virtual paths, but if the working directory changes things get flaky. For the complete sql reference documentation that covers supported SQL, please visit elevate software (http://www.elevatesoft.com).

Let's Create a Simple Query

1. Click on the "?" in the "Query Information" section, to create a new query.
2. In the "Query Name" field, type "Report - Tutorial SELECT"
3. In the "Query Description" field, type "SELECT statement with editable results"
4. In the "Revision" field, type "1"
5. In the "Category" field, type "Tutorial"
6. In the "Excel Template" field, leave "Default.xls"
7. In the "SQL Query" field, type the following:
   -----------------------------------------------------------------------------------------
   SELECT "Date"
   , "Project Number Prefix"+'-'+CAST("Project Number" as CHAR(4)) as "Project ID"
   , "Project Title"
   , "Daily Summary"
   FROM "..\demo\DDemoDB"
   -----------------------------------------------------------------------------------------
8. Click on the "?" in the "Query Information" section, to save your query. And that's all there is to it.
9. Now Click the "Run Query" Button to run the report.
10. In this example, the results are editable. Feel free to double click in one of the fields and change the data to another value.
11. You can add/edit/and delete rows by using the buttons in the "Query Results" for editable data.
12. Now let's run an excel report. Click the "Export to Excel" button. Save it to the "My Documents" folder.
13. Now let's go open the excel file in "My Documents" with Excel or Calc.
14. You should now see a nicely formatted report that you can change the column widths and heights on to nicely present your data. Pretty Simple, Right? Well, It gets better.

Creating Custom Excel Report Templates

Overview

Since realigning your columns and rows every time you run a report can be time consuming, you can create your own excel templates that have the row widths set. Even the printer settings can be setup for each individual report.

Let's Create a Simple Report

For now, use the documentation that is included with flexel.

ChangeLog

1.0.0.5 - 2/4/07

• Initial Release

1.0.0.8 - 3/6/07

• Added Import and Export SQL Functionality to Main Menu.
• Added Cut, Copy, Paste, Delete, Select All functions to Main Menu.
• Added Backup and Restore Query Database Functionality to Main Menu.
• Many subtle interface improvements and enhancements to make the application more intuitive and easy to use.