what does Sum() Mean? - 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

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

what does Sum() Mean?

Author

Topic

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-08-16 : 08:09:09

Hi,

suppose table1 with (ID) field and table2 with (ID,tbl1_ID), in which tbl1_ID refers to table1. I've created a data set which includes the two tables. If I drag tbl1_ID to a text box on report, the value is =Sum(Fields!tbl1_ID.Value, "DS")

What does it suppose to return (I've filtered the data set on a specific table1.ID)? Does it return a comma separated list of all table2.IDs that relates to that specific table1.ID?

Practically,it doesn't show anything in my report!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-16 : 08:34:12

Nope it returns the sum of tbl1_ID field value of entire records which is available inside DS dataset. Why its putting Sum() while dragging and dropping is because of any one below
1.your putting this onto a row where you've applied some grouping
2.you've linked the table or textbox to this dataset
3.you're putting this to header or footer of a table.

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-08-18 : 03:14:05

thanks for the reply

Actually, I need to read a list of IDs from one DB server and find the related names from another DB server in one report.
Do you have any idea how to do this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 04:16:25

quote:
Originally posted by Peace2007

thanks for the reply

Actually, I need to read a list of IDs from one DB server and find the related names from another DB server in one report.
Do you have any idea how to do this?

The best way to do this is to bring the data onto single server and then write a dataset in your report which pulls the data joined from both the tables and displays it in report. you could even make a job in sql agent which does this daily so that each day you will get the latest data onto your server. the transfer logic can be done by means of ssis package which extracts data from your source, does some transformation (only if you've any else simple data dumping will do) and populate the table in your destination server.

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-08-18 : 05:17:18

The problem is that I'm working in a big company and have no right to do those you mentioned :(
There are only a few options for me:
1. do it via datasets in reporting services
2. write a stored procedure, which gets a list of IDs as input and returns a table containing the information of those IDs. In this case I need to gather IDs as a list and send it to the procedure via reporting service which I don't know how.
3. I'm having a stored procedure, which gets one ID as input and returns its information.IS it possible to call one stored procedure in a loop in reporting service?
4. whatever you suggest :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 05:34:36

quote:
Originally posted by Peace2007

The problem is that I'm working in a big company and have no right to do those you mentioned :(
There are only a few options for me:
1. do it via datasets in reporting services
2. write a stored procedure, which gets a list of IDs as input and returns a table containing the information of those IDs. In this case I need to gather IDs as a list and send it to the procedure via reporting service which I don't know how.
3. I'm having a stored procedure, which gets one ID as input and returns its information.IS it possible to call one stored procedure in a loop in reporting service?
4. whatever you suggest :)

Ok in that case what you could do is to call OPENROWSET in your procedure and get details from other server onto a temporary table and join that with your table (i assume you will atleast have rights to run OPENROWSET).Then make this procedure as your source for dataset. Then use the dataset in your report.
Re, your third point, you dont need to call procedure in a loop. you just need to pass a comma seperated list of values and write code to parse the values, retrieve each values from it and then apply filter on them.

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-08-20 : 08:08:21

They have blocked OPENROWSET as well! I'll ask them to give me a linked server.
However, I didn't get the third point! where I should write the code, in the stored procedure or dataset.Could you explain please?
Thank you Visakh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-20 : 09:34:20

quote:
Originally posted by Peace2007

They have blocked OPENROWSET as well! I'll ask them to give me a linked server.
However, I didn't get the third point! where I should write the code, in the stored procedure or dataset.Could you explain please?
Thank you Visakh

For 3rd point, my preffered way is to code your procedure in such a way as to accept a comma seperated list of values as parameter. Then you will parse this inside the procedure and return your value for each and finally merge them into single resultset which is returned to RS. if you want detailed help, post what your field values are and what you're trying to return using them using your table sample data.

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-08-24 : 01:45:23

Thanks a million Visakh

The exact scenario is as follows:
There's a list of projects and each project has a few members in DBServer1 I have a table including project ID and member ID and in DBServer2, I have member ID and member Name. I'm sending a project ID as parameter to a report and need to display related member names. So the list of parameter for that stored procedure is not stable since each project have a different number of members.
MY procedure,which gets a member ID and returns member information is:

ALTER PROCEDURE [dbo].[sp_GetMember] (@memID int)
AS
SELECT *
FROM  dbo.vw_SEprojects
WHERE  dbo.vw_SEprojects.Employee_Code = (@memID)

Now I need the new stored procedure to get a dynamic list of member ID, if possible, as input and return the result set.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-24 : 14:03:25

As said earlier, first thing you need to do is bring both table data to single server then you could just use the project id itself to get the member details. just a simple query like below would do
ALTER PROCEDURE [dbo].[sp_GetProjectMember] (@ProjID int)
AS
SELECT *
FROM dbo.vw_SEprojects p
INNER JOIN ProjectTable pt
ON pt.memberID=p.memeberID
WHERE dpt.projectid = (@ProjID)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources