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

I don’t normally plug too many other blog posts here as I figure you can find them on your own if they are good….BUT Brent Ozar has an excellent blog post on

Why I’m Disappointed in the PASS Election Process

If you have the time, I would read it….it seriously had me rolling….

Cheers,
AJ

SQL Server has a very solid criteria for replication transactions to the subscribers. First it sends out a batch. If it completes then it goes on to the next one. If not, then it tries each of the transactions in the set individually to see if that corrects the problem. Once in a while, even this process fails and you have a hung transaction that keeps the process from completing and, of course, the replication process just doesn’t skip over automagically. So you stuck with troubleshooting…but it doesn’t have to be that bad. This is the process you should follow. First, you need to execute a couple of stored procedures against the distribution database.

sp_helpsubscriptionerrors

@publisher=’<publisher_name>’,@publisher_db=’<publisher_db>’,

@publication=’<publication_name>’,

@subscriber=’<subscriber_name>’,@subscriber_db=’<subscriber_db>’

This procedure will give you details about the error that occurred. More importantly it will provide you with the sequence no that you will need to get the command that tried to execute …..so that you can continue troubleshooting.

Now that we have found the sequence number we can use the following procedure to get the exact command that was executed.

EXECUTE distribution.dbo.sp_browsereplcmds

@xact_seqno_start = ’0x000734CA00003C4F000C00000000′,

@xact_seqno_end = ’0x000734CA00003C4F000C00000000′,

@publisher_database_id = 24, @command_id = 1

In the example above, you can provide either a single sequence no or a range of them. You can get the database id and command id from the previous command. Now you should be able to use the statement to actually execute against the subscriber database. Now you should be able to figure out what went wrong. At the very least, you should be able to perform the intended transaction through a SQL statement using the values. Once you have corrected the issue and applied the transaction or decided to skip it….you need a way to tell the replication process to skip that transaction. You will need the sequence no that you found in the previous statements and will need to issue one last statement before starting up the process again. This time, however, you need to execute it against the subscription database.

sp_setsubscriptionxactseqno  ‘<publisher_name>’,'<publication_db>’,

‘<publication_name>’,<sequence_no>

Hopefully, this helps you out when you get in a jamb.

Cheers!
AJ

Thanks to everyone whom attended the webcast. As promised, here are the sample files as well as the information so that you can download the webcast…if you should so choose.

Live Meeting Recording

Demo Files

Remember if you have any ideas for webcasts next month, then please feel free to drop me a line and we’ll see if we can get something on the schedule.

Cheers!
AJ