Reza Alirezaei’s Blog

Got the mail today with the great news that I got awarded for the forth time with the MVP Award. Looks like that my community works during 2008 were enough for Microsoft to renew my MVP status.

This is just an awesome way to start my Canada day!

Thread spMvp = new Thread(2009);
spMvp.Start();

Summary: Explore different options you have to work with structured data in a high volume while you need to perform complex queries and actions against such data ranging from authoring, approval and landing information on Web Part pages, all the way down to the physical storage. This blog post is part 1 of a blog post series that I am planning to write on this topic. (13 printed pages)Applies to: Microsoft Office SharePoint Server 2007See Also:

• Part 2 : Exposing LOB Data
• Part 3 : Browser enabled InfoPath Forms
• Part 4 : SharePoint Designer

Download this post in PDF version here.Content:

• Introduction
• Creating the Northwind Database
• Creating the Suppliers List
• Configuring SSO
• Additional Resources

Introduction: Data presentation is such a common requirement that it affects just about every layer of a platform on which you build your solution. Structured data must be stored somewhere (right?), so deciding where to physically store your data is just as important as the techniques you leverage to interact with it. Typically, when it comes to working with structured data in SharePoint, you have three options:

1) Keep all your data in a backend system and query it real time. In case this is the first thing that comes to you mind, then you are certainly among those who believe that SharePoint is not meant to be used as a database management system.

2) Keep all your data in SharePoint. In another word, you use SharePoint as your main data repository which means no dependency on any other extra data sources. Less deployment headaches, less configuration and easier maintenance.

3) Use a hybrid approach. It is all about keeping the balance between great features lists and document libraries offer in SharePoint and what database engines can bring to the table. This approach may or may not require some extra work to keep both data structures in sync.

Read more…

I was humbled when Stephen Ibaraki asked me if we could have a chat over the phone about myself,  collaboration and SharePoint. It really was a fun experience and I enjoyed talking to him and sharing some pointers. To listen to the interview, click on this MP3 file link.

More about this interview on Canadian IT Manager’s blog.

Here is the list of the SharePoint classes that I have documented on MSDN, Enjoy!

In Part 1, I gave you a quick tour on what needs to be set up before you can jump into data driven subscriptions in SharePoint. Now the real fun begins. From the ECB menu of a report choose Manage Subscription.

In the Manage Subscriptions page , click on the Add Data Driven Subscriptions button. This will take you to step 1 of 4 of setting up a data driven subscription.

Step 1) This step is literally the heart of the subscription and where our stored proc comes to the picture. Basically, you tell the subscription where to connect to retrieve the parameters at run time and how! I am using a shared data source I already built it to point to my Membership provider (not covered in this post). In the Query textbox I specified a call into my stored proc. Remember, you can also use a T-SQL query instead of the stored proc thing , but you gotta make sure that you hit that darn Validate button to not only validate your query, but also to retrieve the schema of the returned result set to be used in the upcoming steps.

A big No Answer to couple of potential smart questions:

• Can I make the data driven query dynamic and extract it at runtime too? No.
• Can I merge delivery extensions in one single subscription, say deliver the report via email and dump into a doc lib as well? No.
• Is there anyway I can create a 1:m relationship between my data driven subscription and my reports? No – only 1:1.
• I know that the list of subscribers to a data driven subscription can be extracted dynamically and it should be stored somewhere , but is there any way to make where subscribers data source points to , dynamic as well? No. Subscribers data source (In this case membership provider)must be static and defined when setting up the data driven subscription. No dice in making that dynamic too.

Step 2) The sample report I am using in this demonstration has a parameter called sales order number. In this step, you get a chance to set this parameter. I am opting for the parameter’s default value (first option) , but as shown in the picture below you can set another value (specific for this subscription) in the textbox or you can get the parameter from a field returned from your subscription query (discussed in step 1) from the drop down control.

Step 3) A subscription (either static or data driven) is meaningless without a delivery mechanism. In this step , you indicate what the delivery extension you want your subscription to use. The plan is to deliver the report via email so go ahead and choose Email from the drop down menu. As you can tell , there are many other properties in this page that can be parametrized and initialized at run time. Our stored proc only returns the actual email addresses so just set TO field to Email column of the returned result set. You also have to set the Subject and Format (not shown in the picture below) before proceeding to the next step.

Step 4) Now , you need to tell the subscription when to fire off. You have a choice of using an already created Shared Schedule or you can configure a dedicated schedule for this particular subscription. I am using a shared schedule (and Yes, I am going to present this whole thing at my presentations in Microsoft techdays 2008 tour across Canada)

When you click Finish on Step 4, you will be redirected to where you start and you are done. Click on the subscription you just built and you should be able to see something like the following picture:

Once the subscription gets executed , emails are nicely delivered as expected :

Two things need to be highlighted here :

• If you had chosen to include a link to the actual report in step 3 (I didn’t) , you would see that the link to the report is from the zone that user belongs too. It was kind of interesting!
• I am using smarter mail as my mail server and client. Read my post here for more information.

Click on the attachment and watch the result :

Yeap! that’s pretty much all about it. Hope you have enjoyed our journey on SSRS data driven subscription topic.