Monday, 31 December 2012 16:43

Roll your own business intelligence

By

It is a truism that what gets measured gets improved. One important function of the CIO is to ensure strategic information is delivered to executive management as a basis for steering the company. Here is a simple C# app to easily publish results of SQL queries as Excel spreadsheets.

 

What gets measured gets improved; conversely what is not measured is not improved. In my view, one of the important roles of a Chief Information Officer relates to the "information" component of the title; information being meaningful rather than just a dump of data.

Yet, extracting information and publishing it are two separate endeavours. A small enterprise in particular can be hampered if it does not have the funds or in-house skills for dedicated business intelligence tools. Not to worry - with just a small collection of free online tools and compiling a simple application you can make scheduled reports on demand.

Consider this BAT file:

@echo off

sql2xls gm-report.sql gm-report.xlsx

"c:\program Files\7-zip\7z.exe" a -tzip gm-report.zip gm-report.xlsx

blat -to [email protected] -subject "Gross margin report" -attach gm-report.zip -body "Gross margin report attached" -debug -log blat.log

del gm-report.xlsx

del gm-report.zip

This is a standard BAT file; that means it executes a bunch of commands to achieve an overall objective. More than this, as a batch file it can be scheduled via Windows' task scheduler, ie Start/Programs/Accessories/System/Task Scheduler to run every morning or every week.

The commands perform these individual functions:

sql2xls gm-report.sql gm-report.xlsx

sql2xls is a very simple C# application I wrote, with source code in this article. It is a console application created within Visual Studio. You can easily edit and compile this code yourself using Visual Studio Express or any command-line C# compiler. You do not need to purchase the full retail edition of Visual Studio.

The app is so-named because it takes a SQL query, contained in the filename specified in the first parameter, executes it, then outputs all the results into an Excel spreadsheet, whose name is specified in the second parameter.

It is not unusual to find report creation taking far more time than strictly necessary due to the inherit slowness of drag-and-drop reporting tools, or by striving for perfect presentation. For internal reporting Excel is generally the best format rather than a PDF'd report with a specific layout.

Having an app like this means that your SQL-savvy users can create their own reports without needing intervention from IT.

I wrote sql2xls simply to fill a need. I had previously tried the SQL Server bcp command to create CSV files but if output included line breaks or quote marks or commas the output, understandably, broke.

So, to use sql2xls simply create a text file with an SQL query in it, no matter how simple or complex. The output will be an Excel spreadsheet with a single worksheet containing all rows and columns from the query output.

"c:\program Files\7-zip\7z.exe" a -tzip gm-report.zip gm-report.xlsx

The second command in my BAT file uses 7-Zip, a popular and free compression tool, to zip the Spreadsheet up. You can ignore this step if your report is less than 10Mb in size; for sizes larger than that you may find mail servers either refuse to send or receive it.

Download the 7-Zip command-line edition from http://7-zip.org/download.html

blat -to [email protected] -subject "Gross margin report" -attach gm-report.zip -body "Gross margin report attached" -debug -log blat.log

The third command runs "blat", a lesser known but highly useful, program which e-mails from the command line. Thus, in this line blat is employed to e-mail the zipped-up spreadsheet to a specific recipient, and with a specific subject line and body text. Other options exist to customise the e-mail further.

Download blat from http://www.blat.net/

Finally, the batch file tidies up by removing the intermediate files.

del gm-report.xlsx

del gm-report.zip

With creativity and a little knowledge of DOS BAT commands and options you can create filenames programatically, incorporating the date, for example.

As to sql2xls, the source code follows. In Visual Studio, create a new project of type Windows console application.

Copy-and-paste the source code over the page into Program.cs:


Source code for sql2xls:

using System;

using System.Globalization;

using System.IO;

using OfficeOpenXml;

using System.Data;

using System.Data.SqlClient;

 

namespace sql2xls

{

    class Program

    {

        static void Main(string[] args)

        {

            Console.WriteLine("sql2xls\t\tv1.0\t\twww.itwire.com - The Wired CIO");

            Console.WriteLine();

            if (args.Length != 2)

            {

                Console.WriteLine("Usage: sql2xls <input> <output> <DB>");

                Console.WriteLine("\tinput\tinput file with SQL query");

                Console.WriteLine("\toutput\toutput file for XLSX data");

                return;

            }

            var inputFile = args[0];

            Console.WriteLine("Input:\t" + inputFile);

            var outputFile = args[1];

            Console.WriteLine("Output:\t" + outputFile);

            var connStr = "Server=192.168.10.10;Database=Finance;user id=reader;password=reader";

            var streamReader = new StreamReader(inputFile);

            var sqlStatement = streamReader.ReadToEnd();

            streamReader.Close();

            streamReader.Dispose();

            var d = new DataTable();

            var db = new SqlConnection(connStr);

            db.Open();

            var cmd = new SqlCommand(sqlStatement, db) {CommandTimeout = 0};

            var rdr = cmd.ExecuteReader();

            d.Load(rdr);

            Console.WriteLine("Number of rows: " + d.Rows.Count.ToString());

            var package = new ExcelPackage(new FileInfo(outputFile));

            var worksheet = package.Workbook.Worksheets.Add(inputFile);

            var rowNumber = 1;

            var numColumns = d.Columns.Count;

            for (var c = 0; c < numColumns; c++)

                worksheet.Cells[1, c + 1].Value = d.Columns[c].ColumnName;

            foreach (DataRow r in d.Rows)

            {

                rowNumber++;

                Console.Write(".");

                for (var c = 0; c < numColumns; c++)

                    worksheet.Cells[rowNumber, c + 1].Value = r[c].ToString();

            }

            Console.WriteLine();

            package.Save();

            package.Dispose();

            d.Dispose();

            rdr.Close();

            cmd.Dispose();

            db.Close();

        }

    }

}

This won't compile just yet! The app depends on a library to manage Excel spreadsheets from dot NET code, EPPlus. Download EPPlus.dll from http://epplus.codeplex.com/. In your Visual Studio project, right-click on "References" in the project navigator, click "Add Reference" and browse to EPPlus.dll.

The code will now compile but there is one final step: change the value of the string connStr to a connection string relevant to your own database server. This typically will be the server IP address, database name, and a user name and password which has sufficient access to run the query, or a paramater specifying a trusted connection.

It is possible to modify the program code so the connection string is a parameter but or uses an ODBC data source. There is no end of extensibility available.

Compile the program code and you now have a command-line tool which you can run queries against at will.

With this code and these freely-acquired tools we have gone from a SQL query through to an e- mailed spreadsheet.

Now you can focus more on the construction of meaningful database interrogation and far less on the presentation and distribution of such queries.

 

BUSINESS WORKS BETTER WITH WINDOWS 1O. MAKE THE SHIFT

You cannot afford to miss this Dell Webinar.

With Windows 7 support ending 14th January 2020, its time to start looking at your options.

This can have significant impacts on your organisation but also presents organisations with an opportunity to fundamentally rethink the way users work.

The Details

When: Thursday, September 26, 2019
Presenter: Dell Technologies
Location: Your Computer

Timezones

QLD, VIC, NSW, ACT & TAS: 11:00 am
SA, NT: 10:30 am
WA: 9:00 am NZ: 1:00 pm

Register and find out all the details you need to know below.

REGISTER!

ADVERTISE ON ITWIRE NEWS SITE & NEWSLETTER

iTWire can help you promote your company, services, and products.

Get more LEADS & MORE SALES

Advertise on the iTWire News Site / Website

Advertise in the iTWire UPDATE / Newsletter

Promote your message via iTWire Sponsored Content/News

Guest Opinion for Home Page exposure

Contact Andrew on 0412 390 000 or email [email protected]

OR CLICK HERE!

David M Williams

David has been computing since 1984 where he instantly gravitated to the family Commodore 64. He completed a Bachelor of Computer Science degree from 1990 to 1992, commencing full-time employment as a systems analyst at the end of that year. David subsequently worked as a UNIX Systems Manager, Asia-Pacific technical specialist for an international software company, Business Analyst, IT Manager, and other roles. David has been the Chief Information Officer for national public companies since 2007, delivering IT knowledge and business acumen, seeking to transform the industries within which he works. David is also involved in the user group community, the Australian Computer Society technical advisory boards, and education.

VENDOR NEWS & EVENTS

REVIEWS

Recent Comments