First SQL Lunch of the Year - SSRS Tips and Tricks with Pam Shaw

Last Year was a great year for the SQL Lunch.  We had so many great presentations.  I am really excited about this year and what we have coming up.  There are three lunches scheduled for this month and we are going to be making a BIG announcement about what we are doing in May on the SQL Lunch.  Stay tuned.

45-Tips & Tricks for dynamic Reporting Services Reports

Speaker: Pam Shaw

Join Meeting:  https://www.livemeeting.com/cc/usergroups/join?id=PNZ5F4&role=attend

Add To Outlook: Add To Calendar

Date and Time: 1/13/2011 11:30:00 AM CST

Topic: #45-Tips & Tricks for dynamic Reporting Services Reports
In this session we will explore the multitude of ways that you can make your reports adapt to the needs of your users. This will include defining formats based on the data contents. We will explore how to refine layouts based on the data to be delivered. We will demonstrate how to create a parameter driven data source. We will take a look at how creating templates can give your catalog of reports a more professional look. All of this together will empower you to have more control over the reports you create.

BIO: Pam Shaw has been in IT for almost 30 years. Since 2001, Pam has been working with SQL Server, first 2000, then 2005 and now 2008. Pam is currently an independent contractor. Pam is also the Chapter Leader of the Tampa Bay SQL Users Group - a local chapter of PASS and organizer of SQL Saturday Tampa.

Patrick LeBlanc, MCTS, SQL Server MVP

Founder www.SQLLunch.com

SQL Server Reporting Services R2 – Publishing Report Parts

SQL Server Reporting Services R2 (SSRS) allows report developers to not only publish reports and data sources to the report server, but now they have the ability to publish report parts.  You can publish parts of a report, such as charts, tables, logos, etc…  In addition to the report part, any data set and/or parameter that the part depends on accompanies it as metadata.   End-users will have the ability, using Report Builder 3.0, to create reports based on the published Report Parts.  So, how do you publish the report parts?

First, create  a report that contains a few charts, tables and maybe an image.  Similar to the following:

Each item on the report, the five charts and the logo, can be published to the report server as a report part.  Ensure that you give each part that you plan on publishing a descriptive and meaningful name.  Once that is done, select Report from the menu bar and select Publish Report Parts.  The following screen will appear:

Then select the checkbox next to each item that you want to publish as a report part and click OK.  The next time you deploy your report project each selected item will be published to the report server.  Finally, prior to deploying the report parts, you can specify the location where these items will be deployed.  Right-click on the project in the solution explorer and select Properties:

 

Under the deployment section in the row labeled TargetReportPartFolder, you can specify the location where you want to deploy the report parts.  Your end-users can now connect to the report server and use these report parts in their reports.  I will explain how they do this in my next blog post.

Talk to you soon,

Patrick LeBlanc, MCTS

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.

SSRS – Format Multiple Columns

Often when designing reports you are required to apply the same formatting to multiple columns in one table or matrix. Since SSRS 2008 does not allow the developer to change the format of a column using Textbox Properties when multiple columns are selected, the developer must either:

· Change the formatting one column at a time

· Be familiar with the expression language.

Neither of the aforementioned choices are difficult, but maybe time constraints eliminates both as viable options. Therefore, I am going to share a quick tip that will help you format multiple columns quickly.

Let’s assume that you have a report that looks similar to this:

If the requirements were to change the formatting of each TotalDue column to Currency you can quickly change all of them by using the following steps:

1. Using the Textbox Properties change the format to Currency for one of the TotalDue columns.

2. Once the format has been set, right-click on the column and choose Properties.

3. In the properties window locate the Format attribute and copy the value for that property.

4. Return to the table or matrix and select all the additional columns that require formatting.

5. Open the properties window again, ensuring that all the columns remain selected.

6. Paste the format value into the Format attribute.

7. Run your report and all the columns should be formatted.

Now all of your columns are quickly formatted. If you have another method to accomplish this please share it. If you have any questions or comments about this posting please email me at pleblanc@pragmaticworks.com.

Talk to you soon,

Patrick LeBlanc

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.

SSRS – KPIs without Analysis Services

During a recent project I was asked if it was possible to simulate Key Performance Indicator (KPI) images in SQL Server Reporting Services (SSRS) without Analysis Services. Off the top of my head I thought of a couple of things, but I decided to do a little searching before I embarked on my latest challenge. After one quick search I found a great video posted on SQL Share by SQL Server MVP, Jessica Moss, Designing a KPI in Reporting Services.

Using her example, with a few modifications I was able to produce exactly what the client needed. See the following screenshot for an example:

So how did I do it?

To start, I had a quick meeting with the client to determine the calculation for the GOAL of the KPI. It was simply a percentage based calculation between current YTD sales and the prior YTD sales ((CurrentYTD/PriorYTD)*100). The value indicators would be determined by the results of the calculations. As seen in the above screenshot, if the calculation was greater than 100 a green arrow should be shown, if it was greater than or equal to 90 and less than 100 a yellow arrow should be show and if the value was less than 90 a red arrow should be shown.

After all the requirements were gathered, the next step was to develop a method to dynamically show the arrows based on the KPI Range Values. First I created a calculated column that used the aforementioned calculation. To do this, add a calculated field to your existing dataset by right-clicking the dataset and select Add Calculated Field. The Dataset Properties window will appear:

Click the Add button and insert your calculation. You can also return the calculation as part of your result set and ignore this step.

Next add a column to an existing Table item on the report. Then add an Image into the column. See the right-most column in the following screenshot:

Next, right-click the image and select Image Properties and the following window will appear:

=SWITCH ( Fields!KPI.Value > 100, "GreenUpArrow", Fields!KPI.Value >= 90 AND Fields!KPI.Value < 100, "YellowAngleArrow", Fields!KPI.Value < 90, "RedDownArrow" )

Then click the expression button next to the Use this image drop down list. In the expression textbox enter the following or something similar to the following:

The SWITCH function will allow you to specify the image that should be displayed based on your KPI values. If you are not familiar with the SWITCH function read my blog posting, Reporting Services SWITCH Function , for more details. In the function I specify the image to display based on the calculated KPI Value.

Next click on the Size in the right pane of the Image Properties box:

Ensure that the radio button labeled Clip is selected and you can also adjust the padding on the images just in case you want to change the alignment of the image. Click ok and run your report. You now have KPI type images in your Reporting Services report.

Downloads

KPIExample

AdventureWorks2008DW

As always, if you have any questions, comments or concerns regarding this posting please feel free to email me at pleblanc@pragmaticworks.com.

Talk to you soon,

Patrick LeBlanc

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.

Linear Gauge Control: A Practical Use

SQL Server Reporting Services 2008 introduced several new features. One key feature was the inclusion of the Gauge Controls. I was recently asked to provide a real-world example of the Linear Gauge Control. Since I am always up for a challenge, I delve head deep into the gauge controls. After doing a little reading and testing of the linear control I chose my path. I started with the following report:

The data in the report can be reproduced using the stored procedure provided at the end of the posting. As you can see the report contains Sales data from each country. Looking at the report I noticed, after a little time, that the United States has the largest amount of sales. In the past I would have added a parameter that allowed dynamic sorts based on the Total Sales column, which is not a bad solution. This helps the end-user to quickly identify the BIG winners and losers in regards to sales. However, what about all the countries that fall in between? As a result, I decided to add a linear gauge control that would graphically depict sales as a percentage.

When you initially add the Linear Gauge control to the report, there are several items (ranges, pointers and labels) that are unnecessary.

Therefore before attempting to associate data with the control, I remove three things:

1. LinearPointer2

2. Two right-most Ranges, which are shaded in the lighter two shades of gray

I also hide the labels and any tick marks, which can be done by selecting the entire scale and un-checking the items labeled Show Labels and Show Major Tick Marks.

In then end you are left with a control that looks like this:

The next thing I did was to set the End range at scale value for the remaining Range to 100. Since I am attempting to graphically depict sales as a percentage, comparing the values to 100 should provide a meaningful representation of the data. To calculate the percentages I used the following expression:

=(Fields!TotalSales.Value/SUM(Fields!TotalSales.Value,"Tablix1"))*100

In the expression I divide the Sales for each country by the total sales for all countries. Note that the sum for all countries is calculated by using the sum function, including the scope value. Finally the value is multiplied by 100 to ensure that the result is between 0 and 100, which is the start and end values of the controls only range (the remaining dark grey bar in the control).

Now the only thing left is to assign the calculated value to LiinearPointer1. To do this, select the orange part of the control only. Then right click, the following dialogue box will appear:

Click Pointer Properties, then the Linear Pointer Properties dialogue box will appear. Choose the expression button next to the textbox labeled value and insert the above calculation. Once complete run the report and you will be presented with a result similar to the following:

As you can see from the above image, an end user can easily identify which country is selling and which is not. If you have any questions or comments concerning this topic please feel free to email me at pleblanc@pragmaticworks.com.

Talk to you soon

Patrick LeBlanc, founder www.sqlscripts.com and www.sqllunch.com

Deploying Reports on Windows 7

Recently I tried to deploy a report to a Report Server on my laptop, which is running Windows 7 Ultimate. Unfortunately I received the following error:

The permissions granted to user ‘MachineName\SomeUser ‘ are insufficient for performing this operations.

This confused me a bit since my account was an administrator. After digging a bit I found a very simple solution. Right-click on the Visual Studio icon or the Business Intelligence Development Studio icon and click Properties. Then go to the Compatibility tab. On that tab, in the Privilege Level section select the checkbox next to the item labeled Run this program as administrator. See the below screen shot example:

Once done, restart the application and Deploy your report.

Talk to you soon

Patrick LeBlanc, founder www.tsqlscripts.com and www.sqllunch.com

How to Create a Reporting Services 2005/2008 Template

 

At most large companies one business requirement is that all reports have the same look and feel.  This may vary by department, but there is typically some level of standardization amongst the business entities.  In most cases there is a header and footer template that needs to be seen on all reports.  Often developers I have seen developers start from scratch or copy and paste and existing report.  Those days are gone.  For all of you still using Reporting Services 2005, don’t worry this method is available to you also.  Here are the steps:

1.  Create a template report, maybe and .rdl that contains only the header and footer information.  These are items that are typically used throughout a company or department.

2.  Copy the .rdl file to one of the following directories

(SSRS 2005) - C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

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

The next time you create a report, right click on the Reports folder in the Solution Explorer of your Reporting Services Project.  Then choose Add –> New Item.  A dialogue box will open, similar to the one below:

In the above screen shot, my template is named Dashboard Template.  Choose that item and click Add.  When the report is added to you project it will look exactly like the template you created.  Happy Report Writing!

Talk to you soon

Patrick LeBlanc, founder www.TSQLScripts.com and www.SQLLunch.com

Today’s SQL Lunch – Integrating Table Valued Parameters with Reporting Services 2008

Meeting URL: Join Meeting

Click the above Meeting URL around 11:30 AM CST on 11/16/2009 to join the meeting

Date: 11/16/2009

Time: 11:30 AM

Meeting URL: https://www.livemeeting.com/cc/usergroups/join?id=NHHGB9&role=attend

Presenter: Patrick LeBlanc, currently the Sr. Database Administrator for Lamar Advertising. I have worked as a SQL Server DBA for the past 9 years. My experience includes working in the Educational, Advertising, Mortgage, Medical and Financial Industries. I am the founder of TSQLScripts.com, SQLLunch.com and the President of the Baton Area SQL Server User Group.

Topic: Integrating Table-Valued Parameters with Reporting Services

Patrick LeBlanc, currently the Sr. Database Administrator for Lamar Advertising. I have worked as a SQL Server DBA for the past 9 years. My experience includes working in the Educational, Advertising, Mortgage, Medical and Financial Industries. I am the founder of TSQLScripts.com, SQLLunch.com and the President of the Baton Area SQL Server User Group.

If you would like to receive email notification about upcoming SQL Lunches go here:

http://www.sqllunch.com/Register.aspx

Talk to you soon

Patrick LeBlanc, found TSQLScripts.com and SQL Lunch

SQL Down South