The Bold BI allows you to use the stored procedure defined in the SQL Server database using the
Microsoft SQL connection.
NOTE: You can connect to a database hosted in the Microsoft SQL Server whose version should be 2012 or above.
Bold BI supports the following connection modes for the stored procedure connection.
In this OpenQuery mode, Bold BI executes the stored procedure queries on the specified linked server for every execution in the stored procedure data source.
Enable the Data Access for the stored procedure in SQL Server by executing the following query.
EXEC sp_serveroption 'SQLServerName', 'DATA ACCESS', TRUE
NOTE: If you did not enable the Data Access in SQL Server, you cannot access the stored procedure with OpenQuery option in Bold BI.
This OpenQuery only supports the Microsoft SQL Server and Azure SQL Database (Managed Instance only). All other servers like Azure SQL Database, Azure SQL Data Warehouse (Azure Synapse Analytics), and Parallel Data Warehouse does not support this OpenQuery.
In Internal Cache mode, Bold BI executes the stored procedure completely and moving the result into a temporary table. After that, Bold BI uses the created temporary table for every action execution in the stored procedure’s data source.
To connect the SQL Server database, refer to this link Microsoft SQL.
If it succeeds, you can get into the data design view with the available stored procedure displayed on the left pane of the tree view. The available parameters can also be displayed while expanding the procedures.
You can drag the desired stored procedure into the canvas area to create a table view with the supplied parameters.
Here, you can choose either OpenQuery or Internal Cache for your stored procedure connection.
The Dashboard Designer allows you to edit the supplied parameters by using the edit parameters available in the created table.