SQL Saturday Dallas Top Ten(ish)

So for those of you who attended SQL Saturday Dallas this past weekend, here’s my Top Ten(ish) list from the event:

  • Getting lost in the construction zone on Spring Valley on the way
  • A thousand Quest splat balls
  • Competing with SQL Saturday Portland and Indy Tech Fest on the same day – but knowing that ours was the biggest, because everything is bigger in Texas.
  • Monitoring the Twitter feed to keep tabs on everyone else.
  • Lunch outside in the courtyard (ah, the Texas summer sun…)
  • The elevator in which time comes to a standstill when moving 12 feet up.
  • Ice cream (thought the long line… not so much Top Ten)
  • Ordering a round of Buttfaces at Humperdink’s at the after party
  • Live taping of MidnightDBA episode
  • Crossing Centennial street without becoming a hood ornament
  • Joe Celko presenting
  • Joe Celko drinking
  • Knowing we’ll do it all again next year!

It’s Done – SQLSat35

This is a strange feeling.  No frantic deadlines.  No daily conference calls with the team.  No hurried trips to the printers, no sessions to juggle around, no all-day Saturday work days coming up.  My kids recognize me again.  I know what my house looks like in daylight.

More importantly, I get to resurrect my neglected blog. :)

SQL Saturday Dallas was held this weekend, with over 400 attendees, speakers, volunteers, and vendors in the house.  We had 42 sessions, plus several lunchtime presentations by our sponsors, and a live taping of a MidnightDBA episode.  This event was almost a year in the making, headed up by the leadership of the North Texas SQL Server User Group and involving more than 40 volunteers before and during the event.  It was a good day all around – food was great, the sessions were excellent, and, as fellow committee member Dave Stein pointed out, “nobody died”.

I’m working on a comprehensive postmortem to post later in the week, but for now, I want to say a word of thanks to all of the speakers, volunteers, vendors, and attendees who were a part of this event.

PASS BI Virtual Chapter Webcast: Extract, Transform, and Load your Data Warehouse

Everyone is invited to a webcast hosted by the PASS Business Intelligence virtual chapter this Friday, May 14th, as we welcome SQL Server MVP Jessica Moss as she presents “Extract, Transform, and Load your Data Warehouse”.  We’ll start at noon EDT and will run for about an hour, so grab your lunch beforehand and enjoy this virtual lunch-and-learn opportunity.

The full session description and link to the LiveMeeting site can be found here.  For more information about the PASS Business Intelligence virtual chapter, visit our website.

Also, coming up next week, don’t miss Brian Knight’s presentation on the new features of SQL Server 2008 R2 for BI developers.

Report Parameter order in SSRS

Is the order of parameters important in SQL Server Reporting Services reports?  If you’ve got nested parameters (parameters that derive their value from one or more other parameters), it is very important!

Using a trivial report example, I’m going to demonstrate how the order of parameters matters a great deal when those parameters interact with one another.  For this example, I’ll to create a simple sales report in SSRS against the AdventureWorks database to retrieve a list of our highest revenue-generating customers in descending order of revenue.  The report will include a parameter to allow the user to select the number of customers to be displayed on the report.

I’ll start off by creating the report in BIDS, and will add the two parameters:

params

The first parameter will control the number of customer records to be displayed, and I’ll set this to be a static list of possible values (in our case, either 10, 25, or 50 records).  The second parameter will contain the SQL query to be sent to the database, and will be partially derived from the first by including in the query the number of records to be returned:

="SELECT TOP " & Parameters!NumCust.Value.ToString() & " Person.LastName " &
" + ‘,’ + Person.FirstName [CustomerName], Customer.CustomerID, " &
" SUM(SalesOrderHeader.SubTotal) AS TotalSales FROM Sales.SalesOrderHeader " &
" INNER JOIN Sales.Customer ON Sales.SalesOrderHeader.CustomerID = " &
" Sales.Customer.CustomerID INNER JOIN Person.Person ON Person.BusinessEntityID " &
" = Customer.PersonID GROUP BY Person.LastName + ‘,’ + Person.FirstName, " &
" Customer.CustomerID ORDER BY TotalSales DESC"

After adding the three output fields to the data set, I should be able to execute this query and retrieve the expected results.

report

No problems at all.  However, let’s say that I had created the parameters in the reverse order, such that the one containing the query appears before the parameter specifying the number of records:

paramsreverse

Now when I attempt to execute this report, I receive a very generic error message:

reporterror

So obviously, the order of parameters is critical if the value of one of them is based on another.  Fortunately, it’s very easy to change the order of parameters after they have been created using the positional up/down arrows just above the Report Data workspace:

arrows

Since the resulting error message didn’t provide much information for troubleshooting, this type of problem could be difficult to track down, especially if there are a lot of parameters in the report.  Hopefully, this reminder will help someone avoid spending a lot of time tracking down parameter order problems.