Solved

Query report to Datagridview before print it to printer.

Posted on 2008-10-01
9
346 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
It works, but could we do it using query directly?

Thank you.
0
 
LVL 18

Accepted Solution

by:
vbturbo earned 500 total points
Comment Utility
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
Comment Utility
How about using CUBE?

Could it be available in SQL EXPRESS?

Thank you.
0
 
LVL 18

Expert Comment

by:vbturbo
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 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

9 Experts available now in Live!

Get 1:1 Help Now