Power BI。使用用户参数执行SQL Server存储过程 [英] Power BI. Execute SQL Server stored procedure with a user parameter
问题描述
我有一个Power BI报表和一个带有 @DateFrom
参数的存储过程,该参数返回许多行。我想允许用户选择将传递给存储过程的日期。 (使用DirectQuery模式)
I have a Power BI report and a stored procedure with a @DateFrom
parameter which returns a number of rows. I want to allow user to choose the date which will be passed to stored procedure. (Using DirectQuery mode)
对于这个简单的任务,我有零的解决方案。也许我什么都不懂,但这是一个相当普遍的问题,Power BI开发人员似乎忽略了它。
I have fount zero solutions to this simple task. Maybe I don't understand anything, but it's quite a common problem, which seems to be ignored by Power BI developers.
这是我的存储过程:
EXEC [rpt].[sp_rpt_bids_statictics_2] @DateFrom = '20160101'
现在我想允许用户选择该日期,例如 20160501,然后查询如下:
Now I want to allow user to choose that very date, fore example '20160501' and then the query will be the following:
EXEC [rpt].[sp_rpt_bids_statictics_2] @DateFrom = '20160501'
是否可以在Microsoft Power BI中使用?
is it possible in Microsoft Power BI?
推荐答案
好,我找到了解决方案。
Ok, I have found the solution.
- 添加参数并允许加载。 (例如,其名称为Query_param_1)
- 在导入模式下创建查询(仅此模式允许)
- 然后打开用于创建查询的扩展程序编辑器
- 编辑查询部分:
- [Query = EXEC [rpt]。[sp_rpt_bids_statictics_2] @DateFrom =' Query_param_1']]
- 现在它可以与用户选择的参数一起使用。
- Add a parameter and allow load for it. (for example its name is Query_param_1)
- Create query in Import mode (only this mode allows it)
- Then open extendet editor for created query
- Edit "query part:
- [Query="EXEC [rpt].[sp_rpt_bids_statictics_2] @DateFrom = '"Query_param_1"'"]
- Now it works with the parameter, chosen by user.
这篇关于Power BI。使用用户参数执行SQL Server存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!