Solved

Query report to Datagridview before print it to printer.

Posted on 2008-10-01
9
347 Views
Last Modified: 2012-05-05
Hi,

I want to query datat and show it with datagridview. I need to have a subtotal and grandtotal row like using report writer.

Could it be possible to do that?

Thank you.
0
Comment
Question by:emi_sastra
  • 5
  • 4
9 Comments
 
LVL 18

Expert Comment

by:vbturbo
ID: 22657323
Try this.  One form, one datagridview - called dgv - two buttons.  This code

vbturbo

 

Public Class Form1
 

    Private dt As New DataTable("TestTable")
 

    Private Sub fillTable()

        Dim dc1 As New DataColumn("Hours", GetType(Decimal))

        dt.Columns.Add(dc1)

        Dim dc2 As New DataColumn("Rate", GetType(Decimal))

        dt.Columns.Add(dc2)

        For i As Integer = 1 To 6

            Dim dr As DataRow = dt.NewRow

            dr(0) = i + 0.5

            dr(1) = i * 0.5

            dt.Rows.Add(dr)

        Next

    End Sub
 

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        fillTable()  'for demo, equivalent to using dataadapter.fill

        dgv.DataSource = dt

        Button1.Text = "Row Totals"

        Button2.Text = "Column Totals"

    End Sub
 

    Private Sub DoRowTotals()

        If dt.Columns.Count > 2 Then Exit Sub 'already done

        Dim dc As New DataColumn("Total", GetType(Decimal))

        dc.Expression = "Hours * Rate"

        dt.Columns.Add(dc)

    End Sub
 

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        DoRowTotals()

    End Sub
 

    Private Sub doColumnTotals()

        Dim msg As String = ""

        For Each dc As DataColumn In dt.Columns

            Dim thisTotal As Decimal = dt.Compute("Sum(" & dc.ColumnName & ")", "")

            msg &= dc.ColumnName & " Total is " & thisTotal.ToString & vbCrLf

        Next

        MsgBox(msg)

    End Sub
 

    Private Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click

        doColumnTotals()

    End Sub

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 22657551
Hi vbturbo,

I have tried it, I mean print report like using report writer (i.e. Crystal Report).

Item Code  Item Name   Trs Date        Qty

A               Coca Cola   01/10/2008    10
A               Coca Cola   02/10/2008    6
Sub Total                                            16

B               Fanta           01/10/2008    5
B               Fanta           02/10/2008    2
Sub Total                                            7

Grand Total                                         23

The above data is get from querying of table.

Thank you.
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 22658101
Okay then try this

     Private dt As New DataTable("TestTable")

    Private Sub fillTable()
        Dim dc0 As New DataColumn("Item Code", GetType(String))
        dt.Columns.Add(dc0)
        Dim dc1 As New DataColumn("Item Name", GetType(String))
        dt.Columns.Add(dc1)
        Dim dc2 As New DataColumn("Qty", GetType(Decimal))
        dt.Columns.Add(dc2)

        Dim tmpString As String = "Coca Cola"

        For i As Integer = 1 To 5
            Dim dr As DataRow = dt.NewRow
            If i > 3 Then
                dr(0) = "B"
                dr(1) = "Fanta"
            Else
                dr(0) = "A"
                dr(1) = "Coca Cola"
            End If
            dr(2) = i * 1.5
            dt.Rows.Add(dr)
        Next
    End Sub

    'Item Code  Item Name   Trs Date        Qty

    'A               Coca Cola   01/10/2008    10
    'A               Coca Cola   02/10/2008    6
    'Sub Total                                            16

    'B               Fanta           01/10/2008    5
    'B               Fanta           02/10/2008    2
    'Sub Total                                            7

    'Grand Total                                         23
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        fillTable()  'for demo, equivalent to using dataadapter.fill

        Dim view As New DataView(dt)
        Dim drws() As DataRow
        'Sort the item code column desending
        view.Sort = "[Item Code] DESC"

        Dim tmpString As String
        Dim TotalForItem As Double
        Dim Items As New List(Of String)
        'do the sub totals
        Dim tbl As DataTable = dt.Clone()

        For Each dr As DataRow In dt.Rows

            If Items.Contains(dr("Item Code")) = False Then
                tmpString = (dr("Item Code"))


                Items.Add(tmpString)
                TotalForItem = dt.Compute("SUM(Qty)", "[Item Code] = '" & tmpString & "'")

                drws = dt.Select("[Item Code] = '" & tmpString & "'")
                For Each dr1 As DataRow In drws
                    tbl.ImportRow(dr1)
                Next
                dr = tbl.NewRow()
                dr("Item Code") = "Sub total item " & tmpString
                dr("Item Name") = ""
                dr("Qty") = TotalForItem
                tbl.Rows.Add((dr))

            End If

        Next

       
        dgv.DataSource = tbl
        dgv.AllowUserToAddRows = False
        Dim style2 As New System.Windows.Forms.DataGridViewCellStyle
        style2.BackColor = Color.Yellow
        style2.ForeColor = Color.Blue

        Dim row As DataGridViewRow

            For Each row In dgv.Rows()

            If row.Cells(1).Value = "" Then
                row.DefaultCellStyle = style2
            End If

            Next


        'Button1.Text = "Row Totals"
        'dgv.Sort("Item Code")
    End Sub

vbturbo
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 22658334
Sorry forgot to calculate the Grand total

Private dt As New DataTable("TestTable")

    Private Sub fillTable()
        Dim dc0 As New DataColumn("Item Code", GetType(String))
        dt.Columns.Add(dc0)
        Dim dc1 As New DataColumn("Item Name", GetType(String))
        dt.Columns.Add(dc1)
        Dim dc2 As New DataColumn("Qty", GetType(Decimal))
        dt.Columns.Add(dc2)

        Dim tmpString As String = "Coca Cola"

        For i As Integer = 1 To 5
            Dim dr As DataRow = dt.NewRow
            If i > 3 Then
                dr(0) = "B"
                dr(1) = "Fanta"
            Else
                dr(0) = "A"
                dr(1) = "Coca Cola"
            End If
            dr(2) = i * 1.5
            dt.Rows.Add(dr)
        Next
    End Sub

    'Item Code  Item Name   Trs Date        Qty

    'A               Coca Cola   01/10/2008    10
    'A               Coca Cola   02/10/2008    6
    'Sub Total                                            16

    'B               Fanta           01/10/2008    5
    'B               Fanta           02/10/2008    2
    'Sub Total                                            7

    'Grand Total                                         23
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        fillTable()  'for demo, equivalent to using dataadapter.fill

        Dim view As New DataView(dt)
        Dim drws() As DataRow
        'Sort the item code column desending
        view.Sort = "[Item Code] DESC"

        Dim tmpString As String
        Dim TotalForItem As Double
        Dim Items As New List(Of String)
        'do the sub totals
        Dim tbl As DataTable = dt.Clone()

        For Each dr As DataRow In dt.Rows

            If Items.Contains(dr("Item Code")) = False Then
                tmpString = (dr("Item Code"))


                Items.Add(tmpString)
                TotalForItem = dt.Compute("SUM(Qty)", "[Item Code] = '" & tmpString & "'")

                drws = dt.Select("[Item Code] = '" & tmpString & "'")
                For Each dr1 As DataRow In drws
                    tbl.ImportRow(dr1)
                Next
                dr = tbl.NewRow()
                dr("Item Code") = "Sub total item " & tmpString
                dr("Item Name") = ""
                dr("Qty") = TotalForItem
                tbl.Rows.Add((dr))

            End If

        Next


       
        dgv.DataSource = tbl
        dgv.AllowUserToAddRows = False
        Dim style2 As New System.Windows.Forms.DataGridViewCellStyle
        style2.BackColor = Color.Yellow
        style2.ForeColor = Color.Blue

        Dim row As DataGridViewRow

            For Each row In dgv.Rows()

            If row.Cells(1).Value = "" Then
                row.DefaultCellStyle = style2
            End If

            Next
        Dim dr2 As DataRow

        dr2 = tbl.NewRow()
        dr2("Item Code") = ""
        dr2("Item Name") = ""
        dr2("Qty") = 0
        tbl.Rows.Add((dr2))

        dr2 = tbl.NewRow()
        dr2("Item Code") = "Grand total item "
        dr2("Item Name") = ""
        dr2("Qty") = dt.Compute("SUM(Qty)", "[Item Code] <> ''")
        tbl.Rows.Add((dr2))

        'Button1.Text = "Row Totals"
        'dgv.Sort("Item Code")
    End Sub
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:emi_sastra
ID: 22658502
It works, but could we do it using query directly?

Thank you.
0
 
LVL 18

Accepted Solution

by:
vbturbo earned 500 total points
ID: 22658796
That is not possible ,you will have have to build up an unbound table and implement your desired logic

though the above example just illustrates how to build up a table from scratch where i assume you
allready have a table / dataset for your report!
just clone your existing table and then build somewhat the logic in as shown above.

most of how you want to display the data i handled on the datagridview by setting some of the properties that the datagridview exposes and the rest (calculations filtering ,etc...)
is gathered from your table

try paste the sample below to see some of the posibilities

vbturbo

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        fillTable()  'for demo, equivalent to using dataadapter.fill
 

        Dim view As New DataView(dt)

        Dim drws() As DataRow

        'Sort the item code column desending

        view.Sort = "[Item Code] DESC"
 

        Dim tmpString As String

        Dim TotalForItem As Double

        Dim Items As New List(Of String)

        'do the sub totals

        Dim tbl As DataTable = dt.Clone()
 

        For Each dr As DataRow In dt.Rows
 

            If Items.Contains(dr("Item Code")) = False Then

                tmpString = (dr("Item Code"))
 
 

                Items.Add(tmpString)

                TotalForItem = dt.Compute("SUM(Qty)", "[Item Code] = '" & tmpString & "'")
 

                drws = dt.Select("[Item Code] = '" & tmpString & "'")

                For Each dr1 As DataRow In drws

                    tbl.ImportRow(dr1)

                Next

                dr = tbl.NewRow()

                dr("Item Code") = "Sub total item " & tmpString

                dr("Item Name") = ""

                dr("Qty") = TotalForItem

                tbl.Rows.Add((dr))
 

            End If
 

        Next
 
 

        

        dgv.DataSource = tbl
 

        dgv.AllowUserToAddRows = False
 

        dgv.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

        dgv.Columns(2).Width = 120

        dgv.Columns(2).DefaultCellStyle.Format = "#.00"
 

        Dim style0 As New System.Windows.Forms.DataGridViewCellStyle

        style0.BackColor = Color.LightSkyBlue
 

        Dim style1 As New System.Windows.Forms.DataGridViewCellStyle

        style1.ForeColor = Color.White
 

        Dim style2 As New System.Windows.Forms.DataGridViewCellStyle

        style2.BackColor = Color.Yellow

        style2.ForeColor = Color.Blue
 

        Dim row As DataGridViewRow
 

            For Each row In dgv.Rows()
 

            If row.Cells(1).Value = "" Then

                row.DefaultCellStyle = style2

            End If
 

        Next
 

        Dim dr2 As DataRow
 

        dr2 = tbl.NewRow()

        dr2("Item Code") = ""

        dr2("Item Name") = ""

        dr2("Qty") = 0

        tbl.Rows.Add((dr2))
 

        dr2 = tbl.NewRow()

        dr2("Item Code") = "Grand total item "

        dr2("Item Name") = ""

        dr2("Qty") = dt.Compute("SUM(Qty)", "[Item Code] <> ''")

        tbl.Rows.Add((dr2))
 

        row = dgv.Rows(7)

        row.DefaultCellStyle = style1
 

        row = dgv.Rows(8)

        row.DefaultCellStyle = style0
 

        'Button1.Text = "Row Totals"

        'dgv.Sort("Item Code")

    End Sub

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 22658905
How about using CUBE?

Could it be available in SQL EXPRESS?

Thank you.
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 22659224
Hi

Well im not familiar with CUBE but since you are asking how to presenting the data in a datagridview
this would be the way to go around.

off course the Sql cube can provide you with the raw data (when quering as shown with the datatable)
but when you want to show it in a datagridview according to your provided example you will have to do
some grid manipulation.

i suggest you open another thread and ask some of the other experts that has more experience in that regard
it could be that some of the tools that comes/ships with Sql cube can give you what you are asking "Out of the box"

vbturbo
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 22664764
Hi vbturbo,

Ok. I think the way you provide is one of the alternative that works.

Thank you very much for your help
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now