October 24, 2007 at 6:18 am
Has anyone had any luck pulling data from a dynamically generated table into a Reporting Services report?
My DataSet is a stored proc which generates the columns and rows on the fly. Because it is possible (in the future) to generate new columns based on data entered into a base table, I don't want to physically add fields to the dataset in SSRS. I also need to be able to access the row data by a parameter (RecordCreatedOn), so I can't simply use the Proc to generate a temporary physical table and do a Select * on that table.
To make matters worse, this is a dynamically generated pivot table. @sigh.
Any thoughts?
October 24, 2007 at 6:30 am
October 24, 2007 at 6:49 am
That's a wonderful resource, but unfortunately, it doesn't help me in this particular situation for several reasons.
1) I've already written my dynamic pivot script as T-SQL and it works great.
2) My looming deadline doesn't take into account giving me time to learn VB / C# .Net so I can re-write my code into a CRL assembly.
3) The columns in my table are dynamic. The dataset example shown in the article has a script that pulls by specific column names. This doesn't work for me because I never know how many columns I'll have at the end of the script generation. And I don't want to have to re-write the report's dataset every time there's an event that generates a new column.
As a side note, I had an epiphany (I thought). I had the Proc insert the data into a new permanent table and did a SELECT on that...
Exec MyStoredProc @Param
Select * from MyTable
...but that didn't work either. Still no fields available in SSRS for me to pull into the report...
There has to be a way to do this....
October 24, 2007 at 9:04 am
It occurs to me that what I really need to do, to keep the table dynamic, is to make an image out of the result set of the Proc and display the image at the top (or bottom) of the report.
Has anyone ever done something like this before? I'm not talking making a chart of out if. Just a snapshot (so to speak) of the rows and columns pulled back from a stored procedure.
Let me know. BTW, I don't think I can make this a table valued function because I use Temp Tables in the query and ISTR that I keep getting errors when trying to create/use Temp Tables in functions.
Thanks in advance for any thoughts that might help!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply