Instead of using parameters, base your security decisions

Instead of using parameters, base your security decisions on the User!UserID parameter. UserID is populated as the result of the Windows authentication mechanism, so it is inherently more reliable than parameters. Use the expression: User!UserID Use the value of this global parameter (hidden from the user) to pass to the query in order to filter the information specific for that user. Measuring and Improving Report Performance In Chapter 14, I discuss techniques and provide suggestions for optimizing report performance. You should keep a few issues in mind while you are looking for ways to improve report performance on your system: The bulk of report execution time is spent executing queries and retrieving results. If you re using SQL Server, tools such as Query Analyzer and Profiler can help you optimize your queries. Database Tuning Advisor can suggest better indexes for your database. The Report Server ExecutionLog table contains data on report performance. If you want a quick look at how long it took to execute certain reports, and where the bulk of the time was spent, TimeDataRetrieval contains the number of milliseconds spent getting data from the report s data source(s). Use the following syntax in Query Analyzer: ReportServer Select * from ExecutionLog with (nolock) order by TimeStart DESC. Make sure to include nolock. The ExecutionLog table is used by the RS runtime and locking it can severely degrade your server s performance. If you don t need data in your report, don t retrieve it. Leveraging database operations such as filtering, grouping, and aggregates can reduce the amount of data that is processed in the report, improving performance. Keep your reports modest in size and complexity. Do users really want to look at a 1,000-page report? If performance is extremely bad even for single users, check the Application Restarts counter in the ASP.NET category. Some antivirus software is known to touch configuration files. For more information, search http://support.microsoft.com/ for articles relating to antivirus and ASP.NET. If performance is slow on the first Web service access after there have not been any accesses for a certain time period, disable the idle timeout on the Performance tab in the Application Pool in IIS Manager. Chapter 21: Ten Cool Tricks with Reporting Services 373

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

Dynamically Creating a Report Query You actually write

Suppressing Objects or Formulas in an Excel Rendering of a Report When you don t want to see a document map or formula in an Excel rendering of your report, you can control the properties of the rendering by using the devise information settings when specifying the report using the URL access method. For example, to suppress a document map from a report in Excel, use the URL link as follows: http://servername/reportserver?/SampleReports/Sales Order Detail&rs:Command=Render&rc:Format=HTML4.0 &rcOmitDocumentMap=True When you want only the data values and no formulas generated in Excel for the entire report, you can use the Device Information Setting of OmitFormulas to suppress formula generation as in the following expression: http://servername/reportserver?/SampleReports/Employee Sales Summary&EmployeeID=38&rs:Command= Render&rs:Format=EXCEL&rc:OmitFormulas=true Securing Reports with a UserID Parameter Suppose that you want to integrate a report containing HR information in your application and it requires a value for Employee ID to specify the employee who is viewing the page. Let s assume that the data in the HR database is keyed by the EmployeeID field. One solution to this requirement is to have your application determine the value of EmployeeID for the current user, and you could pass EmployeeID as a report parameter that ll drive its queries. This seemingly natural solution is less than optimal because your users could change the parameter and pass in another EmployeeID, which leads to unwanted information disclosure. You can try to hide the parameter so that it doesn t show up in the Report Viewer toolbar, or not show the toolbar at all. This may seem like a solution, however, parameter values will still show up in the URLs generated in the report. User entered report parameters offer opportunities to hack data from a report for savvy users. Even if you use a custom encryption, those encryptions seldom are strong enough to withstand a serious hacker. Because of this, a general rule to follow is: Never use report parameters to implement security. 372 Part VII: The Part of Tens

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

Dynamically Creating a Report Query You actually write

Dynamically Creating a Report Query You actually write code to dynamically create a report with parameters. The way you do this is to go to the Data tab in the Report Designer for the report and select the Generic Query Designer. Then enter a code reference like: =Code.GetQuery(Parameters) Then click on the Report Properties menu item on the Report main menu to open the Report Properties dialog box. On the Code tab, define a function like: Public Function GetQuery(repParameters As Parameters) As String End Function The logic in this function can build a query dynamically with string functions. Note that you can refer to parameters in this function with an expression like: repParameters!paramname.Value Changing the Report Manager Folder Icon to a Corporate Logo You may want to customize the icon at the top left of the Report Manager home page and put your corporate logo in its place. If you browse the source of the Report Manager, home page you will see that the file reference for the folder icon is: /Reports/images/48folderopen.jpg Searching around in the program manager, you can locate this file in the directory: C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportManagerimages If you open this file, you ll find that it s a 48 x 48 .jpg image. Therefore, if you can make a similar resolution .jpg of your corporate logo and save this new logo file as 48folderopen.jpg, the next time you refresh your Report Manager, you will see your corporate logo in the top-left corner of the Web page. Chapter 21: Ten Cool Tricks with Reporting Services 371

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

After you create the two parameters for your

After you create the two parameters for your report, you need to add two expressions to the table control in your report. To do this, follow these steps: 1. Click the top-left part of the table to select it. Right-click the table object to display a pop-up menu of options. 2. Select Properties. The Table Properties dialog box appears. 3. Select the Sorting tab and enter the following expression for an ascending sort: =iif(Parameters!Direction.Value= Ascending , Fields(Parameters!SortBy.Value).Value,0) 4. Add the following expression for a descending sort: =iif(Parameters!Direction.Value= Descending , Fields(Parameters!SortBy.Value).Value,0) When you preview the report, you will see two parameters at the top of the report. Enter a value of the column in the Sortby parameter. Simulating the All Parameter Value Situations exist when you want to see on a parameterized report a parameter choice indicating that all values are selected. To do this, when you define the report parameter in the Report Parameter dialog box, define a valid values list of the parameter including the value All. This means that you will see the All value in the list of values for the parameter. In the first case, consider when the parameter in question is a text field like Department. In your dataset expression in the Query Designer, you need to specify in the query the following expression: WHERE department like @department In this case, the All parameter should be mapped to the value of %, which is a wildcard. When the All value is specified, then the like search criteria becomes: WHERE department like % This expression returns rows containing anything in the Department field. Chapter 21: Ten Cool Tricks with Reporting Services 369

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

After you create the two parameters for your

In the next case, consider when your query criteria is an integer. Assume that the report parameter is Year. Suppose that your dataset expression in the Query Designer, you specify a criteria like: >= @MinYear and < = @MaxYear Define a query parameter expression for MaxYear as: =iif(Parameters!Year.Value=0,10000,Parameters!Year.Value) Define a query parameter expression for MinYear as: =iif(Parameters!Year.Value=0,0,Parameters!Year.Value) In this case, the All parameter should be mapped to the value of 0. When All is selected for the parameter value, then the like search criteria expressions become: >= 0 and < = 10000 This will return rows containing any value (other than NULL) in the year field. Using the wildcard method above can be a security risk and could be vulnerable to SQL injection attacks. An alternative way of accomplishing this is to define the following parameter query for the parameter @Year: SELECT All UNION ALL SELECT Year FROM DateDim Then your dataset query expression would be: SELECT YearName, Col2, Col3 FROM FactTable WHERE 1 = CASE WHEN @Year = All THEN 1 WHEN @Year = YearName THEN 1 ELSE 0 END Using this dynamic filter is a very powerful form of using parameters. 370 Part VII: The Part of Tens Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

Chapter 21 Ten Cool Tricks with Reporting Services

In Reporting Services, to simulate the stripe effect when printing, you need to do conditional formatting on the contents of table cells. To change the BackgroundColor to be an expression that alternates between green and white, use this expression for the BackgroundColor property for all cells in the report line: =iif(RowNumber(Nothing) Mod 2, Green , White ) Controlling Page Breaks If you want to better control page breaks within a report, you can do so with expressions on groups in a matrix or table. If you know that you would like to do a page break on a specific row count, you can specify that in the group expression. That way, you can introduce a hidden page break grouping. Just right-click a row on the table grid and select Edit Group from the pop-up menu. In the Expression Edit dialog box that appears, enter the following expression to insert a page break after 20 report lines: =Ceiling(RowNumber(Nothing)/20) Running Totals In some reporting situations, you may want to express the cumulative total for a report column as it changes row by row. In order to do this, you need to enter an expression for the value in the table cell. Let s assume you want to specify a running total for a field called SalesAmt. The following aggregate function will provide you with running totals: =RunningValue(Fields!SalesAmt.Value, Sum, Nothing) Simulating End-User Sorting If you want to use a parameter to provide a dynamic sorting capability, you need to create two report parameters called Sortby and Direction. The values of the Sortby report parameter should be set to the names of the fields by which you need to have the users sort. The values of the Direction parameter should be set to values Ascending, Descending, and None. Then enter the above information into the Report Parameters dialog box that appears. 368 Part VII: The Part of Tens

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Inexpensive Web Hosting services

Chapter 21 Ten Cool Tricks with Reporting Services

Chapter 21 Ten Cool Tricks with Reporting Services In This Chapter Greenbar paper reporting Controlling page breaks Running totals Simulating end-user sorting Simulating the All parameter value Dynamically creating a report query Changing the Report Manager home page icon Suppressing objects or formulas in Excel report rendering Securing reports with the UserID parameter Measuring and improving report performance You can do a bunch of things in Reporting Services that may involve some custom expressions or customization involving a little extra coding to your reports. This section introduces you to some interesting (and cool) tricks you can use to make your reports either stand out or fit into the existing standards you may be required to fulfill. Greenbar Paper Formatting If you work at a company that still runs mainframe computers, you may have seen the old greenbar paper stock. This is the wide paper that mainframe computers use to print out their results. The paper is lined alternatively with green and white stripes to assist in reading across the columns of a report.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Inexpensive Web Hosting services

Part VII The Part of Tens

In this part . . . The Part of Tens distills the information found in various sections throughout the book into lists of ten telling tidbits to enhance your reports. From cool tricks and helpful resources on the Web to more aspects of the BI platform from Microsoft and a list of third party tools that can complement your repertoire, these items can extend you into the ranks of a professional report developer.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Inexpensive Web Hosting services