Search code examples
sql-serverreporting-servicesmedianreportbuilder3.0

Report Builder 3.0 Add Data Elements to an Array

I'm currently working on building a dashboard for some hospital metrics. The state requires that we report median key times. This dashboard will need to display the group median time for each day and the total column will need to display the entire dataset's median.

I'm working with Report Builder 3.0 and SQL Server 2014.

I've built a stored procedure that can calculate all these values accurately but accomplishing the mean this way makes expanding the dataset a bit of a monster when a new field needs to be pulled in.

All the articles (like as this one) I've read for calculating median within Report Builder point to needing to display (or insert and hide) all the data from the dataset. This method seems really hacky to me and is going to make this dashboard an absolute monster to try to manage as it grows.

What I need to know is, is there any way to pass the group values to a custom code function? The report has to have access to the group values at some point or else it wouldn't be able to perform the built in aggregate functions (Sum, First, Last, etc) on these groups. If I can't pass these values as an array to a custom code function, does anybody know how the group aggregate functions are built?

Thanks in advance to any who might have a direction to point me.

Solution

  • You can pass the values from the procedure to a function within SSRS and then have it just display the resulting median. I'll just give you summary to get you started.

    1. Create a hidden parameter that allows multiple values.

    2. Set its available values to pull from the dataset that is populated by your stored procedure.

    3. Also set its default value so that all values are selected by default.

    4. Go to the Code section of the report properties to write your function.

    5. The function will take the parameter as an argument like this Public Function MyFunc(ByVal p1 as object()) as Integer

    6. In the function you can refer to p1.Length and an item in the array like so: p1(i)

    7. In the report you can call the function in an expression like this: =Code.MyFunc(Parameters!p1.Value)

    Hope this points you in the right direction.