rs data not showing on report - 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

Development Tools

Reporting Services Development

rs data not showing on report

Author

Topic

jaynichols
Starting Member

18 Posts

Posted - 2008-10-09 : 15:04:26

I have a simple report listing a sales transaction file. At the end i have to show three separate totals, one for sales, one for returns and one for the combined total. My solution was to create a second stored proc to sum up the results based on what kind of transaction it was. I created text boxes, additional table, matrix, etc. None of these produced the results from the second dataset. Executing the dataset in the data tab i get the proper results. If I set the function in the text box to produce the date, it does that just fine. So i know the boxes are capable of rendering data. It just seems as though the dataset is not executed for the report. What else do I have to do? The fields are referred to with the qualifying dataset names.

Dirt biking forever!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-11 : 01:38:34

without giving details on what you're returning in second dataset, its hard to understand cause of this problem. Is it that you're not getting second dataset at all or is it that you're getting some error or blank rows?anyways giving some info on data you try to retrieve will help.

jaynichols
Starting Member

18 Posts

Posted - 2008-10-14 : 10:39:52

The second dataset code:
Truncate table SGTotals

insert into SGTotals(DelMargin,DelRetail,DelCost,DelQty)
select ((sum(SGH_FINAL_PRICE) - sum(SGH_FINAL_COST))/sum(SGH_FINAL_PRICE)) * 100.000 [DelMargin]
,sum(SGH_FINAL_PRICE) [DelRetail]
,sum(SGH_FINAL_COST) [DelCost]
,sum(SGH_FINAL_QTY) [DelQty]
from SGTABLE
where SGH_FINAL_QTY <> 0

declare @margin float, @retail float, @Cost float, @Qty int
set @Margin = (select((sum(SGH_FINAL_PRICE) - sum(SGH_FINAL_COST))/sum(SGH_FINAL_PRICE)) * 100.000 from SGTable where SGH_FINAL_QTY < 0 )
set @Retail = (select sum(SGH_FINAL_PRICE) from SGTable where SGH_FINAL_QTY < 0)
set @Cost = (select sum(SGH_FINAL_COST) from SGTable where SGH_FINAL_QTY < 0)
set @Qty = (select sum(SGH_FINAL_QTY) from SGTable where SGH_FINAL_QTY < 0)

update SGTotals
set RetMargin = @Margin
, RetRetail = @Retail
,RetCost = @Cost
,RetQty = @Qty

set @Margin = (select((sum(SGH_FINAL_PRICE) - sum(SGH_FINAL_COST))/sum(SGH_FINAL_PRICE)) * 100.000 from SGTable)
set @Retail = (select sum(SGH_FINAL_PRICE) from SGTable)
set @Cost = (select sum(SGH_FINAL_COST) from SGTable)
set @Qty = (select sum(SGH_FINAL_QTY) from SGTable)

update SGTotals
set TotMargin = @Margin
, TotRetail = @Retail
,TotCost = @Cost
,TotQty = @Qty

select * from SGTotals

Dirt biking forever!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources