last updated: 2015-06-01

jDBexport - quickstart guide

This document provides a very basic HowToGetStarted instruction to jDBexport (version branch 3.2 or higher). This documentation is written to the best of our knowledge, but if you spot mistakes please inform us (and we will correct them). This quickstart guide shows you how to setup and configure jDBexport in a step by step approach. Let us assume the following conditions: We run an averagely complex website, including a calendar (JCal PRO), where people can register for our events, an image gallery (PhocaGallery) where our events are documented. We want to get actual data reports out of our website database.

We have set up our DEMO website accordingly, to make it easier for you to understand these instructions. If you need more detailed information, please refer to our jDBexport user guide. For any questions about this guide or the jDBexport component, please contact us .

Table of content

Step 1: Installation
Step 2: Languages
Step 3: Configuration
Step 4: Categories
Step 5: The first worksheet
Step 6: The first workbook
Step 7: Preparing the frontend
Step 8: Contiuing from here?!
...

go to top

Step 1: Installation

The installation is straightforward. We packed the component, the backend and the frontend module, the datagrid and datafield plugins and all languages into one single installation package, which we downloaded from our website.

We navigate to the [Extension Manager] in the Administration backend of our website...

and [Upload and Install] the jDBexport Package File (filename something like pkg_jdbexport.V3-2-2.JOOMLA3x.zip). This will install the component, create the components menu entries, install the backend module and the frontend module, install the datagrid and the datafield plugin. The package also includes our latest localisation files for all elements. If your own language is not (yet) included, you might consider joining our language translation project on Transifex .

go to top

Step 2: Languages

jDBexport supports multiple languages (to help us increasing this support, please join our translation project at TRANSIFEX.com). All available translations are delivered with the installation package (see Step 1).

go to top

Step 3: Configuration

jDBexport uses a wide varity of configuration options. This options are accessible through the components toolbar button [Options].

The best way to avoid unexpected behaviour is to browse through all options tabs and save-change-save the options you need. We here very briefly describe the most important options only, a full description is available in our user guide. There are six individual tabs in the options dialog (please note that in our demo environments, you can only see five of them and some options are disabled for security reasons).

Worksheets

Substitute in SQL queries: This allows you to use the substitution variables in the main SQL query.
Show help tabs: This activates additional tabs below the main SQL query with useful information.

Live syntax check: This allows you to check the validity of your written SQL query with one click.

Workbooks

Document format: Here you select the default document format (you can override this in every workbook individually).

Substitute workbook parameters: This allows you to use the substitution variables in workbook parameters.

Schedules

For a start, you do not need any configurations here.

Advanced

Check SQL: This is very important for security. Please make sure this is activated.

Disallowed words: This is a list of SQL keywords that are rejected by jDBexport.

For an detailled discussion on these two options, please refer to our user guide.

Permissions

For a start, you do not need any configurations here.

go to top

Step 4: Categories

For the beginning we will only need a single jDBexport category. We navigate to the [Category Manager] ...

and create a new category (we only fill out title, alias and description) and leave the rest to the default values.

go to top

Step 5: The first worksheet

To start using jDBexport, we will create a very simple worksheet. We navigate to the components [Worksheets] page and create a new worksheet ...

To begin, we only need a title a Sheet name and the SQL query, all other parameters could be considered "advanced usage" and are described in our user manual. As title we use "Quick start guide example", as Sheetname we use "quick-start-guide" and the SQL query should return a full list of all JOOMLA categories in the database (" select * from #__categories "). The table prefix " #_ " will be replaced by the actual table prefix in your database. To see this table prefix, go to the [Server] tab in the JOOMLA global configuration and have a look at the [Database settings].

We can check the SQL query for errors with the Check Syntax function below the SQL query field (in this screenshot, we use an invalid table name to demonstrate this). If the syntax check does not show any errors, we save the worksheet.

go to top

Step 6: The first workbook

Now we will create a workbook with this single worksheet in it. So we navigate to the components [Workbooks] page and create this new workbook. On the [General] tab only title, link title, filename and category are required, so we only fill out these fields and let the rest to default values (except for the description, which we fill in for better understanding).

on the [Worksheets] tab, we drag-and-drop the worksheet(s) we want to be included in this workbook (the Quick-Guide worksheet we just created).

and we [save & close] this workbook. Returning to the [Workbooks] page, we already can download the newly created workbook with it's worksheet.

go to top

Step 7: preparing the frontend

For the frontend jDBexport has two menue items to be configured:

Show (nested) jDBexport categories
Show documents in jDBexport category

We create a new menue item of that second type in the JOOMLA [Add new menue item] dialog. It shows a table of (accessible) documents within our jDBexport category (we selected this in the menue items required settings).

This view allows the user to either preview or download the available documents. The preview creates the selected document and directly shows a limited number of records in a simplified layout. In the above screenshot you also see our jDBexport frontend module (below the main menu module), which provides quick links to workbooks.

Step 8: contiuing from here?!

So far, this was not very complicated, wasn't it???

However, this is, as mentioned before, only a very brief instruction on how to get started with jDBexport. But there is a whole lot more to discover in this product. Just let us name a few...

Worksheet formatting with colors, backgrounds, fonts, fontsizes, borders, image (logo) inclusion
dynamic SQL queries based on VARIABLES (user depending, ...)
create Excel formulas in resulting reports
indivudalise worksheets by asking the user to submit parameters before generating the Excel workbook
including multiple SQL queries in one worksheet
add autofilters to your XLSX workbooks
add embedded bar-charts (or others) into your XLSX workbooks
accessing additional databases, even with other database management systems than your JOOMLA database runs on
create complex workbooks in the background, delivering them by email to your users (avoiding the user have to wait for the workbook)
automatically create and deliver workbooks based on a sophisticated task scheduler
provide direct links to your workbooks with the jDBexport modules (both frontend and backend)
include any worksheets dynamically into any content with the datagrid plugin
include a single cell from any worksheet dynamically into any content with the datafield plugin
create XLSX, XLS, ODS, CSV, HTML, PDF, JSON documents as you need them
finetune the frontend layout to be consistent with your website using individualised CSS
use Corporate Image fonts in your documents, including in PDF documents
use Corporate Image colors in a custom defined color table when creating worksheets
and much more...