Arithematic with 2 textboxes which has 2 subreport

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

Development Tools

Reporting Services Development

Arithematic with 2 textboxes which has 2 subreport

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-04-01 : 15:16:33

Has anyone done any arithematic with textboxes that involves subreports?

I had 2 textboxes that each has a subreport. Within those subreport it contains 1 field that is run from a separate stored procedure. I was curious if in the main report to be able to add those 2 textboxes together.

Example:

ID-----Sales-----Cost-----Gross
123----$12.32----$10.00---$2.32
322----$25.00----$14.00---$11.00

Any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-01 : 15:18:51

Are you even able to refer to text boxes in an expression?

Tara

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-04-01 : 16:04:21

Nope. I had never been able to do that. Either I have to do the arithematic with the fields themselves first and then just put them in the textbox. Like going to the Fields and create a calculated field.

In this case, it's involving 3 stored procedure. But I just need to be able to calculate 2 fields from different SP. It's kind of lame that you can't go textbox1 - textbox2. Or at least not that I'm aware of. You got any ideas Tara?

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-04-01 : 16:19:17

Tara, do you know if it's possible to create a stored procedure that will run 3 sp in query analyzer?

The thing is that the sp only reference each other by object fields and not primary keys. For example sp2 will reference the OrderID of sp1 and sp3 will refrence PartNum of SP2. I was thinking about creating temp table and than insert everything in there, but problem is I don't know if it's possible to reference field objects.

Any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-01 : 16:22:48

Yes you can call stored procedures from within another stored procedure. Here's an example

CREATE PROC dbo.SomeParentProc
AS

EXEC dbo.SomeChildProc1...
EXEC dbo.SomeChildProc2...
EXEC dbo.SomeChildProc3...

Then if you need to work with the results of the child sprocs from within the parent, you put them into a temp table:

CREATE PROC dbo.SomeParentProc
AS

CREATE TABLE #Temp1...
CREATE TABLE #Temp2...
CREATE TABLE #Temp3...

INSERT INTO #Temp1(...)
EXEC dbo.SomeChildProc1...

INSERT INTO #Temp2(...)
EXEC dbo.SomeChildProc2...

INSERT INTO #Temp3(...)
EXEC dbo.SomeChildProc3...

Do some work with the temp tables here...

DROP TABLE #Temp1, #Temp2, #Temp3

Tara

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-04-01 : 16:32:02

Tara

With that can you reference fields from one stored procedure to another? To clarify, I want SP2 to reference a field in SP1. Kind of like the where clause.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-01 : 16:40:55

So if your child sproc outputs the fields in the result set (it has to be in the result set), these fields are now in the temporary tables which you can now work with in the parent sproc.

Tara

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-04-01 : 16:57:14

Tara,

Thanks for the example you provided. Do you have link to a more detail example or able to provided a more thorough sample? I haven't been working with SQL query for long so I probably need a little more assistance.

At the point, I created 3 stored procedure, which I'm hoping to put into as one.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-01 : 17:11:23

Here's a more detailed example. I have created 4 stored procedures. 3 child, 1 parent. In the below code, I output what the 3 children are outputting so you can see it. 1,1 1,2 and 1,3. So in the parent proc, I call them and put the result sets into temp tables. I then take the second column of each stored procedure and add them together to get 1+2+3=6. So the parent proc returns 6. Run the code as is. See that I am working with the result sets of the stored procedures via the temp tables in the parent procs select.



CREATE PROC SomeChildProc1
AS

SELECT 1, 1

RETURN
GO

CREATE PROC SomeChildProc2
AS

SELECT 1, 2

RETURN
GO

CREATE PROC SomeChildProc3
AS

SELECT 1, 3

RETURN
GO

-- execute child tables to see what they return
EXEC SomeChildProc1
EXEC SomeChildProc2
EXEC SomeChildProc3
GO

CREATE PROC SomeParentProc
AS

CREATE TABLE #Temp1(Column1 int, Column2 int)
CREATE TABLE #Temp2(Column1 int, Column2 int)
CREATE TABLE #Temp3(Column1 int, Column2 int)

INSERT INTO #Temp1(Column1, Column2)
EXEC dbo.SomeChildProc1

INSERT INTO #Temp2(Column1, Column2)
EXEC dbo.SomeChildProc2

INSERT INTO #Temp3(Column1, Column2)
EXEC dbo.SomeChildProc3

SELECT t1.Column2 + t2.Column2 + t3.Column2
FROM #Temp1 t1
INNER JOIN #Temp2 t2
ON t1.Column1 = t2.Column1
INNER JOIN #Temp3 t3
ON t2.Column1 = t3.Column1

DROP TABLE #Temp1, #Temp2, #Temp3

RETURN 
GO

-- execute parent proc
EXEC SomeParentProc

-- clean up my mess
DROP PROC SomeChildProc1, SomeChildProc2, SomeChildProc3, SomeParentProc

I don't think I can be more detailed than that. Ask questions if you don't understand it.

Tara

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2005-04-04 : 14:37:55

Thanks for the example. I will work on it and if I do have any questions will post it up.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources