介绍 (Introduction)

With today’s challenging economic times it has become more and more important to manage and rectify changing sales patterns and trends.

在当今充满挑战的经济时代，管理和纠正不断变化的销售模式和趋势变得越来越重要。

In today’s “get together” we shall be expanding our outlook by creating efficient and effective reports utilizing SQL Server Reporting Service 2016 and T-SQL, together with the DAX code that we created in our last “fire side chat”.

在当今的“聚在一起”中，我们将通过利用SQL Server Reporting Service 2016和T-SQL以及我们在上一个“边边聊天”中创建的DAX代码创建高效的报告来扩展我们的视野。

In order to make our reports more powerful and all-encompassing, in this chat, we shall be incorporating date and customer parameters to be utilized by our DAX code.

为了使我们的报告更强大，更全面，在此聊天中，我们将合并日期和客户参数，以供我们的DAX代码使用。

Thus, let’s get started.

因此，让我们开始吧。

入门 (Getting started)

As our point of departure, we shall “pick up” with the Tabular project that we created in a previous “get together”. Should you not have had a chance to work through the discussion, do feel free to glance at the step by step discussion by clicking on the link below:

作为出发点，我们将“拾取”在上一个“聚会”中创建的Tabular项目。 如果您没有机会进行讨论，请单击下面的链接随意浏览逐步讨论：

Management Studio和我们的源代码 (Management studio and our source code)

Opening SQL Server 2016 Management Studio we shall once again have a look at the piece of DAX code that we utilized (see below).

打开SQL Server 2016 Management Studio，我们将再次查看我们使用的DAX代码（请参见下文）。

The code seen above will be the code that we are going to incorporate into a standard T-SQL query and execute via a Linked Server! However, there I go again, getting ahead of myself!!

上面看到的代码将是我们将合并到标准T-SQL查询中并通过链接服务器执行的代码！ 但是，我又去了，超越自己！

创建链接服务器 (Creating the linked server)

In order for us to work our ‘magic’, our first task is to create a linked server which may be utilized within the relational region of SQL Server and which points to our Tabular Analysis Server. The code may be seen below:

为了使我们发挥“魔力”，我们的首要任务是创建一个链接服务器，该服务器可以在SQL Server的关系区域内使用，并且指向我们的表格分析服务器。 该代码可以在下面看到：

The code:

代码：

 USE masterGOEXEC sp_addlinkedserver @server='SQLShackTabularBeer2', -- local SQL name given to the linked server@srvproduct='', -- not used @provider='MSOLAP', -- OLE DB provider @datasrc='STR-SIMON\Steve2016tabular', -- analysis server name (machine name)@catalog='SQLShackTabularBeer1' -- default catalog/database 

Now that we have the code, the only thing left to do is to execute this code.

现在我们有了代码，剩下要做的就是执行该代码。

Now that our linked server has now created, we are ready to code our data extraction query.

既然已经创建了链接服务器，我们就可以对数据提取查询进行编码了。

创建查询 (Creating the query)

Opening Management Studio from the relational side, we simply open a new query (see below)

从关系方面打开Management Studio，我们只需打开一个新查询（见下文）

We begin with our “Use” statement and point to our relational database. Having said this, the eagle-eyed reader will point out that we should be talking about DAX and the Tabular Model.

我们从“使用”语句开始，然后指向我们的关系数据库。 话虽如此，鹰眼的读者将指出，我们应该谈论DAX和表格模型。

This is correct and we shall. The purpose of “USE” statement will become more apparent further in our discussion, thus for the meantime, let us just accept the statement.

这是正确的，我们会的。 “ USE”声明的目的在我们的讨论中将变得更加明显，因此，让我们同时接受该声明。

We must declare a few variables. A notable one called @BeerStr, is defined as NVarchar(2000). This variable will contain our DAX query and through the usage of the linked server (that we created above) and the OpenQuery() struct, we shall perform our data extraction.

我们必须声明一些变量。 著名的@BeerStr定义为NVarchar（2000）。 这个变量将包含我们的DAX查询，并通过使用链接服务器（我们在上面创建的）和OpenQuery（）结构的使用，我们将执行数据提取。

As a reminder, in the query that we discussed above, all of the parameters were hard-wired and whilst this is great for a demo, in real life scenarios, this not very useful.

提醒一下，在我们上面讨论的查询中，所有参数都是硬连线的，尽管这对于演示很有用，但在现实生活中，这不是很有用。

We also declare three more variables (see below).

我们还声明了另外三个变量（请参见下文）。

• @startt
  @startt
• @endd
  @endd
• @Customer
  @顾客

For our trials we shall set @startt to ‘201401’ i.e. January 2014, @endd to ‘201412’ i.e. December 2014 and @CustomerNo = 7 (The grocery firm Checker) which we have discussed in our past two “get togethers”.

对于我们的试用，我们将@startt设置为“ 201401”（即2014年1月），将@endt设置为“ 201412”（即2014年12月），并且@CustomerNo = 7（杂货公司Checker），我们在过去的两个“聚在一起”中已经讨论过。

Now here is the tricky part and we really need to discuss this prior to working with our code. Old Fortran programmers will tell you that should you want to place the name “O’Reilly” in a database field (with the apostrophe), then the raw data value must have TWO apostrophes.

现在这是棘手的部分，我们确实需要在使用我们的代码之前进行讨论。 旧的Fortran程序员会告诉您，如果要将名称“ O'Reilly”放置在数据库字段中（带有撇号），则原始数据值必须具有两个撇号。

Thus O’Reilly would look like O’’Reilly and THIS is where things become convoluted.

因此，O'Reilly看起来就像O'Reilly，而THIS就是令人费解的地方。

Our old code appeared similar to this

我们的旧代码看起来与此类似

 EVALUATECALCULATETABLE(SUMMARIZE('beersales554','beersales554'[yearmth],'beersales554'[CustomerNo],'Customer'[CustomerName],"Sales", SUM( beersales554[Amount] )),beersales554[YearMth] >= "201301"  ,beersales554[YearMth] <= "201312", beersales554[CustomerNo] =7)order by 'beersales554'[YearMth] 

Our code for the new query is very different and may be seen below:

我们用于新查询的代码非常不同，可以在下面看到：

The code listing may be seen immediately below.

代码清单可以在下面立即看到。

 EVALUATECALCULATETABLE(  SUMMARIZE(  ''''beersales554'''',''''beersales554''''[yearmth]  ,''''beersales554''''[CustomerNo],  ''''Customer''''[CustomerName],  "Sales", SUM( beersales554[Amount] )  ),  beersales554[YearMth] >= "201301"    ,beersales554[YearMth] <= "201312"  , beersales554[CustomerNo] =7  )  order by ''''beersales554''''[YearMth] 

Building upon this we add a relational select statement and our OpenQuery function which may be seen below:

在此基础上，我们添加一个关系选择语句和我们的OpenQuery函数，如下所示：

Our “Select” statement may be seen above. Do note that the DAX statement is “sandwiched” into a piece of T-SQL code.

我们的“选择”声明可以在上方看到。 请注意，DAX语句“夹在” T-SQL代码中。

As we want to be able to change the start and end dates and customer number dynamically from the reports that we are going to create, we do have a challenge. The challenge is that the dates and customer numbers cannot be placed directly into @startt, @endd and @customer and parsed at runtime. Should we attempt to do so and run the query, we shall encounter a runtime error. We need to circumvent this issue by first creating a text string with the parameters incorporated into the string.

由于我们希望能够从将要创建的报告中动态更改开始日期和结束日期以及客户数量，因此我们确实面临挑战。 挑战在于，日期和客户编号不能直接放入@ startt，@ endd和@customer并在运行时进行解析 。 如果我们尝试这样做并运行查询，我们将遇到运行时错误。 我们需要通过首先创建一个文本字符串并将参数合并到字符串中来规避此问题。

We then execute this string utilizing sp_executeSQL, using the text string as the argument to the parameter.

然后，我们将sp_executeSQL用作字符串，并使用文本字符串作为参数的参数。

 exec sp_executeSQL @Beerstr 

Looking at the query first with hard-wired data we see the following:

首先查看带有硬连线数据的查询，我们将看到以下内容：

Running the query we find the following.

运行查询，我们发现以下内容。

We note the results of the execution of the query and these results are similar to what we have seen in past “get togethers” so why go to all of this bother?

我们注意到查询的执行结果，这些结果与我们过去“聚在一起”所看到的结果相似，那么为什么还要麻烦这些呢？

OK here is why!!! Many of us who work with MDX and DAX find that filtering and/or ensuring that query predicates function correctly is often very challenging.

好的，这就是为什么！ 我们中许多与MDX和DAX一起工作的人都发现，筛选和/或确保查询谓词正确运行通常是非常困难的。

Imagine if we could do a rough filter and then use T-SQL to do the final filtering. In short, we can do more complex filtering with T-SQL.

想象一下我们是否可以做一个粗略的过滤，然后使用T-SQL进行最终过滤。 简而言之，我们可以使用T-SQL进行更复杂的过滤。

Alright, we are now at a point where we can make the final alterations to our query to permit it to be more flexible and to permit us to pass arguments to the query, dynamically at runtime.

好了，我们现在可以对查询进行最终更改，以使其更加灵活，并允许我们在运行时动态地将参数传递给查询。

We begin by removing the hard-wired dates and customer number and replacing these three “fields” with variables”. The changes to the code may be seen below:

我们首先删除固定日期和客户数量，然后用变量替换这三个“字段”。 对代码的更改如下所示：

We then run the query. The results may be seen below for ‘201401’ through ‘201412’ for Customer 7, “Checkers”.

然后，我们运行查询。 可能会在下面针对客户7“ Checkers”的“ 201401”到“ 201412”看到结果。

We note that there is only one record shown. This is correct.

我们注意到仅显示了一条记录。 这是对的。

Changing our dates to ‘201301’ through ‘201312’ we note data for the full year for “Checkers”.

将我们的日期从“ 201301”到“ 201312”更改，我们会记录“跳棋”的全年数据。

Our final alteration to our query is to create a stored procedure (from the query), ensuring that we are able to pass arguments to our @startt, @endd, and @customerNo parameters.

我们对查询的最后更改是（从查询中）创建一个存储过程，以确保我们能够将参数传递给我们的@ startt，@ endd和@customerNo参数。

Now that we have our final stored procedure, let us leave the “Hum Drum” work and create the report about which I was bragging.

现在我们有了最后的存储过程，让我们离开“嗡嗡声鼓”的工作，创建我在吹牛的报告。

从事报告服务 (Working in reporting services)

In order to create our report, we open either Visual Studio 2015 or SQL Server Data Tools 2010 or greater. Should you have never worked with Reporting Services or should you not feel comfortable creating a Reporting Services project, then please do have a look at one of our earlier chats where the “creation” process is described in detail:

为了创建我们的报告，我们打开Visual Studio 2015或SQL Server Data Tools 2010或更高版本。 如果您从未使用过Reporting Services，或者您不满意创建Reporting Services项目，那么请查看我们之前的聊天中详细描述“创建”过程的内容之一：

Having created a new Reporting Services Project (see above) we create a “Shared Data Source” called “OpenQuerry”. The data source points to the relational database where the stored procedure is hosted and that is why I set the SQLSaturday554 database within the “USE” statement, above.

创建了新的Reporting Services项目（见上文）后，我们创建了一个名为“ OpenQuerry”的“共享数据源”。 数据源指向托管存储过程的关系数据库， 这就是为什么我在上面的“ USE”语句中设置SQLSaturday554数据库的原因 。

Now that we have our data source all that is left to do is to add a new report.

现在我们有了数据源，剩下要做的就是添加一个新报告。

Once again we right-click on the “Reports” folder and select “Add” and “New Item” (see below).

我们再次右键单击“ Reports”文件夹，然后选择“ Add”和“ New Item”（如下所示）。

We add a new report as may be seen below:

我们添加了一个新报告，如下所示：

We then click “Add” and we find ourselves back on our drawing surface.

然后，单击“添加”，我们回到自己的绘图表面。

创建必要的数据集 (Creating the necessary datasets)

We right click upon the “Datasets” folder and select “Add Dataset” (see above).

我们右键单击“数据集”文件夹，然后选择“添加数据集”（请参见上文）。

Once again we create a new “Local Data Source” as described in the detail in the previous article. We click the “New” button (circled above) and create our local data source. We click “OK to continue.

再次按照上一篇文章的详细说明创建一个新的“本地数据源”。 我们单击“新建”按钮（在上面圈出）并创建我们的本地数据源。 我们单击“确定”继续。

We find ourselves back on the “Dataset Properties” window as may be seen below:

我们回到“数据集属性”窗口，如下所示：

We choose a “Text” query and extract distinct Customer Numbers and Customer Names as may be seen above. We click “Refresh Fields” and then “OK” to continue.

我们选择一个“文本”查询并提取不同的客户编号和客户名称，如上所示。 我们单击“刷新字段”，然后单击“确定”继续。

Upon clicking “OK”, we find ourselves back on our drawing surface.

单击“确定”后，我们回到自己的绘图表面。

We note that the “Customer” Dataset has been created.

我们注意到已经创建了“客户”数据集。

We repeat this process by creating “Start Date” and “End Date” datasets containing distinct year/month combinations.

我们通过创建包含不同年/月组合的“开始日期”和“结束日期”数据集来重复此过程。

The “Start Date” dataset code is shown above. We repeat the same task for the end date. Now, once again, the “wiseacres” amongst us will be saying “If these are date fields, why not utilize the same dataset for the start date and the end date”. Hold that thought as it will become clear within a few minutes.

上面显示了“开始日期”数据集代码。 我们在结束日期重复相同的任务。 现在，我们中间的“明智之举”将再次说“ 如果这些是日期字段，为什么不使用相同的数据集作为开始日期和结束日期 ”。 保持这种想法，因为几分钟后它就会变得清晰起来。

Now that we have our three data sets , we are in a position to define our parameters that the end user will utilize for reporting .

现在我们有了三个数据集，现在可以定义最终用户将用于报告的参数了。

将参数添加到我们的新报告中 (Adding parameters to our new report)

As a reminder, the stored procedure that we created above, accepts three arguments through the three respective parameters (see below). The arguments will be set (at runtime) within the report and then passed to the Stored Procedure.

提醒一下，我们上面创建的存储过程通过三个相应的参数（请参见下文）接受三个参数。 参数将在报表中设置（在运行时），然后传递给存储过程。

We now shall create these three parameters within our SQL Server Reporting Services report.

现在，我们将在SQL Server Reporting Services报表中创建这三个参数。

Right clicking upon the “Parameters” tab, we select “Add Parameter” (see above).

右键单击“参数”选项卡，我们选择“添加参数”（见上文）。

The “Report Parameter Properties” dialogue box is brought into view.

出现“报告参数属性”对话框。

We call our first parameter start and leave the data type as text (see below).

我们将第一个参数称为开始，并将数据类型保留为文本（请参见下文）。

We now click upon the “Available Values” tab.

现在，我们单击“可用值”选项卡。

The “Report Parameter Properties” dialogue box opens (see above).

“报告参数属性”对话框打开（请参见上文）。

We select “Get Value from a query” (see above).

我们选择“从查询中获取价值”（见上文）。

We select the “Start Date” dataset and set the “Value Field” to “YearMth” and the “Label Field” to “YearMth” as well. We click “OK to continue”. We find ourselves back on our Drawing Screen.

我们选择“开始日期”数据集，并将“值字段”设置为“ YearMth”，并将“标签字段”设置为“ YearMth”。 我们单击“确定以继续”。 我们回到绘图屏幕。

Creating the End Date parameter

创建结束日期参数

The reader will recall that I had created an “End Date” data set as well.

读者会记得我还创建了一个“结束日期”数据集。

This is the reason why.

We re-open this data set, now that we have created the @startt parameter and change the query to include only “YearMth” values greater than or equal to the value that the user selected for the start date.

这就是原因。

现在，我们已经创建了@startt参数，并将查询更改为仅包括大于或等于用户为开始日期选择的值的“ YearMth”值，现在重新打开该数据集 。

This will avoid queries being sent to the server, with a predicate such as

这样可以避免使用谓词将查询发送到服务器

 Where YearMth between ‘201312’ and ‘201301’  

The modified dataset may be seen below:

修改后的数据集如下所示：

Having reached this point, we are in a position to create the end date parameter pointing to the dataset shown above.

至此，我们可以创建指向上面显示的数据集的结束日期参数。

We then create the “Customer” parameter as shown below:

然后，我们创建“ Customer”参数，如下所示：

创建我们的最终数据集 (Creating our final dataset )

The final dataset that we must create, will hold the data that is extracted from the Analysis Services Tabular database. For the sake of ease, we shall call this data set “Final Results”.

我们必须创建的最终数据集将保存从Analysis Services表格数据库中提取的数据。 为了简便起见，我们将此数据集称为“最终结果”。

The result set for “FinalResults” will be obtained from the stored procedure that we created at the start of our “get together” (see above). Once again, we utilize the “OpenQueryDataSource” “Shared Data Source” and simply utilize the “execute” statement (see above).

“ FinalResults”的结果集将从“我们在一起”开始时创建的存储过程中获得（请参见上文）。 再次，我们利用“ OpenQueryDataSource”“共享数据源”，并仅利用“ execute”语句（见上文）。

We click “OK” to continue. We are brought back to our “Data Set” Properties window.

我们单击“确定”继续。 我们回到“数据集”属性窗口。

We click “Refresh Fields” to see the list of fields that the query will bring back to the report (see below).

我们单击“刷新字段”以查看查询将带回到报告中的字段列表（请参见下文）。

We switch to the “Fields” tab as may be seen above.

如上所示，我们切换到“字段”标签。

“Houston, WE HAVE A PROBLEM!!!!!. The issue is that the “Select *” that we utilized in the stored procedure will not work. What we must use is a format similar to “Select CustomerNo, CustomerName…….” etc., instead of Select *.

“休斯顿，我们有一个问题！！！！！。 问题是我们在存储过程中使用的“选择*”将不起作用。 我们必须使用类似于“选择客户编号，客户名称……”的格式。 等等，而不是选择*。

OK now the plot THICKENS!! The field names that are returned from the OpenQuery() are not what we would expect them to be. In order to determine what the actual field names are, we execute the Select statement into a table called junkjunk and from there determine the true field names.

好吧，现在情节稠！ 从OpenQuery（）返回的字段名称不是我们期望的。 为了确定实际的字段名称，我们将Select语句执行到名为junkjunk的表中，然后从中确定真实的字段名称。

The table structure of junkjunk may be seen below.

垃圾邮件的表结构如下所示。

Now that we have the correct name of the fields, we go back into SQL Server Management Studio and change the stored procedure from:

现在我们有了正确的字段名称，我们回到SQL Server Management Studio并将存储过程更改为：

To:

至：

We reprocess our stored procedure.

我们重新处理存储过程。

Meanwhile back in Reporting Services, let us refresh our fields once again.

同时，回到Reporting Services，让我们再次刷新字段。

We note that our field names are now present.

我们注意到，现在存在我们的字段名称。

Our last task within this dataset is to set the “Parameters”.

我们在该数据集中的最后一项任务是设置“参数”。

We click on the Parameters tab (see below).

我们单击“参数”选项卡（见下文）。

We note that our three parameters are present on the screen (see above).

我们注意到，屏幕上显示了我们的三个参数（请参见上文）。

All that we need do is to set the parameter values to those values selected by the end user. This is done as follows:

我们需要做的就是将参数值设置为最终用户选择的那些值。 这样做如下：

The end product looks as follows:

最终产品如下所示：

After clicking “OK” to this screen, we find ourselves back on our drawing surface (see below).

单击“确定”到此屏幕后，我们发现自己回到了绘图表面（见下文）。

We note that the three parameters are present above our drawing surface, albeit greyed out.

我们注意到，这三个参数存在于我们的绘图表面上方，尽管是灰色的。

Our next task is to add a “Chart” from the toolbox to our drawing surface. This chart will show the result set obtained from the database.

我们的下一个任务是将工具箱中的“图表”添加到绘图表面。 该图表将显示从数据库获得的结果集。

As we did in our last “get together”, we add a “column chart” to the drawing surface as shown below:

正如我们在上一个“聚在一起”中所做的那样，我们向绘图表面添加了“柱状图”，如下所示：

Our drawing surface looks as follows:

我们的绘图表面如下所示：

We resize the chart and allocate the “FinalResults” dataset to the “DataSetName” property of the chart (see highlighted below):

我们调整图表的大小，然后将“ FinalResults”数据集分配给图表的“ DataSetName”属性（请参见下面的突出显示）：

设置“图表数据”属性 (Setting the “Chart Data” properties)

Our final task is to set the chart data properties in order for the chart axes to know what they should be displaying and where to display it. Our completed “Chart Data” window is shown below:

我们的最终任务是设置图表数据属性，以使图表轴知道它们应显示的内容以及在何处显示它。 我们完成的“图表数据”窗口如下所示：

让我们对查询进行测试运行 (Let us give our query a test run)

We click the preview button and the report surface changes. We are requested to enter in a “Start Date” (see below).

我们单击预览按钮，报表表面将更改。 我们被要求输入一个“开始日期”（见下文）。

We shall choose ‘201303’ for a reason.

由于某种原因，我们将选择“ 201303”。

If our new “Enddate” dataset is functioning correctly then the first “end date” that we can select is either ‘201303’ or greater.

如果我们新的“结束日期”数据集正常运行，那么我们可以选择的第一个“结束日期”为“ 201303”或更高。

We note that this is in fact so (see above).

我们注意到实际上是这样（见上文）。

Setting the last of the arguments, the Customer parameter to “Checkers”, we obtain the following results when we click the view report button.

将最后一个参数（客户参数）设置为“ Checkers”，当我们单击视图报告按钮时，将获得以下结果。

This is exactly what one would have expected to see.

这正是人们所期望看到的。

大不了!! 为什么以这种方式举报？ (Big Deal!! Why report in this fashion??)

The answer is fairly simple. The big plus of utilizing your DAX code sandwiched in between two “slices” of T-SQL code, is that we may utilize T-SQL code within the predicate.

答案很简单。 利用夹在T-SQL代码的两个“片段”之间的DAX代码的最大好处是，我们可以在谓词中利用T-SQL代码。

Let us say that we change the query slightly and move the customer number out of the DAX query and place it rather together within the T-SQL “Select” statement.

让我们说，我们稍微改变查询和移动客户数出DAX查询和T-SQL“选择”语句中放置它，而在一起。

Doing so makes the query more versatile and we now have the option of viewing one customer at a time or viewing the results for all customers! (See the code below).

这样做使查询变得更加通用，我们现在可以选择一次查看一位客户或查看所有客户的结果！ （请参见下面的代码）。

Further, let us say that when we wish to view the results from all customers, the way to achieve this is to pass -1 to the “CustomerNo” parameter. When we run the query, this is what we shall see.

此外，让我们说，当我们希望查看所有客户的结果时，实现此目的的方法是将-1传递给“ CustomerNo”参数。 当我们运行查询时，这将是我们看到的。

On the other hand, should the incoming argument to the “CustomerNo” parameter not be -1 then the actual results for that “CustomerNo” will be displayed as may be seen below for “CustomerNo” 7.

另一方面，如果“ CustomerNo”参数的传入参数不为-1 ，则将显示该 “ CustomerNo”的实际结果，如下面的“ CustomerNo” 7所示。

Based upon what we originally constructed, in order to have this “one or all” facility, we must alter the code for our “CustomerNo” dataset. The necessary alterations may be seen below:

基于最初构建的内容，为了拥有此“一个或所有”功能，我们必须更改“ CustomerNo”数据集的代码。 必要的更改如下所示：

 select distinct -1 as CustomerNo, 'All' as CustomerName from dbo.Customerunion allSelect distinct CustomerNO, CustomerName from dbo.Customer 

The modified data QUERY code may be seen in Addenda 2.

修改后的数据QUERY代码可以在附录2中看到。

结论 (Conclusions)

Once again we have come to the end of another “get together”.

我们再次走到了另一个“聚在一起”的尽头。

Today we have seen how the DAX code that we have worked with in past “get togethers” may be utilized in combination with T-SQL. Somewhat as cold cuts may be sandwiched in between two slices of “T-SQL” bread. The advantages of doing so are that predicates can be more complex, albeit that we may be pulling more data from the tabular database that may be necessary. Thus it is a toss-up between efficiency and effectiveness.

今天，我们已经看到了过去与“聚会”一起使用的DAX代码如何与T-SQL结合使用。 在两片“ T-SQL”面包之间可以夹一些冷盘。 这样做的好处是谓词可以更复杂，尽管我们可能会从表格数据库中提取更多必要的数据。 因此，这是效率和有效性之间的折衷。

Experimentation is key and I am sure that we shall find another interesting way to utilize what we have just been discussing.

实验是关键，我相信我们将找到另一种有趣的方式来利用我们刚刚讨论的内容。

Happy programming!

编程愉快！

附录1 (Addenda1 )

 use [SQLSaturday554]go  declare @BeerStr nvarchar(2000)declare @startt as varchar(6)declare @endd as varchar(6)declare @customerNO as varchar(1)set @Customerno = 7set @startt = 201401set @endd = 201412 set @beerstr= ' Select *  from Openquery(SQLShackTabularBeer2, ''EVALUATECALCULATETABLE(  SUMMARIZE(  ''''beersales554'''',''''beersales554''''[yearmth]  ,''''beersales554''''[CustomerNo],  ''''Customer''''[CustomerName],  "Sales", SUM( beersales554[Amount] )  ),  beersales554[YearMth] >="' +@Startt    + '",beersales554[YearMth] <="' +@endd + '", beersales554[CustomerNo] ='   + @CustomerNO +  '  )  order by ''''beersales554''''[YearMth]         '')  '    select @BeerStr   exec sp_executeSQL @Beerstr 

附录2 (Addenda 2)

 use [SQLSaturday554]go--Alter procedure FlexSQLShackQuery--(--@startt varchar(6),--@endd   varchar(6),--@customerNo varchar(2) --)-- asdeclare @startt as varchar(6) declare @endd as  varchar(6) set @startt = '201301'set @endd = '201312'declare @BeerStr nvarchar(2000)declare @startt1 as varchar(6)declare @endd1 as varchar(6)declare @customerNo1 as Varchar(2)declare @customerNO as varchar(2)set @Customerno = 7 set @startt1 = @startt set @endd1 = @endd Set @customerNo1 = @customerNoset @beerstr= ' Select 	[beersales554[yearMth]]]  as YearMth ,	[beersales554[CustomerNo]]] as CustomerNo ,	[Customer[CustomerName]]]  as CustomerName ,	[[Sales]]]  as Sales from Openquery(SQLShackTabularBeer2, ''EVALUATECALCULATETABLE(  SUMMARIZE(  ''''beersales554'''',''''beersales554''''[yearmth]  ,''''beersales554''''[CustomerNo],  ''''Customer''''[CustomerName],  "Sales", SUM( beersales554[Amount] )  ),  beersales554[YearMth] >="' +@Startt1    + '",beersales554[YearMth] <="' +@endd1 + '")  order by ''''beersales554''''[YearMth]         '') where (1= (Case when ' + @CustomerNo1 +'= -1 then 1 else 2 end) OR ([beersales554[CustomerNo]]] = ' + @customerNo1 +')) '   --select @beerstrexec sp_executeSQL @Beerstr        

参考文献： (References:)

翻译自: