Is it possible to dynamically update these two charts as different options are selected from a multiselect dropdown. Currently I have the charts being populated by a named query and functional with single select dropdown. I have a feeling this is a very complicated task.
It shouldn't be difficult at all.
- Set up the named query so that it uses two parameters which will be supplied by the dropdowns.
- Bind each of the charts' datasources to the named query. On the named query parameter options press the fx icon, Properties button and navigate to and select the dropdown
value.
That should be it.
Right - but let's say two options are selected from one of the dropdowns (multiselect) - dropdown value becomes an array of values, the parameter passing won't work.
Ah, sorry. I read that as "multiple dropdowns" whereas you wrote "multiselect dropdown".
Thinking ...
No problem - I have been thinking for days ....
Imagine two tests were selected. I want a second line on time series to populate with data for that. From my understanding the chart reads from a dataset where first column has to be time and any column after that is a line. I feel like dynamically creating columns in sql server is a bit complicated and even if that can be done, how do I manage the time column with the new test results. There has to be a simpler way - maybe a different chart, idk?!! Thoughts?
The results of my deliberations are that you could possibly use SQL's WHERE .. IN syntax.
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
I don't know if named queries can take a list parameter like that in the IN ( ) though.
If you're having trouble you might specify what flavour of SQL you're using as the syntax may vary.
Even if I could someone figure out the size of the value array and loop through it somehow to get the values - I believe that would store all the results in the same column resulting in one line on the chart which would be a misrepresentation of the data.
What I ended up doing is writing a stored procedure that dynamically created columns and executed that stored procedure in a named query. This solution works for one single select dropdown and one multiselect dropdown. Still have to tackle 2 multiselect dropdowns.
The multiselect dropdown should work if your named query parameter is query string type instead of value. Unfortunately i don't have enough experience with this to give an example. It should be noted that this shouldn't be used with any user enterable text as it opens you up to sql injection.
Edit:
I got it working, might not be the best example but here it is.
I created a multiselect dropdown and then a custom property with this script transform (probably could be done with an expression):
Then i created a name query parameter of type QueryString. I didn't realize for a while that you would need to use it in the query like {parameter} instead of :parameter. I used an existing table for this example, and a very simple query.
