How to create parameters that works in Power BI Report Builder (SSRS)

[fellipeao]

Hello!

I'm trying to create an item in Power Bi Report Server (SSRS) connected to Databricks. I can connect normally, but I'm having trouble using a parameter that Databricks recognizes.

First, I'll illustrate what I do when I connect to SQL Server and use a parameter that works.

A = my parameter that I connect to item C;

B = my dataset where I create one parameter that is gonna link with A;

C = my entity dimension that I connect to item A;

In my query dataset (B) I create a paramter using @Entidade that SQL server recognizes as paramter, and after that I link to my parameter (A).

Everything works well and I got the expected result:

Now, when I try to do the same thing connected to databricks, I have problems to set the parameters on query.
In databricks I can use parameters as: {{ Entidade}} or :Entidade, but Isn't work on SSRS, always I get a sintax error.  That parameters works  in SQL editor and notebooks in databricks.

What kind of parameter can be used to solve this problem on SSRS?

example query connected to slq server:

select id, AtualizadoEm,EntidadeId, EntidadeNome, ContratoNome, PedidoId, PedidoDataIntegracao, MarcacaoData
from aco.dbo.Orders with (nolock)
where EntidadeNome = @Entidade

Example query connected to databricks:

select a.dt_periodo_registro, a.nm_entidade, a.nr_orders
from prd.dbo.orders_v2 as a
--where a.nm_entidade = '${entidade}' - nok
where a.nm_entidade = :entidade -- nok but works on databricks
-- where a.nm_entidade = {{entidade}} -- nok but works on databricks
-- where a.nm_entidade = {{ entidade }} -- nok but works on databricks

[-werners-]

working with SSRS can be a pain but it is possible.
Using openquery you can probably pull it off.
That's what we use anyway.  We also do some dirty trick with defining databricks as a linked server and then use stored procedures on the linked server (for those reports that will not work otherwise).  Can't tell you a lot about that because I do not build the reports myself.

[fellipeao]

We can't use linkedserver/procedures because we will stop using SQL management studio and focus only on lake/databricks. But it's a path that would work for now.

Because this we are looking for a solution 100% in databricks.

[rahulmittal092]

Did you find any solution for this?

[fellipeao]

Hello.
No, I didn't. I'm still looking for a solution.
I have tried to use a stored procedure in databricks, but PBI Report Builder still not recognized the parameters.

[J-Usef]

@fellipeao This is the only way I found that works well with databricks since positional arguments (?) was a fail for me. This is the latest version of paginated report builder.
https://learn.microsoft.com/en-us/power-bi/paginated-reports/report-builder/connect-snowflake-databr...

Once connected to databricks, you can choose any random table and then go to advanced editor and make these changes: 

1 - Use Nativequery funtion to be able to insert your SQL query:

Value.NativeQuery(Databricks.Catalogs ... etc
2 - Create a parameter in Powerquery and name it.

3 - Concatenate the Parameter with your SQL query. 

Follow the final steps in the documentation to bind the parameter with your Report parameter under dataset properties. 
Good luck!

[fellipeao]

Hello.
I tried this approach a few days ago, and the parameters work fine on my local machine using my token databricks. I got the expected result after run the query.

The main issue is that I'm building this object to be published on Power BI Report Server, which only supports connections to Databricks via an ODBC driver.
After publishing the object, I’m able to switch the connection to ODBC, test it successfully, and I can also see the parameters. However, when executing the query, it returns an error.

Testing odbc connection (ok):

checking my parameters (ok)

opening the report (ok)

Runnin the report (not ok)

I have tried many configurations, but I'm still have trouble.
Thanks for the suggestion.

[J-Usef]

It will most likely fail because because its a different connector which i dont think would work with powerbi server.

Your only bet is to try with positional arguments since you are using ODBC.

Select * from table where columnx = ? And columny = ?

Check this article out: https://medium.com/@kyle.hale/passing-multi-valued-parameters-from-power-bi-paginated-reports-to-dat...

It explains the steps in detail.