Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Passing a Date Parameter Through SQL Statement - Refreshing in the Service

Hi,   I have a number of parameters passing through SQL statements which refresh without any issue in Power BI Desktop. But when I schedule a refresh in the Power BI Service it falls over. I've nar...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi,

     

    I decided to split out the Date parameter into 3 separate number parameters, then pass these through the SQL, as follows:

     

    = Sql.Database("SERVER", "DATABASE", [Query="SELECT * FROM [TABLE] WHERE clientParent_ID='"&Number.ToText(#"Client Parent ID")&"' AND weekStartDate>='"&Number.ToText(#"Day (Number)")&"/"&Number.ToText(#"Month (Number)")&"/"&Number.ToText(Year)&"'"])

     

    I scheduled a refresh of this and it worked without any issues.

     

    So now, when someone opens my report, they see the following:

     

     

    Which then lets then amend the dates for IDs for all queries in the report without having to go into Power Query to do this manually.

     

    Thanks,

    MarkJames

TeigeGao's avatar
TeigeGao
Solution Sage
7 years ago

Hi Anonymous ,

In your scenario, I think you need to check the SQL Server error log to find what has been transferred to SQL Server and what has caused this problem, the query is finally converted to a string before transferring to SQL Server, you will need to check which query can work normally in SQL Server first.

Best Regards,

Teige