SSRS 经验总结

1. 如何去掉由SSRS导出PDF中空白页的问题

 

原因：Body/Page Footer/Page Header中的控件（如Text Box，Table, Matrix等）与Report的右边界有空白，这种情况下SSRS会自动将此空白加到生成的PDF中，从而导致出现PDF空白页。

 

解决方法：将控件的宽度设置（或拖拽）成与Report的宽度一致

 

可以从Properties选择Report/Body/Page Footer/Page Header的查看PageSize/Size/Width等属性

 

 

2. 控件（如Text Box）由于文本过长等原因导致宽度大于Table/Matrix等控件 ，进而无法保持Table/Matrix等控件与Report的宽度一致。这种情况下，可以通过增加一个隐藏列在Table/Matrix等控件中，并且设置（或拖拽）隐藏列的宽度来保证控件与Report的宽度一致。这样做不会导致显示列的宽度变化，也可以消除PDF空白页的问题。

 

 

3. 隐藏Matrix列

 

如果是Row Groups的列，当选中此列，右键会发现Column Visibility选项是置灰不可用的，用其它方式hide列的话，会导致出现空白列。

 

解决方法：在Column Groups中添加此列的分组，并且重新从DataSet中拖入此列到Matrix中，然后选中此列右键选择Column Visibility选项

 

 

4. 设置Table/Matrix隔行不同背景色

 

Table BackgroundColor Expression:

=IIf(RowNumber(Nothing) Mod 2, "PaleGreen", "White")

 

Matrix:

a) 先add一个Column Group 如下图：

 b) 设置Group by: 1

 c) 设置 Expression:

 =IIf(RunningValue(Fields!Client.Value, CountDistinct, Nothing) Mod 2 = 0,"PaleGreen","White")

 d) 设定需要变色的行BackgroundColor Expression:

=ReportItems!running_color.Value

 

参见http://blogs.msdn.com/b/chrishays/archive/2004/08/30/greenbarmatrix.aspx

 

 

5. 得到当前登录用户的ID

User!UserID

 

 

6.SSRS不能使用存储过程OUTPUT参数值，如果需要使用返回参数可以放入结果集返回。

 

参见http://www.purplefrogsystems.com/blog/2008/08/use-stored-procedure-output-parameters-in-ssrs/

 

 

7. column排序

a) Column Groups中，点击右键选择Group Properties...

b) 选择Sorting Tab, 选择Sort by 列 及Order排序

 

8. Add total for each Group

In “Row Groups”, select child group to add total, then hide the parent group if not show it.

e.g.

 

9. Drill down for report
In “Row Groups”, select “Group Properties” on one group, set property as below:

参见：http://www.mssqltips.com/sqlservertip/1329/drill-down-techniques-in-ssrs-with-the-visibility-property/

 

10. Export report into multiple work sheets in excel
In “Row Groups”, select the Group that has the page breaks, set property as below:
The “PageName” will be show as sheet name in excel.

Notice: Above is a new feature of SSRS 2008 R2.

参见：http://www.bidn.com/blogs/bretupdegraff/bidn-blog/234/new-features-of-ssrs-2008-r2-part-1-naming-excel-sheets-when-exporting-reports

 

11. Matrix :Repeat header columns on each page

选中需要repeat行，然后set by below:

 

12. Attempted to divide by zero

In general, you want a pattern like this to avoid division by zero:
=iif(B=0, 0, A / iif(B=0, 1, B))

 

You could also define a generic DivideXByY function in the custom code section of the report that uses IF-ELSE-ENDIF statements (instead of the IIF function call) to perform the division and avoid the DivisionByZero exception.

参见：http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/cc8de00b-e131-45ab-b4a0-a6a865489d22/