Posts Tagged ‘ SSRS ’

Utilizing MSChart in SSRS

    MSChart has been around for a while now, but we found a new use for it regarding some SSRS (SQL Server Reporting Services) reports we were creating the other day.

    We had a need to generate some charts on the fly based on a particular action taken by a user, like clicking a metric. The MS Chart controls have some really nice capabilities for a free charting package. They support caching, multiple data binding scenarios, and AJAX driven events, just to name a few. We utilized the charts ability to generate a binary stream of data to create our own custom image handler.  In order to try this out yourself, simply create a web page with nothing in it but a chart control like the below example.

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="graph.ascx.cs" Inherits="graph" %>
<%@ Register Assembly="System.Web.DataVisualization, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Namespace="System.Web.UI.DataVisualization.Charting" TagPrefix="asp" %> 
<asp:chart id="mainChart" runat="server" height="296px" width="412px"
    imagetype="Png" palette="BrightPastel" backcolor="LightBlue" rendertype="BinaryStreaming"
    borderdashstyle="Solid" backgradientstyle="TopBottom" borderwidth="2" bordercolor="181, 64, 1">
    <titles>
        <asp:Title ShadowColor="32, 0, 0, 0" Font="Trebuchet MS, 14.25pt, style=Bold" ShadowOffset="3" Text="Binary Streaming" ForeColor="26, 59, 105"></asp:Title>
    </titles>
    <legends>
        <asp:Legend Enabled="True" IsTextAutoFit="True" Name="Default" BackColor="Transparent" Font="Trebuchet MS, 8.25pt, style=Bold" Alignment="Center" Docking="Bottom"></asp:Legend>
    </legends>
    <borderskin skinstyle="Emboss" />
    <series>
        <asp:Series XValueType="String" Name="Targets" ChartType="SplineArea" BorderColor="180, 26, 59, 105" Color="220, 0, 0, 0" YValueType="Double"/>
        <asp:Series XValueType="String" Name="Actuals" ChartType="SplineArea" BorderColor="180, 26, 59, 105" Color="127, 255, 255, 0" YValueType="Double"/>
        <asp:Series XValueType="String" Name="ActualPoints" ChartType="SplineArea" BorderColor="180, 200, 59, 105" Color="127, 65, 140, 240" YValueType="Double"/>
    </series>
    <chartareas>
        <asp:ChartArea Name="MainChartArea" BorderColor="64, 64, 64, 64" BorderDashStyle="Solid" BackSecondaryColor="White" BackColor="OldLace" ShadowColor="Transparent">
            <axisy linecolor="64, 64, 64, 64">
                <labelstyle font="Trebuchet MS, 8.25pt, style=Bold"/>
                <majorgrid linecolor="64, 64, 64, 64"/>
            </axisy>
            
            <axisx IsMarginVisible="False" linecolor="64, 64, 64, 64">
                <labelstyle font="Trebuchet MS, 8.25pt, style=Bold"/>
                <majorgrid linecolor="64, 64, 64, 64"/>
            </axisx>
        </asp:ChartArea>
    </chartareas>
</asp:chart>

    This allows us to create an external image in our SSRS report with a source expression something like = "http://mysite/graph.aspx?metricID=&quot; & Fields!MetricID.Value & "&fiscalYearID=" & Fields!FiscalYearID.Value from a bound dataset within the report. One gotcha we encountered was the need to setup an unattended execution account on the Reporting Server so that external images could be served up. Learn how to configure the unattended execution account here: Configuring the unattended execution account.  While this is a very flexible way to generate custom images on the fly for reports, we decided we wanted a little more interactivity.

    MSChart supports image maps as well as AJAX callbacks allowing the client to interact with the chart images. We utilized this ability to create custom tooltips for commentary at certain datapoints.  This can be accomplished by iterating through the individual datapoints of your series and setting the MapAreaAttributes property.

mainChart.Series["ActualPoints"].Points[count].MapAreaAttributes = "onmouseover=\"DisplayTooltip('" + toolTip + "');\" onmouseout=\"DisplayTooltip('');\"";

    If you intend to use the image map or AJAX callback support, you will be unable to use the image handler approach since the chart will need to generate an image map and possibly an update panel to perform AJAX events. We will address using the image map and AJAX callback functionality in a future post.

%d bloggers like this: