It was a rainy Wednesday afternoon when my cell phone rang. The ring tone told me that the call had been forwarded from my office. I suspected it was someone in desperate need of my services. The female voice that came through the earpiece told me that I was right. It seemed that one of her admin staff had gone missing, and she wanted me to find him.
She told me to meet her at her office in the headquarters of Conglomerated Health Services, Incorporated (CHSI to those in the know). The building was huge. I could tell that a good deal of money flowed into the place. With any luck, I'd make a bit of it flow out again—right into my pocket.
Veronica Noonan was the woman who'd called me. She was the director of CHSI's Server Administration Department. My first impression of Noonan was that she knew her CAT-5 from her FireWire. My first impressions are always right (a handy skill to have in this business).
Noonan's desk was incredibly neat. The network topology charts on her office walls were all up-to-date. Her server documentation was filed sequentially by IP address. This wasn't the type of person who goes around misplacing her employees.
She said that her Reporting Services administrator, Greg Parker, hadn't been seen in more than a week. He'd been with the company for a number of years and was a valuable employee. He'd recently taken on the project of rolling out SQL Server 2000 Reporting Services and administering the Reporting Services server. “How was the rollout going?” I asked. I'd seen too many good implementation plans gone bad, too many network admins and project managers who kept getting in deeper and deeper until the only way out was to leave town in the middle of the night without a trace. Turns out, this wasn’t one of those cases.
“The Reporting Services rollout has been a fantastic success,” Noonan replied. “Acceptance has been tremendous throughout the company. We expect to have all the enterprise reports converted to Reporting Services by the end of the year.”
“And Mr. Parker is responsible for doing that report conversion?” I offered.
“Oh, no,” she corrected. “We have report authors in the ERP team, the Web site group, the business intelligence team, and the Personnel Department. They’re all converting existing reports and cranking out new ones. Greg is simply responsible for deploying and managing those reports on the Reporting Services server.”
“So all this deploying and managing came to a halt when Parker disappeared.” I surmised.
“That’s just it. The reports are still being deployed. The security changes are still happening. It’s almost as if Greg is still here, but no one has seen him.”
That last sentence started a vague flicker of a hunch somewhere back in my skull, but I needed more fuel for the flame. “Would you show me where Parker was last seen?”
Noonan lead me to a cubicle that had Gregory S. Parker etched on its nameplate. I looked in. In his inbox grew a tower of forms, which had a pronounced lean that was bringing it dangerously close to toppling onto the Mountain Dew cans that littered the floor. I carefully examined one of the forms that had Reporting Services Deployment Request written in large block letters across the top.
There was another Mountain Dew can next to the keyboard. I took out a handkerchief and picked it up. It still contained some of the yellow liquid. It was warm and getting warmer, but then again, so was I.
I next asked Noonan to take me to the Reporting Services server. I now suspected that Parker was a victim of his own project’s success. That success, along with his commitment to get the job done and an unfortunate lack of training, had doomed him to disappear from the world of the living. When we had successfully navigated the maze of server room equipment racks and rounded the final corner, my suspicions were confirmed. There, in front of the Reporting Services server, furiously navigating Report Manager screens, sat Parker.
It took several minutes for Noonan and me to break into Parker’s sleep-deprived haze. When we finally did, he told us his story. “I set up a folder structure in Reporting Services that mirrors our departments and workgroups at CHSI. There’s a domain local group set up for each of the main folders in Reporting Services. I configured the security for each folder so that only those users in the corresponding domain's local group have rights to view that folder. People in the Personnel Department can see only the Personnel folder. People in the Sales Department can see only the Sales folder.”
“I get the picture,” I interrupted. “It sounds like the cat’s pajamas. So tell me when it started to go south.”
Parker continued. “I started to deploy reports on the server. I found that every report was used by more than one department. I’d have to deploy each report and its shared data source to 10 or 12 folders so that all the departments could have access. That took some work, but I could handle it. Then the revisions started.
“It seemed that each and every user wanted changes made to their reports: new columns, different fonts, revised formats. Why couldn’t they leave things the way they were?”
Parker hesitated. He’d been through a lot.
“Users can be very cruel,” I agreed.
Parker went on. “I had to update the reports in every location each time they changed. Then the shared data sources had to be updated with changes to the server names and logon credentials. There were just too many of them!”
It was then that Noonan chimed in. “Greg, why didn’t you tell me? You didn’t have to go through this alone.”
Parker stared at the floor, unable to meet her gaze. “I didn’t want you to think that I couldn’t handle the assignment. I didn’t want you to think that I was weak.”
“You’re not weak,” I offered, “you just need a bit of know-how. You need to find the missing link, so to speak.”
He looked up. “The missing link?”
“Mr. Parker, your report-management scheme is sound except for one thing: linked reports.”
“You mean the ability to nest one report layout inside another?” Noonan interjected.
“No, you’re thinking of subreports,” I corrected.
“Is that where the user clicks on an element in one report to navigate to another report?” Parker asked.
“No,” I answered. “Reporting Services calls that drill-through. I’m talking about linked reports. With linked reports, you can deploy a report and its shared data source in a single location on the report server—sort of like a master folder. Then you can create links to that report in each of the department folders. The links look just like typical report entries in each of the folders. When a report or a shared data source changes, only the items in the master folder need to be updated. The links are always pointing to the up-to-date version of the reports found in the master folder.”
The overworked Reporting Services administrator was astounded. “How do you do that?”
I took over at the keyboard for a quick demo. I created a folder called Master in the Home folder on the Reporting Services server. I then moved a report and a shared data source into that folder. Next, I opened the Properties page for the report.
I explained the process to Parker and Noonan. “Use the Create Linked Report button to set up the link. After you enter the name of the report and a description, use the Change Location button to create the link in one of your department folders . The linked report shows up in the department folders just as if it were a regular report. The only difference is the small chain link that shows up on the report icon. You can even set different parameter defaults and execution properties for each linked report.”
Parker soon had his entire report structure converted to linked reports. He could then deploy report updates with ease and was ready to return to a normal life—or as close to a normal life as any SQL Server administrator has a right to expect.
It was then that my cell phone rang again. Another case, only this time it was the data that needed to be saved.
About the Author
B.I. Powers shares an office at Superior Consulting Services in Minneapolis with Brian Larson, a frequent presenter about SQL Server business intelligence and author of Microsoft SQL Server 2005 Reporting Services and Delivering Business Intelligence with Microsoft SQL Server 2005 (both from Osborne/McGraw-Hill). Powers is too cheap to get his own email box but can be contacted through Brian’s email address at [email protected].
