Answer: A report template also exists as a normal report (RDL file). If you want to customize your own report templates, you can put the previously prepared reports (which will be used as new templates) in a specific folder. By default, the path is:
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
Then, to use these templates, you can right click on the Reports folder in your project and select New Item. You will then see the name of the report templates you added in the above folder
Question 2: Does SQL Server 2005 Reporting Services support horizontal tables which have fixed rows and dynamic columns?
Answer: Note: This Q&A is only for SQL Server 2005 Reporting Services.
By default, there is no native "horizontal table" report item. We can use Matrix to simulate simple horizontal tables:
1. Add a matrix to your report.
2. Add static rows to the matrix: Right-click in the Data cell and select "Add Row". Repeat for the number of fixed rows you want.
3. Drag fields into the Data cells:
You'll notice that the design tool automatically wraps your field reference in the "First" aggregate (e.g. =First(Fields!City.Value)). Since you're doing this in the context of a matrix, the design tool is ensuring that the expression is meaningful even in the context of a subtotal or if the matrix is showing aggregated data rather than detail data.
Note: Please do NOT remove the aggregate expression.
4. Add a column grouping:
1) Right-click on the column header and select "Edit Group".
2) Enter this for the group expression: =RowNumber(Nothing). This will cause the matrix to give you one column per row of data. Since horizontal tables can end up rather wide, you may want your table wrap around to the next "line" after a specific number of columns.
5. Add a list to your report and drag the table into it.
6. Group by a number of rows:
1) Right-click on the list and select Properties. Then click on Edit Details Group.
2) Enter this for the group expression: =Ceiling(RowNumber(Nothing)/3)
This will cause the list to group on every three rows. So you'll get a separate table for every three rows.
7. Adjust the group expression in the matrix:
Edit the column group expression in your matrix and change the RowNumber argument to be the list group name. For example: =RowNumber("list1_Details_Group")
More Information: SQL Server 2008 Reporting Services has a new Date Region control named Tablix, which can be used for the same requirement easily. For more information about Tablix, Please refer to the documents below:
Question 3: How to reset the page number back to 1 every time the report gets a group break?
Answer: Resetting the page number on group breaks is not natively supported. There is a workaround: you can track group breaks in a shared variable, and reset this variable to 1 when reaches a new group break.
Here are the steps:
1. Make sure there's a textbox in the report which contains the group expression.
2. Add shared variables to track the current group and page offset:
Shared offset as Integer
Shared currentgroup as Object
3. Add a custom function to set the shared variables and retrieve the group page number:
Public Function GetGroupPageNumber(group as Object, pagenumber as Integer) as Object
If Not (group = currentgroup)
offset = pagenumber - 1
currentgroup = group
End If
Return pagenumber - offset
End Function
4. Use the function in the page header or footer:
=Code.GetGroupPageNumber(ReportItems!Category.Value,Globals!PageNumber)
Question 4: How to open the drill- through report in a new browser window?
Answer: Generally, if you use the "Jump to URL" option for a textbox, it will redirect to the specific URL in the same browser when the user clicks on the textbox.
To open the link in a new window, you can use syntax similar to this:
="javascript:void window.open ('http://servername/reportserver?/freportfolder/ReportName&Parameter1=" & Fields!Field1.Value & "&Field2=" & Fields!Field2.Value & "&rs:Command=Render','_blank','resizeable=1,toolbar=0,status=0,menu=0,top=20,left=20,width=740,height=730')"
Question 5: How to use multiple datasets?
Answer:You can combine two or more datasets in one report. There are multiple options to achieve this:
• If the data comes from different tables in the same database, join the datasets based on a common key present in both tables.
• Use the Linked Server functionality.
For more information, see Accessing External Data
• Use the OpenRowSet functionality to join data from another database server into the current query.
For more information, see OPENROWSET (Transact-SQL)
Question 6: How to upgrade report from SQL Server 2000 to SQL Server 2005?
Answer:You can use the following steps:
1. Back up ReportServer and ReportServerTemDB.
2. Back up encryption keys from Reporting services 2000 by using RsKeyMgmt.
3. Restore the databases backed up from step 1.
4. Connect to newly restored databases from step 3 from Reporting Services 2005 Configuration manager and database setup.
5. By using the provided credentials, Reporting Services will connect to new databases and perform the upgrade operations.
6. Confirm Reporting services upgrading the database and granting adequate rights to the credential account.
7. Restore backed up keys from step 2. Confirm that everything is displayed as green now.
Question 7: Is Report Builder available in SharePoint Integrated Mode?
Reporting Services and SharePoint Technology Integration
Answer:Yes. After integrating with SharePoint, what you need to do is to add Report Builder Model through Content Types:
1. Open SharePoint website as administrator.
2. Click Report Center website.
3. Click Site Actions list.
4. Select Site Settings and then select Modify Pages Library Settings.
5. On Content Types part, click Add from existing site content types.
6. Select Report Server Content Types from site content types drop-down list.
7. Select Report Builder.
8. Click OK to finish.
After that, you will be able to use Report Builder.
Please Note: Currently, Report Builder 2.0 has been released as a stand-alone installation for SQL Server 2008 Reporting Service. However, Report Builder 1.0 can still be used for SharePoint as a "click-once" application. This function will be included in the future service pack of SQL Server 2008.
Question8: How to manage user to view reports in SharePoint integrated mode?
Answer:In SharePoint website, if the default security settings do not meet your requirements, you can create new permission levels for the specific report server items or operations. Custom security settings are helpful if restricting access to a particular report is required.
To customize permissions of viewing reports, please refer to the following documents:
Question 9: How to refer the reports on the Report Server in SharePoint Services?
Answer:After integrating SQL Server Reporting Services with SharePoint Services, you can refer a SSRS report in SharePoint Services. Here is the steps:
1. Install the SQL Server Reporting Services Add-in for SharePoint
For more information, see How to: Install or Uninstall the Reporting Services Add-in
2. Refer to the documents below for using SQL Server Reporting Services Add-in:
Using the Report Viewer Web Part on a SharePoint Site
Question 10: How to integrate SQL Server Reporting Services and SharePoint Services?
Answer:1. Check the requirements for running reporting service in SharePoint integrated mode:
Requirements for Running Reporting Services in SharePoint Integrated Mode
2. Follow the documents below to configure the server:
Configuring Reporting Services for SharePoint 3.0 Integration
For more information for Reporting Services and SharePoint Technology Integration, please refer to:
Reporting Services and SharePoint Technology Integration
Question 11: How to deploy reports to SQL Server Reporting Services in SharePoint Services integrated mode?
Answer:You can use the following steps:
1. Create a web application in SharePoint with Report Center template.
2. Open Business Intelligence Development Studio and modify report solution properties as below:
TargetDataSourceFolder
Question 12: How to enable the Select All option for a multi-value parameter?
Problem:
When a user defines an available values list for a multi-value parameter, a Select ALL option on the report parameter drop-down list automatically appears. However, if you don’t want to make the parameter multi-value, how to enable the Select All option for the query parameter?
Solution:
Since the single-value query parameter does not provide the Select All option, you cannot query the data without any limitations. Sometimes, the end user may want to ignore the condition for the parameterized query. In this case, you can add the "Select All" option to the parameter dataset by using SQL statement.
Here are the detailed steps:
1. Create a dataset for the parameter using the following query string:
SELECT ‘ALL’
UNION ALL
SELECT Field from table
2. Edit your base dataset query:
The original query:
… AND Field=@Parameter…
Change it to this:
…AND (( Field=@Parameter ))OR(@Parameter=’ALL’))…
3. Set the report parameter.
1) Set the available values as the field created in Step 1.
2) Set the default value of parameter to All.
Question 13: How to improve PDF quality of the report exported in Reporting Services 2005?
Answer: The PDF renderer in Reporting Services 2005 resizes all images that it is given at 96 DPI no matter what DPI the image is when you pass it to the renderer. That means that a 300 DPI image or even a 600 DPI image will be sized in the PDF as if it is only 96 DPI. In other words, your high DPI image may render larger than expected.
Despite sizing the images as if they were 96 DPI, the PDF renderer appears to render higher DPI images at a higher quality than 96 DPI. Even if the sizing being wrong, the image actually is rendering at a higher quality.
A necessary workaround is to size the image to the proper number of inches based on 96 DPI calculations. Then use Bitmap.SetResolution to set the images to at least 300 DPI. That may provide a higher quality image that is the proper number of pixels to fit properly in the report.
Question 14: How to enable anonymous access to Report Server, and is there any security consideration?
Answer: Please note that when using anonymous access, every person accesses the reports under the anonymous user context, and no one will have rights to administer Reporting Services through Report Manager. This is the reason why it is not recommended to use anonymous access in Reporting Services.
In order to differentiate between users, we need to either disable anonymous access or use a custom security extension. We have a sample using Forms Authentication:
Using Forms Authentication in Reporting Services
In some special situations, it may be necessary to use anonymous access. For security considerations, we can use the steps as below:
1. Add the anonymous user to the System User role
2. Create an Item-level role with all of the tasks you want this user to be able to use, and then assign the anonymous user to that role at the root level.
3. Check permissions for the anonymous user on folders that SRS uses:
For example:
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services (and subfolders)
C:\
The temp folder for the anonymous user
Note: Step1 & Step2 can be accomplished through Report Manager only if anonymous access is disabled and the logged-on user is a member of the System Administrator role.
Question 15: Error of EXECUTE permission denied on object 'xp_sqlagent_notify'.
Problem:
In some special cases, you may receive the following error when trying to select the option execution from a report:
EXECUTE permission denied on object 'xp_sqlagent_notify', database '######', schema '###'.
Cause:
Usually, this error is caused by the user missing the "RSExecRole" role in the Master and MSDB system databases.
Solution:
Reporting Services uses a predefined database role called "RSExecRole" to grant report server permissions to the report server database. If you get the error mentioned above, try to create and provision the "RSExecRole" role in Master and MSDB system database.
For more information, see:
How to: Create the RSExecRole
http://technet.microsoft.com/en-us/library/cc281308.aspx
Question 16: How to combine connecting string via parameter?
Answer:You can include parameter reference in the data source connection string and allow the user to choose a data source for the report. For example, with an expression-based connection string, a user who is running a sales report can select a data source for a particular county before running the report.
The following example illustrates the use of a data source expression in a SQL Server connection string. The example assumes you have created a report parameter named ServerName:
="Data Source=" & Parameters! ServerName.Value & "; initial catalog=AdventureWorks"
Question 17: How to migrate SQL Server 2008 Reporting Services to another computer?
Answer: The migration process for Reporting Services includes manual and automated steps. The following tasks are part of a report server migration:
1. Back up database, application, and configuration files.
2. Back up the encryption key.
3. Install a new instance of SQL Server 2008. If you are using the same hardware, you can install SQL Server 2008 side-by-side your existing SQL Server 2000 or 2005 installation. Be aware that if you do this, you might need to install SQL Server 2008 as a named instance.
4. Move the report server database and other application files from your SQL Server 2000 or 2005 installation to your new SQL Server 2008 installation.
5. Move any custom application files to the new installation.
6. Configure the report server.
7. Edit RSReportServer.config to include any custom settings from your previous installation.
8. Optionally, configure custom Access Control Lists (ACLs) for the new Reporting Services Windows service group.
9. Test your installation.
10. Remove unused applications and tools after you have confirmed that the new instance is fully operational.
If you are reusing an existing report server database that you created in a previous installation, be aware that there are restrictions on the editions of SQL Server you use to host the report server database.
