Category Archives: Development

Things not directly SharePoint related

Connecting SQL Reporting Services to a SharePoint List


There is a multitude of blogs that discuss how to access SharePoint data from SQL Reporting Services, so why another?  Simply because I had not found one that exposed all of the land mines I seem to hit when doing this.  So, I thought I’d try my hand at creating a detailed guide-map to the mine field.

The Requirements

In order to do this, you must have :

  • Visual Studio 2005 (I’m sure this works in 2008 as well, but I haven’t tried it yet)
  • SQL Reporting Extensions.  These are installed by default when you install SQL Server
  • A SharePoint list exposed Anonymously or via Windows Integrated Authentication (more below)

Accessing the List

The Report Designer requires that the Data Source either require no authentication or uses Windows Integrated authentication.  Other options are available when defining the Data Source but you will not be able to use them as they are not supported for web services by the designer.

Note: If your SharePoint list requires Windows Authentication then your development machine *must* be in the same domain or a trusted domain as the SharePoint server.  If you are developing on a system that is not in the domain of the SharePoint list you are attempting to access, you will not be able to proceed.  Brutal, but there you have it.

The Steps

Start Visual Studio

Select File -> New Project -> Business Intelligence Projects -> Report Server Project.  Name and save the project.

In Solution explorer, right-click on Shared Data Sources and select Add New Data Source

FliE3

Make sure you specify the Type as XML and put the proper URL to your server’s list.asmx web service page.  This is usually simply http://<server>/<path>/_vti_bin/lists.asmx, replacing <server> with your server name and <path> with the path to the site with the list you are trying to access.

Click the Credentials tab and make sure you set it to Windows Authentication (default) or No Credentials (if your SharePoint site allows anonymous access).

FliEC

The other options are not supported by the Designer and will throw an error along the lines of “An error occurred while executing the query…” when you try to fetch the data.

Now that you have the data source defined, you will need to define the report.  To do this, right-click on the Reports folder and select Add-> New Item, then add a Report.  Do not use the Add New Report option on the right-click menu as that forces you to use the report wizard which can’t properly connect to the web service to get data.

Fli11F

Fli120

Open the report and click on the Data tab and select <New Dataset…> from the Dataset dropdown

Fli140
Now enter a name for your dataset and make sure to pick the Data Source you created a few steps earlier.  The most essential thing on this form is to set the Query string properly.  It should be :
<Query>
    <Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems"/>
    <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
</Query>

From there, click on the Parameters tab enter your parameters.  See the Parameters section below for more information.

Fli142

Parameters

There are 5 parameters that can be passed: listName, viewName, query, rowLimit and queryOptions and yes, they must be this exact case.  The catch here is that if you define all those parameters you will get data in the Data view, but then the Preview will fail with a multitude of messages, usually along the lines of “The Value expression for the parameter … contains an error … “.  To get around this, define only the parameters you are actually passing values for.

listName

This tells the web service where to get the data from and it is the only required parameter.  You can give it either a list name or a Guid.  If you are unsure how to get the Guid, fire up Stramit CAML Viewer and browse to the list or simply click Settings->List Settings while viewing the list.  The list Guid will be URL encoded in the querystring after ‘List=”.  You can quickly decode it here.

viewName

The viewName tells the web service what view of listName to query in order to pull back data.  This is not required but strongly recommended because the Report Designer mangles the query parameter with severely limits your options for filtering data from the designer side of this process.  This is also an extremely picky parameter and I have yet to be able to get it to work consistently with a actual name of a view and have had to always use a Guid.

Note: If you do not specify a viewName, it will use whatever view is defined as the default for the list as the filter/sort for the data.  This is usually the All Items view and will likely include way more data than you want.

query

Do not use this parameter.  It is extremely useful for people calling the web service from code but does not work with the report designer.  I suspect that designer does some extra encoding of the CAML that this parameter normally accepts which confuses the web service to no end.  If someone finds a way to actually use this parameter from designer, please let me know!

rowLimit

The number of items to return.  SharePoint defaults to 100 so if you need more than this, you will need to include this parameter along with a number exceeding the number of rows you are likely to get.

queryOptions

Do not use this parameter for the same reason as query: it gets improperly encoded by the report designer.

Click OK and then try to get your list of fields by clicking the Refresh Fields icon ( Fli143 ) in the data view.  A small + should appear next to your report name.  Click on that to see all the fields it found.  If you don’t see all the fields you were expecting, be sure to read the tip in the Annoyances section at the end of this posting.

Last step – get some data by clicking the Run icon ( Fli1EA ) on the Data tab.  It should pop up a dialog with the parameters you defined earlier.  Make sure all the parameters and values are there that you expect and click OK.

That’s pretty much it.  I do have some general thoughts on the whole process that I’ve tacked on below.  Hopefully, this covers most of the quirks and oddities associated with this process.

 

General Troubleshooting

I cannot recommend Fiddler highly enough.  With this running on the dev machine you can easily see everything that is going on in the actual SOAP calls that are responsible for those vague errors that the designer throws out.  (click to see details)

Fli154

Guid’s versus Names

Ok, so do I reference the list using the Guid or name?  There isn’t an easy answer here as this is the classic catch 22.  The Guid is the ID of the item regardless of the name and is the natural thing for developers to want to use.  Unfortunately, if you are in an environment where code is migrated from a Dev farm through test/qa and then to Production, that Guid will change in each environment.  The Name is much friendlier and works across environments, but names have a tendency to change over time which will break your report.  Choose what works best in your environment.

A Word About Formatting

SQL Reports has no clue what to do with many of the columns used in SharePoint, so you might end up having to write some code to handle these.  The first you will probably see of this are the SharePoint fields that contain lookup values because these will show up on your report as something like “245#;My real name“.  Pretty nasty.

What you can do is add the snippet of code below to the Code section of the report.  To get there choose the Layout view, then Click on Report -> Report Properties from the main menu.  Click on the Code tab and paste the code below in the window

function GetNameFromSP(pFullID as string) as string
  dim strRet as string
  dim iPos as integer

  if pFullID = nothing then return ""
  if pFullID = "" then return ""
  iPos = Instr(pFullID, ";")
  if iPos &lt; 1 then return pFullID

  return Mid(pFullID, iPos +2)
end function

Then right-click on the field in the report that you want to fix this with and select Expression.  In the Expression Builder window, set it to the following:

=Code.GetNameFromSP(<your field reference>)

It is possible to use a .Net assembly for this function as well, but that is way beyond the scope of this article.  Besides, this method doesn’t require any special installation steps on the target server.  If you are creating dozens of reports where you need this behavior or others like it, then it makes sense to look into the assembly approach.

A Word About Sorting

Odds are that the second place that you will hit the formatting snag mentioned above is when attempting to sort the report by one of the fields containing such values as it will sort by the ID part of the value string and not the name.  The easy fix is to use the same Expression as above in the Sorting and Grouping section of the report, which now allows you to sort on the real name.

Annoyances About the Report Designer in Visual Studio

Overall, working with the Report Designer in Visual Studio goes pretty good, but there are some soggy areas that you are bound to step in eventually.  I’m pretty sure these are related to working with either web services in general as a data source, or SharePoint web services specifically as I’m not seeing a lot of people reporting this problem over the net.

Vanishing Parameters

For reasons known only to designer itself, it will occasionally completely delete your list of parameters.  If you suddenly start getting errors or the wrong data and haven’t changed anything substantial, make sure your parameters are still defined.  Just make sure you have them written down somewhere where you can refer to them in order to enter them again.  I guarantee you will hit this one at least once.

Erroneous Errors

Sometimes something gets stuck in memory and designer will keep throwing an error when you try to get data or preview what should be a good data call.  Nine times out of ten, just closing the report and re-opening it will take care of this.  It’s very easy to lose an hour or more chasing a problem that isn’t really there from this.

Now you have data, Now you don’t

Fetching the data / previewing the report will work occasionally fail one attempt, then work perfectly the next with *no* changes in between.  This is a minor irritation and can usually be fixed by closing and re-opening the report.  After a while, you get used to trying everything twice.  If it fails on the second attempt, you probably really have an error.

Missing Columns

When you build the list of columns available for the report in Designer, it takes only the data in the first row of data returned.  If any of those columns are null, it won’t include the column.  Make sure all the vital columns have data when you create the report and you should be fine, even if that means manually editing them for a short time to put temporary data in.  Update: Maria has offered a solution to this.  I’m not able to try it at this moment but it looks promising – thanks Maria!

Note: if you know of fixes to any of the above, please, please post it in the comments!

 

Some Helpful Links

Creating a Generic Dictionary


It is great when you are in a field where you can learn at least one new thing every day.  For example, I've been using generics since they became available in .Net 2.0 and yet, somehow, this was the first time I've seen this particular way to use one of them:


Dictionary
<string, object> oProps = new Dictionary<string, object>();

This creates a strongly-typed dictionary object and that uses a string as the key and an object as a value.  Super simple and wonderfully powerful yet I had missed that in all of my readings and experimentation.  This just made my day!

Handy SnagIt Trick


I've been using SnagIt for years and absolutely love it.  Recently, I needed to grab a picture of menus but also needed to apply some effects to the unimportant areas so as to draw more attention to the menus themselves.  I stumbled a bit with the freehand selection tool, largely because I can't draw a straight line for diddly.  The rectangular selector was *so* close to what I wanted but it kept bringing in parts of the image that I didn't want to highlight.  Out of curiosity, I tried the old Shift trick and it worked exactly like I'd hoped!

FliEBWhat is the Shift Trick?

  • Use the Selection tool of your choice and make the first selection
  • Hold Shift and make another selection such that it partially overlaps with the first. 
  • SnagIt will merge the two selections
  • repeat as needed

This allows for some really odd selection shapes but is quite handy when you need to outline complex areas.  In this picture, I was able to select just the menus and then dim and blur the background.

(and for the record, yes, I could have applied the Torn Edge effect to the selected area which resulted in torn edges on each of the menus.  Interesting, but lost clarity)

Just for fun, I wanted to see what I could do with this – pretty neat, actually.

#RENDER FAILED in View With Totals and Grouping


After searching the net, it appears that the generic "RENDER FAILED" message (below) appears when customizing certain views for a multitude of reasons, each with its own unique combination factors and its own solution.  So, here is one more set of parameters / solution to add to the chorus.

 

 

I had a view that was grouping on a single field and also had to provide the AVERAGE of a number field.  When trying this with the default Group By settings, I would get the <!– #RENDER FAILED –> error message.

 

 

The fix was obscure but simple.  Change the Group By options to Expanded and the problem was solved.

 

The Value of a TestApp


The single greatest programming I’ve ever received is this: Use test apps religiously.  In this world of Nunit and the like, perhaps I should explain what I mean by test apps.

What is a test app?

A test app is simply the absolute smallest amount of code that you can use to create your new functionality in.  In my case, I usually create a new Windows Application in Visual Studio and put starter code in the Load event.  If I feel like being extravagant, I’ll drop a button on the form and put the hook code in there instead.  That’s it; the entire framework of a test app.

A test app is meant for creating a single piece of production code.  It lives for the code, it dies for the code and nothing beyond what is needed to support that code should *ever* be added.  Do not fall into the trap of creating a complex test application that can handle dozens of bits of code.  This is unmanageable and defeats its own purpose as the code in it gets further removed from the real code over time.  Disk space is cheap, don’t be afraid to create a few folders.

Test apps are not to be confused with Test Harnesses and utilities like NUnit.  These tools are invaluable but are meant to be applied to the full body of code.  The test app is only for one little bit of functionality.  If you really feel that compelled to use NUnit everywhere, you can wire it into your test app as well 🙂

Why a test app?

  • Lightning fast revisions and compilation. Since the test app is so small, you can test, fix, build and retest several times in the same amount of time that it would take to compile the main application once.
  • Fully Isolated debugging. If it breaks in the test app, you are 99.999% sure that it is your code. In the main app, things can break for an almost infinite number of reasons. Perhaps someone checked in bad code, the database is down, someone playing with test data – all of that is completely unrelated to the code you are working on but slows you down nonetheless
  • You get full debugging abilities on the test app. This may not always be the case in your test environment. In many test environments, you are limited to writing to logs for everything. This is nice, but far less useful than a fully interactive debugger.
  • Rich testing. You can define any number of scenarios to run your new functionality through simply by copy-pasting a line a code in the Shell and changing a parameter or two.
  • Performance testing. If you suspect that your code might be a bit slow, it is trivial to set up loops and timers in the test app. I can run my code through millions of iterations with almost no effort in the test app. Try setting up this type of test in the main application and you will immediately see the perks.
  • Easy experimentation with unproven algorithms. There are times that you need to feel your way to a solution in code. This provides a completely safe environment to do this in. Trying to do this in the main app often requires a lot of instrumentation in order to call the new logic, all of which might just have to be undone if the algorithm doesn’t work.
  • Known code. Because of how small the test app is, odds are good that you will walk through the code in the debugger quite a few times while working on it. This simple task might be all but impossible in a shared dev environment.
  • Generic. In creating your new code so that it can be tested in the harness, you might discover that it works just fine if you define a parameter as IEnumerable instead of ArrayList or a custom collection, making it potentially even more useful to the main application.
  • There’s also a free bonus benefit at the end of the article

The Code

Here is where a little design goes a long way.  Write your new logic so that everything can be passed into it as parameters (your eventual production code will thank you for this!).  If you are creating a new class, provide constructors that allow it to be set up with enough information to make the class think it is in the real app.  Ideally, your new code should know nothing about it’s environment other than what is fed to it.

ABOVE ALL – TREAT THIS CODE LIKE YOU WOULD PRODUCTION CODE.  No hard-coded values, no assumed paths, etc.  If you need something like that, pass it in as a parameter.  This is so that you can literally copy-paste this logic into the larger application unchanged.

The Shell

Create the absolute least amount of code needed in order to support the functionality you are about to write.  By this, I mean hard-coded parameters and the like.   The shell is meant to be dirty, ugly code that you wouldn’t show to anyone – even for money.  Its sole reason for existing is to be a fast way to call your new functionality, not as a show piece for CodeProject.

Connect it up

Go back to your shell code and connect it to your new code.  Does it work?

Test it and test it again

Once you have it working the way you want with the expected inputs, you can play all sorts of games with the code.  Hard code values in the parameters to test out error handling or to simulate conditions that may be very hard to reproduce in the main application.  Testing and fixing these sort of issues in a test app is easily 10 times faster than trying the same thing in the main application.  The bigger the main app, the greater the advantage of using a test app

Drop it in

Since you went through the effort of putting your new code into its own black box of sorts by parameterizing so much of it, dropping it into your real application is almost a no brainer – just feed it the parameters it needs and you are pretty sure that you have a reasonably well tested body of logic in place.

Revisit

Once you have the code in there, run it through it’s paces in the main app.  If it breaks because you have, say, a null coming in where you hadn’t planned for one, its nothing to fix.  Just create another test in your Test App to simulate the new situation and debug away happily.  When fixed, drop the updated code back in again.

I Object!

This is great and all, but isn’t this actually extra coding?  Yes, slightly.  The shell might take you 30 seconds to create if you type really slow, and then the main hooks another 2-3 minutes or so.  The rest of the code you have to write anyway so you might as well do it in a place where you can beat the snot out of it easily and quickly.

No way, my test environment is SO complex.  That might be true, but that doesn’t mean the functionality you are writing needs to know about it.  Odds are good that it can be abstracted pretty easily.  Not always, but more often than you might think.

Enjoy!

I have written several thousand test apps in the 12 or so years that I have been using them and have never thought even one to be a waste of time.

Freebie benefit

Remember I said that you should never show your test app to anyone?  That’s not entirely true.  There is one person who will be *very* grateful for it – the guy who comes to you and asks: “How do I use your object?”  You just toss him your test app and he can immediately see not only how to call it, but what you had in mind for it when you created it.  For most of us, a quick example with actual code is worth a 1024 words.

Speaking of which, here’s a somewhat contrived example of a test app:

Sample Code