如何在報表中格行換色

2010-02-11

9229
0
SQL-SSRS

摘要:如何在報表中格行換色

如何在報表中格行換色
1.Table(資料表) 使用
      iif(rownumber() mod 2 ,color1,color2)

2.Matrix(矩陣表)
   矩陣表不能使用Rownumber 或columnnumber()等方式處理.用此方式都會產生不預期的結果.資料顏色會亂跳.因此要做一些變通手法
   例如我們想要做複數列變成綠色
   一般我們會用iif(RowNumber(Nothing) Mod 2,"Green","White").這樣子的方式處理.

   Step 1. 需在列群組之中新增一個列的群組值.這個群組值是1

   Step 2.插入列中的表頭並且設定運算值為
               =iif(RunningValue(Fields!Country.Value,CountDistinct,Nothing) Mod 2, "AliceBlue", "White")
           如果有多個群組則必須設定如下:
               =iif(RunningValue(Fields!Country.Value & CStr(Fields!Year.Value),CountDistinct,Nothing) Mod 2, "AliceBlue", "White")

   Step 3. 在插入列中的表頭列中設定背景顏色值為
              =Value

   Step 4.在矩陣表中的資料中的背景顏色設定對應來源群組列的欄位名稱.值
            例如:
              =ReportItems!ColorNameTextbox.Value

   Step 5.將所有列表頭部份設定相同顏色內容.

   Step 6.將產生出來的多的列欄位設為最小值.

For a green-bar table, you can simply use a background color expression like this: =iif(RowNumber(Nothing) Mod 2,"Green","White")
However, there is currently no GroupNumber() function on which to base a green-bar calculation in a matrix.
GroupNumber can be (mostly) simulated by using the RunningValue function to get a running distinct count of group expression values.
However, the trickiest part of green-bar in a matrix is the fact that some matrix cells may contain no data at all. This makes the group number calculation incorrect for empty cells.
To work around this, you need to effectively calculate the group number in the row header and then use that value inside the data cells.

Step 1: Add a (fake) inner row grouping
Select the innermost row grouping in your matrix. Right-click and select Insert Group.
For the group expression, group on a constant, such as =1

Step 2: Calculate the name of the color in the inner row grouping header
In the Value property of the newly created grouping header, add a calculation for the desired color based on a running value of a count distinct of the containing group expression.
For example: =iif(RunningValue(Fields!Country.Value,CountDistinct,Nothing) Mod 2, "AliceBlue", "White")
Note: If you have more than one row grouping, you may need to do the count distinct on the combination of all group expressions, like this:
=iif(RunningValue(Fields!Country.Value & CStr(Fields!Year.Value),CountDistinct,Nothing) Mod 2, "AliceBlue", "White")

Step 3: Set the background color of the inner row grouping header to =Value

Step 4: Set the background color of the matrix data cell to the value of the inner row grouping header
For example: =ReportItems!ColorNameTextbox.Value

Step 5: Set the background color of the outer row grouping header
You'll need to use the same expression here that you used for the Value of the inner row grouping header.

Step 6: "Cloak" the inner row grouping header (so it looks like part of of the outer grouping header)
Set the right border style of the outer grouping header to None.
Set the left border style of the inner grouping header to None.
Set the font weight of the inner grouping header to 1 pt.
Set the font color of the inner grouping header to =Value.
Set the CanGrow property of the inner grouping header to False.
Drag the inner grouping header to be as narrow as possible.
Optional: Hand-edit the RDL to set the width of the inner grouping header to 0in.

A full working sample of green-bar matrix is attached.

請大家永躍參與Facebook MSBI 粉思團:http://www.facebook.com/#!/group.php?gid=303757165010

回首頁