Join me for Lunch - SQL Lunch #33 (Indicators, Sparklines and Databars)

#33-SSRS 2008 R2 - Indicators, Sparklines and Databars

Speaker: Patrick LeBlanc

Add To Outlook: Add To Calendar

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

Date and Time: 9/1/2010 11:30:00 AM CST
BIO: Patrick LeBlanc, SQL Server MVP, is currently a Business Intelligence Architect for Pragmatic Works. He has worked as a SQL Server DBA for the past 9 years. His experience includes working in the Educational, Advertising, Mortgage, Medical and Financial Industries. He is also the founder of TSQLScripts.com, SQLLunch.com and the President of the Baton Rouge Area SQL Server User Group. Patrick is a regular speaker at various SQL Server community events, including SQL Saturday’s, User Groups and the SQL Lunch.
Topic: #33-SSRS 2008 R2 - Indicators, Sparklines and Databars
SQL Server Reporting Services R2 introduced several new features. In this lunch Patrick LeBlanc will provide three demonstrations that will show you how to create Indicators, Databars and Sparklines on your reports.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

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

MDX Puzzle #7

I have presented you with a couple of easy puzzles in the last few posts.  So, let's step it up a little with one that is a little more challenging.  I have to be honest, this puzzle was sent to me by one of my co-workers at Pragmatic Works.  Here are the requirements:

Columns:  Reseller Sales Amount and using the Source Currency Code add a column to the result that displays US if the currency code is USD and International for all other currency codes.

Rows:  Country and State-Province

Filters:  None

Hint:  You may need to use a CASE statement or the IIF function.

Your results should show Reseller Sales Amount by Country and State-Province.  An additional column will also be included that displays the currency type.  Be careful when designing your tuple for the rows.  You may notice that I have intentionally omitted one small nugget of information in the requirements.  Your results should resemble the following:

Notice the additional column that corresponds to the currency code.  Remember, don't post your solution here.  Save them for my solution post.  I will post it along with the steps that was taken to solve this puzzle in a couple of days. 

Don’t forget to check back in a couple days for the solution.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

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

MDX Puzzle #6 - Solution

Puzzle #6 should not have presented too much of a challenge, it's intentions were to introduction you to the ORDER function that is available in MDX.  Ok, so this is what I started with:

This is a very simple statement that satisfies most of the puzzle requirements.  The only one that is does not satisfy is the ordering of the results.  To do this I used the ORDER function.  The function accepts two arguments.  The first is a valid expression that returns a set.  In this example it is the set of Product Subcategories.  The next argument is the numeric or string expression, which will be used to determine the order.  Finally, you must include the direction that your want to order by, DESC or ASC.  There are a couple of more, but I am saving them for a later puzzle. 

Modifying the above query to include the ORDER function yields the result to this puzzle.  See the following:

Stay tuned for Puzzle #7, I promise it will be a little more challenging.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

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

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

MDX Puzzle #6

As we continue with the puzzles, I will continue to introduce new MDX functions.  In this puzzle the requirements are as follows:

Columns:  Reseller Sales Amount

Rows:  Product Subcategories

Filters:  None

Hints:  This puzzle is too easy for a hint. ;)

Ahhhh, but there is one additional requirement, the resulting rows should be returned in DESC order by Reseller Sales Amount.  Remember, don't post your solution here.  Save them for my solution post.  I will post it along with the steps that was taken to solve this puzzle in a couple of days.  Here is a screen shot of what the results should look like:

Don’t forget to check back in a couple days for the solution.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

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

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

SQL Saturday #28 - Lessons Learned

After spending a week in Tampa, FL at a client site I've had some time to unwind and reflect back on SQL Saturday #28.  I have received some really positive feedback from all individuals involved.  While I can't say that every aspect of the event was excellent, I can say that compared to last years event this one far exceeded any of my expectations. I have read several blog posting about the event and I have to issue one correction, most of you are reporting our attendance as 350 attendees.  That is a little off.  After reviewing our attendance roster our final tally was 397 signed in attendees.  I know this is not a numbers game, but for little Baton Rouge, LA this is a big number.  Ok, now that I have that out of the way let's talk about a few lessons we learned from the event.

1. More Signs on Campus: Our event was held on the beautiful campus LSU.  If you are familiar with the campus finding the location is a easy.  On the other hand, if you are not then you can get lost easily.  We only had signs on the main streets into and off of campus.  At the next event we need to add signs from the event hotel directly to the event.  Even though the event and the hotel are located on the campus, the hotel is a bit of a hike from the event.  You can easily get lost.  I am sure a couple of our speakers can attest to that. In addition, we will include more signs on the the main streets and at various locations leading up to campus.  This is one aspect that tends to be overlooked, but after this event I realized that this is a vital component to a successful event.

2.  Physical Address:  Having the event on a very large college campus can present a challenge when attempting to map out a physical address.  This can be attributed to the fact that building on campuses change names, which can cause a problem as we found out.  I thought that we had the correct address, but most GPS devices could not locate the address.  As a result, we have decided to include Longitude and Latitude coordinates in our correspondence next year.

3.  Air Conditioning:  Everyone knows that it is HOT in Louisiana, so to prevent any complaints about the heat we coordinated with the facility to ensure that the AC was on all night the day before the event and all day the day of the event.  Even with that, it was still warm in some of the rooms.  We are currently looking for ways to solve this problem.  Nothing yet, but we have about 359 days until our next event.  I am sure we will come up with something.

4.  Food:  Now, this is a touchy topic for me, since we did not charge for Lunch.  I read a blog post about SQL Saturday #28 that started out very positive, but ended really bad.  The bad part of the blog was in regards to the food that we served.  How could food be bad if it is FREE?  For breakfast we served, donuts, muffins, coffee, water and soda.  This is typical for most of the SQL Saturday's that I have attended.  In addition to those items I have seen fruit, but with our humid and hot environment we voted against the fruit.  This was the last thing that I thought anyone would complain about, but our breakfast was described as horrendous.  Wow, such a harsh word.  I will say this, if you plan on attending next year, the only thing that we will change about breakfast is to buy more of the same because there wasn't a donut or muffin left.  For lunch we had a boxed Subway lunch, which is another typical item served during SQL Saturday's.  Maybe not Subway, but the boxed lunch with a sandwich, chips and cookie.  Everyone who was there when lunch was served received a FREE lunch.  We do plan on upgrading lunch next year, but we are only upgrading the lunch provider not the type of lunch.  A box lunch is convenient for our lunch team.  You can easily distribute them and the garbage collection is quick.  I don't recall what the post stated directly about the lunch, but I do remember a comment about the lackluster thought that was put into it.  In the afternoon, we supplied a snack of cookies and crackers, which is not a typical SQL Saturday practice.  Since the lunch is small, we decided to provide a little extra snack to all those that decided to stick around all day.  Not sure where to complain about any of the items, but since there was a complaint it has to be labeled as a bad.  We are going to discuss our food choices and there maybe some slight changes, but nothing too drastic.  We are on a budget, and I believe that charging for lunch is a deterrent to potential attendees.  As a result, we provide a low-cost, but quality lunch to our attendees.  Two years in a row and only 1 complaint about lunch that I know of, I would say those are good numbers.

5.  Too much .Net:  A lot of the SQL folk stressed their concern about the 4 .Net tracks at our SQL Saturday.  I have to be honest, initially I was concerned about this myself, but after the day started and I walked around and visited each room as the classes were in session I realized that there were plenty attendees to go around.  I was concerned that some of the SQL sessions would have fewer attendees.  I was wrong.  The attendance across all sessions appeared to be evenly distributed.  I also enjoyed the banter between the Devs and DBAs.  Our attendance roster was almost evenly distributed between the two.  I even noticed some of the developers in the SQL sessions and some the DBAs in the developers session.  Maybe we can learn something from each other.

6.  Schedule:  I have to be honest we could have done a much better job on our Event Schedule handouts.  This was probably the most commented about thing at the event, in regards to "Needs Improvement".  We printed a one sheet schedule with the event diagram on one side and he schedule on the other.  Since we had nine tracks it was difficult to fit them all on a regular sheet.  Therefore, we decided to print the schedule on a legal sheet of paper.  On top of that, we decided to fold the sheet and put it in the event bag.  Big MISTAKE!.  The folded sheet was lost in our event bag.  Two things we are going to change.  First, instead of placing the schedule in the bag, it's going to be handed to each person.  Second, we are going to mimic what the guys in Dallas did.  We are going to create an Event Guide that will contain a very detailed schedule of the days events. 

Wow.  I did not intend on writing that much about lunch, but I just couldn't stop.  Please note that I enjoy constructive criticism about anything I do because it only makes me do it better the next time.  Thanks to everyone that has provided really good points, we have some really good documentation and I promise that they all will be addressed.  As with any event there is going to be some good and some bad.  Overall, I have to say that we did pretty good.  Stay tuned, I will be sharing more information about the event.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

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

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

SQL Reporting Services - Line Graph with a Vertical Bar

So, I was recently asked if you could display a vertical bar on a line graph to denote the last day of the month.  Initially, I had no clue how to solve this problem.  I talked to a couple of people and searched the web, but came up empty.  I decided to just dive into a report and solve the problem on my own.  So I started with the following query that can be run in the AdventureWorks2008 database:

SELECT 

    st.CountryRegionCode,

    CAST(sod.OrderDate AS DATE),

    SUM(sod.TotalDue) TotalDue,

    NULL Marker

FROM Sales.SalesOrderHeader sod

INNER JOIN Sales.SalesTerritory st

    ON sod.TerritoryID = st.TerritoryID

WHERE

    sod.OrderDate BETWEEN '4/28/2003' AND '5/03/2003' AND

    st.CountryRegionCode NOT IN ('US', 'CA')

Note that there is a column labeled Marker that currently has a value of NULL.  It will be explained later in this post.  This query was the basis for this Line Graph:

The RED vertical line you see in the graph is just an edit of the image, but this is the expected result.  I have to admit my solution to this problem is somewhat of a hack, but I think it will work.  The first thing i did was append a UNION to the query that would add an additional row which would be used to help create the vertical bar on the line graph. Since the initial query included four columns the UNION had to include four columns.  The only two columns that required values are the OrderDate and the Marker.  In this example, I hard-coded the OrderDate to the last day in April.  You can modify it so that it is a parameter that is selected by the end-user.  The second is a value or marker that will be used to determine the height of the vertical line.  Slight modifications can be made to query to ensure that the value is consistent with the results of the query.  For example, you could select the max from the result and increase it a little to further enhance the visualization.  In this case, since I know the maximum is 300,000, I set it to 350,000.

What does the Union accomplish?  This is done to ensure that the Marker value is visible or placed only on the last day of the month.  I know what you are thinking, this is not going to work.  That is what I thought.  To create the vertical line I completed the following steps:

1.  Added the Marker to the Values section of the Report

2.  Next right-click the Marker value and selected Change Chart Type. Choose the first column Graph from the column section.

3.  Now right-click on the bars on the graph and selected Series Properties.  Choose Fill from the navigation pane and choose Red from the Pick color section.

4.  With the bars selected on the graph open the properties windows.  Then expand the Custom Attributes property. Finally change the PointWidth value to .2.  This changes the sizes of the bar.

Now run the report.  Your report should resemble the following:

You will see a single vertical bar whose point on the x axis is the last date of April and whose value is 350,000.  This line is a bar graph that has been customized to resemble a vertical line.  I am not saying that this is a perfect solution, but it appears to work.  If you have any other ideas or methods that may solve this problem please post your comments here.  Also, feel free to email me at pleblanc@pragmaticworks.com.

There are several ways to accomplish this, but this solutions does work.  Stay tuned for Puzzle #6.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

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

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

MDX Puzzle #5 - Solution

Again I want to apologize for the slow posting of the solution to this puzzle, but I have been hard at work on SQL Saturday #28.  Fortunately, I carved out some time to write it up.  The solution to this puzzle could have been accomplished a couple of ways.  I chose to use the SUM and YTD MDX functions, but before I discuss these functions I will start with the basic query, which satisfies these requirements:

1.  Internet Sales Amount as a Column

2.  Delivery Date Calendar Month as a Row

3.  Applies a filter to limit the rows from the Delivery Date of January 2006 to December 2006.

SELECT

    NON EMPTY(

        {

            [Measures].[Internet Sales Amount]

        }

    )ON COLUMNS,

     [Delivery Date].[Calendar].[Month].&[2006]&[1]:[Delivery Date].[Calendar].[Month].&[2006]&[12]ON ROWS

FROM [Adventure Works]

Next I created a calculated member whose purpose is to return the YTD or Running Total.  To accomplish this I coupled the SUM and YTD functions.  See below:

WITH MEMBER Measures.[YTD Internet Sales]

AS

SUM (YTD([Delivery Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount])

Using the YTD function I was able to obtain a set of members from the same level as the given member, which in this case was the [Delivery Date].[Calendar].CurrentMember.  I then used the SUM function to accurately calculate the YTD aggregations for the [Internet Sales Amount] measure.  The solution to the puzzle should resemble this:

WITH MEMBER Measures.[YTD Internet Sales]

AS

SUM (YTD([Delivery Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount])

SELECT

    NON EMPTY(

        {

            [Measures].[Internet Sales Amount],

            Measures.[YTD Internet Sales]

        }

    )ON COLUMNS,

    [Delivery Date].[Calendar].[Month].&[2006]&[1]:[Delivery Date].[Calendar].[Month].&[2006]&[12]ON ROWS

FROM [Adventure Works]

There are several ways to accomplish this, but this solutions does work.  Stay tuned for Puzzle #6.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

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

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

SQL lunch #28-SSIS Design Patterns 1a: Introduction to SSIS Design Patterns

In this three part series SQL Server MVP Andy Leonard will discuss SSIS Design Patterns.

Speaker: Andy Leonard

Add To Outlook: Add To Calendar

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

Date and Time: 8/9/2010 11:30:00 AM CST

Topic: #28-SSIS Design Patterns 1a: Introduction to SSIS Design Patterns
This is part one of a three part series.
A brief introduction to the concept and usage of design patterns and a demonstration of the Counts Acquisition in ETL Instrumentation pattern.

BIO: Andy Leonard is an Architect with Molina Medicaid Solutions, SQL Server database and Integration Services developer, SQL Server MVP, PASS Regional Mentor, and engineer. He is a co-author of Professional SQL Server 2005 Integration Services and SQL Server MVP Deep Dives. Andy blogs for SQLBlog.com. His background includes web application architecture and development, VB, and ASP; SQL Server Integration Services (SSIS); data warehouse development using SQL Server 2000, 2005 and 2008.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

Founder

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

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

SQL Saturday #28 Baton Rouge Update

We are just about a week away from probably the biggest FREE training event to ever take place in Baton Rouge, LA.  Heck this is probably the biggest FREE technology event in Louisiana.  I really don't know.  Nevertheless, since it is almost here I wanted to encourage any one that has not already signed up to do so now.  We are well over 500 registered attendees and that number is increasing daily.  If you are not sure whether to attend or not here are a few highlights:

• 53 sessions, including 4 early morning beginner sessions and an all day .Net track
• Ask the Experts, where you can chat with some of the top SQL Server and .Net professionals in the country
• Win an MSDN subscription
• Win a Netbook
• Free Breakfast and Lunch
• Networking, Networking, Networking
• Did I forget to to mention all the FREE training

If you still are not convinced take a look at our schedule.  I hope to see you all there and if you are already registered please continue to spread the word. 

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

Founder

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

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

SQL Saturday #28 Announces: Ask the Expert

Do you have a challenging SQL Server or .Net issue that is causing you to have sleepless nights? Well, we have convinced 12 of our speakers (Experts) to allocate an additional hour of their day strictly for answering questions. These are some of the top SQL Server and .NET guys in the industry. So, if you have any questions bring them to SQL Saturday #28 Baton Rouge. Here is the schedule.

Time Slot	Speakers	Topics
9:00 am – 10:00 am	Chris Eargle and Chander Shekhar Dhall	Software Development, Application, Performance Tuning, Software Development Architecture
10:15 am – 11:15 am	Kevin Boles and Sean McCowen	Power Shell, Storage, Performance Tuning, SQL Server
11:30 am – 12:30 pm	Barry Ralston and Bryan Smith	SQL Server Analysis Services, MDX, Data Warehousing
1:30 pm – 2:30 pm	Denny Cherry and Steve Jones	SQL Server Storage, Performance Tuning
2:45 pm – 3:45 pm	Tim Mitchell and Steve Simon	SSIS, ETL, Data Warehousing
4:00 pm – 5:00 pm	Eli Weinstock-Herman and Drew Minkin	Data Modeling, Data Mining

These sessions will run concurrent with all of the other sessions. So plan accordingly. See you all at SQL Saturday #28 Baton Rouge.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

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

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

MDX Puzzle #5

Writing YTD totals and Running totals using T-SQL can take a little effort.  However, with MDX it's not too difficult.  In this puzzle I will write an MDX statement that produces a monthly YTD or Running total.  Here are the requirements:

Columns:  Monthly Running Total (Calculation), Internet Sales Amount

Rows:  Delivery Date Calendar Month

Filters:  Delivery Date from January 2006 to December 2006

Hints:  You may need to create a scope calculation (WITH MEMBER), use the YTD and SUM functions

Remember, don't post your solution here.  Save them for my solution post.  I will post it along with the steps that was taken to solve this puzzle in a couple of days.  Here is a screen shot of what the results should look like:

Don’t forget to check back in a couple days for the solution.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

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

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