My breakout TechEd session BIN305 – Applied Microsoft SQL Server 2005 Reporting Services is scheduled for June 4 at 10:30 AM – 11:45 AM in room S320 A. That’s right – let’s talk reporting right after the keynote!
I hope to see some of you there.
If you follow the Microsoft .NET roadmap, you have probably heard about the forthcoming Language Integrated Query (LINQ) in .NET 3.0. LINQ will add query capabilities directly into the CLR and will be supported by both VB.Net and C#. This means that you will be able to use standard query operators directly from within your code!
To help you get started with LINQ, Marco Russo and Paolo Pialorsi wrote a book
Introducing Microsoft LINQ published by Microsoft Press. The book is expected to be published in mid-May. Meanwhile, the authors have set up a public forum (http://introducinglinq.com/) and they are eagerly awaiting your LINQ-related questions.
Oh, yes, I’ve made a tiny contribution to the book by reviewing a few chapters. I found the book to be a great introduction to LINQ. I particularly liked the code examples.
I had an interesting issue this week. I was working on a small test cube with financial data. The lowest grain in the measure group was the Account dimension which represents a customer bank account. I had to implement rolling 12 calculations on semi-additive measures (e.g. Avg Account Balance) by weighting the balances at the account level:
Since I had to weight the account balances by the number of the number of the days the account was active, I decided to scope at the account level, e.g.; SCOPE (Leaves(Account), Leaves(Date)…). Also, I had to use the ParallelPeriod() MDX function to sum the weighted balanced for the 12-month period.
This statement was causing the server to evaluate the Account All member which was triggering the formula evaluation for each account. Oops! The moral of this story is to test your cube initialization time in SQL Server Management Studio. As for me, I decided to reduce the scope (always a good idea) to Leaves (Date) only by pro-rating the Active Days in the data source so I could use the days in the month. This makes the formula universal on rows irrespective of the cube slice and makes the Cube Browser happy.
Thanks for Russell Christopher’s blog, I’ve learned that Microsoft donated an Analysis Services Load Simulator (ASLoadSim) sample project which lets you load test Analysis Services using the VS.NET 2005 test capabilities. While I am to try the code sample, I am very impressed by the whitepaper included in the sample which got harvested from Project REAL.
Chris Webb also contributed in the SSAS performance area by donating his MDX Script Performance Analyzer sample which I am yet to try as well.
Microsoft has released a cumulative update package (build 3161) for SQL Server 2005 SP2. Among other things, the update package fixes the SSRS black preview.
Nick Barclay is telling us that the book The Rational Guide to Microsoft Office Business Scorecard Manager 2005 that he co-wrote with Adrian Downes is out! You can use BSM to design scorecards that can be published to SharePoint dashboards or SSRS reports. As far as I know, this is the first BSM book which makes it even more appealing.
A bit outdated announcement perhaps but the Office team has released a Designing SQL Server 2005 Analysis Services Cubes for Excel 2007 PivotTables whitepaper. Instead of pestering you with how-to PivotTable questions, now your users can have proper documentation. A great deal of it discusses UDM implementation details but who is to say that you didn’t travel the extra mile… As for me, well I still wait for the Office team to implement a few more goodies like pre-filtering support for large dimension, before I get impressed by Excel 2007 as an SSAS browser.
I’ve been having fun lately with a dashboard page which has an Excel Web Access (EWA) web part connected to a PivotTable report. The PivotTable report is nothing to brag about. It displays a few KPI-related measures coming from an SSAS cube. PivotTable was converted to formulas to achieve a free-form report layout. So far, pretty straightforward stuff. That is, until the customer wanted the ability to drill through the chart to see a more detailed view from the cube.
Since to my understanding cross-EWA drillthrough is not possible because Excel Services is not URL-addressable (shame [:@]), I implemented this scenario by allowing the user to drill through from the Excel spreadsheet to an SSRS report. To implement this, I converted the Excel cell to a hyperlink which has the URL address of the SharePoint report using the Excel HYPERLINK function:
=HYPERLINK(“http://” & CUBEVALUE(“<connection name>”,”[Measures].[ServerName]”) & “:8080/ReportServer?http://” & CUBEVALUE(“<connection name>”,”[Measures].[ServerName]”) & “/Reports/ReportsLibrary/Top 50 Customers.rdl&DateCalendar=[Date].[Calendar].[Calendar Year].%26[” & PreviousPeriod & “]&rs:Command=Render”, “Growth in Customer Base”)
There are a few interesting things going on in this nasty expression that deserve more explanation. First, instead of hardcoding the server name, I added a ServerName calculated member to the cube that is mapped to the following SSAS stored procedure:
public static string ServerName() {
return Context.CurrentServerID;
}
Second, note the URL address of the report. Since SSRS is configured for SharePoint integration, the report path points to the WSS library where the report is deployed. Finally, I had to solve the nasty issue with ampersands in the member names b/c the drillthrough SSRS report takes a member as a parameter. If I just use & the Report Server will choke b/c it will think that it is a parameter placeholder. The hack is to escape & to %26 .
Here is what the link looks when rendered in the browser.
