Balance Sheet Reporting (Part 2)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Balance Sheet Reporting (Part 2)

Author

Topic

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-08-12 : 09:30:42

This is a continuation of my previous Financial Reporting post [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=177014[/url]. I have this query below that displays the hierarchical view of a balance sheet base on BalanceSheet_Template Table.


use MFR_Merchandise
go

; with BalanceSheet (AccountID, AccountName, ParentID,AccountOrder)
As (
	Select 
		AccountID,
		Display=convert(varchar(1000),AccountName),
		ParentID,
		AccountOrder
	From BalanceSheet_Template
), AccountPart1

AS
(
Select 
		Level=1,
		AccountID,
		Display=convert(varchar(1000),AccountName),
		ParentID,
		AccountOrder
From BalanceSheet A
Where A.ParentID is NULL

Union All

Select 
		Level=B.Level + 2,
		A.AccountID,
		Display=convert(varchar(1000),replicate('  ', B.Level) + AccountName),
		A.ParentID,
		A.AccountOrder
	From BalanceSheet A
	Inner Join AccountPart1 B
	On B.AccountID = A.ParentID
)
Select * 
From AccountPart1
Order By AccountOrder

This is the result:


Level	AccountID	Display	                   ParentID	AccountOrder
1	1	        ASSETS	                     NULL	     1
3	2	          Current Assets	      1       	     2
5	3	             Checking/Savings	      2	             3
7	4	               Bank	              3	             4
5	5	             Accounts Receivable      2	             5
5	6	            Other Current Assets      2	             6
3	7	          Fixed Asset	              1	             7
3	8	          Other Asset	              1	             8
1	9	        LIABILITIES & EQUITY	     NULL	     9
3	10	          Liabilities	              9	             10
5	11	             Current Liabilities     10	      11
7	12	               Accounts Payable	      11	     12
7	13	               Credit Card	      11	     13
7	14	               Other Current Liability11	     14
5	15	             Long Term Liability      10	     15
3	16	          Equity	              9	             16
5	17	            Net Income	              16	     17

I need to join this to tbl_accnt_list table base on ParentID to get the sub accounts. So the result must be something below as marked in color red:


Level	AccountID	Display	                   ParentID	AccountOrder
1	1	        ASSETS	                     NULL	     1
3	2	          Current Assets	      1       	     2
5	3	             Checking/Savings	      2	             3
7	4	               Bank	              3	             4
                                 Union Bank          4              
                                 World Bank          4              
5	5	             Accounts Receivable      2	             5
                               Cellcard              5
                               Collectible Taxes     5
5	6	            Other Current Assets      2	             6
3	7	          Fixed Asset	              1	             7
3	8	          Other Asset	              1	             8
1	9	        LIABILITIES & EQUITY	     NULL	     9
3	10	          Liabilities	              9	             10
5	11	             Current Liabilities      10	     11
7	12	               Accounts Payable	      11	     12
                                 Dressed Chicken     12
                                 Cellcard            12
7	13	               Credit Card	      11	     13
7	14	               Other Current Liability11	     14
5	15	             Long Term Liability      10	     15
3	16	          Equity	              9	             16
5	17	            Net Income	              16	     17

Any hep is greatly appreciated!

Thank you & God bless!
-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-08-12 : 09:42:13

And whats the question ???

--------------------------
http://connectsql.blogspot.com/

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-08-12 : 09:49:13

Thank you lionofdezert!
The question is, how can I join this to tbl_accnt_list table base on ParentID to get the sub accounts. So the result must be something below as marked in color red:


Level	AccountID	Display	                   ParentID	AccountOrder
1	1	        ASSETS	                     NULL	     1
3	2	          Current Assets	      1       	     2
5	3	             Checking/Savings	      2	             3
7	4	               Bank	              3	             4
                                 Union Bank          4              
                                 World Bank          4              
5	5	             Accounts Receivable      2	             5
                               Cellcard              5
                               Collectible Taxes     5
5	6	            Other Current Assets      2	             6
3	7	          Fixed Asset	              1	             7
3	8	          Other Asset	              1	             8
1	9	        LIABILITIES & EQUITY	     NULL	     9
3	10	          Liabilities	              9	             10
5	11	             Current Liabilities      10	     11
7	12	               Accounts Payable	      11	     12
                                 Dressed Chicken     12
                                 Cellcard            12
7	13	               Credit Card	      11	     13
7	14	               Other Current Liability11	     14
5	15	             Long Term Liability      10	     15
3	16	          Equity	              9	             16
5	17	            Net Income	              16	     17

Any hep is greatly appreciated!

Thank you & God bless!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-12 : 11:38:10

where's the DDL for tbl_accnt_list table? without that we cant understand what columns it contain and how its related to main table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-08-12 : 11:54:07

Thank you visakh16!

Here is what the tbl_accnt_list columns which should be link on BalanceSheet_Template table.


AccountID        AccountName      ParentID
   21             Union Bank           4           
   22             World Bank           4             
   23             Cellcard             5
   24             Collectible Taxes    5
   25             Dressed Chicken      12
   26             Cellcard             12

           ........................

Where ParentID is the same as in the BalanceSheet_Template table.

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-12 : 12:03:43

[code]
use MFR_Merchandise
go

; with BalanceSheet (AccountID, AccountName, ParentID,AccountOrder)
As (
Select
AccountID,
Display=convert(varchar(1000),AccountName),
ParentID,
AccountOrder
From BalanceSheet_Template
UNION ALL
Select
(-1) * AccountID,
Display=convert(varchar(1000),AccountName),
ParentID,
NULL
From tbl_accnt_list
), AccountPart1

AS
(
Select
Level=1,
AccountID,
Display=convert(varchar(1000),AccountName),
ParentID,
AccountOrder
From BalanceSheet A
Where A.ParentID is NULL

Union All

Select
Level=B.Level + 2,
ABS(A.AccountID),
Display=convert(varchar(1000),replicate(' ', B.Level) + AccountName),
A.ParentID,
A.AccountOrder
From BalanceSheet A
Inner Join AccountPart1 B
On B.AccountID = A.ParentID
)
Select *
From AccountPart1
Order By COALESCE(AccountOrder,ParentID)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-08-12 : 12:45:05

Thank you visakh16! Its almost working. But some sub accounts are positioned not under its Parent. Ive marked it in red.


Level	AccountID		Display			  				  ParentID		AccountOrder
1			1	ASSETS							    NULL			1
3			2	  Current Assets					     1				2
5			3	      Checking/Savings					     2				3
7			4	          Bank						     3				4
9			275	              Cash on Drawer_2				     4				NULL
9			276	              Cash in Bankr_2				     4				NULL
5			5	      Accounts Receivable				     2				5
7			283	          Owners Equity					     5				NULL
7			284	          Sales						     5				NULL
7			285	          Shipping & Delivery Income		             5				NULL
7			286	          Freight Cost					     5				NULL
5			6	      Other Current Assets				     2				6
3			7	  Fixed Asset						     1				7
3			8	  Other Asset						     1				8
1			9	LIABILITIES & EQUITY					    NULL			9
3			10	  Liabilities						     9				10
5			11	      Current Liabilities				     10				11
7			12	          Accounts Payable				     11				12
9			289	              Bank Charges				     12				NULL
9			290	              Computer Expenses				     12				NULL
9			291	              Depreciation Expense			     12				NULL
9			292	              Insurance Enxpense			     12				NULL
9			293	              Interest Expense				     12				NULL
9			294	              Meals & Intertainment			     12				NULL
9			300	              Repair & Maintainance			     13				NULL
9			301	              Taxes - Property				     13				NULL
9			302	              Communication 			             13				NULL
9			303	              Transportation 		                     13				NULL
9			304	              Tax Payable				     13				NULL
7			13	          Credit Card					     11				13
7			14	          Other Current Liability			     11				14
5			15	      Long Term Liability				     10				15
3			16	  Equity						     9				16
5			17	      Net Income					     16				17

The accounts marked in red, must be under accounts marked in blue since its ParentID is the AccountID on the account in color blue.

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-08-12 : 13:52:10

Thank you visakh16. I think the problem why some sub accounts was mis-positioned is because there are some sub accounts on my tbl_accnt_list that do not have ParentID which is not suppose to be. When I put on the ParentID, it works perfectly.

One more thing to make this a perfect Balance Sheet, can we add a sub-total on each account that has sub accounts? Example results below, Total marked in red:

Level	AccountID		Display			  			          ParentID	          AccountOrder
1			1	ASSETS							    NULL			1
3			2	  Current Assets					     1				2
5			3	      Checking/Savings					     2				3
7			4	          Bank						     3				4
9			275	              Cash on Drawer_2				     4				NULL
9			276	              Cash in Bankr_2				     4				NULL
                                          Total Bank
                                      Total Checking/Savings
5			5	      Accounts Receivable				     2				5
7			283	          Owners Equity					     5				NULL
7			284	          Sales						     5				NULL
7			285	          Shipping & Delivery Income		             5				NULL
7			286	          Freight Cost					     5				NULL
                                      Total Accounts Receivable
5			6	      Other Current Assets				     2				6
                                 Total Current Assets
3			7	  Fixed Asset						     1				7
3			8	  Other Asset						     1				8
                               Total ASSETS

1			9	LIABILITIES & EQUITY					    NULL			9
3			10	  Liabilities						     9				10
5			11	      Current Liabilities				     10				11
7			12	          Accounts Payable				     11				12
9			289	              Bank Charges				     12				NULL
9			290	              Computer Expenses				     12				NULL
9			291	              Depreciation Expense			     12				NULL
9			292	              Insurance Enxpense			     12				NULL
9			293	              Interest Expense				     12				NULL
9			294	              Meals & Intertainment			     12				NULL
                                          Total Accounts Payable
7			13	          Credit Card					     11				13
9			300	              Repair & Maintainance			     13				NULL
9			301	              Taxes - Property				     13				NULL
9			302	              Communication 			             13				NULL
9			303	              Transportation 		                     13				NULL
9			304	              Tax Payable				     13				NULL
                                          Total Credit Card
7			14	          Other Current Liability			     11				14
                                      Total Current Liabilities
5			15	      Long Term Liability				     10				15
                                 Total Liabilities
3			16	  Equity						     9				16
5			17	      Net Income					     16				17
                                 Total Equity
                               Total LIABILITIES & EQUITY

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-12 : 15:59:12

this is something which can be very easily generated at your front end app. which front end application are you using?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-08-12 : 19:35:15

Thank you visakh16!

I'm using vb6 32 bit. At First I develop this Balance sheet in my front end app but it gets too complicated to handle the positioning of accounts and it gets time the app to generate the balance sheet. That is why Ive decided to generate the report in SQL. Will it be possible to use cube or rollup or CURSOR? Ive heard this powerful stuff of SQL Server but Ive never use this before. If this can be done ALSO in vb6 smoothly it would be greatly appreciated if there is some solution.

Some suggest to use Crystal Report but its another big investment for the company while SQL Server or vb6 can do the task!

Thank you & God bless!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-08-13 : 21:32:22

Any body has an Idea on how to solve this?

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-08-15 : 05:57:54

Can I use rollup or cube or grouping_id()? Where in my query to put it?

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources