Forum Discussion
Helper IDeclaration in SQL to parameters instead
My solution to this was to replace the declared values entirely in each instance with my parameters. The Parameters also had to be in TEXT form and not DATE or it didn't work. So treating them like injecting a string:
Deleted the first two lines entirely (Declare ....), and then replaced any instance of @startDate with '" & startDateParam & "' and did the same with endDate.
This had to be done in Advanced query editor, and then I had to go thru and clean up my query in there because I had started in the normal SQL Query box.
What I learned is if you do this just delete all of the data between query=" ", and then re-copy your query directly into that area with the changes already made (Notepad++ worked for me). This solved my issue entirely.
One downside and what I didn't know is that end-users cannot edit parameters, only the report owner can. So it doesn't solve my issue in that regard, but I did at least get the parameters to also come into PowerBI Service, and it DOES solve another issue I was having with a different report that only I run and wanted to alleviate it connecting to me directly anymore and just run it on the service. 🙂
Helper IMy solution to this was to replace the declared values entirely in each instance with my parameters. The Parameters also had to be in TEXT form and not DATE or it didn't work. So treating them like injecting a string:
Deleted the first two lines entirely (Declare ....), and then replaced any instance of @startDate with '" & startDateParam & "' and did the same with endDate.
This had to be done in Advanced query editor, and then I had to go thru and clean up my query in there because I had started in the normal SQL Query box.
What I learned is if you do this just delete all of the data between query=" ", and then re-copy your query directly into that area with the changes already made (Notepad++ worked for me). This solved my issue entirely.
One downside and what I didn't know is that end-users cannot edit parameters, only the report owner can. So it doesn't solve my issue in that regard, but I did at least get the parameters to also come into PowerBI Service, and it DOES solve another issue I was having with a different report that only I run and wanted to alleviate it connecting to me directly anymore and just run it on the service. 🙂