Blog – Page 86 – Prologika

Microsoft Live Labs Pivot

December 2, 2009 / No Comments »

In case you've missed this, the Pivot era has begun. After Excel PivotTable and PivotChart, we'll have PowerPivot in SQL Server 2008 R2. But Pivot evolves… A co-worker showed me today a glimpse of the Pivot future which I guess is the Microsoft Live Labs Pivot. Since grids and charts are not cool anymore we now have pictures and animation. It's hard for me to understand at this point how this would apply to Business Intelligence but the Silverlight app with all these pictures sure looks catchy. Long live Pivot!
Expert Cube Development with Analysis Services 2008

December 2, 2009 / No Comments »

Now that I've got my copy and read it, I can say a few things about the book Expert Cube Development with Microsoft SQL Server 2008 Analysis Services by Chris Webb, Alberto Ferrari, and Marco Russo. As mentioned in another post, the authors (all MVPs and prominent SSAS experts) don't disappoint and you won't go wrong picking up this book. The book is somewhat small in size (330 pages) but big in wisdom and best practices. It also includes plenty of references to external resources and blogs that the authors and other SSAS experts have written when you need further understanding of the topics discussed. Make no mistake though, this is not a step-by-step book and it doesn't target novice users. As its title suggests, the book is geared toward more advanced users who have a few years of SSAS experience under their belt and are thirsty for more insightful...
SharePoint List Data Extension

November 24, 2009 / No Comments »

R2 brings several SharePoint enhancements, including: Support for multiple SharePoint zones – Previously, SSRS supported access from SharePoint URLs in the default zone, as I explained before. Support for the SharePoint Universal Logging service - Reporting Services introduced a new server interface called IRSSetUserToken. When this interface is implemented, the report server calls the UserToken Set property on this interface and passes the SharePoint user token to the data extension. Scripting with the rs utility - The rs utility now supports servers configured in SharePoint integrated mode. This lets you create scripts that automate management tasks that target report servers in SharePoint mode. New SharePoint List Data Extension – Lets you report from SharePoint lists The last on the list, the new SharePoint List data extension, probably picked up your interest and deserves more explanation. Previously, if you had to use the XML Data Provider to call the SharePoint web...
Report Parts

November 24, 2009 / 2 Comments »

The major new R2 feature that debuts in CTP 3 (November CTP) of SQL Server 2008 R2 Reporting Services is report parts. A report part is a fragment of a report definition that you can publish to the report server to facilitate best practices and reuse. Just like a developer can refactor and share some piece of code, the report author can re-factor a portion of a report and publish it to the server so that end users can use it as it-is. The following report items can be published as report parts: Charts Gauges Images Maps Parameters Rectangles Tables Matrices Lists A notable exception to this list is page headers and footers which are probably the most likely candidates for reuse but at this point they didn't make the list. It's important to note that if a data region uses a report-specific dataset (not a shared dataset), publishing the...
Cumulative Update Package 5 for SQL Server 2008 Service Pack 1

November 20, 2009 / 2 Comments »

Microsoft released Cumulative Update Package 5 for SQL Server 2008 Service Pack 1 (build 10.00.2746). Among other things, if fixes an SSRS issue with printing and conditional visibility of recursive groups which I reported.
Sparklines and Data Bars

November 20, 2009 / No Comments »

Besides indicators, R2 adds sparklines and data bars to the arsenal of data visualization features. Sparklines are small graphics embedded in a context of words, numbers, and images. They help end users visualize simple trends. You could implement sparklines and simple data bars in the previous versions of Reporting Services but you had to go through many steps to strip down the chart or gauge regions. R2 makes the process much simpler. Let's see what it takes to author a sparkline report with R2 that is similar to the Sparklines report included in my book source code. [View:https://prologika.com/CS/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/blog/6175.Sparklines-R2.rdl] This report helps the user visualize the performance of the Adventure Works sales persons. For each person, the report shows the employee's trend based on the reseller sales made over four quarters and his performance ratio which is defined as the actual sales made by the employee divided by his sales quota....
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...