Programmer's Edge

Well, I promised the XRM Virtual Users Group ( website | twitter ) and Julie Yack ( blog | twitter ) that I would provide them a blog of an example of using the Lookup feature in SQL Server Reporting Services 2008 R2….well, that time has come. I will throw out the caveat that I have been ill all day and am now under the heavy influence of, my friend, Mr. Nyquil. So if the post seems kind of disjointed then you will understand why.

So to start out with I am going to just create a simple Reporting Services project with a shared data source to the AdventureWorksLT database. Nothing fancy here.

However, please note that I could have 2 separate data sources pointing to 2 totally different databases. I am just trying to keep things simple here for demonstration purposes.

So now let’s create a report with a couple of datasets in it. We are going to pretend that we have Sales information in one database while we would have all of our Customer information in another database. So I will create one dataset for Sales based off of the SalesLT.SalesOrderHeader table and the other one of Customer details off of the SalesLT.Customer and associated tables. Nothing crazy going on here.

So now the problem is that since the data for the customers is in a totally separate dataset …How do we relate the two in order to remove CustomerID from our table and replace it with say the customer’s company name? That’s where the Lookup function comes in. I will add a column on the left of my report and set the header to Company. Next I will delete my CustomerID column from the report since I won’t need that anymore. Lastly, I add a lookup function as an expression for the field under the Company column. This is where it gets kind of tricky….

=Lookup(Fields!CustomerID.Value,Fields!CustomerID.Value,
Fields!CompanyName.Value,"Customers")

So in order to use the Lookup function I need to pass it four things.

1. The value from the original dataset that is going to be used as the left side of the match.
2. The value from the second dataset the is going to be the right side of the match. This is the tricky part because when you look at the two parameters they look to be the same. You just have to  remember that the field references are named the same in each dataset ..SSRS knows which one it is supposed to be looking in.
3. The value from the second dataset that needs to be pulled out.
4. The name of the second dataset where all this lookee upee stuff is supposed to be going on.

Now if we run our report we can see that everything turned out exactly as it should have.

Now it should be stated that I could have used an expression for any of the values passed as parameters to the function. This allows us to do matching on things like complex keys(ie more than one column to denote a match) or bringing back complex values…like the full name of the customer.

It should also be noted that there are  two other Lookup functions: LookupSet and MultiLookup that return arrays of matching values. I will try to detail those in a follow up blog post.

I am including the sample project below to help you out .

Sample Reports

Cheers!
Aj

The webcast recording is up for my presentation to the XRM Virtual User’s Group on SSRS 2008 R2 in CRM. In it I go over some of the new features as well as some gotchas for people looking to upgrade to the newer version. 

http://www.xrmvirtual.com/events/ssrs_crm

Thanks to Julie Yack et all for putting everything together!

Cheers,
AJ

That’s right people. I am heading out on the road once again for a SQL Saturday. This time it will be SQL Saturday 50 in Iowa City, IA on Sept 18th, 2010. I hear tell that there will be an awesome lineup of speakers available to get you all ‘learned up’ on the latest and greatest techniques for SQL Server.

I myself will be presenting two sessions at least on the following:

Monitoring Data Changes the Microsoft Way with CDC

Most modern enterprise database environments require some measure of auditing their data. Previously, the DBA would need to put together a complicated web of triggers and history tables in order to properly track data changes. Now in SQL Server 2008, Change Data Capture allows the DBA to readily enact a very straight-forward method for tracking the changes and understanding easily how they took place. In this session, we will examine how to set-up, configure, use, and administer the Change Data Capture process in your environment.

Take Control With Resource Governor and PBM

A large majority of a modern DBA’s workload consists of managing their policies and resources on their system in order to ensure that they are being followed and resources are accessible when needed. SQL Server 2008 provides both Policy Based Management(PBM) and Resource Governor, which allows the DBA to discretely manage system policies and resources in a very structured way. In this session, we will show you how to configure, monitor, and administer these features to help lower your anxiety levels while increasing your system’s availability.

Plus, this SQL Saturday looks like it will be featuring a contingent of female speakers such as Kendra Little , Kathi Kellenberger, and Wendy Pastrick ! Even Danika Patrick can’t hold a SELECT statement to this group of DB savvy professionals! Do I exaggerate? I think not! Come see what all the hype if about by signing up

http://www.sqlsaturday.com/50/eventhome.aspx

Cheers!
AJ

Well, another successful SQL Saturday has been logged in the books. I had a single session for this one for Change Data Capture and went out of my way to ensure that it was crammed packed with information for the people attending….even to the point that when the volunteer monitor in the room exclaimed  “I guess you know you have less than 10 minutes left.” . I politely replied

I possess an advanced degree in

Computation Physics.

I am the Master of Space and Time.

For those of you that would like to see the presentation along with the script files you can pick them up here.

So now I would like to share some of my thoughts on how it all went. First off, what was great was that I received the feedback from my attendees before then end of the DAY! Yep, that’s right! Not a week later ….the same DAY! Just to let you know, I believe this is the first SQL Saturday that I have actually received my speaker feedback at all. How cool is that! For those of you that are wondering, here are the ole’ resultos:

• 5 (Excellent)  =  57.14 %
• 4                        =  35.57 %
• 3                        =  7.14 %
• 2                        = 0 %
• 1(You suck)    =  0 %

So overall, really good. I only had 1 person give me less than a 4 but since I will be giving the presentation again in SQL Saturday #50 East Iowa, I will brush it up a little to see if I can max it out.

Now on to some specific areas of the event that those  of you planning or thinking about planning can take to heart.

Slide Templates

The 2 sets of slide templates that were provided to us prior to the event were great. I think that the other SQL Saturday’s should definitely follow suite. Maybe even put forth the effort to have one of the peeps in you local group brand them specifically for you event. Here are some examples to show you what I mean:

You win over a lot of IT people when you show Sneaky Ninja DBA Tips ala Naruto!

Event Venue

The venue was great. I talked with Kevin Kline and Joe Webb and they saved a lot of money by having the event at Nashville State Community College. The rooms were more than adequate in size and had all of the AV equipment needed. They even had one of the college technicians on hand to address any of the difficulties we had. I know of at least a couple of instances where that came in handy.

Sponsor Area

This was one of the best setups for the sponsors that I have been at for a non-paid user group event EVER! We each had a a seperate area along the main drag with not only a extra long table but also a couple of comfy chairs with small tables. If we wanted to we could have pulled people in to have in depth conversations or show them something else that they were wanted to ask questions on. Definitely not the cramped quarters off in a separate room that I am used to.

I know that these events are for the users but we have to make sure that there is something in it for the sponsors because they are ultimately the ones that are fronting the money to put these on. I am lucky enough that my employer wants me to go and speak at these and they also frequently pony up the money for sponsorships.

Women in Technology

Who woulda thunk it? But there are women in IT. All kidding aside, there were three high caliber women presenters in attendance. Hopefully, this is a continually growing trend as I believe this is a good thing for the community. I will probably be writing more on this in the future.

Volunteers

Not only did this event have a great cast of volunteer help but it was executed flawlessly. You had a volunteer in the room with you while your talk was going to do things like…get you water, keep track of time, address issues with AV, collect the feedback as people leave. This not only ensured that everything ran smoothly but that the feedback was collected from everyone. What a great idea!

Overall

Overall, I give this event an A+! Big kudos go out to Kevin Kline, Joe Webb, Christina Leo, Louis Davidson  and everyone else involved. I will definitely plan on coming down next year!

Cheers!

AJ

It’s true! Come Saturday September 25th ,2010 Perpetual Technologies, my parent company, is putting on an ALL DAY free training event for both Microsoft and Oracle people in the Indianapolis area! This is our 3rd annual event and has finally been moved to a Saturday so that more people from the adjoining areas(Cincinnati, Chicago, Ft. Wayne, Louisville, Evansville. etc.) can attend!

You can sign up to attend here: https://events.perptech.com

This year’s event is going to be sweet as it contains a SQL Server track, Developer track, Platform track, and an Oracle track with an awesome lineup of speakers such as:

Joe Carver  -  Microsoft

Hope Foley  – PTI

Eddie Wuerch  – Exact Target

Kyle Neier  -  Herff Jones

Robert Bogue  -  Thor Projects LLC   <—Yeah! That guy!

Arie Jones   -  PTI   <—Yeah! That @&^&@#!

Vance Upshaw   -  PTI

Dave Leininger  – DEFENDER Direct, Inc   <—Him too?

Scott Archer – VMWare

Erik Ives & Scott Singer  – Utility Financials Consulting

Chris Ruel  – PTI

Cheers!
AJ