Dynamic SQL Newbie needs urgent help -- sql server reporting services

Nat Johnson
8/15/2005 3:29:01 PM

Have a report that is working fine. Customer wants to add another field to
it. I have a field name of navigationPK to get the field they want. Only
problem is is that the navigationPK can be a primary key from any number of
tables.

Scenario.......

Have a usertask displayed on dashboard.....need to do a report on all
overdue tasks. Each task has a particular navigationpk. This navpk can be
linked to three or four tables. ie.....navpk 1189134 could be the pk in a
tpp table, rvc table or a tr table. Need to ascertain which one it is so
that I can get the relevant additional field from each table.

Kinda hard to explain. But if anybody wants anymore info then please ask.
I was told to do dynamic sql....only prob is I have no idea where to start
and how to fit into existing script. Below is my initial script.....works
fine so far. Needs tidying up I know, but essentially it works.

select fulllegalname, p.Id AS PersonId, dali.Name As DDCMAreaName,
ut.UserName As UTUserName, ut.MessageBody AS Task, ut.NavigationPK as NavPK,
ut.DueDate As DueDate
--h.id as HerdId

from person p
--INNER JOIN personherdrole phr on p.id = phr.personid
--INNER JOIN herd h on phr.Herdid = h.id
INNER JOIN personddcmarealink pdal on pdal.personId = p.Id
INNER JOIN ddcmarealookupitem dali on pdal.ddcmarealookupitemId = dali.Id
INNER JOIN usertask ut on ut.UserName = p.UserName
where (ut.DeletedDate IS NULL) and (ut.DueDate < getdate())

The additional field required is the herdid, and this herdid could be from
the tpp table, rvc table, tr table .

Help please. Going round in circles and not sure where to start.

rmartinez
8/16/2005 8:17:06 AM

Article about dynamic sql
http://www.sommarskog.se/dynamic_sql.html

You did not describe the rules as to how you know which table it will be in.
Can it be in multiple tables but you only want a specific one?
Or the HerdID will only be in one of the tables?
If so
select fulllegalname, p.Id AS PersonId, dali.Name As DDCMAreaName,
ut.UserName As UTUserName, ut.MessageBody AS Task, ut.NavigationPK as NavPK,
ut.DueDate As DueDate
--h.id as HerdId
from person p
INNER JOIN personherdrole phr on p.id = phr.personid
INNER JOIN herd h on phr.Herdid = h.id
INNER JOIN personddcmarealink pdal on pdal.personId = p.Id
INNER JOIN ddcmarealookupitem dali on pdal.ddcmarealookupitemId = dali.Id
INNER JOIN usertask ut on ut.UserName = p.UserName
Inner JOIN (SELECT newfield, HerdID
FROM tpp
UNION
SELECT newfield, HerdID
FROM rvc
UNION
SELECT newfield, HerdID
FROM tr ) DerivedTable on DerivedTable.HerdID=HERD.id
where (ut.DeletedDate IS NULL) and (ut.DueDate < getdate())
No Dynamic required

[quoted text, click to view]

gene h via SQLMonster.com
8/16/2005 5:03:39 PM

1. create your select statement
2 run the select statement and generate fields
3. generate report

last but not least turn the sql into a string after you have everything
working. us a report parameter to pass the dynamic part to the querry.

like this

="select * from " + Parameters!company.value

its that easy.

[quoted text, click to view]

--
Message posted via SQLMonster.com

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server reporting services

sql server reporting services : Dynamic SQL Newbie needs urgent help