The Case of the All-Inclusive Collection

Over the years, I've amassed a modest collection of promotional gizmos: a BizTalk blinking LED pin, an Analysis Services stress ball, a Microsoft Office foam cube puzzle, a Visual FoxPro foam-rubber planet earth, and the must-have SQL Server Magazine detective sunglasses. But my pride in my collection paled as I entered the estate of Ms. Amanda Devano, a socialite and avid collector of anything that wasn't nailed to the floor. Ms. Devano's assistant, Albert Swenson, led me through a twisting maze of corridors lined with rows upon rows of doors, behind which lay the famed collections. Mr. Swenson identified each closed door as we progressed.

"We are now in the animalia hallway.This is the ursa room. In there, Ms. Devano has a stuffed male and female of every bear species known to man. And this is the lepidoptera room. In there, Ms. Devano has a mounted specimen of every butterfly and moth in the world.And this is the arachnid room. In there, Ms. Devano has a live, mating pair of every spider in existence. In here are the Beatles..."

"I know," I interrupted, "in there, Ms. Devano has a specimen of every beetle known to man."

"Actually," Mr. Swenson corrected,"in there, Ms. Devano has a copy of every Beatles song ever recorded. This is the music hallway."We reached the study, where Ms. Devano greeted me.

"You seem to specialize in collecting collections," I observed.

"I specialize in complete collections," Ms. Devano specified. "I like to see everything together in one place.And that is exactly why I asked you here. Mr. Swenson has used SQL Server 2000 Reporting Services to create a collection of reports to help me manage all my collections. He's even created parameters so that I can select the information I want in the report.The problem is, most of the time I don't want to select just one thing. I want to see everything; the whole collection, as it were."

"Yes,"I agreed,"I can see where that would be the case. May I see one of the reports?"

Mr. Swenson showed me one of his Reporting Services reports."For each of my reports, I've created one dataset containing a list of the items that Ms. Devano can include in the report. (Figure 1) I'm using that dataset to provide the available values for the SelectedItemID report parameter. (Figure 2) And I'm using the item identifier in the WHERE clause of a second data set to supply the data for the report. (Figure 3)

I informed Mr. Swenson that we needed to make only a few minor changes to provide the ALL option Ms. Devano was looking for. First, I used the UNION operator to include a second SELECT statement in the dataset that provided the list of available values. (Figure 4) The field list of this new SELECT statement contains a string constant of -ALL-for the item name and a numeric constant of -1 for the item ID. I put a dash at the beginning of the string constant to ensure that it would appear at the top of the list. I used the value -1 for the numeric constant to ensure that it wouldn't duplicate the ID of any existing item.

Next, I changed the SELECT statement that supplies the data for the report. (Figure 5) If Ms. Devano selects the -ALL-item for SelectedItemID, the WHERE clause will always be true and all the items in the collection will appear on the report. If any other item is selected for Selected-ItemID,theWHERE clause will be true only when the ItemID is equal to Selected-ItemID.

Finally, I pointed out that selecting all items from a list is much easier if you use the multivalued parameter feature in SQL Server 2005 Reporting Services. (For details about this feature, see Rodney Landrum's article "Reporting Services and Multivalued Parameters," January 2006, InstantDoc ID 48596.) Ms. Devano said she'd add SQL Server 2005 to her software collection as soon as possible.

As I turned to go, Ms. Devano offered me a 1927 Babe Ruth baseball card. It seemed she had a duplicate in her collection, so she could part with this particular card and still have them all. I declined her offer. I had my own little collection. And even though I don't have all of Microsoft's promotional giveaways, I have enough. Unless, of course, you have a blinking Microsoft CRM LED ball with sound effects you'd like to part with!

In this case, I added something so that someone could get more.In my next case,I add something so that someone can see less.