Reporting Services Data Extensions & Access Services in Sharepoint 2010
One of the features of Access Services, SharePoint 2010’s new “host Access databases on the web” service, involves the promotion of Access reports to SQL Server Reporting Services (SSRS) reports. Essentially the publishing feature of Access Services takes an Access report and generates an RDL file from it, allowing the SSRS Web control to display it inside SharePoint.
This feature requires that you have an SharePoint Integrated mode instance of Reporting Services and have configured Reporting Services Integration in SharePoint Central Administration. Assuming that you’ve done all this, you may expect that your reports just show up. Unfortunately this is probably not going to be the case, and you may find yourself being faced with an error similar to the following:
An attempt has been made to use a data extension 'ADS' that is either not registered for this report server or is not supported in this edition of Reporting Services
The following except from a TechNet article gives the solution:
Modify the C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config file on the Reporting Services server. Remove the comments from the ADS data extension. For example:
TO
This allows Reporting Services to construct a data set out of a particular data source. You can find more information on Reporting Services Extensions here, and more specifically Data Processing Extensions here.
The interesting thing about all this is that Access Services actually promotes all of your Access tables to SharePoint lists, making me wonder why the generated SSRS reports need to know about Access at all. MIcrosoft.ReportingServices.DataExtensions.SharePointList.SPListConnection already provides an implementation that talks to SharePoint lists, so why not use that? I figured that I’d do a little digging into the SharePoint site generated by Access Services to find out just why they needed their own Data Extension.
If you navigate to the _layouts/viewlsts.aspx page (type it into your address bar directly, so http://mysite/Applications/MyApplication/_layouts/viewlsts.aspx) of your Access Services application you should be able to explore the Report library and associated lists that make up your application. Inside the Report library you’ll notice that there is an "”AccessDataSource”, who’s connection string contains both the SiteId and WebId of your application, and whose Data Source Type is “ADS”. This tells us that, at the very least, the report knows that it’s communicating with a particular SharePoint Site.
If you download a copy of one of the report RDL files and check it out in a text or XML editor you’ll notice something interesting; inside the DataSet element there is a Query definition that looks something like the following:
ADS _sq_rContactList =Parameters!AccSrv_Where.Value
The important thing to note here is the CommandText element. Access Services stores most of your Access objects, including queries, as serialised text inside list items in a list called MSysASO. If you inspect that list you’ll find a list item matching up with the CommandText from your DataSet Query inside your RDL file.
If you look at the ServerObject field of this list item you’ll see something that initially looks somewhat like a CAML query but is actually a query from the Access Services namespace, http://schemas.microsoft.com/office/accessservices/2009/04/application.
Here’s an example of one of these queries:
At first glance this doesn’t tell us a whole lot; it just seems to be a collection of field names that we would want to fetch in our query, but it doesn’t say anything about where the data is coming from or whether there are any restrictions on what data we should bring back. You will notice, however, that it seems to reference another item in the MSysASO list through the Source attribute of the Property element. In this it is referencing the _sq_rContactList_Base list item, which looks like the following:
=(((Contacts.Company)<>"Local Bank"))
This second query contains the meat and potatoes of the query itself, including any query restrictions (which you can see in my example where I have restricted the data where Contacts.Company <> “Local Bank”) and the source tables and fields where the data is coming from.
As such, each query is actually made of of two serialised objects stored in two separate list items inside the MSysASO list; _sq_r%ReportName% and _sq_r%ReportName%_Base. With these two objects, when combined with the ADS Data Extension and the SiteId/WebId referenced in the Reporting Services Data Source object previously discussed, Reporting Services now has enough information to fetch the appropriate data for the report.
So it is reasonable to infer from all this that the reason Microsoft don’t use the MIcrosoft.ReportingServices.DataExtensions.SharePointList.SPListConnection Data Extension is because they needed to be able to perform lookups into their shared resources list (MSysASO) to fetch the real query that they want to run, hence needing a custom Data Extension to do so. The use of this central repository makes total sense, especially when combined with Access’ shared query model where multiple reports could be referencing the same query. If they did not do this and simply relied upon the SharePoint List Data Extension then they would essentially have to generate CAML queries for each report, repeating them inside each RDL file as needed.
Refreshing Reports using URL access in Reporting Services
In the past I've been a fairly big advocate of the Microsoft Report Viewer control for accessing SQL Server Reporting Services reports from within a web application, simply because it allows authentication to be proxied through the host application (so you can use a service account to talk to reporting services and control security through your application rather than adding every user in your application to reporting services), but the control requires a traditional ASP.Net webforms Form and page life cycle, so it's out of the picture for ASP.Net MVC development.
To solve the problem I turned to Reporting Services' URL Access functionality that allows direct linking to reports while still giving you control over parameters, the toolbars and other functionality through the use of variables passed in via the query string. I used iframes to get the inline reports onto the page, and just linked directly to the reports that were meant to open in their own window.
This was great until I noticed that the data on the report was not being refreshed at a regular interval, leaving users with stale data on screen. A quick check of the report in Report Manager confirmed that the data was in the system and the report updated correctly when shown through Reporting Services' own interface.
A quick inspection of the execution parameters of my report (located under properties->execution for each report in Report Manager) showed that I was not using the in built Reporting Services caching, so I figured I was dealing with some sort of HTTP caching. This lead me to try one of the oldest tricks in the "how to fool the HTTP cache duration" book, random junk parameters. The theory behind this is that adding a random parameter that is not used by an application to the end of a query string will tell the web server that this is a request that it hasn't seen before, and hence it will need to figure out the actual result rather than looking in the cache. I postfixed the URL with something along the lines of "&RefreshParameter=" + System.DateTime.Now.Ticks.ToString(), and hoped for the best.
Unfortunately it did nothing. Realising that the problem had nothing to do with HTTP caching I thought that I'd try and apply the same trick to an actual report parameter to see if reporting services could be fooled in a similar manner. The URL Access feature allows you to specify reporting services parameters on the query string in the exact same format as the "RefreshParameter" I had already created, so I simply had to make a new parameter on each of my reports called "RefreshParameter" that accepts blank/null values and does nothing. This worked a treat, enabling me to have current data displayed in iframes on my MVC site.
Update: Peter Van Ooijen suggests that using the rc:ClearSession=true parameter may work in some scenarios (but apparently not all). Some people seem to have had good mileage by combining both the session clearing and the random variables.
When You Assume..
I, like many others, have never thought of Linux as a "pretty" operating system, instead choosing to think of it as the home of "best effort" programmer-designed GUIs and "command line first" design.
This line of thought has set my expectations for my desktop Linux environments at home. When I switched to running Linux as my main Desktop machine 18 months ago I noticed that the fonts were nowhere near as nice as my Mac and Windows machines. Blindly assuming that Ubuntu didn't support ClearType, I simply went about my business and put up with the horrible fonts.
That was up until today, when I finally decided to investigate. It turns out that Ubuntu fully supports ClearType (although they call it "subpixel smoothing"), it's just turned off by default. There's even a handy GUI for turning it on. I'm never going to assume that my OS has to look like a dog's breakfast ever again.
Now You See It
Today I was helping out one of my colleagues with a particularly annoying InfoPath problem; he wanted to enable the multi-line option for a text box bound to a web service, but whenever he did the read-only option would automatically trigger. Truly frustrating.
Not being one to believe that things are impossible, I decided to take a crack at it at home, and as soon as the kids were in bed I got stuck into Everyone's Favorite Search Engine (TM). Unfortunately, Google was not my friend on this problem; I found all sorts of information about binding Rich Text Boxes to Web Services and forcing text boxes to re-size when using Forms Server, but nothing related to editable mutli-line text boxes.
I decided that the best idea was to try and replicate the problem, so I fired up my trusty Win2k8 VM, made a quick ASMX web service and hooked it up to an InfoPath form. Sure enough, as soon as I tried to enable the Multi-line option of a text box it became read only. Reassuringly, it worked when using an unbound text box, which at least let me know that displaying editable mutli-line text boxes in InfoPath is possible.
The fact that the problem was only occurring with web service data-sources made me suspect that the schema being returned by the web service may be the key, but upon inspection the schema InfoPath created for my web service connection was using exactly the same types as the schema for my default form data-source. The elements were not read only, and I couldn't see anything else out of the ordinary in the schema. So much for that idea.
Then it clicked. The problem had been staring me in the face the whole time in the guise of a huge red circle with a white "x" through the middle; the form was configured for InfoPath Form Services compatibility, which disables certain options. This MUST have something to do with the problem. I quickly turned off Form Services compatibility then turn on the Multi-line option again, and....
... the field became read only. Doh! But wait! There were two sub-options under Mutli-line: Paragraph breaks and Wrap text. In Form Services compatibility mode I could not edit these options, and selecting Mutli-line highlighted both, which in turn highlighted the read-only option, but with compatibility mode turned off I was now free to edit these values.
Then it hit me. I noticed that, by default, I couldn't select the Paragraph breaks option unless the read-only option was checked. That was it! For some reason, InfoPath can't handle having paragraph breaks in a text box when it's bound to a web service
So instead of clicking on Multi-line I clicked on Wrap text. This checked the Mutli-line option for me, and at the very least let me have a text box that could wrap text onto a new line. I could live with not being able to have paragraph breaks, but unfortunately I could only select this mode in InfoPath Form Services compatibility mode, meaning that I wouldn't get all the fancy error checking to make sure that it would work when viewed over the web. When in compatibility mode, clicking on Multi-line would still check the Paragraph breaks option which would then force the control to be read only. I couldn't win.
Or could I? On a whim I turned off compatibility mode again and set up my control to be Mutli-line/Wrap text as described above, then I turned compatibility mode back on. Success! The options stayed as they were, giving me a mutli-line text box that wasn't read-only. I would just have to make the necessary multi-line related changes to my fields with compatibility mode turned off, and only turne it on for the specific occasions where I would need it.
Not a great solution, sure, but at least it works, and that's certainly better than nothing. I'm honestly still amazed that I couldn't find any info about this on Google, though. I would have thought that using a bound multi-line text box would be a more common use case.
The Future is Looking Good
As I've previously mentioned, as of October 1st 2008 MSDN subscribers who have previously only had access to Visual Studio Team System Development Edition can now also use Database Edition. The reasoning behind this move has now been made clear thanks to the January MSDN Subscription Newsletter:
In the next release of Visual Studio Team System we will be merging the feature sets of the Development Edition and the Database Edition into a single product. The new product will include all of the features in the Development Edition and the Database Edition as well as new capabilities delivering even more value in a single product. This will provide a more complete set of capabilities for building software in today’s data-driven environments. Bringing these two feature sets together enables you to take advantage of the core tools for application development as well as the necessary tools for database development, including performance profiling, code analysis, code metrics, code coverage, database refactoring, Schema Compare, Data Compare, and more
Looks like they've finally got the message that a large proportion of developers still need to touch the database, and as a result will be combining the Developer and Database editions of VSTS in the next version. Now if only they could get rid of the comparatively useless Architect edition. Roll on VS 2010!
The Misleading Error Message Strikes Again
We've got a stock standard Windows 2008 virtual hard drive image at work that I wanted to make some additions to and generally polish up into a decent developer image. The install media was the "Windows Server 2008 Datacenter, Enterprise and Standard (x86) - DVD (English)" ISO sourced from MSDN.
I noticed that the image had not yet been activated, so I set out to rectify the situation. Hopping into the system settings in control panel, I navigated down the the section on product key information and clicked on the link to activate windows. Unfortunately this process errored out with the following message:
Code: 0x8007232B
Description: DNS name does not exist.
I immediately checked network connectivity. Can I get to google? Yes. Can I resolve MS addresses? Yes. Hmm.
Then I happened to notice some text in the bottom right hand corner of my desktop:
Windows Server(R) 2008 Standard
Evaluation copy. Build 6002
Evaluation copy? That means that my product key is not going to be valid. So I grabbed a new product key from MSDN, entered that in and tried the activation process again. Success!
After all that I still can't figure out what a DNS error has to do with a product key being invalid. I would have been more than happy with something along the lines of "Unable to activate due to invalid product key". They may as well have told me that activation failed due to insufficient vegemite.
InfoPath Form Security and Custom Code
Generic error messages are high on my list of pet peeves. Being told that "an error occurred" may be something that you can pass off in a line of business application, especially if the actual error message gets emailed to the help desk or something similar, but it is totally unacceptable in a product targeted towards software developers.
Tonight, when trying to add some managed code to an InfoPath 2007 solution, I ran into one of these horrible errors. InfoPath was presenting me with a highly helpful "InfoPath cannot open the selected form" error message whenever I tried to debugging the solution.
After spending a few minutes scratching my head I remembered that InfoPath forms need to run in Full Trust mode in order to use managed code. I navigated to "Tools>Form Options>Security and Trust" and had a look at the settings, which were still set to the default "Automatically determine security level". Unfortunately I assumed that, when using this setting, InfoPath would be able to figure out that I was using managed code and select the appropriate security level. Obviously my assumption was incorrect.
After setting the level of trust to "Full Trust" I was finally able to launch the form, complete with managed code.
If I had not previously read about security and trust within InfoPath then I would have had to waste more time floundering on Google with only a generic error message to help me. All that could have been avoided if they had just presented me with a dialog saying "The form has an insufficient trust level to perform this operation". Well, either that or make the "Automatically determine security level" trust setting actually determine an appropriate trust level in the first place.
Flashdance
Until recently, Adobe's support for Flash in 64 bit Linux environments has been pretty much non existent. Those of us who have made the jump have had to make do with nspluginwrapper, which enables the Netscape plugins to run on architectures (such as x64) that they were not written for. The experience offered by this solution was seriously sub par, and was almost a show stopper issue for me when deciding whether or not to make the switch to Linux.
This all changed when Adobe started releasing Alphas of the Flash 10 player for x64 linux distros. Finally we can run the flash plugin natively, ditching the horrid performance and "gray screen of death" that was commonly associated with nspluginwrapper.
It took me about 20 seconds to set up the new plugin using this guide from Softpedia. I uninstalled nspluginwrapper, created a plugin directory as instructed and copied in the libflashplayer.so file. Then I reboot firefox and....
Argh! The performance was even worse than before! What was going on?
A quick scan of top told me that a process called gtk-gnash was eating 100% of one of my 4 CPU cores. I had found the culprit. Unfortunately Ubuntu comes with a horrible open source flash implementation called gnash. The nspluginwrapper implementation had successfully kept it at bay, but now that was gone, leaving me exposed to the CPU usage equivalent of Unicron that is gnash.
After uninstalling everything to do with gnash and giving the system a quick restart for good measure, seeing as gnash had successfully crashed ALSA and a few other core systems, and I was up and running. Finally I can enjoy watching mind numbing Youtube videos and playing benign Flash games at a speed more befitting of my Quad Core box with 8 Gb of RAM!
DataDude for All
Picking an appropriate version of Visual Studio Team System for your team to use has always been an interesting proposition. The full Team Suite edition is outside the price range of most projects, and you have to make sure that the majority of your team will be able to see all of the artifacts with minimal trouble. Using features such as the infrastructure diagramming tools or database projects can often mean that you have one machine capable of performing that function, which can act as an annoying bottleneck for your team.
The choice of product becomes an even tougher question if you are a Microsoft Gold Partner (with ISV or CDS competency), as Gold Partners have traditionally gotten a certain number of VSTS Developer Edition licenses as part of their partner pack. This is great, but it makes it really hard to justify the cost of another version of VSTS. Other versions are now $4000 dollars more expensive than Developer edition, rather than all of the editions being the same price.
I have always maintained that, bang for buck, VSTS Database Edition is the best value for money out of all the VSTS editions, and, given a choice, I would pick this for every member of the team so that they could all use the database source control feature. It's the feature I always miss whenever I don't have the full Team Suite edition of Visual Studio installed.
There was much rejoicing, then, when I found this lovely little tidbit on the MS Partner site.
Certified Partners who have earned either ISV and/or CDS Competency are upgraded to Microsoft Visual Studio Team System 2008 Development Edition with MSDN Premium Subscription. Starting 1 October, 2008, these Certified Partners will be able to download VSTS Database Edition from MSDN Subscriber Downloads as part of their upgrade to VSTS Development Edition with MSDN Premium Subscription.
It seems that we now get the choice between VSTS Developer Edition and VSTS Database Edition. I for one am extremely happy with this news. Test Edition and Architect Edition have some nice features (most notably the performance testing tools in Test Edition which run rings around the old Application Centre Test product that came with 2003), but for me these two products cover the majority of the Team Suite features that most development teams would need.
Expand Your Mind
After the recent release of Visual Studio 2008 SP1 and .Net 3.5 SP1 I thought it would be a good idea to upgrade my Windows development environment to take advantage of some of the cool new features. I do .Net development on my Ubuntu machine by using VirtualBox to run a Windows 2008 Virtual Machine. It works quite well, especially if you're mainly doing server side stuff or Windows Forms. I'm not sure how it would handle the DirectX goodness of WPF, but I'm willing to live without that for the moment, and I can always install Vista x64 on another box if need be.
When I originally set up the VM, 20 gig sounded like a good size for the hard drive, so I set up a 20 gig dynamically expanding VDI file using VirtualBox and started installing. The dynamically expanding disks are cool in that they will only expand if need be, so if you create a 300 gig image and only use 20 gig of it, the VDI file for the drive only uses 20 gig of your actual disk space disk. I wasn't planning on installing office or anything else, so I thought that the drive would be more than big enough. How wrong I was.
After installing the Windows 2008, Visual Studio 2008, SQL Server 2008 and Firefox 3.0 I had around 3 gig free. Where did all my drive space go??? "No matter", I thought, "3 gig should be enough to work with for the moment, especially as I'm using network drives for all my data storage". I continued working like this for a few months, right up until I went to install the VS 2008/.Net 3.5 Service Pack 1 updates.
Half way through the install I merrily discovered that the service packs require over 6 gig of free space just to install themselves. But.. but.. but... I don't HAVE 6 gig of free space! It should be noted that the installation program says that it is merely "recommending" that you have this amount of free space, but it won't let you continue unless you free up the "recommended" 6 gig. That's a pretty definite recommendation.
My first thought was that to free up that amount of space I would have to uninstall either Visual Studio or SQL Server, which would sort of defeat the purpose of installing the service packs. Obviously this wouldn't do, so I went looking for ways to save a few gig here and there, and quickly discovered that I had a 2 gig page file by default. I reduced this to 200 meg or so, but I still needed to free up more space to install the service packs.
At this point I gave up on the idea of trimming back my install to allow the service pack to continue. I went hunting for some free hard drive cloning software, and found the excellent (and free) HDClone. HDClone allows you to copy one hard drive to another, and it can also expand partition so that it takes up all the space on the new drive.
I downloaded the free version of HDClone and mounted the ISO in VirtualBox, then I created a new 230 gig dynamically expanding VDI file which should be enough to install the service pack (cough). I mounted the new drive as the secondary master for my Windows 2008 Virtual Machine and mounted the ISO in the virtual CD-ROM drive.
A quick reboot of the machine presented me with the HDClone interface, and in 4 clicks I was ready to copy my drive. It even asked me if I wanted to covert the partition to take up all 230 gig of the new virtual drive. I let it do the conversion and left it to copy all my data.
An hour or so later it was done. HDClone asked me if it wanted me to automagically set up my partition data, so I let it do that and then reboot. "Yay!", I thought, "finally I'll be able to install the service pack!".
Unfortunately it was not to be. When trying to boot off my new VDI file I discovered that it wouldn't boot, even though it new the OS was on there. I dug around for my Win2k8 install media and went into repair mode. Sure enough, Windows was reporting my partition as being 0 meg. Something wasn't quite right here.
I could have downloaded something like the Ultimate Boot CD to play around with the partitions, but it was late and quite frankly I couldn't be bothered, so I booted back into HDClone again and redid the copy, this time making sure that I said "no" to its overzealous partition magic (no pun intended).
Once that was finished I removed the old 20 gig VDI, gave it a reboot and bingo, I now had Win2k8 installed on a 224 gig drive. This was more than enough space for the Service Pack 1 installer to work with. I might even have enough room to install MOSS 2007 now.
Headshot
About Garry
Me On The Web
User Groups
Post Calendar
| M | T | W | T | F | S | S |
|---|---|---|---|---|---|---|
| « Apr | ||||||
| 1 | 2 | 3 | 4 | 5 | 6 | |
| 7 | 8 | 9 | 10 | 11 | 12 | 13 |
| 14 | 15 | 16 | 17 | 18 | 19 | 20 |
| 21 | 22 | 23 | 24 | 25 | 26 | 27 |
| 28 | 29 | 30 | 31 | |||
Tags
Who's Online
8 visitors online now
0 guests, 8 bots, 0 members
Map of Visitors
Blogroll
- Coding Horror
- Cup(Of T)
- Haacked
- Joel On Software
- Otaku Software Blog
- Rands In Repose
- Scott Hanselman’s Computing Zen
Archives
- April 2011 (7)
- March 2011 (4)
- July 2010 (1)
- May 2010 (1)
- January 2010 (1)
- July 2009 (1)
- April 2009 (1)
- February 2009 (1)
- January 2009 (6)
- December 2008 (2)
- October 2008 (1)
- August 2008 (1)
- July 2008 (3)
- June 2008 (4)
- May 2008 (5)
- April 2008 (12)