How to call a stored procedure from PowerBi report

[rohitpaliwal]

Frnds,

I've got four drop downs in my report

1. Country

2. State

3. City

4 Store

the navigation is working fine, but I've a requirement to take this to next level i.e. in each drop down I've option to select either one or all, now once the selection is made, I want to use this as a input parameter for a stored procedure which resides in my sql server database.

Is this possible

If yes then how

[v-shex-msft]

HI @rohitpaliwal,

Current you can only invoke SP at import mode, live mode(direct query, live connection) not support.

Reference:

Call an SP

Regards,

Xiaoxin Sheng

[rohitpaliwal]

I understand that in the new query editor I can use the stored procedure but how do I populate the parameter from the drop down visualization i.e. slicer in my case, first will list country, second will have states, third will have city and fourth will have storeIDs, now I can either have all selected or any indivudial values and on the basisi of this select tion, my procedure should execute like below

exec dbo.myproc countryid = @countryvar,  stateid = @statevar, cityID = @cityID, storeDi = @storeID

[v-shex-msft]

Hi @rohitpaliwal,

Nope, it is impossible to post visual value as parameter to power query, you need to use query parameters to instead.

Regards,

Xiaoxin Sheng

[rohitpaliwal]

Is it possible to capture the selection in Page1 (using any method) and once user has selected the options then on the basis of the selection on page1, I can redirect it to next page where I'll get data on the basis of selection on page 1?

[v-shex-msft]

Hi @rohitpaliwal,

>>Is it possible to capture the selection in Page1 (using any method) and once user has selected the options then on the basis of the selection on page1, I can redirect it to next page where I'll get data on the basis of selection on page 1?

Maybe you can try to use the drill throught filter feature.

Use drillthrough in Power BI Desktop

Regards,

XIaoxin Sheng

[Greg_Deckler]

Are you importing the data from SQL or connected live?

[rohitpaliwal]

Currently I'm importing data, and all related tables for these drop down are present into my power Bi desktop, alternative,y can I rewrite the stored procedure login into power bi? i.e. all the data manuplation etc.