• Excel Web Access Drill-through to SSRS Report

    April 2, 2007 / 1 Comment »

    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]")...

  • Report Viewer SP1 Redistributables

    March 28, 2007 / No Comments »

    As I mentioned in a previous blog, Visual Studio 2005 SP1 includes updated versions of both the WinForms and ASP.NET versions of the Report Viewer control. Microsoft has just released full and upgrade redistributables for the Report Viewer SP1.

  • Roll Your Own UDM Schema Generator

    March 22, 2007 / No Comments »

    Documenting the UDM schema is a common requirement but to the best of my knowledge neither Microsoft nor third-party vendors provide schema generators. While you are waiting for a commercial tool to fill in the gap, you can roll out your own schema generators. Thanks to the Analysis Management Objects (AMO) library, this task is remarkably simple to implement. For example, here is a .NET code snippet that builds upon the DisplayObjectNames sample (included in the SSAS samples). The code loops through all SSAS databases on the server, traverses the dimension attribute hierarchies and measures and displays their names and source columns. foreach (Database db in server.Databases) { Console.WriteLine("{0}", db.Name); // Display Dimensions Console.WriteLine("\tDimensions:"); foreach (Dimension dimension in db.Dimensions) { Console.WriteLine("\t\t{0}", dimension.Name); // Display attributes Console.WriteLine("\t\t\tAttributes:"); foreach (DimensionAttribute a in dimension.Attributes) Console.WriteLine("\t\t\t\t Name: {0}Source: {1}", a.Name.PadRight(50), GetKeyCollection(a.KeyColumns)); } // Display Cubes Console.WriteLine("\tCubes:"); foreach (Cube cube in db.Cubes) { Console.WriteLine("\t\t{0}", cube.Name);...

  • Get More Out of SQL Server Reporting Services Charts

    March 21, 2007 / No Comments »

    One must-read SSRS resource which I missed was the excellent article "Get More Out of SQL Server Reporting Services Charts" by Robert Bruckner from the Reporting Services dev team. Now I have something to point folks to for how-to chart-related questions.

  • MVP Summit Is Over

    March 19, 2007 / No Comments »

    MVP Summit is over and I am back in Atlanta. I had great time meeting other MVPs, MS employees, and learning about the future in MS terms. I posted pictures from the event. Some interesting statistics given to us by Microsoft: The MVP Summit is the largest event held at the Microsoft campus 1889 attendees were registered to attend 88 countries will be represented (based on registrations)...including for the first time: Algeria, Andorra, Bahrain, Bangladesh, Bolivia, Cyprus, El Salvador, Gibraltar, Honduras, Latvia, Morocco, Nepal, Oman, Rwanda, Tunisia, UAE, Uruguay and Vietnam 533 Sessions were delivered 956 Microsoft employees pre-registered as speakers/attendees 10 Microsoft Executives and 4 Technical Fellows  Next stop – TechEd USA 2007. See you in Orlando.

  • 2007 MVP Global Summit

    March 11, 2007 / No Comments »

    Joining some 2,000 other MVPs from all around the world, I am heading tomorrow for Seattle to participate in the annual MVP Global Summit. It's time to shake hands, take pictures, put names to faces, and soak up as much as possible about what's coming up in Katmai (the next SQL Server release). Unfortunately, the last part is under strict NDA so I won't be able to talk about it (not until the first public CTP is out that is). I will also present a spotlight session for the Reporting Services group during which I will share my feedback about the product harvested from a real-life project. Looking forward to meeting these guys at last! Hey, BillG will be also kind enough to talk to the MVP Community and this will be one of his last public appearances as a Microsoft Chairman. A sort of a farewell party for Bill....

  • Report Builder and Text-based Calculated Members

    March 10, 2007 / 2 Comments »

    Although not a common practice, UDM supports defining text-based calculated members. For example, here is an useless but valid text-based calculated member: Create Member CurrentCube.[Measures].[Hello] As "Hello"; Much to my surprise, a text-based member won't show up in Report Builder. That's because the Report Builder defaults the member's data type to float and the text is simply "lost" when the report is run. How does the Report Builder determine the field data type with UDM as a data source. With regular measures, it's simple: the Report Builder derives the field type from the DSV data type. It seems that for calculated members, however, the Report Builder always defaults the data type to float. While there may other workarounds to resolve this issue, one way is to "correct" the Report Builder invalid assumption about the member data type. Since one would typically auto-generate the Report Builder model when UDM is used...

  • SQL Server 2005 SP2 Refresh

    March 7, 2007 / No Comments »

    The initial release of SQL Server 2005 Service Pack 2 (SP2) contained an issue that caused maintenance plan cleanup tasks to remove data before the specified cleanup interval. Install this update to correct the interpretation of existing cleanup task intervals, and to avoid this issue in new cleanup tasks. After you install this item, you may have to restart your computer. If you already installed the SQL 2005 SP2 released on Feb. 19th, 2007: Install the General Distribution Release (GDR) on top of that build to fix the problem.  No un-installation of the old SP2 is needed. GDR download location: http://www.microsoft.com/downloads/details.aspx?FamilyId=e2c358a1-ecc4-4c49-8f65-daa6b7800eec&displaylang=en If you have not upgraded to SQL 2005 SP2 official release: Microsoft has released the refreshed SQL 2005 SP2 at Microsoft Download Center last night (March 5th, 2007): http://www.microsoft.com/downloads/details.aspx?FamilyID=d07219b2-1e23-49c8-8f0c-63fa18f26d3a&DisplayLang=en This SP2 (9.00.3042.00) includes the fix for the database maintenance job problem Microsoft has identified on the SP2 build we...

  • Calculated Member as Regular Measure

    March 5, 2007 / 3 Comments »

    One of my favorite modeling tricks when I need an UDM calculated measure is implementing it as a regular measure by creating a named calculated column in DSV. I set the expression of the named calculation column to NULL to minimize storage. This approach has a couple of advantages: It allows you to set a default aggregation function, e.g. SUM(). In comparison, a calculated member (created with CREATE MEMBER) cannot have a default aggregated function. This could be useful if you need to perform a calculation only at the leaf members of a dimension and then sum the results up. You can scope across several measures. For some obscure reason, the SCOPE operator doesn't support calculated members. For example, the following statement will trigger a "The Measures dimension is used multiple times" (or similar) error on deploy if Member 1 and Member 2 are calculated members: SCOPE ( [Date].[Date].[Date].Members, {...

  • Presenting at TechEd 2007

    March 3, 2007 / 2 Comments »

    Microsoft has just let me know that my proposal for a TechEd 2007 breakout session has been approved. I've been honored to present Microsoft SQL Server Reporting Services for four consecutive years at TechEd. This time, I will share practical lessons and tips harvested from report-enabling a real-life application. Here is the formal session description (not yet on the TechEd website): One of the most prominent feature of Microsoft Reporting Services is its open and extensible architecture that allows developers to report-enable easily their custom applications. Join this "from-the-trenches" session to learn how we leveraged Microsoft Reporting Services 2005 and SQL Server 2005 to add ad-hoc reporting features to our line-of-business application. Topics covered include: Understanding the importance of the Report Definition Language (RDL) Building an RDL object model to abstract access to RDL Integrating the application middle tier with the Report Server web service Implementing flexible data retrieval with...

Training

We offer onsite and online Business Intelligence classes! Contact us about in-person training for groups of five or more students.
For more information or to register click here!

Books

Learn Power BI at your own pace with our latest book Applied Microsoft Power BI. Targeting information workers, data analysts, pros, and developers, It is designed as an easy-to-follow guide for learning how to implement BI solutions spanning the entire personal-team-organizational BI spectrum.

Syndication