last updated: 2014-06-12
This document provides a very basic HowToGetStarted instruction to jDBexport (version branch 3.1). 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?!
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-1-2.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 .
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).
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).
Show download button: This allows you to generally make the download button visible on the [Category view] or not.
Show background processing button: This allows you to generally make the background processing button visible on the [Category view] or not.
Show preview button: This allows you to generally make the preview button visible on the [Category view] or not.
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.
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.
- For a start, you do not need any configurations here.
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.
- For a start, you do not need any configurations here.
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.
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.
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.
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.
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
- 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, 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...
And there is virtually NO LIMIT to the complexity of your SQL queries. The SQL command SELECT has an enormous potential...
End of quick start guide
Copyright Schultz IT Solutions