Schedule: Read CSV-File -> Bar-Chart -> Send Mail ?

StinkyPete · 2016-06-20 17:46:56

Hello, community,
I am absolutely new to ReportServer (and still dont know if it replaces something like BIRT or just complements it), but after installing the comunity-version on my Windows 10 laptop
I have no clue how to get the following task solved.

My plan is to setup a daily schedule that reads a CSV-file from a network-share.
Then I want to turn the data into a bar-chart and send this as a PDF-attachement to my mail-address.

After a couple of hours I am not making any progress on that task ;-(
Till now I have even not found a way to read any external CSV-file.

Can someone please help me here and point me into the right directions?
Or is it not possible to do that with ReportServer???

Greetings,
Stinky Pete

Arno Mittelbach · 2016-06-26 14:47:59

Hi Pete,

sorry for the late reply. Now, there are many ways you can implement your use case and I hope that my answer also along the way answers the question whether ReportServer replaces BIRT or complements it (in short, it does both). So,
the task is to implement the following steps

1) read a csv file
2) create a bar chart
3) send it via email

As I said, there are multiple ways of going about this. For example, you could create a BIRT report with a CSV datasource and render the bar chart in the BIRT report. In ReportServer you could then upload the BIRT report and schedule it such
that you get the result via email. Instead of using the BIRT engine, you could implement the same strategy with the JasperReports engine.

A third option is to use a CSV datasource. If your file is stored on location /tmp/test/somefile.csv you could use the URL connector with the datasource and use the url

file:///tmp/test/somefile.csv

You could then use the datasource to power a Dynamic List. To get to a bar chart from the Dynamic List, you could, for example, have a look into the Excel Templating (https://reportserver.net/en/guides/user … #Templates).

Yet another option is to use a script report. Following is an example script that reads the following CSV File

"City", "Population"
"New York City, NY", 8175000
"Los Angeles, CA", 3792000
"Chicago, IL", 2695000
"Houston, TX", 2099000
"Philadelphia, PA", 1526000

The script then renders an HTML document that uses Google's Chart API to render a bar chart.

import net.datenwerke.rs.base.service.datasources.helpers.*;

/* config */
def filename = "/Path/to/csv/file.csv";
def separator = ',' as char;
def quote = '"' as char;

/* read file */
def data = new File(filename).text;

/* prepare csv helper */
def csvHelper = new CsvToTableModelHelper();
csvHelper.setPreferences(quote, separator, "\n");

/* parse csv */
def model = csvHelper.processCSV(data.getBytes());

/* create html for chart */
def report = """<html>
  <head>
    <!--Load the AJAX API-->
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">

      // Load the Visualization API and the corechart package.
      google.charts.load('current', {'packages':['corechart']});

      // Set a callback to run when the Google Visualization API is loaded.
      google.charts.setOnLoadCallback(drawChart);

      // Callback that creates and populates a data table,
      // instantiates the pie chart, passes in the data and
      // draws it.
      function drawChart() {

        // Create the data table.
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'City');
        data.addColumn('number', 'Population');
""";

/* add data */
report += "data.addRows(["

report += model.getData().collect{"['${it.getAt(0)}', ${it.getAt(1)}]"}.join(',');

report += "]);";

report += """
        // Set chart options
        var options = {'title':'City / Poulation',
                       'width':400,
                       'height':300};

        // Instantiate and draw our chart, passing in some options.
        var chart = new google.visualization.BarChart(document.getElementById('chart_div'));
        chart.draw(data, options);
      }
    </script>
  </head>

  <body>
    <!--Div that will hold the pie chart-->
    <div id="chart_div"></div>
  </body>
</html>"""

return report;

The result (rendered in ReportServer) would look as follows:

Besides using Google's Chart APIs, you could of course use any other library that allows to render charts.

I hope that this gives you a place to start exploring the possibilities offered ReportServer.

Cheers,
Arno

ReportServer Forum

#1 2016-06-20 17:46:56

Schedule: Read CSV-File -> Bar-Chart -> Send Mail ?

#2 2016-06-26 14:47:59

Re: Schedule: Read CSV-File -> Bar-Chart -> Send Mail ?

Board footer