Scary DBA Takes Over the World

October 15, 2010 at 2:58 pm (Uncategorized) (, , )

Or, at least gets translated into Russian.

I realize I’m effectively a 10 year old, but, come on, HOW COOL IS THAT?

My thanks to Artem Bikshanov for his exceedingly kind offer to do the translation.

Permalink 1 Comment

SQL Saturday New England: The Date

October 5, 2010 at 10:17 am (Uncategorized) (, )

Mark your calendars. Make your travel arrangements. Plan on being in the Boston area on April 2nd, 2011. Three of the local SQL Server users groups will be hosting a SQL Saturday event on that date. I’ll be blogging (a lot) with more details as they become available.

UPDATE: Fixed the date.

Permalink 1 Comment

Recursive Hiearchies in Reporting Services

September 3, 2010 at 8:44 am (SQL Server 2008, SQLServerPedia Syndication, Uncategorized) (, , , )

I am not a Reporting Services guru and nor do I play one on TV. I am however forced to be all things Microsoft Data where I work. So I frequently find myself stretching way beyond my abilities. I just had to get a report running that feeds from a web service and has a recursive hiearchy with customized aggregation on multiple fields with drill down to a different set of details. Yeah, through the internet I can see the SSRS monsters rolling their eyes at the ease of this task. But for us mere mortals it was work. Since I spent so much time learning how to do it, I thought I’d share.

XML as a Source

First, because we have a very paranoid (and appropriately so) group of PeopleSoft administrators, I couldn’t get direct access to the Oracle database. Instead, they provided me with a web service. Easy enough to consume, but it comes back as XML. Good news is Reporting Services can consume XML through a URL. Bad news is that it has a sort of proprietary XQuery language that is extremely obtuse (or I find it so, but then I’ve had trouble with SQL Server’s XQuery as well).

Setting up the Data Source is extremely simple. When you select XML from the Type dialogue, it’s going to ask you for a Connection String. Supply the URL. Done.

The work comes when you need to set up the DataSet. When you set the Data Source the Query Type will change to Text. No options. And you’ll be looking at a big blank box of nothing. My initial XML data set was this stacked hiearchy that had nested departments, accurately portraying the structure of the data. To query this XML you can do one of two things, set up the XML path as described in this excellent Microsoft white paper or allow SSRS to parse the XML for you. I tried working through the path, but I kept excluding parts of the structure. Basically I needed a method to recursively union the data within XML and, frankly, that was too hard. So I tried the automatic route. What’s the query look like for the automatic route?

*

That was tough. But, the same problem occurred. According to the white paper referenced above, letting SSRS figure out how to parse the XML means it will walk through and identify the first repeating group within the XML and that will be the structure it uses for the rest of the data. So, in my example, I have Departments and Personnel. The Personnel are inside the Department and Departments are inside Departments which have other Personnel… etc. It looks something like this:

<?xml version="1.0"?>
<ROOT_SEGMENT>
<REPORT_TITLE>Monster Hunters Status</REPORT_TITLE>
<SUMMARY>
<DEPTID>997</DEPTID>
<PARENT_DEPTID></PARENT_DEPTID>
<DETAIL>
<EMPLID>000001</EMPLID>
<NAME>Shackleford, Julie</NAME>
<TERMINATED>N</TERMINATED>
<RETIRED>N</RETIRED>
</DETAIL>
<DETAIL>
<EMPLID>000002</EMPLID>
<NAME>Jones, Trip</NAME>
<TERMINATED>Y</TERMINATED>
<RETIRED>N</RETIRED>
</DETAIL>
<SUMMARY>
<DEPTID>998</DEPTID>
<PARENT_DEPTID>997</PARENT_DEPTID>
<DETAIL>
<EMPLID>000003</EMPLID>
<NAME>Pitt, Owen</NAME>
<TERMINATED>N</TERMINATED>
<RETIRED>N</RETIRED>
</DETAIL>
<DETAIL>
<EMPLID>000003</EMPLID>
<NAME>Newcastle, Holly</NAME>
<TERMINATED>N</TERMINATED>
<RETIRED>N</RETIRED>
</DETAIL>
<SUMMARY>
<DEPTID>342</DEPTID>
<PARENT_DEPTID>998</PARENT_DEPTID>
<DETAIL>
<EMPLID>000022</EMPLID>
<NAME>Harbinger, Earl</NAME>
<TERMINATED>Y</TERMINATED>
<RETIRED>Y</RETIRED>
</DETAIL>
</SUMMARY>
</SUMMARY>
</SUMMARY>
</ROOT_SEGMENT>

Problem is, the first repeating group didn’t include the nesting. That was a deviation, so it didn’t read in the same way. What I had to do, in order to use the automated parsing, was flatten the structure, moving the SUMMARY areas outside of each other. With the new structure, the query returned all the data. Now the trick was to get the department hiearchy into the report

Recursive Hiearchies

Thankfully, after a bit of searching, I found this in the documentation on SSRS. It shows exactly what I needed, the, incredibly simple, method for creating a recursive hiearchy. The only trick was to have the Parent field stored with the child records. You can see that in the XML above, but the original didn’t have it. Once that modification was in place, it was simple. Follow the directions. In my case, DEPTID became the grouping field. To support other functions I also changed the name of the group so it could be referenced in functions.

Once it was created, simply going into the Advanced tab in the Row Groups property window and setting PARENT_DEPTID as the recursive parent was all that was needed.

Way too easy. But, how to get the drill down and the aggregates?

Drill Down & Aggregates

 With that in place, the query will return hiearchical data, grouping on the DEPTID and keeping the parent child relationships in order. To establish drill down, it’s just a matter of going into the Row Group properties for the Department group again. In the Visibility tab, you set the visibility to Hide and check “Display can be toggled by this report item:”

Once that’s done, the recursive groups are only displayed as the little plus signs expand and contract the groups. It works great. You can even get fancy and add an indent function as shown in this bit of the documentation. But, how to do get the totals to display recursively? Not tricky at all. In fact, pretty easy. Since the data coming out has a set of flags that I have to check for positive or negative values, I have to use a expression to check them anyway. Something like this:

=Sum(CInt(IIf(Fields!HIRE_FLG.Value.Equals("Y"),1,0))

Luckily, built right into the function is a method to make it work recursively, so that you get totals of the children displayed with the parent. All that’s necessary is to supply the group, which I named earlier, Department, and tell it to total this stuff in a recursive manner, like this: 

=Sum(CInt(IIf(Fields!Terminated.Value.Equals("Y"),1,0)),"Department",Recursive)

Put one of these with the appropriate field and you have a nice neat report.

Conclusion

To finish up, none of this is rocket science. It’s just a question of knowing where to go and how to put it all together. Being a newb when it comes to Reporting Services, I spent a lot of time struggling. Now, you won’t have to.

Permalink 5 Comments

A Milestone

August 1, 2010 at 7:00 am (Uncategorized)

I just passed 100,000 views on the blog. That’s from 400 posts over a period of time starting in March of 2008. You guys have posted 1129 comments. The average views per week went from 39 in 2008 to 160 in 2010.

I realize others out there are hitting 100,000 a week, but for some of us small time bloggers, this is a big deal.

I just wanted to say thanks for stopping by and I hope my attempts at understanding SQL Server, PowerShell, SCOM, spatial data, Visual Studio, ORM and whatever else I’ve posted about has been helpful in some way.

Permalink 6 Comments

Geek of the Week

November 6, 2009 at 2:05 am (Uncategorized) (, )

I made geek of the week. I’m sorry, but I’m excited by that.  I’m very proud to be a geek. Thanks guys.

Permalink 4 Comments

PASS Summit 2009 Key Note 2

November 5, 2009 at 12:10 pm (Uncategorized)

Patrick Ortiz from Dell, the platinum sponsor for the PASS Summit.

The goal will be to discuss the Dell Microsoft Practice, Configuration Management, Disaster Recovery and Consolidation. The MS Practice team has server tools, messaging & communications and collaboration & databases. Lots of work going on there.The see the CM, DR & Consolodation as linking cogs, if you trust the slide. They want to start implementing CM management systems. He’s driving the data suggested with Asset Data and physical server information. Then you drill down and collect SQL Server information followed by drilling down further to collect DB info. He’s gone on to show some of the benefits of CM. The idea behind CM is so that, once you’ve defined your servers, you can define disaster recovery and start looking into server consolidation. I hate to say this, but this is really the equivalent of eating your vegetables. This is pretty dull stuff. A key note needs to be a little more exciting than this. Show us some hardware, show us some flashing lights, some screens, something. It’s early in the morning. More.

Permalink Leave a Comment

No longer blind, or deaf… still a little dumb

July 15, 2009 at 3:49 pm (Uncategorized) ()

The Twitter account is back, but I don’t have an explanation for why I was shut down or why I was re-enabled. Nothing to see here. Move along.

Permalink 3 Comments

I’m Blind, Deaf and Dumb

July 15, 2009 at 2:02 pm (Uncategorized) ()

My Twitter account was suspended for some reason. I turned to waste three minutes of time and found that I was cut off. This is a bit of a shock.

Permalink Leave a Comment

Isaac Kunen Spatial Indexes Roundup

May 28, 2009 at 10:48 am (Uncategorized)

Mr. Kunen just submitted this to the comments on the first Brain Pain post, but it deserves a bit more obvious, top  of the bill placement. I’m just reading through the posts now, but I think it’s safe to say, these are must reads for everyone.

Permalink 3 Comments

Idera PowerShell Plus

May 8, 2009 at 10:46 am (Uncategorized)

I’ll have to come up with a detailed review of this eventually, but I just have to say this, right up front…. oooooohhhhh, aaaahhhhhhhh, Special… How special? WICKED Special!

Permalink 2 Comments

« Previous page · Next page »