Forum Discussion

mcody's avatar
mcody
Frequent Visitor
6 years ago
Solved

Modify SQL query paramters from Report running on service

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

Need Help
  • TomMartens's avatar
    TomMartens
    6 years ago

    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 

3 Replies

  • Bamak's avatar
    Bamak
    Helper V
    6 years ago

    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's avatar
      mcody
      Frequent Visitor
      6 years ago

      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's avatar
        TomMartens
        Super User
        6 years ago

        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