JavaScript code that will communicate with Power BI

[chesterraepined]

Currently I'm using API to call my Stored Procedures as data source to all of my tables in power bi. But as time passed by the database is getting bigger and bigger that power bi can't load it anymore or may took quiet some time to load or refresh the data from power bi tables. So I'm thinking for a solution to have a dynamic parameter Dates (Start and End) that will prompt to ask the users to enter before proceeding on rendering all the visuals of the report.

Is there a way for my Stored Procedure date parameters to be dynamic or changed based on user inputs?  (Javascript is what came first through my mind but don't know how.)

As an example of my web api sp source below, I had to fixed the dtStart to 2018-01-01 and dtEnd to 2018-06-12 of my Stored proc.

= Json.Document(Web.COntents("http://10.106.167.65:7006/Api/WebAPI?sp=PE_ResponseTimeReport&param=%272018-01-01%27,%272018-06-12%27,4,1))

Thanks in advance!

[Anonymous]

Hi @chesterraepined 

I have a slightly different question. How to do call Stored Procedure using API or external event like a click of a button or using PowerBI API. It would be grateful if you could share some of your ideas.

Thank you in Advance.

Cheers!

[Anonymous · Accepted solution]

HI @chesterraepined,

>>Is there a way for my Stored Procedure date parameters to be dynamic or changed based on user inputs?  

You can consider to use query parameter to parametrize your connection query.(you should modify your api to receive query string parameters first)

Deep Dive into Query Parameters and Power BI Templates

Notice:

power bi will disable javascript functions/codes when get data from website.So if your filter is based on javascript functions, I don't think it is possible.

Regards,
Xiaoxin Sheng