SQL

What's new in SQL Reporting Services 2008

Submitted by Robert MacLean on Mon, 10/20/2008 - 09:37

On Saturday I did a 20min (which is basically nothing) presentation on what is new in SRS 2008 at Dev4Devs! The feedback I have gotten has been very positive and I personally learnt more about what it takes for me to present well. That said some people asked me afterwards about the slides and content I used and the reality is that I didn’t have a single slide! The truth was Eben indicated when I volunteered that developers don’t like slides, so I took it as a challenge and did all my “slides” in SRS! For those who couldn’t attend, here is the run down on what I covered!

Introduction

The first “slide” was really about what I was covering and also some of the user groups out there! I must say sorry to Craig for leaving out www.sadeveloper.net. For those wanting the links to the groups they are:

srs1

For the Designers

Building the slide also gave me the platform for the first section, what  is new for designers! So switching to edit mode I was able to demo the fact that textboxes can now contain rich text, so the entire of the title and agenda was a single textbox with different font styles and positioning. Below that you can see the communities worth supporting with two different colours! In SQL 2005 you were limited to a single font configuration per textbox so to do the above in 2005 would have take 7 textboxes!

Next I showed off the new design surface improvements which make it no longer feel like a annoying grid but a real smooth surface. There is also enhancements like the guide lines which snap you to other design elements and the distance tooltips which show your distance from other elements (see below).

srs2

I then showed off my favorite feature, UNDO and REDO. I know it seems small but the UNDO in 2005 reloaded entire reports and took forever to do, now there is a real instance UNDO!

We’ve been Robbed

Next I went into what had been taken out… sort of Smile First was the data tab (missing highlighted in red below) which has moved to it’s own window. It feels so much slicker there! And then I spoken about the fact Table, Matrix and List were gone and what the table, matrix and list tools are now! I won’t retell the story on that because Teo Lachev has a better description at his blog.

srs3

Pretty Pictures

From there I moved into showing off the new Dundas based charting and gauge controls which make your reports look super slick and demo'd why gauges are great for showing multiple pieces of data at the same time (in my demo the distance and time of my exercising):

srs4 

The Boring Slide

Lastly I ended with a “slide” on things I couldn’t demo but are noteworthy and I included a smiley rating scale on how noteworthy they are:

srs5

  • No More IIS: SRS 2008 no longer requires IIS to run! It actually has it’s own web server and this means that not only does it scale better, it also is a true middle tier application.
  • Memory Management: Because SRS 2008 is in charge of everything now and not needing IIS, you can limit how much RAM is used!
  • Data Driven to SharePoint: You can now use a data driven subscription (i.e. one which is based on data in the DB) to publish to SharePoint.
  • Support for Teradata: A lot has been said on this, so I assume it is important (I saw some heads nod when I spoke about it in my talk), but as I have never used Teradata it got the confused smiley.
  • Per page rendering: This is big, it no longer renders the entire report at once. Now it just renders a page at time! Great for those massive reports.
  • Custom and forms based authentication: A really great feature for hybrid environments! Also combined with no more IIS those Kerberos issues between CRM and SRS should be a thing of the past!
  • Export to Excel: In my series on complex report building, the last part mentioned the horror that was exporting to excel and how sub reports generated ugly grey blocks! Well that is no longer the case. YEAH!
  • Export to CSV: Has been improved to export just data. I did point out that there is limitations (like values from gauges will not be included if you use CSV) so be careful.

I lastly mentioned a new tool, called Report Builder which is an 18Mb download and gives an Office (ribbon bar) experience to building 2008 reports. It is really great in that it has low overheads (no Visual Studio requirements), it has all the design surface features I mentioned at the start and it is very easy to get up and running. It does require a full SRS server to be available if you want to run the report, so no preview mode like in SQL BI Studio. That said it is great for power users and I see the real value coming in the future when you need to work with old reports and don’t want to install old versions of Visual Studio, like we have to with 2003 and 2005! I mentioned on Saturday it was RC1 status, we’ll it seems that was a lie because at 3:30am Saturday they released the RTM version!!!! For more and to download it (it’s free) see: http://blogs.msdn.com/robertbruckner/archive/2008/10/17/report-builder-20-release.aspx

Finally thanks for Eben and Ahmed for arranging the event and EVERYONE who attended!

Dev4Devs - This Saturday

Submitted by Robert MacLean on Wed, 10/15/2008 - 08:55
A few weeks ago I posted about the Dev4Devs event coming up, well it is this Saturday! I will not only be attending but I will also be doing a presentation on what’s new in SQL reporting services (SRS) in 2008! It will be a nice quick level 100 to 200 demo of the new stuff.

I also saw on Eben’s post that two of my absolutely favorite presenters are speaking, namely Rudi Grobler (he is speaking on what’s new in 3.5 SP 1 for client development) and Brent Samodien (he is speaking on consuming ASP.NET data services using AJAX… which I hope means he will show ADO.NET Data Services, will have to wait until Saturday to find out).

Complex Reporting: Part 5 - Wrap up

Submitted by Robert MacLean on Thu, 08/14/2008 - 19:28
Over the last four parts (1, 2, 3 and 4) you’ve seen how to build this complex SRS report, but there are a few points to be aware when building reports like this, first this is not a really scalable solution, both in computation cost and development cost. Sub-Reports add a lot of over head to the CPU and RAM and the fact that for each year you want to include you have to add more SQL. Thankfully in this example we had 3 years and 3 commodities. In the real world, which inspired me, it was also only three years with a low number of items (sub 25) so the performance considerations were ok.
The next problem is the Export To option in SRS, where you can export to a variety of formats such as PDF, Excel etc… Having sub-reports within table cells is NOT supported by the Excel one, you just a gray block were the sub report would be. PDF, TIFF, and web archive all work fine. If your users are expect to export to Excel you need to rethink using this.
Lastly you have to make sure you set the width and height and lock the cells on the report and sub-reports to not expand and shrink. If you don’t get the width/height the same all over what happens is your cells don’t align between the report and sub-reports and it looks shocking.

Complex Reporting: Part 4 - Introducing Sub-Reports

Submitted by Robert MacLean on Mon, 08/11/2008 - 21:28
A sub report is a report which is embedded into another report, basically it’s SRS answer to IFRAMEs. This should not be confused with the ability to drill through from one report to another, as those render separately and are provided separately. With sub-reports the main report renders, then the sub-report then the output of all of those is combined to produce a single report. Sub-reports are normal SRS reports as well, so they have the same features as other reports.
So how do we use them? Well if we look back at our previous image where we had the fields scattered all over there is a distinct pattern here, basically there are 5 blocks (Q1, Q2, Q3, Q4 and Total) in a row under each fiscal year for each commodity/deal, and the horizontal total row at the end is basically the same.


What we can do is merge those five cells on the table together and insert a sub report into that merged cell, and since all the groups are the same they can all point to the same report. The exception is the horizontal total row, which is the same in look is calculated a little differently. So we only need two sub-reports and we would structure it as follows:


So how does the sub report know what to show? Well remember it’s a normal SRS so you can just parameters to it, and because it’s in a cell of a table you can just access the values from row that is being rendered. So all we need to do is pass two parameters, the fiscal year and the commodity.
Now the complexity is easy since it’s just a simple bit of SQL using the same UNION stuff as we used before:
SELECT     f1q1value, f1q2value, f1q3value, f1q4value
FROM         Deals
WHERE     (deal = @projectid) AND (fiscal1 = @fiscal)
UNION
SELECT     f2q1value, f2q2value, f2q3value, f2q4value
FROM         Deals
WHERE     (deal = @projectid) AND (fiscal2 = @fiscal)
UNION
SELECT     f3q1value, f3q2value, f3q3value, f3q4value
FROM         Deals
WHERE     (deal = @projectid) AND (fiscal3 = @fiscal)
UNION 
SELECT '0','0','0','0'

If you read that and saw the last SELECT and went, WHOA, good for spotting it. What’s happening is that I always want a result regardless, so that I don’t get issues caused by missing fields. So by adding that and only selecting the top record I ensure that there is always a value, even if it is zero. The total column on the sub report is just a calculated field adding the four values up.
For the total row sub report, it’s basically the exact same except we are now wrapping the fields in SUM’s, that’s the only change. The last thing to make sure of is that for the initial table on the main report you get all the commodities for all the periods. To do that your SQL needs to take into all the possibilities like so:
SELECT     Deal
FROM        Deals
WHERE     ((fiscal1 = @FiscalYear) OR (fiscal2 = @FiscalYear) OR (fiscal3 = @FiscalYear) OR (CAST(RIGHT(fiscal2,2) AS Int) = CAST(RIGHT(@FiscalYear,2) AS Int)+1) OR
(CAST(RIGHT(fiscal2,2) AS Int) = CAST(RIGHT(@FiscalYear,2) AS Int)+2) OR (CAST(RIGHT(fiscal3,2) AS Int) = CAST(RIGHT(@FiscalYear,2) AS Int)+1) OR (CAST(RIGHT(fiscal3,2) AS Int) = CAST(RIGHT(@FiscalYear,2) AS Int)+2))
ORDER BY Deal
@FiscalYear is the name of our drop down we mentioned earlier and we use a little bit of SQL to get it into an INT and manipulate it to give us every possible combination.

Complex Reporting: Part 3 - Structure

Submitted by Robert MacLean on Sat, 08/02/2008 - 20:40
So in part two I showed what was needed, so now how do we actually build this report. Well if your gut said to use a table or matrix then you would quickly hit a problem because what data needs to be shown in each field is not constant. A picture may help explain what I mean. If we look at our previous example:

 

 

 

Our fields would have to look like this:

 

 

So note how F1Q1Value appears in a different place in each row, try building that in a table or  matrix. You just can’t! Also now think about the requirement for sub totals, going horizontal is ok in concept but going vertical is tough because you are adding the values of a lot of different fields together.

So how do you go about building this? Well the financial year indicators at the top could just be calculated from the value from the drop down and put into three text boxes. The next row, which is the header row for quarters could also be text boxes or the header row of a table since it doesn’t change, then we would need a table below that for the commodity names and values, but how do we get the values of the field to dynamically change based on the commodity? If you go back to part 1, you’ll remember I mentioned sub-reports, but I will get to that next time.

Complex Reporting: Part 2 - Report Requirements

Submitted by Robert MacLean on Mon, 07/28/2008 - 17:50
In part 1 we looked at the requirements at a high level and also looked at the data structure, in part 2 we will now look at the finer details of the requirement. First the user needs to be able select from a drop down, which contains a list of all possible fiscal years, the first fiscal year they want to see, next the report must show blank spaces for commodities which do not feature in that fiscal, but may feature in future fiscals and lastly we need sub totals for years and commodities.
So the drop down is a simple SRS parameter which is connected to a data set which does a simple SQL command:
SELECT DISTINCT Fiscal1 AS Fiscal FROM Deals 
UNION
SELECT DISTINCT Fiscal2 AS Fiscal FROM Deals 
UNION
SELECT DISTINCT Fiscal3 AS Fiscal FROM Deals 
and then set the value and name of the drop down to the Fiscal field.
The next requirement is actually telling us something very subtle, which is at the core of the complexity. That is that if I choose FY06, the report will render FY06, FY07 and FY08 and that if a commodity starts in any of those years it must be shown. So we are not ONLY showing what starts in FY06, we are showing things that start in FY07 and FY08 too. This means our final result should look like:


If you are a regular SRS developer your gut is telling you that it is just a table, or maybe a matrix, unfortunately it’s a little more than that. But we will look at that next time.
The last requirement, sub totals, is very easy and logical (especially looking at the image above) so I am not going to go into detail about that.

Complex Reporting: Part 1 - Introduction

Submitted by Robert MacLean on Thu, 07/24/2008 - 19:29
Recently I got the chance to build a report using Microsoft SQL Server Reporting Service 2005 for a customer, that in itself is nothing fantastic. However this was one of the most complex reports I have ever had to build, for two main reasons:
  1. What needs to be displayed is so simple in concept, that you get misled into thinking it’s easy.
  2. To do this you need to use a component of SRS which is seldom needed, sub-reports.
But before I give away too much let me explain what the report should show, the customer I am using in this series, tracks the deals for commodities over a three year period, broken down to each quarter. So they want to know in year 1, quarter 1 they sold x values worth of beer, and so on for all 12 quarters. As they are constantly adding new commodities the year 1 is not the same for each commodity. So they may start selling beer in 2008 and chickens in 2009, so they would only sell beer until 2011 but chickens would be sold until 2012. And all we need is a report to show this.
Disclaimer: Most of this has been changed to protect the innocent, so the report images are drawn in Visio and are not real images, the customer doesn’t sell commodities (especially not coffee and beer) and the data is completely faked.

So lets look at the data structure, it’s a simple single table (no I didn’t have anything with the design of this), which has a Deal field for the name of commodity; Fiscal1, Fiscal2, Fiscal3 store the years that relate to each year we are tracking and the FxQxValue is the value for that year and quarter.

Lets look at how this would look with sample data, this is the same data we will use for the rest of the series:

Next time we will look at the report requirements itself and start to look at how to build it.

SQL Server Express with Advanced Services

Submitted by Robert MacLean on Fri, 11/02/2007 - 09:45
This really is the worlds most needed, most hidden and most marketing bs item ever. For those who don't know SQL Server Express is the light version of SQL Server, basically runs a database server but nothing else, not even a management tool. However it is free to redist with apps and it ships with Visual Studio etc... so it's out there a lot.
But you can just feel there are things needed to get it from almost useless to void filling tool (management tools being number 1). Anyway someone at MS realized this at some point in the last year and decided to bring out the Advanced Services edition (how is that for marketing spin, it should be the complete edition but no we need it to be advance, even though it install stripped down features of everything still).
Anyway above the standard express you get:
  1. Stripped down management tool
  2. Reporting services server (the /ReportServer, you don't get /Reports)
  3. and full text search.
These features are so advanced it's insane, oh wait they are all in the standard SQL server from day one.
Anyway if you need this you can get the 250 odd MB download, which you can just run on your vanilla SQL express to upgrade it, from
http://www.microsoft.com/downloads/details.aspx?familyid=5B5528B9-13E1-4DB9-A3FC-82116D598C3D&displaylang=en
More details on this is available at
http://msdn2.microsoft.com/en-us/library/ms365248.aspx