It’s getting more and more difficult to find time to sift through the compost, let alone the interesting requests for help that I get here.<\/p>\n
Before getting on the next plane and making the next meeting, I’d like to answer at least one item of general interest, and it shouldn’t take long. I’ll give you the entire question in a short, separate text file, with no attribution, because there are some ins and outs that are not entirely relevant, and the question unfolded over several messages.<\/p>\n
DynamicChartExpressionsQuestion<\/a><\/p>\n In general, here are the requirements we have:<\/p>\n The request also specifies that the graphs are simple plots, nothing fancy, so I’m not going to worry much about the details of formatting a scatterplot.<\/p>\n In general, it’s the dynamic data aspects that seem to be the issue. I’ll excerpt: “I can deal with the looping to add the graphs but the bit I can’t get my head around is how to programmatically specify the x and y data source and also the axis titles using the headings. All my graphs will be the same layout and format so I can use a single rdlc file for that.”<\/em> <\/p>\n I usually name the questioner, but in this case, he mentioned one detail about his work situation I’d like to share without causing him any work difficulties: for whatever reason, he’s been stuck in VS and RS 2005. So that’s what I’m going to use to illustrate, although I’m sure it’s pretty much the same scenario in later versions. (At least I hope it is — I’ve been wrong before<\/a>, please let me know if this is a technique that needs to be tweaked. Certainly charting changed a lot in RS 2008.)<\/p>\n I especially want to mention this version constraint because I’m seeing the same thing with a number of clients. For whatever reason — hosted databases on a third-party provider, generally conservative habits, a huge backload of work that would need testing for a new version — a lot of people are just beginning to move towards SQL Server 2008 now. It makes it difficult for me to get really excited about 2010 and all the truly gotta-have improvements it “must” be bringing. I guess I’m just getting old.<\/p>\n Now, about that question.<\/p>\n To simplify my own life, I’m going to fake the situation using a table from my usual SQLWorld database, with some random values in three columns (don’t try to make sense of this data) that I’ll specify dynamically for one axis. I’m sure you’ll see how this could be extended to the other axis. I’ll also dynamically determine the axis title using the name of the column I’m dynamically specifying, rather than considering this a separate problem, since we know this is different for each iteration of the graph. <\/p>\n SELECT<\/span><\/span> <\/span>TOP<\/span><\/span> 25 If you’re playing along, just think “pivot table” in Excel and you’ll have the right-shaped data.<\/p>\n <\/p>\n To be honest, I think if I was solving this problem, I’d use a different data structure. I wouldn’t pivot the data, I’d use a key column to indicate what type of value each row was. Each graph would filter the results differently. <\/p>\n But, if the graph is doing the filtering, rather than the dataset doing it before the RDLC “sees” the dataset, you’re still specifying something dynamically in the graph — and you’ll still be able to do it the way I’m showing you here.<\/p>\n There’s nothing special about the layout, either:<\/p>\n How do you normally give instructions to a report from the outside? You use a parameter, of course. <\/p>\n It’s no different here. <\/p>\n <\/p>\n As I write this, I’m wondering if the person asking the question doesn’t understand how to use parameters with an RDLC. If that’s the case, he’ll write and tell me, and I’ll explain (probably by pointing him to a different post<\/a>). <\/p>\n In this example, I’m going to use a single parameter, shown above, to provide information for one axis and also the chart title. I’ll use a direct expression for the former and a code function for the latter, just to illustrate the possibilities. Obviously, you can have additional parameters (and do more of the work outside the report, rather than writing a code function inside the report) if you want.<\/p>\n As you can see here, the code function shows up in the Chart Properties…<\/p>\n <\/p>\n … and the function itself is just for illustration purposes (it didn’t matter what I used here, as long as you could see that the result was dynamic)…<\/p>\n Now we come to the axes. Forgive me for naming the columns incorrectly; by the time I whipped up the graph I’d decided to specify Y dynamically, instead of X (in real life, of course, you can do both), so I shouldn’t have named them RandomX<N>. The X axies is really being specified as Country in this example:<\/p>\n … and, just to make sure you see everything, the Series in this graph are taken care of by the Sets column I added to the dataset: <\/p>\n … which just leaves us with the dynamic column. It’s specified similarly to the other items you see above, by using an expression rather than a literal. You’ve already seen it in the layout above, but here it is up close and personal:<\/p>\n … pretty much as you would expect. For each iteration of the loop, your code is going to change the value of WhichColumn to match one of your field names in the dataset, and this is going to drive your graph…<\/p>\n\n
This question will remain anonymous<\/h3>\n
Simple setup<\/h3>\n
<\/span> RTRIM<\/span><\/span>(<\/span><\/span>Country<\/span>.<\/span><\/span>Name<\/span>)<\/span><\/span> <\/span>AS<\/span><\/span> Country <\/span>,
<\/span><\/span> ROW_NUMBER<\/span><\/span>()<\/span><\/span> <\/span>OVER<\/span><\/span>(<\/span><\/span> <\/span>ORDER<\/span><\/span> <\/span>BY<\/span><\/span> Country<\/span>.<\/span><\/span>Name<\/span>)<\/span><\/span> <\/span>%<\/span><\/span> 5 <\/span>AS<\/span><\/span> <\/span>Sets<\/span><\/span>,
<\/span><\/span> SUM<\/span><\/span>(<\/span><\/span>City<\/span>.<\/span><\/span>Population<\/span><\/span>)<\/span><\/span> <\/span>AS<\/span><\/span> RandomX1<\/span>,
<\/span><\/span> MIN<\/span><\/span>(<\/span><\/span>City<\/span>.<\/span><\/span>Population<\/span><\/span>)<\/span><\/span> <\/span>AS<\/span><\/span> RandomX2<\/span>,
<\/span><\/span> MAX<\/span><\/span>(<\/span><\/span>City<\/span>.<\/span><\/span>Population<\/span><\/span>)<\/span><\/span> <\/span>AS<\/span><\/span> RandomX3
<\/span>FROM
<\/span><\/span> City <\/span>JOIN<\/span><\/span> Country <\/span>on<\/span><\/span> City<\/span>.<\/span><\/span>CountryCode <\/span>=<\/span><\/span> Country<\/span>.<\/span><\/span>Code
<\/span>GROUP <\/span><\/span> <\/span>BY<\/span><\/span> Country<\/span>.<\/span><\/span>Name
<\/span>ORDER<\/span><\/span> <\/span>BY<\/span><\/span> Country<\/span>.<\/span><\/span>Name<\/span><\/p>\n![\"\"](\"\/lisa\/wp-non\/migrated\/2010\/4\/DCE1-Data.PNG\")
<\/p>\n![\"\"](\"\/lisa\/wp-non\/migrated\/2010\/4\/DCE2-Layout.PNG\")
<\/p>\nSpecifying dynamic behavior to a graph<\/h3>\n
![\"\"](\"\/lisa\/wp-non\/migrated\/2010\/4\/DCE3-Parameter.PNG\")
<\/p>\n![\"\"](\"\/lisa\/wp-non\/migrated\/2010\/4\/DCE4-ChartProperties.PNG\")
<\/p>\n![\"\"](\"\/lisa\/wp-non\/migrated\/2010\/4\/DCE5-Code.PNG\")
<\/p>\n![\"\"](\"\/lisa\/wp-non\/migrated\/2010\/4\/DCE6-XAxes.PNG\")
<\/p>\n![\"\"](\"\/lisa\/wp-non\/migrated\/2010\/4\/DCE7-Series.PNG\")
<\/p>\n![\"\"](\"\/lisa\/wp-non\/migrated\/2010\/4\/DCE8-DynamicValues.PNG\")
<\/p>\n
![\"\"](\"\/lisa\/wp-non\/migrated\/2010\/4\/DCE9-Results.PNG\")
<\/td>\n