Hi Folks,
So I've created a Dynamic SQL query for WordFly Pages (Survey) results with column headings being the Questions and Answers that the kids over here come up with. They change for each different survey obviously.
Am I right in thinking that I can't run that as a Stored Proc to SSRS because the fields are ever changing? And if so will I have to run the Dynamic SQL as a Dataset Query in SSRS directly?Many thanks,
HeathAn eg: on one possible test output with 3 question fields
Like Chris said, it is usually pretty fiddly, but you should be able to accomplish what you want. It is going to be a matter of making sure your design is flexible enough to accommodate the potentially expanding columns of questions off to the right.
For example, I created a custom, SSRS version of the Ticket Sales by Period report which has a matrix with the rows grouped as the standard report does while the columns are grouped based upon the categories listed. Looking at MOS category, for example, it is theoretically possible for that to stretch off to the right until the end of time as long as someone creates another MOS grouping and at least one ticket is purchased or returned in an MOS in that grouping (fortunately, we have pretty much reached our limit already).
If you intend to proceed, I recommend doing something similar. Using a row grouping on the patron and a column grouping on the survey questions will allow you to expand out to the right without having to specify the questions individually. You might also want to encourage restraint on the number of questions asked.
Thanks Guys,
As I thought I was making is WAY to hard on myself. I've popped it in a simple matrix and now I can fake the beautiful Dynamic SQL table by unpivotting it.
Also "restraint" ... I had to google the meaning of that word. I can't say we've ever used it