Connect to Microsoft SQL Server Analysis Services(SSAS) data source

The Bold Reports^® allows you to connect with Microsoft SQL Server Analysis Services (SSAS) database.

How to create Microsoft SQL Server Analysis Services(SSAS) data source

To configure the Microsoft SQL Server Analysis Services(SSAS) data source, follow these steps:

1. Click on the Data icon in the configuration panel.
2. In the DATA configuration panel, click on the NEW DATA button.
3. In the connection type panel, choose the Microsoft SQL Analysis Services data source type.

Connection properties

After clicking the data source, the NEW DATASOURCE configuration panel opens.

Specify the data source name without special characters, in Name field.

In Server Name field, you need to select existing server in the local network from the drop-down list or specify the specific remote server name like myserver.domain.com.

In Authentication Type field, choose Windows or SQL Server authentication.

Choose or enter an existing valid database. e.g. AdventureWorks.

Click on the Connect to proceed with query design pane. Now, enter the required query and execute. Its corresponding values will be shown in grid for preview.

Only query mode is supported for Microsoft SQL Server Analysis Services data source.

Click Finish to save the data source with a relevant name to proceed with designing report.

Authentication types for basic connection

You can set authentication type, either as Windows or SQL server.

If your data source supports authentication directly through Windows Credentials(Integrated Security), choose Windows under Authentication Type.

If your data source requires username and password to authenticate. Choose SQL server under Authentication Type and input the username and password for server name mentioned in Server Name field.

Enable the Save password option to embed the credentials within the report when saving it in Report Server.

On report download action, the credentials will be not be saved with report data.

Create Microsoft SQL Server Analysis Services(SSAS) data source with advanced options

To connect the Microsoft SQL Server Analysis Services database with advanced options, click on the Advanced Options in New Data panel.

Now, the panel is switched to advanced options. In advanced options, you can build your own connection string.

Set the connection string and authentication type as required and click on Connect.

Now, enter the required query and execute. Its corresponding values will be shown in grid for preview.

Only query mode is supported for Microsoft SQL Server Analysis Services data source.

Click Finish to save the data source with a relevant name to proceed with designing report.

Click on Basic Options to switch back to the basic connection settings.

Authentication types for advanced connection

You can use windows integrated security, SQL server credentials, prompted credentials, or use no credentials.

Windows - If your data source supports authentication directly through windows credentials(Integrated Security), choose Windows under Authentication Type.

SQL Server - A user name and password must be supplied to access the Microsoft SQL Server Analysis Services(SSAS) database, the credentials might be for a database login. The credentials are passed to the data source for authentication.

Enable the Save password option to embed the credentials within the report when saving it in Report Server.

On report download action, the credentials will be not be saved with report data.

Prompt - When you configure a data source connection to use prompted credentials, each user who access the report must enter a user name and password on preview action to retrieve the data.

None - Choose the authentication type as None, when the authentication details or any other arguments required to connect with the data source are provided in connection string.

Connecting to OLAP Cube Data

When connecting to an OLAP Cube data using SSAS, the query language you use depends on the cube type:

• Multidimensional Cube : Use MDX (Multidimensional Expressions) queries.

  

• Tabular Cube : Use DAX (Data Analysis Expressions) queries.

  

When using DAX with SSAS Tabular models in the Bold Reports^® Designer, consider the following points:

• You can use existing measures defined in the SSAS model without any issues but creating new measures directly in the Bold Reports^® Designer’s query builder is not supported.

• Additive measures (e.g., SUM, COUNT) can be used across related dimensions and are suitable for designing charts, tables, and similar report elements.

• Semi-additive measures can also be applied over dimensions and used effectively in table report items.

• Non-additive measures can be queried and utilized in report items such as text boxes.

Note: Always ensure that the dimension you use has a valid relationship with the table containing the measure to avoid data inconsistencies.