The two types of filters in SSRS are:
- Dataset Filter. This is the most common: filtering within the source query. When you implement a filter within the dataset, less data is sent over the network from the source database server to the Report Server – usually a good thing.
- Report Filter. This includes filtering after the source query has come back – on a data region (like the Tablix), or a data grouping. When you implement a filter within the report, when the report is re-executed again with different parameter choices, the Report Server uses cached data rather than returning to the database server. This is a viable option if you intentionally want to return all possible data from the database server in the initial query. Keep in mind this could be far less performant if the volume of data is high & the report won’t be re-executed numerous times.
Generally speaking, using a Dataset Filter is the most efficient method.
A report is processed in the following order: query execution, report processing, and report rendering. To reduce overall processing time, some of the first things you need to decide are which data to retrieve from the data source, and which calculations to include in the report. Reporting Services supports a wide range of data sources, including plain file systems, advanced database servers, and powerful data warehousing systems. The functionality of data sources and the structure of the report determine which operations should be done in the query, as opposed to which operations should be done inside of the report. Although the Reporting Services processing engine is capable of doing complex calculations such as grouping, sorting, filtering, data aggregation, and expression evaluations, it is usually the database system that is best optimized to process some or all of these data operations. With that said, always keep the following in mind:
- Optimize report queries.Query execution is the first step of the reporting process. Having a good understanding of the performance characteristics of your database system is the first step to good query design. For example, you can use SQL Profiler to track the performance of your queries if you use SQL Server as the database server.
- Retrieve the minimum amount of data needed in your report.Add conditions to your query to eliminate unnecessary data in the dataset results.If the initial view of the report shows only aggregated data, consider using drill-through to display details. Drill-through reports are not executed until the user clicks the drill-through link, whereas show/hide reports or subreports are processed during the initial report processing, even if they are hidden.
SharePoint 2013 has been out a year now and next week I’m off to attend a 5 day training course on Developing Microsoft SharePoint Server 2013 Core Solutions.
Given 2013 has been out a year and I’m getting a rare chance to attend some training I think it is also time to step up and bite the bullet regarding my SharePoint certifications so I’m currently planning on sitting the Core Solutions (70-488) certification exam a few weeks after the training and then follow that up with the Advanced solutions (70-489) exams after that. If I can successfully pass both of these then I will only need to do the Programming HTML5 (70-480) exam and the ASP.NET MVC 4 Web Applications (70-486) exams and I would have my Microsoft Certified Solutions Developer: SharePoint Applications certification.
So the planned path is:
- Developing Microsoft SharePoint Server 2013 Core Solutions (70-488)
- Developing Microsoft SharePoint Server 2013 Advanced Solutions (70-489)
- Developing ASP.NET MVC 4 Web Applications (70-486)
Not necessarily the order Microsoft suggests you do these certifications but I think if I am going to do any I want to do the SharePoint based ones first.
You may not be aware of it but Microsoft are again offereing their ‘second shot free’ deal for Microsoft certification exams. Here’s how it works:
- Order a Second Shot Voucher at: http://www.microsoft.com/learning/en-us/second-shot.aspx
- Schedule you initial certifcation exam using the voucher at: http://www.register.prometric.com/
- Take your exam
- If you don’t pass the exam at first, simply register for free to retake the exam using the same voucher number.
The Second Shot deal is available for the regular-priced individual Microsoft technical exams with a prefix of 070 or 071, and for the Microsoft Technology Associate (MTA) certification exams administered through Prometric.
The Second Shot offer is available until May 31, 2014.
Encountered an interesting problem today whilst configuring SQL Server Reporting Services (SSRS) integration on the web front end in a new SharePoint 2010 farm. I’m using SharePoint Server 2010 Enterprise Edition with Service Pack 1 and the October CU applied. SQL Server 2008 R2 is the DB version. Using the Reporting Services Configuration Manager I’d configured Reporting Services on the front end server to point to the Reporting Services DB on the back-end database server and created the relevant virtual directories on the front end server. For the time being the farm account is being used for the Reporting Services integration.
Next I’ve gone to Central Admin –> General Application Settings and selected Reporting Services Integration and successfully completed the configuration as you can see from the picture below.
The next step is usually to return to the General Application Settings page and select Set server defaults under Reporting Services but as soon as I did this I received the following error…
System.Web.Services.Protocols.SoapException: The report server installation is not initialized.
---> Microsoft.ReportingServices.Library.ReportServerNotActivatedException: The report server installation is not initialized.
at Microsoft.ReportingServices.WebServer.ReportingService2005Impl.GetSystemProperties(Property Properties, Property& Values)
at Microsoft.ReportingServices.WebServer.ReportingServiceSPImpl.GetSystemProperties(Property Properties, Property& Values)
at Microsoft.ReportingServices.WebServer.ReportingService2010Impl.GetSystemProperties(Property Properties, Property& Values)
at Microsoft.ReportingServices.WebServer.ReportingService2010.GetSystemProperties(Property Properties, Property& Values)
Looking into the problem didn’t return much useful information from the web but eventually I found a post that had a similar error only they were using SSRS with Team Foundation Server. Given I’d found nothing else of any use I gave it a go. The tip was to return to Reporting Services Configuration Manager and click on Encryption Keys in the left menu and then delete the encrypted content. I found when I first went to the Encyption Keys view that the Backup and Change buttons were both greyed out, only Retore and Delete were enabled. It sounded like a radical step but it worked. After deleting the encrypted content I found the Encyption Keys view now looked like this…
and when I returned to the General Application Settings page and ran Set server defaults it now worked as expected and allowed me to maintain the default settings for Reporting Services.
I was configuring a set of external content types and associated external lists the other day and encountered the following error message “The form cannot be rendered. This may be due to a misconfiguration of the Microsoft SharePoint Server State Service. For more information, contact your server administrator” when I create the lists to have associated InfoPath based forms (the ASPX based forms were still created).
It turns out that because we chose to perform the configuration of services manually the config wizard wasn’t run and as a result the State Service wasn’t configured. To fix this problem we need to configure the state service either by running the config wizard through Central Admin or using the following process from within the SharePoint Powershell command window…
- Login to the server using the SharePoint Admin account
- On the Taskbar, click Start, point to Administrative Tools, and then open the Windows PowerShell Modules as administrator.
- In Windows PowerShell, create a service application by typing $serviceApp = New-SPStateServiceApplication -Name “State Service”
- Create a State Service database and associate it with a service application, by typing New-SPStateServiceDatabase -Name ”StateServiceDatabase” -ServiceApplication $serviceApp.
- Create a State Service Application Proxy and associate it with the service application by typing New-SPStateServiceApplicationProxy -Name ”State Service” -ServiceApplication $serviceApp -DefaultProxyGroup.
Once these instructions are run, you should have a new State Service Service Application visible under the Service Applications list. You might also need to verify that your web Applications iare associated with the new State Service Service Application before things will start working properly.
Well, I’m back from 6 weeks of leave and wading through the considerable backlog of email at work 🙂 Maybe its just a by product of having so much time off work but I’ve returned with a sense of expectation and excitement about what the year ahead holds in store regarding SharePoint 2010. There were lots of terrific opportunities on the cards when I started my leave so I’m looking forward to seeing what eventuates.
The other exciting fact is that in about a month the second Australian SharePoint conference is on in Sydney and in October this year the bi-annual global Microsoft SharePoint Conference is on as well in California.