Archive for October, 2008

Blogging from Techdays 2008 Venue

October 30th, 2008 3 comments

Here is my presentation wrapped up at Techdays 2008 Toronto,  2 hours later , I’m sitting at the speaker room doing what every *normal* geek does! Writing a follow up blog post on my session. Truth to be told, once I am done with his blog post, I feel comfortable saying goodbye to Toronto and say hello to Winnipeg, Calgary and Vancouver’s upcoming sessions! can’t wait to see folks in west coast….I am in particular excited about Vancouver because 3 years ago when I immigrated to Canada I first landed there and I have very good Canadian friends back there who taught me how to converse, convey and write in English so I feel kind of attached to the city!

Alright , let’s get to the formal THANK YOU paragraph :

First and foremost, I’d like to thank every each of you for attending my session. Data track room was packed and I truly had fun , up on the stage, presenting to all 250 people sitting in the room.  I am hoping that you managed to walk away from this presentation with some take away. I apologize about wsdl command line not working as  I had left my contacts at home and I had difficulty reading the small fonts on the virtual machines (Yeah one of VMs and the mic was really driving me around the band) , therefore I didn’t notice that I was misspelling it .Below is a picture that shows how a call into the wsdl command line is made to get the proxy class generated for you from the ReportService2006.asmx endpoint.


With that being said, here are couple follow up points to my session:

1) Due to the volume of the things I wanted to share with you , I didn’t have the chance to spend enough time on the security context of your executing reports as much as I had planned to.  I would highly recommend that you read the following post before touching anything, because you want to avoid headaches. right?

 SSRS 2008 integrated mode: Security

2)  On the beginning of my presentation I demonstrated that how you can flip the configuration from native to integrated mode. you remember that? cool , here is the blog post that walks you through the steps with lots of screen shots . Enjoy!

 Shifting From SSRS 2008 Native to Integrated For Good

3)  Mini IIS term I referred to during my session has been clearly document here:

Say Goodbye to IIS, Say Hello to HTTP.SYS!

4) Powershell and Feature code and a walk through : Put the mouse pointer here and here and kindly press the right button!

5) Data Driven Subscriptions Part1 and Part2 – Remember, If you googled it , that’s me LOL

Got a question? Drop me a line!

Now I can go home, relax and have a nice cigar in my bathtub! :)

Categories: MOSS 2007, SSRS Tags:

SSRS:Data Driven Subscriptions in Integrated mode (Part 2)

October 26th, 2008 4 comments

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.

Categories: MOSS 2007, SSRS Tags: , ,

SSRS:Data Driven Subscriptions in Integrated mode (Part 1)

October 26th, 2008 2 comments

The thing is, if you read SQL Server 2008 books online (For example here or here) , it is explicitly mentioned that Data-driven subscriptions are not supported when the report server is configured for SharePoint integrated mode!  I could have sworn that I heard several times from SSRS top subject matters that they have added this new functionality to the SSRS 2008. Plus looking at the proxy endpoint tells me that the functionality is there, but <rant>like a lot of other things in the Integrated mode (and I’m talking about SSRS 2008 not 2005 SP2), there are a few non-MS people who have gone ahead and partially demonstrated how to implement stuff! All I see is a marketing *LIST* of all the new goodies ,but  not even a single Web cast or How-To with the focus on the integration</rant> lovely! eh?

So one million dollar question is : Is Data Driven Subscriptions part of SSRS 2008 when it is configured in integrated mode? Answer is Yes! It is fully supported.

Before I walk you through how to set this up in SharePoint (mostly covered in Part 2) , I would like to give you  a brief tour of what the data driven subscription concept is and show you some preparatory steps that you need to take before you touch the SharePoint equivalent feature sets. Okay, let’s get the ball rolling here:

Generally speaking in SSRS , subscriptions are an alternative way of delivering reports to people in a very dynamic and automated manner.The objective is minimizing On-demand access to the report server; therefore reducing the traffic and boosting the performance.  It is all about targeting different information at different audiences , all done in a data driven fashion and asynchronously (can’t u tell that I am a developer?! 😉 )  delivered to them. Subscriptions are processed on the report server and are distributed through delivery extensions (another important concept in SSRS) that are deployed on the server. Two very important delivery extensions in SharePoint are:

  • Email
  • SharePoint Document Library.See this.

Let me quickly walk you through setting up a data driven subscription (using Email delivery extension) on a sample report deployed  to a Report Library in a SharePoint site. I am sure you will see the value! All right, the plan is to distribute our sample report to a fluctuating list of subscribers kept in a role (in my role provider) called SSRSSubs. Let’s see , for a data driven subscription based on the email delivery extension , what you would need to put together:

1) Membership and Role provider: I am using the out of the box AspnetSqlMembership and role provider for this demonstration; therefor I don’t spend any time digging into what roles and memberships are. Having a membership and role provider to benefit from data driven subscriptions is absolutely NOT necessary, but making the parameters of a subscription dynamic and keep their values somewhere (such as a SQL db) and retrieving them at run time is what data driven subscriptions is all about. Are we square on this? GREAT! I heard the yes answer loud and clear (I have ear over IP kind of organ!) .Let’s move on then.

For this demonstration purpose I am just making the list of emails dynamic , but feel free to make everything dynamic such as the report’s parameter(s) , email’s subject, report’s format and etc. Just think about how powerful this could be!

I have added three users (user1, user2 and user3) to my membership provider and added them to SSRSSubs role. Each user has a respective email with the extension of (i.e All three emails are also defined in my mail server.

2) Data Driven Query: Each data driven subscription requires a query that retieves its paramters value at runtime. For this demonstration, I have written an stored proc called aspnet_GetEmailsInRoles that would return the emails of all the users in a given role stored in my role provider. This stored proc is not part of the out of the box AspnetSqlMembership database schema . As you will see in Part 2 , I will call into this stored procedure to retrieve the list of emails at run time.

USE [aspnetdb]
CREATE PROCEDURE [dbo].[aspnet_GetEmailsInRoles]
@ApplicationName nvarchar(256),
@RoleName nvarchar(256)
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
DECLARE @RoleId uniqueidentifier

SELECT @RoleId = RoleId
FROM dbo.aspnet_Roles
WHERE LOWER(@RoleName) = LoweredRoleName AND ApplicationId = @ApplicationId

IF (@RoleId IS NULL)

SELECT m.Email
FROM dbo.aspnet_Users u, dbo.aspnet_UsersInRoles ur, dbo.aspnet_Membership m
WHERE u.UserId = ur.UserId AND u.UserId = m.UserId AND @RoleId = ur.RoleId AND u.ApplicationId = @ApplicationId
ORDER BY m.Email



Here is the store proc execution screenshot:


3) Payload: Below is the report that I am trying to attach to a data driven subscription ( nice and sexy)


Clarification: As much as I’d love to take the credit for authoring the report shown above, this report is part of the AdventureWorks report pack that coms out of the box with default installation of SSRS 2008.

4) Email Settings: No matter how you’ve configured the outgoing email settings in SharePoint (Central Admin -> Operation -> Outgoing Email Settings) , you have to setup email settings in Reporting Service Configuration manager for the email delivery extension to show up in static and data driven subscriptions you set up in SharePoint.

Email delivery extension is not available:


You configure the email settings in Reporting Service Configuration tool :


Here is the “Email delivery extension” all of a sudden showing up:


5) SQL Agent Service: Obviously SSRS automated background services are handled by SQL Server Agent , so get the bad boy started if you haven’t already.


Okay, now that we have all of our non-SharePoint parts in place, so let’s just move on with making SharePoint aware of what we are planning to set up. Please proceed to part 2.

Categories: MOSS 2007, SSRS Tags: , ,

The Way I See It #21

October 24th, 2008 No comments

wayiseeit.PNG “People need to see that , far from being an obstacle , the world’s diversity of languages , religions and traditions is a great treasure affording us precious opportunities to recognize ourselevs in others.”

Say a big NO to racism!

Categories: General Tags:

Report Builder 2.0 RTMed , but…

October 23rd, 2008 No comments

…  replacing the Open with Report Builder link (to point to the new 2.0 RB ClickOnce app)  in the Report Viewer Web part is NOT available in the RTM version.It is pushed back to SQL Server 2008 SP1 or CU release. Read this thread!


FYI, if they would have released this functionality in RB 2.0 , you could set the URL to the new RB 2.0  by browsing to central administration site, under Application Management tab in the Reporting Services Section , choose Set Server Default page then you could set the URL in the Custom Report Builder launch URL text box. Looks like for now we should just leave it empty 😉

rbcustomlunch.pngClarification : This doesn’t mean that you can’t use RB 2.0 in the Integrated mode. Just replacing the link in reportviewer Web part isn’t possible. Not a biggie , but I thought you may be intrested to know.

I’ll have another quick tip popping up in the next blog post. See ya in a bit!

Categories: MOSS 2007, SSRS Tags: , ,