Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I have a dashboard for which data is coming from stored procedures in SQL server. I want my dashboard to have the lastest data.
Can anyone suggest me how yo automate the data load into Power BI with system date.
The stored procedures run when we give system date as input parameter.
Please suggest
Solved! Go to Solution.
HI @neelofarshama,
It seems like you missed the right 'quotation' in your query that stored the 'store procedures', please add it to confirm if the issue fixed:
Source =
Sql.Database(
"test",
"test",
[
Query =
"DECLARE @return_value int EXEC @return_value = [dbo].[usp_Response_SLA] @date = "
& Date.ToText(Date.From(DateTime.LocalNow()),"MM/dd/yyyy")
]
)
Notice:
1.'02-09-2021' will be generated by the right part M query function, you not need to add them to your query.
2. I also modify the generated date functions to get the same format as you shared.
Regards,
Xiaoxin Sheng
Hi @neelofarshama,
I think you can try to parameterize your connection string to send the current system date and use it as a parameter to get data.
M query DateTime.LocalNow
Regards,
Xiaoxin Sheng
Thank you for the reply can you please elaborate the steps in details.
Hi @neelofarshama,
You can invoke your store procedures with the function that I mention.
Sample steps:
1. Past the 'Stored Procedure' string into the SQL statement of your connector: (advanced option)
EXECUTE [dbo].[xxxxxxx] @date= xxxxx
Import data from a database using native database query
2. Enter to 'query editor' and navigate to the 'advanced editor' to modify the steps to concatenate the raw string with 'DateTime' functions:
Source =
Sql.Database(
"test",
"test",
[
Query =
"EXECUTE [dbo].[xxxxxxx] @Date= "
& Text.From(DateTime.LocalNow())
]
)
Power Query and Stored Procedures with Parameters
Regards,
Xiaoxin Sheng
Hi @v-shex-msft ,
Thank you for the reply, I tried using this but its thorwing below error.
The below is my Strored Procedure in Power BI
DECLARE @return_value int
EXEC @return_value = [dbo].[usp_Response_SLA]
@date = '02-09-2021'
& Text.From(DateTime.LocalNow()) -----------------I added as per your suggestion here
SELECT 'Return Value' = @return_value
HI @neelofarshama,
It seems like you missed the right 'quotation' in your query that stored the 'store procedures', please add it to confirm if the issue fixed:
Source =
Sql.Database(
"test",
"test",
[
Query =
"DECLARE @return_value int EXEC @return_value = [dbo].[usp_Response_SLA] @date = "
& Date.ToText(Date.From(DateTime.LocalNow()),"MM/dd/yyyy")
]
)
Notice:
1.'02-09-2021' will be generated by the right part M query function, you not need to add them to your query.
2. I also modify the generated date functions to get the same format as you shared.
Regards,
Xiaoxin Sheng
@neelofarshama , refer if this older solution can offer any help
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |