A new beginning

I am slowly migrating the articles from this site to a new location (www.lukehayler.com). Please check out the new site for these and other articles.

Running/Rendering SSRS Reports From the command line

I have been having umpteen issues with trying to get the Report Subscription Service (to File Share) working one of our servers and so have had to change tack slightly and try another method of getting reports onto a fileshare and subsequently to clients over FTP.

The magic of the rs Utility is that you can run/render reports from the command line, which means that (if xp_cmdshell is enabled in SQL Server) you can add a SQL Agent Job to generate reports.

I found that there are two parts to getting this right:

1. Creating an RSS file to carry out the required task [here's my example: GenerateReport.rss]

The file (GenerateReport.rss) is actually a .doc file (so it could be uploaded to WP). Open it and check the content, it is fairly easy to understand if you have any experience with VB.Net. Alter the file where appropriate (Format/Filename/ReportPath) & ‘Save As‘  a .rss file to a directory that you will be able to reference from the command line. IF your report requires the use of parameters, then see the resource links at the bottom of this post for more info.

2. The right command to run the .rss file

Here is the command I have used to run the GenerateReport.rss file:

‘rs -i C:\reports\GenerateReport.rss -s http://localhost/reportserver -e mgmt2000‘

Update the -i FileLocation and -s Reportserver accordingly.

Execute the command and check you Report directory for the file.

Here are some resources that I used getting this solution:

SQL Junkies , MSDN RSS files, Google Search, rs Utility

If you need more info on this topic please leave a comment and I’ll repy as soon as I can.

-Luke

SSRS ERROR: Could not find file C:\WINDOWS\TEMP\1347m7s0.dll

I have come across this error several times before and have tried numerous different ‘fixes’ that I found through as many searches. I found out that at each occurence of this error one update or another had been done on the server, but no reboot had been carried out. It turns out that that is all I needed to do to get everything back in sync and working again.

If in doubt. REBOOT

Screen shot: Could not find file C:\WINDOWS\TEMP\1347m7s0.dll

 

Powered by ScribeFire.

Login Pop up on SSRS Deployment

Today I tried to do a redeployment of some reports to a new server and kept getting a Login pop-up.

It turned out the the TargetServerURL I was pointing to was incorrect. I had created a named (i.e. not default Reports/ReportServer) website (ClientReports/ClientReportsReportServer) a while back and had used the correct url in my server properties.

As soon as I had updated the details, deployment was no longer a problem.

Powered by ScribeFire.

Custom Visibility Toggling in the SSRS Table Object – Quick Tips

Have you ever wanted to show some of your table groupings in an expanded state and some in a collapsed state when the report is first run?
By using the visibilty properties of your cell that you use to toggle the hidden items/rows, you can choose exactly which groups should be expanded and which should be collapsed when the report starts up.

How?

In the example below, textbox8 is my toggle item for the group “Priority“. When the report loads I want to show the “critical” items in an expanded state, and all other priorities in a collapsed state. By right-clicking on textbox8 and choosing properties I get the window below. Select Expression for the Initial appearance of the cell and enter a formula that will decide is the initial state is expanded (‘true‘) or collapsed (‘false‘). What you use in your expression to evaluate whether or not the toggle item is expanded or collapsed is up to you.
That’s it!

Powered by ScribeFire.

Removing The Document Map in SSRS

Removing the document map in a SQl Server Reporting Services report is easy when you know where to look, and can potentially drive you insane trying to find out how to do it…..

1. In the solution explorer, right-click on the report that has the document map and select ‘veiw code’.
   
2. Now hit ctrl+F and search for “<Label>“.
   
3. Click on find next to find the occurence of the Label tag,
   
4. Remove the value that is contained within it. ie:

• From: <Label>=Fields!Year.Value</Label>
• To: <Label></Label>
  

1. DO NOT Delete the <Label></Label> tags!
   

Save the file anda close the Code View tab. Preview your report. It’s gone! Woo hoo!!

Powered by ScribeFire.

Sorting more than one column using Interactive Sort in SSRS

This is a quick tip.

This post by Joe Webb describes very clearly how to add interactive sorting to a SQL Server Reporting Services Table. However, there are a few comments that I’ve seen that ask how to sort on multiple columns. It is actually quite simple:

Run the report and click on the first column you want to sort by, then holding down the Shift key click on the next column that you want to sort by (clicking a second time to change the direction of the sort). And that’s all there is to it. You can sort by as many columns as you want to.

Using the DateAdd() Function in SSRS

I seemed to struggle with this for a little while (Microsoft is notorious for never being able to use a standard for expressions/syntax across they’re products…don’t get me started). After a bit of digging I found a couple of posts (msdn, russell christopher) that made some sense of the madness.

If you don’t want to read through them, here are some examples that DO WORK:

=DateAdd(DateInterval.Month, 6, Today()) ‘just ignore the red lines that appear under Month
=DateAdd(“D”, 6, Today())
=DateAdd(“Month”, 1, Today())

Trust Microsoft to make as easy as possible…..

Report Execution Logging and Analysis

Whilst reading the forums & articles at SQLServerCentral I came accros this article all about Monitoring Report Execution Performance with Execution Logs.

It is a short but informative article about how to set up Report execution logging and how to analyse the data with the sample Reports (supplied). Whilst it is not all-encompassing, it certainly provides the ground work for analysis into how long reports take to execute, where bottlenecks occur, frequency of execution, parameters used in each report, user report usage, to name a few.

I, for one, will be setting up this functionality for all my client solutions ASAP.

== Update: 2008/07/28==

After searching high and low I finally found the files that the article was referring to. This site provides an install file that includes all SQL 2005 samples (not just the ones specific to this article).  This msdn article provides the instructions for installing the files, if you need them)

Once I have had a chance to install & review the sample reports, I will create another post detailing my findings.

Technorati Tags: SQL Server reporting Services, SSRS, Hints & Tips, Great Articles

Using temp tables, Table variables, & CTE’s in Views

I was recently in the unfortunate position of not being able to use stored procedures to return a dataset, and had to use a view instead. The trouble I had was that my SP was using temporary tables to aggregate a number of fields prior to using those aggregates in the final SELECT statement. As you may (or indeed, may not) know, one cannot use variables (eg. TABLE variable) or temp tables (eg. #temp/##temp) in views. So this was a bit of a problem. After a bit of digging however, I chanced upon Common Table Expressions (aka: CTE’s).

As per msdn a CTE is defined as follows:
“A common table expression (CTE) can be thought of as a temporary result
set that is defined within the execution scope of a single SELECT,
INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a
derived table in that it is not stored as an object and lasts only for
the duration of the query. Unlike a derived table, a CTE can be
self-referencing and can be referenced multiple times in the same query.”

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries.
- you may of course use nested SELECT statements in your query, but this can quickly get messy.

The basic structure of a CTE:
– ========================================
WITH expression_name [(column_1, column_2, ...n)]
AS
(CTE_query_definition)

Example:

WITH myTable
AS
(Select CustomerID, TotalOrders = COUNT(OrderID), TotalOrderAmount = SUM(OrderAmount), AverageOrderAmount = SUM(OrderAmount)/COUNT(OrderID)
From Orders Group by CustomerID)

– You can then do with your CTE what you like: SELECT, INSERT, DELETE, UDPATE, JOIN to other tables…
SELECT CustomerName, CustomerAddress, TotalOrders, TotalOrderAmount, AverageOrderAmount
FROM myTable m INNER JOIN Customers c
ON m.CutsomerID = c.CustomerID
–================================================================================

The above is just an example, and could of course, be done a number of different ways. The point was to demonstrate what you could do with a CTE.

All that is left to do now is wrap the code in a Create View Statement and that’s it.

There are many other (& better) uses for CTE’s, but I found this to be a great one. It keeps the code neat and tidy, and you are able to step through each part of your query simply in order to get you result. Which is great if you are still learning, or ensuring that your code is readable by the next developer to come across it. Don’t forget: “Comments are King!”