VS2012 BIDS之Reporting Service/SSRS 项目2--开发过程问题总结(全)

由刚开始的接触到现在做出来一个基本完整的SSRS的项目，学到了比较多的知识，和大家共享。

上一篇学习总结可能有些问题，一起修正和总结。

===================================================================

首先，使用BIDS开发SSRS过程中，感觉还是不太灵活，比如参数的控件《暂且说为“控件”》只能是一行排两个，而且根据参数多值还是单值的类型，控件长度不可调整，导致看起来比较不舒服。

群好友建议：

可以结合winform与C#系统集成，或者Webform弄一个.aspx页面，在.aspx页面上放置各种参数控件，然后传给SSRS报表,aspx页面是自己写的，可以随便改。还可以通过调用dll,弹出友好的提示框等。但是如果这样做，可能要用附属于vs里面的 ReportViewer控制项，设计过程中也会有一定的缺点《后续学习》

以下是效果图：

=================================================================================

现在开始总结：

Q1.上篇讲到共享数据集采用：调用存储过程的形式，但是存错过程的参数（多值参数）会有了一些讲究。因为SSRS传多值参数是以逗号分割的，比如多选’A,B,C‘需要自己分割为'A','B','C'才可用。

Solution:

参考：http://blog.sina.com.cn/s/blog_5ef7acf50100ri6p.html

参考利用Join函数的时候感觉不是很好用，可能是我用错了，另一篇，没研究（以下是链接），我直接用了第二种自定义表值函数的方法来得到分割后的字段值，然后取用。

参考2：http://www.xuebuyuan.com/656470.html

以下是自定义分割函数代码：

 1 USE [DM_ACCN_T1]
 2 GO
 3 
 4 /****** Object:  UserDefinedFunction [dbo].[fnSplitStr]    Script Date: 2016/5/27 16:16:13 ******/
 5 SET ANSI_NULLS ON
 6 GO
 7 
 8 SET QUOTED_IDENTIFIER ON
 9 GO
10 
11 -- =============================================
12 -- Author:        <Ammy,Guo>
13 -- Create date: <2016/05/09>
14 -- Description:    <split paras>
15 -- =============================================
16 ALTER FUNCTION [dbo].[fnSplitStr] (
17     @sText      NVARCHAR(Max),
18     @sDelim     CHAR(1)
19 )
20 
21 RETURNS @retArray TABLE (
22     value   VARCHAR(100)
23 )
24 AS
25 BEGIN
26     DECLARE 
27         @posStart        BIGINT,
28         @posNext        BIGINT,
29         @valLen            BIGINT,
30         @sValue            NVARCHAR(100);
31 
32     IF @sDelim IS NULL 
33     BEGIN
34         IF LEN(@sText)>100 SET @sText = SUBSTRING(@sText, 1, 100)
35         
36         INSERT @retArray (value)
37         VALUES (@sText);
38     END
39     ELSE
40     BEGIN
41         SET @posStart = 1;
42 
43         WHILE @posStart <= LEN(@sText)
44         BEGIN
45             SET @posNext = CHARINDEX(@sDelim, @sText, @posStart);
46 
47             IF @posNext <= 0 
48                 SET @valLen = LEN(@sText) - @posStart + 1;
49             ELSE
50                 SET @valLen = @posNext - @posStart;
51 
52             SET @sValue = SUBSTRING(@sText, @posStart, @valLen);
53             SET @posStart = @posStart + @valLen + 1;
54 
55             IF LEN(@sValue) > 0
56             BEGIN
57                 IF LEN(@sValue)>100 SET @sValue = SUBSTRING(@sValue, 1, 100)
58                 
59                 INSERT @retArray (value)
60                 VALUES (@sValue);
61             END
62         END
63     END
64     RETURN
65 END
66 GO

View Code

在存储过程中调用的时候需要在WHERE参数条件后添加：

AND (D.PROVINCE_CODE IN(SELECT value from [dbo].[fnSplitStr](@PROVINCE ,','))OR('ALL'=@PROVINCE))

以下是存储过程：

  1 USE [DM_ACCN_T1]
  2 GO
  3 
  4 /****** Object:  StoredProcedure [dbo].[SSRS_RAWDATA_SELLTHRU]    Script Date: 2016/5/27 15:44:22 ******/
  5 SET ANSI_NULLS ON
  6 GO
  7 
  8 SET QUOTED_IDENTIFIER ON
  9 GO
 10 
 11 
 12 
 13 
 14 
 15 -- =============================================
 16 -- Author:    Ammy Guo        
 17 -- Create date: 2016/05/09  
 18 -- Description:      SSRS_RAWDATA_SELLTHRU         
 19 -- =============================================
 20 CREATE PROCEDURE [dbo].[SSRS_RAWDATA_SELLTHRU] 
 21     -- Add the parameters for the stored procedure here
 22     --@YEAR INT,
 23     --@YEAR_QUARTER INT,
 24     @STARTDATE DATETIME,
 25     @ENDDATE DATETIME,
 26     @CON_REGION VARCHAR(20),
 27     @CON_SUBREGION VARCHAR(20),
 28     @DISTRIBUTOR VARCHAR(20),
 29     @PROVINCE VARCHAR(20),
 30     @DIJI_CITY VARCHAR(20),
 31     @CHANNEL_MODE VARCHAR(20),
 32     @ITEM_CODE VARCHAR(20),
 33     @BU_CODE VARCHAR(10),
 34     @CHANNEL_TYPE VARCHAR(10),
 35     @BRAND VARCHAR(10)
 36 AS
 37 BEGIN
 38     -- SET NOCOUNT ON added to prevent extra result sets from
 39     -- interfering with SELECT statements.
 40     SET NOCOUNT ON;    
 41     DECLARE @CON_REGION_CODE_SQL VARCHAR(200)
 42     DECLARE @CON_SUBREGION_CODE_SQL VARCHAR(200)
 43     DECLARE @CHANNEL_TYPE_SQL VARCHAR(200)
 44     DECLARE @SPLIT_RESULT VARCHAR(200)
 45 
 46 IF(DATEDIFF(DAY,@STARTDATE,@ENDDATE)>300)
 47     BEGIN
 48     --RAISERROR('The day interval must be within 300! ',16,1)
 49     --SELECT 'The day interval must be within 300!'
 50     RETURN
 51     END
 52 
 53  IF(charindex('ALL',@CON_REGION)>0)
 54     BEGIN 
 55     SELECT @CON_REGION_CODE_SQL ='ALL' 
 56     END 
 57 ELSE
 58     BEGIN SET @CON_REGION_CODE_SQL=@CON_REGION
 59     END
 60 IF(charindex('ALL',@CON_SUBREGION)>0)
 61     BEGIN 
 62     SELECT @CON_SUBREGION_CODE_SQL ='ALL' 
 63     END 
 64 ELSE
 65     BEGIN SET @CON_SUBREGION_CODE_SQL=@CON_SUBREGION
 66     END
 67 IF(charindex('ALL',@DISTRIBUTOR)>0)
 68     BEGIN 
 69     SELECT @DISTRIBUTOR ='ALL' 
 70     END 
 71 ELSE
 72     BEGIN SET @DISTRIBUTOR=@DISTRIBUTOR
 73     END
 74 IF(charindex('ALL',@BRAND)>0)
 75     BEGIN 
 76     SELECT @BRAND ='ALL' 
 77     END 
 78 ELSE
 79     BEGIN SET @BRAND=@BRAND 
 80     END
 81 IF(charindex('999',@PROVINCE)>0)
 82     BEGIN 
 83     SELECT @PROVINCE ='ALL' 
 84     SELECT @DIJI_CITY ='ALL' 
 85     END 
 86 ELSE
 87     BEGIN SET @PROVINCE=@PROVINCE 
 88     END
 89 IF(charindex('9999',@DIJI_CITY)>0)
 90     BEGIN 
 91     SELECT @DIJI_CITY ='ALL' 
 92     END 
 93 ELSE
 94     BEGIN SET @DIJI_CITY=@DIJI_CITY 
 95     END
 96 IF(charindex('ALL',@CHANNEL_MODE)>0)
 97     BEGIN 
 98     SELECT @CHANNEL_MODE ='ALL' 
 99     END 
100 ELSE
101     BEGIN SET @CHANNEL_MODE=@CHANNEL_MODE 
102     END
103 IF(charindex('ALL',@CHANNEL_TYPE)>0)
104     BEGIN 
105     SELECT @CHANNEL_TYPE_SQL ='ALL' 
106     END 
107 ELSE 
108     BEGIN
109     SELECT @CHANNEL_TYPE_SQL =@CHANNEL_TYPE
110     END
111 
112 IF(charindex('ALL',@BU_CODE)>0)
113     BEGIN 
114     SELECT @BU_CODE ='ALL' 
115     END 
116 ELSE
117     BEGIN SET @BU_CODE=@BU_CODE 
118     END
119 
120 SELECT A.SSID AS MainGUID,B.CURRENT_YEAR,B.YEAR_QUARER as Quarter,B.YEAR_MONTH  as Month, B.YEAR_WEEK  as Week,B.DAY as Date, 
121       D.AREA_CN as Area, D.CON_REGION_CODE,D.CON_REGION_CN as CONRegion, D.CON_SUBREIGON_CODE, D.CON_SUBREION_CN as CONSubRegion,
122       D.PROVINCE_CODE,D.PROVINCE_CN as Province,  D.DIJI_CITY_CODE, D.DIJI_CITY_NAME_CN AS City, 
123       D.XIANJI_CITY_CODE,D.XIANJI_CITY_NAME_CN AS District, 
124       D.CITY_CODE , D.CITY_NAME_EN, D.CITY_NAME_CN AS Town, D.CITY_LEVEL as City_Level, 
125       C.CHANNEL_MODE AS CHANNEL_MODE,C.BU AS BU,C.Sale_BU AS SALE_BU,
126       C.FAMILY_CODE AS Family,C.MODEL_CODE AS Model,C.PN AS PN,A.DISTRIBUTOR_CODE AS DistributorCode,
127       A.DEALER_CODE AS DealerCode, E.DEALER_NAME AS DealerName,E.GroupName AS GROUP_NAME,
128       CASE A.CHANNEL_TYPE_CODE WHEN 'Y3C'THEN'3C'WHEN'YKA'THEN 'TC'WHEN'YTC'THEN 'TC'ELSE 'NC'END AS ChannelType,
129       --salesname,salescode
130       H.EMPLOYEE_NAME AS salesname,CAST(H.EMPLOYEE_CODE AS nvarchar(30)) AS salescode,
131       A.ST_QTY, A.ST_AMT, A.ST_AMT_USD, A.ACER_PRICE, A.ACER_PRICE_USD  INTO #TEMP_DATA
132 FROM vw_FS_SELLTHRU_ACT AS A INNER JOIN
133       vw_CB_DATE AS B ON A.YYYYMMDD = B.DAY INNER JOIN
134       vw_CB_PRODUCT AS C ON A.ITEM_CODE = C.PN LEFT OUTER JOIN
135       CB_GEOGRAPHY AS D ON A.CITY_SG_CODE = D.CITY_CODE LEFT JOIN 
136       vw_CB_DEALER_T2 E ON A.DEALER_CODE=E.DEALER_CODE  LEFT OUTER JOIN
137       (SELECT DISTINCT DISTRIBUTOR_CODE ,DISTRIBUTOR_NAME FROM CB_DISTRIBUTOR) F ON A.DISTRIBUTOR_CODE =F.DISTRIBUTOR_CODE LEFT OUTER JOIN 
138       CB_ST_CHANNEL G ON A.CHANNEL_TYPE_CODE =G.CHANNEL_TYPE_CODE LEFT OUTER JOIN
139       CB_EMPLOYEE H ON A.EMPLOYEE_SG_CODE=H.EMPLOYEE_SG_CODE
140 WHERE B.DATE BETWEEN @STARTDATE AND @ENDDATE
141       --AND (B.QUARTER_KEY IN (@YEAR_QUARTER) OR (999999=@YEAR_QUARTER))
142       AND (D.CON_REGION_CODE IN (SELECT value from [dbo].[fnSplitStr](@CON_REGION_CODE_SQL,','))OR ('ALL'=@CON_REGION_CODE_SQL))
143       AND (D.CON_SUBREIGON_CODE IN(SELECT value from [dbo].[fnSplitStr](@CON_SUBREGION_CODE_SQL,','))OR('ALL'=@CON_SUBREGION_CODE_SQL))
144       AND (A.DISTRIBUTOR_CODE IN (SELECT value from [dbo].[fnSplitStr](@DISTRIBUTOR ,','))OR('ALL'=@DISTRIBUTOR))
145       AND (C.BRAND IN (SELECT value from [dbo].[fnSplitStr](@BRAND ,','))OR('ALL'=@BRAND))
146       AND (D.PROVINCE_CODE IN(SELECT value from [dbo].[fnSplitStr](@PROVINCE ,','))OR('ALL'=@PROVINCE))
147       AND (D.DIJI_CITY_CODE IN(SELECT value from [dbo].[fnSplitStr](@DIJI_CITY ,','))OR ('ALL'=@DIJI_CITY))
148       AND (C.CHANNEL_MODE IN (SELECT value from [dbo].[fnSplitStr](@CHANNEL_MODE ,','))OR('ALL'=@CHANNEL_MODE))
149       --AND (A.CHANNEL_TYPE_CODE IN(SELECT value from [dbo].[fnSplitStr](@CHANNEL_TYPE_SQL ,','))OR ('ALL'=@CHANNEL_TYPE_SQL))
150       AND (C.BU IN(SELECT value from [dbo].[fnSplitStr](@BU_CODE,','))OR('ALL'=@BU_CODE))
151       AND (A.ITEM_CODE=@ITEM_CODE OR('ALL'=@ITEM_CODE))
152       ORDER BY A.YYYYMMDD, B.MONTH_KEY,D.CON_REGION_CODE,D.CON_SUBREIGON_CODE
153 
154 
155 SELECT * FROM #TEMP_DATA TEMP 
156 WHERE (TEMP.ChannelType IN(SELECT value from [dbo].[fnSplitStr](@CHANNEL_TYPE_SQL ,','))OR ('ALL'=@CHANNEL_TYPE_SQL))
157 
158 DROP TABLE #TEMP_DATA
159 
160 END
161 
162 
163 GO

View Code

Q2：需要控制查询期间，并提示信息！避免数据量过大导致浏览器负荷过载崩溃。（虽然经过Procedure优化调用后，查询速度明显加快）

因为以前写Winform的经验是，调用存储过程，如果存储过程raiserror的话，在程序catch时，formshow直接弹出提示框即可。所以在procedure中判断查询时间期间，发现这样的话在SSRS报表处理期间就会出错！所以不可行！

Solution:

在RDL页眉处添加文本框，设置表达式，用以提示信息。在procedure中用IF语句，条件不符合，直接return跳出存储过程，查询结果直接是'Return Value'=0即查询结果为空就好了(见以上Procedure代码)。

以下是表达式：

=IIf(DateDiff(DateInterval.Day,Parameters!STARTDATE.Value,Parameters!ENDDATE.Value)>=365,"查询日期期间超出一年范围！","查询日期期间为一年内OK!")

Q3：客户要求参数选择需要对应归总显示，如下需要ChannelType字段，并且只显示'NC,3C,TC',但是WHERE筛选的时候还是需要对应。

select distinct A.CHANNEL_TYPE_CODE,
CASE A.CHANNEL_TYPE_CODE WHEN 'Y3C'THEN'3C'WHEN'YKA'THEN 'TC'WHEN'YTC'THEN 'TC'ELSE 'NC'END AS ChannelType
from CB_ST_CHANNEL A

View Code

Solution:

参数只可选'NC,3C,TC'，先查询筛选除ChannelType字段之外的数据集 INTO #TEMP临时表（并转换ChannelTyep值），然后选择#TEMP的记录筛选 ChannelTyep（详见Procedure）

Q4:参数默认为ALL,并且位于下拉框的第一个。参数数据集查询语句经过SQL定义排序即可。如：Brand品牌参数。

1 SELECT DISTINCT BRAND, IDENTITY(INT,1,1)AS BRAND_CODE INTO #TEMP FROM CB_PRODUCT
2 WHERE BRAND IS NOT NULL
3 
4 SELECT 'ALL'AS BRAND,000 as BRAND_CODE
5 UNION 
6 SELECT * FROM #TEMP 
7 ORDER BY BRAND_CODE ASC
8 
9 DROP TABLE #TEMP

View Code

Q4：一页内下拉Scroll Bar固定冻结表头,每一页重复显示表头。

旧版矩阵/表控件属性的"RpeatHeaderOnNewPage"或者是"RpeatFooterOnNewPage"属性神马的就不要想了，书中说不好用，亲测也不好用。以下解决方案绝对好用，但是要认真按步骤做。

Solution：

每页重复表头：

STEPS：列组右角点击"高级模式" -> 点击行组（静态）<在详细信息上方> -> 看属性窗口"Tablix 成员"属性 -> 修改"RepeateOnNewPage"值为"True"，Ok!Bingo!

页内冻结表头：

STEPS：列组右角点击"高级模式" -> 点击行组（静态）<在详细信息上方> -> 看属性窗口"Tablix 成员"属性 -> 修改"FixedData"值以及"KeepTogether"的值为"True"，Ok!Bingo!

以下是部署到sharepoint之后的效果图：

Q5：item_code（料号）参数可为空，即不传此参数时，直接不筛选此条件：

Solution:

将ITEM_CODE参数属性设置为：可为null值，Procedure里加一句判断即可：

1 IF(@ITEM_CODE IS NULL)
2     BEGIN
3     SET  @ITEM_CODE ='ALL'
4     END
5 ELSE
6     BEGIN SET @ITEM_CODE=@ITEM_CODE 
7     END

View Code

Q6：SSRS导出到excel之后出现单元格合并/隐藏现象

Solution:主要是要注意页眉页脚控件Width/Height设置（对齐），SSRS导出到Excel是以pt来计量，根据转换公式：1in = 2.54cm = 25.4 mm = 72pt = 6pc 来转换。参考：http://blog.csdn.net/hery2002/article/details/45697777

一般Width设置为保留两位小数均可（单位in/cm均可）。excel默认单元格Width：72pt(1in)/Height:18pt(0.25in)

调整后：

结果图：明朗了很多！

posted @ 2016-05-27 17:03 依旧一生有你阅读(1182) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

依旧一生有你

VS2012 BIDS之Reporting Service/SSRS 项目2--开发过程问题总结(全)

公告