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.