Tips to improve SQL Server database design and performance

Best performance is the main concern to develop a successful application. Like a coin database is the tail side (back-end) of an application. A good database design provides best performance during data manipulation which results into the best performance of an application.

During database designing and data manipulation we should consider the following key points:

1. Choose Appropriate Data Type

   Choose appropriate SQL Data Type to store your data since it also helps in to improve the query performance. Example: To store strings use varchar in place of text data type since varchar performs better than text. Use text data type, whenever you required storing of large text data (more than 8000 characters). Up to 8000 characters data you can store in varchar.

2. Avoid nchar and nvarchar

   Practice to avoid nchar and nvarchar data type since both the data types takes just double memory as char and varchar. Use nchar and nvarchar when you required to store Unicode (16-bit characters) data like as Hindi, Chinese characters etc.

3. Avoid NULL in fixed-length field

   Practice to avoid the insertion of NULL values in the fixed-length (char) field. Since, NULL takes the same space as desired input value for that field. In case of requirement of NULL, use variable-length (varchar) field that takes less space for NULL.

4. Avoid * in SELECT statement

   Practice to avoid * in Select statement since SQL Server converts the * to columns name before query execution. One more thing, instead of querying all columns by using * in select statement, give the name of columns which you required.

   
    -- Avoid
   SELECT * FROM tblName
   --Best practice 
   SELECT col1,col2,col3 FROM tblName 
   
   

5. Use EXISTS instead of IN

   Practice to use EXISTS to check existence instead of IN since EXISTS is faster than IN.

   
    -- Avoid 
   SELECT Name,Price FROM tblProduct 
   where ProductID IN (Select distinct ProductID from tblOrder)
   --Best practice 
   SELECT Name,Price FROM tblProduct 
   where ProductID EXISTS (Select distinct ProductID from tblOrder)
   
   

6. Avoid Having Clause

   Practice to avoid Having Clause since it acts as filter over selected rows. Having clause is required if you further wish to filter the result of an aggregations. Don't use HAVING clause for any other purpose.

7. Create Clustered and Non-Clustered Indexes

   Practice to create clustered and non clustered index since indexes helps in to access data fastly. But be careful, more indexes on a tables will slow the INSERT,UPDATE,DELETE operations. Hence try to keep small no of indexes on a table.

8. Keep clustered index small

   Practice to keep clustered index as much as possible since the fields used in clustered index may also used in nonclustered index and data in the database is also stored in the order of clustered index. Hence a large clustered index on a table with a large number of rows increase the size significantly. Please refer the article Effective Clustered Indexes

9. Avoid Cursors

   Practice to avoid cursor since cursor are very slow in performance. Always try to use SQL Server cursor alternative. Please refer the article Cursor Alternative.

10. Use Table variable inplace of Temp table

    Practice to use Table varible in place of Temp table since Temp table resides in the TempDb database. Hence use of Temp tables required interaction with TempDb database that is a little bit time taking task.

11. Use UNION ALL inplace of UNION

    Practice to use UNION ALL in place of UNION since it is faster than UNION as it doesn't sort the result set for distinguished values.

12. Use Schema name before SQL objects name

    Practice to use schema name before SQL object name followed by "." since it helps the SQL Server for finding that object in a specific schema. As a result performance is best.

    
     --Here dbo is schema name
    SELECT col1,col2 from dbo.tblName
    -- Avoid
    SELECT col1,col2 from tblName 
    
    

13. Keep Transaction small

    Practice to keep transaction as small as possible since transaction lock the processing tables data during its life. Some times long transaction may results into deadlocks. Please refer the article SQL Server Transactions Management

14. SET NOCOUNT ON

    Practice to set NOCOUNT ON since SQL Server returns number of rows effected by SELECT,INSERT,UPDATE and DELETE statement. We can stop this by setting NOCOUNT ON like as:

    
     CREATE PROCEDURE dbo.MyTestProc
    AS
    SET NOCOUNT ON
    BEGIN
    .
    .
    END 
    
    

15. Use TRY-Catch

    Practice to use TRY-CATCH for handling errors in T-SQL statements. Sometimes an error in a running transaction may cause deadlock if you have no handle error by using TRY-CATCH. Please refer the article Exception Handling by TRY…CATCH

16. Use Stored Procedure for frequently used data and more complex queries

    Practice to create stored procedure for quaery that is required to access data frequently. We also created stored procedure for resolving more complex task.

17. Avoid prefix "sp_" with user defined stored procedure name

    Practice to avoid prefix "sp_" with user defined stored procedure name since system defined stored procedure name starts with prefix "sp_". Hence SQL server first search the user defined procedure in the master database and after that in the current session database. This is time consuming and may give unexcepted result if system defined stored procedure have the same name as your defined procedure.

How to have a Select All Multi-Value Cascading Parameter

I’ve seen several cases that involved Multi-Value Cascading Parameters and how the behavior of those work.  Let me start with the default (Out of the box) behavior.  The example report I’ll be using will make use of the AdventureWorksDW sample database.  I will also be using SQL 2008 R2 November Release, but the same will apply to SQL 2008 Reporting Services as well.

In my report, I have three multi-value parameters.  Each one dependent on the parameter before it (cascading parameters).  The parameters are Category, Subcategory and Product, in that order.

As you can see above, I have Bikes selected for the category which will select all by default as that is my first selection.  But what happens if I now add in another category?

Only the first three are selected after adding Components to the category list.  We have seen where people feel this is a bug, but that was actually intended.  We will only maintain what your current selection is.  On the first selection, we didn’t have anything, we actually didn’t have a dataset yet because I didn’t have it loading by default.  But that initial selection will be Select all.  After that, it will be based on what you have selected.  Removing entries will make it seem that we still select all, but that is because the items left were still selected previously, so nothing really changes.  But adding items to the mix will cause them to not be selected by default.  Only the original selection will remain.

So, then the question comes in, what if i want them to all be selected whenever it gets refreshed?  We currently do not provide for that ability out of the box, but I have a way you can add the functionality into your report. 

I will add a disclaimer here.  The code that you will see in this blog has not been fully tested and should not be used in production.  Please be sure to review it first and determine how it will affect performance within your report.

Subcategory

Lets start with the Subcategory parameter as there is nothing we need to do with Category.  To start, you will want to add the following into the code section of the report.  This can be found by going to Report on the Menu bar and selected Report Properties.

Function EncodeParamValue(ByVal ParentParam As Object(), ByVal ChildParam As Integer) As String

    Dim i As Integer
    Dim EncodedChildParam As String = ""

    'Concatenate the ParentParam as the prefix
    For i = 0 To ParentParam.Length - 1
        If i = 0 Then
            EncodedChildParam = CStr(ParentParam(i))
        Else
            EncodedChildParam = EncodedChildParam + "-" + CStr(ParentParam(i))
        End If
    Next i

    'Add the ChildParam at the end
    EncodedChildParam = EncodedChildParam + "_" + CStr(ChildParam)

    Return EncodedChildParam

End Function

Function DecodeSubcatParamValue(ByVal SubCategoryIDs As Object()) As Object()

    Dim i As Integer
    Dim SubCategoryID As String

    'Remove the prefix on the parameter values to restore the original value
    For i = 0 To SubCategoryIDs.Length - 1
        SubCategoryID = SubCategoryIDs(i)
        SubCategoryIDs(i) = SubCategoryID.Substring(SubCategoryID.IndexOf("_") + 1)
    Next i

    Return SubCategoryIDs

End Function

These two functions will be what we use to alter the parameter values.  Essentially saying that the value includes everything.  But we strip it out when we actually want to use it.

The next step is to modify the dataset for Subcategory to make use of the EncodeParamValue function.  We will add a Calculated Field.  You can do this by going to the properties of the DataSet, selecting the Fields section and click on Add, select Calculated Field.

We will call this field ModifiedSubcategoryKey.  This name is based on what the value field was we were using for the Parameter itself which was the ProductSubcategoryKey field.  So, we are just modifying that field a bit.  The expression for that field will be the following:

=Code.EncodeParamValue(Parameters!Category.Value,Fields!ProductSubcategoryKey.Value)

We are using the EncodeParamValue to take in the Category (1st Parameter) list and tack it onto the SubCategoryKey. We then need to adjust the Available Values and Default Values to use the ModifiedSubcategoryKey instead of ProductSubcategoryKey.  This is done in the Parameter Properties for Subcategory.  You will want to change the Value Field to be ModifiedSubcategoryKey.

The last thing we will do for Subcategory is to change the refresh value to “Always Refresh”.  This is done in the Parameter Properties for Subcategory under the Advanced section.

That is all you need to get the Subcategory parameter drop down to work correctly.

Product

Now we need to make sure Product works with the SubcategoryKey, as it expects that as a parameter for its dataset.  It expects an Integer and right now we are giving it bit string. Without changing anything, you will see the following error:

We need to make use of our Decode function as the Parameter Value expression for the Product Dataset.  This is done in the Product Dataset properties under the Parameters section.  The expression we will use is the following:

=Code.DecodeSubcatParamValue(Parameters!Subcategory.Value)

This will take in the big string we created, and break it up to just give us the value we really want.  This allows the Product Drop down to show values, but in order for the Select all behavior to work, we need to use the Encode method as well.  So, we will add a Calculated Field to the Product Dataset called ModifiedProductKey.  The expression for this field will be the following:

=Code.EncodeParamValue(Parameters!Subcategory.Value,Fields!ProductKey.Value)

We then want to change the Available Values and Default Values for the Product Parameter.  This is done in the Parameter Properties for Product.  We want to change the Value Field to ModifiedProductKey.  This will get the Product Drop down to have the Select All behavior we are looking for.

This will introduce two levels of encoding on the product value, so I created a second Decode method which will strip it twice for use in the Report Dataset.

Function DecodeProductParamValue(ByVal ProductIds As Object()) As Object()

    Dim i As Integer
    Dim ProductId As String

    'Remove the prefix on the parameter values to restore the original value
    For i = 0 To ProductIds.Length - 1
        ProductId = ProductIds(i)
        'The first one removes the Category encoding
        ProductId = ProductId.Substring(ProductId.IndexOf("_") + 1)
        'The second one removes the SubCategory encoding
        ProductIds(i) = ProductId.Substring(ProductId.IndexOf("_") + 1)
    Next i

    Return ProductIds

End Function

Report Dataset

The last thing to take into account is the Dataset for the report itself.  You will need to use the Decode methods for the parameters within the Report Dataset like we did on the Product Dataset for the parameter listing. You will need to do this for both the SubCategory Parameter value as well as the Product Parameter Value usingDecodeSubcatParamValue and DecodeProductParamValue respectively.

Hopefully this will help you to get the desired behavior you are interested in.

Adam W. Saxton | Microsoft SQL Server Escalation Services

Reporting Services, 2008, Adam, Report Design, 2008 R2

How To Watermark your Report With Custom Image

SSRS: How To Watermark your Report With Custom Image

Hello and Welcome, Today our topic to discuss under discussion is how to watermark your image with your custom image or with your company logo. I will be guiding you with an example so that it will be easy to learn for new developers as well

We need a custom image to watermark our report, we shall use the following image,

Just an image with our site URL pasted in it.

Let us make use of already developed report Enable HTML Tags in SSRS Reports (Tutorial here:http://www.heysup.net/ssrs-tutorials/enable-html-tags-in-ssrs-reports/260/ )

Now, in your Report Data navigate to Images folder

Right click and add Image

Browse your Server/PC and select the image you want to have as a watermark. In our case its background.png

First change the format of the image you want to find, its JPEG by default. Select the appropriate one from the drop down menu, select the image and click on Open button. You will end up the image name been displayed in Images folder in your report builder

As, image has been imported in to your report data just we need to call it to watermark the report. In order to do that click somewhere on your report body (remember its report body) and then hit F4 in your keyboard. Properties menu will be displayed navigate to Background Image property

In background image just update your image name manually, please not do not write the extension of the image just write image name. In Source tab select embedded meaning your image is ready in your report data just pull from it.

If in case you have image hosted on some other server and you need to pull it then you select Source as External and value as image path. Here is the screenshot for you

If you have image stored in your database then your source will be Database and value will be the path to database. Then Source will be database and value will be expression and in expression select the column which has images. Here is screenshot for you

That’s it when you click on preview you get to see your background image as watermark to your report here in my case

http://www.heysup.net/ssrs-tutorials/ssrs-how-to-watermark-your-report-with-custom-image/271/

Reporting Services Performance Optimizations

RATE THIS

                                

This technical note is part of the Building and Deploying Large Scale SQL Server Reporting Services Environments technical note series, which provides general guidance on how to set up, implement, and optimize an enterprise scale-out architecture for your Reporting Services environment. This note provides guidance for Reporting Services in both Microsoft® SQL Server® 2005 and SQL Server 2008. The focus of this technical note is to optimize your Reporting Services architecture for better performance and higher report execution throughput and user loads.

Architecture

Figure 1 represents a typical scale-out Reporting Services environment; as noted by the red box, the focus of this technical note is that of Performance Optimization of your scale-out deployment.

Figure 1: Reporting Services Scale-Out Architecture

Should You Use 64-Bit?

Yes.
Why, you ask? The answer can be divided into two different sections – how 64-bit helps your report server catalog performance and how it helps your report server performance.
How 64-bit Helps Your Report Server Catalog Performance
Remember that your report server catalogs are SQL Server databases, so your standard database techniques for optimizing SQL Server databases come into play here, as noted in the Report Server Catalog Best Practices technical note. Since SQL Server 2005, the database has been written natively for 64-bit and it is able to make use of the additional addressable memory.
How 64-bit Helps Your Report Server Service
For your report servers, the answer is slightly more complicated. As a general statement, most of your Reporting Services reports are memory-intensive, and the additional addressable memory made available by 64-bit will provide you with more scale. Note that for some workloads, 32-bit can execute faster, especially in scenarios where you have many small reports. But if you have more memory made available by 64-bit, you can handle more concurrent report users. Because there is less memory contention, report throughput will be higher; that is, you can allow more users to view and export larger reports. In SQL Server 2005 Reporting Services, each report’s data set is placed into memory; the more concurrent reports, the more memory used. If you use 32-bit, you can easily hit the 3 GB ceiling, which can also result in Internet Information Services (IIS) process recycles, leading to report failure.
But as noted in the Reporting Services Scale-Out Deployment Best Practices technical note, SQL Server 2008 Reporting Services is not memory-bound. It is able to effectively use the file system in order to move data structures in and out of memory and the file system if the report server experiences memory pressure. These memory limits are explicitly configurable in SQL Server 2008 Reporting Services via RSReportServer.config, as noted in the Memory Configurations for SQL Server 2008 Reporting Services section below. When Reporting Services uses the file system, the reports run more slowly, because it is much more efficient to request data from the memory than from disk. The file system is used only if Reporting Services memory usage gets close to the configured memory limits. If you overload the report server in SQL Server 2008 Reporting Services with a large number of concurrent users and/or very large reports, all of your reports can still complete processing, albeit more slowly, because Reporting Services can hold all of this data without running out of memory space. In enterprise environments, you will eventually run into situations where your servers will need to be able to handle many concurrent users and a large load – the optimization in SQL Server 2008 Reporting Services (in comparison to SQL Server 2005 Reporting Services) is that while the reports may run slower at times, they will complete.
Exceptions
Keep in mind that certain data providers are not available for 64-bit (for example, the Microsoft JET provider or certain third-party providers). In these cases, customers will need to continue using 32-bit for their Reporting Services environment.

Handling a Large Workload

As noted in the previous section, the two main issues concerning enterprise reporting environments are the ability to handle concurrent user load and the ability to handle a large workload (that is, large reports). To help mitigate the concurrency issue, your solution is to scale out to multiple report servers to handle the user query load, as noted in theReporting Services Scale-Out Deployment Best Practices technical note.
To get the highest performance when handling large workloads that include user requests for large reports, implement the following recommendations.
Control the Size of Your Reports
You will first want to determine the purpose of these reports and whether a large multipage report is even necessary. If a large report is necessary, how frequently will it be used? If you provide users with smaller summary reports, can you reduce the frequency with which users attempt to access this large multipage report? Large reports have a significant processing load on the report server, the report server catalog, and report data, so it is necessary to evaluate each report on a case-by-case basis.
Some common problems with these large reports are that they contain data fields that are not used in the report or they contain duplicate datasets. Often users retrieve more data than they really need. To significantly reduce the load placed on your Reporting Services environment, create summary reports that use aggregates created at the data source, and include only the necessary columns. If you want to provide data feeds, you can do this asynchronously using more appropriate tools, such as SQL Server Integration Services, to provide the file data feed.
Use Cache Execution
As noted in the Reporting Services Scale-Out Deployment Best Practices, if you have reports that do not need to have live execution, enable the cache execution setting for each of your appropriate reports. This setting causes the report server to cache a temporary copy of those reports in memory.
Configure and Schedule Your Reports
For your large reports, use the Report Execution Timeouts setting to control how long a report can execute before it times out. Some reports simply need a long time to run, so timeouts will not help you there, but if reports are based on bad or runaway queries, execution timeouts ensure that resources are not being inappropriately utilized.
If you have large reports that create data processing bottlenecks, you can mitigate resource contention issues by usingScheduled Snapshots. Instead of the report data itself, a regularly scheduled report execution snapshot is used to render the report. The scheduled snapshot can be executed during off-peak hours, leaving more resources available for live report users during peak hours.
Deliver Rendered Reports for Nonbrowser Formats
The rendering performance of nonbrowser formats such as PDF and XLS has improved in SQL Server 2008 Reporting Services, as noted in the Scaling Up Reporting Services 2008 vs. Reporting Services 2005: Lessons Learned technical note. Nevertheless, to reduce the load on your SQL Server Reporting Services environment, you can place nonbrowser format reports onto a file share and/or SharePoint® team services, so users can access the file directly instead of continually regenerating the report.
Prepopulate the Report Cache by Using Data-Driven Subscriptions for Parameterized Reports
For your large parameterized reports, you can improve performance by prepopulating the report cache using data-driven subscriptions. Data-driven subscriptions enable easier population of the cache for set combinations of parameter values that are frequently used when the parameterized report is executed. Note that if you choose a set of parameters that are not used, you take on the cost of running the cache with little value in return. Therefore, to identify the more frequent parameter value combinations, analyze the ExecutionLog2 view as explained below. Ultimately, when a user opens a report, the report server can now use a cached copy of the report instead of creating the report on demand. You can schedule and populate the report cache by using data-driven subscriptions. For more information, see Report Caching in Reporting Services.
Back to the Report Catalogs
You can also increase the sizes of your report server catalogs, which allows the databases to store more of the snapshot data. For more information, see Report Server Catalog Best Practices.
Tuning the Web Service
IIS and Http.sys tuning helps get the last incremental performance out of the report server computer. The low-level options allow you to change the length of the HTTP request queue, the duration that connections are kept alive, and so on. For large concurrent reporting loads, it may be necessary to change these settings to allow your server computer to accept enough requests to fully utilize the server resources.
You should consider this only if your servers are at maximum load and you do not see full resource utilization or if you experience connection failures to the Reporting Services process. To do this:

• For SQL Server 2005 Reporting Services, tune IIS.
• For SQL Server 2008 Reporting Services, tune Http.sys within the operating system: Windows® 2003 or Windows 2008.

Monitoring by Using ExecutionLog2

The Reporting Services ExecutionLog2 view is a good starting point from which to analyze your current workloads and understand its dataset size, performance, and complexity characteristics. For more information, see Robert Bruckner’s blog, which provides extensive details on the ExecutionLog2 view (http://blogs.msdn.com/robertbruckner/archive/2009/01/05/executionlog2-view.aspx). For more information about query and reporting on report execution log data, see SQL Server Books Online (http://msdn.microsoft.com/en-us/library/ms155836.aspx).
In particular, this view contains a new AdditionalInfo column. ExecutionLog2.AdditionalInfo contains information related to the size of memory-pressure responding data structures. One way this information can be useful is to check whether you have reports with high values (10s, or 100s of MBs) – these reports might be candidates for further review, focusing on the design of those reports and the dataset query sizes.
Below are some tips on how to view the ExecutionLog2 view to quickly understand potential performance bottlenecks.Linked is the Review Execution Logs Reporting Services project, which creates summary and detail Reporting Services reports on the last 1,000 entries into the ExecutionLog2 view, with the sorting options noted below.

Figure 2: Review Execution Logs (ExecutionLog2) Summary Report

Figure 3: Review Execution Logs (ExecutionLog2) Details Report
Long-Running?
Sorting by ElapsedSec or RowCount helps you identify long-running reports. If the value for TimeDataRetrieval is high, the data source is your bottleneck, and you may want to optimize. If the there is a high value for RowCount, a lot of data is being retrieved and aggregated by Reporting Services – perhaps have your data source do this to reduce the load on your report server.
Subscriptions or Interactive?
Sorting by the RequestType field allows you to determine whether you have a lot of subscriptions; you can then determine the bottlenecks and stagger-schedule the reports (that is, schedule the subscription execution times of the reports at different times).
Live Data or Snapshots?
Sorting by the Source field allows you to determine whether your reports are typically live data or snapshots. If the reports can be snapshots (for example, yesterday’s report), create snapshots so you can avoid query execution, report processing, and report rending.
Load Balanced?
Sorting by the Instance field can help you see whether your network load balancer is handling report requests in a balanced fashion. You can also see if some nodes are down or not processing requests.
Discover Report Patterns
Sorting by ReportPath and TimeStart can help you to find interesting report patterns – for example, an expensive report that takes 5 minutes to run is executed every 10 minutes.
Report Health
You can sort by status to determine if you have a high number of failures that occurred before (for example, incorrect RDL) or after (for example, subscription delivery error) the report is processed. This can also help you identify reports where there is outdated information or settings (for example, expired data source passwords or missing subreports).
In addition, if ScalabilityTime > 0, Reporting Services is in scale mode, which means that it is under heavy memory pressure and will start pushing long-running reports to the file system to ensure enough memory to complete smaller query requests. If this happens frequently, consider trying one of the following:

• Reduce the size of the dataset.
• Simplify the grouping, filtering, or sorting logic in the report to use less memory.
• Add more memory.
• Add more servers to better handle the load.

Data-Driven Subscriptions
Based on all of this information, you can then create your own data-driven subscriptions that can sort, filter, and track your issues. For example, you can create a subscription that alerts you if Errors > 5%.

Memory Configurations for SQL Server 2008 Reporting Services

As alluded to above in the 64-bit section, memory in SQL Server 2008 Reporting Services is used more efficiently at the data structure level. Under intensive workload pressures, it uses a file system cache in order to reduce memory utilization. By being more efficient and writing to disk, it can process more report executions (even large reports) successfully. The administrator uses settings to determine when SQL Server 2008 Reporting Services starts to use the file system cache and how aggressively the cache is used. The administrator should consider configuring memory settings for the SQL Server 2008 Reporting Services to optimally use their computer resources. Fine-tuning these settings can provide an increase in performance under intensive workload over the default configuration.
For more information, see SQL Server 2008 Books Online: Configuring Available Memory for Report Server Applications. The key memory configurations are:

• WorkingSetMinimum: This is the minimum amount of memory that Reporting Services makes available in order for it to perform its task; that is, it does not use the file system cache if SQL Server Reporting Services process memory utilization is below this limit. It is configured as a KB value within the RSReportServer.config file. After this threshold is hit, Reporting Services responds to memory pressure by having long-running requests use the file system cache and smaller queries use memory.

For many concurrent users, you can potentially increase this configuration value after it hits peak performance, enabling more process requests to be completed within memory instead of the file system.

• WorkingSetMaximum: This is the maximum amount of physical memory Reporting Services will use; it is also configured as a KB value within the RSReportServer.config file. After this threshold is hit and exceeded for a period of time, Reporting Services recycles the app domains to reduce memory utilization. This will ensure that there is enough memory left for the operating system to complete its task. You can increase this value if you want to process more reports concurrently.

• MemorySafetyMargin and MemoryThreshold: These values define how aggressively the file system cache is used.MemorySafetyMargin defines the boundary between low-pressure and medium-pressure scenarios, with a default value of 80%. MemoryThreshold defines the boundary between medium-pressure and high-pressure scenarios, with a default value of 90%. Both are configured within the RSReportServer.config file.

In general, if you are constantly hitting memory thresholds, it is recommended that you consider scaling up (adding more memory, altering these configuration values) and then scaling out. You should scale up first because resources are better managed in SQL Server 2008 Reporting Services, as noted above and in Scaling Up Reporting Services 2008 vs. Reporting Services 2005: Lessons Learned.

Memory Configurations for SQL Server 2005 Reporting Services

As noted in the Reporting Services Scale-Out Architecture technical note, you can scale up by adding more memory as well. But the impact may not be as dramatic; one scenario might see a doubling of memory and CPU (to 16 GB RAM and 8 cores) with only 1/3 increase in capacity. Nevertheless, there are things you can still do to scale up SQL Server 2005 Reporting Services prior to scaling out.
As with SQL Server 2008 Reporting Services, you can use memory configurations to address memory threshold issues. There are two primary memory configurations in SQL Server 2005 Reporting Services that we recommend you change if you’re constantly hitting memory thresholds:

• MemoryLimit: This configuration is similar to WorkingSetMinimum in SQL Server 2008. Its default is 60% of physical memory. Increasing the value helps Reporting Services handle more requests. After this threshold is hit, no new requests are accepted.
• MaximumMemoryLimit: This configuration is similar to WorkingSetMaximum in SQL Server 2008. Its default is 80% of physical memory. But unlike the SQL Server 2008 version, when its threshold is met, it starts aborting processes instead of rejecting new requests.

While we do recommend memory configuration changes if you’re constantly hitting memory thresholds, note that changing these values might create or exacerbate other resource contentions.

Conclusion

This concludes our four-part “Building and Deploying Large Scale SQL Server Reporting Services Environments” technical note series. We hope that this set of technical notes will help you design, manage, and maintain your enterprise SQL Server Reporting Services environments.

SSRS Some Concepts

Create custom assembly


1) Open Visual Studio 2008 and create a new Project.

2) The New Project window will open; Select the Visual C# node; from the right pane select Class Library. Complete the following:

In the Name field enter "rsCustomAssembly"

In the Location field enter "C:\Visual Studio 2008\Projects"

In the Solution field enter "rsCustomAssembly"

Click OK when you are done

3) Delete the default class.cs file created. When prompted click OK.

4) Add a new class by right clicking on the solution name and selecting Add >> Class...

5) Name the new class Utilities.

6) Enter the code below into the class file and save it.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;



namespace rsCustomAssembly

{

public class Utilities

{

///

/// Sample concatenate string method; not intended for production use.

/// Note: the string object already provides a concatenate method - string.concat(object o)

///

///

///

///

public static string Concat(string str1, string str2)

{

return str str1+ " " + str2;

}



public static string HelloReportingServices()

{

return "Reporting Services";

}

}

}

Your screen should look similar



7) From the top menubar you will find Build; Click this and select rsCustomAssembly from the list. The bottom status bar should presenet Build succeeded. If not, check your code again to make sure it follows the description above.

Copy the assembly to the Visual Studio Private assemblies and Report Server bin

You must copy you assembly (.dll) to the report designer and report server folders before you can use the assembly in Reporting Services. You can find your assembly by locating the bin folder in your project. For example: C:\Visual Studio 2008\Projects\rsCustomAssembly\rsCustomAssembly\bin\Debug

Note: the location is dependent on your install

For Report Designer: C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies

For Report Server: C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin

You have completed with this step.

Create a new Report

This step we will perform the following:

Create a new report

Reference the custom assembly

Add a new textbox to the report designer with a custom expression

1) Open BIDS and create a new project, like you did in 1) under "Create custom assembly".

2) Under the Project Type pane, select Business Intelligence Projects; from within the Templates pane, select Report Server Project and complete the following:

For the Name field enter SampleReport

For the Location field enter C:\Visual Studio 2008\Projects

For the Solution field enter SampleReport

Click OK

3) From the Solution Explorer, right click on the Reports folder and select Add >> New Item

The Add New Item window will open



4) From within the Templates pane, select Report and leave the default name of Report1.rdl

5) Click Add

Reference the Assembly

1) From the top menu bar select Report >> Report Properties

Note: if you don't see the Report menu item, try placing your cursor anywhere in the design view; you should then see the menu item.

2) The Report Properties window opens. From within the left pane, select References.

3) Click Add under "Add or remove assemblies".

4) Click the ellipsis right of the text box. The Add Reference window will open.

5) Select the Browse tab and navigate to the Report Server bin location, where you copied your .dll, as described earlier in this article under Copy the assembly to the Visual Studio Private assemblies and Report Server bin

you should see a similar value in the reference text box

6) Click OK to save and close the Report Properties window.

Add Expression and run report

1) Drag and drop a Text Box from the Toolbox pane on the left of your design pane. Strech the text box so you have some room for text.

2) Right click the text box you just added and select Expression

3) For the expression value enter :

=rsCustomAssembly.Utilities.Concat("Reporting", "Services")

4) Click OK to save and close the Expression window.

5) To the right of the Design tab, click Preview. You should see the following:

Save your work. You have completed referencing a custom assembly in SQL Server Reporting



SQL SERVER Reporting Services: Create and Call a Custom Assembly



namespace MyAssembly

{

public class Colors

{

public string PercentageToColor(double percentage)

{

string returnValue = string.Empty;



if (percentage < 20)

{

returnValue = "red";

}

else if (percentage < 80)

{

returnValue = "blue";

}

else

{

returnValue = "green";

}



return returnValue;

}

}

}

Once you’ve compiled your class you need to copy the resulting assembly to the directory in which it is accessible from within your report:

· To use it in the report designer, you need to copy it to: C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies

· To make it available for reports that have been deployed to the report server, you need to copy it to: C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin



Before you can access your custom assembly you have to reference the assembly: Open the report properties (Menu: Report – Report Properties) and select the References tab.

Browse to your assembly and define a Class Name and an Instance Name. (The Class Name and the Instance Name or only for non-static methods). Make sure to prefix your class name with the assembly name.

Now you can call the methods in your assembly from your report, using an expression:

· To call a static method: =..

· To call an instance method: =Code..

So in our example this would be: =Code.TestColor.PercentageToColor(Fields!Percentage.Value)

That's it! You've just created and called a custom assembly from your report...

In one of my future articles I will explain how to pass parameters to the class constructor and I will also tell something about SSRS and .Net Permissions.

Source: ssrstips

Monday, January 17, 2011

Moving the Report Server Databases to Another Computer

You can move the report server databases that are used in a current installation to a SQL Server Database Engine instance that is on a different computer. Both the reportserver and reportservertempdb databases must be moved or copied together. A Reporting Services installation requires both databases; the reportservertempdb database must be related by name to the primary reportserver database you are moving.

Moving a database does not effect scheduled operations that are currently defined for report server items.

Schedules will be recreated the first time that you restart the Report Server service.

SQL Server Agent jobs that are used to trigger a schedule will be recreated on the new database instance. You do not have to move the jobs to the new computer, but you might want to delete jobs on the computer that will no longer be used.

Subscriptions, cached reports, and snapshots are preserved in the moved database. If a snapshot is not picking up refreshed data after the database is moved, clear the snapshot options in Report Manager, click Apply to save your changes, re-create the schedule, and click Apply again to save your changes.

Temporary report and user session data that is stored in reportservertempdb are persisted when you move that database.

SQL Server provides several approaches for moving databases, including backup and restore, attach and detach, and copy. Not all approaches are appropriate for relocating an existing database to a new server instance. The approach that you should use to move the report server database will vary depending on your system availability requirements. The easiest way to move the report server databases is to attach and detach them. However, this approach requires that you take the report server offline while you detach the database. Backup and restore is a better choice if you want to minimize service disruptions, but you must run Transact-SQL commands to perform the operations. Copying the database is not recommended (specifically, by using the Copy Database Wizard); it does not preserve permission settings in the database.

Important 
The steps provided in this topic are recommended when relocating the report server database is the only change you are making to the existing installation. Migrating an entire Reporting Services installation (that is, moving the database and changing the identity of the Report Server Windows service that uses the database) requires connection reconfiguration and an encryption key reset. For more information about migrating a Reporting Services installation, see Migration (Reporting Services).


Detaching and Attaching the Report Server Databases
--------------------------------------------------------------------------------

If you can take the report server offline, you can detach the databases to move them to the SQL Server instance you want to use. This approach preserves permissions in the databases. If you are using a SQL Server 2008 database, you must move it to another SQL Server 2008 instance. After you move the databases, you must reconfigure the report server connection to the report server database. If you are running a scale-out deployment, you must reconfigure the report server database connection for each report server in the deployment.

Use the following steps to move the databases:

1.Backup the encryption keys for the report server database you want to move. You can use the Reporting Services Configuration tool backup the keys.

2.Stop the Report Server service. You can use the Reporting Services Configuration tool to stop the service.

3.Start SQL Server Management Studio and open a connection to the SQL Server instance that hosts the report server databases.

4.Right-click the report server database, point to Tasks, and click Detach. Repeat this step for the report server temporary database.

5.Copy or move the .mdf and .ldf files to the Data folder of the SQL Server instance you want to use. Because you are moving two databases, make sure that you move or copy all four files.

6.In Management Studio, open a connection to the new SQL Server instance that will host the report server databases.

7.Right-click the Databases node, and then click Attach.

8.Click Add to select the report server database .mdf and .ldf files that you want to attach. Repeat this step for the report server temporary database.

9.After the databases are attached, verify that the RSExecRole is a database role in the report server database and temporary database. RSExecRole must have select, insert, update, delete, and reference permissions on the report server database tables, and execute permissions on the stored procedures. For more information, see How to: Create the RSExecRole.

10.Start the Reporting Services Configuration tool and open a connection to the report server.

11.On the Database page, select the new SQL Server instance, and then click Connect.

12.Select the report server database that you just moved, and then click Apply.

13.On the Encryption Keys page, click Restore. Specify the file that contains the backup copy of the keys and the password to unlock the file.

14.Restart the Report Server service.

Backing Up and Restoring the Report Server Databases
--------------------------------------------------------------------------------

If you cannot take the report server offline, you can use backup and restore to relocate the report server databases. You should use the ‘Copy_Only’ option. Beginning in SQL Server 2008, SQL Server Management Studio supports copy-only backups. After you restore the databases, you must configure the report server to use the database on the new server instance. For more information, see the instructions at the end of this topic.

Using BACKUP and COPY_Only to Backup the Report Server Databases
When backing up the databases, set the COPY_OnlyL option and backup type to Full.

Note 
Be sure to back up both the ReportServer and ReportServerTempDB databases as well as their associated log files.


For more information on using SQL Server Management Studio to backup a database, see How to: Back Up a Database (SQL Server Management Studio)

Using RESTORE and MOVE to Relocate the Report Server Databases
When restoring the databases, you can Use the RESTORE WITH NORECOVERY argument to perform the initial restore; this keeps the database in a restoring state, giving you time to review log backups to determine which one to restore. You would then repeat the RESTORE operation but use the RESTORE WITH RECOVERY argument.

Note 
Be sure to restore both the ReportServer and ReportServerTempDB databases as well as their associated log files.


For more information on using SQL Server Management Studio to restore a database, see How to: Restore a Database Backup (SQL Server Management Studio)

How to Configure the Report Server Database Connection
1.Start the Reporting Services Configuration tool and open a connection to the report server.

2.On the Database page, click Change Database. Click Next.

3.Click Choose an existing report server database. Click Next.

4.Select the SQL Server that now hosts the report server database and click Test Connection. Click Next.

5.In Database Name, select the report server database that you want to use. Click Next.

6.In Credentials, specify the credentials that the report server will use to connect to the report server database. Click Next.

7.Click Next and then Finish.

Note 
A Reporting Services installation requires that the SQL Server Database Engine instance include the RSExecRole role. Role creation, login registration, and role assignments occur when you set the report server database connection through the Reporting Services Configuration tool. If you use alternate approaches (specifically, if you use the rsconfig.exe command prompt utility) to configure the connection, the report server will not be in a working state. You might have to write WMI code to make the report server available. For more information, see Reporting Services WMI Provider.

Monday, December 20, 2010

SQL Server Reporting Services: Quick way to get 10 digit year (all the zero’s) using String.Format

Dates are fun. See, by default most dates come out like 5/6/2008. But computers, and programs like them formatted as 05/06/2008. That way, all the dates, no matter what month or day, are all the same length, cool huh?

Well, in Reporting Services, if you have a date field coming back in a dataset, and you want to format it as a 10 digit string, there are about 50 different ways to do it. You can use old VBA Left and Mid etc, or you can use String.Format like..

=String.Format(“{0:MM}/{0:dd}/{0:yyyy}”,CDate(Fields!CalendarDate.Value))

Monday, September 27, 2010

Working with Report Snapshots in SQL Server Reporting Services (SSRS)

Problem


We have many SQL Server Reporting Services (SSRS) reports that use our OLTP systems as data sources. We are always adding new reports and the number of users running these reports is also increasing rapidly. We would like to look at leveraging report snapshots as a way to reduce some of the load on our database servers. Can you provide us with an example of how to do this?

Solution

A report snapshot provides the ability to run a report at a scheduled time or on demand and save the report in the ReportServer database. When a user requests the report, they automatically get the snapshot version stored in the ReportServer database rather than executing the SQL queries to retrieve the data from the underlying data source. You can schedule report snapshots to be run during off-peak hours; this can reduce the load on your database servers. You can also create snapshots manually and select which snapshot version of a report that you would like to run. You can save as many snapshots as you like or you can have Reporting Services limit the number of snapshots that are saved. Besides reducing the load on your database servers, you can use report snapshots to archive reports at a particular point in time for later viewing; e.g. month-end reports, year-end reports, etc. Depending on how many report snapshots you keep, you could use a significant amount of space in the ReportServer database. Read more..

How to: Create and Manage Subscriptions

You can create subscriptions for reports that you access from a SharePoint Web application that is integrated with a report server that runs in SharePoint integrated mode.


To create a subscription, the report must use stored credentials and you must have permission to view the report and create alerts. When you create a subscription, there are three ways to specify its delivery:

You can create a subscription that delivers a document based on the original report to a library within the same SharePoint site as the original report. You cannot deliver the document to a library on another server or another site within the same site collection. To deliver the document, you must have Add Items permission on the library to which the report is delivered.

You can deliver a document based on the original report to a shared folder on the file system. You must select an existing folder that is accessible over a network connection.

If the report server is configured to use the Report Server E-mail delivery extension, you can create a subscription that sends a report or an exported report file (saved in an output format) to your in-box. To receive just the notification without the report or report URL, clear the Include a link to this report and the Show report inside message checkboxes.

When you create a subscription, you can select a file format that creates the report as a certain document type. Not every report works well in every format. Before you select a format in a subscription, open the report and export it to different formats to verify that it appears as expected.


Important:

A subscription that delivers a report to a library or to a shared folder creates a new, static file that is based on the original report, but it is not a true report definition that runs in the Report Viewer Web Part. If the original report has interactive features (such as drillthrough links) or dynamic content, those features will not be available in the static file that is delivered to the target location. If you select a "Web Page" you can preserve some interactivity, but because the document is not an .rdl file that runs in the Report Viewer, clicking through a report creates new pages in the browser session that you must scroll through to return to the site.

You cannot rename the file name extension of an exported report to .rdl and have it run in the Report Viewer Web Part. If you want to create a subscription that provides an actual report, use the Report Server E-mail delivery extension and set options to include a link to the report.

Version settings on the library that contains the delivered document determine whether a new version of the document is created with each delivery. By default, version settings are enabled for each library. Unless you specifically choose No versioning, a new major version of the document will be created upon delivery. Only major versions of the document are created; minor versions are never created as a result of subscription delivery, even if you select a versioning option that allows minor versions. If you limit the number of major versions that are retained, older deliveries will be replaced by newer ones when the maximum limit is reached.

Output formats that you select for a subscription are based on rendering extensions that are installed on the report server. You can only select output formats that are supported by the rendering extensions on the report server. For descriptions of the output formats, see Exporting Reports.

To deliver a report to a SharePoint library

Point to the report in a library.

Click the down arrow next to the report, and select Manage Subscriptions.

Click Add Subscription.

In Delivery type, select SharePoint Document Library.

In Document Library, select a library within the same site.

In File Options, specify the file name and title for the document that will be created by the subscription.

In Output Format, select the application format.

Web archive (MHTML) is the default because it produces a self-contained HTML file, but it will not preserve interactive report features that might be in the original report.

In Overwrite Options, specify an option that determines whether subsequent deliveries overwrite a file. If you want to preserve previous deliveries, you can select Create a file with a unique name. A number will be appended to new files to create a unique file name.

In Delivery Event, specify a schedule or event that causes the subscription to run. You can create a custom schedule, select a shared schedule if one is available, or run the subscription whenever the data is refreshed for a report that runs with snapshot data. For more information about schedules and data processing, see How to: Set Processing Options (Reporting Services in SharePoint Integrated Mode) and How to: Schedule Report and Subscription Processing (Reporting Services in SharePoint Integrated Mode).

In Parameters, if you are creating a subscription to a parameterized report, specify the values that you want to use with the report when the subscription is processed. For more information about parameters, see How to: Set Parameters on a Published Report (Reporting Services in SharePoint Integrated Mode).

To create a subscription for shared folder delivery

Point to the report in a library.

Click the down arrow next to the report, and select Manage Subscriptions.

Click Add Subscription.

In Delivery type, select Windows File Share.

In File Name, enter the name of the file that will be created in the shared folder.

In Path, enter a folder path in Uniform Naming Convention (UNC) format that includes the computer's network name. Do not include trailing backslashes in the folder path. An example path might be \\ComputerName01\Public\MyReports, where Public and MyReports are shared folders.

In Render Format, select the application format for the report.

In Write Mode, choose between None, Autoincrement, or Overwrite. These options determine whether subsequent deliveries overwrite a file. If you want to preserve previous deliveries, you can choose Autoincrement. A number will be appended to new files to create a unique file name. If you choose None, no delivery will occur if a file of the same name already exists in the target location.

In File Extension, choose True to add a file name extension that corresponds to the application file format, or False to create the file without an extension.

In User Name and Password, enter credentials that have write permissions on the shared folder.

In Delivery Event, specify a schedule or event that causes the subscription to run. You can create a custom schedule, select a shared schedule if one is available, or run the subscription whenever the data is refreshed for a report that runs with snapshot data. For more information about schedules and data processing, see How to: Set Processing Options (Reporting Services in SharePoint Integrated Mode) and How to: Schedule Report and Subscription Processing (Reporting Services in SharePoint Integrated Mode).

In Parameters, if you are creating a subscription to a parameterized report, specify the values that you want to use with the report when the subscription is processed. For more information about parameters, see How to: Set Parameters on a Published Report (Reporting Services in SharePoint Integrated Mode).

To create a subscription for report server e-mail delivery

Point to the report in a library.

Click the down arrow next to the report, and select Manage Subscriptions.

Click Add Subscription.

In Delivery type, select E-mail.

In Delivery options, specify an e-mail address to send the report to.

Optionally, you can modify the Subject line. The Subject line uses built-in parameters that capture the report name and time when it was processed. These are the only built-in parameters that can be used. The parameters are placeholders that customize the text that appears in the Subject line, but you can replace it with static text.

Choose Include a link to this report if you want to embed a report URL in the body of the message.

In Report Contents, specify whether you want to embed the actual report in the body of the message.

The rendering format and browser determine whether the report is embedded or attached. If your browser supports HTML 4.0 and MHTML, and you select the Web archive rendering format, the report is embedded as part of the message. All other rendering formats (CSV, PDF, and so on) deliver reports as attachments. Reporting Services does not check the size of the attachment or message before sending the report. If the attachment or message exceeds the maximum limit allowed by your mail server, the report will not be delivered. Choose one of the other delivery options (such as URL or notification) for large reports.

In Delivery Event, specify a schedule or event that causes the subscription to run. You can create a custom schedule, select a shared schedule if one is available, or run the subscription whenever the data is refreshed for a report that runs with snapshot data. For more information about schedules and data processing, see How to: Set Processing Options (Reporting Services in SharePoint Integrated Mode) and How to: Schedule Report and Subscription Processing (Reporting Services in SharePoint Integrated Mode).

In Parameters, if you are creating a subscription to a parameterized report, specify the values that you want to use with the report when the subscription is processed. For more information about parameters, see How to: Set Parameters on a Published Report (Reporting Services in SharePoint Integrated Mode).

To view or modify a subscription

Point to the report.

Click the down arrow, and then click Manage Subscriptions.

Each subscription is identified by the type of delivery. Click the subscription type to view and change the existing properties.

To delete a subscription

Point to the report.

Click the down arrow, and then click Manage Subscriptions.

Click the checkbox next to the subscription, and click Delete.

Thursday, September 9, 2010

Creating a Drill Down Report with SQL Server 2008

In SSRS we can generate drill down reports in very easy way. To create Drill down reports, please follow the following steps.
Step1: Create New Report Project with one Shared DataSource with data query for generating report.
Step1: Create report by taking Table type report as shown bellow

Step3: Now Add Group for product by right clicking on Product Column as shown bellow

Select Add Parent group

Selet Product from Group By DropDown and check Add Group Header, then click OK

The report table looks like this

Step 4: From Row groups > select Product group details (group Details and not group header) > go to Group Properties > select Visibility tab > select Hide > click on Display can be toggled by this group item and select the name of the group then click on OK

Then the  preview the report

Step 5:We can also change the drill down and make it reverse by setting ‘InitialToggleState’ to True and by selecting the group properties (header), visibility tab and click Show

Monday, April 26, 2010

Navigating in SQL Server Reporting Services 2008

We can make three types navigations in SQL Server Reporting Services

•  Navigating in same report(Book Marking)
• Navigating to another Report(Jump to Report)
• Navigation Website(Jum to URL)

 Any text box or image item can be used for intrareport or interreport navigation, for navigation to external resources like web pages and documents, and also to send e-mail. All of these features are enabled by using navigation properties that can be specified in the Textbox Properties or Image Properties dialog.

1.Navigating in same report(Book Marking)

This can be achieve with Book Marking in Reports.
Abookmark is a text box or image in a report that can be used as a navigational link. If you want to allow the user to click an item and navigate to another item, assign a bookmark value to each of the target items.

First, open the Textbox Properties dialog by right-clicking the text box and selecting Properties from the pop-out menu. In the Textbox Properties dialog, then switch to the Action tab in left panel.

To enable Hyperlink to a bookmark, set the Go to bookmark property to the target bookmark.

Using bookmarks to navigate within a report is very easy to do. Each report item has a BookMark property that may be assigned a unique value.

After adding bookmarks to any target items, use the Go to Bookmark Selection list to select the bookmark in the Properties for the Source item. This allows the user to navigate to items within the same report.

 2.Navigating to another Report(Jump to Report)

This powerful feature enables a text box or image to be used as a link to another report by passing parameter values to the target report. The target report can consist of a specific record or multiple records, depending on the parameters passed to the target report.

The following example uses a Products by Category report.That means the Products report shows the list of all Products and the Category Report shows the information about perticular Product. Name text box is used to link to a report that will display the details of a single product record. The Product Details report is very simple. This report accepts a ProductID parameter to filter the records and narrow down to the record requested.

First, open the Textbox Properties dialog by right-clicking the text box and selecting Properties from the pop-out menu. In the Textbox Properties dialog, then switch to the Action tab.

Then select the Go to report radio button, and select the target report from the drop-down list. And Add the perameters for the targetting report by clicking on Add button.

When a product name is clicked on the main report, the viewer redirects to the detailed report for the specific product by passing the ProductID parameter value.

3.Navigation Website(Jum to URL)

The Go to URL option can be used to navigate to practically any report or document content on your report server, and files, folders, and applications in your intranet environment or on the World Wide Web. It can also be set to an expression that uses links stored in a database, custom code, or any other values. It’s more accurate to say that any URI (Uniform Resource Identifier) can be used since a web request is not limited only to a web page or document. With some creative programming, queries, and expressions, your reports could be designed to navigate to a web page, document, e-mail address, Web service request, or a custom web application, directed by data or custom expressions.

Click on "Fx" to enter custom Web URL like http://www.google.co.in/ then click on ok.

Once you run the Report and click on the column which you have set "Go to URL", then you will be redirected to that web site.