June 23, 2014 at 6:38 am
Hey folks,
I'm trying to build a report in SSRS 2012. It is landscape oriented. The data sets are coming back just fine. The top part of the report has a table with one to five rows, plus a totals row - easy enough. The bottom part of the report can have anywhere from one to five stacked column charts that are about 3.4 inches tall and 2 inches wide. I can hide the report if there is no data, but we're trying to get the charts to be centered on the page and the vertical sides flush with one another (so there aren't "missing" charts in between). I see an option for dynamic height and width, but not location.
Any thoughts? If there is there additional information that would be helpful, please let me know and I can try to provide it.
Thanks
Steve
June 23, 2014 at 7:17 am
You didn't say what actually happens when you run this report, and what appearance you actually end up seeing. I've yet to see SSRS 2012, but previous versions seemed to work best when you don't do the dynamic position type of thing, and instead manually decide exactly where on the page the chart goes, and exactly how big that chart will be. There's no reason you can't go down that road, other than the inconvenience of having to actually measure out exactly where things will need to be. One concern I have is the idea behind 5 charts on a single page. This may not be under your control, but generally one or two charts per page is a much more practical way to format information, because when you squeeze 5 charts on a page, you might need 11x17 paper to have much in the way of detail for each chart and still have it useful and readable. Often, squeezing so many charts on one 8.5 x 11 page renders the charts nearly useless or even unreadable, which then ends up begging the question of why choose a chart to begin with.
Just some things to think about...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 23, 2014 at 7:34 am
Hey, thanks for the reply.
Actually when mocked up, in Excel - I think, but not sure how it was done, the charts looked "presentable" as there were only two bars per chart - one for the current period and one for the prior period.
I can lay out all five charts (which represent insurance plans) in 2 inch intervals and if there is/are data for all five plans for a client, the layout looks good as the lower section has five similar looking graphs with data points. What is happening, is that clients seldom have all five, but may have as few as one plan and usually two or three plans. So, the report has empty charts intermixed with populated charts. If there are only two charts with data for client ABC, the desire is to show only those two charts with data in them, centered on the page. However when the report is run for client DEF, or if client ABC added or removed plans, we'd still like to show only those charts with data (whether it be 1,2,3, etc.) centered. Basically dynmically centering the collection of charts and suppressing those without data to show for a particular client.
Sorry if this is clear as mud.
Thanks again,
Steve
June 23, 2014 at 7:43 am
Well... I honestly don't know if SQL 2012 SSRS has that ability. I know the previous versions don't, and I would have to use something like Crystal Reports to get such a thing to happen, if it's even capable. You might try using visibility, based on some means of determining that there's data for a given chart, but somehow, I doubt that the whole thing will end up centered on the page. You might be able to use visibility and then left-align the first chart, with other charts possibly pushing left to fill empty space.
Actually, now that I think about it, how about putting those charts in a subreport so that the subreport is centered? Not sure if that solves the problem or not, though. Maybe combinations of subreports and visibility? I'm likely to be grasping at straws here...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 23, 2014 at 7:58 am
I've considered options similar those, but they all felt "kludgy" so I didn't go far to implement. I kicked around the subreport option a bit, but I thought there would be an easier way. Thank you for your timely feedback. I'll experiment with the subreports. We're already nesting this page and twenty-some others into a main report via subreports - so at least its familiar, albeit slightly unpleasant territory.
Thanks again,
Steve
June 23, 2014 at 8:15 am
Yep. Slightly unpleasant is a rather nice way to say it, though...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 23, 2014 at 12:48 pm
Well, after weighing some options I used a rectangle on the main report with subreports calling each of the five charts individually. I them left justified all five subreports, pushing each subreport in an additional .05" from the left margin to preserve the proper ordering of the subreports. The good news is that this pretty well eliminates the empty report space I had by layout out each report next to one another. The bad news is that I still haven't solved centering them on the page. There's really not a horizontal alignment option for a rectangle or a subreport. Moving the subreport items to the center of the containing rectangle just pushes the whole set of charts to the right, starting precisely where I placed the left most subreport. The same result is achieved by horizonally shrinking the rectangle.
For now, I will probably need to settle with the left justified list of charts. Thanks again for your help.
Steve
June 23, 2014 at 1:31 pm
SCH70 (6/23/2014)
Well, after weighing some options I used a rectangle on the main report with subreports calling each of the five charts individually. I them left justified all five subreports, pushing each subreport in an additional .05" from the left margin to preserve the proper ordering of the subreports. The good news is that this pretty well eliminates the empty report space I had by layout out each report next to one another. The bad news is that I still haven't solved centering them on the page. There's really not a horizontal alignment option for a rectangle or a subreport. Moving the subreport items to the center of the containing rectangle just pushes the whole set of charts to the right, starting precisely where I placed the left most subreport. The same result is achieved by horizonally shrinking the rectangle.For now, I will probably need to settle with the left justified list of charts. Thanks again for your help.
Steve
Just had another thought... What if you could dynamically, via query determine the data that appeared in each chart, so that if a given client had chart A and chart C, but another client had chart B and Chart D, the chart count would determine which data went where? With this setup, you always have 5 charts, but which ones are empty is dynamically determined. That requires that for 1 chart, it's data goes to chart C in the series A, B, C, D, E. If there are 2 charts, then B & D, while 3 charts go to B, C, & D, then 4 charts is A, B, D, & E, and 5 charts is a full set. Could you construct the query in such a way as to properly group the data by assigning a chart number? Maybe there is another way to skin this particular cat ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 24, 2014 at 6:15 am
If it were limited to T-SQL querying alone, then I think this would be fairly straightforward. Trying to incorporate that into SSRS's scripting would be difficult and laborious. Not that I mind difficult, but with the fixed positions of the charts, they would still only look centered if there were 1, 3 or 5 products for a client. Two or four products would still be unbalanced on the page. This isn't a bad thought, but the payoff isn't that great if it can actually be done. There are a number of data specific properties that would have to be included in the dynamic script also which could make this option hard to maintain going forward. I've found that trying to set properties in individual charts at design time after trying to copy and paste a chart tends to want to hang on to the original chart's data set and other properties. I'm just not that confident in my ability to coerce the chart into dynamically selecting the correct data set to display.
Thanks for continuing to think about it. I'll continue to look for ideas, but function is more critical than form at this point. 🙂
Thanks!
June 24, 2014 at 6:52 am
SCH70 (6/24/2014)
If it were limited to T-SQL querying alone, then I think this would be fairly straightforward. Trying to incorporate that into SSRS's scripting would be difficult and laborious. Not that I mind difficult, but with the fixed positions of the charts, they would still only look centered if there were 1, 3 or 5 products for a client. Two or four products would still be unbalanced on the page. This isn't a bad thought, but the payoff isn't that great if it can actually be done. There are a number of data specific properties that would have to be included in the dynamic script also which could make this option hard to maintain going forward. I've found that trying to set properties in individual charts at design time after trying to copy and paste a chart tends to want to hang on to the original chart's data set and other properties. I'm just not that confident in my ability to coerce the chart into dynamically selecting the correct data set to display.Thanks for continuing to think about it. I'll continue to look for ideas, but function is more critical than form at this point. 🙂
Thanks!
I agree, there are times when either the time isn't there to spend to do something, or there may well be a question as to whether it will even be worth it. This may well be a combination of both. On the alignment, if you make a single chart appear in chart C of ABCDE, a pair of charts B & D, 3 charts as A, C, E, 4 charts as A, B, D, E, then all the scenarios are centered. This would have to be done at the T-SQL level. You would also have to design all the charts to operate off exactly the same dataset, but filtered on the chart number. T-SQL would have to take you at least that far, and provide the needed dynamic values for the chart labels. Everything else would have to be dynamic, but otherwise perfectly identical.
Alternatively, it's 5 subreports that all draw from the same dataset originally created in T-SQL. There are probably data warehouses that do something similar, now that I think about it. But as with so many things DWH, there's a cost...
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