Stored Procedure to Filter on a Dashboard

#1

Hi,

I am trying to connect a filter to a stored procedure. My problem is that I have a stacked bar chart visualization with ~15,000 bars. This means the dashboard can hardly load (takes about 5-10 min to load initially and after each filter action).

Ideally I would attach the filters to a stored procedure and pass “dummy” parameters to limit the number of displayed records. The user should then still be able to select their filter criteria from the dropdown.

For example, one of the filter criteria is called “Benefit Type”. I do not want the user to have to remember all 150+ Benefit Type codes, but instead, be able to click the filter and see all the options listed to then select which they would like to see information for.

I have tried passing parameter values from dashboard to stored procedure, stored procedure select, and a manual select but have had no luck.

Should I try a bridge parameter? If so, where can I find some documentation to help me with this?

TIA

#2

Hi Pierre,

You can find info on the Bridge Parameters here:
https://www.dundas.com/Support/learning/documentation/cleanse-consolidate-modify-data/using-a-bridge-parameter

But, have you simply tried to set ‘none’ as default values for you filters? Like this as long as the user hasn’t set them, no data will be returned.

Or, set in your Stored Procedure “If” clause(s) that will force to return no rows, until the parameters have a valid value (“If @MyParameter IS NOT NULL”), link the viewable Dashboard’s filters to the result query sent by your stored procedure (not to the parameters of this stored procedure), and launch a copy thru script of the values set in those filters to hidden filters that are connected with the Bridge Parameters linked to the parameters of the Stored Procedure, on a click event of a button for example.

I hope this helps,
Olivier

1 Like