I have a rather complex report that presents data results from a SQL Server stored procedure in a table then displays charts of the data for each category. The chart is the same data set as the table but with a filter. The table and each chart reference the same data source which in turn references the stored procedure. I discovered that this causes the same rather complex stored procedure to be executed not only for the table but again separately for each chart.
My first thought was to execute this complex stored procedure only on the table while stashing the results into a SQL Server table then have the charts reference only the resultant SQL Server table. However, a temporary table generated within the stored procedure vaporizes at the completion of the procedure. I cannot use a static table as it would cause conflicts between multiple executions of the report from different users. What solutions exist for this situation?