Feb 2 2012

Consolidated Download list for sql server

Category: — abhishek.shukla @ 17:47 | |

http://www.softpedia.com/get/Internet/Servers/Database-Utils/Microsoft-SQL-Server.shtml

Tags:

Jan 29 2012

dfdsfsdfs

Category: — abhishek.shukla @ 15:07 | |

Tags:

Jan 28 2012

Best practice for SSRS deployment

Category: SSRS 2008 — abhishek.shukla @ 23:58 | |

While SQL Server Reporting Services (SSRS) platform is not hard to learn and work with, it is still a honestly complex equipment. Successful utilization of SSRS requires a combination of database, administration, report building and data analysis skills. Such a combination of expertise is regularly hard to place together, especially in smaller companies where one person might wear many hats.

As a consultant, I have seen several SQL Server Reporting Services deployments that could have benefited from a few simple SSRS best practices. Here are a few.

Back up the key.

SSRS uses encryption to protect insightful data in its configuration. Things like connection strings and passwords are stored in the back-end ReportServer database and in the configuration files. They are encrypted using an encryption key that’s stored in SSRS. If you go SSRS to another server, you need to use the same encryption key to decrypt all encrypted data. Therefore, proper encryption key management is extremely vital.

When you install SSRS, the first thing you should do is use the and back up the encryption key to a password-protected file. Keep a copy of this key file on the SSRS server and also in a safe spot somewhere on the network. If you ever need to migrate SSRS to another server, you can use the same configuration manager to restore the key from the original server. Otherwise, you will have to manually re-make all your data sources and other encrypted content. That’s not something you want to do, especially if your SSRS server is not functional and you are promptly trying to bring up SSRS on another server. Even though Microsoft has emphasized the importance of keeping a backup of the encryption key, I still sometimes find myself at a and learn that the key isn’t backed up.

Use Windows Active Directory groups to control security.

Systems administrators have long been following the practice of making Windows groups and granting privileges to the group as a replacement for of assigning privileges to party user financial statement. This practice makes a lot of significance, since you can easily add or remove users from a group and make your security management much simpler. But I don’t see this practice as widely used among developers and database administrators. I’ve seen many SSRS installations where whoever was managing privileges assigned individuals access to reports or report folders as a replacement for of making groups like Marketing or Management to simplify administration.

Use report folders to control security.

Just as it makes significance to use Windows groups as a replacement for of user financial statement, you’ll gain a similar advantage by managing security at the folder level. Group your reports into logical groups, house them in a report folder and then assign privileges to the folder rather than to party reports. SSRS also allows you to cascade privileges to the subfolders so you can design a hierarchy of privileges in which higher privilege groups can view all folders, while other groups can view only reports closer to the root folder.

Use saved authentication when configuring report data sources.

While using Windows Authentication is regularly the most recommended option, it doesn’t always work well in SSRS. If you configure a report to use Windows Authentication to connect to a SQL Server database, it only works if the database is on the same server as the SSRS server. But if you need to connect to another , a “double-hop” authentication is needed — one hop between the browser and SSRS and the other hop between SSRS and the . I had to troubleshoot this issue when a report was working while the user was using a browser on the SSRS server but stopped working when SSRS was accessed from another machine, resulting in double-hop authentication. Theoretically, double-hop authentication should work if you properly configure the  Kerberos on the network, but I haven’t seen much success in that area. You are better off configuring a data source to use a SQL Authentication login, or state a Windows account that should be used to connect to SQL Server.

Back up the SSRS back-end databases.

SSRS uses ReportServer and ReportServerTempDB databases, and you should back those up to a location other than the SQL Server machine they run on. You will need them if your server dies and you need to re-make the SSRS environment; otherwise you will have to redeploy all your reports and redo all configurations. I’ve seen companies building backups to a community drive, but if you lose the whole machine, those will do you no excellent.

Practice SSRS migration to another server.

Migrating SSRS to another server is relatively simple: Back up the ReportServer and ReportServerTempDB databases and the encryption keys. Next, restore them on another SQL Server and configure the new SSRS server to use them. Once you restore the encryption key, your new SSRS environment should be identical. This is a excellent exercise, because if your SSRS server ever dies, you will be able to bring a new server online much quicker.

Keep all reports under source control.

Very regularly, a companionship has several people developing reports and deploying them to the server without having a central location to store the files and keep them versioned. Developers are used to working with such as SourceSafe or SVN, but business users are not used to them. Since they regularly build and deploy reports, they should use the same procedure and discipline to check new reports into a source control and check them out if they need to make modifications. Aside from having your reports in a central house, where they are versioned and backed up, you’ll find it much simpler to build a new SSRS environment, pulling the reports from source control as different to collecting the report definition files from the individuals who urban each report.

While the SSRS best practices in this article are intuitive and simple to apply, not every companionship has them in house. I highly recommend that you check your SSRS configuration and make the recommended configurations. In addendum, remember to back up the keys, the databases and practice migrating to another server. After all that work, your SSRS administration will require less time, and you will be better prepared to deal with an unexpected migration to a new SSRS server.

Tags:

Jan 28 2012

The Power of Reporting Services – Caching

Category: SSRS 2008 — abhishek.shukla @ 22:46 | |

If you haven’t heard the phrase “This report is really slow!” there are only a couple of possibilities: you don’t write or administer Reporting Services reports and are at the wrong blog, or you need to come teach me all you know!

There are many reasons a report can generate slowly. The code could be written poorly, the database could be experiencing high volume, or it could be a huge report that returns a lot of data. There are several strategies to make the report faster. You could rewrite and rewrite, tuning it like an OLTP query. You could throw more and more hardware at the server, or add bandwidth to your network. You could review the report with the business users, and try to determine if the scope could be narrowed down to return fewer results.

There’s another option to consider as well – caching a temporary copy of the report.

Caching will store a temporary copy of the report for a specified period of time. If a cache is a hidden store of objects, where is this hidden? You would find it in your ReportServerTempDB database. When a copy is stored, information is written to the dbo.ExecutionCache table.

Setting up the Cache

Pirates have to hide their treasure, so you too must sneak into Reporting Services under the cover of darkness and set up your cache.

To find it, open your report. Go to the Properties tab, and click Execution.

By default, your report will be set to “Always run this report with the most recent data”. (I’ll cover “Render this report from a report execution snapshot” in a separate post.) Here, you have three choices:

  • Do not cache temporary copies of this report – Every time a user runs the report, it will be generated. It’s like a pirate who never buries the treasure chest, but just leaves it sitting on the beach.
  • Cache a temporary copy of the report. Expire copy of report after a number of minutes – When a user runs the report with a specific set of parameters, the report is generated, and then stored in the cache for a specific number of minutes. This is a straight setting. If the report is run at 9:28 AM, and the setting is 240 minutes, it will expire at 12:28 PM the same day. Think of it as burying the treasure under three inches of sand, knowing the wind will blow away an inch an hour.
  • Cache a temporary copy of the report. Expire copy of report on the following schedule – this option allows you to have the report copy expire in cache on a day and time of your choosing. With this option, it’s like the pirate buried a timer, and the chest only pops up when he wants it to.

Does It Really Work?

Or have I been drinking the rum the pirate buried with the treasure?

I took a report from my production environment and tested it. The report was written as:

SELECT *
FROM ViewName

This view runs on the largest table in this database, and aggregates the data. (I didn’t write it. But I will be re-writing it.)

First, I ran the query in Management Studio. It returned 2 rows in 2 minutes, 53 seconds.

Then, I ran the report from Reporting Services. It executed in 2 minutes, 44 seconds.

Then, I enabled caching and set it to expire in 30 minutes. I ran it again one minute later, and it executed in less than one second.

Yes, less than one second. It just popped up.

Go ahead – find your nastiest, ugliest, longest-running report. Set up caching. Time the execution before and after. I’d love it if you posted your results in the comments!

How to Make This Even More Awesome

There is one small dilemma here: the first person to run the report after it expires from the cache will be faced with the nasty, ugly, long-running report. Wouldn’t it be great if there was a way to expire and build the cache on a regular basis, so it was that fast every time?

It can be done! You need to set up a data-driven subscription, using the null delivery provider. The best part: I already wrote about that! http://jesborland.wordpress.com/2010/09/03/the-power-of-reporting-services-subscriptions-–-data-driven-subscriptions-–-null-delivery-provider/

Some Important Notes

A report won’t stay in the cache indefinitely. It will be removed if the report is set to expire, if the report is modified, if the parameters are modified, if the credentials of the data source change, if any other execution options change, or if you delete the report.

You can’t set up caching for a report that uses Windows authentication or one that prompts the user for credentials.

If your report has a parameter, every different parameter option that the report is run with will store another copy in the cache. Example: my report has a parameter of “Folder”. I have ten options – one for each department (Accounting, Marketing, etc.). If the report is run with “Accounting” selected, that will produce one copy in cache. If it is run two minutes later with “Marketing” selected, another copy will be stored in cache. Thus, if you have a report with a parameter that has a lot of options, and different options are frequently selected (for example, a customer drop-down with one hundred options), caching may not be an ideal solution for that report.

Tags:

Jan 28 2012

The Power of Reporting Services Subscriptions

Category: SSRS 2008 — abhishek.shukla @ 22:03 | |
The Power of Reporting Services Subscriptions

Tags:

Jan 28 2012

Types of Reports in SSRS

Category: SSRS 2008 — abhishek.shukla @ 22:00 | |

In Reporting Services, you can use reports in a variety of ways. This topic describes the terminology used to describe the various types of reports and the ways reports get created and used. A single report can have characteristics from more than one type; for example, snapshot reports can be parameterized, ad hoc reports incorporate clickthrough report functionality due to the report models upon which they are based, and subreports can be linked reports.

With Reporting Services, you can create the following types of reports:

There are several ways to think about report type. You might think about it as the way data appears in the report. In Reporting Services, the appearance of data in a report depends on the type of data region you use; for example, tabular reports and chart reports use different data regions. For more information about how to display data, see Data Regions and Maps (Report Builder 3.0 and SSRS). Likewise, the functionality that is available in a report depends on the output format; for example, interactive features like drillthrough reports are available in Web-based export formats but not in all Image-based export formats. A report's final output format affects which features you can include in a report. For more information about design considerations for various export formats, see Exporting Reports (Report Builder 3.0 and SSRS).

There is also terminology associated with the stage of processing a report is in. For more information about the differences between reportdefinitions, publishedreports, and renderedreports, see Reports, Report Parts, and Report Definitions (Report Builder 3.0 and SSRS). Finally, for information about report scheduling and on-demand reports, see Scheduling Reports, Shared Datasets, and Subscriptions.

A parameterized report uses input values to complete report or data processing. With a parameterized report, you can vary the output of a report based on values that are set when the report runs. Parameterized reports are frequently used for drillthrough reports, linked reports, and subreports, connecting and filtering reports with related data.

Using Parameters

Parameters are used in dataset queries to select report data, to filter the result set that the query returns, or to set layout properties used to display or hide parts of a report. You can also specify cascading parameters that populate a series of dependent, drop-down parameter lists. For example, a drop-down list of Region parameter values can be used to populate a drop-down list of City parameter values.

You can use parameters with linked reports by pairing a specific parameter with each linked report to change the outcome. For example, you can create a single regional sales report that shows the sales for all regions, and then use a parameter for each linked report to filter data for a particular region. Specific parameter values can be stored with the report so that users do not have to type values.

Not all parameters may be visible in the report at run time. A report author, report server administrator, or content manager can specify which values to use and then hide the input fields on the report.

Query Parameters and Report Parameters

Reporting Services supports two kinds of parameters: query parameters and report parameters. Query parameters are used during data processing to select or filter data. Query parameters are specified in the syntax of a data processing extension. If a query parameter is specified, a value must be provided either by the user or by default properties to complete the SELECT statement or stored procedure that retrieves data for a report. Report parameters are used during report processing to show a different aspect of the data. A report parameter is usually used to filter a large set of records, but it can have other uses depending on the queries and expressions used in the report. Report parameters differ from query parameters in that they are defined in a report and processed by the report server, while query parameters are defined as part of the dataset query and processed on the database server. For more information, see Parameters (Report Builder 3.0 and SSRS) and Setting Parameter Properties for a Published Report.

A linked report is a report server item that provides an access point to an existing report. Conceptually, it is similar to a program shortcut that you use to run a program or open a file.

A linked report is derived from an existing report and retains the original's report definition. A linked report always inherits report layout and data source properties of the original report. All other properties and settings can be different from those of the original report, including security, parameters, location, subscriptions, and schedules.

You can create a linked report on the report server when you want to create additional versions of an existing report. For example, you could use a single regional sales report to create region-specific reports for all of your sales territories.

Although linked reports are typically based on parameterized reports, a parameterized report is not required. You can create linked reports whenever you want to deploy an existing report with different settings. For more information, see Adding, Modifying, and Deleting Linked Reports.

A report snapshot is a report that contains layout information and query results that were retrieved at a specific point in time. Unlike on-demand reports, which get up-to-date query results when you select the report, report snapshots are processed on a schedule and then saved to a report server. When you select a report snapshot for viewing, the report server retrieves the stored report from the report server database and shows the data and layout that were current for the report at the time the snapshot was created.

Report snapshots are not saved in a particular rendering format. Instead, report snapshots are rendered in a final viewing format (such as HTML) only when a user or an application requests it. Deferred rendering makes a snapshot portable. The report can be rendered in the correct format for the requesting device or Web browser.

Report snapshots serve three purposes:

  • Report history. By creating a series of report snapshots, you can build a history of a report that shows how data changes over time.

  • Consistency. Use report snapshots when you want to provide consistent results for multiple users who must work with identical sets of data. With volatile data, an on-demand report can produce different results from one minute to the next. A report snapshot, by contrast, allows you to make valid comparisons against other reports or analytical tools that contain data from the same point in time.

  • Performance. By scheduling large reports to run during off-peak hours, you can reduce processing impact on the report server during core business hours.

For more information about creating report snapshots, see Setting Report Processing Properties.

A cached report is a saved copy of a processed report. Cached reports are used to improve performance by reducing the number of processing requests to the report processor and by reducing the time required to retrieve large reports. They have a mandatory expiration period, usually in minutes. For more information about how to use cached reports, see Caching Reports (SSRS).

A clickthrough report is a report that displays related data from a report model when you click the interactive data contained within your model-based report. These reports are generated by the report server based on the information contained within the report model. The person who created the model determines which fields are interactive and which fields are returned when a clickthrough report is opened. These field settings cannot be changed in the report authoring tools.

Clickthrough reports are autogenerated. However, you can create an alternative customized report to the model for interactive data items that is displayed instead. The custom report is a standard Reporting Services report. For more information, see Working with Clickthrough Reports.

Drilldown reports initially hide complexity and enable the user to toggle conditionally hidden report items to control how much detail data they want to see. Drilldown reports must retrieve all possible data that can be shown in the report. For more information, see Hiding and Showing Report Items by Adding Drilldown (Report Builder 3.0 and SSRS).

For reports with large amounts of data, consider drillthrough reports instead.

Drillthrough reports are standard reports that are accessed through a hyperlink on a text box in the original report. Drillthrough reports work with a main report and are the target of a drillthrough action for a report item such as placeholder text or a chart. The main report displays summary information, for example in a matrix or chart. Actions defined in the matrix or chart provide drillthrough links to reports that display greater details based on the aggregate in the main report. Drillthrough reports can be filtered by parameters, but they do not have to be. Drillthrough reports differ from subreports in that the report does not display within the original report, but opens separately. They differ from clickthrough reports in that they are not autogenerated from the data source, but are instead custom reports that are saved on the report server. They differ from drilldown reports in that they retrieve the report data only for the specified parameters or for the dataset query. For more information, see Drillthrough Reports (Report Builder 3.0 and SSRS).

A subreport is a report that displays another report inside the body of a main report. Conceptually, a subreport is similar to a frame in a Web page. It is used to embed a report within a report. Any report can be used as a subreport. The subreport can use different data sources than the main report. The report that the subreport displays is stored on a report server, usually in the same folder as the parent report. You can set up the parent report to pass parameters to the subreport.

Although a subreport can be repeated within data regions using a parameter to filter data in each instance of the subreport, subreports are typically used with a main report as a briefing book or as a container for a collection of related reports. For more information, see Subreports (Report Builder 3.0 and SSRS).

For reports with many instances of subreports, consider using drillthrough reports instead.

 

Tags:

Jan 28 2012

Shared Schedules and Custom Schedules

Category: SSRS 2008 — abhishek.shukla @ 21:54 | |

Reporting Services provides shared schedules and report-specific schedules to help you control processing and distribution of reports. The difference between the two types of schedules is how they are defined, stored, and managed. The internal construction of the two types of schedules is the same. All schedules specify a type of recurrence: monthly, weekly, or daily. Within the recurrence type, you set the intervals and range for how often an event is to occur. The type of recurrence pattern and how those patterns are specified is the same whether you create a shared schedule or a report-specific schedule.

Both types of schedules yield the same output and have an almost identical composition in how they specify dates, times, and duration.

  • Shared schedules are portable, multipurpose items that contain ready-to-use schedule information. You create a shared schedule once, and then reference it in a subscription or property page when you need to specify schedule information. Shared schedules can be centrally managed, paused, and resumed. Because shared schedules are system-level items, creating a shared schedule requires system-level permissions. For this reason, a report server administrator or content manager typically creates the shared schedules that are available on your report server. Shared schedules are stored and managed on the report server by using Report Manager.

  • Report-specific schedules are defined in the context of an individual report, subscription, or report execution operation to determine cache expiration or snapshot updates. These schedules are created inline when you define a subscription or set report execution properties. You can create a report-specific schedule if a shared schedule does not provide the frequency or recurrence pattern that you need. To prevent a report from running, you must edit a report-specific schedule manually. Report-specific schedules can be created by individual users.

You can schedule reports, shared datasets, and subscriptions in Report Manager to retrieve data and deliver reports and dataset queries at specific times or during off-peak hours. Schedules can run once or on a continuous basis at intervals of hours, days, weeks, or months. You can:

  • Schedule report delivery in a standard or data-driven subscription.

  • Schedule report history so that new snapshots are added to report history at regular intervals.

  • Schedule when to refresh the data of a report snapshot.

  • Schedule when to refresh the data of a shared dataset

  • Schedule the expiration of a cached report or shared dataset to occur at a predefined time so that it can be subsequently refreshed.

You can create a shared schedule if you want to use the same schedule information for many reports or subscriptions. Shared schedules are defined separately, and then referenced in reports, shared datasets, and subscriptions that need schedule information.

When you create a schedule, the report saves the schedule information in the report server database. The report server also creates a SQL Server Agent job that is used to trigger the schedule. Schedule processing is based on the local time of the report server that contains the schedule. The time format follows the Microsoft Windows operating system standard. For more information about the scheduling engine used in Reporting Services, see Scheduling and Delivery Processor.

Why Use Shared Schedules?

In contrast with specific schedules that you define through report, shared dataset, or subscription properties, shared schedules are easier to manage and maintain for the following reasons:

  • Shared schedules can be managed from a central location, making it easier to compare schedule properties and adjust frequency and recurrence patterns if scheduled operations are running too close together or conflicting with other processes on your server.

  • Allows you to quickly adapt to changes in the computing environment. For example, suppose you have a set of reports that run at 4:00 A.M. after a data warehouse is refreshed. If the data refresh operation is rescheduled or is delayed, you can easily accommodate that change by updating the schedule information in a single shared schedule.

  • If you use only shared schedules, you know precisely when scheduled operations occur. This makes it easier to anticipate and accommodate server loads before performance issues occur. For example, if you decide to schedule computer backups at a specific hour, you can adjust shared schedules to run at different times.

To manage shared schedules, use the Schedules page in Report Manager or the Shared Schedules folder in Management Studio. You can view all the shared schedules that are defined for the report server, pause and resume schedules (on Report Manager only), and select schedules to modify or delete. The Shared Schedules page summarizes the following information about the state of each schedule: frequency, owner, expiration date, and status.

You can tell whether a shared schedule is actively used by:

  • Inspecting the values in the Last Run date, Next Run date, and Status fields on the Shared Schedules page in Report Manager. If a schedule no longer runs because it has expired, the expiration date appears in the Status field.

  • Viewing the Reports page of a given Shared Schedule. This page lists all reports and shared datasets that use the shared schedule.

  • Viewing the report execution log files or trace logs to determine whether reports have been run at the times specified by the schedule. For more information, see Reporting Services Log Files.

 

Tags:

Jan 6 2012

Fix to Report viewer problems in IIS 7 or later

Category: — abhishek.shukla @ 17:20 | |

http://www.codeproject.com/KB/reporting-services/Deploying_SSRS.aspx p>

 

 

 
When we migrate web applications from IIS 6 to IIS 7 or IIS 7.5, we will face some problems in http handlers, mappings etc. I faced some problems with reportviewer control. So, below are all problems I have faced and solutions to them. And one more thing is, the server to which we migrated the application may or may not have all the report viewer dlls available in the system. If they didn't install then we need to install them. Otherwise you will get compilation error as we are using that in our application.

First of all, before proceed what are the dlls needed for the report viewer to run?
Microsoft.reportviewer.common.dll
Microsoft.reportviewer.processingobject.dll
Microsoft.reportviewer.webforms.dll
Microsoft.reportviewer.winforms.dll - For windows applications.

So, to get the dlls you need to install the redistributable packages for 2005/2008. Below are details to get that. Find the matched version[2005/2008] and isntall the correct patch. Once installed you will find all above dlls in the GAC.

Microsoft Report Viewer Redistributable 2008
File name : ReportViewer.exe
Version : 9.00.21022.08
Download Size : 2.8 MB

Microsoft Report Viewer Redistributable 2005
File name : ReportViewer.exe
Version : 2007
Download Size : 1.8 MB

Microsoft Report Viewer Redistributable 2005 SP1 (Upgrade)
File name : VS80-KB933137-X86.exe
Version : 1
Download Size : 1.7 MB

Microsoft Report Viewer Redistributable 2005 SP1 (Full Installation)
File name : ReportViewer.exe
Version : 1.0
Download Size : 1.8 MB

ReportViewer Samples for Microsoft Visual Studio 2008
File name : ReportViewerSamples2008.exe
Version : 1.0
Download Size : 172 KB

ReportViewer Samples for Visual Studio 2005
File name : ReportViewerSamples.exe
Version : 1.0
Download Size : 173 KB

With the installed patch, you can solve the compilation error. But, the report viewer control won't render correctly on the browser. What are the possible problems come?
  1. 'RSClientController' is undefined
  2. Report resource images are not coming or loading.
  3. RSParamaters not defined.
  4. ReportViewerHoverButton not defined etc...
  5. Other javascript errors which caused the report viewer failed to load correct.
The only fix to the problems are below.
Solution:
We need to configure the report viewer auto generated axd files[Reserved.ReportViewerWebControl.axd] in the IIS.
See below pictures to understand it well.
1. Open the IIS by typing the "inetmgr" in run command.
2. Goto your site in the list of web sites and select it as shown below.
3. Now, see the Features view area. Here, you can see all options available for the web site. From all the options select "Handler Mappings".
 
 
4. Now, click on the "Handler Mappings" section then you will see all the mappings applied for that web site. Now, on the right side panel, you are able to see all the options available for the handler mappings. Select the option "Add Managed Handler" as shown below.
5. When you click that link, you will see a popup window with the text boxes as shown below.
Fill, Path = Reserved.ReportViewerWebControl.axd,
Type = Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Name= Reserved.ReportViewerWebControl.axd
Note: Please change the version number[9.0.0.0] in the Type above given depends on your requirement.
6. Click OK and do IISRESET.
Now, browse the page where you have report viewer control. And you see no more issues and everything renders fine. Hope this will help you to understand well and please let me know, if you have any questions or issues.

 

Tags:

Jan 4 2012

Transpose a DataTable using C#

Category: ASP.Net — abhishek.shukla @ 20:19 | |

Introduction

This article helps to transpose (convert rows into columns and columns into rows) a DataTable using C# code in an ASP.NET Web application (can be used in Windows Form as well).

Background

This articles uses .NET Framework 3.5, can be used in any version as DataTable is applicable to all. Readers should be familiar with basic ASP.NET, C# & DataTable concepts to understand this article.

Using the Code

This code uses a DataTable with four columns and three rows as shown below. After calling the method GenerateTransposedTable, it will give you the output which is shown in the second table.

This can be used in the DataGrid control (both in Windows and Web applications). Using a single click, we can transpose a table. This may help us in most of the reporting functionalities.

Here, I have used it in an ASP.NET web page.

Actual Table

Status Phase I Phase II Phase III
Not Started 100 200 300
Partially Completed 101 201 301
Successfully Completed 102 202 302
Blocked 103 203 303
Completed with Conditions 104 204 304
Cannot proceed 105 205 305

Transposed Table

Status Not Started Partially Completed Successfully Completed Blocked Completed with Conditions Cannot proceed
Phase I 100 101 102 103 104 105
Phase II 200 201 202 203 204 205
Phase III 300 301 302 303 304 305 

<table id="TableTopGrid" 
        style="width: 100%; border-left: darkgray 1px solid;"
        cellspacing="0" cellpadding="0" align="center">                           
    <tr>
    <td style="width:100%; background-color: #66ccff" align="center">
        <asp:Button ID="btnTransposeReport" 
            runat="server" Font-Bold="True" 
            Font-Names="Tahoma"
            Font-Size="8pt" Text="Query" 
            Width="75px" ForeColor="Navy" 
            OnClick="btnTransposeReport_Click" />
    </td>
    </tr>
    <tr>
    <td width="100%" align="center" valign="top">
        <asp:DataGrid ID="GridReport" runat="server"
           Height="100%" Width="100%" HorizontalAlign="Center"
           Font-Size="8pt" EditItemStyle-Wrap="true" 
           Font-Names="Tahoma" PageSize="50"
           AutoGenerateColumns="True" 
           AllowPaging="False" AllowSorting="False" GridLines="Both"
           ShowFooter="False" BackColor="AliceBlue">
        <ItemStyle Height="18px" Width="100%" 
            HorizontalAlign="Center" CssClass="Grid" />
        <HeaderStyle Font-Size="8pt" Font-Names="Tahoma" 
            Font-Bold="False" Width="100%" Height="20px"
            ForeColor="White" BackColor="DimGray" 
            HorizontalAlign="Center" CssClass="Grid" />

        </asp:DataGrid>
       </td>
    </tr>
    <tr>
    <td style="width:100%; background-color:DimGray" align="center">
         
    </td>
    </tr>
</table>

Here is the code:

protected void Page_Load(object sender, EventArgs e)
{        
     if (!IsPostBack)
     {
         GridReport.DataSource = <your datatable>;
         // This is the table I shown in Figure 1.1

         GridReport.DataBind();

         // Your other codes here (if any)
     }
}

protected void btnTransposeReport_Click(object sender, EventArgs e)
{
     DataTable inputTable = <your datatable>;
     // Table shown in Figure 1.1

     DataTable transposedTable = GenerateTransposedTable(inputTable);

     GridReport.DataSource = transposedTable;
     // Table shown in Figure 1.2

     GridReport.DataBind();
}

private DataTable GenerateTransposedTable(DataTable inputTable)
{
     DataTable outputTable = new DataTable();

     // Add columns by looping rows

     // Header row's first column is same as in inputTable
     outputTable.Columns.Add(inputTable.Columns[0].ColumnName.ToString());

     // Header row's second column onwards, 'inputTable's first column taken
     foreach (DataRow inRow in inputTable.Rows)
     {
         string newColName = inRow[0].ToString();
         outputTable.Columns.Add(newColName);
     }

     // Add rows by looping columns        
     for (int rCount = 1; rCount <= inputTable.Columns.Count - 1; rCount++)
     {
         DataRow newRow = outputTable.NewRow();

         // First column is inputTable's Header row's second column
         newRow[0] = inputTable.Columns[rCount].ColumnName.ToString();
         for (int cCount = 0; cCount <= inputTable.Rows.Count - 1; cCount++)
         {
             string colValue = inputTable.Rows[cCount][rCount].ToString();
             newRow[cCount + 1] = colValue;
         }
         outputTable.Rows.Add(newRow);
     }

     return outputTable;
}

Conclusion

You can notice that the actual report is transposed (rows into columns and columns into rows) as shown in the figure.

Tags:

Dec 11 2011

SQL Server Reporting Services Reports Performance Debugging and Analysis

Category: SSRS 2008 — abhishek.shukla @ 09:20 | |

Problem

SSRS provides a very user friendly way to author and deploy reports. These reports can be accessed from different platforms where the reports are deployed - reports manager, SharePoint, stand-alone / distributed applications or programmatically using SSRS SOAP endpoints.

Unfortunately, SSRS / BIDS does not provide any high-end debugging tools such as SQL Profiler for analyzing the performance of SSRS reports. In this tip we will look at different ways of debugging and analyzing SSRS reports performance using execution logs and freeware tools.

Solution

Two of the main areas which can provide insight into the performance of a report are SSRS reports execution logs and a Web proxy. The execution logs is pretty obvious, but you maybe wondering why a web / http proxy.

SSRS Reports are mostly deployed on a platform like SharePoint or are accessed by applications over the network. Often it may be that the hosting platform or the network bandwidth is the cause of the performance bottleneck. Analyzing the entire life of the report execution along with the type of content exchanged between the client and server can provide details of each activity that happens during the report execution. Execution Logs SSRS provides a set of standard views to query the report execution logs.

You will find three execution log views in the ReportServer database. If you are using SSRS 2008 R2, you should use view "ExecutionLog3", because the rest of the views are for backward compatibility.

Let's go through an example to better understand this mechanism. I have created a report and intentionally configured the stored procedure query to wait for 25 seconds.

Now log on the "ReportServer" database and query the ExecutionLog3 view and you should be able to find the statistics of the report just executed.

The below screenshot shows the statistics of the report I executed on my machine. If you look carefully at the "TimeDataRetrieval" field, you will see the time taken for retrieving data is approx 25 seconds which is just what I had configured in my stored procedure.

TimeDataRetrieval + TimeProcessing + TimeRendering should be almost equal to the actual time taken for the report request. You can learn more about each of these fields here. different ways of debugging and analyzing ssrs reports As far as debugging and analyzing a single report, this mechanism is okay. But if you want to analyze a huge volume of reports on your report server, manually analyzing the logs for each report is time consuming and inefficient.

Some framework that automates this process is required. Fortunately, you do not need to develop it from scratch, because on Codeplex there are two such frameworks that are available: Server Management Report Samples SCRUBS : SQL Reporting Services Audit, Log,

Management and Optimization Analysis Web / HTTP Proxy Every application that gets accessed over the network using HTTP or HTTPS as the communication protocol, needs a web proxy sooner or later for request and response analysis over the network.

One such great FREE web proxy tool is Fiddler. Download and install Fiddler. Now execute your report from reports manager and check out how Fiddler profiles the exchange between the client and server. It has a view that is of very high value for the report analysis - the "Timeline" tab.

This tab shows which content took what time to download and render. Many times you will find that report execution took little time, still the report is taking a long time to render. You can analyze the traffic using Fiddler and you might end up with the observation that a huge ".gif" file which is a part of the report takes a long time to download and render, which causes the report rendering to be slow. Below is a screenshot of the timeline analyzed by Fiddler for the report I executed. You can make out that the timeline shown here is similar to what is shown in the ExecutionLogs. The difference here is that it shows which content and request took what amount of time.

There is a lot more analysis that you can do with Fiddler, and I leave it up to the reader to explore this tool as per their needs. install the frameworks to analyze your ssrs reports execution logs Next Steps Install the suggested frameworks to analyze your SSRS reports execution logs. Server Management Report Samples SCRUBS : SQL Reporting Services Audit, Log, Management and Optimization Analysis Install Fiddler and check out poorly performing reports to narrow down the point where report execution takes longer than expected.

Tags: