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