Reports/Stored Procedure - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Reports/Stored Procedure

Author

Topic

tclose
Starting Member

24 Posts

Posted - 2010-04-27 : 11:15:28

I created a query that is suppose to take 11 unrelated tables, put them in a temporary table and then tally the sum of balances based off of the first 2 digits in the Account. When I run this as a regular query in SQL Server 2008 it works fine. However, when I use it as a stored procedure in a Visual Studio Report - it only displays the first set of results and doesn't display the balance sum. It should find many accounts and their corresponding balance sum. I tried using it as a regular query with the same results. It's as if the loop stops after finding the first set. Any ideas on this, thanks.

Declare @i tinyint = 0
Declare @j tinyint = 1
Declare @sum money = 0

CREATE TABLE #TempTable (
Account int NOT NULL,
Description varchar(2000),
Product varchar(2000),
Balance money,
CONSTRAINT [pk_account] PRIMARY KEY(Account)
)
while (@i < 11)
BEGIN

IF @i = 0
INSERT INTO #TempTable(Account,Description,Product,Balance)
SELECT Account,Description,Product,Balance FROM Assets
ELSE IF @i = 1
INSERT INTO #TempTable(Account,Description,Product,Balance)
SELECT Account,Description,Product,Balance FROM Freight
ELSE IF @i = 2
INSERT INTO #TempTable(Account,Description,Product,Balance)
SELECT Account,Description,Product,Balance FROM Purchases
ELSE IF @i = 3
INSERT INTO #TempTable(Account,Description,Product,Balance)
SELECT Account,Description,Product,Balance FROM Sales
ELSE IF @i = 4
INSERT INTO #TempTable(Account,Description,Product,Balance)
SELECT Account,Description,Product,Balance FROM Expenses
ELSE IF @i = 5
INSERT INTO #TempTable(Account,Description,Product,Balance)
SELECT Account,Description,Product,Balance FROM Liabilities
ELSE IF @i = 6
INSERT INTO #TempTable(Account,Description,Product,Balance)
SELECT Account,Description,Product,Balance FROM Inventory
ELSE IF @i = 7
INSERT INTO #TempTable(Account,Description,Product,Balance)
SELECT Account,Description,Product,Balance FROM Profit
ELSE IF @i = 8
INSERT INTO #TempTable(Account,Description,Product,Balance)
SELECT Account,Description,Product,Balance FROM Payroll
ELSE IF @i = 9
INSERT INTO #TempTable(Account,Description,Product,Balance)
SELECT Account,Description,Product,Balance FROM Returns
ELSE IF @i = 10
INSERT INTO #TempTable(Account,Description,Product,Balance)
SELECT Account,Description,Product,Balance FROM Vendors
SET @i += 1

END

WHILE (@j < 62)
BEGIN
SELECT @sum = (SELECT SUM(Balance) FROM #TempTable
WHERE LEFT(Account,2) = @j AND Balance IS NOT NULL)
IF @sum != 0
SELECT Account,Description,Product,Balance FROM #TempTable WHERE LEFT(Account,2) = @j AND Balance IS NOT NULL
SET @sum = (SELECT SUM(Balance) FROM #TempTable WHERE LEFT(Account,2) = @j AND Balance IS NOT NULL)
IF @sum > 0
SELECT SUM(Balance) as [SubTotal] FROM #TempTable WHERE LEFT(Account,2) = @j AND Balance IS NOT NULL

SET @j += 1
SET @sum = 0
END
DROP TABLE #TempTable
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 11:20:48

its how reports work. they take only first result set. why not combine everything into one result along with a derived category column and then filter inside report based on category

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

tclose
Starting Member

24 Posts

Posted - 2010-04-27 : 11:38:32

quote:
Originally posted by visakh16

its how reports work. they take only first result set. why not combine everything into one result along with a derived category column and then filter inside report based on category

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

I obviously don't understand how report queries differ from other queries. How would I combine everything into one result.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-27 : 11:42:40

quote:
Originally posted by tclose
I obviously don't understand how report queries differ from other queries. How would I combine everything into one result.

Redesign your stored proc so that it only returns a single result set. You could insert the results of each select statement into a temp table, and select the temp table at the end of the proc.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 11:46:46

quote:
Originally posted by tclose

quote:
Originally posted by visakh16

its how reports work. they take only first result set. why not combine everything into one result along with a derived category column and then filter inside report based on category

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

I obviously don't understand how report queries differ from other queries. How would I combine everything into one result.

using UNION or UNION ALL . thats all i can say unless i see their resultsets. why not post sample resultset from each query and then explain what you want in report

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

tclose
Starting Member

24 Posts

Posted - 2010-04-27 : 12:21:59

quote:
Originally posted by DBA in the making

quote:
Originally posted by tclose
I obviously don't understand how report queries differ from other queries. How would I combine everything into one result.

Redesign your stored proc so that it only returns a single result set. You could insert the results of each select statement into a temp table, and select the temp table at the end of the proc.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

I think I get what you're saying. How would I get the subTotal column into the table though. Here is a sample of how the report should look:

Account|Description|Product|Balance
-----------------------------------_
10234 farm fresh Eggs 225.00
10783 Whole Milk 175.00

subTotal
----------
400.00

Account|Description|Product|Balance
-----------------------------------_
12034 Jimmy Dean Bacon 130.00
12913 Low Fat Bologna 155.00

subTotal
----------
285.00

Account|Description|Product|Balance
-----------------------------------_
13007 baby Carrots 314.00
13270 Red Apples 110.00

subTotal
----------
424.00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 12:26:45

can you tell on what basis you determine which rows are to be grouped?

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

tclose
Starting Member

24 Posts

Posted - 2010-04-27 : 12:32:25

quote:
Originally posted by visakh16

can you tell on what basis you determine which rows are to be grouped?

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

They will be grouped by the first two digits in the account number like so:

10234
10945
10038
subTotal
11034
11870
11466
subTotal
14003
14032
14990
subTotal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 12:35:52

then what you need is to bring only detail data in your procedure and in your report group on left(accnt,2) and then on detail portion drag and drop a table with reqd fields and on group footer add expression =SUM(Fields!Balance.Value)

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

tclose
Starting Member

24 Posts

Posted - 2010-04-27 : 13:19:41

quote:
Originally posted by visakh16

then what you need is to bring only detail data in your procedure and in your report group on left(accnt,2) and then on detail portion drag and drop a table with reqd fields and on group footer add expression =SUM(Fields!Balance.Value)

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

I understand the Group part, but not the drag and drop a table part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 13:48:16

that means you'll have a table within a table. first table you apply group by and in its group detail part you drag and drop a new table with reqd fields. in main tables group footer you show the total

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources