SSRS Report List for your Report Catalog

Obtain a list of reports in your  SQL Server Reporting Services Catalog with this nice query, which you can plug straight into a Reporting Services report.


SELECT Catalog.*

FROM

(

Select   [Name],

CASE

WHEN Type = 1 THEN 'Folder'

WHEN Type = 2 THEN 'Report'

WHEN Type = 3 THEN 'File'

WHEN Type = 5 THEN 'Data Source'

WHEN Type = 9 THEN 'Report Part'

ELSE 'Other' END AS ContentType,

CASE WHEN SubType IS NULL THEN 'N/A'

ELSE SubType END AS 'Report Part Type',

[Description],

SubString([Path],1,Len([Path]) - (CharIndex('/',Reverse([Path]))-1))
) As [Path],

Case

When [Hidden] = 1

Then  'Yes'

Else    'No'

End As [Hidden]

From    [Catalog]

)

Catalog

Order By Path, Name

Thanks to Nathon Dalton for this blog

http://nathondalton.wordpress.com/2010/02/22/get-reporting-services-report-list-from-database/

Analysis Services Many to Many Relationships Must Read

Many to many relationships are a fact of life in business intelligence data modelling.  Whether you are just starting out or halfway down the road, be sure to read the following article by Marco Russo and Alberto Ferrari – and excellent paper on Many to Many relationships in Analysis Services.

http://www.sqlbi.com/wp-content/uploads/The_Many-to-Many_Revolution_2.0.pdf

 

 

Query Another Instance of SQL Server with sp_addlinkedserver and sp_addlinkedsrvlogin

Adding a linked server through SQL Server Management studio can be a tad confusing.   You could be much better off using a standard set of T-SQL commands to help expedite the setup using sp_addlinkedserver and sp_addlinkedsrvlogin.  Here goes …

SP_ADDLINKEDSERVER

The following example creates a linked server to another sql instance :

EXEC sp_addlinkedserver

@server=N'COMPUTER_A',     -- Enter Linked Server Name

@srvproduct=N'',                         -- Not Needed

@provider=N'SQLNCLI',           -- Enter the SQL Server Driver Here

@datasrc=N'COMPUTER_A\SQLEXPRESS'; -- Enter the Instance Name Here

SP_ADDLINKEDSRVLOGIN

The following example uses a mapping to make sure that all logins to the local server connect through to the linked server COMPUTER_A by using their own user credentials.

EXEC sp_addlinkedsrvlogin ' COMPUTER_A'

The following example creates a mapping to make sure that the Windows user Domain\Mary connects through to the linked server COMPUTER_A by using the login MaryP and password d89q3w4u.

EXEC sp_addlinkedsrvlogin ' COMPUTER_A', 'false', 'Domain\Mary', 'MaryP', 'd89q3w4u'

Once the linked server is in place you can use OPENQUERY or you can use a SYNONYM i.e.


USE tempdb;

GO

CREATE SYNONYM MySynonym FOR COMPUTER_A.AdventureWorks2012.HumanResources.Employee;

GO

References :

http://msdn.microsoft.com/en-us/library/ms190479(v=sql.105).aspx

http://msdn.microsoft.com/en-us/library/ms189811(v=sql.105).aspx

Format a Timestamp String Expression for a Filename in SSIS

Two useful methods for how to format a timestamp string  in SSIS – assign to a package variable via a Script task, or, via a regular expression. Both techniques are outlined.

The first option is to use the Script task and then use .Net code to format the timestamp as outlined here…

http://www.mssqltips.com/sqlservertip/2530/dynamically-name-text-files-in-sql-server-integration-services/.

Another simpler alternative for the .Net disinclined is to create a package variable called @Timestamp, then format an expression that evaluates to a string version of the current timestamp.  You can use the package start time, container start time or getdate() as a few options.     Then create your package filename variables and reference @Timestamp in the filename expressions.

The following code snippet will configure the timestamp string in the format yyyy_mm_dd_hr_min_sec based on the package start time system timestamp…


(DT_WSTR, 4)YEAR( @[System::StartTime])

+ (DT_WSTR,1)"_"

+ RIGHT("0" + (DT_WSTR, 2) MONTH( @[System::StartTime]),2)

+ (DT_WSTR,1)"_"

+ RIGHT("0" + (DT_WSTR, 2) DAY( @[System::StartTime]),2)

+ (DT_WSTR,1)"_"

+ RIGHT("0" + (DT_WSTR, 2) DATEPART( "hh", @[System::StartTime]),2)

+ (DT_WSTR,1)"_"

+ RIGHT("0" + (DT_WSTR, 2) DATEPART( "mi", @[System::StartTime]),2)

+ (DT_WSTR,1)"_"

+ RIGHT("0" + (DT_WSTR, 2) DATEPART( "ss", @[System::StartTime]),2)

If you choose to use getdate() however take care when referencing the same filename in more than one task in the package as the timestamp could shift between tasks and you will get a file not found error.

Deploy SSRS Report Parts From Report Designer with SQL Server Data Tools

Report Parts can be managed in SQL Server Data Tools (previously Business Intelligence Development Studio) through the Report Designer, as well as Report Builder 3.0 (available in SQL Server 2008 R2).

From your SQL Server Data Tools (SSDT) Project, double click your report, go to the Report Menu and Select Publish Report Parts, then select the parts you specifically would like to publish in the current report.

Image

The difference between Report Designer and Report Builder is that Report Builder 3.0 will actually deploy the parts to your Report Server, whereas Report Designer still has an additional step to go.  To finish the job, go to the deployment properties for your SSDT Report Server project, set deployment options for your report parts and deploy the project.

Image

Also, unlike Report Builder, republishing a Report Part from Report Designer has to be made in the report you created the report part.    Whereas Report Builder, allows you to republish an existing part by simply republishing the changes even if you are not in the original report.  An option to ease the management is to maintain all of your report parts in one or more master RDLs.

Images can be saved as stand alone Report Parts or incorporated into existing parts e.g. A report Header Rectangle.  Use External or embedded links to store the image before publishing the part.  If the environment is too complicated for managing External links between development and production environments, consider embedding the image before publishing the part.

Managing the deployment of Report Parts through Visual Studio SSDT will make it easier to deploy report part content between the various environments whether it be development, test or production.

Windows 8 – Winlogon.exe – No disk in drive errors on Shutdown

You get multiple Winlogon.exe – No disk in drive errors on shutdown of Windows 8.   Additionally, the PC hangs in a powered state, even though the monitor has switched off.  Initially I thought this was due to driver issues with the PC’s card reader, however stumbled on this excellent blog http://www.ptmaroct.com/2012/06/fix-windows-8-rc-shutdown-errors.html.

Disabled fast start up (search Control Panel for “Change what the power buttons do”) – the errors magically disappeared and the PC now shuts down.  Thanks !

Monday SSRS Quick Tip – Formatting 24 hour time

You can format a datetime column as 24 hour time in a Tablix by issuing a capital HH for the hour component in the Format property. e.g. the following expression will show 24 hour time.  Note HH will do leading zeros where applicable, H will not.


dd/MMM/yyyy HH:mm:ss

… and the following expression will show regular 12 hour time with AM/PM.


dd/MMM/yyyy hh:mm:ss tt