Fields Expression - 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)

Fields Expression

Author

Topic

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-07-20 : 05:18:05

Hi,

I'm new to SQL reporting services. I've created a report and it displays contents of two tables well. Now,I'd like to replace one of the fields, which is and ID,with a value but don't know how.
suppose in one table we have {ID, Name, ParentID} and ParentID is actually an ID and I'd like to find the value of ParentID in ID column and replace it with corresponding name.
Any idea is appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-20 : 05:33:07

This you should do in your dataset source (either stored procedure or query whichever you've used). the query will be like this

SELECT t1.ID,t1.Name,t2.ID as ParentID,t2.Name AS ParentName
FROM Table t1
INNER JOIN Table t2
ON t2.ID=t1.ParentID

and if you want to do this recursively (i.e different levels of hierarchy until root level) you need a recursive CTE.

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-07-20 : 05:41:14

Thanks for the immediate reply
but I don't know where I should enter that query in SQl Reporting Services to work only on one of the fields of my report.

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-07-20 : 06:11:51

I got the solution :)
That was a silly question sorry

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-20 : 13:47:54