Bulk update of shared datasource for SSRS reports

 Computer, Sharepoint, Software  Comments Off on Bulk update of shared datasource for SSRS reports
Apr 242013
 
Share

Accidently killed a shared datasource for 1000 reports.  Found a handy query to update all those reports with a re-created datasource.

Updated a couple of reports manually in Sharepoint. Ran the following against the ReportServer database.

select c.Name as reportname, d.link, d.Name as datasource, d.dsid, d.itemid from Catalog c
inner join DataSource d on c.ItemID = d.ItemID
where ModifiedDate > '4/23/2013'

You are looking for the D.LINK response. This is your GUID for the second query below.

This will get you all the reports you borked.

select c.Path, ds.name
from DataSource as ds
join Catalog as c on c.ItemID = ds.ItemID
where ds.flags = ds.flags & 0x7FFFFFFD /*
and ds.Link is NULL
and ds.ConnectionString is NULL
order by path

And now update the ReportServer database.

update ds set [Flags] = [Flags] | 2, [Link] = 'YOURGUID'
output deleted.Name, deleted.DSID, deleted.ItemID, deleted.Flags
from datasource as ds
join catalog as c on c.itemid = ds.itemid
where ds.flags = ds.flags & 0x7FFFFFFD
and ds.link is NULL
and ds.connectionstring IS NULL
and path like '%folder%' /* just on the safe side I updated a couple of folders separately to make sure it worked */

		
 Posted by at 2:37 am  Tagged with:

SSRS – Query for NULLs and Order by NULLs last

 Computer, Sharepoint, Software  Comments Off on SSRS – Query for NULLs and Order by NULLs last
Apr 022013
 
Share

Had a request to create a report for Project Server milestones. There was a custom enterprise column added for tasks which contained a location code. My report had to return all values including where location was NULL.

The first step was to get a valid NULL parameter in my Site parameter drop-down menu.

SELECT DISTINCT(LT.MemberValue) as Site
FROM MSP_EpmTask_UserView AS T
INNER JOIN MSP_EpmProject_UserView AS P ON P.ProjectUID=T.ProjectUID
left join MSP_EpmCustomFieldAssociation on EntityUID = T.TaskUID
left join MSP_EPMLookupTable LT on lt.memberuid = lookupmemberuid
WHERE T.TaskIsMilestone=1
AND TaskFinishDate > GETDATE()
AND LT.MemberValue IS NOT NULL
— Here’s where we add NULL for the menu.
UNION
SELECT ‘(NULL)’ AS Expr1
ORDER BY LT.MemberValue

Second step was to create the dataset query to accept all values including NULLs.

SELECT ISNULL(Lt.MemberValue,'(NULL)’) as Site, P.ProjectName AS Project, P.PM AS Author, P.ProjectStartDate AS Start, P.ProjectFinishDate AS Finish, T.TaskName, TaskStartDate, TaskFinishDate
FROM MSP_EpmTask_UserView AS T
INNER JOIN MSP_EpmProject_UserView AS P ON P.ProjectUID=T.ProjectUID
left join MSP_EpmCustomFieldAssociation on EntityUID = T.TaskUID
left join MSP_EPMLookupTable LT on lt.memberuid = lookupmemberuid
WHERE T.TaskIsMilestone=1
AND TaskFinishDate > GETDATE()
AND P.ProjectName in (@Project)
AND TaskStartDate >= @TasksStart
AND TaskFinishDate <= @TasksEnd
GROUP BY Lt.MemberValue, P.ProjectName, P.PM, P.ProjectStartDate, P.ProjectFinishDate, T.TaskName, TaskStartDate, TaskFinishDate
order by project, ISNULL(Lt.MemberValue, ‘(NULL)’)

I then added a Filter on this dataset for the @Site parameter.

Third: There was a requirement that when sorting the table, Sites with NULL values should be LAST in the list.
The dataset query works in SQL Management Studio, however, not so much in SSRS. I had to put an expression in the Row group sorting field.

=IIF(Fields!Site.Value = “(NULL)”, “ZZZ”, Fields!Site.Value)