Forum Discussion

vishy4271's avatar
vishy4271
Frequent Visitor
7 years ago
Solved

Dynamic Data Refresh - Power BI Service

We have a requirement wherein the source for the powerbi report is a stored procedure in a database the output data of which needs to be imported in the report monthly.
 
Issue :. The stored procedure is a parameterized one where in the parameter is a start date parameter.
The parameter values needs to be the 3rd day of the month when ever we do a schedule refresh.
 
The query within the stored procedure is:
 
Select * from table where date < @ startdate 
Wherein the @startdate is the sproc parameter.
 
And the value needs to be dynamic based on month.
 
Eg: 
For 28th Nov the parameter should be 3rd Nov
For 1st Dec the parameter should be 3rd Dec.
 
I saw the invoke function of the sproc source of powerbi but the parameter value is to be selected manually from the calendar option but we want it to be dynamically selected based on getdate function.
  • Anonymous's avatar
    Anonymous
    7 years ago

    I have a similar stored procedure where i need to pass along a parameter for start and end dates that i'm calculating in a function. 

     

    So my source line looks like:

    Source = Sql.Database("MyInstance.database.windows.net", "SQL-DW-PROD", [Query="Exec MyDataBase.[sp_MyProcedure] 0, " & fnStartDate & ", " & fnEndDate", HierarchicalNavigation=true, CommandTimeout=#duration(0, 0, 90, 0)])

    Then i have another 2 queries as the functions, named fnStartDate and fnEndDate where i can calculate those exact dates.  They can look as based as this:

     

    let
        Result = "'2016-01-01'"
    in
        Result

    Or you can get more complex and calculate a date like this:

    let
        CurrentMonth = Date.Month(DateTime.LocalNow()),
        CurrentYear = Date.Year(DateTime.LocalNow()),    
        Result = Number.ToText(CurrentYear) & "-" & Number.ToText(CurrentMonth) & "-03"
    in
        Result

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    I have a similar stored procedure where i need to pass along a parameter for start and end dates that i'm calculating in a function. 

     

    So my source line looks like:

    Source = Sql.Database("MyInstance.database.windows.net", "SQL-DW-PROD", [Query="Exec MyDataBase.[sp_MyProcedure] 0, " & fnStartDate & ", " & fnEndDate", HierarchicalNavigation=true, CommandTimeout=#duration(0, 0, 90, 0)])

    Then i have another 2 queries as the functions, named fnStartDate and fnEndDate where i can calculate those exact dates.  They can look as based as this:

     

    let
        Result = "'2016-01-01'"
    in
        Result

    Or you can get more complex and calculate a date like this:

    let
        CurrentMonth = Date.Month(DateTime.LocalNow()),
        CurrentYear = Date.Year(DateTime.LocalNow()),    
        Result = Number.ToText(CurrentYear) & "-" & Number.ToText(CurrentMonth) & "-03"
    in
        Result

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Something to add to this response, depending on how picky your stored procedure is for the date you pass, you could format the date more or change it to a Date Type.  I'm concerned with things like whether your stored procedure needs a 2 digit month.