本文共 19964 字，大约阅读时间需要 66 分钟。
sql2012 ssrs
A few months ago, I was working on a few SQL Server reports for a client. The one request that I had received (from this client) was to ensure that the finished reports were as ‘all encompassing’ as possible, as they wanted to conduct a considerable amount of Business Analytics, via the reports.
几个月前，我正在为客户端编写一些SQL Server报告。 我从该客户那里收到的一个请求是，确保完成的报告尽可能“全面”，因为他们希望通过报告进行大量的业务分析。
Knowing this, I decided (where possible) to attempt to construct the reports so as to enable the firm to do their ‘what if’ scenarios with a minimal amount of time and effort.
知道了这一点，我决定（在可能的情况下）尝试构建报告，以使公司能够以最少的时间和精力进行“假设分析”方案。
The screen shot below is a sample of the final report AND we are going to look at the steps necessary to create this report. Stay with me!!!
下面的屏幕快照是最终报告的示例，我们将研究创建此报告所需的步骤。 跟我在一起！！！
Opening SQL Server Data Tools (hence forward referred to as SSDT) we begin by creating a Reporting Services Project call ‘Cars’. The project may be created in any directory that is convenient to you.
打开SQL Server数据工具（以下简称为SSDT），我们首先创建一个称为“汽车”的Reporting Services项目。 可以在您方便的任何目录中创建项目。
We click OK to arrive at our drawing surface. We first need to create a brand new ‘report’.
我们单击“确定”到达我们的绘图表面。 我们首先需要创建一个全新的“报告”。
By right clicking on the Reports folder on the ‘Solution Explorer; we see the context menu (see above). We next click ‘Add’ and then ‘New Item’. The ‘new report’ menu is then brought up for you to see.
通过右键单击“解决方案资源管理器”上的“报告”文件夹； 我们看到上下文菜单（见上文）。 接下来，我们单击“添加”，然后单击“新建项目”。 然后会弹出“新报告”菜单供您查看。
We choose the option ‘Report’ and give our report a name: ‘SQLShackCars’. The click Add.
我们选择“报告”选项，并为我们的报告命名：“ SQLShackCars”。 单击添加。
As the reader will note above, we now have a drawing surface, and an area to define and to create datasets parameters (upper left).
正如读者在上面指出的那样，我们现在有一个绘图表面，以及一个用于定义和创建数据集参数的区域（左上方）。
We must now create a ‘Shared Data Source’ to access our data.
现在，我们必须创建一个“共享数据源”来访问我们的数据。
A data source may be likened to a ‘water tap’ on a house. The report will be using the ‘water’ /data thus it is necessary to define where that ‘water’ / data resides.
数据源可以比作房屋上的“水龙头”。 该报告将使用“水” /数据，因此有必要定义“水” /数据所在的位置。
I right click on ‘Shared Data Sources’ and the following context menu is displayed (see below top right).
我右键单击“共享数据源”，然后显示以下上下文菜单（请参见右上方）。
I choose ‘Add New Data Source’ from the context menu (see above). It should be noted from the outset that this data source (as it is shared) is for all intents and purposes a ‘GLOBAL’ data source. The ‘Shared Data Source Properties’ data entry screen is then displayed.
我从上下文菜单中选择“添加新数据源”（请参见上文）。 从一开始就应该注意，此数据源（因为它是共享的）出于所有意图和目的都是“ GLOBAL”数据源。 然后显示“共享数据源属性”数据输入屏幕。
Note that I have given the data source a name (see above) and the only step left is to define the ‘connection string’ to our database (where our report data resides). By clicking ‘Edit’ we arrive at the ‘Connection Properties’ data entry screen (see below).
请注意，我已经给数据源指定了一个名称（见上文），剩下的唯一步骤是定义数据库的“连接字符串”（报表数据所在的位置）。 通过单击“编辑”，我们进入“连接属性”数据输入屏幕（见下文）。
I have taken the liberty completing the name of the connection string and point the string to the correct SQL Server instance and database.
我冒昧地填写了连接字符串的名称，并将该字符串指向正确SQL Server实例和数据库。
We are now ready to create our first report.
现在，我们准备创建我们的第一个报告。
I first want to increase the size of our drawing surface (see below).
我首先要增加绘图表面的尺寸（请参见下文）。
As we want our report to be a flexible as possible, we must create a few ‘user selection’ parameters. These parameters will permit the user to pass ‘desired’ criteria to a stored procedure that will utilize these criteria to pull the correct data from the database table(s) and return the data to the report. This data will then be reflected in a data matrix.
由于我们希望报告尽可能灵活，因此必须创建一些“用户选择”参数。 这些参数将允许用户将“所需”条件传递给存储过程，该存储过程将利用这些条件从数据库表中提取正确的数据并将数据返回给报表。 然后，该数据将反映在数据矩阵中。
In order to create a parameter, we must ensure that our ‘Report Data’ window is visible. Should yours not be visible, then simply click on the ‘View’ Tab of SSDT and the option to show the ‘Report Data’ window may be found there. My screen looks as follows:
为了创建参数，我们必须确保“报告数据”窗口可见。 如果您的看不见，则只需单击SSDT的“查看”选项卡，即可在其中找到显示“报告数据”窗口的选项。 我的屏幕如下所示：
I right click on the Parameters folder (in the upper left hand portion of the screen dump above) and choose ‘Add Parameter’.
我右键单击“参数”文件夹（在上面的屏幕转储的左上角），然后选择“添加参数”。
The ‘Report Parameter Properties’ screen is then shown.
然后显示“报告参数属性”屏幕。
The astute reader will note that I have set the parameter name to ‘Color’, the prompt to ‘Color’, and more importantly the ‘Allow multiple values’ box has been checked.
精明的读者会注意到，我已将参数名称设置为“颜色”，将提示设置为“颜色”， 更重要的是，已选中“允许多个值”框 。
For the time being I am just going to say ‘OK’ and close the parameter selection box. Fear not, we shall be returning to this parameter in a few minutes.
目前，我只想说“确定”并关闭参数选择框。 不用担心，我们将在几分钟后返回此参数。
At this point in time your screen will appear as follows:
此时，您的屏幕将显示如下：
Note that we have created a ‘Color’ parameter which is highlighted in blue. It is left up to the reader to create the ‘Number of doors’ and the ‘Make’ parameters. They will be created in a similar manner as described above.
请注意，我们已经创建了一个'Color'参数，该参数以蓝色突出显示。 由读者自行创建“门数”和“制造”参数。 它们将以如上所述的类似方式创建。
Our final parameters may be seen on the screen dump immediately below.
我们的最终参数可以在下面的屏幕转储中看到。
When our data flows from the ‘house water tap’ through the connection ‘hose’, it must be placed in a ‘bucket’ that will be used when required by our report. This bucket is called a dataset.
当我们的数据从“房屋自来水”流经连接“软管”时，必须将其放置在“桶”中，以便在报告需要时使用。 该存储桶称为数据集。
The datasets that we shall be creating, will provide the parameters (that we have just created) with a complete list of colors, makes and number of doors that exist within our database table.
我们将要创建的数据集将为参数（我们刚刚创建的）提供数据库表中存在的颜色，品牌和门数的完整列表。
To create our dataset we right click on the ‘Datasets’ folder and the following screen is brought up.
要创建我们的数据集，我们右键单击“ Datasets”文件夹，然后出现以下屏幕。
We now click on ‘Add dataset’. Our next data entry screen is then displayed.
现在，我们单击“添加数据集”。 然后显示我们的下一个数据输入屏幕。
We now give our dataset the name ‘Color’ and select the ‘Use a dataset embedded in my report’ radio button. In my case I chose to enter a simple SQL statement to pull all the colors that we have available (within the ‘cars’ table). Note that not all cars come in all colors nor do all makes have all the ‘number of doors’ that may be selected. I am merely trying to pull all possible combinations to have them available for the end user, knowing that some selections may return an empty dataset.
现在，将数据集命名为“颜色”，然后选择“使用嵌入到我的报告中的数据集”单选按钮。 在我的情况下，我选择输入一个简单SQL语句以提取所有可用的颜色（在“汽车”表中）。 请注意，并非所有汽车都有所有颜色，也并非所有品牌都具有可以选择的所有“门数”。 我只是想拉出所有可能的组合以使它们可供最终用户使用，因为他们知道某些选择可能会返回一个空的数据集。
The contents of the Cars table may be seen immediately below.
汽车表的内容可以在下面立即看到。
In order to obtain the necessary data we must now create a local data source which will communicate with the global data source which will pull the necessary data from the table.
为了获得必要的数据，我们现在必须创建一个本地数据源，该数据源将与全局数据源进行通信，该全局数据源将从表中提取必要的数据。
Simply click on the ‘New’ tab shown above and the ‘Data source’ dialog box will open.
只需单击上面显示的“新建”标签，“数据源”对话框就会打开。
I have once again taken the liberty of completing the necessary data fields. I called the local data source ‘SQLShack01’. I then selected the ‘Use shared data source reference’ tab and I selected our ‘SQLShackCars’ global data source.
我再次自由地完成了必要的数据字段。 我将本地数据源称为“ SQLShack01”。 然后，选择“使用共享数据源引用”选项卡，然后选择“ SQLShackCars”全局数据源。
I click OK, and OK to close out.
我单击“确定”，然后单击“确定”关闭。
We can see our finished dataset in the screen dump below:
我们可以在下面的屏幕转储中看到完成的数据集：
In a similar manner we may create the ‘Number of doors’ and the ‘Make’ datasets UTILIZING the local data source (SQLShack01) that we just created above. This is left to the reader HOWEVER when you do go to create these datasets, you will find that the local data source (SQLShack01) is NOW available for you to use.
以类似的方式，我们可以利用上面刚刚创建的本地数据源（SQLShack01）创建“门数”和“制造”数据集。 但是，当您去创建这些数据集时，留给读者的是，您会发现本地数据源（SQLShack01）现在可供您使用。
Our workspace with the three datasets may be seen below:
我们的工作区和三个数据集如下所示：
Returning to our ‘Color’ parameter, I double click on the ‘word’ Color and the parameter dialog box is displayed once again.
返回“颜色”参数，我双击“单词”颜色，然后再次显示参数对话框。
This time however I select the ‘Available Values’ tab.
但是，这次我选择“可用值”标签。
Note that I have opted for the ‘Get values from a query’ radio button. By opening the ‘Dataset’ tab we note that the three datasets that we created in the steps above are present. Merely select ‘Color’.
请注意，我选择了“从查询中获取值”单选按钮。 通过打开“数据集”选项卡，我们注意到存在上面步骤中创建的三个数据集。 只需选择“颜色”。
Having selected ‘Color’ we can finalize our setup as shown below:
选择“颜色”后，我们可以完成设置，如下所示：
The reader will note that I have selected Color for the ‘Label’ field and for the ‘Value’ field. I now click OK to accept and leave the parameter set up properties dialog.
读者会注意到，我已经为“标签”字段和“值”字段选择了颜色。 我现在单击“确定”以接受并保留参数设置属性对话框。
It is left up to the reader to configure the ‘Number of doors’ and ‘Make’ parameters in the same manner as just described.
由读者自行决定以与上述相同的方式配置“门数”和“制造”参数。
Alright, let us have a quick look at what we have thus far.
好吧，让我们快速浏览一下到目前为止。
By clicking the ‘Preview’ tab we shall see that the parameter boxes have been populated.
通过单击“预览”选项卡，我们将看到已填充参数框。
Color
颜色
Make
使
And Number of Doors
和门数
Great!!!! Here comes the real fun!!!
大！！！！ 真正的乐趣来了！！！
The most critical part of our report has yet to be created. This is the dataset that will hold our final results. To channel the data to our fourth and final dataset, we shall create a stored procedure that will accept three incoming parameters (Color, Make and number of doors). These parameters will be used within the predicate and will filter out records that do not match the search criteria.
我们报告中最关键的部分尚未创建。 这是将保存我们最终结果的数据集。 为了将数据引导到第四个也是最后一个数据集，我们将创建一个存储过程，该过程将接受三个输入参数（颜色，制造和门数）。 这些参数将在谓词中使用，并将过滤出与搜索条件不匹配的记录。
To do this we create a new ‘query’ within SQL Server Management Studio.
为此，我们在SQL Server Management Studio中创建一个新的“查询”。
The reader will note that I have created the ‘header’ for our stored procedure (see above). You will also note that I have placed the query prototype within the code. BUT WAIT!! IF we were to pass through a single valued request such as where the color is ‘red’, the number of doors is ‘2’ and that the make was ‘Chevy’ then the predicate / where clause would be ‘where NumOFDoors = @NumOFDoors i.e. 2 and Make = @Make (Chevy) and Color = @Color (red)’
读者会注意到，我已经为存储过程创建了“标头”（请参见上文）。 您还将注意到，我已将查询原型放置在代码中。 可是等等！！ 如果我们要传递一个单值请求，例如颜色为“红色”，门数为“ 2”，品牌为“雪佛兰”，则谓词/ where子句为“ where NumOFDoors = @NumOFDoors即2和Make = @Make（Chevy）和Color = @Color（red）'
We would then be cooking with gas!
然后，我们将用天然气做饭！
Unfortunately it is not that simple when comes to multiple selections.
不幸的是，要进行多重选择并不是那么简单。
What is required, is to pass the stored procedure a COMMA DELIMTED STRING containing the arguments that the end user has selected. In other words should the user wish to see green, red and yellow cars then the argument value passed to the parameter would be GREEN, RED, YELLOW. We shall see how this is achieved when we return to our report project.
所需要的是，将包含最终用户选择的参数的COMMA DELIMTED STRING传递给存储过程。 换句话说，如果用户希望看到绿色，红色和黄色的汽车，那么传递给该参数的参数值将是GREEN，RED，YELLOW。 我们将在返回报告项目时看到如何实现这一目标。
In the interim, we need to program our stored procedure to handle a comma delimited string and this is how I LIKE to achieve this task.
在此期间，我们需要对存储过程进行编程，以处理以逗号分隔的字符串，这就是我希望实现此任务的方式。
The code below accepts the value of @Color and splits the string of the color values, placing the values one by one into a table variable. Placing these values in a table variable permits us to iterate through the string stripping the string of the color that was just processed, shortening the string and doing so until the entire string is processed.
下面的代码接受@Color的值，并拆分颜色值的字符串 ，然后将这些值一一放入表变量中 。 将这些值放在表变量中可以使我们遍历字符串，以剥离刚刚处理过的颜色的字符串，缩短字符串，直到完成整个字符串。
declare @Color table ( Color1 varchar(70) NOT NULL ) declare @Comma varchar(1) Set @Comma = ',' BEGIN DECLARE @Position INT DECLARE @Substringg VARCHAR(8000) SELECT @Position =1 IF (LEN(@Color)<1) OR @Color IS NULL RETURN WHILE @Position!=0 BEGIN SET @Position=CHARINDEX(@Comma,@Color) IF @Position<>0 SET @Substringg=LEFT(@Color,@Position-1) ELSE SET @Substringg=@Color IF(LEN(@Substringg)>0) INSERT INTO @Color1(Color) VALUES(RTRIM(LTRIM(@Substringg))) SET @Color=RIGHT(@Color,LEN(@Color)-@Position) IF LEN(@Color)=0 BREAK END select Color as [Color] Into #Color from @Color1 END
The effect of this piece of code is to convert ‘ Yellow, Red, Green’ into
这段代码的作用是将“黄色，红色，绿色”转换为
Yellow
黄色
Red
红
Green
绿色
Note that in the last step I moved all the values from the table variable into a temporary table. I do this by habit as I may have to issue a GO statement further down within the code. Issuing a GO statement clears any table variables that may exist whilst temporary tables persist until the end of the code.
请注意，在最后一步中，我将所有值从表变量移到了临时表中。 我这样做是出于习惯，因为我可能不得不在代码中进一步发布GO语句。 发出GO语句会清除临时表持续存在直到代码结束时可能存在的任何表变量。
Placing this code snippet into our stored procedure we have now catered for the color parameter. It is left up to the reader to create the necessary code for the number of door and the make of car.
现在，将此代码段放入存储过程中，我们迎合了color参数。 留给读者的是门号和汽车品牌的代码。
Looking at the data structure of our table we find the following:
查看表的数据结构，我们发现以下内容：
Our finished stored procedure is similar to the one shown below:
我们完成的存储过程类似于以下所示的过程：
The actual final query is shown immediately below:
实际的最终查询如下所示：
select cars.make, cars.NoOfDoors, cars.Color from Cars carsinner join #make makeon rtrim(ltrim(cars.Make)) = rtrim(ltrim(make.Make))inner join #Doors doorson rtrim(ltrim(cars.NoOFDOORS)) = rtrim(ltrim(doors.NoOfDoors))inner join #color coloron rtrim(ltrim(cars.Color)) = rtrim(ltrim(Color.Color))
Once again, the astute reader will note that I have used inner joins as a predicate (i.e. the where clause). Now wait a minute!!! Why not use where color in ( ‘Yellow’, ‘Green’ etc). The reason being that one may ONLY HAVE 1000 “in’s”. Any more, say 1001 will cause a query to fail. This is true in SQL Server and Oracle. By using inner joins, the number of filter choices are virtually limitless.
机敏的读者会再次注意到，我已使用内部联接作为谓词 （即where子句）。 现在等一下！！！ 为什么不在其中使用颜色（“黄色”，“绿色”等） 。 原因是一个人可能只有1000英寸。 再说1001，将导致查询失败。 在SQL Server和Oracle中都是如此。 通过使用内部联接，筛选器选择的数量实际上是无限的。
REMEMBER THAT IF YOU ‘SELECT ALL’, it is quite possible for the number of values to exceed 1000.
请记住，如果您“全选”，则值的数量很有可能超过1000。
Now that the store procedure has been created, we must create a fourth and final dataset within our reporting services project. This dataset will contain the client requested filtered data.
现在已经创建了存储过程，我们必须在报告服务项目中创建第四个也是最后一个数据集。 该数据集将包含客户端请求的过滤数据。
Back in our reporting services project we create a dataset which I call ‘FinalDataSet’ which will source its data from the return from the stored procedure (SQLShackCars1) that we just created. Once again we right click on ‘Datasets’ and select ‘Add dataset’. This opens the dataset properties data entry screen. Note that I have completed the stored procedure data capture box with the name of the stored procedure that we just created. The completed properties screen may be seen below:
回到我们的报告服务项目中，我们创建一个数据集，我称之为“ FinalDataSet”，该数据集将从刚创建的存储过程（SQLShackCars1）的返回中获取数据。 我们再次右键单击“数据集”，然后选择“添加数据集”。 这将打开数据集属性数据输入屏幕。 请注意，我已经使用刚刚创建的存储过程的名称完成了存储过程数据捕获框。 完整的属性屏幕如下所示：
Clicking on the ‘Fields’ tab (see the screen dump above upper left), we shall see that the fields that the query returns, are present on your screen(see below).
单击“字段”选项卡（请参见左上方的屏幕转储），我们将看到查询返回的字段显示在屏幕上（请参见下文）。
As we shall be passing a string (to the stored procedure) which potentially contains more than one value i.e. “Green, Yellow), we must change the format of the parameter values for Make, NoOFDoors and Color.
当我们将一个可能包含多个值（即“绿色，黄色”）的字符串（传递给存储过程）传递时，我们必须更改Make，NoOFDoors和Color的参数值的格式。
Right now they reflect as ….
现在，它们反映为……。
Note the value [@Make], [@NoOFDoors] and [@color] (see above).
注意值[@Make]，[@ NoOFDoors]和[@color]（请参见上文）。
We modify [@Make], [@NoOFDoors] and [@color] to be
我们将[@Make]，[@ NoOFDoors]和[@color]修改为
=JOIN(Parameters!Make.Value,",")=JOIN(Parameters!NoOFDoors.Value,",")=JOIN(Parameters!Color.Value,",")
The change to ‘color’ is shown below. Make and NoOFDoors are processed in a similar manner.
对“颜色”的更改如下所示。 Make和NoOFDoors的处理方式相似。
Back on our project surface, we drag in a MATRIX/TABLIX control from the tool box.
回到项目图面，从工具箱中拖动MATRIX / TABLIX控件。
We set the ‘dataset name’ property of the control to FinalDataSet (see below lower right)
我们将控件的“数据集名称”属性设置为FinalDataSet（请参见右下方）
We leave the row grouping box untouched. The reason being, that we do not wish to aggregate the extracted values. Quite the contrary, we wish to view the entries as they are. (See below)
我们保持行分组框不变。 原因是我们不希望汇总提取的值。 相反，我们希望按原样查看条目。 （见下文）
Next, right click on the ‘Column Grouping’ box. We click ‘Delete Group’ (see below)
接下来，右键单击“列分组”框。 我们点击“删除组”（见下文）
We are then asked if we want to delete the ‘Group and the data’ or just the Group. We check the ‘Delete group only’ option (see below).
然后询问我们是要删除“组和数据”还是仅删除组。 我们选中“仅删除组”选项（请参见下文）。
We click OK to finish this task.
我们单击确定以完成此任务。
We are now in a position to populate our table cells.
现在，我们可以填充表格单元格了。
Above you will see the list of fields returned from the stored procedure. I remove the ‘Rows’ grouping box and then I place ‘Make’ in the first text box, ‘NoOFDoors’, in the second and ‘Color’ in the last text box. Please remember that they fields may be in any order. Further, the header name for the fields may be modified to something more meaningful.
在上方，您将看到从存储过程返回的字段列表。 我删除“行”分组框，然后在第一个文本框中放置“制作”，在第二个文本框中放置“ NoOFDoors”，最后一个文本框中放置“颜色”。 请记住，它们的字段可以按任何顺序排列。 此外，可以将字段的标题名称修改为更有意义的名称。
The final Matrix layout may be seen below:
最终的Matrix布局如下所示：
Once again, we click the preview button. Our parameter capture screen appears. We select all the colors (see below)
再次单击预览按钮。 出现参数捕获屏幕。 我们选择所有颜色（见下文）
Next we set the ‘Car Make’. We only wish to see “Fords”
接下来，我们设置“汽车品牌”。 我们只希望看到“福特”
And last but not least we set the number of doors to “Select All”
最后但并非最不重要的一点是，我们将门数设置为“全选”
The result of this query may be seen below:
该查询的结果如下所示：
Thus we have completed our final report.
至此，我们完成了最终报告。
With development costs consistently on the rise and with more being demanded with fewer resources, we (as developers) must think outside the box and try to construct our reports etc. with a view to providing the end client with the best possible ‘user experience’. Often this requires making our reports more ‘all-encompassing’.
随着开发成本持续上升，以及对更多资源的需求越来越少，我们（作为开发人员）必须跳出思路，尝试构建报告等，以便为最终客户提供最佳的“用户体验” 。 通常，这需要使我们的报告更加“包罗万象”。
SQL Server Reporting Services combined with ‘Multiple Select’ parameters permit us to enhance our reports and provide users with this ‘ultimate experience’.
SQL Server Reporting Services结合了“ Multiple Select”参数，使我们能够增强报告并为用户提供“最终体验”。
The database and the Reporting Services package used in this article are available on request. They are complete, small and give you a feeling for what you could really achieve. Why not take them for a test drive?
本文中使用的数据库和Reporting Services程序包可应要求提供。 它们是完整的，很小的，给您带来您真正可以实现的感觉。 为什么不带他们去试驾呢？
Happy programming.
编程愉快。
翻译自:
sql2012 ssrs
转载地址：http://zsswd.baihongyu.com/