SQL Server & ODBC Database Plugin

The following are details regarding the use of the SQL Server and ODBC plugin to output your data into Bezlio using any of our many templates. However, these same instructions apply for most any plugin / data source.  In the case of all other traditional databases, the ODBC plugin would be used and these instructions would be nearly identical.  


Step 1 - Configure the SQL Server Plugin

The latest documentation for this plugin can be found at: https://github.com/bezlio/bezlio-plugins/tree/master/Plugins/SQLServer

Start by editing the SQLServer.dll.config file in your Bezlio Plugins directory (C:\Program Files (x86)\Bezlio Remote Data Broker\Plugins by default).  There are two things you are defining in this file. 


Define the Location of Your Queries

The first things to define are the folder locations where you intend to store the query files that will be available within Bezlio.  These are defined in the ‘sqlFileLocations’ section and by default we have configured a folder structure in the Bezlio Queries subdirectory you may utilize or tweak as desired. 

Screenshot of the SQL Server configuration file in Bezlio that allows you to define the folders where you are storing your queries. 

Each sqlFileLocation entry begins with an opening curly brace and ends with a closing curly brace.  The ‘locationName’ will be how it will be presented to users within Bezlio (and can be a more friendly name than the folder may be). The ‘locationPath’ is the actual file system location. 

NOTE:  Any backslash in this path needs to be represented as two backslashes. 


Define Your Database Credentials 

The next things that you will need to define in this file are the credentials available for use to connect to your SQL Server.  This is defined in the ‘connections’ section and by default we have examples for connections named ‘Production’, ‘Test’, and ‘Training’.  You can either retain these connection names or modify them to suit your needs.   For each connection you need to fill in the following:

  • serverAddress: Either the IP address or name of your SQL Server.
  • databaseName: The name of the database on the SQL Server.
  • userName: The user name to connect to this SQL Server.  Note this must be a user using SQL Server Authentication (not Active Directory).
  • password: The password for the specified user name.

Step 2 - Create a Query for Your Database

The next thing that we will need to do is create a query for your database.  Queries can be written using a variety of tools from SQL Server Management Studio to Excel.  Here is an example from an Epicor database where we are summing up invoices by year:

                SUM(Erp.InvcHead.InvoiceAmt) As Amount
                ,CAST(Erp.InvcHead.FiscalYear AS VARCHAR) AS FiscalYear
                Erp.InvcHead with(nolock)
                LEFT OUTER JOIN Erp.Customer with(nolock)
                On Erp.InvcHead.Company = Erp.Customer.Company And Erp.InvcHead.CustNum = Erp.Customer.CustNum
Group By
                CAST(Erp.InvcHead.FiscalYear AS VARCHAR)
Order By
                Amount Desc

When you have created this query using your preferred tool, save it into a text file in one of your sqlFileLocations with a .sql extension.  In our example, I am naming it SalesByYear.sql.  Note that it is possible for parameters to be passed from Bezlio into these query files, but that will be covered on a different topic.


Step 3 - Create a Bezl to Utilize This Query

Now we are finally ready to use this query within Bezlio. 

Select a Template

To begin, log into Bezlio, click the gears icon below your profile picture, click ‘Create Bezl’, then click ‘Templates’. 

You will see a list of the many (and ever expanding) stock templates Bezlio has that will allow you to easily turn your data into a graphical Bezl.  They all follow the same exact steps with different configurable options based on the type. 

For this example, we will pick ‘Column 3D’ – navigate to that template and click the ‘Select’ button below it.

Animated GIF of a Bezlio user go through several steps to drill down to select the template they want to use to build this bezl.

Name Your Bezl

Next provide a name for the Bezl.  This is what this Bezl will show up as within your ‘My Bezls’ tab when you go to add it to your panels.  We are going to call it ‘Sales By Year’ for this example.

Screenshot of a Bezlio user's screen as they name the Bezl that they are creating.

Configure the Data Connection 

Now fill in each of the following in the ‘Data Connection’ section (see ‘Step5.gif’)

  • Provider: Bezlio Remote Data Broker
  • Connection: Pick your BRDB server here (most users will only have a single choice).
  • Resource: SQL Server
  • Method: ExecuteQuery
  • Context: Pick the locationName here as defined in 1a. 
  • Connection: Pick the connection as specified in 1b that you wish to use (for example ‘Production’, ‘Test’, or ‘Training’).
  • QueryName: Type in the name of the query file you created in step #2 without the extension.  For example, ‘SalesByYear’.
Animated GIF that shows a Bezlio user going through the data connection screen to define the various aspects to provide data to the graphical Bezl.

Configure the Graphical Display of Your Data

Press the next button to tell the template how your data should be used. 

This section will vary based on the type of template you selected.  For example, on a chart we typically just need to know the ‘label’ and ‘value’ columns whereas a grid would be asking you which columns to show and hide.  For my example query I am going to select ‘FiscalYear’ for the label and ‘Amount’ as the value. 

Press the next button. 

An animated GIF that shows a Bezlio user configuring all of the parameters available when defining the graphical features of the Bezl.

Review and Save Your Bezl

At this point you should see a preview of the output and be given an opportunity to tweak any settings available. 

When satisfied press the ‘Save’ button to add this Bezl to your ‘My Bezls’ list. 

Now you can add this Bezl to any panel by (1) creating a view via the drop-down arrow next to the view name and (2) using the ‘Select Bezl’ button.

An animated GIF showing a Bezlio user reviewing the results of the Bezl, going through the steps to save it, and then embedding it into a panel.