Rhyous

In my first post, A basic reporting chart in ASP.NET, I went over the basics of creating a report using MSChart and ASP.NET and this was quit easy. However, in today’s world where the importance of business intelligence is ever increasing, the ability to drill down on a report has become the de facto standard. MSChart, ASP.NET, and HTML make it easy to create a drill-down report.

Note: Microsoft has a drill-down report in their ChartSamples example, but it was bundled as part of the same project with two-hundred other reports and was not a minimal example. It requires the use of an Access database (and I had nothing to read Access with), it has a bunch of javascript code that is for a tooltip preview of the drill down report, and the charts are in two objects. All of this made it more difficult for me to break this down.  In this example, the report will be its own ASP.NET project and will be a minimal example, however the use case and the sample data is taken directly from Microsoft’s example.

Report Example Use Case

Imagine you have a list of sales reps, their regions, and their sales results. You want a report to look at total sales per region. Then you want to click on a region to the see the sales by sales rep.

Download the project here: SampleChart.zip

Step 1 – Create the Visual Studio project

1. In Visual Studio, click on File | New | Project.
2. Select Visual C# | Web from the Installed Templates.
3. Locate and select ASP.NET Empty Web Application.
   Note: I like to demonstrate using an Empty project you nothing is done for you, and you have to learn everything you actually need to do.
4. Give the project a name.
   I named mine DrillDownChart because that is my example’s purpose.
5. Click OK.
6. Right-click on the newly created project and click Add | Reference.
7. Select the .NET tab.
8. Locate System.Web.DataVisualization and highlight it.
9. Click OK.

Step 2 – Add a web form for your chart

1. Right-click on the Project and choose Add |  New Item.
2. Select Web Form.
3. Give the file a name.
   I named my file  Report.aspx.
4. Click OK.

Step 3 – Create a data object for the report

Because data is often coming from a database, this example is going to use a DataSet. I am not going to connect to a database, but just use a statically build DataSet.

1. Right-click on the Project and choose Add |  Class.
2. Give the file a name.
   I named my file  SalesDataSet.cs.
3. Make the class inherit from DataSet.
4. Click OK.

Step 4 – Add example data to the data object for the report

While in a real world scenario, you would get the data from a database or somewhere, lets first just create some sample data. We are going to create two simple tables. One is a Region table, that has the region name and ID. One is a RepSales table that has sales per rep and the rep’s region id.

1. Create a property with only a getter that creates a region DataTable called RegionTable.
2. Add the columns needed: RegionID and RegionName.
3. Add the appropriate rows.
4. Create a property with only a getter that creates a reps sales DataTable called RepsSalesTable.
5. Add the columns needed: ID, Name, RegionID, and Sales.
6. Add the appropriate rows.
7. Now in your constructor, add those to the list of Tables in your object.

   using System;
   using System.Collections.Generic;
   using System.Linq;
   using System.Web;
   using System.Data;
   
   namespace DrillDownChart
   {
       public class SalesDataSet : DataSet
       {
           public SalesDataSet()
           {
               Tables.Add(RegionTable);
               Tables.Add(RepsSalesTable);
   
           }
   
           public DataTable RegionTable
           {
               get
               {
                   if (_RegionTable == null)
                   {
                       List<String> Regions = new List<string>() { "East", "West", "Central", "International", "South" };
   
                       _RegionTable = new DataTable("Region");
                       _RegionTable.Columns.Add("RegionID", typeof(int));
                       _RegionTable.Columns.Add("RegionName", typeof(string));
   
                       int i = 0;
                       foreach (var region in Regions)
                       {
                           DataRow row = _RegionTable.NewRow();
                           row["RegionID"] = ++i;
                           row["RegionName"] = region;
                           _RegionTable.Rows.Add(row);
                       }
   
                   }
                   return _RegionTable;
               }
           } private DataTable _RegionTable;
   
           public DataTable RepsSalesTable
           {
               get
               {
                   if (_RepsSalesTable == null)
                   {
                       List<String> reps = new List<string>() {
                           "Aaron", "Larry", "Andrew", "Mary", "Sally", "Nguyen", "Francis",
                           "Jerry", "Danny", "Jim", "Sarah", "Hannah", "Kim", "Gerry", "Bob" };
                       int[] regions = { 1, 2, 3, 1, 4, 2, 4, 3, 1, 2, 2, 3, 5, 5, 5 };
                       int[] sales = { 10440, 17772, 23880, 7663, 21773, 32294, 11983, 14991,
                                       17946, 8551, 19443, 27887, 30332, 16668, 21225 };
   
                       _RepsSalesTable = new DataTable("RepsSales");
                       _RepsSalesTable.Columns.Add("ID", typeof(int));
                       _RepsSalesTable.Columns.Add("Name", typeof(string));
                       _RepsSalesTable.Columns.Add("RegionID", typeof(int));
                       _RepsSalesTable.Columns.Add("Sales", typeof(int));
   
                       for (int i = 0; i < reps.Count; i++)
                       {
                           DataRow row = _RepsSalesTable.NewRow();
                           row["ID"] = i + 1;
                           row["Name"] = reps[i];
                           row["RegionID"] = regions[i];
                           row["Sales"] = sales[i];
   
                           _RepsSalesTable.Rows.Add(row);
                       }
   
                   }
                   return _RepsSalesTable;
               }
           } private DataTable _RepsSalesTable;
       }
   }
   

That is it, your fake example data is prepared.

Step 5 – Add a Chart to the Report.aspx file

1. Open the Report.aspx file.
2. Add a Register to the System.Web.DataVisualization assembly.
3. Locate the div inside the body.
4. Inside the div, add a Chart that includes a ChartArea.

   <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Report.aspx.cs" Inherits="CompareYearsByQuarter.Report" %>
   
   <%@ Register Assembly="System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
       Namespace="System.Web.UI.DataVisualization.Charting" TagPrefix="asp" %>
   
   <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
   <html xmlns="http://www.w3.org/1999/xhtml">
   <head runat="server">
       <title></title>
   </head>
   <body>
       <form id="form1" runat="server">
       <div>
           <asp:Chart ID="SalesReport" runat="server">
               <chartareas>
                   <asp:ChartArea Name="ChartArea1">
                   </asp:ChartArea>
               </chartareas>
           </asp:Chart>
       </div>
       </form>
   </body>
   </html>
   

Step 6 – Add code the Report.aspx.cs file

We are going to use the same object for both the original report and the drill down report. We will just a little code that switches which data the chart is populated with.

1. Open the Report.aspx.cs file.
2. Create an instance of the SalesDataSet object that has our sample data.
3. Add code in the Page_Load method to configure the Chart.
   Note 1: The steps for this code is in the code and comments itself. I created a method for each step and then populated the methods as  needed.
   Note 2: Notice that the AddDataToSeries() method uses and if statement to determine whether to add the original data or the drill down data.
   Note 3: Because we used a DataTable we query the example data using LINQ. It is likely that in your production reports you are using a real database and you will probably use queries directly to your database.

   using System;
   using System.Collections.Generic;
   using System.Linq;
   using System.Web;
   using System.Web.UI;
   using System.Web.UI.WebControls;
   using System.Data;
   using System.Web.UI.DataVisualization.Charting;
   
   namespace DrillDownChart
   {
       public partial class RegionReport : System.Web.UI.Page
       {
           // Step 1 - Create Example Data
           SalesDataSet ExampleData = new SalesDataSet();
   
           protected void Page_Load(object sender, EventArgs e)
           {
               // Step 2 - Populate chart drop down
               PopulateChartTypeDropDown();
   
               // Step 3 - Create Series
               Series series = CreateSeries();
   
               // Step 4 - Set the chart type
               SetChartType(series);
   
               // Step 5 - Add data (and if needed drilldown links to series)
               AddDataToSeries(series);
   
               // Step 6 - Add series into the chart's series collection
               SalesReport.Series.Add(series);
           }
   
           private void PopulateChartTypeDropDown()
           {
               List<String> chartTypes = new List<String>(Enum.GetNames(typeof(SeriesChartType)));
               chartTypes.Insert(0, "");
   
               foreach (var item in chartTypes)
               {
                   DropDownListChartType.Items.Add(item);
               }
           }
   
           private Series CreateSeries()
           {
               Series series = new Series("Sales");
               series.BorderWidth = 3;
               series.ShadowOffset = 2;
               return series;
           }
   
           private void SetChartType(Series inSeries)
           {
               if (Page.Request["ChartType"] != null)
                   DropDownListChartType.SelectedValue = Page.Request["ChartType"];
   
               if (DropDownListChartType.SelectedValue.ToString() == "")
                   DropDownListChartType.SelectedValue = SeriesChartType.Column.ToString();
   
               inSeries.ChartType = (SeriesChartType)System.Enum.Parse(typeof(SeriesChartType), DropDownListChartType.SelectedValue.ToString());
           }
   
           private void AddDataToSeries(Series series)
           {
               if (Page.Request["ChartType"] == null)
                   AddAllRegionData(series);
               else
                   AddSpecificRegionData(series);
           }
   
           private void AddAllRegionData(Series series)
           {
               DataTable sales = ExampleData.Tables["RepsSales"];
               DataTable regions = ExampleData.Tables["Region"];
   
               var query = from reps in sales.AsEnumerable()
                           join region in regions.AsEnumerable()
                           on reps.Field<int>("RegionID") equals region.Field<int>("RegionID")
                           group reps by region.Field<string>("RegionName") into regionGroup
                           select new { Region = regionGroup.Key, Sales = regionGroup.Sum(total => total.Field<int>("Sales")) };
   
               // Populate new series with data
               foreach (var value in query)
               {
                   series.Points.AddXY(value.Region, value.Sales);
               }
   
               // Step 7 - Make this series drillable
               for (int i = 0; i < series.Points.Count; i++)
               {
                   series.Points[i].Url = string.Format("RegionReport.aspx?region={0}&ChartType={1}", series.Points[i].AxisLabel, DropDownListChartType.SelectedValue);
               }
           }
   
           private void AddSpecificRegionData(Series series)
           {
               var query = from reps in ExampleData.RepsSalesTable.AsEnumerable()
                           join region in ExampleData.RegionTable.AsEnumerable()
                           on reps.Field<int>("RegionID") equals region.Field<int>("RegionID")
                           where region.Field<string>("RegionName") == (Page.Request["Region"] ?? "East")
                           select new { RepName = reps.Field<string>("Name"), Sales = reps.Field<int>("Sales") };
   
               // Populate new series with data
               foreach (var value in query)
               {
                   series.Points.AddXY(value.RepName, value.Sales);
               }
   
               // Step 7 - Make this series drillable
               for (int i = 0; i < series.Points.Count; i++)
               //{
               //    // Add drill down code to drill to a third chart
               //}
           }
   
           private void AddDrillDown(Series series)
           {
               for (int i = 0; i < series.Points.Count; i++)
               {
                   series.Points[i].Url = string.Format("RegionReport.aspx?region={0}&ChartType={1}", series.Points[i].AxisLabel, DropDownListChartType.SelectedValue);
               }
           }
       }
   }
   

Step 7 – Add an http handler to the Web.Config for the Chart

1. Open the Web.Config file.
2. Add an http handler for the chart.

<?xml version="1.0"?>
<configuration>
  <system.web>
    <compilation debug="true" targetFramework="4.0" />
    <httpHandlers>
      <add path="ChartImg.axd" verb="GET,HEAD,POST" validate="false"
           type="System.Web.UI.DataVisualization.Charting.ChartHttpHandler, System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
    </httpHandlers>
  </system.web>
</configuration>

Your project is now complete.

You now have a report that shows you the total sales per region.

Click on the report and you have the sales per region broken out by sales rep.

And your done.

Download the project here: SampleChart.zip

It is time to learn to write some charts. By charts I mean graphic views for reporting on data.

Obtaining MSChart

For .NET 4, MSChart is included in the .NET Framework, so if you have installed .NET 4, you have already obtained MSChart.

For .NET 3.5, the MSChart project which was an add-on. If you are using .NET 3.5, you need to download and install the add-on.

Note: I am using .NET 4 and it was installed with Visual Studio 2010, so I have no need to install the add-on.

Also, we are going to the very minimal steps manually. Many of these steps may be done for you (for example, the Visual Studio Designer will populate the Web.Config for you, but it is always good to know how do things yourself.

Report Example Use Case

Imagine you have sales trending for four years, 2009-2012, and you want to visualize this trend. You want a chart that should all four years, with the quarter results next to each other.

Download the project here: SampleChart.zip

Step 1 – Create the Visual Studio project

1. In Visual Studio, click on File | New | Project.
2. Select Visual C# | Web from the Installed Templates.
3. Locate and select ASP.NET Empty Web Application.
   Note: I like to demonstrate using an Empty project you nothing is done for you, and you have to learn everything you actually need to do.
4. Give the project a name.
5. Click OK.
6. Right-click on the newly created project and click Add | Reference.
7. Select the .NET tab.
8. Locate System.Web.DataVisualization and highlight it.
9. Click OK.

Step 2 – Add a web form for your chart

1. Right-click on the Project and choose Add |  New Item.
2. Select Web Form.
3. Give the file a name.
   I named my file  Report.aspx.
4. Click OK.

Step 3 – Create a data object for the report

1. Right-click on the Project and choose Add |  Class.
2. Give the file a name.
   I named my file  Data.cs.
3. Click OK.

Step 4 – Add example data to the data object for the report

While in a real world scenario, you would get the data from a database or somewhere, lets first just create some sample data.

1. Create a few lists of numbers, one for each year as shown.

   namespace CompareYearsByQuarter
   {
       public class Data
       {
           public int[] Sales2009 = new int[] { 47, 48, 49, 47 };
           public int[] Sales2010 = new int[] { 47, 50, 51, 48 };
           public int[] Sales2011 = new int[] { 50, 52, 53, 46 };
           public int[] Sales2012 = new int[] { 53, 54, 55, 49 };
       }
   }
   

That is it, your fake example data is prepared.

Step 5 – Add a Chart to the Report.aspx file

1. Open the Report.aspx file.
2. Add a Register to the System.Web.DataVisualization assembly.
3. Locate the div inside the body.
4. Inside the div, add a Chart that includes a ChartArea.

   <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Report.aspx.cs" Inherits="CompareYearsByQuarter.Report" %>
   
   <%@ Register Assembly="System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
       Namespace="System.Web.UI.DataVisualization.Charting" TagPrefix="asp" %>
   
   <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
   <html xmlns="http://www.w3.org/1999/xhtml">
   <head runat="server">
       <title></title>
   </head>
   <body>
       <form id="form1" runat="server">
       <div>
           <asp:Chart ID="SalesReport" runat="server">
               <chartareas>
                   <asp:ChartArea Name="ChartArea1">
                   </asp:ChartArea>
               </chartareas>
           </asp:Chart>
       </div>
       </form>
   </body>
   </html>
   

Step 6 – Add code the Report.aspx.cs file

1. Open the Report.aspx.cs file.
2. Create an instance of the Data object that has our sample data.
3. Add code in the Page_Load method to configure the Chart a separate series of data for each year.

   using System;
   using System.Collections.Generic;
   using System.Linq;
   using System.Web;
   using System.Web.UI;
   using System.Web.UI.WebControls;
   using System.Web.UI.DataVisualization.Charting;
   
   namespace CompareYearsByQuarter
   {
       public partial class Report : System.Web.UI.Page
       {
           Data data = new Data();
   
           protected void Page_Load(object sender, EventArgs e)
           {
               Series year2009 = new Series("Sales 2009");
               // Populate new series with data
               foreach (var value in data.Sales2009)
               {
                   year2009.Points.AddY(value);
               }
               SalesReport.Series.Add(year2009);
   
               Series year2010 = new Series("Sales 2010");
               // Populate new series with data
               foreach (var value in data.Sales2010)
               {
                   year2010.Points.AddY(value);
               }
               SalesReport.Series.Add(year2010);
   
               Series year2011 = new Series("Sales 2011");
               // Populate new series with data
               foreach (var value in data.Sales2011)
               {
                   year2011.Points.AddY(value);
               }
               SalesReport.Series.Add(year2011);
   
               Series year2012 = new Series("Sales 2012");
               // Populate new series with data
               foreach (var value in data.Sales2012)
               {
                   year2012.Points.AddY(value);
               }
               SalesReport.Series.Add(year2012);
           }
       }
   }
   

Step 7 – Add an http handler to the Web.Config for the Chart

1. Open the Web.Config file.
2. Add an http handler for the chart.

<?xml version="1.0"?>
<configuration>
  <system.web>
    <compilation debug="true" targetFramework="4.0" />
    <httpHandlers>
      <add path="ChartImg.axd" verb="GET,HEAD,POST" validate="false"
           type="System.Web.UI.DataVisualization.Charting.ChartHttpHandler, System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
    </httpHandlers>
  </system.web>
</configuration>

You are done. Build and look at your report.

You now have a simple report that should show you the sales trend for quarters 1,2,3,4 over four years. Your chart should look like this.

Download the project here: SampleChart.zip

Ok, so I am into FreeBSD and open source software, but I have recently had to do a QlikView implementation for my company LANDesk. QlikView has a feature where you can pull data from the web and report on it. So I needed to learn how to use the web-based reporting, so I decided to do a report from www.distrowatch.com.

Report Goals
There are few things that interests me from the data at DistroWatch:

• Which base platforms are the most used?
• Which platforms should software companies focus on supporting?
• Where does BSD sit in the rankings.

How the report was made
So on the main DistroWatch page, there is a report that will give you the average hits per day (hpd) that a Distro’s web site gets. At the bottom there is a link to full popularity page of just these reports:
http://distrowatch.com/stats.php?section=popularity

So at first glance, you see Ubuntu is the best and Fedora is second and so on. I wanted to take the statistics a bit further. I wanted to know what main base distribution was the most used. What I mean by base distro is this: Ubuntu is #1. But Ubuntu is not a base distribution, instead it is based on Debian. Mint is #3 and is also based on Debian. Debian itself is #6 and it is a base distribution. Fedora is a base distribution.

QlikView can connect to this web page and consume this data. It was also able to loop through and click go to the link for each distribution where it was able to pull the “Based on” results. I did a few little tweaks to clean it up.

So I used QlikView to match each Distribution to its base distribution and built my report. I gathered the cumulative hits per day (hpd) of each base distro by summing the hpd from itself and its child distros. The results are staggering.

Result of the Report
I am going to show you a screen shot of the report, but I am only going to show the top 10 base distributions because otherwise it is to hard to view the report.

# 1 – Debian
Well, I have to say that I new that Debian (13818 hpd) was popular because of Ubuntu, but I didn’t know how far ahead it was compared to other base distributions. I expected Red Hat to be a lot closer but its just not. Lets look at the top ten Debian platforms by hits. In QlikView this is easy, I can simply click on the Debian pillar in the report.

So not only is Debian’s cumulative hits per day first, but it is first by a long ways. The cumulative hits per day of distros based on Debian is more than three times larger than any other base distribution’s cumulative hits. It is pulling away from the pack and nobody is going to catch up any time soon.

What I don’t know is are these new users or are other distributions losing members to Debian or Debian-based distros?

You might be grumbling to yourself and saying some incorrect statement like: Well, Ubuntu doesn’t have Enterprise support like Red Hat. But like I said, that is an incorrect statement. See their support page:
http://www.ubuntu.com/support

# 2 – Red Hat
Now, lets look at the top ten distros under Red Hat.

Ok, can I tell you that I was surprised at these results. I realize that Fedora was huge, I mean it is second on the distro list under Ubuntu, but I had missed the fact that CentOS was getting more than twice the hits Red Hat itself gets. The rest are hardly worth mentioning.

Historically mong Enterprise environments Red Hat is the most known distro, but when you look at these stats, you have to wonder if Ubuntu has taken over. The numbers for Fedora are fine, but for Red Hat they are not really that good. In fact, I keep hearing about companies using CentOS instead of Red Hat and as you can see, CentOS is getting a lot more exposure than Red Hat.

I will make this statement. Based on this data, if you are a software company considering whether to support Debian or Red Hat first, based on this data you have to choose Debian. If you were to make up some fuzzy logic for Red Hat (which due to its enterprise presence may or may not actually be valid) and weight the distributions based on other factors and somehow found a way to say Red Hat and its distro’s cumulative hits per day were worth three for every one, it would still be less than the cumulative hits per day Debian gets.

# 3 and #4 – Mandriva and Slackware
Ok, back to the report. Something that shocked me from the first chart and I had to analyze it further. Slackware? I had no idea that it was third. However, is it really third? It has a lot of very small distros based on it and Slackware itself gets 590 hpd and most the distros get less than 100 hpd. Mandriva is fourth but arguable could be third over Slackware. In fact, I have to call Mandriva third over Slackware. Sometimes you have to look at the data and make a judgment like this. Sorry Slackware, I am not trying to be biased (otherwise I would be talking up FreeBSD). I have no bias to any Linux distribution. I just say this based on the fact that Mandriva (1048 hpd) and the based-on-Mandriva version PCLinuxOS (773 hpd) both get more hits by a long way than Slackware’s top distros. The only reason Slackware got more hpd was because it has a lot of distros that were really small, while there were very few small distros based on Mandriva. The difference in the amount of small base distros is most likely due to the fact that Slackware is one of the oldest Linux distros, if not the oldest remaining distro, so naturally it has more distros based on it.

# 5 – Gentoo
Gentoo’s cumulative 1804 hpd was fourth. I have to apologize to Sabayon (760 hpd) as I had never heard of it until now. Gentoo itself only gets 428 hpd.

# 6 – BSD
What is next. Well, finally BSD shows up at number 6 with 1743 hpd. For those of you that are reading this and only know about Linux, BSD is NOT Linux. It does not run on the Linux kernel and is not likely to use many GNU tools. I hope I don’t drip with too much bias as FreeBSD is my favorite open source distribution.

Lets pull up the chart of BSD distros. There are 15 distributions listed under BSD, which is probably more than most people would believe since BSD often claims that it is not as broken up as Linux, but it has had its share of forks.

FreeBSD (553 hpd) is the main distribution. Of the Linux distributions, only Debian has more software packages available than FreeBSD.

PC-BSD (355 hpd) is to FreeBSD as Ubuntu is to Debian. For being such a new distribution PC-BSD is doing rather well. It is pretty comparable in ease of use to Ubuntu, Fedora, and OpenSUSE. Yes, PC-BSD is fully featured, running a nice windows environment with everything you could want, including a working Flash Player, the ability to configure your wireless card, and more. I recommend that if you are looking for a new desktop distribution, you at least install PC-BSD and give it a try. Ok, so my bias does show a little here.

# 7 – SUSE
So I was very surprised that SUSE wasn’t on this list until #7. Well, OpenSUSE is doing its part getting 1327 hpd. Remember, OpenSUSE is #4 if you just go by distro and not cumulative base distros. I think in time SUSE could be more popular. SUSE is newer than some of the other base distros and so it only has four distros listed. Novell’s SUSE Linux Enterprise (121 hpd) is the second most popular SUSE distro, however, it just not getting any were near the hits I expected it to be getting.

The others
And then there are the rest of the top ten: #8 Arch, #9 Puppy, and #10 Solaris (Or is that Oracle now?). Sorry if your distro was left out, this report is in the control of those who visit the distro’s web pages.

How accurate is this data?
On DistroWatch’s popularity page, it says:

The Page Hit Ranking statistics have attracted plenty of attention and feedback. Originally, each distribution-specific page was pure HTML with a third-party counter at the bottom to monitor interest of visitors. Later the pages were transformed into plain text files with PHP generating all the HTML code, but the original counter remained unchanged. In May 2004 the site switched from publicly viewable third-party counters to internal counters. This was prompted by a continuous abuse of the counters by a handful of undisciplined individuals who had confused DistroWatch with a voting station. The counters are no longer displayed on the individual distributions pages, but all visits (on the main site, as well as on mirrors) are logged. Only one hit per IP address per day is counted.

There are other factors to consider, such as the fact that some of the distributions are Live CD distros and not really platforms meant to be installed. It would be interesting to exclude them and only include installable distros but for lack of time, I didn’t.

I did nothing to verify the accuracy of the data at DistroWatch and any errors you see are not likely mine, as all the data was pulled from DistroWatch, please report any error to them and once they fix these errors, the QlikView report’s data can be reloaded.

Also, this data includes all hits from all areas: Consumer, Enterprise, Education, etc. Unfortunately there is no way I know of to tell where the hits came from. If there is a distribution that is 100% education hits, there would be no way to know that. Obviously if your target is Enterprise, you are left wondering which open source distros are really the most used in Enterprise environments. Unfortunately this report doesn’t answer that question. This is not a report of installed platforms, it is a report of cumulative hits per day. It is what it is.

Copyright ® Rhyous.com – Linking to this article is allowed without permission and as many as ten lines of this article can be used along with this link. Any other use of this article is allowed only by permission of Rhyous.com.

Ok, so I had a problem today that I couldn’t find any documentation on. I needed to remove a single quote from a variable in a string during a load script but it took some time.

I tried this but it didn’t work:

purgechar(‘It’s got a quote’, ‘\”)

It turns out that QlikView doesn’t seem to have an escape character, so I had to figure out another method. Google searching for this string didn’t help: single quote Qlikview escape character

So I figured it out. You have to use the chr() function.

purgechar(‘It’s got a quote’, char(39))

You can get the correct ascii number to pass to the char() function from here:
http://www.asciitable.com/

So hopefully if you do the same google search you will find my post and not have to spend as much time as I did trying to figure this out.

Why does the calendar date field and the date field of some other table some times not match up in QlikView?

Ok, so I have two tables, one a Calendar table and one a table of of support cases (pulled from Salesforce). They both have a column called CalendarDate and both have the same date values. The table for support cases looks something like this (obviously this is a minimal example).

CaseNumber, CalendarDate
123456, 1/1/2010
123457, 1/2/2010
123458, 1/2/2010
123459, 1/2/2010
123460, 1/2/2010
123461, 1/3/2010
123462, 1/3/2010
123463, 1/3/2010

The Date value is calculated from a field and converted using the Date() function: Date(sourcecolumn)

The Calendar table of course has days and months, etc…For more information about my calendar, go here:
http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/

Anyway, I should be able to create a chart that has CalendarDate as the Dimension and Count(CaseNumber) as the Expression and get something like the following:

However, it wouldn’t work. This is too simple to possibly fail, right? Is it a QlikView bug? Or a bug with the Salesforce plugin?

What is interesting, is that if I do a select field, which only shows unique values, all the values show up:
…
1/1/2010
1/1/2010
1/2/2010
1/2/2010
1/2/2010
1/2/2010
1/2/2010
1/3/2010
1/3/2010
1/3/2010
1/3/2010
…

Now that is just not right. They are the same value right? Well, obviously not if only unique values show.

So how could they be different?

Well, QlikView doesn’t actually store the date, it stores a number. So obviously the number is different somehow. Maybe it is because my Calendar creates the CalendarDate using an integer but the Salesforce date values come in as doubles.

So here is why I think this is a Salesforce bug. I am calling Date() against the value and it is creating the 1/1/2010.

I have to say that I feel that QlikView needs to normalize this data, so that a Date created using Date(Integer) that returns 1/1/2010 and a Date created using Date(Double) that returns 1/1/2010 should match.

I did find a simple workaround. If I do this to round the double to an integer, it works.

Date(Round(DateAsDoubleValue - .5, 1)) as CalendarDate,

I am not sure if this is by QlikView design, but personally, I would prefer that If I call Date() on any data type, the return values should be normalized and match.

Ok, lets say you have a table with seconds in it.

First use this function to convert it to a time based number:

Time#(TimeInSeconds,’ss’)

Second, wrap that

Time(Time#(TimeInSeconds,’ss’), ‘m:ss’)

Now your table will look like this:

While there is more to this, as you will see with a wide range of data, this should get you started and you should be able to go from here.

Ok, so here is what I am doing:

1. I am storing Salesforce data in a QVD file.
2. I am getting the QVD file date using:

   Let QVDPath='C:\ProgramData\QlikTech\QVD';
   Let ThisExecTime = Reloadtime();
   Let LastExecTime = QvdCreateTime('$(QVDPath)\SFCase.qvd');
   

3. I am trying to only get data modified since load using WHERE LastModifiedDate > $(LastExecTime ).

According to salesforce, the time stamp should be in this format:

1999-01-01T23:01:01Z

See this page for more detailed information and alternate formats:
http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_select_dateformats.htm

So I created this sub to get Salesforce time from QlikView time:

Sub SFTime(inDateTime)
    Let inDateTime=date(inDateTime, 'YYYY-MM-DD') & 'T' & time(inDateTime, 'HH:MM:SS') & 'Z';
EndSub

It works.

In QlikView 9, How to report on what Support Cases were open on any given date using open date and close date?

So here is what I have in the most basic terms:
1. A Calendar (see my previous post)
My new and improved Calendar in QlikView 9

2. A table loaded from a Ticketing/Case Management database (in this instance it is Salesforce but if could be any ticketing system).

Load CaseId as CaseNumber,
	CreatedDate as DateOpened
SQL SELECT CaseId,
	CreatedDate as DateOpened,
	ClosedDate as DateClosed,
FROM Case;

The table loaded would look like this (only I would have exact times to the second for open and closed dates:

So each of these cases have a range of time they were open.
For example, Case 0001 was opened on 10/2/09. It continues to be open at the start of the day on 10/3/09, 10/4/09, and 10/5/09 before it was closed later in the day on 10/5/09.

So I have a MasterCalendar that has every day in it. I want to click on 10/4/09 and have this case show as being opened on that day.

I will be getting this to work this week, I hope. So stay in touch.

….

I am back, and much faster than I thought I would be.

Ok, so you won’t have my Salesforce database, so I created an inline load of data you can use to see this work.

Cases:
LOAD * INLINE [
CaseNumber,	DateOpened,	DateClosed
	0001,	11/2/09,	11/5/09
	0002,	11/2/09,	11/12/09
	0003,	11/3/09,	11/4/09
	0004,	11/3/09,	11/9/09
	0005,	11/3/09,	11/1/09
	0006,	11/4/09,	11/4/09
	0007,	11/4/09,	11/3/09
	0008,	11/4/09,	11/12/09
	0009,	11/4/09,	11/4/09
	0010,	11/5/09,	11/9/09
	0011,	11/5/09,	11/6/09
	0012,	11/5/09,	11/6/09
	0013,	11/6/09,	11/6/09
	0014,	11/6/09,	11/9/09
	0015,	11/6/09,	11/9/09
	0016,	11/9/09,	11/11/09
	0017,	11/9/09,	11/21/09
	0018,	11/10/09,	11/13/09
	0019,	11/10/09,	11/13/09
	0020,	11/11/09,	11/21/09
	0021,	11/12/09,	11/31/09
	0022,	11/13/09,	11/115/09
	0023,	11/16/09,	11/17/09
	0024,	11/16/09,	11/31/09
	0025,	11/17/09,	11/31/09
	0026,	11/17/09,	11/31/09
	0027,	11/17/09,	11/25/09
	0028,	11/18/09,	11/23/09
	0029,	11/19/09,	11/27/09
	0030,	11/20/09,	11/21/09
	0031,	11/23/09,	11/23/09
	0032,	11/23/09,	11/23/09
	0033,	11/24/09,	11/25/09
	0034,	11/24/09,	11/26/09
	0035,	11/25/09,	11/29/09
	0036,	11/25/09,	11/31/09
	0037,	11/26/09,	11/30/09
	0038,	11/27/09,	11/31/09
	0039,	11/27/09,	11/30/09
	0040,	11/30/09,	11/31/09
	0041,	11/30/09,	11/31/09
	0042,	11/30/09,	11/31/09
	0043,	11/31/09,	11/31/09
	0044,	11/31/09,	12/1/09
	0045,	11/31/09,	12/1/09
];

Now just do this, and you have your information that you need.

LEFT JOIN ([MasterCalendar])
INTERVALMATCH (CalendarDate)
LOAD
	DateOpened,
	DateClosed
RESIDENT [Cases];

LEFT JOIN ([MasterCalendar])
LOAD
	CaseNumber,
	DateOpened,
	DateClosed
RESIDENT [Cases];

Now load this script up with the calendar script and you can see how it works by playing with a few charts.

…

Ok I am back with a problem.

PROBLEM
If the case is not closed yet, it is not being counted. I need to fix that, because that is not going to work for me. I need to count the cases that are currently open.

So back to research mode…I will update you as I can.

See this forum post:
http://community.qlikview.com/forums/t/23247.aspx

Solved my own question by continuing to RTM.

I needed to add the following just before my Load Statement:

NullAsValue SFCaseDateClosed;

Hey all,

After working with QlikView for a few days, and working with the calendar, here is my new and improved load script for a Calendar.

Calendar:
LET vDateMin = Num(MakeDate(2000,1,1));
LET vDateMax = Floor(YearEnd(Today()));

TempCalendar:
LOAD
$(vDateMin) + RowNo() – 1 AS DateNumber,
Date($(vDateMin) + RowNo() – 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax); MasterCalendar: LOAD TempDate AS CalendarDate, // Standard Date Objects Day(TempDate) AS CalendarDay, WeekDay(TempDate) AS CalendarWeekDay, Week(TempDate) AS CalendarWeek, Month(TempDate) AS CalendarMonth, 'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter, Year(TempDate) AS CalendarYear, // Calendar Date Names DayName(TempDate) as CalendarDayName, WeekName(TempDate) as CalendarWeekName, MonthName(TempDate) as CalendarMonthName, QuarterName(TempDate) as CalendarQuarterName, YearName(TempDate) as CalendarYearName, // Start Dates DayStart(TempDate) as CalendarDayStart, WeekStart(TempDate) as CalendarWeekStart, MonthStart(TempDate) as CalendarMonthStart, QuarterStart(TempDate) as CalendarQuarterStart, YearStart(TempDate) as CalendarYearStart, // End Dates DayEnd(TempDate) as CalendarDayEnd, WeekEnd(TempDate) as CalendarWeekEnd, MonthEnd(TempDate) as CalendarMonthEnd, QuarterEnd(TempDate) as CalendarQuarterEnd, YearEnd(TempDate) as CalendarYearEnd, // Combo Dates 'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear RESIDENT TempCalendar ORDER BY TempDate ASC; DROP TABLE TempCalendar; LET vDateMin = Null(); LET vDateMax = Null(); [/sourcecode] It is much better and more complete than the previous one I had. Update: Here is what I am using now. Almost the same, but not quite: [sourcecode language="sql"] ///$tab Calendar Calendar: LET vDateMin = Num(MakeDate(2003,1,1)); LET vDateMax = Floor(MonthEnd(Today())); LET vDateToday = Num(Today()); TempCalendar: LOAD $(vDateMin) + RowNo() - 1 AS DateNumber, Date($(vDateMin) + RowNo() - 1) AS TempDate AUTOGENERATE 1 WHILE $(vDateMin)+IterNo()-1<= $(vDateMax); Calendar: LOAD Date(TempDate) AS CalendarDate, // Standard Date Objects Day(TempDate) AS CalendarDayOfMonth, WeekDay(TempDate) AS CalendarDayName, Week(TempDate) AS CalendarWeekOfYear, Month(TempDate) AS CalendarMonthName, 'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter, Year(TempDate) AS CalendarYear, // Calendar Date Names WeekName(TempDate) as CalendarWeekNumberAndYear, MonthName(TempDate) as CalendarMonthAndYear, QuarterName(TempDate) as CalendarQuarterMonthsAndYear, // Start Dates DayStart(TempDate) as CalendarDayStart, WeekStart(TempDate) as CalendarWeekStart, MonthStart(TempDate) as CalendarMonthStart, QuarterStart(TempDate) as CalendarQuarterStart, YearStart(TempDate) as CalendarYearStart, // End Dates DayEnd(TempDate) as CalendarDayEnd, WeekEnd(TempDate) as CalendarWeekEnd, MonthEnd(TempDate) as CalendarMonthEnd, QuarterEnd(TempDate) as CalendarQuarterEnd, YearEnd(TempDate) as CalendarYearEnd, // Combo Dates 'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear, Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter, 'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays RESIDENT TempCalendar ORDER BY TempDate ASC; DROP TABLE TempCalendar; LET vDateMin = Num(MakeDate(2000,1,1)); LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12))); LET vDateToday = Num(Today()); STORE Calendar INTO C:\ProgramData\QlikTech\Support\QVD\Calendar.qvd; [/sourcecode]

How to create a Calendar in QlikView 9?

UPDATE: Check out my new calendar here: http://rhyous.com/2009/11/30/my-new-and-improved-calendar-in-qlikview/

Ok, so the fact that I cannot just have one line in a Load Script is a negative for QlikView. In a perfect world, I would have one line that would give me a bunch of possible values I could use for a dimension, such as CalendarDay, CalendarWeek, CalendarMonth, CalendarQuarter, CalendarYear, etc… It would be one line like this:

Alas…it is not a perfect world, so this feature doesn’t exist in QlikView. (Enhancement Request please!!!!)

So there is a Wiki on how to do it. Here is the link.
http://community.qlikview.com/wikis/qlikview-wiki/how-to-create-a-calendar.aspx

However, the problem is that this didn’t work.

So after some research I remember that internet search engines exist and I don’t have to just search QlikView’s site and documentation. I did a google search for this string:
qlikview how to create a calendar

The script didn’t fail to load…yeah…wait…there is not data in my report that has to do with a Calendar.

This sucks. Why can’t I just create a new Calendar. This is common problem with some software companies. There is a “key features” that can be done, but with great difficulty. However, because it can be done, they don’t spend any more development time on it.

Anyway, I added a post in the QlikView Forum and watched the QlikView free training Video for developers (especially module 8).

Here is the result:

LET vDateMin = Num(MakeDate(2000,1,1));
LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));
LET vDateToday = Num(Today());

TempCalendar:
LOAD
$(vDateMin) + RowNo() – 1 AS DateNumber,
Date($(vDateMin) + RowNo() – 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax); MasterCalendar: LOAD TempDate AS CalendarDate, Day(TempDate) AS CalendarDay, WeekDay(TempDate) AS CalendarWeekDay, Week(TempDate) AS CalendarWeek, Month(TempDate) AS CalendarMonth, Year(TempDate) AS CalendarYear, 'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter, WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekAndYear, Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear RESIDENT TempCalendar ORDER BY TempDate ASC; DROP TABLE TempCalendar; LET vDateMin = Num(MakeDate(2000,1,1)); LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12))); LET vDateToday = Num(Today()); [/sourcecode] Now when your script loads, you can right click and choose New Sheet Object, Slider/Calendar Object. Choose Calender, not Slider and base it off of the CalendarDate field. Also on the Sort tab, use the Numeric Value to change the sort to Descending.

Ok, so at work I installed JasperServer and spent quite a lot of time in their documentation and could find the basic information of how to connect to a Microsoft SQL 2008 server.

JasperSoft has a nice installer that installs everything for you: JasperServer, Tomcat, Java, MySQL, etc…

The first problem was I had a 2008 R2 64 bit VM that I thought I would use that OS and their installer just crashed with MySQL errors. So I installed MySQL myself, and then reinstalled JasperServer and pointed to my MySQL install and it appeared to work better but there were still a lot of errors in normal use that I couldn’t find documentation on. So I am now using a 2003 R2 SP2 server and everything installed just fine, first time.

I thought Microsoft was really friendly about deploying their JDBC driver for SQL 2005/2008 and that anyone could distribute it as long as they just told Microsoft first, (maybe I am wrong and maybe MS charges to distribute it). Anyway, I expected JasperServer to install the JDBC driver for me (included in their all-encompassing package) but to my dismay, the driver wasn’t there. So that is fine, I was certain there would be a quick and easy document on how add SQL 2008 as a database. Yeah, three days later, still no documentation found….

Finally, with almost no help from the JasperServer documentation, I figured out that really it was Tomcat that needed the JDBC driver, not JasperServer. Well, it turns out that Tomcat no longer uses the CLASSPATH environment variable, so I just had to copy the SQL 2008 JDBC .jar file to a directory Tomcat did look at and I was done. It was simple.

1. Download the Microsoft JDBC driver: Microsoft SQL Server JDBC Driver
2. Extract to a good location. I chose c:\program files to extract to.
3. Copy the sqljdbc4.jar to your installation directory which by default is:
   c:\program files\jasperserver-pro-3.5.1\apache-tomcat\webapps\jasperserver-pro\WEB-INF\lib

4. Restart JasperServer (there is a shortcut in the start menu to restart it).
5. Log into JasperServer’s web site. http://YourServer:8080/jasperserver-pro
6. Go to View | Repository.
7. Click the second icon called Add Resource and select Data Source.
8. Choose JDBC Data Source and click next.
9. Provide any Name, Label, and Description.
10. For the driver put this:
    com.microsoft.sqlserver.jdbc.SQLServerDriver

11. For the URL put this:
    jdbc:sqlserver://ServerName:1433:databaseName=MyDatabase;

12. Enter the username and password.
13. Select a time zone.
14. Click Test and it should succeed.

Troubleshooting: If it doesn’t succeed, save anyway and restart the JasperServer and watch for any exceptions.

Wow that was easy once I figured it out. Too bad that the lack of good documentation resulted in me spending three days to figure out that I just needed to spend three seconds to copy a file.

JasperServer score card

Install = 8 (Minus two points for not working Server 2008 R2 64 bit)
Documentation = 0 (no points as so far I have not found a document that has been helpful yet.

Also I note that the JasperForge (the opensource part of JasperSoft) has a wiki, but it is nothing but headers and the data is pretty much blank.

I am not trying to knock JasperSoft with this post, but I am just trying to make sure that someone who tries to do the same thing ends up finding my post and getting their SQL 2008 server added as a data source rather quickly, instead of wading through the lack of documentation for three days.

Even to show my good will towards open source projects, I documented my steps on their wiki for them.
http://jasperforge.org/plugins/mwiki/index.php/Jasperserver/DataSources#Adding_a_Microsoft_SQL_Server_2008_Database_as_a_Data_Source