Josh Robinson's WebLog

June 2004 - Posts

Using a Strongly Typed DataSet with Null Values

Strongly typed datasets are great, I use them for all but the smallest datasets that I work with.  They are not as complicated to use as some people seem to think they are (this probably does not include the crowd that reads ASP.NET weblogs, I am sure that eveyone here uses them every day).  Over time I have realized that there are a few tricks and gotchas that you won't learn about from reading the “Introduction to Datasets“ topic in the Visual Studio help file.  One of these gotchas is when the data that you are returning contains null values.  You cannot just use the graphical schema designer, check the option for Schema | Generate DataSet and leave it at that.  You must open up the XSD and add a few small annotations to those fields which can contain null values.  Here is a good reference for the class generator annotations that can be used to specify what values to return in place of null.  The default option is to throw an error, so you might want to change that to something more workable.  (The error is “Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.”, not the most clear error you will see).  Another thing that you need to add is the minOccurs=”0” attribute.  This is basically the XSD equivalent of saying “Allow Nulls”.  After that, you should be good to go.

I recognize that this is not earth-shattering news, but it is something that I have run into and have had a little difficulty tracking down on Google.  I thought I would put it here for any others that might have similar problems.  I am sure that there are other ways of approaching the issue of nulls with strongly typed datasets, but this is one technique that has worked for me.

How to fix File | New menu in BI Portal XP

If anyone is using BI Portal XP from Microsoft, it is very likely that you have run into the following problem: You open the app, create a new view and then click on File | New again to expand the New submenu.  At this point instead of properly showing the submenu, you will see the entire current page repeated in the <div> that composes your expanded menu option instead of New File, New View, etc.

I finally have the fix for this, thanks to the kind help of Jegan.  Here it is:

1.) Open the menu.xsl file in the ...BIP\ext folder
2.) Go to Line 77
3.) Replace <a href='#' class="dropMenuItem"> with <a nohref="" style="cursor:hand" class="dropMenuItem">

That is all it takes, you should be good to go after that.

Using SQL Server Reporting Services within Sharepoint 2003

I know that there has to be some interest in the community in displaying SQL Reporting Services reports within Sharepoint 2003.  It seems as though there has been some vague mention that web parts for SQL RS would be released in the near future, or perhaps the SQL Server 2005 timeframe (if someone knows of these, let me know).  I am happy to report that there is a solid solution for integrating SQL Reporting Services into Sharepoint 2003 that is available right now in the form of BI Portal 2003.  This is an update to the BI Portal XP that is available directly from MSDN.  Strangely enough, the updated 2003 version has never really been mentioned anywhere, I stumbled on a reference to it deep within a reply to an OLAP newsgroup message.

The new version of BI Portal offers the ability to build dashboards with up to 4 panes.  These can consist of any type of Reporting Services reports, Office Web Components (pivot, chart, spreadsheet), MS Data Analyzer objects, URL's, documents, or rudimentary gauges that are driven by one of the other components.  The OWC components can be linked together such that if you drill into a pivot table, a linked chart will update to display the new drill level.

SQL Reporting Services functionality within the product is functional, albeit limited.  It handles authentication by passing your Sharepoint login to SQL RS.  It makes use of the SQL RS web service to show a tree view of available documents and pre-fill prompts.  You have the ability to show/hide the toolbar and prompts from the setup screen as well.  It then displays the report using the URL access method.  This is fine, except that they didn't trap the redirect so if you link to a new report within your existing report it will take over the entire screen unless you jump through a few hoops to get around that.  It is a good start for anyone who wants to view SQL RS reports through Sharepoint without having to completely develop a custom solution.  One downside is that unlike the older version of BI Portal XP, the source code is not provided with this version.

Best Practices for Handling Concurrency?

A while ago I developed a system for a call center in which I implemented pessimistic concurrency (ie, once a record was locked, no one else could touch it).  In that case we implemented concurrency in SQL Server using stored procedures to flip a bit in a table and effectively lock a record.  At the time, I wondered if it would be better to have a windows service that ran on the web server and served as “central dispatch” for this type of thing but due to time contraints was unable to explore that avenue.
I am going to be implementing a similar system in the near future and would like to know if any best practices or other resources exist in the community in the area of pessimistic concurrency.  If anyone has any resources that you can point me to in this area, I would be interested to see them.  I am also interested to know whether other developers consider it best to handle this type of activity at the database level, or in some type of other module such as a windows service?  Any thoughts?

-Josh

Two new Microsoft products for Business Intelligence

There are two new products out today from Microsoft that will help with developing Business Intelligence and Analytics solutions with Microsoft tools.  The first is an add-on for Excel called Office Excel Add-in for SQL Server Analysis Services.  At first glance this add-in simply enhances the Pivot Table feature with easy to use report templates and an enhanced way of looking at the back-end cube.  In the few minutes I have spent with it so far, it looks interesting, but seems to primarily be a pretty looking interface for standard Pivot Table features.  If I find it to be deeper than that, I will post my findings here.

Of more interest to me is the Office Business Scorecards Accelerator.  This is essentially scorecarding engine and metadata database that sits on top of Analysis Services and uses Sharepoint 2003 (either WSS or Sharepoint Portal) as its presentation layer.  From what I have seen, this is a slick tool that has some depth to it and can output a very nice looking dashboard.  I haven’t dug deep enough to see if it is a full-on attempt to compete with products like Dashboard Manager from Business Objects, but it looks like a great start.  It is built in ASP.net and as I mentioned takes the form of Sharepoint Web Parts.  It requires Analysis Services on the back end.

More Posts