In this article, I present my own reporting solution which would be easy to use, easy to deploy at a central location where reports can be stored.
Introduction
Symmetric Web Server is a lightweight SQL & HTML web server reporting tool. This tool provides an easy way for users to access SQL reports on their local network without the hassle of having to setup an entire web server. The web server also allows control of user accounts. The REST interface (API) provides the ability for user integration to third party applications.
Benefits of this application include:
- Easy to deploy
- Centralized configuration
- Customizable report template creation
- Display and print reports in HTML
- MySQL and MSSQL databases supported
- Integrated user accounts and security management
- REST interface (API) for Users
Background
I work for a company that deals with building automation (the controlling and monitoring of PLCs in buildings). I used to write SQL reports alongside the applications that we deployed. These applications would accept user input and output data into a data table. I identified that these application reports were only made available to the PC they were on; this made it hard for the deployment of the reports. The other limitation with this method is that it is not very dynamic, meaning I had to program the user input-fields and data layout each time one of my clients required a new report. I then decided to create my own reporting solution which would be easy to use, easy to deploy at a central location where reports can be stored.
Below is an example of how I used to make reports. The user can enter the time range they require and click "Go" to display the results in the data table. When you click on the "Print" button, it opens an HTML report of the data table.
Overview of the Libraries Used
I have used a few 3rd party libraries when programming this app. First of all, this was written in C# with the intention of not only working in .NET for Windows but also working in mono for Linux and mac. I have yet to port this app but I am in the process of doing so. I have also used Nancy (http://nancyfx.org/), a lightweight web server written in C# (this is some of the best and most fun code I have used before). I have also used Bootstrap (http://getbootstrap.com/) with a metro theme (http://talkslab.github.io/) for styling my webpages.
Using the Application
Starting the Server
On opening the application, you will notice a popup balloon in the system tray saying the server has started up. If the server does not startup, you can view the logs in the application folder under "WebServer_MESSAGE.txt".
Double clicking on the icon opens the web server in your browser or right clicking on the icon gives you the option to open the server; restart the server; view the about screen or shutdown the server.
Logging into the server is now possible using the default account: "Admin" and leaving the password blank.
Environment Settings
To access this page, go to Admin Panel > Environment Settings.
Environment Settings allow you to set-up the network IP addresses you want your Web Server to use. For example, if the computer has 3 network cards, i.e., 3 different IP addresses, you will have to enable these different network cards on the web server if you want computers on that specific network to access your web server.
From the Environment Settings page, you are also able to set the port you want your server to start on; if the server must startup on system login and if the getusers REST method is exposed (this REST method is explained later on in the "REST user interface section").
Note: Remember to allow the port on your firewall.
Server Status
To access this page, go to Admin panel > server status.
Here, you can view what IP addresses are being used by your server.
Users
To access this page, go to the users link on the navigation bar.
Here, you can add/edit and remove users.
Default Admin User
As the default admin does not have a password, this will need to be the first thing you should change. Click on the edit button for the Admin account and change the password.
Users Explained
All users are stored in a sqlite database called "user.db" in the Web_Content folder of the application directory. Every user has a security level attached to them, the Default Admin account cannot be deleted and has a security level of 100 (the highest security level avaliable).
There are different types of security levels:
- 0 - 19 : Junior Operator (can view reports)
- 20 - 39 : Senior Operator (can view reports)
- 40 - 59 : Supervisor (can view reports)
- 60 - 79 : Manager (can view reports and control users)
- 80 - 100: Administrator (can view reports, control users and control reports)
If your security level allows it, logging on to the server gives you access to controlling the users, you can't delete your own account, you can't adjust your own security level and you are not able to add or edit a user with a higher security level than yours.
REST User Interface
I have included a REST interface for the users section of the web server. The whole point of this is so a developer can use these users in his own applications.
These are the REST methods:
getbasicusers (GET) - Get the basic properties of all the users (no passwords)getusers (GET) - Get all the properties of the users including the passwords (which are stored in SHA256 format). Note this REST method has to be enabled from your environment settings.remotelogin (POST) - Login to the serverremotelogout (POST) - Logout of the serverdeleteuser (POST) - Delete a useraddedituser (POST) - Add or edit a userreaduserlastupdated (GET) - Get the last time the user.db file was updated
Creating Reports
In this section, I will give a quick overview of how to create reports. Note that only admins can create reports.
Each report you create must link to one connection item, one HTML template and may link to an HTML form.
I find it best to explain things by example so I am going to create a report using the database "Northwind", this example along with other reporting examples can be found on the github repository in the "Northwind Example" folder.
Connections
First things first, setting up a connection. Go to the Admin Panel > Reporting > Manage Connections.
Here, you can create a connection to either a MySQL or MSSQL database.
Here, I have setup a connection to the database Northwind on my local PC.
Forms
The next step is setting up a form (this is optional). Go to Admin Panel > Reporting > Manage Forms.
The form is the part of the report which is used by the user to input data into the HTML template.
Forms have different types of tags that they can use for input, namely:
inputdate - Used by the user to select a dateinputtext - Used by the user to input textselect - Used by the user to select an item from the combo box. When you get to the manage report section, you will be able to define a SQL query for this combo box. The format of the SQL query is as follows:
select [id], [name to display] from [table]. The select must have 2 columns, the first column must be the id and the second column must be the display name (i.e., the name that the user will use).var - This is inputted when we get to the manage reports stage. This is used by the admin to enter text to be displayed on the form.
Note: The tag names are available on the form page.
So here, I have created a simple form with a var tag and a select tag.
The output of this form will look like this:
I entered the name "Customers" for the var tag with the name var1 in the "Manage Reports" section. The "manage reports" section also asked me to enter in a SQL query for my select tag called select1, so I entered in "SELECT customerid, contactname FROM customers" which fits the select SQL format.
Templates
The next step is setting up a template. Go to Admin Panel > Reporting > Manage Templates.
The template is the part of the report which is used to display the actual data back to the user.
Templates have different types of tags that can be used to display data, namely:
date - Used to display the current date.inputtext - Used to display a text input from a form. If a form has a text input with the same name as the text input on the template, the template will display that value.inputdate - Used to display a date input from a form. If a form has a date input with the same name as the date input on the template, the template will display that value.select - Used to display a select from a form. If a form has a select with the same name as the select on the template, the template will display that selected item's display name.var - This is inputted when at the "Manage Reports" stage. This is used by the admin to enter text to be displayed on the template.query - This creates a table of data to be displayed to the user. In the "Manage Reports" section, one can write a SQL query to populate a table here.
To add an image to your template, you can reference files inside the "report_resources" folder.
In the example below, I have added an image "/report_resources/northwindlogo.png"; a var tag named var1 which will be used when we create our actual report; a select tag named select1 which will be used to display the selected item from the form; a date tag which will display the current date and finally a query tag named query1 which will populate an HTML table with SQL results.
The output of this template will look something like this:
I entered the name "Customers" for the var tag with the name var1 in the "manage reports" section. The date tag is displaying the current date. The select tag with the name select1 is showing which item I have selected in my form. Lastly, the manage report section asked me to enter in a SQL query for my query tag called query1; I entered in:
"SELECT [CustomerID] AS ID
,[CompanyName] AS [Company Name]
,[ContactName] AS [Name]
,[Country] AS [Country]
FROM [dbo].[Customers]
WHERE customerID = @select1".
The columns are given alias names. I am also selecting where the customerid is equal to select1, this is the selected item id from the select1 tag in the form.
Reports
Lastly, you have to setup the actual report. Go to Admin Panel > Reporting > Manage Reports.
This is where we define our actual report.
Here, you can see that I have made a reporting group called "Northwind Reports" and named the report "Customers". I have selected the "Northwind" connection we defined earlier. I have also selected the form "Basic Select Form" as earlier defined. Note: The forms field can be left blank. For the form, I have setup the var1 and select1 tags. As you can see for the select1 tag, I am selecting customerid and contactname from the customers table to populate my select combobox, this matches up to the required [id], [name to display] that the select tag requires.
I have selected "Basic Template" which was also previously defined. For the template, I have setup the var1 and query1 tags. In the query1 tag, you can see that I have typed "WHERE customerid = @select1" this will use the selected item's id from the combobox "select1".
View Reports
Now we can view the report we just created. Go to the view reports link on the navigation bar and select the "Customers" report.
Project Examples
I have include an example of reporting in my github repository under "Northwind Example", you will need a copy of Microsoft SQL Server 2005 or higher to use it. This example shows you how to use all the different types of tags in Symmetric Web Server.
Real World Examples
I just want to give a quick example of how I have used Symmetric Web Server in my work. I work for a company that deals with building automation, i.e., the controlling and monitoring of PLCs in a building. The Symmetric Web Server is used by various users on the different workstation PCs to view reports. These reports range from what lights are faulty on a site, to how many fire detectors have been active in the last month, to who has access to which area in the building, etc.
Limitations
The most noticeable limitation of this app is that printing tables in Chrome/Firefox cuts the bottom line off. Funnily enough, in Internet Explorer, it works. A way around this would hopefully be to save the HTML to PDF and then do the printing.
The Future
I plan to update this application in the future when I get time to do so. Some of the things I want to focus on are:
- Support for Linux using Mono
- Adding in an HTML editor for forms/templates
- Cleaning up a bit more of the code
- Adding support to save as a PDF
- Creating a more intuitive interface for report creation. For example; you can preview the report before saving it.
If people find this article of any use to them, they are welcome to help contribute to the github repo. :)
Thanks
Thanks to Tiaan Wolmarans for editing this article and also thanks to you for reading the article. I hope this will help you with your reporting!
