{"id":1692,"date":"2013-08-27T00:00:00","date_gmt":"2013-08-27T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/using-dax-to-create-ssrs-reports-the-basics\/"},"modified":"2021-08-24T13:39:51","modified_gmt":"2021-08-24T13:39:51","slug":"using-dax-to-create-ssrs-reports-the-basics","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/using-dax-to-create-ssrs-reports-the-basics\/","title":{"rendered":"Using DAX to create SSRS reports: The Basics"},"content":{"rendered":"
\n

This article is the fourth in a series about the SQL Server Analysis Services (SSAS) tabular model. In the first three articles (one<\/a> | two<\/a> | three<\/a>), we’ve covered the basic components that make up a SQL Server 2012 tabular database and examined how to create Data Analysis Expressions (DAX) queries to access data in those databases. We also looked at how to use SQL Server Management Studio (SSMS) and Microsoft Excel to create DAX queries that retrieve data from an SSAS tabular instance.<\/p>\n

In this article, we move onto a new client application: SQL Server Reporting Services (SSRS). You’ll learn how to incorporate DAX into an SSRS report to return and display tabular data. However, as the previous articles illustrate, using DAX is not always a straightforward process. The Report Designer interface makes it easy enough to use Multidimensional Expressions (MDX) statements in your reports, but not so with DAX. In fact, you’ll find little indication, if any, that you can use DAX. But you can, and knowing how to do so is essential if you’re working with a tabular database configured in DirectQuery mode, which accepts DAX queries but not MDX.<\/p>\n

This article walks you through the process of creating an SSRS report that uses DAX to retrieve tabular data from the AdventureWorks Tabular Model SQL 2012<\/strong><\/code> database, available as a SQL Server Data Tools (SSDT) tabular project from the AdventureWorks CodePlex site<\/a>. On my system, I’ve downloaded the project and used it to implement the database on a local instance of SSAS 2012 in tabular mode. To follow along in this article, you should already have a basic understanding of how to create an SSRS report and write a DAX query.<\/p>\n

Creating a DAX-Based Dataset<\/h2>\n

The key to using DAX to access tabular data from within an SSRS report is to create a dataset that includes a DAX query. However, before we get into that, we first need to create an SSRS project in SQL Server Data Tools (SSDT) and then add a data source to our initial report. For this exercise, we’ll be embedding the data source (and subsequently the dataset) directly into the report, as opposed to creating either a shared data source or dataset, but feel free to take whichever approach you like.
If necessary create an SSRS project in SSDT and then create a report. To add an embedded data source to the report, right-click the Data Source<\/strong><\/code> node in the Report Data<\/strong><\/code> window and click Add Data Source<\/strong><\/code>. When the Data Source Properties<\/strong><\/code> dialog box appears, provide a name. (I used AWtabular<\/strong><\/code>.) Next, select Microsoft SQL Server Analysis Services<\/strong><\/code> from the Type<\/strong><\/code> drop-down list, and then provide a connection string that specifies the SSAS tabular instance and the name of the tabular database, as shown in Figure 1.<\/p>\n

\"1864-ssas1.png\"<\/p>\n

Figure 1: Connecting to an SSAS tabular data source<\/p>\n

In this case, I’m connecting to the ssas2012tab<\/strong><\/code> instance on my local system, with the initial catalog set to AdventureWorks Tabular Model SQL 2012<\/strong><\/code>. I’ve included the connection string here as well in case you want to copy it or have trouble reading it within the figure:<\/p>\n

Data Source=localhost\\\\ssas2012tab;Initial Catalog=\"AdventureWorks Tabular Model SQL 2012\"<\/code><\/p>\n

Instead of manually typing the connection string, you can let SSRS define it. Click the Edit<\/strong><\/code> button in the Data Sources Properties<\/strong><\/code> dialog box. This launches the Connection Properties<\/strong><\/code> dialog box, shown in Figure 2. Here you enter the instance name and select the target database. When you finish, click OK<\/strong><\/code>. SSRS will automatically generate the connection string and insert it into the Connection string<\/strong><\/code> text box in the Data Sources Properties<\/strong><\/code> dialog box.<\/p>\n

\"1864-ssas2.png\"<\/p>\n

Figure 2: Defining a connection string for an SSAS data source<\/p>\n

After we add our data source, we can create the dataset. In the Report Data<\/strong><\/code> window, right-click the Datasets<\/strong><\/code> node and then click Add<\/strong> Dataset<\/strong>. When the Dataset<\/strong> Properties<\/strong> dialog box appears, type in a name for the data source. (I used ProductInfo<\/strong>.) Next, select the option Use<\/strong> a<\/strong> dataset<\/strong> embedded<\/strong> in<\/strong> my<\/strong> report<\/strong>, and then select your data source from the Data<\/strong> source<\/strong> drop-down list, as shown in Figure 3.<\/p>\n

\"1864-ssas3.png\"<\/p>\n

Figure 3: Configuring a dataset based on SSAS tabular data<\/p>\n

The next step is to define your DAX query. The query goes into the Query<\/strong><\/code> text box; however, you cannot add the query directly to the text box. You must use the Query Designer tool. To access that tool, click the Query Designer<\/code> button to launch the Query Designer<\/code> window, shown in Figure 4.<\/p>\n

\"1864-ssas44.png\"<\/p>\n

Figure 4: Accessing Query Designer when defining a dataset<\/p>\n

By default, the Query Designer<\/code> window lets you create an MDX query that you can use to retrieve data from your data source. However, we’re not interested in MDX this time around, and we can’t use this window in its present state to create a DAX query. Instead, we must change to the mode used for the Data Mining Extensions (DMX) language, which lets you create and work with data mining models in SSAS. We won’t be writing DMX expressions, though, but we can use the DMX mode to write our DAX queries. So click the Command Type DMX<\/strong><\/code> button on the menu bar. When you do, you’ll receive the message shown in Figure 5.<\/p>\n

\"1864-ssas5.png\"<\/p>\n

Figure 5: Receiving a warning about switching from MDX to DMX<\/p>\n

You don’t need to worry about this message for what we’re doing here. Simply click Yes<\/strong><\/code> and you’ll be taken to DMX mode. Once in that mode, you’ll then have to switch to Design mode. To do so, click the Design Mode<\/strong><\/code> button on the menu bar. You can then add your DAX query in the bottom window, as shown in Figure 6.<\/p>\n

\"1864-ssas6.png\"<\/p>\n

Figure 6: Adding a DAX statement to Query Designer in DMX mode<\/p>\n

That’s all you need to do to add your DAX statement. It’s a bit of a convoluted process, but not too bad. Should you want to copy and paste the statement, I’ve included it here as well:<\/p>\n

evaluate\n(\n   summarize\n   (\n      'Internet Sales',\n      'Product'[Product Name],\n      'Product Subcategory'[Product Subcategory Name],\n      'Product Category'[Product Category Name],\n      'Date'[Calendar Year],\n      \"Total Sales Amount\", sum('Internet Sales'[Sales Amount])\n   )\n)<\/pre>\n
Essentially,  we’re retrieving sales information about the various products sold via the  Internet. For information about how the statement works, refer back to the  second article in this series, “Using  DAX to retrieve tabular data<\/a>,” which explains the various components.<\/p>\n
 Once you’ve  added your DAX query to the Query Designer window, click OK<\/strong><\/code> to return to the Dataset Properties<\/strong><\/code> dialog box. If there’s a problem with the query, you’ll receive an  error message, though it won’t provide many details. I find it easier to write  my queries in SSMS and then paste them into the other applications. However,  even SSMS has limited support for writing DAX queries. <\/p>\n
When you’re  returned to the Dataset Properties<\/strong><\/code> dialog box, you should find that all your settings on the Query<\/strong><\/code> page are now complete, as shown in Figure 7. Notice that the DAX  query is now inserted in the Query<\/strong><\/code> text  box.<\/p>\n
   \"1864-ssas7.png\"<\/p>\n
Figure 7: Setting up a dataset based  on an SSAS tabular data source<\/p>\n
At this point,  you can go to the Fields<\/strong><\/code> page  to verify and modify your field names, as shown in Figure 8. Each column  returned by your query should be listed on this page. In this case, I’ve  changed the outputted field names so they’re simpler to work with. For example, Product[Product Name]<\/strong><\/code> is now simply Product<\/strong><\/code>. The  outputted field names are the ones you’ll be working with when you create your  report.<\/p>\n
   \"1864-ssas8.png\"<\/p>\n
Figure 8: Modifying the field names  in your dataset<\/p>\n
That’s all we  need to do to set up our dataset to use DAX to retrieve tabular data. We can  now create our report and use the fields in our dataset to populate the various  components. However, describing how to create a report in SSRS is beyond the  scope of this article, although Figure 9 shows you the matrix I created in  Report Designer on my system. <\/p>\n
  \"1864-ssas9.png\"<\/p>\n
Figure 9: Defining a matrix in Report  Designer<<\/p>\n
Essentially, I  added a Matrix<\/strong><\/code> object by  dragging it from the Toolbox<\/strong><\/code> to  the design surface. I then added the fields, as shown in the figure. It’s all  fairly straightforward, except the <<Expr>><\/strong><\/code> placeholder. For this, I defined the following expression:<\/p>\n
   =FormatCurrency(Fields!Sales.Value, 2)<\/code><\/p>\n
All I’m doing  here is formatting the Sales<\/strong><\/code> field to  be displayed as currency, with two digits to the right of the decimal point.  Figure 10 shows what the report looks like when I view it in the previewer  available to Report Designer in SSDT. <\/p>\n
  \"1864-ssas10.png\"<\/p>\n
Figure 10: Previewing your report  from Report Designer<\/p>\n
The figure shows only part of the results returned by the report, but as you can see, I’ve  grouped the data first by category, then subcategory, and finally product. I’ve  then provided each year’s worth of sales for the individual products.<\/p>\n
Adding Parameters to DAX<\/h2>\n
Undoubtedly, the  report shown above is a very basic one. But it does demonstrate how to use DAX  to retrieve tabular data. However, there’s more you can do with DAX: add  parameters to your queries. <\/p>\n
In SSRS, it’s  not uncommon to include parameters when defining a dataset. Even a dataset  based on a DAX query might benefit from a parameter or two, depending on the  type of information being retrieved. For example, suppose in the report above  we want users to be able to view the amount of sales for each product within a  specific product category. In this case, we can add a parameter based on the Product Category Name<\/strong><\/code> column in the Product Category<\/strong><\/code> table. (In our dataset’s output fields, we’ve renamed this column Category<\/strong><\/code>, which you can see if you refer back to Figure 8.)<\/p>\n
To add the  parameter, we must first modify our DAX query, as shown in the following evaluate<\/strong><\/code> statement:<\/p>\n
evaluate\n(\n   summarize\n   (\n      calculatetable\n      (\n        'Internet Sales',\n        'Product Category'[Product Category Name] = @Category\n      ),\n      'Product'[Product Name],\n      'Product Subcategory'[Product Subcategory Name],\n      'Date'[Calendar Year],\n      \"Total Sales Amount\", sum('Internet Sales'[Sales Amount])\n   )\n)<\/pre>\n
Originally, the  first argument in our summarize<\/strong><\/code> function was the Internet Sales<\/strong><\/code> table. But we’ve now replaced the table name with a table  expression that consists of the calculatetable<\/strong><\/code> function and it’s two arguments. In DAX, the calculatetable<\/strong><\/code> function applies one or more filters against a specified table. In  this case, the function is applying one filter (the second argument) against  the Internet Sales<\/strong><\/code> table (the first argument). The filter specifies that, for each row  returned by the calculatetable<\/strong><\/code> function, the Product Category Name<\/strong><\/code> value must equal the value specified in the @Category<\/strong><\/code> parameter.<\/p>\n
We can give our  parameter any SSRS-supported name, as long as we precede it with the at (@<\/strong>) symbol in our DAX statement. The key is to use the same  capitalization we use here in any subsequent references within the Report Designer  interface. You’ll run into errors if your capitalization doesn’t match. In  other words, @Category<\/strong><\/code> is not equal  to @category<\/strong><\/code> when building  a report.<\/p>\n
One other change  this DAX statement makes is to remove the Product Category Name<\/strong><\/code> column from the results. Because we’re filtering by the category  name, we don’t need to include that name in the report data because all values  would be the same.<\/p>\n
To make these changes  to your DAX statement, launch the Query Designer<\/strong><\/code> window from the Dataset Properties<\/strong><\/code> dialog box associated with your dataset, and then update the  statement, as shown in Figure 11. But don’t click OK<\/strong><\/code> yet because we need to take another step.<\/p>\n
 \"1864-ssas11.png\"<\/p>\n
Figure 11: Adding a parameter to your  DAX query<\/p>\n
Once you’ve updated your query and added the @Category<\/strong><\/code> parameter, click the Query Parameters<\/strong><\/code>button in the menu bar to launch the Query Parameters<\/strong><\/code> dialog box. In  the first row of the grid, type the name of the parameter (without the @<\/strong> symbol) in the Parameter<\/strong><\/code> column, and type an initial value in the Value<\/strong><\/code> column, as shown in Figure 12. This value should be an actual value  within that column, or your initial report will show no results. For this  exercise, I’ve used Clothing<\/strong><\/code>.<\/p>\n
 \"1864-ssas12.png\"<\/p>\n
Figure 12: Adding a parameter to your  SSRS report<\/p>\n
Once you’ve  added your parameter and its value, click OK<\/strong> to close the Query Parameters<\/strong><\/code>dialog box, then click OK<\/strong> to close  the Query Designer<\/strong><\/code>window, and finally click OK<\/strong><\/code> to close the Dataset Properties<\/strong><\/code> dialog box.<\/p>\n
The next step is  to define a dataset that will supply a list of product categories to our  parameter when the report is rendered. Follow the steps described in the previous  section to create the dataset. However, this time around, we’ll name the dataset Categories<\/strong><\/code> and use the  following DAX query to retrieve our data:<\/p>\n
evaluate\n   values('Product  Category'[Product Category Name])\norder by 'Product Category'[Product Category Name]<\/pre>\n
Because we’re  retrieving our data from a single column, we can use the values<\/strong><\/code> function as our table expression in the evaluate<\/strong><\/code> function. The values<\/strong><\/code> function returns a one-column table that contains distinct values from the  specified column, in this case, Product Category Name<\/strong><\/code>. Our DAX statement  also includes an order by<\/strong><\/code> clause that sorts our results in ascending order. TheDataset Properties<\/strong><\/code> dialog box  for the new dataset should now look similar to the one shown in Figure 13.<\/p>\n
 \"1864-ssas13.png\"<\/p>\n
Figure 13: Setting up a dataset to  provide parameter values<\/p>\n
To finish  setting up your dataset, go to the Fields<\/strong><\/code> page  and change the field name to Category<\/strong><\/code>.  Then click OK<\/strong><\/code> .<\/p>\n
Once we’ve  created the dataset for our parameter, we must now associate that dataset with  the parameter. To do so, expand the Parameters<\/strong><\/code> node in the Report Data<\/strong><\/code> window. TheCategory<\/strong><\/code> parameter should be listed. Double-click the parameter to launch the Report Parameter Properties<\/strong><\/code> dialog box, as shown in Figure 14.<\/p>\n
 \"1864-ssas14.png\"<\/p>\n
Figure 14: Configuring your  parameter’s properties<\/p>\n
Go to the Available Values<\/strong><\/code> page and select  the option Get values from< a query<\/strong><\/code>. From theDataset<\/strong><\/code> drop-down list, select Categories<\/strong><\/code>;  from the Value field<\/strong><\/code> drop-down list, select Category<\/strong><\/code>; and  from the Label field<\/strong><\/code> drop-down list, select Category<\/strong><\/code>.  The Available Values<\/strong><\/code> page should now look like the one shown in Figure 15.<\/p>\n
   \"1864-ssas15.png\"<\/p>\n
Figure 15: Retrieving your parameter  values from the dataset<\/p>\n
If you like, you  can go to the Default Values<\/strong><\/code> page and change the default value. Figure 16 shows how it is  currently configured, with Clothing<\/strong><\/code> specified as the default value. However, you can change this if you want no  default value specified or want to retrieve values from a dataset. For now, the  current setting is fine, so click OK<\/strong><\/code> to close  the Report Parameters roperties<\/strong><\/code> dialog box.<\/p>\n
   \"1864-ssas16.png\"<\/p>\n
Figure 16: Confirming your  parameter’s default value<\/p>\n
Because we  removed the Category<\/strong><\/code> field from our  dataset, we need to modify the matrix on the design surface. Figure 17 shows  the modifications I made on my system. All I did was remove the Category<\/strong><\/code> column, which included the header and the column reference. (Refer  back to Figure 9 to see the original.)<\/p>\n
   \"1864-ssas17.png\"<\/p>\n
Figure 17: Defining a matrix in  Report Designer<\/p>\n
When we preview  our report now, we’ll be presented with the Category<\/strong><\/code> drop-down list, as shown in Figure 17. By default, Clothing<\/strong><\/code> is selected from the list, and all the information displayed in the  report is specific to that product category. As you can see in the figure, SSRS  displays only columns for which there is sales data. Because there were no  sales in the Clothing<\/strong><\/code> category for  2005 and 2006, those columns are not displayed.<\/p>\n
     \"1864-ssas18.png\"<\/p>\n
Figure 18: Viewing sales for the  Clothing product category<\/p>\n
The report also no  longer includes a Category<\/strong><\/code> column. Because Clothing<\/strong> is selected in  the Category<\/strong><\/code> drop-down list,  we know that all the sales data in this report applies to that product  category. If we were to select a different product category, the data would  change accordingly. For example, Figure 19 shows part of the results returned  when the Bikes<\/strong><\/code> category is  selected. In this case, sales occurred in all four years, so sales data is  available for all those years.<\/p>\n
  \"1864-ssas19.png\"<\/p>\n
Figure 19: Previewing your report  from Report Designer<\/p>\n
You can just as  easily view data for the other categories, assuming data exists. If it doesn’t,  no report will be displayed. For example, you’ll find in the Category<\/strong><\/code> drop-down list the option Components<\/strong><\/code>. If you select this category and then try to view a report, no data  will be returned. That’s because no Internet sales are associated with that  category. <\/p>\n
DAX and SSRS <\/h2>\n
As you can see,  it’s quite possible to use DAX within your SSRS reports to retrieve tabular  data. The process might seem a bit unusual, but once you know the trick, it’s easy  enough to achieve. Even adding parameters to your DAX queries is a fairly  straightforward process. But the method we used here for adding parameters  works only if you want to base your results on one value at a time. If, for  example, you want to modify the report above to be able to display data about  multiple product categories at the same time, you would have to take a  different approach to including a parameter, one a bit more complicated that  what we’ve done (and beyond the scope of this article). <\/p>\n
Microsoft has been  a bit slow in fully supporting DAX, despite the prominent role that DAX plays in  the company’s business intelligence (BI) offerings. Excel, SSRS, and SSMS are  all limited in their support for DAX when retrieving data from a SSAS tabular  instance. In fact, you can’t even use DAX in Report Builder. Given these  limitations, it might seem surprising that an SSAS tabular database can be  configured in DirectQuery mode, which supports only DAX queries. Still, you now  have some workarounds for working with DAX in a variety of environment,  including your SSRS reports.<\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
You can use DAX to create reports from a tabular database in SQL Server Reporting Services. To do so requires a few workarounds. Rob Sheldon describes a poorly-documented but important technique for Business Intelligence.…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[4242,4178,4168,4170,4379,4150,4151],"coauthors":[],"class_list":["post-1692","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-basics","tag-bi","tag-database","tag-database-administration","tag-reporting-services","tag-sql","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1692","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1692"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1692\/revisions"}],"predecessor-version":[{"id":92236,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1692\/revisions\/92236"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1692"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1692"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1692"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1692"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}