Multiple charts from stored procedures

Dave O

We are using BIRT 3.7.1 with MSSQL 2008 R2.

We have an application that asks several question with all answers posted to an Answer's table with information about the instrument instance, the question, and the answer.

We need to report the results in a report that shows the data in charts using various selections and aggregations.

I created a stored procedure that returns a superset of the data needed for a specific instance.

What is the best approach (maintainability with reasonable execution time)"

Create a stored procedure that does the selection and aggregation for each chart?
Use the superset stored procedure in multiple datasets?
Other?

Thanks,

Dave


(I also put this topic into the Eclipse forum by mistake. Sorry for any confusion - I don't know the relationship between the forums)

bricombs

Good Day
As the stored procedures run in MSSQL and just return the data to BIRT report you will need to do your testing in MSSQL to see which way performs the best. From the networking side the less data you pass across the wire the better you will be from a performance standpoint. So it could be passing a small amount of data across the wire and doing the aggregation in BIRT will be quicker than doing a lot of aggregation in MSSQL and then passing a large amount of data across the wire. However you will need to test to see what works best for you.

Maintainability is more of a design choice. Do you want your code in a SQL stored procedure or in your BIRT report.