Direct Query using Stored Procedure with a date/time parameter

[kurrysamir]

Hi All,

I have a single date filter which passes the date to the stored procedure. It works perfectly fine using Import Mode.
I need to execute a stored procedure using direct query as the scenario is for every date the user selects, it needs updated data for that particular date.

Have been following this blog
https://prologika.com/power-bi-directquery-with-parameterized-stored-procedure/ to achieve this but unable to get through.
Below is the query text,
= Sql.Database(Server, Database, [Query="SELECT * FROM #(lf)OPENROWSET('SQLNCLI','trusted_connection=yes', 'EXEC Stored Procedure "" & Date.ToText(date/time parameter) & ""')", CreateNavigationProperties=false])
Getting the below error as the,

DataSource.Error: Microsoft SQL: Access to the remote server is denied because no login-mapping exists.
Details:
DataSourceKind=SQL
DataSourcePath=Server;Database
Message=Access to the remote server is denied because no login-mapping exists.
ErrorCode=-2146232060
Number=7416
Class=16
State=2
ConnectionId=1873d7e9-5f71-4ff8-a07d-7a608093ee06

I'm also trying to consider using a table valued function instead using this blog https://blog.crossjoin.co.uk/2022/02/20/tsql-table-valued-functions-and-dynamic-m-parameters-in-powe...

Please advise what is the best way to consume a stored Procedure with a single date parameter using Direct Mode.

Thanks in advance.

[Anonymous]

Hi @kurrysamir ,
Has the issue been resolved on your end? If so, please share your solution and mark it as "Accept as Solution." This will assist others in the community who are dealing with similar problems and help them find a solution more quickly.
Thank you.

[Anonymous]

Hi @kurrysamir ,
I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question.
Thank you.

[kurrysamir]

Hi Pallavi,

Thanks for the response.

I am currenntly using table valued function and my M query looks like below:

let
Source = Sql.Database(Server, Database, [Query="SELECT * FROM [tableValuedFunction]('"&DateTime.ToText(DateTimeParameter)&"', NULL);"]),
#"Filtered Rows" = Table.SelectRows(Source, each true)
in
Source

Using this as a reference Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

The DateTimeParameter doesnt work on single Date Slicer(I managed to do by enabling the right slicer by slicer settings -> style set to Before), gives a warning saying,

Unsupported Filter ->This visual is producing a filter that's not supported for dynamic query parameters.

In the documentation its mentioned as the below are Unsupported filters :

But mine is not either 

• Relative time slicer or filter
• Relative date

Its just a regular date filter. Can you please explain where am I incorrect here?

Also, tried changing the date slicer to a drop down and then it works, like the one below.

Thanks in advance,

Samir Kurry

[Anonymous · Accepted solution]

Hi @kurrysamir ,
Thanks for the update!
The issue is with the slicer style. When you use the "Between" style, even for a single date, Power BI treats it as a range filter, which is not supported for dynamic M parameters. Thats why switching to a drop-down works . It applies a direct, single-value filter, which is supported. Just make sure your slicer is set to List or Drop-down, and the field is from a proper date table.
If you would like to see support for single-date "Between" slicers in the future, feel free to suggest it on the Power BI Ideas forum. Feedback submitted through these channels is frequently reviewed by the product teams and can contribute to meaningful improvements.

Hope this helps.If so,consider accepting it as solution.

Regards,
Pallavi.

[kurrysamir]

Developed using the date drop-down instead of a date picker. Once I choose diferent dates, the direct query fires and gets the updated data. But, I have written in Power Bi Ideas forum to get the same working for a date picker as well. 

[Anonymous]

Hi @kurrysamir ,
Thank you for reaching out to us on Microsoft Fabric Community Forum!
The error you are seeing is likely due to security restrictions on OPENROWSET, it is often blocked by default on SQL Server for DirectQuery scenarios. A more reliable approach is to wrap your stored procedure logic into a table-valued function (TVF) that accepts a date parameter. Then, use Dynamic M Parameters in Power BI to pass the selected date directly to the TVF. This method is much better supported in DirectQuery and avoids login-mapping or remote access issues.

Hope this resolve your query.If so,consider accepting it as solution.

Regards,
Pallavi.

[kurrysamir]

Hi Pallavi,

I would be needing this as a date filter, as using a drop-down to traverse dates behind 3-4 months wont be a feasible solution.

Please correct my understanding here, if use the Import mode on the stored procedure I get the data for that particular date and hence on the charts. By using Incremental refresh can I persist the historical data for the last one-more years? By doing this we ensure we have the latest data for the selected date today would persist, so on next day the user wants to select yesterdays data. Its still available in Power BI Staging.

[Anonymous]

Hi @kurrysamir ,
Thank you again for the follow-up!

Switching to Import mode with Incremental Refresh is a good fit for your scenario with this setup.

You may run your stored procedure or table-valued function to fetch data for the selected date.

Incremental Refresh will automatically load and persist data for past dates.

You may schedule daily refreshes so new data is added each day, and users may freely filter past and current dates using a full "Between" date range slicer , no limitations like in DirectQuery.

Just make sure your stored procedure supports date-based filtering and set up the RangeStart/RangeEnd parameters correctly in Power BI.

Hope this resolve your query.