July 10, 2015 at 9:13 am
Hi
I have a stored procedure which returns a result set as follows:
(Headers)Total,WV1,WV2,WV3,WV4,WV5.....
(Example data) "Some total name",1,2,3,4,5.....
The WV1, WV2, WV3 column names will be different depending on parameters passed to the stored procedure. In other words, the column names or number of columns aren't fixed (apart from "Total").
What I would like to be able to do is to just force SSRS to use the column headers supplied by the stored procedure as the column names in the report. I am fairly new to SSRS.....
Is this possible?
Thanks
Jonathan
July 10, 2015 at 11:26 am
99zardoz (7/10/2015)
HiI have a stored procedure which returns a result set as follows:
(Headers)Total,WV1,WV2,WV3,WV4,WV5.....
(Example data) "Some total name",1,2,3,4,5.....
The WV1, WV2, WV3 column names will be different depending on parameters passed to the stored procedure. In other words, the column names or number of columns aren't fixed (apart from "Total").
What I would like to be able to do is to just force SSRS to use the column headers supplied by the stored procedure as the column names in the report. I am fairly new to SSRS.....
Is this possible?
Thanks
Jonathan
Simple answer? No. Can't be done, not no way, not no how. However, it doesn't mean you can't come up with a solution. It's just going to be rather complex. You'll need to output ALL possible column names, which means modifying the stored procedure to have a single common set of output columns, and within SSRS, use the parameters to determine which columns are visible, as you'll need to have all of them within the report. If the number of columns makes this impractical, then you'll either need a larger paper size, or you may be stuck with never printing as well as having to scroll the report left and right. You may be able to export to PDF, but I don't know if PDF can support a custom page size or not.
I also wonder, given the requirement, whether this might be better handled as separate reports, especially if the parameters differ between what you would have for each individual report.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 10, 2015 at 11:42 am
In short - no, not really, at least not from the few times I've used SSRS. Even if it were possible - what do you hope SSRS would add to that process?
SSRS is expecting a defined data set (with pre-defined column names). Now you could fake this by creating a report with LOTS of generic column names, and dumping your "real" data into enough of the generic columns as required and allow SSRS to hide the rest of them, but that's ultimately the extent of it.
For something completely wide open I'd lean towards simply dumping it into EXCEL as a query. Even then you'd have to not exceed the maximum number of columns allowed in Excel.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 10, 2015 at 1:04 pm
Matt Miller (#4) (7/10/2015)
In short - no, not really, at least not from the few times I've used SSRS. Even if it were possible - what do you hope SSRS would add to that process?SSRS is expecting a defined data set (with pre-defined column names). Now you could fake this by creating a report with LOTS of generic column names, and dumping your "real" data into enough of the generic columns as required and allow SSRS to hide the rest of them, but that's ultimately the extent of it.
For something completely wide open I'd lean towards simply dumping it into EXCEL as a query. Even then you'd have to not exceed the maximum number of columns allowed in Excel.
Matt,
The use of generic column names and just stuffing data in makes for a complete nightmare with more than 5 or 6 columns, and utterly destroys the idea of using dates in many cases, as you end up having to do so much work in the report that it becomes impractical. Been there, tried it, bad idea, start to finish. Best way is to include all possible columns, and use a separate report section for each possible combination of parameters that results in a different set of valid output columns. Even that can get really ugly.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 10, 2015 at 8:46 pm
sgmunson (7/10/2015)
Matt Miller (#4) (7/10/2015)
In short - no, not really, at least not from the few times I've used SSRS. Even if it were possible - what do you hope SSRS would add to that process?SSRS is expecting a defined data set (with pre-defined column names). Now you could fake this by creating a report with LOTS of generic column names, and dumping your "real" data into enough of the generic columns as required and allow SSRS to hide the rest of them, but that's ultimately the extent of it.
For something completely wide open I'd lean towards simply dumping it into EXCEL as a query. Even then you'd have to not exceed the maximum number of columns allowed in Excel.
Matt,
The use of generic column names and just stuffing data in makes for a complete nightmare with more than 5 or 6 columns, and utterly destroys the idea of using dates in many cases, as you end up having to do so much work in the report that it becomes impractical. Been there, tried it, bad idea, start to finish. Best way is to include all possible columns, and use a separate report section for each possible combination of parameters that results in a different set of valid output columns. Even that can get really ugly.
Agreed - we got away with it because our variability was under control (3-4 "flavors and no more"), but you're right - I didn't have much to worry about fancy formatting. None of this is a great idea either way. Thanks for the redirect.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 13, 2015 at 2:48 am
Thanks for the replies. I had a feeling it would not be possible. Having done a fair bit of Crystal, I know it wouldn't be possible there and SSRS seems very similar in Crystal in many areas.
What I think I will do is get the stored procedure to always output 12 columns labelled Col001 to Col012. For the scenario I am covering, 12 should be enough. (Its about "phases" within a project which normally correspond to month names, but sometimes don't sadly so a date based calculation is out).
I'll then pass back an extra field containing a comma-separated list of what I actually want the columns to be, but I wont show this on the report.
I'll try and use a formula function to work out the column names e.g. getColumnName(x) which will just look at this value for the 1st row.
Frankly, I would prefer not to use SSRS at all in this case and just write an app to build an Excel sheet containing the output from my stored proc, but I have been told to use SSRS it will make it easier for the report to be distributed via the intranet.
If anyone has any comments on my harebrained idea, they are welcome:cool:
July 13, 2015 at 3:03 am
You could put in a work around that might work out better for SSRS but not so great for your query.
Simply pivot the columns that are variable and make them rows. Then reference this new set as a matrix report.
Basically take the dataset
id Name City1 City 2 City 3
1 Andrew newYork Seattle Houston
and make it
id Name City
1 Andrew New York
1 Andrew Seattle
1 Andrew Houston
Then make the city column as part of the column group for Matrix report.
This way the column names and the number of columns are automatically adjusted .
July 13, 2015 at 7:59 am
99zardoz (7/13/2015)
Thanks for the replies. I had a feeling it would not be possible. Having done a fair bit of Crystal, I know it wouldn't be possible there and SSRS seems very similar in Crystal in many areas.What I think I will do is get the stored procedure to always output 12 columns labelled Col001 to Col012. For the scenario I am covering, 12 should be enough. (Its about "phases" within a project which normally correspond to month names, but sometimes don't sadly so a date based calculation is out).
I'll then pass back an extra field containing a comma-separated list of what I actually want the columns to be, but I wont show this on the report.
I'll try and use a formula function to work out the column names e.g. getColumnName(x) which will just look at this value for the 1st row.
Frankly, I would prefer not to use SSRS at all in this case and just write an app to build an Excel sheet containing the output from my stored proc, but I have been told to use SSRS it will make it easier for the report to be distributed via the intranet.
If anyone has any comments on my harebrained idea, they are welcome:cool:
What do you do with numeric or date columns? You won't be able to just change data type on any given column. The data type has to be fixed. That's why this kind of thing is so problematic. Ever tried to do all your fancy formatting in T-SQL ? You'll have no choice but to CAST every single field to varchar, and then control ALL of the formatting from within your query. Good luck with date parameters - you just lost the ability to let SSRS provide a quick calendar to select a date from. Hope it works out for you...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 14, 2015 at 4:58 am
What do you do with numeric or date columns? Format them how I want them in SQL and treat them as text
Ever tried to do all your fancy formatting in T-SQL ? Yep, quite a lot. It's easier in SSRS/Crystal, but sometimes limitations mean you need SQL to fall back on
You'll have no choice but to CAST every single field to varchar, and then control ALL of the formatting from within your query. Seemingly so but Jayanths idea took me down another path.
Good luck with date parameters - you just lost the ability to let SSRS provide a quick calendar to select a date from. Hope it works out for you... Thank you my friend 😀
Actually, I followed Jayanths suggestion to use PIVOT (or actually UNPIVOT) to allow SSRS to pivot the data itself, which seems to be working. Its a bit annoying, as the data is already pivoted in the sproc so I'm just unpivoting to allow SSRS to pivot it but at least I have what I want, and the UNPIVOT isn't making much difference performance wise so far.
Thanks for all your suggestions and to Jayanth for his solution.
July 14, 2015 at 12:31 pm
Based on your initial post, I judged the likelihood of Jayanth's methodology actually being able to work with data that you had indicated would be different number of columns depending on parameters, seemed highly impractical. If your data was that easily categorized, and had you mentioned that fact, a solution would have been offered much sooner. As always, the less detail provided up front, the longer it takes to get there. To be honest, you probably lucked out that someone was willing to not just think outside the box, but to ignore what appears to be common sense, and go in that direction instead of where your post naturally leads.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply