I have a project due shortly and keep getting Sum(Field!Amount.Value) instead of just =Field(Amount.Value) in the detail row.
s there a way to pull in results from multiple queries in one datasets in the SQL Reporting Services 2000
I have to add an amount field in different fields and I keep getting the Sum instead of
individual amounts if I use different datasets and if not, I get the same amount.
Is there a way around this
Thank you for your help.
SK
Is there a way to Un-sum in SQL Server 2000 Reporting Services
Sepp Obermayer
You have to join the data in the data set query. There are multiple options to achieve this:
* if the data comes from different tables in the same database, join the datasets based on a common key present in both tables
* Use the Linked Server functionality (see http://msdn.microsoft.com/library/default.asp url=/library/en-us/acdata/ac_8_qd_12_23xd.asp)
* Use the OpenRowSet functionality to join data from another database server into the current query (http://msdn2.microsoft.com/en-us/library/ms190312.aspx,
http://msdn.microsoft.com/library/default.asp url=/library/en-us/tsqlref/ts_oa-oz_78z8.asp)
Note: Using OPENROWSET is in general not as efficient as using the linked server
though. Follow the below link to find the related info:
http://www.sql-server-performance.com/linked_server.asp
Here is an example for a simple OPENROWSET using integrated security:
USE pubs
GO
SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=xxxxxxxx;Integrated Security=SSPI;',
pubs.dbo.authors) AS a
ORDER BY a.au_lname, a.au_fname
GO
Note: Before trying this in a report, you might want to try simple queries
through Query Analyzer to make sure the target server does not reject
integrated security.
-- Robert
jko
As mentioned previously, RS currently does not support joining datasets in the report - you have to perform the join in the dataset query so that you only have one dataset that contains all the data.
In a data region bound to Dataset1, you can reference detail data from Dataset1, but only dataset-level aggregate data from any other dataset.
-- Robert
DineshB
The joins are not really a problem. I have INNER JOINS in my query, but I need to use rows as columns. So I've created several datasets to accomplish that through multiple queries.
I just don't understand why the "Sum" can not be changed through IIF or any other way.
And I can't have more than 1 query per dataset.
SK