My requirement is to call a stored procedure in snowflake from power bi by passing parameters dynamically. The function of the stored procedure is to update the output dataset ('Change Table') with data changes between the current and previous snapshot time.
I am using native query (Query1) to Call stored procedure in Direct mode. e.g. Call schema.Storedproc_name('" & P_Table_Name &"','" & P_Prev_Snap &"', '" & P_Cur_Snap & "')
I am passing 3 parameters to the stored procedure in the Call - Table Name, Current snapshot time, Previous snapshot time. I have created 3 parameters in power bi P_Table_Name, P_Cur_Snap and P_Prev_Snap.
In Manage Parameter, for parameter P_Table_Name, I have given the list of table names in the 'List of Values' under 'Suggested value' dropdown . For parameter P_Cur_Snap, the Type is text, Suggested Values is Any value and Current value is 2024-01-02 06:52:29.652. Similarly defined the parameter P_Prev_Snap - the Type is text, Suggested Values is Any value and Current value is 2024-01-01 11:01:06.644.
In Advanced Editor, I am using 2 Native queries(Query 2 and Query 3) in Import mode to create 2 tables ('CurSnapshot List' and 'PrevSnapshot List') for the values of parameters P_Cur_Snap and P_Prev_Snap.
In Model view, using 'Bind to parameter' option I have bind the parameter P_Cur_Snap to the column CUR_SNAP of table 'CurSnapshot List' and bind the parameter P_Prev_Snap to the column PREV_SNAP of table 'PrevSnapshot List'.
In the Report view I have 2 slicers for these 2 fields CUR_SNAP and PREV_SNAP.
I see that the Call to snowflake stored procedure is successful and the output table is updated with changes between the Current value of current snapshot time and previous snapshot time ( i.e; changes between P_Cur_Snap = 2024-01-02 06:52:29.652 and P_Prev_Snap = 2024-01-01 11:01:06.644).
I have another native query(Query 4) in Direct mode to select the data from the 'Change table' dataset for the user selected CUR_SNAP and PREV_SNAP values. e.g. SELECT * FROM database.schema.table_name WHERE PREV_SNAPSHOT = '" &P_PRV_SNAPSHOT &"' AND CURR_SNAPSHOT = '" &P_CUR_SNAPSHOT &"'.
In Report view, i see that the report shows change data for P_Cur_Snap = 2024-01-02 06:52:29.652 and P_Prev_Snap = 2024-01-01 11:01:06.644.
But when I change the value in any of the slicers CUR_SNAP and PREV_SNAP, the call to SP is not getting triggered and the changed snapshot time is not being passed via parameters to SP. This is working only for the Current values given in the parameters definition but not for dynamic values.
I am not sure if I have missed something here, please help!
