Tuesday 24 November 2009

Reporting Services Top Level Access Rights - SSRS

To get a list of the top-level folders and associated user permissions:


SELECT Catalog.Name, Catalog.Path, Users.UserName
FROM Catalog INNER JOIN
Policies ON Catalog.PolicyID = Policies.PolicyID INNER JOIN
PolicyUserRole ON PolicyUserRole.PolicyID = Policies.PolicyID INNER JOIN
Users ON PolicyUserRole.UserID = Users.UserID
WHERE (Catalog.ParentID =
(SELECT ItemID
FROM Catalog
WHERE (ParentID IS NULL)))
ORDER BY Catalog.Path, Users.UserName

this code was obtained from:
http://virtualgenius.spaces.live.com/?_c11_BlogPart_BlogPart=blogview&_c=BlogPart&partqs=cat%3DSSRS

Reporting Services Access Rights - SSRS

To get a full breakdown of which users have access rights to reports in Reporting Services:


SELECT Catalog.Path, Catalog.Name, Users.UserName, Catalog.Type
FROM Catalog INNER JOIN
Policies ON Catalog.PolicyID = Policies.PolicyID INNER JOIN
PolicyUserRole ON PolicyUserRole.PolicyID = Policies.PolicyID INNER JOIN
Users ON PolicyUserRole.UserID = Users.UserID
ORDER BY Catalog.Path

this code was obtained from:
http://virtualgenius.spaces.live.com/?_c11_BlogPart_BlogPart=blogview&_c=BlogPart&partqs=cat%3DSSRS

Monday 16 November 2009

Checkboxes - CR

Provide Checkboxes for a boolean field.


If {fldName} = true then Chr(254) else Chr(168)


and set the font to wingdings.

Wednesday 11 November 2009

Set Operation EXCEPT - SQL

The following returns customers that have made no orders:


SELECT customerid FROM dbo.Customers
EXCEPT
SELECT customerid FROM
dbo.Orders

Tuesday 10 November 2009

Self Join - SQL

Self join between two instances of the same table. For example, obtaining employees and their managers.


SELECT E.Name as emp, M.Name as mgr
FROM Employees as E
LEFT OUTER JOIN Employees as M
ON E.ParentID = M.EmployeeID

Saturday 7 November 2009

Logical Query Processing Phases - SQL

Phases involved in the logical processing of a query:

(8) SELECT (9) DISTINCT (11) (top_specification) (select_list)

(1) FROM (left_table)

(3) (join_type) JOIN (right_table)

(2) ON (join_condition)

(4) WHERE (where_condition)

(5) GROUP BY (group_by_list)

(6) WITH (CUBE ROLLUP)

(7) HAVING (having_condition)

(10) ORDER BY (order_by_list)


Obtained from:
http://www.amazon.co.uk/Inside-Microsoft-SQL-Server-2005/dp/0735623139/ref=sr_1_2?ie=UTF8&s=books&qid=1257628926&sr=8-2