Click here to Skip to main content
15,892,809 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
VB
Imports System.Data.SqlClient
Imports Microsoft.Reporting.WinForms


Public Class TrialBalanceForm

    Private connString As String = _
    "Data Source=.\sqlexpress;Initial Catalog=FinancialAccounting;Integrated Security=True"

    Private Sub TrialBalanceForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Try
            With Me.ReportViewer1.LocalReport

                .ReportPath = Application.StartupPath & "\..\..\rptTrialBal.rdlc"
                .DataSources.Clear()

                Dim parameters(1) As ReportParameter
                parameters(0) = New ReportParameter("ApplicationUser", "jpaulino")
                parameters(1) = New ReportParameter("ApplicationLevel", "Administrator")
                .SetParameters(parameters)

            End With

            Dim SQL As String = "SELECT COA .Account_Code  AS [Account Code], COA.Account_Name  AS [Account Name],CASE WHEN " & _
"SUM (CASE WHEN COA .Account_Type in ('CA','CA/FA','FA')and COA .Acc_Type_Cate in ('BS') and COA .Acc_Type_Sub_Cate in ('Ass') THEN " & _
"debit-credit ELSE 0 END) > 0 THEN (SUM (CASE WHEN COA .Account_Type in ('CA','CA/FA','FA') and COA .Acc_Type_Cate in ('BS') and COA .Acc_Type_Sub_Cate in ('Ass') " & _
"THEN   debit-credit end )) ELSE 0 END AS [Debit], CASE WHEN SUM (CASE WHEN COA .Account_Type in ('CA','CA/FA','FA') and COA .Acc_Type_Cate in ('BS') and COA .Acc_Type_Sub_Cate in ('Ass') THEN  " & _
"debit-credit ELSE 0 END) < 0 THEN (SUM (CASE WHEN COA .Account_Type in ('CA','CA/FA','FA') and COA .Acc_Type_Cate in ('BS') and COA .Acc_Type_Sub_Cate in ('Ass') THEN   debit-credit end )) ELSE 0 END AS [Credit] " & _
"FROM COA JOIN Ledgers ON COA .Account_Code  = Ledgers .Account_Code WHERE COA .Account_Type in ('CA','CA/FA','FA') GROUP BY COA .Account_Code ,COA .Account_Name  " & _
"UNION ALL SELECT COA .Account_Code  AS [Account Code], COA.Account_Name  AS [Account Name], CASE WHEN " & _
"SUM (CASE WHEN COA .Account_Type in ('CL','LTL')and COA .Acc_Type_Cate in ('BS') and COA .Acc_Type_Sub_Cate in ('Lbt') THEN   debit-credit ELSE 0 END) > 0 THEN " & _
"(SUM (CASE WHEN COA .Account_Type in ('CL','LTL')and COA .Acc_Type_Cate in ('BS') and COA .Acc_Type_Sub_Cate in ('Lbt') THEN   debit-credit end )) ELSE 0 END AS [Debit], CASE WHEN " & _
"SUM (CASE WHEN COA .Account_Type in ('CL','LTL')and COA .Acc_Type_Cate in ('BS') and COA .Acc_Type_Sub_Cate in ('Lbt') THEN   debit-credit ELSE 0 END) < 0 THEN " & _
"(SUM (CASE WHEN COA .Account_Type in ('CL','LTL')and COA .Acc_Type_Cate in ('BS') and COA .Acc_Type_Sub_Cate in ('Lbt')  THEN   debit-credit end )) ELSE 0 END AS [Credit] " & _
"FROM COA JOIN Ledgers ON COA .Account_Code  = Ledgers .Account_Code WHERE COA .Account_Type in ('CL','LTL') GROUP BY COA .Account_Code ,COA .Account_Name  " & _
"UNION ALL SELECT COA .Account_Code  AS [Account Code], COA.Account_Name  AS [Account Name], " & _
"CASE WHEN SUM (CASE WHEN COA .Account_Type in ('Equities')and COA .Acc_Type_Cate in ('BS') and COA .Acc_Type_Sub_Cate in ('Equ') THEN   " & _
"debit-credit ELSE 0 END) > 0 THEN (SUM (CASE WHEN COA .Account_Type in ('Equities')and COA .Acc_Type_Cate in ('BS') and COA .Acc_Type_Sub_Cate in ('Equ') THEN   debit-credit end )) ELSE 0 END AS [Debit], " & _
"CASE WHEN SUM (CASE WHEN COA .Account_Type in ('Equities')and COA .Acc_Type_Cate in ('BS') and COA .Acc_Type_Sub_Cate in ('Equ') THEN   debit-credit ELSE 0 END) < 0 THEN " & _
"(SUM (CASE WHEN COA .Account_Type in ('Equities')and COA .Acc_Type_Cate in ('BS') and COA .Acc_Type_Sub_Cate in ('Equ') THEN   debit-credit end )) ELSE 0 END AS [Credit] " & _
"FROM COA JOIN Ledgers ON COA .Account_Code  = Ledgers .Account_Code WHERE COA .Account_Type in ('Equities') GROUP BY COA .Account_Code ,COA .Account_Name  " & _
"UNION ALL SELECT COA .Account_Code  AS [Account Code], COA.Account_Name  AS [Account Name], " & _
"CASE WHEN SUM (CASE WHEN COA .Account_Type in ('Revenues')and COA .Acc_Type_Cate in ('PL') and COA .Acc_Type_Sub_Cate in ('Rev') THEN   debit-credit ELSE 0 END) > 0 THEN " & _
"(SUM (CASE WHEN COA .Account_Type in ('Revenues')and COA .Acc_Type_Cate in ('PL') and COA .Acc_Type_Sub_Cate in ('Rev') THEN   debit-credit end )) ELSE 0 END AS [Debit], CASE WHEN " & _
"SUM (CASE WHEN COA .Account_Type in ('Revenues')and COA .Acc_Type_Cate in ('PL') and COA .Acc_Type_Sub_Cate in ('Rev') THEN   debit-credit ELSE 0 END) < 0 THEN " & _
"(SUM (CASE WHEN COA .Account_Type in ('Revenues')and COA .Acc_Type_Cate in ('PL') and COA .Acc_Type_Sub_Cate in ('Rev') THEN   debit-credit end )) ELSE 0 END AS [Credit] " & _
"FROM COA JOIN Ledgers ON COA .Account_Code  = Ledgers .Account_Code WHERE COA .Account_Type in ('Revenues') GROUP BY COA .Account_Code ,COA .Account_Name  " & _
"UNION ALL SELECT COA .Account_Code  AS [Account Code], COA.Account_Name  AS [Account Name],  " & _
"CASE WHEN SUM (CASE WHEN COA .Account_Type in ('Expenses')and COA .Acc_Type_Cate in ('PL') and COA .Acc_Type_Sub_Cate in ('Exp') THEN   debit-credit ELSE 0 END) > 0 THEN " & _
"(SUM (CASE WHEN COA .Account_Type in ('Expenses')and COA .Acc_Type_Cate in ('PL') and COA .Acc_Type_Sub_Cate in ('Exp') THEN   debit-credit end )) ELSE 0 END AS [Debit], CASE WHEN  " & _
"SUM (CASE WHEN COA .Account_Type in ('Expenses')and COA .Acc_Type_Cate in ('PL') and COA .Acc_Type_Sub_Cate in ('Exp') THEN   debit-credit ELSE 0 END) < 0 THEN  " & _
"(SUM (CASE WHEN COA .Account_Type in ('Expenses')and COA .Acc_Type_Cate in ('PL') and COA .Acc_Type_Sub_Cate in ('Exp') THEN   debit-credit end )) ELSE 0 END AS [Credit] " & _
"FROM COA JOIN Ledgers ON COA .Account_Code  = Ledgers .Account_Code WHERE COA .Account_Type in ('Expenses') GROUP BY COA .Account_Code ,COA .Account_Name"

            Using da As New SqlDataAdapter(SQL, connString)
                Using ds As New DataSet
                    da.Fill(ds, "TrialBalance")

                    Dim rptDatasource As New ReportDataSource("dsTrialrep_TrialBalance", ds.Tables("TrialBalance"))
                    Me.ReportViewer1.LocalReport.DataSources.Add(rptDatasource)
                End Using
            End Using

        Catch oex As Exception
            MsgBox(oex.Message)
        End Try

    End Sub

    Sub SubreportProccessingEvent(ByVal sender As Object, ByVal e As SubreportProcessingEventArgs)

        Try

            Dim SQL As String = " Select Company_Name,Company_Legal_Name,City,Phone_No,Email_Address From Company"
            Using da As New SqlDataAdapter(SQL, connString)
                Using ds As New DataSet
                    da.Fill(ds, "Company")
                    Dim rptDataSource As New ReportDataSource("dsTrialrep_Company", ds.Tables("Company"))
                    e.DataSources.Add(rptDataSource)
                End Using
            End Using

            ReportViewer1.RefreshReport()

            AddHandler ReportViewer1.LocalReport.SubreportProcessing, AddressOf SubreportProccessingEvent

        Catch ex As Exception
            MessageBox.Show(ex.Message, My.Application.Info.Title, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub
End Class
Posted
Updated 2-Aug-13 8:41am
v2
Comments
ridoy 2-Aug-13 15:25pm    
not sure whether anyone is going to help you.you need to declare specific problem inside the code.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900