Using SQL to override a parameter built into the data source

[peebes1]

My SQL Server database has an optional "fiscal year" parameter.  If no value is chosen at the database level, the default is to return data for only the current fiscal year.  I can't figure out how to use SQL to get Power BI to interact with the parameter and let me choose the fiscal year(s) I want.  If I use a SELECT statment with a WHERE clause for the fiscal year, it is too late, and the database is only sending through current fiscal year data.  In Tableau, I could use the OpenTable command (example below) to take control.  Is there a way I can get Power BI to do this as well?

OpenTable("Beneficiary"."Frequently Used Fields",
"Choose one or more Fiscal Years"= "[2023, 2024]"
) 

[v-venuppu]

Hi @peebes1 ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[peebes1 · Accepted solution]

Hello,

I was able to resolve my issue by entering the SQL below into Power BI's SQL Server connector. Previously I was missing a table alias.  Please note, we use a product called Senturus Analytics Connector, which connects Power BI to Cognos Analytics (our data source):

select * from OpenTable( "Beneficiary"."Frequently Used Fields",

"Choose one or more Fiscal Years"= "[2023, 2024]") as T

[v-venuppu]

Hi @peebes1 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Akash_Varuna for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..? If any of the responses has addressed your query, please accept it as a solution, so other members can easily find it.

Thank you.

[v-venuppu]

Hi @peebes1 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Akash_Varuna for the prompt response.

Use a native SQL query in Power BI combined with a parameter that lets users select the fiscal years:

Create a parameter in Power BI to let users pick one or more fiscal years.Use a native SQL query (via "Advanced options" in the SQL Server connector) that includes a placeholder for fiscal year filtering.Edit the M code in Power Query to dynamically insert the selected parameters into the SQL query before it’s sent to the server.Ensure you query a table or custom SQL,not a view/function that already applies default filtering.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly.

Thank you.

[Akash_Varuna]

The default behavior of Power BI’s SQL Server connector does not allow you to pass parameters directly into the database query. To address this, create a stored procedure in SQL Server that accepts fiscal years as input and dynamically retrieves data based on the parameter. Use Power BI to execute this stored procedure via DirectQuery and bind it to a parameter in Power BI for user selection.