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.
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:
SELECT
SUM(Erp.InvcHead.InvoiceAmt) As Amount
,CAST(Erp.InvcHead.FiscalYear AS VARCHAR) AS FiscalYear
FROM
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 DescWhen 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.
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.
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’.
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.
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.
