Tuesday, December 1, 2009

December 2009 – Baton Rouge SQL Server User Group

December SQL Server User Group Meeting

Location: At Lamar Advertising

Wednesday, December 2, 2009

6:00 PM - 8:00 PM

Sponsored by: EMC

Topic

An Overview of Business Intelligence

Presenter

Brandon McMillon

Brandon McMillon is a Senior Solutions Principal at EMC.  He has 10 years of experience at Microsoft, working on the Visual Studio & Windows development teams.  He was also a Chief Software Architect for a Microsoft Gold Partner before coming to EMC.

Abstract:

This talk will first examine the current marketplace for Business Intelligence, and how it has gotten there.  We’ll look at how EMC is leveraging some of the newer tools and products to lower the costs and effort for implementing BI, in the context of a real-world EMC BI project.  We’ll also share best practices & lessons we’ve learned in these projects.  Finally, we’ll look at the next generation of BI tools and products, and talk about how they will impact the market and what problems they’re attempting to solve.

Agenda

5:45 pm - 6:00 pm:

General Introduction/Food and Drinks

6:00 pm - 7:30 pm:

Overview of Business Intelligence

7:30 pm - until:

Open forum for questions

Talk to you soon

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

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:

clip_image002

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.

clip_image004

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.

clip_image006

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

clip_image008

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:

clip_image010

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:

clip_image012

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