YAPS on Dynamic Reports and Parameters: Charts get into the act

Wow. I can’t believe how long it’s been since my last post here.

The questions are piling up, the garden wants attention, or at least serious admiration, and I’m travelling a bit more than I’d like for work. C and I keep stockpiling films we’d like to see… someday. And time really flies when you have a Jonah to play with.

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.

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.

DynamicChartExpressionsQuestion

In general, here are the requirements  we have:

• Do something the same way in WebForm and WinForm.  Well, ReportViewer certainly fits that bill. But the full question indicates that the person doesn’t necessarily know that — so I’d better mention it.
• Do it with an RDLC. No problem there, in either environment.
• Show a scatterplot several times on a page, each time using different columns of the same dataset for the axes, and potentially other aspects of the graph, such as titles.

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.

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.” 

This question will remain anonymous

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, please let me know if this is a technique that needs to be tweaked.  Certainly charting changed a lot in RS 2008.)

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.

Now, about that question.

Simple setup

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. 

SELECT TOP 25
   RTRIM(Country.Name) AS Country ,
   ROW_NUMBER() OVER( ORDER BY Country.Name) % 5 AS Sets,
   SUM(City.Population) AS RandomX1,
   MIN(City.Population) AS RandomX2,
   MAX(City.Population) AS RandomX3
FROM
 City JOIN Country on City.CountryCode = Country.Code
GROUP  BY Country.Name
ORDER BY Country.Name

If you’re playing along, just think “pivot table” in Excel and you’ll have the right-shaped data.

 

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. 

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.

There’s nothing special about the layout, either:

Specifying dynamic behavior to a graph

How do you normally give instructions to a report from the outside?  You use a parameter, of course. 

It’s no different here. 

 

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). 

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.

As you can see here, the code function shows up in the Chart Properties…

 

… 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)…

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:

… 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: 

… 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:

 … 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…

… and that’s all she wrote.  If you have questions, or if there is a complexity I’m overlooking here, please let me know? Hmmmm. As I write this, I wonder if there is an obvious way to do this without iterating over the graphs in code that I’m not thinking about. Why, for example, can’t I use a code variable instead of a parameter here, and change the information in a function called between each graph? That approach could be dangerous — especially when we change versions — but I might be able to group on a sequencing SQL column to do the same thing, or something similar. And I still bet it would be easier without the table being pivotted, probably painfully, in preparation. Hard to get to !?! It bears thinking about. Maybe on the plane…

P.S.

Yes I know that last line was a stretch, but I haven’t paid my Serenity tax in a while.

Wow, now, that’s a cheery link to visit just before a plane trip…

“This is the captain, we may experience some turbulance, and then… explode.” 
“I don’t wanna explode!”
“I think we better run…”