• Cumulative Update 3 for SQL Server 2008 Service Pack 1

    July 21, 2009 / No Comments »

    Microsoft released today a Cumulative Update 3 for SQL Server 2008 Service Pack 1. Among other things it fixes the Report Builder 2.0 ClickOnce deployment issue on x64 which I reported here.

  • Excel Page Numbering Issue

    July 10, 2009 / No Comments »

    An issue was reported about page numbering and exporting reports to Excel with Reporting Services 2008. Specifically, if the page header or footer uses a placeholder for the page number, when exported and previewed in Excel, the report will show Page 1 on all pages. Page [&PageNumber] of [&TotalPages] While waiting for Microsoft to fix this, use an expression that concatenates the PageNumber member of the Globals collection. = "Page" & Globals!PageNumber & " of " & Globals!TotalPages To enter the expression, select the textbox item, right-click and choose Expression.

  • Google – The Best Thing that Ever Happened to Microsoft

    July 8, 2009 / No Comments »

    On a different subject, you've probably heard the news: Google will release an operating system called Google Chrome OS which will challenge Windows and instill fear into the Microsoft camp. To the contrary, I think Google is the best thing that ever happened to Microsoft. How come? I remember reading somewhere that after the perestroika, Mikhail Gorbachev had supposedly told Ronald Reagan "we'll now do the worst thing to you (USA); we'll leave you without enemy". Perhaps, too extreme but paraphrased to business, completion is a good thing. When challenged, good companies become better, products improved and consumers benefit. So, I hope Google will continue expanding their ambitions. Similarly, I hope Microsoft gives Google a run for its money by competing relentlessly to increase their share of the search market. I'd personally love to see business intelligence features added to the search results. Why not, the first two letter s...

  • How to Test SSRS MDX Queries in SQL Server Management Studio

    July 6, 2009 / 2 Comments »

    I often need to capture an MDX query that a report sends to Analysis Services and execute it in SQL Server Management Studio (SSMS). I usually do this to understand what parameters the report passes to the query and to troubleshoot the query itself. Unfortunately, SSMS doesn't support parameterized MDX queries so you have to resort to the following technique: Capturing MDX Queries The easiest way to capture an MDX query is to use the SQL Server Profiler. Start SQL Server Profiler from the Microsoft SQL Server <version>-> Performance Tools program group. Click File -> New Trace or press Ctrl+N. In the Connect To Server dialog box, choose Analysis Services, enter your server name, and click Connect. In the Trace Properties dialog box, click the Event Selection tab. Check the Show All Events and Show All Columns checkboxes. Optionally, if you want to see only your events, that is events...

  • Maintaining State in Reporting Services 2008

    June 30, 2009 / 5 Comments »

    Sometimes, more advanced reporting needs may require maintaining state using custom code. Recently I had to implement a rather involved report consisting of two sections: a detail section that used a recursive sum, Sum(Fields!SomeField.Value, , True), and a summary section with additional calculations which would reference some of the aggregated values in the detail section. Since Reporting Services is not Excel, you cannot reference arbitrary fields on a report. So, I had to cache the values from the detail section in a hashtable using custom code so the summary section could obtain these values when needed. The following embedded code gets the job done: Friend Shared _hashtable As New System.Collections.Hashtable() Function GetKey(ByVal id as Long, ByVal column as String) as String return Report.User!UserID & id & column End Function Function AddValue(ByVal id as Long, ByVal column as String, ByVal value As Object) As Object Dim key as String = GetKey(id,...

  • Suppressing Auto-generation of MDX Parameter Datasets

    June 8, 2009 / No Comments »

    There is an unfortunate issue with the MDX Query Designer in SSRS 2008 Report Designer and Report Builder 2.0 where changing the main dataset overwrites the parameter datasets. This is a rather annoying issue because often you need to make manual changes to the parameter datasets. However, when you make a change to the main dataset, the MDX Query Designer wipes out the manual changes and auto-generates the parameter databases from scratch. According to the feedback I got from Microsoft, the issue will be fixed in SQL Server 2008 R2. Meanwhile, there is a simple workaround which requires manually changing the dataset definition in RDL. Basically, you need to add a SuppressAutoUpdate designer switch to the parameter dataset, as follows: <Query> <DataSourceName>Viking</DataSourceName> <CommandText> … <rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate> <rd:Hidden>false</rd:Hidden> </Query>

  • TechEd 2009 North America BIN304 Slides and Code Uploaded

    May 22, 2009 / No Comments »

    I've uploaded the slides and code from my Reporting Services 2008 Tips, Tricks, Now and Beyond breakout presentation delivered on May 13th 2009 at TechEd 2009 North America.

  • TechEd 2009 BI Power Hour Demos

    May 21, 2009 / No Comments »

    The TechEd 2009 BI Power Hour demos, which I blogged about before, are posted on the Microsoft BI Blog. Robert Bruckner has also posted the SSRS demo from the TechED USA 2008 Power Hour, which he renamed to Sea Battle.

  • Transmissions from TechEd USA 2009 (Day 4)

    May 15, 2009 / No Comments »

    I got the final evaluation results from my session. Out of some 15 Business Intelligence sessions, mine was the fourth most attended session with 169 people attending. Based on 35 evaluations submitted, it was ranked as the third most useful session with an average satisfaction score of 3.46 on the scale from 1 to 4. For someone who does presentations occasionally, I'm personally happy with the results. Thanks for all who attended and liked the session! I took it easy today. I attended the Scott Ruble's Microsoft Office Excel 2007 Charting and Advanced Visualizations session in the morning. This was purely Excel-based session with no BI included. It demonstrated different ways to present information effectively in Excel, such as with conditional formatting, bar charts, sparklines, etc. Next, I was in the Learning Center until lunch. In the afternoon, I decided to do some sightseeing and take a tour since I've...

  • Transmissions from TechEd USA 2009 (Day 3)

    May 14, 2009 / No Comments »

    Today was a long day. I started by attending the Richard Tkachuk's A First Look at Large-Scale Data Warehousing in Microsoft SQL Server Code Name "Madison". Those of you familiar with Analysis Services would probably recognize the presenter's name since Richard came from the Analysis Services team and maintains the www.sqlservernanalysisservices.com website. He recently moved to the Madison team. Madison is a new product and it's based on a product by DATAllegro which Microsoft acquired sometime ago. As the session name suggests, it's all about large scale databases, such as those exceeding 1 terabyte of data. Now, this is enormous amount of data that not many companies will ever amass. I've been fortunate (or unfortunate) that I never had to deal with such data volumes. If you do, then you may want to take a look at Madison. It's designed to maximize sequential querying of data by employing a shared-nothing...

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