Declaration in SQL to parameters instead

[tagban]

I've done a lot of testing and research and am still struggling to get this to work. My SQL team had given me a query like this:

DECLARE @startDate DATE = '2024-03-01';
DECLARE @endDate DATE = '2024-03-21';

WITH .....

The "DECLARE" seems to not work in powerBI when it works fine in SSMS. What I wanted to do anyway was parameters so these could be adjusted over time. However, I'm currently having issues with getting these to work. I have parameters created already both as start_date and end_date but cannot for the life of me get it to work even after trying several suggestions here. Essentially the code is looking for @startDate and @endDate which I suspect can change in the code to reference parameters?

Example of the code:
AND actual_date >= @startDate
AND actual_date < DATEADD(DAY, 1, @endDate)
AND m.is_test_member = 0

[tagban · Accepted solution]

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. 🙂 

[lbendlin]

Read about Value.NativeQuery  .  Or - add your date filters in Power Query and check if the query folds back into SQL code.  (yes, read about Query Folding )