Telerik blogs

Binding Telerik Reports to Google Spreadsheets API

February 23, 2011 Productivity, Reporting 0 Comments


Introduction

A while back, someone pointed me to this news article containing a Google Documents based spreadsheet with information about UK Inflation. Since I typically spend a lot of time working with Telerik Reporting, I thought it would be fun to create my own report based on this data.

Accessing Google Data from .NET

One of the cool things about Google is that they are a very data oriented company. Pretty much all of their services are centered on storing, organizing, and maintaining different types of data. They make this data extremely easy to access through the use of their various Google Data APIs. Accessing these APIs is quite simple since Google also provides Client Libraries for a number of different development platforms including .NET.

In order to begin working with the data from the spreadsheet featured in the news article, I first made a copy of it in my own Google Documents account. The data needed to be reorganized a little bit for use in my report. You can access the updated spreadsheet here.

Spreadsheet
Figure 1. The Updated Spreadsheet

In order to actually access the data in the report, I created a simple repository class responsible for retrieving the data from the spreadsheet with the Google Spreadsheets API. This class contains a property that retrieves the data and converts it into C# objects that can be easily bound to a Telerik Report.

// Convert spreadsheet rows to these
public class InflationInfo
{
    public double OleDate { get; set; }
    public float RPIPercent { get; set; }
    public float CPIPercent { get; set; }
}
  
  
// Queries for data from Spreadsheet and returns a list of InflationInfo objects
public List<InflationInfo> InflationInfos
{
    get
    {
        // Populate InflationInfos only once
        if (_inflationInfos == null)
        {
            _inflationInfos = new List<InflationInfo>();
 
  
           SpreadsheetsService service = new SpreadsheetsService("UKInflation-ReportApplication");
                      
            // Query the spreadsheet
            ListFeed feed = service.Query(query);
   
            foreach (ListEntry worksheetRow in feed.Entries)
            {
                ListEntry.CustomElementCollection elements = worksheetRow.Elements;
                          
                // Add a new InflationInfo to the list of them
                InflationInfo iInfo = new InflationInfo();
                _inflationInfos.Add(iInfo);
   
                // Parse the values returned from the spreadsheet into their data types
                foreach (ListEntry.Custom element in elements)
                {
                    if (element.XmlName.Equals("year"))
                        iInfo.OleDate = DateTime.Parse(element.Value).ToOADate(); // Chart uses OLE Automation Date format
                    if (element.XmlName.Equals("rpichange"))
                        iInfo.RPIPercent = float.Parse(element.Value) / 100;
                    if (element.XmlName.Equals("cpichange"))
                        iInfo.CPIPercent = float.Parse(element.Value) / 100;
                }
            }
        }
   
        return _inflationInfos;
    }
              
    set { _inflationInfos = value; }
}


Creating the Telerik Report

Once the repository class had been created for retrieving the data, I added an additional parameterized method for use with the Telerik Report.

public List<InflationInfo> GetInflationDataByYear(DateTime startDate, DateTime endDate)
{
    double start = startDate.ToOADate();
    double end = endDate.ToOADate();
   
    return InflationInfos.Where(i => i.OleDate >= start && i.OleDate <= end).ToList();
}

This method queries for and returns only items that fall between the two passed in dates. By using this method in conjunction with an ObjectDataSource and Report Paramaters, I can allow the users of the report to specify subsets of information they want to generate the report on. The Report Parameters are set up as follows.

  • StartDate Report Parameter
    • Name: StartDate
    • Text: Start Date
    • Type: DateTime
    • Value: 1/6/1948
    • Visible: True
    • EndDate Report Parameter
      • Name: EndDate
      • Text: End Date
      • Type: DateTime
      • Value: 1/11/2010
      • Visible: True

As for visualizing the data, I chose to go with a chart report item. This report item contains its own DataSource property independent of the main report. In binding only it to the ObjectDataSource, I easily created a single page report containing only a chart. As for the series of the chart report item, they must be set as follows in order to successfully display the data.

  • Series 1: RPI Data
    • Name: RPI rate: All items, monthly, UK
    • Type: Line
    • DataXColumn: OleDate
    • DataYColumn: RPIPercent
    • DefaultLabelValue: #Y{P}
    • Appearance -> ShowLabels: False
    • Series 2: CPI Data
      • Name: CPI rate: All items, monthly, UK
      • Type: Line
      • DataXColumn: OleDate
      • DataYColumn: CPIPercent
      • DefaultLabelValue: #Y{P}
      • Appearance -> ShowLabels: False

Finally, in order to re-scale the chart when a user changes the start and end date report parameters, I started out by setting the PlotArea’s X-Axis properties to the following.

  • PlotArea -> XAxis
    • Appearance -> CustomFormat: yyyy
    • Appearance -> ValueFormat: ShortDate
    • AutoScale: False
    • MaxValue: 47000
    • MinValue: 17000
    • Step: 6000

Finally, I implemented the NeedDataSource event of the report object. This event gets called whenever the report is generated. Inside of this event, I added code responsible for rescaling the X-Axis plot area of the chart when the parameters change.

private void UKInflationSince1948_V2_NeedDataSource(object sender, EventArgs e)
{
    // Retrieve the start and end Dates from the report parameters
    DateTime startDate = (DateTime)this.ReportParameters["StartDate"].Value;
    DateTime endDate = (DateTime)this.ReportParameters["EndDate"].Value;
  
    // Convert the start and end dates to their OA equivilants
    double start = startDate.ToOADate();
    double end = endDate.ToOADate();
    int step = ((int)end - (int)start) / 6;
  
    // Manually send the scale of the PlotArea
    this.chart1.PlotArea.XAxis.Step = step;
    this.chart1.PlotArea.XAxis.MaxValue = end;
    this.chart1.PlotArea.XAxis.MinValue = start;
}

Analysis of the Completed Report

As you can see, in the 1970s inflation in the UK went out of control. It peaked at 25% in 1975 and didn't slow back down until 1983. Growth in government spending, increased oil prices, and higher pay deals resulted in this massive growth in inflation.


Click here to download the source code used in this post. (Note: The source code includes two reports, one that contains report parameters to re-scale the chart, and one that does not.)

Automation

Comments

Comments are disabled in preview mode.