Modify SQL query paramters from Report running on service

[mcody]

Hi,

Would likre report user to be able to change start/end date for report (on service) and send parameters to SQL to retrieve dataset.

I can add parameters on Power BI desktop but cannot figure out how to add parameter to report and send to SQL.

Is this possible?

We are running Power BI Pro.

Thanks

[Bamak]

Hi mcody

• On repport you manage dynamic filters (Whatever the content of the dataset , based on tables relationships)

• On PBI service , you can manage query (lg M) parameters  value via DATASET parameters

Here, obviously we are not on something which must be handled by the users of the reports, but controlled by the manager of the dataset
The goal is to optimize the number of lines returned by the query (type folding) to the source
I advise to look at the mechanics set up for the incremental ETL (available for pro for 2 weeks)

A Query M sample :

let
Source = Sql.Database("XXXXXXXX", "YYYYY"),
dbo_DimProduit = Source{[Schema="dbo",Item="DimProduit"]}[Data],
#"Lignes filtrées" = Table.SelectRows(dbo_DimProduit, each [ProduitCode] = codeProduit)
in
#"Lignes filtrées"

This will generate the folding SQL request (show native query)

select [_].[Produit_PK],
[_].[ProduitCode],
[_].[Produit],
[_].[SousFamilleCode],
[_].[SousFamille],
[_].[FamilleCode],
[_].[Famille],
[_].[URL_Photo_famille],
[_].[Valide]
from [dbo].[DimProduit] as [_]
where [_].[ProduitCode] = 'LL1100'

Regards

Christophe 

[mcody]

Hi Christophe,

Thanks for reply.

Is the functionality you mention only available on Premium? We are on Pro only.

I would like the user to interact with the parameter, pick date, date goes to SQL, data for just that date comes back.

Reuirement is to be able to pick one date form  a number of years data, so to import all possible data to service and use a slicer is not practical.

I have done this for paginated report using Report Builder, but these reports cannot be published to service - as far as I know.

I am wondering would Power BI dataflow work for this type of application? I see it looks like it is possible to add parameters to a dataflow.

Have you used these?

Thanks,

Michael

[TomMartens · Accepted solution]

Hey @mcody ,

what you are looking for is not possible, as @Bamak already mentioned. Parameters are not an interactive feature, and for this: using dataflows will be of no help.

Consider using directquery, here data "remains" inside the source system. But be aware, that this means you need a star schema (this means a dwh), if you have to avoid importing all possible data.

Regards,

Tom