Hitachi Consulting’s Announcement About by MVP Award

Being named an MVP this year is very exciting news for me.  As I was trying to come up with the right words for a post, Stacey Toevs, who maintains our corporate Intranet site created this feature article for a series and internal Hitachi Consulting initiative called “Be Known”.  I thought I’d share it with you here…

 Paul Photo small

Microsoft’s Newest Most Valued Professional

A rare honor was given to Paul Turley October 1: He was named Microsoft’s MVP in the SQL Server domain. The Microsoft MVP Award is given annually to recognize exceptional technology community leaders worldwide who actively share their high quality, real-world expertise with users and Microsoft. With fewer than 5,000 awardees worldwide, Microsoft MVPs represent a highly select group of experts and the diversity of today’s technical communities. “I believe there are only a handful of MVPs worldwide on the Microsoft SSRS reporting platform,” says Hillary Feier VP on the high tech products team. “This is a tremendous accomplishment, and having an MVP on our team demonstrates to Microsoft our commitment and expertise on their platform.” Paul has a very active blog that illustrates his willingness to share his expertise and help others – a key MVP requirement. “MVPs share a passion for technology, a willingness to help others, and a commitment to community,” says Microsoft’s head of customer and partner advocacy Richard Kaplan. “These are the qualities that make MVPs exceptional community leaders. By sharing their knowledge and experiences and providing objective feedback, they help people solve problems and discover new capabilities every day. MVPs are technology’s best and brightest, and we are honored to welcome Paul as one of them.”

It hardly makes sense to attempt to improve the thoughts of an all-star blogger, so we reproduce here some of Paul’s thoughts on blogging. For starters, Paul sees blogging as a natural progression in his career: “For me blogging is the next stage of technical community involvement. In the early 1990s, I was an independent consultant working at HP. I found myself doing a lot of Access and VB projects, where I got to know Access really well. To find answers, I would look on the public support forums and newsgroups, and while I was looking for answers to my questions, I read questions from others and realized that I had answers for them. Before long, I was spending more time answering questions and helping others purely in the spirit of service. After a couple of years, I guess I became somewhat of an authority on Access and was contacted through a forum to write a couple of chapters in a Wrox Press book. Nine years later, I’ve contributed to ten books. I started speaking at the SQL Server PASS global summit because I could attend the conference for free. Now I just enjoy being involved.

“Blogging is great fun, and it’s good for the core muscles – no, wait, that’s Pilates… I get confused. I’ve started and stopped blogging in the past but I didn’t really have a forum or a good reason to keep going. Hilary Feier challenged me to blog regularly and this gave me the traction to reach critical mass. Since March, I’ve had 3,330 visitors from 92 countries and I get regular email, which motivates me to keep at it.

“To come up with a solution for a technical problem, sometimes I just need to let it bake for a while and later some creative solution will just hit me. When someone asks me a question at a client site, in a training class or at a conference; I often tuck these things away in my mind. I love to help people but I can’t be responsible for fixing everyone’s problems. Instead of promising to get back to an individual and then forgetting, when I get a chance I’ll post a blog with the solution. I get a sense of closure because I was able to resolve some nagging problem but I didn’t have to meet a deadline or remember who to get back to.

“I get a lot of questions from people, mostly from technical people in the trenches – and a lot of them are from our clients and prospects. I’m not the only one at Hitachi that writes books or speaks at conferences. When I go to a client site, I see our books all over. When I go to a sales call with one of our DBDs, I often hear people say ‘I read your blog’ or ‘I read an article from one of your associates.’ They’ve already heard about us from multiple sources, and I think this gives us credibility over another consulting company they’re talking to.

“Like my Access forums work in the ’90s, I’ve spent a lot of free time more recently posting answers in the Microsoft Development Network (MSDN) support forums. Before long, they made me a forum moderator and then received the MVP designation. In the past five years, I’ve been focusing my energy on SQL Server Reporting Services and BI. It’s important to build relationships. When we were working on the first Reporting Services book in 2003, I took the Microsoft product team leads out to lunch. Now I can pick up the phone or get email replies directly from the developers. I do a lot of this off of company time but when I write, present at a user group or speak at a conference; I always make a point to let people know who I work for. I’m there because of the smart people I surround myself with. You can’t volunteer because it will get you noticed or promoted. You have to do the right thing for the right reason. But, in making the effort, good things happen.”

Dynamically Adding and Removing Columns to a Query and Report

Creating one report to meet multiple business requirements can save a lot of work down the road. A common scenario is for different users to ask for similar reporting metrics that may just be variations of existing reports. Using SQL Server Reporting Services, you can achieve this goal with a little planning and creative report design.

The following technique in Reporting Services for SQL Server 2008 uses a parameterized expression to modify a dataset so that it returns a different set of columns. Conditional logic in the report is used to include only columns in a table which correspond to the columns returned by the query. The same technique will work in earlier versions of Reporting Services but the designer user interface will be a little different.

This example uses a parameter to change the stored procedure that will be executed to return the report data. Conditional query logic could also be applied to generate a dynamic SQL or MDX statement.

To begin, use SQL Server Management Studio to connect to the AdventureWorksDW2008 sample database and then execute the following script to create two new stored procedures:

create proc spResellerSales1
    @DateFrom    int,
    @DateTo    int
as
    select
        d.CalendarYear, d.CalendarQuarter, d.EnglishMonthName as Month
        , sum(fs.OrderQuantity) as OrderQuantity
        , sum(fs.SalesAmount) as SalesAmount
    from
        FactResellerSales fs inner join DimDate d on fs.OrderDateKey = d.DateKey
        inner join DimProduct p on fs.ProductKey = p.ProductKey
        inner join DimProductSubcategory ps
on p.ProductSubcategoryKey = ps.ProductSubcategoryKey
        inner join DimProductCategory pc
on ps.ProductCategoryKey = pc.ProductCategoryKey
    where d.DateKey between @DateFrom and @DateTo
    group by d.CalendarYear, d.CalendarQuarter, d.EnglishMonthName
, d.MonthNumberOfYear
    order by d.CalendarYear, d.CalendarQuarter, d.MonthNumberOfYear
;
go
—————————-
create proc spResellerSales2
    @DateFrom    int,
    @DateTo    int
as
    select
        d.CalendarYear, d.EnglishMonthName as Month
        , sum(fs.OrderQuantity) as OrderQuantity
, sum(fs.SalesAmount) as SalesAmount
    from
        FactResellerSales fs inner join DimDate d on fs.OrderDateKey = d.DateKey
        inner join DimProduct p on fs.ProductKey = p.ProductKey
        inner join DimProductSubcategory ps
on p.ProductSubcategoryKey = ps.ProductSubcategoryKey
        inner join DimProductCategory pc
on ps.ProductCategoryKey = pc.ProductCategoryKey
    where d.DateKey between @DateFrom and @DateTo
    group by d.CalendarYear, d.EnglishMonthName, d.MonthNumberOfYear
    order by d.CalendarYear, d.MonthNumberOfYear
;
go

Note that the only significant difference between the spResellerSales1 and spResellerSales2 stored procedures is that the first one returns the CalendarQuarter column and the second one does not. Remember that this is a simple example for the sake of demonstration. You could have as many different procedures or query variations as you like as long as one of them returns all possible columns and the others return a subset of columns in the same order.

The report contains parameters, two that will be automatically generated from the DateFrom and DateTo parameters in the procedure, and another parameter named DataSource that I added myself. Note that I’ve provided default values for the DateFrom and DateTo parameters since they’re really not part of the demonstration scenario.

To populate the dataset Fields collection, configure the dataset to use a stored procedure and select the first procedure (the one that returns all of the columns) as you normally would without using an expression. The DataSource parameter (not to be confused with a report data source) is use to pass and swap the stored procedure names to the report’s main dataset. Manually add this parameter to the report:

The available values for this parameter include the two stored procedure names:

This parameter is referenced in the report’s main dataset using the expression: =Parameters!DataSource.Value

A table is added to the report and each dataset field is added as a column to the table:

Since the CalendarQuarter column may not be available when the spResellerSales2 stored procedure is used in the parameterized dataset, this column must be hidden when the column is not returned in the result set. This is accomplished by changing the Hidden property of the table column. Right-click the column header for the Calendar Quarter column and choose Column Visibility… from the menu. Choose Show or hide based on an expression and then click the expression button to use the Expression Builder to create the following expression for this property: =Fields!CalendarQuarter.IsMissing.

Remember that there is a difference between a report dataset and the query that it references. When a query is written, (or in this case, when a stored procedure is selected) field definitions are added to the dataset object and will be there whether the query returns a column or not. If that query doesn’t return a corresponding column for the field, the field’s IsMissing property returns True.

You’re all done!

Preview the report and choose the first stored procedure in the parameter list. The report returns columns for all fields, including the Calendar Quarter:

Change the Data Source parameter to use the second stored procedure and click the View report button on the toolbar. Now the report returns columns for all fields except the Calendar Quarter:

A copy of this sample SSRS 2008 report is included for download.

 Dynamic Columns.rdl (351.00 bytes)