D J Horton Consulting Ltd

Personal reference to useful tips and tricks for SQL Server, SQL Server Reporting Services and Crystal Reports.



My alternative to scraps of paper lying about.



Others may find bits of it useful!








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