Reports , Doubts with sums and dates

Question

for a few days I have had a small problem where the client requested us a change in a report we have in our system written in VB.NET Basically, Filter my Datagridview, and through it I assemble the report,

With this code I filter.

 Dim cn As New OleDb.OleDbConnection
    Dim cmd As New OleDb.OleDbCommand
    Dim Da As New OleDb.OleDbDataAdapter
    Dim Dt As New DataTable
    Dim dtinicio As String
    Dim dtfim As String
    dtinicio = mskdtcomeco.Text
    dtfim = mskdtfinal.Text
    datainicio3 = mskdtcomeco.Text
    datafim3 = mskdtfinal.Text

    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Firebird.mdb"
    cn.Open()
    mskdtcomeco.Text = CDate(mskdtcomeco.Text).ToString("dd/MM/yyyy")
    mskdtfinal.Text = CDate(mskdtfinal.Text).ToString("dd/MM/yyyy")
    Try
        With cmd
            .CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@dtInicio", dtinicio)
            cmd.Parameters.AddWithValue("@dtFim", dtfim)
            .CommandText = "SELECT * FROM OS WHERE data between @dtInicio And @dtFim  And NOMEFUNCIONARIO = '" & ComboBox2.Text & "' ORDER BY data"
            .Connection = cn
        End With

        With Da
            .SelectCommand = cmd
            Dt = New DataTable
            .Fill(Dt)
            OSDataGridViewFuncionario.DataSource = Dt

        End With

    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
    cmd.Parameters.Clear()
    Dim linha As DataGridViewRow

    Dim valor As Double
    Dim valor2 As Double
    Dim valor3 As Double

    For Each linha In OSDataGridViewFuncionario.Rows
        valor = valor + linha.Cells(5).Value ' 
        valor2 = valor2 + linha.Cells(6).Value '
        valor3 = valor3 + linha.Cells(4).Value
    Next
    lbltotfunc.Text = FormatCurrency(valor)
    LBLSALAO.Text = FormatCurrency(valor2)
    lbltotal.Text = FormatCurrency(valoe3)

After the results listed in the DG, comes the printing.

    Private Sub pdFuncionario_PrintPage(sender As Object, e As Printing.PrintPageEventArgs) Handles pdFuncionario.PrintPage
    Dim g As Graphics = e.Graphics
    Dim Cliente As String = String.Empty
    Dim funcionario As String = String.Empty
    Dim servico As String = String.Empty
    Dim totservico As String = String.Empty
    Dim remum As String = String.Empty
    Dim data As String = String.Empty
    Dim PosicaoLinha As Integer = 40
    Dim LinhasPorPagina As Byte = 45
    'Definições da pagina
    g.PageUnit = GraphicsUnit.Millimeter
    'Desenhar folha
    Dim Titulo As New Font("Times New Roman", 8, FontStyle.Regular Or FontStyle.Bold, GraphicsUnit.Millimeter)

    g.DrawString("Relatorio de Funcionario ", Titulo, Brushes.Black, 65, 7)


    'Ler datagridview
    For L = 0 To LinhasPorPagina - 1
        If LinhaAtual = OSDataGridViewFuncionario.Rows.Count Then Continue For
        For i As Integer = 0 To OSDataGridViewFuncionario.ColumnCount - 9

            If Not OSDataGridViewFuncionario.Item(i, LinhaAtual).Value = Nothing Then
                Cliente = OSDataGridViewFuncionario.Item(i, LinhaAtual).Value

            End If

        Next
        For o As Integer = 0 To OSDataGridViewFuncionario.ColumnCount - 8
            If Not OSDataGridViewFuncionario.Item(o, LinhaAtual).Value = Nothing Then
                funcionario = OSDataGridViewFuncionario.Item(o, LinhaAtual).Value

            End If
        Next
        For u As Integer = 0 To OSDataGridViewFuncionario.ColumnCount - 6
            If Not OSDataGridViewFuncionario.Item(u, LinhaAtual).Value = Nothing Then
                servico = OSDataGridViewFuncionario.Item(u, LinhaAtual).Value

            End If
        Next
        For a As Integer = 0 To OSDataGridViewFuncionario.ColumnCount - 4
            If Not OSDataGridViewFuncionario.Item(a, LinhaAtual).Value = Nothing Then
                totservico = OSDataGridViewFuncionario.Item(a, LinhaAtual).Value

            End If
        Next

        For c As Integer = 0 To OSDataGridViewFuncionario.ColumnCount - 2

            If Not OSDataGridViewFuncionario.Item(c, LinhaAtual).Value = Nothing Then

                data = OSDataGridViewFuncionario.Item(c, LinhaAtual).Value


            End If

        Next



        'Desenhar conteudo na página
        e.Graphics.DrawString("Cliente", New Font("Verdana", 6), Brushes.Black, 10, 30)
        e.Graphics.DrawString(Cliente, New Font("Verdana", 6), Brushes.Black, 9, PosicaoLinha)
        e.Graphics.DrawString("Funcionario", New Font("Verdana", 6), Brushes.Black, 55, 30)
        e.Graphics.DrawString(funcionario, New Font("Verdana", 6), Brushes.Black, 55, PosicaoLinha)
        e.Graphics.DrawString("Serviço", New Font("Verdana", 6), Brushes.Black, 90, 30)
        e.Graphics.DrawString(servico, New Font("Verdana", 6), Brushes.Black, 90, PosicaoLinha)
        e.Graphics.DrawString("Valor Recebido", New Font("Verdana", 6), Brushes.Black, 130, 30)
        e.Graphics.DrawString("R$ " & totservico, New Font("Verdana", 6), Brushes.Black, 134, PosicaoLinha)
        e.Graphics.DrawString("Data", New Font("Verdana", 6), Brushes.Black, 190, 30)
        e.Graphics.DrawString(data, New Font("Verdana", 6), Brushes.Black, 180, PosicaoLinha)


        PosicaoLinha += 5
        LinhaAtual += 1
        Cliente = String.Empty
        funcionario = String.Empty
        servico = String.Empty
        totservico = String.Empty
        remum = String.Empty
        data = String.Empty

    Next
    'Se linha atual for igual á linhas devidas por página então muda de página
    If LinhaAtual < OSDataGridViewFuncionario.Rows.Count Then
        e.HasMorePages = True
    Else
        e.HasMorePages = False
        PosicaoLinha = PosicaoLinha + 5
        e.Graphics.DrawString("______________________________________________________________________________________________________________________________", New Font("Verdana", 8), Brushes.Black, 0, PosicaoLinha)
        PosicaoLinha = PosicaoLinha + 5
        e.Graphics.DrawString("Data Inicial do Relatorio: " & datainicio3 & "                                   Data Final do Relatorio  : " & datafim3, New Font("Verdana", 8), Brushes.Black, 20, PosicaoLinha)
        PosicaoLinha = PosicaoLinha + 2
        e.Graphics.DrawString("__________________________________________________________________________________________________________________________________", New Font("Verdana", 8), Brushes.Black, 0, PosicaoLinha)
        PosicaoLinha = PosicaoLinha + 5
        e.Graphics.DrawString("Funcionario: " & ComboBox2.Text, New Font("Verdana", 12), Brushes.Black, 20, PosicaoLinha)
        e.Graphics.DrawString("Valor a Receber: ", New Font("Verdana", 12), Brushes.Black, 135, PosicaoLinha)
        e.Graphics.DrawString(lbltotfunc.Text, New Font("Verdana", 12), Brushes.Black, 175, PosicaoLinha)
    End If
End Sub

Thus, when the customer makes a search with a date range Ex : 10/05/2015 - 20/05/2015, the report only shows the total values,

I wonder if there would be some way ,to create a total for each day in this report, that when pass the line to next date add up the daily values , so that it n can be filtering day by day... from now on I thank you for the attention and collaboration of all. thank you.

Answer 1

First of all: never, never, never build SQL queries by concatenating strings unless you know exactly what you’re doing. Do this:

cmd.Parameters.AddWithValue("@dtInicio", dtinicio)
cmd.Parameters.AddWithValue("@dtFim", dtfim)
cmd.Parameters.AddWithValue("@nomeFuncionario", ComboBox2.Text)
.CommandText = "SELECT * FROM OS WHERE data between @dtInicio And @dtFim And NOMEFUNCIONARIO = @nomeFuncionario ORDER BY data"

To generate the aggregated value per day, you can change the SQL query to something like (note GROUP BY)

SELECT data, SUM(coluna_valores) AS total
FROM OS
WHERE data between @dtInicio And @dtFim AND
      NOMEFUNCIONARIO = @nomeFuncionario
GROUP BY data
ORDER BY data

That’ll give you an answer like

    data    | total
------------+-------
 2015-06-05 | 3500
 2015-06-08 | 1700
 2015-06-09 | 700

that you would have to somehow integrate into your current report or use to generate a new report.