Simple Web Report With Pivot Table and Linq to Entity in VB.NET

Pivot Table is a way to quickly summarize data and information from your database and also an useful way to organize and present data in your reports.

Usually in SQL server we can use the PIVOT statement in SQL SELECT, something like this:

SELECT <non-pivoted column>,

[first pivoted column] AS <column name>,

[second pivoted column] AS <column name>,

...

[last pivoted column] AS <column name>

FROM

(<SELECT query that produces the data>)

AS <alias for the source query>

PIVOT

(

<aggregation function>(<column being aggregated>)

FOR

[<column that contains the values that will become column headers>]

IN ( [first pivoted column], [second pivoted column],

... [last pivoted column])

) AS <alias for the pivot table>

<optional ORDER BY clause>;

 

Imagine to have a classic Northwind database with Orders table, table structure is like this:

ordered-table-in-windows8.jpg

We can build a pivot table with this query:

SELECT ShipCountry AS Countries, [1996], [1997], [1998]

FROM

(SELECT OrderID, ShipCountry, YEAR(OrderDate) AS Years

FROM Orders) ShipsTable

PIVOT

(

COUNT(OrderID)

FOR Years IN ([1996], [1997], [1998])

) AS PivotTable

ORDER BY ShipCountry

This is the output of the query within management studio:

build-report-in-windows8.jpg

BUILD THE REPORT

Now we want  to explain how to use pivot table within an Entity Model and LINQ syntax, we use the same example above, so you can easily understand differences between the two ways to operate.

The first thing that we must do is to create an empty ASP.NET project, now we can add a default webform called "Default.aspx" and with only a simple panel called "panelPivotTable" in it:

<body>

    <asp:Panel ID="panelPivotTable" runat="server">

    </asp:Panel>

</body>

 

In this panel we'll show a table with the representation of the pivot table data. The next thing is to add an Entity Data Model when we'll map the Orders table of Northwind database:

pivot-table-in-windows8.jpg

At this point we can write code in the code behind file "Default.aspx.vb", is useful pay particular attention to the part of the code in which we write the SQL Pivot table with LINQ syntax:

 

            Dim query = From g In _

                        (From o In db.Orders _

                         Group o By o.ShipCountry Into Group)

                         Let m1996 As String = (From r In g.Group _

                                                Where r.ShippedDate.Value.Year = "1996" _

                                                Select r.ShipCountry).Count _

                         Let m1997 As String = (From r In g.Group _

                                                Where r.ShippedDate.Value.Year = "1997" _

                                                Select r.ShipCountry).Count _

                         Let m1998 As String = (From r In g.Group _

                                                Where r.ShippedDate.Value.Year = "1998" _

                                                Select r.ShipCountry).Count _

                         Select New With { _

                                                .Country = g.ShipCountry, _

                                                .y1996 = m1996, _

                                                .y1997 = m1997, _

                                                .y1998 = m1998 _

}

 

What we are doing is to make a query grouped by ShipCountry field, the keyword "Let" is an useful way to storing the results of a query in a new variable initialized with the result of the expression, so in our example we are storing in String variable m1996 the result of query "(From r In g.Group r.ShippedDate.Value.Year = "1996" Select r.ShipCountry).Count" and so on for the others years.

Then we retrieve the valorized values in new variables within Select New statement, you can notice that the variables (ex. .Country, .y1996 etc.) have a dot before the name, this dot is needed when we create new customized fields in a Select statement. Now that we know how to retrieve a pivot table from our database we can build the report we can loop through data and write values in a table.

 

        For Each item In query

            row = New TableRow

            cell = New TableCell

            cell.Text = item.Country

            row.Cells.Add(cell)

........... 

     table.Rows.Add(row)

        Next
        panelPivotTable.Controls.Add(table)

 

And this is the result of our work:

 

result-in-windows8.jpg

 

I hope that this tutorial will help you in development of easy little reports as you can see in the attached source project.

Up Next
    Ebook Download
    View all
    Learn
    View all