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

Okay, so I will be presenting another webcast on SQL Server 2008 R2 Features….this time specifically with a slant towards CRM for the XRMVirtualUserGroup on Aug 26th,2010.

SSRS in CRM with Arie Jones

Join Arie Jones, Principal Technology Manager of Perptech, as he shares with us what’s new in SSRS 2008 R2. This session will cover reporting basics and will have some deeper technical content as well. System customizers, power user, DBAs, developers, technical users, numbers people (pretty much anyone USING or MAKING CRM) will learn a great deal in this session.

**** Please note the timezone below !!!

**** PST = Pacific Standard Time   PST <> Pretty Standard Time

Schedule:

• SSRS in CRM
  8/26/2010 9:00:00 AM (PST) – 8/26/2010 10:00:00 AM (PST)

You can Register here!

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

For those of you that don’t know the XRMVirtualUserGroup(http://www.xrmvirtual.com/ ) covers all things related to XRM(CRM) and is coordinated by the (in)famous Julie Yack(MVP) (http://julieyack.com/)  from an undisclosed location deep in the Colorado mountainside. The virtual group is fairly large I believe with over 1000 members internationally…….so you may be asking yourself why you haven’t joined yet??…….yes, it is because you are a noob so stop wasting time and just do it!

Cheers,
AJ

Well, our first PTI webcast is complete. Except for a few technical glitches everything turned out okay in the end for a first run through. Got a lot of emails post event thanking me for the presentation so it was well worth it. Now I just have to figure out what I will do for the next one! For those of you that missed it you can get the recorded video with the info below:

Recording Details
    Subject: Using the New Features in Reporting Services 2008 R2
    Recording URL: https://www.livemeeting.com/cc/perpetualtechnologies/view
    Recording ID: H7DN6M

Feel free to share this with your friends and make sure to send me some ideas on what webcasts you are looking for so I don’t have to think too hard:)

Cheers,
AJ

We are having our first webcast at PTI in the month of July. This month’s topic will be SSRS 2008 R2 so it should be filled with some very fun stuff. The last presentation I gave on this subject one guy told me that he hadn’t been this excited about reporting since RPG!

We’ll be making this at least a monthly thing so if you have any requests for topics then make sure you forward them to me!

Details are below!

Cheers!,

AJ

Join us as we offer an exciting addition to the PTI educational event format, and the first in our 2010 webinar series.
Using the New Features in Reporting Services 2008 R2

Presented by:
Arie "AJ" Jones

SQL Server Reporting Services has been around for a number of years now and the 2008 R2 release provides DBAs and Developers with a truly enterprise class reporting platform. If your company has not embraced Reporting Services fully yet then now is the time! Come see not only some of the new features that are available but also how you can leverage the environment to provide custom solutions to fit your extensive business needs.

Date
Tuesday
July 27, 2010

Time
11:00am EST
Register Today!

If you know someone who may be interested
in attending this webinar, please…………

About PTI
Perpetual Technologies, Inc. (PTI) a veteran-owned small business founded in 1997 and headquartered in Indianapolis Indiana; provides clients with unrivaled database management consulting services. With our base in the Midwest and our focus on remote services, PTI competes on a national basis by offering highly competitive rates that save our clients an average of 28%.  Our subject matter experts have extensive experience with all major database products, with a specialty focus on Oracle and Microsoft SQL Server.  In addition to the database services, our team of certified professionals provide software integration services, data warehouse design, business intelligence, custom application development, database design, capacity planning, performance tuning and disaster recovery planning. We focus on building relationships to understand a client’s needs, gaining confidence and trust, and developing a long-term partnership to deliver solutions that maximize business performance.