SSRS 2008 Quick tip: designing your databinding scheme for a custom charting control

So you've decided to create your own SSRS 2008 charting control. Good luck. The API is highly under-documented and there are very few examples out there. Now that I've got that disclaimer out of the way I'll proceed.

One of the decisions you'll have to make is how to structure your data for consumption by your control.

If your chart only needs a simple mapping scheme, where you consume data as it's returned by the dataset, then this tip won't matter much to you.

If you want to do something more complex then you'll be pleased to discover that any custom chart has similar column and row grouping capabilities that a standard tablix has.

Tip:

When getting your control databinding working create a tablix for debugging. This tablix should have the same grouping setup, including expressions, which also consumes the same dataset. This way you can debug any issues with the data you're receiving and also experiment with new grouping setups and expressions without wondering if there's a problem with your code.

I'm not going to go into great depth about the databinding code itselfu at this stage. I plan to do that in a future post.

Migrating relational data into a SQL Server XML datatype column

Recently I needed to take the data from a SQL Server table, convert it into XML and insert it into an xml datatype column of a related table. In the past I would have used a scripting language such as Perl to achieve this, but since SQL Server 05/08 has native XML support I decided to give that a go.  I did this because I thought it would be more efficient, and less error prone, to do everything at the database level. It turns out that it was easier than I thought.

I was already familiar with SELECT .. FOR XML syntax from previous experiments with SQL Server's XML functionality, so was able to convert the existing table data to XML as follows:

SELECT part_id, part_name, part_description
FROM parts FOR XML AUTO, ELEMENTS

This generated the following XML:

<parts>
<part_id>4</part_id> 
  <part_name>Widget</part_name>
  <part_description>Widget to do something</part_description>
</parts>

Note: The XML generated by FOR XML AUTO was good enough for my purposes. If you need more control over the XML output you can use FOR XML EXPLICIT instead.

The next step was to insert this into the appropriate row of a related table. I achieved this using a sub-query on an UPDATE.  The resulting SQL was as follows:

UPDATE orders SET bio_sample_aliquot_info = ( 
SELECT part_id, part_name, part_description FROM parts 
WHERE order.id = part.order_id FOR XML AUTO, ELEMENTS)
FROM orders

Normally I don't like using sub-queries as they can be inefficient, but this ended up been fast enough for my needs.

After running the above query I had the XML I wanted in the appropriate XML datatype rows of the related table.

Note: I've changed all of the names of the tables and columns in the example above.  Also I tested this on SQL Server 08, but not 05.