Musings on Data

A blog for data, apis, and all things Power BI by Lukasz Pawlowski.

  • Musings on Data

    Solving the Reporting Services Login issue in the February CTP of SQL Server 2008

    • 1 Comments
    I’ve seen customers run into authentication issues using the SQL Server 2008 February CTP of Reporting Services (you should all give it a try!). This post provides solutions and a detailed discussion of the underlying causes. The material in this post is applicable to all SSRS deployments, beyond the scope of solving the specific issue. Symptom: A) When you access report server or report manager, you get a login prompt multiple times and eventually you get a blank screen. B) When you try to publish reports/models from Business Intelligence Development studio, you get access denied errors. Solutions: 1) Remove RSWindowsNegotiate and ensure RSWindowsNTLM is specified in the file rsreportserver.config 2) OR, change the report server service account...
  • Musings on Data

    Reporting Services HTTP 401 (Unauthorized) - Host Headers require your attention

    • 6 Comments
    In a previous post , I talked about issues with repeated login prompts when trying to access either Report Server or Report Manager using your browser. Recently, during an upgrade of our internal test server we identified a new type of HTTP 401 (Unauthorized) message. This post provides a description of the problem and the solution. Symptoms: When all of the following symptoms apply, it could be this issue: A. When accessing Report Manager or Report Server you use a URL that looks like http(s)://<foo>/reports. The name <foo> is NOT the computer name of the computer on which Report Server and Report Manager are deployed. However, DNS or the machine's hosts or lmhosts files (WINDOWS\system32\drivers\etc) are configured to send requests...
  • Musings on Data

    How to diagnose issues when running reports in the report server?

    • 7 Comments
    A customer came to the SQL Server Customer lab to investigate a problem with one of the feature I’m responsible for – data-driven subscriptions. In looking at their solution, we discussed any number of problems they had encountered. I realized that it can be difficult to find out where to start looking when a problem occurs. This post will hopefully provide you a starting point as you endeavor to fix the issues you run into. Reports can take up - a lot of memory, - a lot of time to execute, - a lot of CPU Generally speaking, it is possible for errors to occur as a result: - Out of Memory - Internal Errors - Rendering errors This begs the question, “How to diagnose issues when running reports in the report...
  • Musings on Data

    SQL Server 2005 SP1 Reporting Services database expected version is 'C.0.8.43' problem

    • 3 Comments
    (Originally I thought this post should be titled: SQL 2005 SP1 Tattered my Report Server Database – What’s up with that?) When you upgrade SQL Server 2005 to SP1 and you are running Reporting Services, you may start to get the following error message: The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'C.0.8.40'. The expected version is 'C.0.8.43'. To continue, update the version of the report server database and verify access rights. (rsInvalidReportServerDatabase) First the solution: Got to Start à All Programs à Microsoft SQL Server 2005 à Configuration Tools à Reporting Services Configuration Connection to your report server on the connection...
  • Musings on Data

    Per user snapshots? Or multiple snapshots for a report each with different parameters at the same time

    • 3 Comments
    I've received questions in the past about whether the report server supports per user snapshost - that is report snapshots customized with different parameter values for a given user. The answer I have always given is no - you get one snapshot per report and that snapshot always uses the default parameter values. Well, it turns out that some inventive users have come up with a 'work around'; it involves some coding on your part. But here's the sketch: Though RS supports only 1 *execution* snapshot per report, it supports N *history* snapshots per report. Also, you can cause a history snapshot to be created programmatically. So there we have it - "all" you need to do to create a set of history snapshots with different parameter values is...
  • Musings on Data

    Keeping your report servers awake (or No more waiting for report server to startup)

    • 1 Comments
    Update 5/3/2010: With time and experience, we learn. So of course an easier way exists to do what this blog post states. Just set the Idle Time-out to zero on the Application Pool configuration. Bill Staples gives the UI snapshot of where to make the change here: http://forums.iis.net/t/1148433.aspx HTH. Reporting Services in SQL 2005 is hosted in IIS. IIS has a performance optimization built-in that you cannot disable (AFAIK), which shuts down an application once there is a period of 20 minutes of inactivity. This is governed by the Idle Timeout IIS Metabase property. You can read about this Idle Timeout here: http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/b39a8ab9-7331-4bad-a89a-0210aadbd4a7.mspx...
  • Musings on Data

    Report Viewer Control - Reset() method

    • 0 Comments
    This post is about a consequence of the calling ReportViewer.Reset() method related to revaluating parameter default values. The Report Viewer controls are a great way to integrate reports into your applications. You can see some examples here: http://www.gotreportviewer.com One aspect of using the controls that may not be easily found/understood is the value of the Reset() method. http://msdn2.microsoft.com/en-us/library/microsoft.reporting.winforms.reportviewer.reset(vs.80).aspx http://msdn2.microsoft.com/en-us/library/microsoft.reporting.webforms.reportviewer.reset(VS.80).aspx The description of the method obsurely references to resetting the control to the 'default state'. This has an implication in the case of parameters...
  • Musings on Data

    Using Active Directory Groups with Reporting Services

    • 0 Comments
    I have received several questions from customers, the latest in a conference call today, wondering how Reporting Services works with Active Directory groups The question goes along the lines - How do I configure Reporting Services to use Active Directory groups?, or Do I need to write a custom security extension to use Active Directory groups with Reporting Services? There are two kinds of groups in Active Directory: 1) Security Groups 2) Distribution Groups In both cases you should be able to use these groups out of the box, so long as your computer is a member of the Domain or a Trusted Domain of the Active Directory server in question. So by default you should have to do exactly nothing to get this to work. Here comes the...
  • Musings on Data

    Charts in a Tablix cell don’t work for me… but I’ve see these nice demos where they do…

    • 0 Comments
    Problem: You may have learned about sparklines or you want to add a chart to your Tablix.  You either copy/paste your working(!!!) chart into the tablix and then you preview and you get: "The chart 'Chart1' has a detail member with inner members.  Detail members can only contain static inner members." Solution: In a Tablix you cannot put dynamic member in a Detail row.  You have to put it into a Group header or Group Footer row. Steps: Insert a table into your report. Set the DataSetName for the Tablix (either in the Properties pane or by dragging a field into the tablix). In Row Groups , right click the (Details1) group.  Select Add Group –> Parent Group… In the Tablix group dialog, Select your Group by: clause...
  • Musings on Data

    InternalCatalogException, ASSERT, or NullReferenceException when using a Custom Security Extension

    • 1 Comments
    We have seen multiple customers with issues when customizing the Security Extension Sample ( 2005 , 2008 ) that is available on http://www.codeplex.com/ . This is the sample that demonstrates how to create a Forms Authentication solution with SQL Server Reporting Services. There are two scenarios which are the primary sources of the problems: Scenario 1: “Anonymous” access Many custom security extension authors want to allow ‘anonymous’ access – where users do not need to login, or to allow all users some basic level of permission without needing to login explicitly. In order to do this, they change the sample code’s implementation of AuthenticationExtension.GetUserInfo to return a NULL for out IIdentity userIdentity. However, this approach...
  • Musings on Data

    SQL 2008 R2 RTM! Time to look at some new Execution Log Reports

    • 0 Comments
    Wow.  We just RTM’ed SQL Server 2008 R2.  Check out the official web site SQL Server 2008 R2 Website . Now that the official release is on its way out the door, I thought I’d share some reports that show off some of the new capabilities.  In SQL 2008 R2 there is a new ExecutionLog3 view in the Report Server database.  For those familiar with SSRS, the ExecutionLog table, and then ExecutionLog and ExecutionLog2 views were used in previous versions to show which reports ran on the report server, who ran them, and to provide a lot of information about the execution.  With SQL Server 2008 R2 the ExecutionLog content is expanded again.  The newest version of the log is ExecutionLog3.  You will find it by connecting...
  • Musings on Data

    Monitoring Subscription Status (also calling RS SOAP methods from inside reports)

    • 2 Comments
    While posting on the Reporting Services forum, I came across a post by some folks looking to know how to automatically find out that a subscription had failed to run. Right now, the way the RS tools are, there is no way to do this automatically without calling a SOAP method - ListSubscriptions. These folks were DBAs who wanted to monitor RS without needing to build an entire application to do so. The solution they were trying was to access the report server database directly to read values out its tables. They weren't impressed when I told them that MS does not support direct access to the report server database other than for getting data out of the execution log. There are several ways to get around the lack of subscription monitoring...
  • Musings on Data

    High Availability – Frequently Asked Questions about Failover Clustering and Reporting Services

    • 0 Comments
    In SSRS high availability is achieved through a Scale-out deployment that is placed behind a Network Load Balancer (NLB).  The NLB is then responsible for routing requests to Report Server nodes that are responding to requests.  High Availability Documentation Reference : http://msdn.microsoft.com/en-us/library/bb522745.aspx However, many customers are interested in using Windows Failover Clustering with SSRS, since this is how they deploy their SQL Servers.  This article discusses Windows Failover Clustering in regards to SSRS. Windows offers built-in Failover Clustering capability.  SSRS does not natively support Windows Failover Clustering.  The use of Failover Clustering with SSRS service is possible using standard...
  • Musings on Data

    How to trigger a subscription in Reporting Services 2000

    • 2 Comments
    RS 2000 doees not support custom events. What this means is you cannot, on the face of it, trigger a subscription on demand. Luckly, we thought of this. The method of doing this is not particularly pretty, but it does work. Eventing in Reporting Services is currently (2000 and 2005) limited to two events: SnapshotCreated and TimedSubscription. The TimedSubscription event is interesting because it can be submitted to the report server using the FireEvent SOAP API. You can use this property to programmatically submit events to Reporting Services. What you need: 1) A shared schedule that will never fire. This is easy – just create a shared schedule that executes once and that starts in the past (e.g. yesterday). Take a note of the schedule...
  • Musings on Data

    SQL Server 2008 Reporting Services Scalability

    • 1 Comments
    Much has been said up to now about the scalability work the Reporting Services team has done in the SQL 2008 version. However, until now, there were no numbers to back up the architecture discussions. The SQL Server Customer Advisory Team (SQLCAT) just released a Technical Note titled Scaling Up Reporting Services 2008 vs. Reporting Services 2005: Lessons Learned . This is a must read for anyone looking to compare the two products. It also shows that an investment, even now, in SQL 2005 Reporting Services provides a roadmap to higher scalability in the future. Previously, I discussed an approach for Scale Testing Reporting Services that is very similar to the approach used in the Technical Note. Some key notes from the Technical Note: 1) " Reporting...
  • Musings on Data

    Monitoring Subcription Status - New reports!

    • 0 Comments
    A while ago I wrote a blog post to explain how to monitor subscription status. You can read it here: http://blogs.msdn.com/lukaszp/archive/2005/12/30/monitoring-subscription-status-also-calling-rs-soap-methods-from-inside-reports.aspx Today, I updated the Subscriptions List report contained in that entry and published to enhance its functionality. I also added 3 reports that will help you see which subscriptions use which delivery extension. You can get the reports here: http://cid-e5eac755bcb2e151.skydrive.live.com/self.aspx/.Public/New%20Subscription%20Reports New Subscription List Report: http://cid-e5eac755bcb2e151.skydrive.live.com/self.aspx/.Public/New%20Subscription%20Reports/SubscriptionsList.rdl Extension used by subscriptions...
  • Musings on Data

    Using Multi-Value Parameters Programmatically

    • 0 Comments
    It seems there is a ton on confusion about how to programmatically use multi-value parameters. Here's how to do it using the SOAP API for report execution: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1515942&SiteID=1 Here's how to do it in subscriptions: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1233857&SiteID=1 Here's how to do it using expressions inside a report: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=161179&SiteID=1 Now, just for your inner geek, here's a treatment of how NULLs work in multi-value parameters (they don't, but this post includes some work arounds): http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=379668&SiteID=1 (Update 04/25)...
  • Musings on Data

    SQL Server 2005 Notification Services Components Package RC1 Availability

    • 2 Comments
    As part of this blog, I comment about Notification Services. I wanted to provide you the following information about the availability of a pre-release SQL Server 2005 Notification Services components package. If you have questions about SQL Server 2005 Notification Services, visit the SQL Server Notification Services Forum on MSDN . You can help improve the final version by submitting bugs to the Connect Feedback Center . Take care and good luck, -Lukasz ---------------------- SQL Server 2005 Notification Services Components Package Availability The SQL Server 2005 Notification Services component package made available in the February 2007 release of the Microsoft Feature Pack for SQL Server 2005 is being updated to include Notification Services...
  • Musings on Data

    IIS Troubleshooting for SQL Server 2005 Reporting Services

    • 1 Comments
    Over the years, we have seen a number of IIS related issues that cause trouble for users who deploy SQL Server 2005 Reporting Services. We have tried to resolve these in the MSDN Forums on a case by case basis. Now we have compiled the common issues with IIS into a single KB Article along with explanation of why these occur and what can be done to resolve them. Kudos to James Wu on the SSRS team for slogging through the issues and compiling them into this document. How to troubleshoot IIS configuration issues in SQL Server 2005 Reporting Services Take care and good luck, -Lukasz
  • Musings on Data

    Power BI Dynamic Date Filtering

    • 0 Comments
    One question I get from time to time is how to filter to the last week's worth of data automatically in Power BI. You might want the last 7 days, this week, this month, etc. You can make it fully dynamic by combining the technique Matt Masson describes here to create a date table and add to it some Power Pivot DAX functions to classify each date into the date range you'd like. Download a sample here OneDrive personal doesn't load the Power View sheet so download and open the example in Excel on your desktop. EDIT 7.27.2015 - I was asked for the formulas since some folks don't have Power View. These should work in Power BI Desktop , but I've not tested them there. Here you go: Power Query query: //let // CreateDateTable ...
  • Musings on Data

    New Reporting Services Portal on MSDN

    • 1 Comments
    Just adding a quick note about the new MSDN Home Page for Reporting Services. It has links to many great pieces of content including articles, blogs, Webcasts, and references to online docs. http://msdn2.Microsoft.com/en-us/sql/aa336316.aspx Take care and good luck, -Lukasz
  • Musings on Data

    Upgrading Reporting Services - What about my Report Definitions (RDLs)?

    • 1 Comments
    Some folks have asked me what happens when I upgrade my RS 2000 to an RS 2005 deployment with respect to Report Definitions (RDLs). This is an interesting lifecycle problem that we’ve approached using the following idea: What you publish to the report server is what you retrieve from the report server. Here’s how it works: Say you have an RS 2000 Reporting Services deployment that has a number of working reports in it. You probably have one or more corresponding report projects that you edit in Report Designer in Visual Studio 2003 .Net. When you publish these reports, the RDL is stored in the Report Server Database (not the file system!). The RDL is stored and we create a corresponding compiled version of the report that we also store...
  • Musings on Data

    SQL Pass presentation: Management, Configuration, Security

    • 0 Comments
    It's been a little while since my last post. I wanted to provide additional information related to my presentation to day at SQL PASS. The talk was extremely well attended - the room was full and standing room only. I hope you got what you were looking for out of the talk - feel free to drop me a line with comments. Unfortunately, due to a technical glitch (err. blue screen of death! Yikes!), we could not record the demos. So I wanted to provide to you some of the demo materials so you can try to recreate the demos. Demo 1: Configuration Just install a report server instance with the files only option. Then in the start menu navigate to SQL Server, find Configuration Tools and run the Reporting Services configuration manager. You should...
  • Musings on Data

    Report Designer for SQL Server 2000 Reporting Services does not work on Visual Studio 2005

    • 0 Comments
    Some folks have run into a problem using SQL Server 2000 Reporting Services now that Visual Studio 2005 has been released. Specifically, the Report Designer in RS 2000 doesn't work in Visual Studio 2005. Luckily, there is a solution: The Visual Studio 2005 license includes a downgrade provision. The details are located here: http://msdn.microsoft.com/vbasic/previous/downgrade.aspx The short answer is if you buy a copy of Visual Studio 2005, you can use that license to run the older version. You need to call the Microsoft Supplemental & Replacement Parts Center to get a copy of the Visual Studio .Net 2003 media. Take care and good luck, -Lukasz
  • Musings on Data

    SQL 2008 RTM - Where is Report Builder 2.0?

    • 1 Comments
    We've just released SQL Server 2008 (it feels great by the way!) and there have already been many e-mails asking us where to find Report Builder 2.0. You can find an update on the release plan for Report Builder 2.0 on the main Reporting Services Blog: http://blogs.msdn.com/sqlrsteamblog/archive/2008/08/07/report-builder-2-0-release-plan-update.aspx Take care and good luck, -Lukasz
Page 1 of 3 (53 items) 123