SQL Server

The SQL Server query type allows you to query MSSQL databases. It has a few advantages over the classical ODBC query:

• Allows configuring the query with a custom connection string for ad-hoc use.
• Doesn't require ODBC drivers or a pre-defined DSN.
• Significantly better performance (depending on scenario)

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

System Data Source

By default, the query uses a pre-defined source, which you can select from the menu bar:

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.

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

Custom Data Source

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

Select the Custom Data Source radio button and enter a Connection String . The connection string is typically in the form Data Source=<server>\<instance>;Initial Catalog=<database>. The connection string configures several additional options including TCP port, encryption, Kerberos authentication etc.

Under Login, select Specific user and provide credentials for the connection. If you use the same credentials to log in to the database as you used to log in to OmniFi, or if you specified the Integrated Security option in the connection string, you can leave the login option to Current user.

📘

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 11:31:27 SqlQueryClient.RunSqlQuery(): : SQL Statement: SELECT *  
FROM [OmniFiDataMart].[Client] 
WHERE [Country] = 'all' OR [Country] = @p0
Trace/Application : 2025-02-26 11:31:27 SqlQueryClient.RunSqlQuery(): : SQL Parameters: @p0:NL

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.