SQL Server 2008 R2 August Community Technology Preview Available
Microsoft released SQL Server 2008 R2 August Community Technology Preview which includes the Report Builder 3.0 R2 August CTP redistributable.
Microsoft released SQL Server 2008 R2 August Community Technology Preview which includes the Report Builder 3.0 R2 August CTP redistributable.
Reporting Services SQL Server 2008 R2 features reports as Atom data feeds. This is accomplished with a new Atom Data Feed extension, as shown on this screenshot.
This extension produces an atom service document (an *.atomsvc file). It lists at least one data feed for each data region in a report. In fact, some regions, such as tablix with column groups (cross-tab report layout) may generate multiple feeds.
At this point, you probably imagine subscribing to reports and receiving feeds in Outlook when report data is changed. Unfortunately, you won’t get very far doing so. As it turns out, atom data feeds are different from Atom feeds that most applications, such as IE and Outlooks understand directly. So even if you extract the Url out of the service document and display the .atom rendering of a report in IE, the browser won’t show useful information because it doesn’t understand that the data rows in the data feed are the important part.
So, how are report data feeds useful then? At this point, the design goal for reports as data feeds was to support Gemini – the new Excel 2010 extension that lets end users build in-memory cubes. Gemini can acquire data from variety of data sources, including data feeds and SSRS reports. Here is what it takes to subscribe to a report feed.
Reporting Services, Analysis Services, Microsoft Office, SQL Server 2008 R2
This imports the feed data into the Excel spreadsheet. From there, you can use Gemini to build the in-memory model and analyze the report data.
One new SSRS SQL Server 2008 R2 feature that caught my attention is lookup functions. When reading the Reporting Services discussion list, I come frequently across questions that ask if it is possible to look up a value from a dataset other than the dataset the region is bound to. Excluding getting the first or last value (First and Last functions) or aggregate functions that work on the entire dataset, the only solution was to cache the dataset in a report code-behind code and use custom code to perform the navigation.
R2 will change this by introducing Lookup, LookupSet, and Multilookup functions. The lookup functions are best understood with an example. Take a look at this report (attached) which is based on the Sales Order Detail 2008 report that ships with the Adventure Works sample reports.
I added a couple of textboxes to the report. The order details section includes a Sales Person field that displays the sales person’s name. What’s interesting is that the sales person name is retrieved from the SalesOrder dataset that feeds the order header section. Just to address the question that I can almost hear you asking, no, R2 will not support joining datasets at a report level. Instead, I use the Lookup function which performs on-the-fly search, as follows:
=Lookup(Fields!SalesOrderID.Value, Fields!SalesOrderID.Value, Fields!SalesPerson.Value, “SalesOrder”)
I pass the field of the SalesOrderDetail dataset (Fields!SalesOrderID.Value) on which the lookup will be based. I pass the field of the SalesOrder dataset that will be matched. As a third argument, I pass the field (Fields!SalesPerson.Value) of the SalesOrder dataset that I need. Finally, I pass the name of the dataset (“SalesOrder”) that will be searched. The Lookup function is executed for each row in the source dataset.
In the order header section, I display the third item total from the SalesOrderDetails dataset by using the LookupSet function, as follows:
=Code.GetValueByRowNumber(LookupSet(Fields!SalesOrderID.Value, Fields!SalesOrderID.Value, Fields!LineTotal.Value, “SalesOrderDetail”), 2)
You call LookupSet in the same way you call Lookup. The difference is that Lookupset returns a variant array of all values that match. I decided to make the demo more interesting and let you specify the row index needed by adding a simple function to the report code.
Function GetValueByRowNumber(ByVal items As Object(), ByVal pos as Integer) As Object
If items Is Nothing Then
Return Nothing
End if
If pos > items.Length -1 Or pos<0
Return Nothing
End If
Return items(pos)
End Function
The expression passes the variant array returned by LookupSet and the row index to the GetValueByRowNumber. The GetValueByRowNumber returns the field at that row.
The Multilookup function, which I don’t demonstrate, accepts an array of source values to match and returns an array of the matched values that have 1:1 relationship to the source values, such as:
=MultiLookup(Split(Fields!CategoryList.Value,”,”) , Fields!CategoryID.Value, Fields!CategoryName.Value,”Category”))
[View:/CS/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/blog/7532.Sales Order Detail 2008.rdl]
In this and future blogs I’ll cover the most important new features in the forthcoming release of Reporting Services which will be included in SQL Server 2008 R2 (code-named Kilimanjaro or KJ for short). Let’s start with the most obvious new feature: the all new Report Manager UI.
SharePoint-like UI
The moment you open Report Manager, you will notice that significant work was done to make the UI more intuitive, a la SharePoint.
For example, similar to SharePoint, a drop-down context menu pops up when you hover on a report item that lets you quickly access relevant tasks.
Simplified UI
The management pages also received facelift. Microsoft has reduced the number of buttons and changed the tab styling so you are sure which tab is selected.
Although seemingly minor, I am sure you’d love the new look and feel. For example, I like the fact that I don’t have run a report or switch to Details mode just to get to the report properties.
Report Builder 3.0 ClickOnce
Another welcome enhancement is that opening a report in Report Builder, launches Report Builder 3.0 (more on it in another post) by default. Report Builder 1.0 is still installed but it’s not the default reporting client. You see the writing on the wall. Report Builder 1.0 days are counted and you better embrace the future.
AJAX Report Viewer
Another major enhancement is the debut of an AJAX-enabled ReportViewer control (developers will get the Visual Studio counterpart in Beta 2 of Visual Studio 2010). The control eliminates visible page reposts when interactive actions, such as drilldown, are initiated. The page fades out but it stays in place and a spinny lets the user know that an interactive action is taking place.
All in all, end users and administrators will enjoy the new Report Manager. You won’t have to switch to SharePoint integration mode just to get a SharePoint-like UI.
It turned out that for time being the CTP2 of SQL Server 2008 R2 which I announced yesterday is available for TAP participants only. It will be publicly available on Wednesday. Meanwhile, Robert Bruckner listed the new Reporting Services features which got implemented in CTP2. The biggy is the new map region that lets you visualize geospatial and geometry data.
In my first book I had a sample that demonstrated how to export a report as a data feed. Well, in R2 this will be natively supported. Reports as data feeds, anyone? Visual Studio 2010 Beta 2 will include an AJAX Report Viewer. I would hope for a Silverlight version but one step at a time.
Again, post your KJ-related question to the Reporting Services forum.
Microsoft just pushed CTP2 of SQL Server 2008 R2 build #1092.20 (code-named Kilimanjaro).
The SQL Server R2 (Kilimanjaro) discussion forums went live today. Your humble correspondent is the moderator of the Reporting Services forum. SQL Server R2 CTP 2 should be out on connect.microsoft.com shortly. Looking forward to your feedback!
So, how’s this not cool? Running SharePoint integrated with Reporting Services on Windows Vista! I’ve always had a grudge with SharePoint that it doesn’t run on a desktop OS. This forces you to install Windows Server or use a virtual image, both options far from ideal. When I went to TechEd I blurted by disappointment to a couple of SharePoint gurus who clued me in (this is why we go to conferences, right, to learn the latest hacks) but I didn’t have time to try it … until today.
The solution is to use the Bamboo’s workaround (OK, an ingenious hack) but it works like a charm. What about Windows 7 which has recently RTM’ed? Bamboo took care of that as well. I hope Microsoft will take a note and finally support officially SharePoint on a desktop OS. If SQL Server can scale down all the way to mobile devices, so should SharePoint.
Bamboo made my day today!
Reporting Services charting not enough? Nevron Software has released a Nevron Charting for Reporting Services which I haven’t personally tested yet. According to the vendor, here are the major features where Nevron excels in comparison with Reporting Services charting:
– Speed – Nevron Chart for SSRS is the fastest rendering chart inside Reporting Services – in both 2D and 3D modes.
– 3D Charting – Nevron Chart for Reporting Services has the best layout in 3D and the most advanced feature set in 3D – including: 11 predefined lighting modes, ability to specify element materials (control the way in which elements reflect light), largest charting types set in 3D, biggest set of shapes in 3D (applicable to pies, bars, markers, areas etc.).
– 2D Charting – Nevron Chart for Reporting Services is the only chart for Reporting Services to provide Adobe Photoshop like image filter effects, which are applicable to all chart elements – these include bevel, emboss, lighting filters etc. When it comes to shadows it is the only chart for Reporting Services which has soft shadows (based on Gaussian and Radial blur image filters).
– 2D/3D Consistency – Nevron Chart for Reporting Services is consistent in 2D/3D modes. For example you can map gradients, textures to elements in both 2D and 3D. Colors specified in 2D are inherited by materials in 3D. You can mix 2D and 3D chart areas in a single chart control.
– Visual Effects – Nevron Chart for SSRS introduces numerous Lighting Models; Orthogonal Projection with Depth Offset (perfect for creating 2D half imitation charts); Soft Shadows based on image filters; Blur, Glow, Lighting, Bevel and Emboss image filters.
– Numerous XY and XYZ scatters – Nevron Chart for SSRS includes the industry leading support for XY and XYZ scatters inside Reporting Services.
– XML Formatted Texts – Nevron Chart for Reporting Services supports XML Formatted Texts. This feature is applicable to all elements which display text. It allows you to mix fonts, mix bold, italic underline formatting and many other advanced features like different fill styles, shadows, image filters etc. – all inside a single label, title etc. XML formatted texts use a format similar to HTML, but with many more advanced options.
– Multithreaded UI – The user interface in design time is multithreaded. This allows for very quick editing of the component, regardless of the preview complexity
– Save/Load Templates (available in Vol.2).
– Ability to inject custom code and to author the generated chars programmatically (code editor) (available in Vol.2).
Unique charting types:
– Surface Chart Series (Grid, Mesh and Triangulated).
– XYZ Scatter Series (Bar, Line, Point, Bubble, Float Bar).
– Financial charting types Renko, Kagi, Three Line Break, Point and Figure (available in Vol.2).
Major Chart Axis Features:
– Multiple X and Y Axes.
– Axes can be docked or crossed.
– Many scale options including support for Categorical, Hierarchical, Logarithmic, Numeric, Date/Time and Time Line scales.
– Control over the range and appearance axes.
– Scale sections
Nevron is also working on SharePoint charting which will combine their charting technology with the new Nevron Pivot Data Aggregation engine.
Some of you shared with us your frustration with training budgets cut in half. Many of you couldn’t attend industry conferences this year. Many of you asked how we can help.
We are excited to offer online Business Intelligence classes – no travel, no hotel expenses, just 100% content delivered right to your desktop!
Our first class is Applied Reporting Services 2008. This three-day intensive event teaches you the knowledge and skills you need to master Reporting Services to its fullest.
Applied Reporting Services 2008 Online Training Class with Teo Lachev Date: August 31 – September 2, 2009 |
Attend this class for only $799 and get a free paper copy of the book Applied Microsoft SQL Server 2008 Reporting Services by Teo Lachev!
For more information or to register click here!