Database (OLEDB)

The OLEDB query type allows you to query database which you have OLEDB drivers installed for.

Note that OmniFi isn't tested with any particular OLEDB drivers except Analysis Services and SQL Server, so compatibility may vary depending on your target database system.

OLEDB queries can use either a pre-defined System Data Source or an ad-hoc data Custom Data Source.

Custom Data Source

You can configure a custom connection string for use in ad-hoc scenarios in the settings menu.

Select Custom Data Source and enter the appropriate connection string. Note that with OLEDB, it is important to include the Provider=...connection option, as this determines what database driver to use.

Select Specific user under Login and enter credentials.

📘

In this example we are connecting to the Microsoft Access Northwind sample database file, which doesn't require nor support any credentials. The User and Password fields are therefore left empty.

For reference, this is the complete connection string used to connect the MS Access DB file:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\var\db\AccessNW.accdb;Mode=Share Deny Write;Jet OLEDB:System Database=system.mdw;

Click the refresh button to test your query.

When you are satisfied with your results, click the Save & Run button at the bottom of the window.

System Data Source

Alternatively, you can use a System Data Source defined by your system administrator. Select any of the sources available in the Data Source drop-down in the toolbar.

The data sources available are provided by your system administrator, and will connect to the database using credentials provided for you.

To query data from the data source, enter an SQL command text in the text field and click the refresh button.

📘

There are a couple of subtle differences between using a System Data Source and a Custom Data Source.

When using a System Data Source, the alias name ("DatMart" in the example above) is saved to the query and used for execution. The definition of the alias can be different between OmniFi systems, so when you move a report from Development to Production it will transparently switch between the Development and Production database. Custom Data Sources instead use the exact same connection string regardless of which system the report is executed under.

The availablility and credentials used with System Data Sources is individual on user/group/role level as the administrator has chosen to configure it. Users that shouldn't have access to the data source will not be able to execute the report. When you use a Custom Data Source with Specific user, anyone running the report will have query data with the credentials you provided, which depending on scenario can be a security risk.

Any time you need to use a Custom Data Source, consider if it would be prudent to request a System Data Source from your administrator instead. You can always develop your report on a Custom Data Source and switch it to a System Data Source before publishing it.

Parameterization

You can parameterize your query using either parameters or Excel references. Typically it is recommended to use parameters over Excel references, since parameters will be usable also if you publish your report as an interface, Access API endpoint or OmniFi DataMart model. Excel references, on the other hand, are convenient if you need to reference some value calculated in Excel in a pure Excel report.

To add a parameter to the query, place the cursor where you want to insert the reference and click the Insert Parameter button.

The parameters dialog will pop up and ask you to create a parameter by clicking the Add button in the Parameters window menu. Note that you must select the appropriate data type and enter a suitable name for the parameter.

When you click the OK button in the Parameters window, a reference on the form {@ParameterName@} is inserted into the SQL text at the cursor location.

To add an Excel reference, place the cursor where you want to insert the reference and click the Insert Excel Reference button.

The reference dialog will appear and let you select an Excel cell.

When you click the accept button in the dialog an Excel reference on the form @@EXREFnn is inserted into the command text.

📘

Although the parameter and Excel references appear in-line in the command text, values aren't inserted in-line. Instead, SQL parameters are created with the parameter values and the parameter references are inserted in-line in the command text. This is done for security reasons, as it prevents SQL inject attacks when the query is executed. You can see the effect of this in the application log:

Trace/Application : 2025-02-26 16:50:57 SqlQueryClient.RunSqlQuery(): : SQL Statement: SELECT * FROM [qryOrder] 
WHERE ? = "all" OR StatusName = ?
Trace/Application : 2025-02-26 16:50:57 SqlQueryClient.RunSqlQuery(): : SQL Parameters: @p0:New, @p1:New

This has two implications:

• You should not wrap string arguments in quotes.
• You can only parameterize values that support SQL parameterizzation, i.e. not object or column names. Constructs like select * from {@TableName@}will not work.