Musings on Data

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

  • 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

    Scale Testing Reporting Services

    • 1 Comments
    I wanted to provide a quick note on how to scale test Reporting Services. Partially I'm posting this so I don't lose the link :-). Often we are asked how best to scale test Reporting Services and what scale numbers to expect coming from a particular configuration. We always hesitate to promise specific numbers because so much depends on your usage patterns, your reports, the size of your data, and your architecture. To say that N users will always be supported by X hardware is just too much of a simplification. Therefore the guidance we give is based on patterns we have seen in our testing lab. You can see the guidance we gave for SQL Server 2005 Reporting Services here: http://www.microsoft.com/technet/prodtechnol/sql/2005/pspsqlrs.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

    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

    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

    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

    Reliability through isolation

    • 0 Comments
    Reporting Services is an example of a system that both handles interactive user load and schedule load. It is also allows interactive users to schedule reports. A poor scheduling decision on the part of one user can can have significant impact on the overall responsiveness and reliability of your Reporting Services deployment. As an administrator when you plan your load, you want to ensure interactive user request responsiveness does not diminish due to recurring scheduled load. The report server provides a neat way to ensure scheduled load does not impact interactive load at all. First, let me say you'll need two instances of report server running in a scale-out deployment. This means you'll need Enterprise edition. So obviously this solution...
  • Musings on Data

    Daylight Savings Time issues with Notification Services - update your databases!

    • 1 Comments
    With the coming change to Daylight Savings Time in the United States, you will need to update your NS application databases for NS v2 and NS v2 SP1. For SQL Server 2005 NS, you will need to update your databases for existing applications. SQL Server 2005 SP2 contains a fix to ensure new applications created after applying SP2 do not encounter this problem. Here is the KB article that includes database scripts you can use to update the time zone information affected by Daylight Savings Time changes. http://support.microsoft.com/kb/931815/ SP2 is not available at the time of this post, but it will be prior to the Daylight Savings Time change taking effect. Take care and good luck, -Lukasz
  • 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

    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

    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

    Getting Started with Reporting Services

    • 1 Comments
    The question sometimes comes up for how to get out of the gate fast when ramping up on Reporting Services. Luckily the User Education team has been doing a fantastic job cranking out tutorials about Reporting Services. This is where I would start: http://msdn2.microsoft.com/en-us/library/ms170246.aspx And then I’d follow with search queries for specific topics on RS. Use MSN Search or Google with the suffix “reporting services site:msdn2.microsoft.com”. Eventually msdn2.microsoft.com will be merged to msdn.microsoft.com, but for now it is ok to include it in your search queries. Take care and good luck, -Lukasz
  • Musings on Data

    IE 7 Beta 3 Issue when viewing reports in Reporting Services

    • 1 Comments
    There are a couple of threads on the Reporting Services MSDN Forumn and newsgroup about IE 7 Beta 3 freezing when trying to view Reporting Services reports. Just wanted to put some quick info out for folks who run into this issue: Firstly, we know abou the issue (we too hit it in our internal testing just not in time to catch Beta 3). It only affects IE 7 Beta 3. Beta 2 is fine. Secondly, the IE team is working on a fix to the issue for us. It will show up in later builds of IE 7 as they become released for broader testing. Take care and good luck, -Lukasz
  • 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 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

    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

    Populating the Cache before your users pound your report server

    • 1 Comments
    Often people take a look at their report execution times and wonder how to optimize it. They readily see that it is possible to cache a report the first time a user runs it so that subsequent users don't have to incur the initial execution time. Well that still leaves the first person to take the 'pain' of waiting for the report to run. If yours is like many organizations, the 'first person' is actually a race right at 8am to see who clicks the report. Many folks end up waiting through that initial report execution time. Luckily, there is a feature of reporting services that allows you to solve this and prepopulate the cache before your users show up to click the report. Before we get to the solution, a little background. There...
  • Musings on Data

    I'm famous - no seriously, you can watch a video on Reporting Services and Notification Services upgrade on Microsoft.com

    • 0 Comments
    Wanted to share with you the following link. This is a presentation I gave a little while ago on Reporting Services and Notification Services upgrade. It is now available for viewing in high fidelity video. http://www.microsoft.com/emea/itsshowtime/sessionh.aspx?videoid=83 Take care and good luck, -Lukasz
  • 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

    Windows SharePoint Services and RS 2005

    • 0 Comments
    In RS 2000, users had a lot of grief to get RS to work on the same computer as Windows SharePoint Services (WSS). The reason for this is that WSS version 2.0 effectively commandeers the computer. We provide a trouble shooting document for these here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSinstall/htm/gs_installingrs_v1_9fdy.asp In RS 2005 we've done some work to make this *MUCH* easier. So much so, that RS 2005 no long requires many of the instructions listed in the specific article documentation. We have added two key things to setup and the configuration tool (WMI provider) to make this possible: RS setup creates an application pool on Windows 2003 Server – this creates process isolation between Windows...
  • Musings on Data

    Community Resources

    • 0 Comments
    I've received several emails from folks reading my blog asking for direct assistance with various issues. Though in some cases I can help directly - often times I just don't have the bandwidth for direct responses. Not to fear, there are plenty of ways to get help on Reporting Services and Notification Services. I've tried to summarize them below. If you know of others, please do let me know and I'll add them to this posting for your reference. Reporting Services Web site: http://www.microsoft.com/sql/reporting Reporitng Services Public newsgroup: news:microsoft.public.sqlserver.reportingsvcs Reporting Serivces Forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=82 Report Viewer Controls in Visual Studio 2005: http...
  • Musings on Data

    Support for RS 2000 RDL in RS 2005

    • 1 Comments
    I've started getting questions like the following: Given a report developed on SRS 2000 can that report run or be deployed unaltered on a SRS 2005 install can that report be modified on a SRS 2005 install Given a report developed on SRS 2005 can that report run or be deployed on a SRS 2000 install? Can that report be modified on a SRS 2000 install? An RS 2000 RDL report can be published and run on an RS 2005 server. When you request the RDL from the report server for such a report the original, unmodified rdl is returned. You can use the RS 2000 report designer to publish to both an RS 2000 and an RS 2005 report server. An RS 2005 RDL cannot be published to/edited in the RS 2000 server or tools. When you use...
  • Musings on Data

    Reporting Services Webcasts

    • 0 Comments
    Andrew Watt, an MVP for InfoPath, has a great post on his blog that lists the SQL Server 2005 Reporting Services weblogs. Check them out here: http://www.tfosorcim.org/archives/000409.html
  • Musings on Data

    Scripting Actions in Reporting Services (Also, copy my server to another server)

    • 0 Comments
    I am sometimes asked how to automate actions on the report server. The answer is to use the rs.exe scripting utility. The utility gives you a host to run VB based script files easily against the Report Server SOAP API. The utility in SQL Server 2005, supports both the 2005 and 2000 endpoints. These are accessed through a command line parameter. The default is the 2005 management SOAP endpoint. Probably the most common task I'm asked about is how to publish a report using a script. Here's a sample. You'll note that I'm reading the RDL from a file. First add yourself to the lukaszpblog group on http://groups.msn.com/lukaszpblog . Then click this link: Sample File The second thing folks ask me about is how to copy the contents of a report...
  • Musings on Data

    How to click in a report to show/hide (toggle) multiple items

    • 0 Comments
    Many people want the user to be able to click an area of a report and have items in the report be shown based on this interaction. For example, I might want to toggle between a table view and a chart view of some data. The way to do this is to use a report parameter in combination with a drill-through link back to the same report. Once you have this infarstructure, you can use an expression to drive the 'Hidden' property of a report item. If you have one or two items to show/hide you can probably do this inline. However, if you have many items, the logic is complex, or the logic needs to be repeated across reports, it would be better to centralize the logic so you can better maintain your reports. The way to do this is to put your...
Page 2 of 3 (53 items) 123