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
 Subreport footer shown in main report?

Author  Topic 

chriskhan2000
Aged Yak Warrior

544 Posts
Posted - 2005-01-24 : 12:38:44
Got a report that has a subreport. I need to sum a field in the subreport and have it displayed in the main report.

Not sure how I would be able to pull a field from a subreport into the main report. I wonder if this can even be done. I'm thinking of a parameter, but how would I reference the field from the subreport to be a parameter to be pull from the main report?

I was able to pull a field from the main report as a parameter to be pass to a field in the subreport, but can it be reversed?

Please help me with any advise or workaround. Thanks.

jhermiz

3564 Posts
Posted - 2005-01-24 : 21:39:22
quote:
Originally posted by chriskhan2000

Got a report that has a subreport. I need to sum a field in the subreport and have it displayed in the main report.

Not sure how I would be able to pull a field from a subreport into the main report. I wonder if this can even be done. I'm thinking of a parameter, but how would I reference the field from the subreport to be a parameter to be pull from the main report?

I was able to pull a field from the main report as a parameter to be pass to a field in the subreport, but can it be reversed?

Please help me with any advise or workaround. Thanks.


The sub report doesn't get its data till the main report has gotten its data from its own data set. It isn't possible to reference that sub report data until the entire main report has gotten its data.

There may be a way to specify parameters, however it might be long winded.

Chris I noticed you create a lot of subreports, can I ask why you do that so often? Really any report you create with a subreport can be easily created with just a main report and using a good amount of group bys with a tight little query. The only reason I use subreports is for additional graphs that may be shown alongside with some data from a main report.



Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]
Go to Top of Page

chriskhan2000
Aged Yak Warrior

544 Posts
Posted - 2005-01-25 : 10:34:09
Jon thanks for responding. The reason why I use a lot of subreports is that I need to somehow be able to reference objects from another field. I can't seem to do this with just joining or grouping tables, or at least not to my understanding.

Also for the example that I posted up in the T-SQL forum about when joining tables, I tend to get more data output then if I was not to join, that's why need to refenerece the fields to be use in the where clause.

Instead of using subreports, would it be possible to use multiple tables in the same report and cross reference fields as parameters from one dataset to the next to be use in those tables? Like dataset 1 you can reference it's field as a parameter to be pass to dataset 2 and vice versa. I wonder if this is possible at all?
Go to Top of Page

jhermiz

3564 Posts
Posted - 2005-01-25 : 11:57:13
quote:
Originally posted by chriskhan2000

Jon thanks for responding. The reason why I use a lot of subreports is that I need to somehow be able to reference objects from another field. I can't seem to do this with just joining or grouping tables, or at least not to my understanding.

Also for the example that I posted up in the T-SQL forum about when joining tables, I tend to get more data output then if I was not to join, that's why need to refenerece the fields to be use in the where clause.

Instead of using subreports, would it be possible to use multiple tables in the same report and cross reference fields as parameters from one dataset to the next to be use in those tables? Like dataset 1 you can reference it's field as a parameter to be pass to dataset 2 and vice versa. I wonder if this is possible at all?




Yes this is possible, I wish there was a central "demo" db to show you some examples. As soon as I free up some time I will try to send u an example for northwind or pubs.

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]
Go to Top of Page

chriskhan2000
Aged Yak Warrior

544 Posts
Posted - 2005-01-25 : 12:23:01
Thanks Jon. I'm currently trying to get the example I explained to you to work, but at the moment I'm struggling. Keeps giving me expression errors. Do send me an example if you have the time to make one.

Thanks again.
Go to Top of Page

chriskhan2000
Aged Yak Warrior

544 Posts
Posted - 2005-01-28 : 09:30:17
Jon,

Have you got the chance to create that little demo? I can't seem to get it to work at all. I have 2 datasets in the main report with 2 tables, but I can't pass one variable from one table to the next table and vice versa. Keeps giving me error saying that I can't use a field as a parameter. Let me know when you will be able to help me out. Thanks.
Go to Top of Page

jhermiz

3564 Posts
Posted - 2005-01-28 : 09:44:19
Chris when you say pass one from the other...let me give you an example:

Customer->Orders

DataSet.CustomerID=DataSet2.CustomerID

Meaning passing the customer ID from one data set to another ?

After rereading your problem it seems simple to just use a stored procedure and pass the parameter to the next data set.
You would have a total of 3 data sets..one for your main selection, another that filters your main selection to give you the
results of another data set, and finally the main data set taking a combination of both to create your report.

I dont see the need for a subreport here, unless you are creating a report that has different data or a graph that does not pertain to the main data set.

Jon


Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]
Go to Top of Page

chriskhan2000
Aged Yak Warrior

544 Posts
Posted - 2005-01-28 : 11:52:52
Jon,

I'm a little confuse with the stored procedure that you are describing. Here's what I have. I have one stored procedure that outputs 2 fields, the Product_ID and Sale_Amount. I then have a second stored procedure that only output the field Total_Cost. The second dataset references the object of dataset 1, Product_ID. Which will output the correct Total_Cost to the Product_ID.

I can reference the object by using subreport to pass the field Product_ID to the Total_Cost stored procedure fine, but new problems arise after that. I want to sum Total_Cost and have it displayed at the footer of the main report and that's where the problem lies.

The question is how would I be able to do the same with 2 tables in the main report, one for Product_ID and Sale_Amount, and the other just for Total_Cost?

One big problem with joining these two together in the stored procedure is that I don't get the expected result. There seems to be more records in the Total_Cost, making the numbers double or triple.
Go to Top of Page

chriskhan2000
Aged Yak Warrior

544 Posts
Posted - 2005-01-28 : 12:07:25
About passing the value, I just want to use it as a parameter. FOr example use =Fields!Product_ID.Value as a parameter and be able to reference that with the second dataset.

Here's a example of the stored procedure.

Store Procedure 1:

SELECT DISTINCT CUSTOMER.PRODUCT_ID, SUM(CUSTOMER.AMOUNT) AS
[SalesAmount]
FROM CUSTOMER INNER JOIN
CUSTOMER_SALE ON CUSTOMER.ORDER_ID = CUSTOMER_SALE.ORDER_ID
WHERE (CUSTOMER.PRODUCT_ID IS NOT NULL) AND (CUSTOMER.DATE BETWEEN @STARTDATE AND @ENDDATE)
GROUP BY CUSTOMER.PRODUCT_ID
ORDER BY CUSTOMER.PRODUCT_ID

Stored Procedure 2:
SELECT SUM(MAT_COST + LAB_COST + BUR_COST + SER_COST) AS [Total Cost]
FROM INV_TRANSACTION
WHERE (TRANS_ID IN
(SELECT MAX(TRANS_ID)
FROM CUSTOMER, INV_TRANSACTION, CUSTOMER_SALE
WHERE CUSTOMER.ORDER_ID = INV_TRANSACTION.ORDER_ID AND
CUSTOMER_SALE.ORDER_ID = CUSTOMER.ORDER_ID AND CUSTOMER.PRODUCT_ID = @PRODUCTID AND (CUSTOMER.DATE BETWEEN @STARTDATE AND @ENDDATE)
GROUP BY CUSTOMER.ORDER_ID))

The @ProductID is use for referencing the Product_ID field in stored procedure 1. I can pass this object in Reporting services as a parameter to the subreport, but then is it possible to pass it as a parameter to another table in the same report?
Go to Top of Page

chriskhan2000
Aged Yak Warrior

544 Posts
Posted - 2005-02-01 : 15:13:25
Any thoughts on this anyone? I'm stuck at this part and can't seem to get any where. I also tried using two tables in the same report and pass one field as parameter to the other, but I can't get it to work either.

Example:

Table 1:

Product_ID, SaleAmount

Table 2:

TotalCost

Since table 2, in the WHere clause it's referencing the Product_ID, I try using the Product_ID field as the parameter and pass it to table 2. So far no luck.

Intended output:

Product_ID SaleAmount TotalCost
-----------------------------------
FABRICATED $2400.00 $1800.00
STEELBAR $1500.00 $1200.00
METALFAB $232.00 $80.00
------------------------------------
TOTAL $4132.00 $3080.00 Profit: $1052.00

etc...


Please advise.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site