What twitter hash tags do you follow for SQL Server?

#

The key to twitter is to follow the right hash tags.  What hash tags do you follow related to Microsoft SQL Server? Here are a few of the twitter hash tags that I follow:

#SQLServer

The generic SQL Server hash tag. Most anything goes on this hash tag.

#SQLHelp

The #SQLHelp hash tag is great when you need to get a question answered. May experts follow this hash tag and answer a wide variety of SQL Server questions. There are some general rules about not spamming this hash tag, and keeping it a pure Q and A mechanism.

#SQLPass

General discussion about PASS and PASS events. When a major PASS event is happening there is a lot of activity on this hash tag.

#SQLSaturday

General discussion about SQL Saturday events.

#SQLSatXXX where XXX is a number

Discussion about a specific SQL Saturday event.

#SQLFamily

Discussion between those who are part of the worldwide SQL Family… Those who thrive on SQL Server on twitter, and in the world at large.

#SSRS

Questions, answers and news about SQL Server Reporting Services.

#SSMS

Questions, answers, news, and gripes about SQL Server Management Studio. It’s common to read things like “lost all my work when SSMS locked up on me”.

What SQL Server hash tags do you follow?  Please post a comment with your favorite SQL Server related hash tag on twitter.  

Thanks, and keep on tweeting.

Can we use CTEs use in SSRS?

One of the common questions that I get asked after my Unleashing CTE Presentation is “Can we use CTEs in SSRS reports?”

The answer is YES, Common Table Expressions work great in SSRS reports, one of my favorite ways of cleaning up some of those big ugly SSRS report queries is to use CTEs.

No more Business Intelligence Development Studio in SQL 2012

There is no more Business Intelligence Development Studio (BIDS) in SQL Server 2012, instead it has been replaced with the SQL Server Data Tools that uses the core of Visual Studio 2010, with the Report Builder and Report Designer built in to the new Visual Studio.

The UI has changed colors, and looks a bit sharper, but overall you have similar functionality as before with a few new features.  Anyone who is used the the 2008 or 2008R2 report builder will have no problem here.  For those moving to this from 2005 or earlier it might be a bit of a surprise.

This is also a good time to mention… Please take a look at the Database Health Reports project.

 


SSRS – Using a subreport for navigation

As I have been working on my SQL Server Health Reports for my presentation at SQL Saturday 114 in Vancouver on March 17th, I have changed the way that I do things several times.  After attending a SSRS class “SSRS Beyond the Basics ” at SQL Saturday 108 in Redmond presented by @sqlbelle I learned a few tricks to help make this happen.

In a nutshell the SQL Server Health Reports project is a set of 11 reports to monitor some of the problems that creep into SQL Servers over time.  They all worked fine independent of each other, but I wanted them all to function as one application.  To do this I started building links from one report to another, then discovered that for each report that I add, I had to go back and edit all the previous reports to get them to link.  So here is the solution.

First I removed the headers from the old reports.  I discovered that you can’t put a subreport into a report header, so I just removed all the headers from the existing reports.

Then I set up a new report called Header.rdl shown here.  At first it looks a bit odd with the blue “Change Database: fields overlapping other fields, I will get to that soon.

Then I added 4 parameters to the header.rdl.

Database Name Parameter

The first parameter called Database Name is used and passed from report to report to keep the currently selected database around so that you don’t have to continually select the database name every time you switch reports.

ReportName Parameter

Used to show the large font at the top of the report with the name of that report.

ReportPath Parameter

The path to use to link back to the current report after changing the database.

Server Parameter

The name of the SQL Server that we are connecting to.  Currently the process only supports localhost, but I am working on expanding this.

 

 

Then in the header I implemented the ability to switch databases, which is very useful when browsing database metrics.  Next to the “Database: master” there is a + which indicates that clicking it will expand something.

Then I added a section that lists all of the databases on the currently selected server.  Which is initially not visible, but when you click the + it is shown, and it is shown over the current database name.

All of the database names are shown as links.  Clicking one of those links changes the @DatabaseName parameter and links back to the current report @ReportPath with the required parameters.  This is all accomplished on the action dialog on the settings page for each item in the list.

So clicking the link changes the database used in the report.

 

Next adding in the buttons with the action to switch between different reports.  The buttons are just text boxes colored to sort of look like buttons.  I added several gray buttons across the top of the report (yes I know that they need some UI work, gray buttons aren’t very interesting).

One button for each report in the project, with an action linking to that report passing along the required parameters.

 

And the action for each button looks like this for the DB Sizes button which links to the Database sizes report:

 

Then I added the header into each of the reports, and the the parameter for it.

From there it was just a matter of testing the linking between each of the reports, fixing any typo’s or other parameter errors, and getting it running.

Overall it took about an hour to set it all up and get it tested, but once it is set up it is now much easier to add a new report with a new link. To add this into a new report it takes about 1 minute to insert the sub-report and set the parameters for it.

Next step for another day, setting up a new SSRS template with a standard RDL for this project to save time for any new report that I add.

If you want to see it in action, or see the code behind it, download my SQL Server Health Reports project and take a look.  I hope you find this useful in your SSRS endeavors.

 

SSRS Editor Tip of the Day… CTRL + Arrows

I learned this from one of my co-workers.  A quick tip for formatting SSRS reports.

When you are in the SSRS report editor, and you want to move the currently selected object (or objects), you can move them with the arrow keys.  Most people who have used SSRS know that one, but did you know that if you use the CTRL key with the arrows that report objects move smaller jumps.  This helps when you are doing that final layout of a report to get everything positioned just right.

Using your own RDL as a SSRS Template

This is very useful if you are creating many reports based on a single template.

To start with when you use the “Add new Item” functionality into a report project, this is all you get.  Report Wizard, Report, and Data Source.  Then searching Online Templates is not very useful.  So how do I add my own SSRS rdl templates.

 

Steps:

  • Add a new report to your project.
  • Give it a name that you will remember.
  • Add any content into the report that you would want in the template.
  • Save the rdl file.
  • Copy the rdl file into

C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

  • Then start using the template.
Now to walk through the steps:

Add a new report to your project.

Give it a name that you will remember.

Add any content into the report that you would want in the template.

 

Save the rdl file.

 

Copy the rdl file

The rdl should be copied to:

C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

 

Then start using the template.

To use the template, just create a report project then insert a new item.

 

 

 

Once inserted your report template will show up as an rdl in the current project.

 

Further suggestions:

If you have a common template that you use, you may want to create 2 formats, one landscape, and one portrait.