Solved

Query report to Datagridview before print it to printer.

Posted on 2008-10-01
9
351 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

707 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