• Indicators

    November 19, 2009 / No Comments »

    Often, reports need to show images that represent discrete values, such as an image that shows a KPI status. Previously, you didn't have another option but to use images that are dynamically changed based on the field value. Indicators, a new R2 feature, make this much simpler. In Reporting Services, an indicator is a simplified gauge region that lets you associate images with states. Although you can use indicators as stand-alone regions, you would typically nest them in a tablix, as the following report [View:https://prologika.com/CS/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/blog/8712.IndicatorDemo.rdl] demonstrates. The right-most column of this report shows the Gross Margin KPI status as an indicator. Authoring this report takes a few simple steps: Add a dataset that requests the Status property of the Financial Gross Profit Margin KPI from the Adventure Works 2008 Analysis Services cube grouped by Year and Quarter. Use a tablix region to show the KPI status with Year and Quarter...

  • Pagination and Rendering Enhancements

    November 17, 2009 / No Comments »

    R2 brings more control over pagination and rendering. A question that pops every now and then on the discussion list is how to name Excel worksheets for each instance of a row group. This scenario wasn't supported before R2. In R2, there are addition page break options, as shown in the screenshot below. One of them is PageName. In this case, I set the PageName property of the ProductCategory row group to the Category field. Then, I set up the BreakLocation of the ProductCategory row group to break when the product category changes. The screenshot below shows what the exported to Excel report looks like. As you can see, each worksheet is now named. If the row group spills on the next page, a new worksheet is added with the same name. Another welcome enhancement is the ability to reset page numbering. Suppose you have a master report that includes...

  • Shared Datasets

    November 16, 2009 / No Comments »

    When the Reporting Service team asked my opinion about shared datasets, a new feature in the forthcoming SQL Server Reporting Services R2, I was somewhat skeptical. I preferred them to focus on more important in my mind features, such as the ability to join datasets at report level. But the more I look at the way shared datasets got implemented, the more real-life scenarios I think may benefit from this enhancement. Think of a shared dataset a hybrid between a shared data source and report execution. Similar to a shared data source, a shared dataset is a report dataset that can be managed independently and shared among reports. A shared dataset must use a shared data source. The shared datasets can be parameterized and reports that use it can pass parameters to it. Similar to report executions, a shared dataset can also be cached and refreshed on a schedule. In...

  • Not All Calculated Members Are Born Equal

    November 13, 2009 / No Comments »

    An interesting issue popped up yesterday regarding the calculated member syntax. I had to multiply negative amounts in a financial cube for certain account categories that had negative amounts. The cube also had a Many:Many relationship between financial accounts and account groups which may be related to the issue. So, I had the following scope assignment: CREATE MEMBER CURRENTCUBE.[Multiplier] /* Old Style*/ AS [Financial Account].[Financial Account Hierarchy].CurrentMember.Properties("Multiplier", TYPED), VISIBLE = 0; Scope ( [Financial Account].[Financial Account Hierarchy].Members ); this = [Financial Account].[Financial Account Hierarchy].CurrentMember * [Measures].[Multiplier]; End Scope; The Multiplier calculated member returns the Multiplier property of the current account member, which could be 1 or -1 (if the account has to be negated). Then, the scope assignment overwrites all measures for the current member. As simple as it is, the assignment didn't work and the account amounts didn't get converted although the Multiplier calculated member would return the correct...

  • Aggregates of Aggregates

    November 12, 2009 / No Comments »

    Aggregates of aggregates is a new feature in SQL Server R2 Reporting Services. It can be better understood by the sample Aggregates.rdl report ([View:https://prologika.com/CS/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/blog/2474.aggregates.rdl]). It shows sales grouped by year and quarter. The Year group subtotals use the Sum aggregation function to sum the quarter sales. The report footer total averages the year subtotals. Prior to R2, authoring such a report would require dividing the total sales by the number of quarters, which you can obtain by using the CountRows function. You cannot just use the Avg function in the footer as the Bad Sales measure shows: =Avg(Fields!Internet_Sales_Amount.Value) If you do so, the Avg function would operate on the detail rows (all quarter rows) and it would produce 2,258,360. R2 lets you nest aggregate functions. The Sales footer total uses the following expression: =Avg(Sum(Fields!Internet_Sales_Amount.Value, "Year")) In this case, the Average function operates over the aggregated year subtotals. Note that not...

  • Where is the SSAS 2008 Book?

    November 10, 2009 / No Comments »

    Readers have been asking about a revised edition of my Applied Microsoft Analysis Services book for version 2008. First of all, I'd like to thank all my readers who bought and liked the book. This book has been a great success and it really exceeded my expectations in any way! It has great reviews on Amazon and it has sold over 10,000 copies to date, which is not a bad sales record for a technical book. I am still supporting my readers on the book discussion list and more than likely will continue doing so for foreseeable future. At the same time, I decided not to write a revision for SSAS 2008 or R2 for two main reasons: I just need a break from book writing. It took me several months to write the Applied Microsoft SQL Server 2008 Reporting Services and BI toolkit. I am not ready to start...

  • BIDS 2008 and R2 Project Support

    November 10, 2009 / No Comments »

    Continuing my intrepid journey in the SQL Server 2008 R2 land, I will discuss the new features of the recently-released CTP3. In this blog, I'll talk about the first new feature that caught my attention – BIDS support for 2008 and R2 projects. After hearing the outcry from many developers about project compatibility, in R2 Microsoft has decided to support both SQL Server 2008 and R2 report server projects. Similar to Analysis Services, Reporting Services now lets you target a server version in the project deployment settings. There is even a cool Detect Version link that would detect the SQL Server version based on the TagetServerURL setting. So, you can have a project that targets either edition. What happens when you use R2-specific features, such as the map control, but set the TargetServerVersion to SQL Server 2008? The map, Map1, was removed from the report. SQL Server 2008 Reporting Services...

  • SQL Server 2008 R2 November CTP (CTP3)

    November 10, 2009 / No Comments »

    Microsoft announced release on SQL Server 2008 R2 November CTP (CTP3) with a build number of #1352.12. MSDN, TechNet Subscribers and TAP customers can download the SQL Server 2008 R2 November CTP today. Non-subscribers will be able to access the CTP on Nov. 11th. This should be a feature-complete build that includes all SQL Server R2 functionality. Reporting Services The new Reporting Services features include: SharePoint Integration SharePoint Integration has several new features that include the following: Support for multiple SharePoint Zones. Support for the SharePoint Universal Logging service. A new data extension. A query designer for SharePoint Lists as a data source. Support for right-to-left text with Arabic and Hebrew in the SharePoint user interface. In a report, the data extension supports include data from SharePoint lists for SharePoint Foundation 2010, SharePoint Server 2010, Windows SharePoint Services 3.0, and Office SharePoint Server 2007. Report Parts. Shared Datasets and Cache...

  • The Aggregate Function

    October 29, 2009 / No Comments »

    Reporting Services supports an Aggregate function to retrieve aggregated values directly from the data provider if the provider supports this feature. The Aggregate function is especially useful with retrieving data from Analysis Services because it may be difficult if not impossible to recreate aggregated values on the report, such as when the cube script overwrites the server aggregates. It's easy to use the Aggregate function. Just replace the default Sum() aggregate function with Aggregate() in the group subtotals. When you do this, the Analysis Services provider brings in additional rows that include the server aggregates which the report displays in the group subtotals. Recently, I came across a report that would return the following exception after attempting to use Aggregate(). The Value expression for the text box ''<textbox.' uses Aggregate with an invalid scope parameter. I couldn't find anything wrong with the way Aggregate() is used because it was in...

  • Why We Can’t Connect Anymore?

    October 28, 2009 / No Comments »

    New OS (Windows 7 in this case) and new issues when trying to connect to Analysis Services on a remote server. I installed Windows 7 and added it to a corporate domain. I can use SSMS to connect to some SSAS servers but there was one server (the most important of course) which refused the connections with the following dreadful message which I am sure you've seen somewhere along the line:   TITLE: Connect to Server ------------------------------ Cannot connect to <servername>. ------------------------------ ADDITIONAL INFORMATION: The connection either timed out or was lost. (Microsoft.AnalysisServices.AdomdClient) ------------------------------ Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System) ------------------------------ An existing connection was forcibly closed by the remote host (System)   I was able to connect to that server from other non-Windows 7 machines so I narrowed the issue to Windows 7. Interestingly, running the...

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