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 to Network Service
3) OR, configure a specific hostheader for SSRS, configure your DNS server to understand that hostheader, and configure your domain controller to have an SPN for the hostheader and the report server service account.
Clearly #1 is the easiest but not necessarily the right change. The rest of this post attempts to explain the cause and the merits of each solution.
Cause:
In the cases I have seen, the problem is due to Kerberos authentication. Kerberos is an authentication protocol that allows clients that create authentication tokens to associate a specific destination to that token. In the failure case there is a mismatch between the destination specified in the token and the report server process configuration. Due to this mismatch, the underlying Kerberos authentication scheme supported by Windows prevents report server from authenticating the user.
So how does this apply to SSRS 2008?
By default in the February CTP, setup includes RSWindowsNegotiate in the AuthenticationTypes listed in the file rsreportserver.config. Negotiate is an authentication type that specifies the server can either accept Kerberos or NTLM authentication tokens. The client application decides what kind of token to pass to the server.
Side note: in later builds we changed the setup behavior to add RSWindowsNegotiate only if network service is used in setup as the report server service account.
Internet Explorer (perhaps other browsers, too) uses Kerberos authentication if the server it connects to supports it. In order to use Kerberos, IE (the client application) must specify the destination in the authentication token it creates. It does this by examining the URL and asking the Domain Controller for an SPN for the destination. SPNs are Service Principal Names and are used to identify resources on the network.
There are a number of default SPNs created by the Windows ecosystem. One of these is for the Network Service account for the HTTP/Host SPN. This is the SPN used by IE to authenticate to SSRS. If SSRS is not running as the Network Service account the SPN may not resolve correctly and the Kerberos authentication will fail.
The default SPN HTTP/Host for Network Service account applies to SSRS because in SSRS 2008 we use HTTP.SYS to listen for network traffic. All services that use HTTP.SYS share this SPN by default, and they all can be subject to this problem if they support Kerberos or Negotiate authentication.
If SSRS is running under the Network Service account, things just work. However, if SSRS is running under a domain account, IE will include an SPN in the Kerberos authentication token that does not resolve to the SSRS service account. This will cause continued failure to authentication, and thus the login prompt.
NTLM is different than Kerberos - NTLM does not include a destination in the token and it will allow report server to successfully authenticate the user. Because Kerberos authentication specifies a destination in the tokens it uses, it is in some degrees more secure than NTLM.
So what should I do to fix it?
Firstly, you need to determine how much benefit using the more secure Kerberos tokens are in your environment. Generally, I would recommend that you use Kerberos authentication since having destination specific tokens is a good practice.
Secondly, you need to determine if you are OK changing the SSRS service account to Network Service. We recommend that you use a Domain Account for the SSRS service account - this helps to isolate SSRS from other processes running on the computer. Thereby, it helps make the computer that hosts SSRS more secure.
Clearly the best solution security wise is to configure a hostheader and have a specific SPN. However, it is also the hardest solution to deploy. Most computer administrators do not have the ability/permission level to make the required DNS and Active Directory SPN changes.
Is there a clear winner here? No - Unfortunately the first and second options achieve different objectives from a security perspective. Option 3, though advised, will not commonly be used due to complexity of configuration. For our setup experience, we decided that we'll use Negotiate (Kerberos) if in setup you select Network Service and that we'll use NTLM if you select a domain account for the SSRS service account. This is a good compromise since it allows report server to work by default and be as secure as possible after installation.
More sophisticated IT organizations should consider option 3 for their data center deployments.
In the interest of completeness - you may encounter this issue when changing the service account through the configuration tool. At the time of writing, you will have to ensure the Authentication Types are correct based on the service account you're using.
Is this problem new in SQL 2008? Could I have seen it in SQL 2005?
No - the problem is not new. Yes - you could have seen it in SQL 2005. The problem would manifest differently. In SSRS 2005, we set the Report Server application pool identity to Network Service. The reason for this was the very same - otherwise there are authentication failures due to the SPN. You can try this for yourself by creating 2 application pools in IIS one with Network Service, the other with a Domain Account and trying to authenticate to each.
Take care and good luck,
-Lukasz
Hi everyone - I've been away from my blog for a little while. However, now again I have some time to start blogging again.
I get tons of customer questions over the course of a week. I’d like to share with you how I make my life easier by using Internet Explorer’s built-in search box to help me find things faster. Other browsers provide similar capabilities, so you should be able to get it working there as well.
The first issue I'd like to address is an issue that has come up a number of times in a number of ways. I first addressed it here. But since then SQL Server 2008 has made a lot of progress and I find I need to find the latest documentation, quickly.
How to Add a SSRS Search Provider to Internet Explorer 7:
1. Find the Search box in the upper right corner of the IE window.
2. Use the down arrow to open the provider selector menu.
3. Click Find More Providers…
4. Find the Create Your Own box (highlighted in Yellow/Mustard color)
5. In the text box labeled Paste the URL of the Search results page URL, paste the following:
http://search.live.com/results.aspx?q=TEST+%22reporting+services%22+site%3Amsdn2.microsoft.com
6. In the text box labeled Specify a name for the search provider Name, paste the following:
RS on MSDN2
7. Click Install.
8. Now type a search term into the search box (choose your favorite SSRS feature name), use the down arrow and select RS on MSDN2 from the selector menu. You will see the results of your search.
You can use additional search terms to find documentation that is specific to a version:
Find only SQL Server 2008 documentation
http://search.live.com/results.aspx?q=TEST+100+"reporting services"+site:msdn2.microsoft.com
Find only SQL Server 2005 documentation
http://search.live.com/results.aspx?q=TEST+90+"reporting services"+site:msdn2.microsoft.com
Find only SQL Server 2000 documentation
http://search.live.com/results.aspx?q=TEST+80+"reporting services"+site:msdn2.microsoft.com
Alternately, you can just type your search term followed by 100, 90 or 80 in the Search Provider text box.
Clearly this doesn't apply only to SSRS, but that's where I find it most useful :-).
Take care and good luck,
-Lukasz
Update 1: I corrected the search terms to be actual URLs. It should work better for people. Note, I'm using www.live.com as my search provider. You can do something similar with other search engines.
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?mfr=true
You can read about how to set the Idle Timeout here:
http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/83b35271-c93c-49f4-b923-7fdca6fae1cf.mspx?mfr=true
The behavior of the Idle Timeout causes the user’s perception that Report Server is constantly falling asleep. In fact, the same behavior applies to any web application hosted in IIS. Once IIS has shut down the report server, it can take anywhere from 5 to 20 seconds to respond to the next request. This value ranges based on the performance of the server hosting IIS & RS. This time is taken up by IIS loading ASP.Net, ASP.Net in turn loading the report server DLLs, and finally report server starting up. All of this takes some time. Naturally, to a user it looks like report server is dozing off - again.
To address this issue you need to ping the report server periodically to ensure it is never inactive. To ping the server, make a Web Services (SOAP) request to the report server virtual directory. You could do the same with Report Manager; though RM load time is quite fast if the report server is already running. You can do this using your favorite scheduling technology like the Windows Task Scheduler, a SQL Agent job that runs an extended stored procedure, etc.
But since we’re talking about Reporting Services, I thought I’d create a sample to show you how to use the solve this problem using Subscriptions.
I posted a report that lists the items in the report server namespace.
To get the report:
First add yourself to the lukaszpblog group on http://groups.msn.com/lukaszpblog.
Then you can get the report here:
ListChildren Report:
http://www.msnusers.com/lukaszpblog/Documents/Files/ListChildren.rdl
Take the report and publish it to your report server. Viewing it will show you a very basic listing of content at the root of the report server namespace. Edit the properties for the report and set the data source credentials the report will run as. The credentials will either need to be stored or none. In order to choose none, you’ll need to set the unattended execution account. The account you choose should have the minimum level of privilege (I’d suggest Browser role) on the report server. Test that the report executes to make sure everything works.
Now you can create a subscription to this report. Use the parameters in the subscription to ensure the report executes against the report server you want it keep alive. If you’re deployed in a scale-out deployment you should create one subscription for each node in the scale-out and set the parameters explicitly to access the machine directly (not through the virtual server name or NLB). Set subscription to execute on a schedule and set that schedule to occur every 9 minutes. I like this frequency since if something were to go wrong, like a networking issue, you the subscription will run twice within the span of the 20 minute IIS application shutdown window.
With this solution, the report server will keep itself alive by continually processing the subscription which pings the web service and therefore IIS will never unload it. Problem solved.
Take care and good luck,
-Lukasz
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
You should be aware that SQL Server 2008 CTPs (Community Technology Preview) are becoming available as 2007 continues. To date, I believe we have provided 3 public SQL Server 2008 CTPs. You can find the latest one here:
https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395
As always with a new version, users want to know how well it scales relative to the old version. Bear in mind that these CTPs are kind of like Alpha or Beta releases of the product - they're not finished yet and performance/scale characteristics will change as we get closer to release.
However, if you're thinking of adopting the next version, now is the time to start planning your testing strategy. Specifically, you can start by building your test infrastructure to do scale testing against the existing product and reuse as new CTPs come available. The leg work to understand which reports are key to test and which scenarios you should be including is independent of your actual testing timeframes and having it done and out of the way can greatly simplify your acceptance testing later. In this regard, you may find the following article very interesting (I did):
Using Visual Studio 2005 to Perform Load Testing on a SQL Server 2005 Reporting Services Report Server (http://technet.microsoft.com/en-us/library/aa964139.aspx)
Whether you go with this approach or another solution, you will be well served to start planning this testing well in advance of having the final product in place. Also, we have not released any guidance on perf/scale for SQL Server 2008 Reporting Services; this will come in due course as we get closer to release.
Take care and good luck,
-Lukasz
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 - when you reset the control, you create a new 'session' if connected to the report server. This causes the report to be reexecuted when you run it. It also means that the parameter values will be reset to their initial defaults. Once done, you can again customize the parameter values in your code.
Take care and good luck,
-Lukasz
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.
To get the reports:
First add yourself to the lukaszpblog group on http://groups.msn.com/lukaszpblog.
Then you can get the reports here:
New Subscription List Report: http://www.msnusers.com/lukaszpblog/Documents/Files/NewSubscriptionReports/SubscriptionsList.rdl
Extension used by subscriptions: http://www.msnusers.com/lukaszpblog/Documents/Files%2FNewSubscriptionReports%2FSubscriptionsByExtension.rdl
Sub report - Standard Subscriptions: http://www.msnusers.com/lukaszpblog/Documents/Files%2FNewSubscriptionReports%2FStandardSubscriptionsExtension.rdl
Sub report - Data Driven Subscriptions: http://www.msnusers.com/lukaszpblog/Documents/Files%2FNewSubscriptionReports%2FDataDrivenSubscriptionExtension.rdl
After you download all these files to your machine, create a new report project in Visual Studio/BIDS and add the RDLs.
Then click the preview tab to see what they do.
The reports are quite interesting in and of themselves. Between the reports, I use the following features of Reporting Servies:
XML data extension, expression based connection strings, internal parameters with validation logic (value substitution), data set filters, data set sort, subreports, custom data set fields, code functions in reports, show/hide/visibility control, SOAP web services, charts, etc.
Take care and good luck,
-Lukasz
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
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) Look like I missed a popular post on how multi-value parameters and subreports:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=164056&SiteID=1
And of course the real product documentation is here:
http://msdn2.microsoft.com/en-us/library/aa337292.aspx
Take care and good luck,
-Lukasz
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 isn't for everyone.
The solution is to have 2 servers configured to use the same report server database (a scale-out deployment). One of the servers is for interactive user load, the other is for scheduled unattended operations - subscriptions, snapshot creation, etc.
On each of your report server computers, open the Surface Area Configuration tool. Go to "Surface Area for Features" and navigate to the Reporting Services item.
To make a report server only handle interactive load, disable "Scheduled Events and Report Delivery".
To make a report server only handle schedule load, disable "Web Service and HTTP Access".
Of course for really large deployments you might have a set of report servers dedicated to interactive load and another set for scheduled load. Just make sure to set the Surface Area Configuration appropriately for each server.
There you have it - a neat way to isolate load for large report server deployments.
Take care and good luck,
-Lukasz
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
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 server?”
General process to follow:
-
For Report Execution problems, start with the report server execution log
-
This log will tell you which reports are failing, who ran them, what parameters they used
-
It will also provide the time at which it failed and the server in your deployment on which the report failed
-
You can use the time and server to find the actual stack trace in the trace log files
-
Reference the trace logs based on timestamps ranges you find in the event log
-
The trace logs provide detailed stack trace information.
-
You can sometimes understand what the error is based on this info
-
Search the Reporting Services MSDN forum using the stack trace information for solutions to the problem you encountered
-
You can find information on some of the error codes here:
Logging
- Report Server exposes a number of log files, these include:
o Information on report executions and whether they were successful and additional data related to the execution
o Detailed error stacks that show what the problems were
o Major events that occurred on your report server that you should be aware of are in the application event log in windows
- You can read up on all of this here:
o http://msdn2.microsoft.com/en-us/library/ms157403.aspx
Diagnosing processing and rendering problems – topic name “Processing Large Reports”
- http://msdn2.microsoft.com/en-US/library/ms159638.aspx
Monitoring Performance
- You will want to look at things like memory consumptions, application domain recycles, cpu usage, etc. To isolate problems you may adjust concurrency for the scheduling service so you know exactly which report is currently running (instructions are below).
- Application Domain recycles indicate the report server is under memory pressure. We use them to clean out memory. If your interactive report execution failed suddenly, and you're monitoring the performance counters for application domain recycles, you may see a correlation. You should also see information in the trace log related to this.
- This topic describes the performance counters
o http://msdn2.microsoft.com/en-us/library/aa972240(SQL.80).aspx
- Monitoring Report Execution Performance with Execution Logs
o http://msdn2.microsoft.com/en-us/library/aa964131.aspx
Specific actions to help diagnose problems:
- Adjusting Memory limits – see section “Report Size in Memory”
o If you’re seeing out of memory exceptions you can try increasing the memory used by report server
o http://msdn2.microsoft.com/en-US/library/ms156002.aspx
- Adjusting concurrency for Scheduling:
o While trying to determine what is happening, you might reduce the number of simultaneous report executions
§ If you’re always running extremely large reports, setting this to 1 will allow you to use all of the memory for the report
o In the file rsreportserver.config:
§ <MaxQueueThreads>0</MaxQueueThreads> determines concurrency for scheduling and delivery
§ “0” means the report server will determine the right number
Performance Whitepaper:
- It is recommended to hosting report server and the report server database and data sources from which you get report data on different computers
- http://www.microsoft.com/technet/prodtechnol/sql/2005/pspsqlrs.mspx
How to configure a scale-out deployment:
- Scale outs can increase throughput, reliability, and concurrency
- http://msdn2.microsoft.com/en-us/library/ms159114.aspx
Monitoring and triggering subscriptions:
- Sometimes making the report run on a schedule can help you isolate the performance issues (interactive report execution can lead to excessive load on your server)
· See ‘how to trigger a subscription’; this works on SQL 2000 RS, and SQL 2005 RS
· See ‘how to monitor a subscription’
Monitoring interactive report executions:
- The ListJobs SOAP API allows you to see which long running reports are currently executing
- http://msdn2.microsoft.com/en-gb/library/aa225969(SQL.80).aspX
If all else fails:
-
Use SQL Profiler to monitor the actions the report server is taking in the report server database
-
this is useful if you think the report server is not doing anything - you can watch the actual queries run through and watch report server respond to your actions
-
generally speaking, it is possible to see everything in our various log files, so there should be no need to go to this level
If you have additional questions, feel free to leave me a comment or post a question on my blog:
- Lukasz’s Blog: http://blogs.msdn.com/lukaszp
Take care and good luck,
-Lukasz
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 automate a few steps:
1) Set the default parameter values for the report using the SetReportParameters SOAP API
2) Call the CreateReportHistorySnapshot SOAP API after step 1.
3) Repeat - as in lather, rinse, repeat ;-).
The reason we've found out about this workaournd is that there is one catch - history snapshots have a resolution of 1 second in reporting services. That means that if you try to create 2 history snapshots within the same second (time), the second (order) request is *ignored* - CreateReportHistorySnapshot returns the same history id for the second request as for the first request. You'll need to add a delay in your code to compensate. Based on the comment below from b5lurker, a delay of 1.001 seconds works well in practice.
Finally, since History Snapshots are identified in RS only by their ID and the time they were created, you'll need to be able for your end users to correlate the history snapshots with their parameters. You can do this in multiple ways - store the correlation in a database somewhere, flat file, etc. Of course you could do it all within RS by storing the information as custom properties on the report itself by using the SetProperties SOAP API.
You'll also likely need to create some management/viewing UI for controlling how many of these 'history' snapshots are stored on the server due to size limitations. You'll also want to have the UI allow end-users to view the right snapshot.
Finally, you should consider this a work around not a recommended solution – due to the idea represented here if you have a set of interactive users trying to browse the report live, while you’re generating your history snapshots, they might see wildly different default parameter values.
Kudos go out to Hi_i_am_Amit and b5lurker from the MSDN Reporting Services forum that brought this workaround to our attention via their debugging efforts. Here are their threads, respectively. You may want to ask them what else they did to make this solution really work in their environments.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1077593&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=379728&SiteID=1
Take care and good luck,
-Lukasz
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 be able to step through the tool to get the same experience as I had.
Demo 2: Security
Start with the report server from Demo 1. You'll need to create a local, low-privilege account (I called it Demo) on your computer. Run one browser (call it browser 1) window as yourself and point it to the report manager virtual directory you just created. See that you can create folders. Run another browser (call it browser 2) as the demo account and try to access the same virtual directory. You should see nothing. In browser 1, navigate to Home, click properties, click security, and then add a role assignment for the user "Demo" and assign them the browser role. Refresh browser 2, you should see access. If you play around with various item types, you should see that you in browser 1 can see many properties not visible to the user Demo in browser 2. In browser 1, you can drill into the various role definitions, look at task, try creating a new role, etc.
In this demo I also covered that users only need access to the item you're using, even if it depends on another item. So a user only needs to be able to see a report and does not need any permission on the shared data source it uses.
Demo 3: Report and Model Management
Kind of wide ranging demo - started looking at properties for a report. Talked about custom properties, and property extraction from RDL. Spent some time demo'ing model item security and drill through reports.
I'll post the actual files after I get back from vacation, but the prep work for the demo is as follows:
Open the adventure works model sample in model designer. Navigate to the SalesOrder entity. In the list of fields, right click and add a new filter. Define the filter on the OrderDate year property so that you're filtering only years greater than or equal to 2003. Save, then rename the filter to "NewOrders". Do the same steps but create a filter for less than 2003 and name it "OldOrders". For each filter update the properties to ensure it is nullable (the order date is nullable by default for some reason in the sample, so this is necessary). Next click on the OrderDate entity and in the properties window expand the SecurityFilters options. Add the NewOrders and OldOrders filters to the list. Save and deploy your model to the report server.
Ok, now in Management Studio, connect to RS and open the properties for the adventure works sample model. In Model Item Security, first assign permission to "Everyone" at the root. On the SalesOrder entity, select the OldOrders filter and set a custom policy on it to only include you.
Now create a report using report builder that uses the SalesOrder entity. Save the report to the report server. In Browser 1 view the report, see that you can see all the sales orders. In Browser 2, view the report and see that you can see only the new orders.
For the drill thorough report, I create a basic report on the products entity using report builder - I included the name, the product id and some other miscellaneous information. I saved the report to the file system. I opened the file in report designer as an existing item. I then added some pictures, changed the color. I published the report to the report server (I did all of this before the demo!). Then in the demo, I showed a simple report built on the ProductSubcategories entity. I showed the default drill through experience. Then, in management studio I again when to the properties of the adventure works model, chose drill through reports, chose the Product entity, and set the multiple instance report to the customized report I published. Then I again when to the original report and drilled through.
Demo 4 (Yes still more demos!): Schedules and Subscriptions
I had these all prepared. I simply used the Employee Sales Summary sample report for the data-driven subscription and the Product Catalog sample report for the standard subscription. The key thing is I needed to update the shared data source they reference to store my credentials before creating the subscriptions and to use the credentials as windows credentials.
The d-d subscription was easy to create because I use the adventure works shared data source with the modification above. For the query, I used the Sales.vSalesPeople view in a simple query. I then used the email address and employee id fields to customize the reports being delivered.
Since I didn't have an SMTP server running, ahead of time I switched the email delivery provider to use a pickup directory. I also shortcutted the delivery of e-mails by using the trick of starting the Agent Jobs manually thorough management studio.
Demo 5: (yup, keeping on trucking) Scripting, I think
I wanted to show properties being updated, so I chose the problem of updating printer margins and page settings on a linked report. The script I created was simple:
Dim Reset as Boolean = False
Sub Main()
Dim sourceReport As String
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim pageProperties() As [Property] = GetPageProperties()
If (Not Reset) Then
sourceReport = rs.GetReportLink(LinkedReport)
Console.WriteLine("Linked to: {0}", sourceReport)
Dim sourceProperties As [Property]()
Dim curProperty As [Property]
sourceProperties = rs.GetProperties(sourceReport, pageProperties)
pageProperties = sourceProperties
For Each curProperty In sourceProperties
Console.WriteLine("Property: {0} Value:{1}", curProperty.Name, curProperty.Value)
Next
End If
rs.SetProperties(LinkedReport, pageProperties)
End Sub
Function GetPageProperties() As [Property]()
Dim PageHeight As [Property] = New [Property]
Dim PageWidth As [Property] = New [Property]
Dim TopMargin As [Property] = New [Property]
Dim BottomMargin As [Property] = New [Property]
Dim LeftMargin As [Property] = New [Property]
Dim RightMargin As [Property] = New [Property]
PageHeight.Name = "PageHeight"
PageWidth.Name = "PageWidth"
TopMargin.Name = "TopMargin"
BottomMargin.Name = "BottomMargin"
LeftMargin.Name = "LeftMargin"
RightMargin.Name = "RightMargin"
Dim pageProperties() As [Property] = {PageHeight, PageWidth, TopMargin, BottomMargin, LeftMargin, RightMargin}
Return pageProperties
End Function
and I ran it with the following command line
rs.exe -i "C:\Documents and Settings\lukaszp\Desktop\SetLinkReportPageProperties.rss" -s http://localhost/reportserver -v LinkedReport="/Link to Landscape"
Of course ahead of time, I create a simple report that had 2 text boxes on the opposite ends of a page with the correct portrait page height/width set in the properties of the report. I published that and created a linked report at the root named "Link to Landscape".
In the demo, I showed the print control preview to show how the original had the right settings and the linked report had the wrong settings. I then ran the script again to show how it all worked afterwards, kinda c