Tag Archives: Reporting
Your Data, Your Brand: Creating Trust in Integrated Workflows and Reporting
Every company is a data company. Insights Everywhere explores the ways companies are evolving to include analytics in their products as a market differentiator and revenue driver.
The key to success for apps of all kinds is stickiness, or getting users to, well, use your app regularly. The stickiest business applications are those that can be seamlessly embedded into the toolkit of your target audience’s daily work environment. Analytic apps are applications that can be bidirectionally embedded, rebranded, and integrated into everyday workflows, connecting employees and customers with data and insights to help them make smarter decisions.
When you’re evaluating a tool to enrich your core product or service, certain features are de rigueur: security, governance, compliance, etc. However, beyond these nuts-and-bolts elements, you also need to consider features that impact how your brand presents itself to users.
The power of branding
Brand-related features may even be the most important criteria when choosing a platform like Sisense to enhance your analytic app. Making sure your analytic app matches your brand’s look and feel is vital to ensuring that the application enjoys the full trust of the user. This includes the app’s user interface, colors, and fonts, but more importantly, you need to drive home the idea that data is integral to your organization. Your data (and any analytics presented to internal or external users) is one of your most important assets, and how these insights are displayed impacts your integrity.
Making data-based decisions is becoming increasingly important to organizations of all kinds. Savvy companies are finding ways to infuse insights into their workers’ daily tasks, allowing them to seamlessly make decisions within a business application. Additionally, these same companies are integrating insights and analytics into their customer-facing products to increase stickiness and even drive new revenue.
Wherever you’re putting data and insights, it’s imperative that any analytics presented to decision-makers be displayed within your brand’s guidelines. In short, your data and analytics must look and feel like they are coming from you.
For this reason, the Sisense product team requires every component built with APIs to keep this integration of data and analytics into other applications in mind. These APIs empower developers to tap into any Sisense interface or functionality and enhance, rebrand, or integrate it into the brand’s own analytic apps and off-the-shelf business systems.
Custom analytics with the Linux Pivot API
The newly released Linux Pivot encompasses a new set of APIs, allowing brands to customize the look and feel of, transform the data in, or change the structure of pivot tables to fit their unique needs.
Developers are motivated to extend and enhance their software using the functionality that standard and supported APIs (like the JavaScript APIs) offered over the pivot table. Using these APIs, developers can ensure that their custom solutions are backward compatible with every version upgrade and count on the vendor to announce breaking changes well in advance for them to mitigate issues. Their software becomes a fully functioning extension of the Sisense Pivot Table, never a hack.
To jump-start the adoption of the new Pivot Table APIs and offer cloud-native customers a list of reusable extensions, Sisense partnered with Paldi Solutions. The joint project took only a few weeks and some well-planned lines of code. Paldi’s team posted several extensions in the Sisense Plugins forum, including adding visual indicators to cells, checkboxes as interactive filters, and presenting sparklines to accompany the data (plus several other features).
Ravid Paldi, CEO of Paldi Solutions, puts it this way: “Two especially interesting features are the new ‘transformPivot()’ function with a very convenient cell selector for cells you wish to manipulate, and the ability to inject React components as your cell’s content. Cool stuff!”
For example, many of our customers in the retail industry present images of their products that align with each product’s key performance indicators inside an analytic app. These are presented in pivot tables; each image is clickable and allows the marketing manager to easily jump into their e-commerce campaign management tool or the product page on their Content Management System.
Taking our example a few steps further, let’s assume that inside the pivot table you have the forecast sales for each product, along with the allocated marketing budget. Using Sisense’s AI capabilities such as Forecast and AI Trends, the marketing manager can adjust the campaign budget within the analytic app to achieve the greatest return. It’s a fully integrated workflow that makes the marketing manager’s job easier and more productive.
Empowering with plugins
These add-ons extending the power of pivot tables are only a few of the numerous solutions customers, partners, and Sisensers post to the Plugins forum. As an open platform, Sisense offers an abundance of APIs, both REST and JavaScript, to embed, rebrand, extend, and customize analytic apps.
Whatever your company’s core product or service is, infusing data and analytics throughout will make it stickier, deliver more value to internal and external users, and can even help drive revenue from your data. Using a platform like Sisense, with an emphasis on flexible, powerful APIs (versus building your analytics from scratch internally), can be a game-changer when it comes to simplifying your analytics deployment. Analytics are the future of every industry, so choose a partner that’s as committed to your success as you are.
Tomer Lapid is a Product Manager for Sisense analytics and reporting. He brings 20 years of experience in a variety of R&D, customer-facing, leadership, and product management roles that he combines into one superpower: problem-solving!
5 Creative Ways to Use Customer Insights Beyond Reporting
4) Improve PPC Campaigns
When done right, pay-per-click (PPC) advertising is a great way to increase brand awareness, drive site traffic, and generate conversions. When done wrong, however, you can run through a lot of your budget with very little to show for your efforts.
Customer insights are an invaluable piece of the PPC puzzle — with crystal clear metrics like click-through rate, cost-per-click, and cost-per-conversion being among the most important benchmarks of success.
If you notice that certain campaigns have high click-through rates with a high cost-per-click but very few conversions, that’s a good indicator that you’re not delivering what your users are expecting. So you should pay close attention to your most successful campaigns, content assets, webpages, landing pages, and emails to try to harvest and replicate that success. There’s no need to reinvent the wheel, and there’s no law that says you can’t repurpose good content — just don’t duplicate your previous work verbatim, as that can wreak serious SEO havoc.
You can also use customer insights to your advantage by conducting thorough keyword research. No doubt you can hazard a guess as to what your potential customers are searching for, but helpful PPC research tools like Moz and SEMrush can help you challenge those assumptions and build effective root and long-tail keyword lists that align with your users’ search queries. Additionally, you should review your most successful webpages and blogs and mine the target keywords on those pages to use in your PPC campaigns. If they’re working well in organic search, it stands to reason that you might also find success using them in your paid initiatives.
Lastly, and maybe the most effective use of customer insights on this entire list, you should set up remarketing campaigns to automate relevant ads wherever your users are online. Ever wonder how the internet seems to know you just a little too well? And have you ever found these constant reminders of your previous browsing history to be just enough to motivate you to revisit your online shopping cart and complete a purchase? Well, that’s because some savvy marketer out there has created remarketing campaigns to keep their brand front and center as you browse the internet.
Setting up remarketing requires a minor lift at the outset, but Google Ads, Facebook, and LinkedIn all do a tremendous job walking new PPC marketers through the process. Choose your ideal users and budget, place a tracking cookie or pixel on your preferred digital properties, and watch the leads come rolling in! Remember to check your progress at regular intervals and update your campaigns accordingly.
5) Retain and Evangelize Existing Customers
Using customer insights to create more satisfied and enthusiastic customers… what a concept! It sounds simple, but you’d be amazed at how many good marketers don’t realize how powerful this information can be in retaining and evangelizing their existing customers.
For example, if you notice a correlation between poor performance and negative reviews, that’s probably a pretty good sign that something’s got to change. Follow up with reviewers (negative and positive) to thank them for sharing, get more detailed information about their experience, and ask where and how you can improve. After you make the recommended adjustments, reach out again to get their thoughts. If they’re happy with your improvements, ask if they’d be willing to update their review or act as a brand advocate with new prospects. Customers love when companies are willing to go the extra mile to improve their experience, so be sure to take advantage wherever possible.
In many instances, even though a customer might be getting good use out of your products and services, they might not realize their full potential until you’re able to highlight additional use cases. Showcasing how and when to use your offerings to the fullest extent is an excellent way to retain customers who otherwise wouldn’t have known just how powerful and dynamic your products and services truly are. And what better way to do that than by leveraging existing customer feedback!
Whenever we have clients with great NPS scores or who are frequently singing our praises online or directly to our account management team, we reach out to schedule a success story or case study. By creating a simple questionnaire designed to elicit the best responses and conducting a brief 30-minute overview, we’re able to create great content that we then promote on our website, social media channels, automated email nurture programs — pretty much everywhere we can to drive home the value of our platform and the satisfaction of our customers. Best of all, our sales team then uses these success stories to prove our value to high-intent buyers, and they frequently make a big difference when it comes time to close the deal.
5 Creative Ways to Use Customer Insights Beyond Reporting
4) Improve PPC Campaigns
When done right, pay-per-click (PPC) advertising is a great way to increase brand awareness, drive site traffic, and generate conversions. When done wrong, however, you can run through a lot of your budget with very little to show for your efforts.
Customer insights are an invaluable piece of the PPC puzzle — with crystal clear metrics like click-through rate, cost-per-click, and cost-per-conversion being among the most important benchmarks of success.
If you notice that certain campaigns have high click-through rates with a high cost-per-click but very few conversions, that’s a good indicator that you’re not delivering what your users are expecting. So you should pay close attention to your most successful campaigns, content assets, webpages, landing pages, and emails to try to harvest and replicate that success. There’s no need to reinvent the wheel, and there’s no law that says you can’t repurpose good content — just don’t duplicate your previous work verbatim, as that can wreak serious SEO havoc.
You can also use customer insights to your advantage by conducting thorough keyword research. No doubt you can hazard a guess as to what your potential customers are searching for, but helpful PPC research tools like Moz and SEMrush can help you challenge those assumptions and build effective root and long-tail keyword lists that align with your users’ search queries. Additionally, you should review your most successful webpages and blogs and mine the target keywords on those pages to use in your PPC campaigns. If they’re working well in organic search, it stands to reason that you might also find success using them in your paid initiatives.
Lastly, and maybe the most effective use of customer insights on this entire list, you should set up remarketing campaigns to automate relevant ads wherever your users are online. Ever wonder how the internet seems to know you just a little too well? And have you ever found these constant reminders of your previous browsing history to be just enough to motivate you to revisit your online shopping cart and complete a purchase? Well, that’s because some savvy marketer out there has created remarketing campaigns to keep their brand front and center as you browse the internet.
Setting up remarketing requires a minor lift at the outset, but Google Ads, Facebook, and LinkedIn all do a tremendous job walking new PPC marketers through the process. Choose your ideal users and budget, place a tracking cookie or pixel on your preferred digital properties, and watch the leads come rolling in! Remember to check your progress at regular intervals and update your campaigns accordingly.
5) Retain and Evangelize Existing Customers
Using customer insights to create more satisfied and enthusiastic customers… what a concept! It sounds simple, but you’d be amazed at how many good marketers don’t realize how powerful this information can be in retaining and evangelizing their existing customers.
For example, if you notice a correlation between poor performance and negative reviews, that’s probably a pretty good sign that something’s got to change. Follow up with reviewers (negative and positive) to thank them for sharing, get more detailed information about their experience, and ask where and how you can improve. After you make the recommended adjustments, reach out again to get their thoughts. If they’re happy with your improvements, ask if they’d be willing to update their review or act as a brand advocate with new prospects. Customers love when companies are willing to go the extra mile to improve their experience, so be sure to take advantage wherever possible.
In many instances, even though a customer might be getting good use out of your products and services, they might not realize their full potential until you’re able to highlight additional use cases. Showcasing how and when to use your offerings to the fullest extent is an excellent way to retain customers who otherwise wouldn’t have known just how powerful and dynamic your products and services truly are. And what better way to do that than by leveraging existing customer feedback!
Whenever we have clients with great NPS scores or who are frequently singing our praises online or directly to our account management team, we reach out to schedule a success story or case study. By creating a simple questionnaire designed to elicit the best responses and conducting a brief 30-minute overview, we’re able to create great content that we then promote on our website, social media channels, automated email nurture programs — pretty much everywhere we can to drive home the value of our platform and the satisfaction of our customers. Best of all, our sales team then uses these success stories to prove our value to high-intent buyers, and they frequently make a big difference when it comes time to close the deal.
Reporting Services Basics: Adding Groups to Reports
The series so far:
- Reporting Services Basics: Overview and Installation
- Reporting Services Basics: Creating Your First Report
- Reporting Services Basics: Data Sources and Datasets
- Reporting Services Basics: Parameters
- Reporting Services Basics: Adding Groups to Reports
So far in this series, I’ve shown you how to create a basic report with parameters. The next necessary skill for report developers is to add grouping levels to the report. Managers often want to see subtotals, for example, at various levels, and adding groups is the way to do this. The report might be divided into categories and subcategories, maybe by locations and departments, or possibly by year and month. Whenever there is a hierarchical relationship in the data, it might make sense to add groups based on those relationships.
There are several ways to add groups, but I’m going to show you the ones that have worked the best for me.
The Groups section
When you look at a report in Visual Studio in design view, you will see a section under the report canvas with Row Groups and Column Groups as shown in Figure 1.
Figure 1: The groups section
If you don’t see the section, click on the report and then select Report View Grouping from the menu as shown in Figure 2 to make it visible.
Figure 2: Where to turn on the Grouping section
You may be wondering what the difference is between column and row groups, and they are quite different. Row groups are used to organize the report into horizontal sections in a typical report. Figure 3 shows one page of a report with row groups.
Figure 3: A report with row groups
Column groups are used in matrix reports, which might also be described as “pivot” reports. A matrix report displays data from a column across the top of the report as headings. You can also add row groups to matrix reports. For example, you might want to display several years across a report along with row groups for category and subcategory as shown in Figure 4.
Figure 4: A matrix report
The SSRS Toolbox contains Table and Matrix items. When you add either one to a report, you’ll notice that each is called a Tablix instead of the original name. It’s also possible to turn a Table into a Matrix by adding column groups or turn a Matrix into a Table by removing the column groups. In my experience, it is better to start with the one you need.
For years, I used the SSRS wizard to create matrix reports, but eventually I realized that simple matrix reports were not that difficult.
Creating a matrix report
In this example, I’ll show you how to create the report shown in Figure 4. If you need help with setting up an SSRS project or creating data sources or datasets, be sure to go back to the earlier articles in this series to learn more.
To get started, you’ll need an SSRS project with a shared data source pointing to the AdventureWorks2017 database. Create a new report by right-clicking Reports and selecting Add New Item…. In the Add New Item dialog, select Report. Name the report Sales by year and category and click Add. The dialog will look similar to Figure 5.
Figure 5: Creating a new report
Inside the Report Data window, create a Data Source that points to the project’s Shared Data Source. Create a Dataset that is embedded in the report named ProductSales with the query below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SELECT PC.Name AS Category, PS.Name AS SubCategory, P.Name AS ProductName, P.ProductID, YEAR(SOH.OrderDate) AS OrderYear, SUM(SOD.OrderQty) AS Quantity, SUM(SOD.LineTotal) AS SalesAmt FROM Production.Product AS P JOIN Sales.SalesOrderDetail AS SOD ON SOD.ProductID = P.ProductID JOIN Sales.SalesOrderHeader AS SOH ON SOH.SalesOrderID = SOD.SalesOrderID JOIN Production.ProductSubcategory AS PS ON PS.ProductSubcategoryID = P.ProductSubcategoryID JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PS.ProductCategoryID GROUP BY YEAR(SOH.OrderDate), PC.Name, PS.Name, P.Name, P.ProductID; |
After creating the dataset, the Report Data window should look like Figure 6.
Figure 6: The Report Data window
The next step is to drag a Matrix object to the report canvas from the Toolbox window. You can also right click the canvas and select Insert Matrix. The empty matrix object will look like Figure 7.
Figure 7: The empty Matrix
When creating a matrix report, the trick is figuring out what goes where. The Columns cell will contain the column you want displayed as headings across the report. The Data cell will contain the value you want to aggregate. The Rows cell will be the remaining columns. In this case, the report must display the years across the report and add up the sales amount.
Drag OrderYear to the Columns cell, SalesAmt to the Data cell, and Category to the Rows cell as shown in Figure 8.
Figure 8: Report after adding main items
Notice that you can see the grouping levels in the grouping section. When you run the report, it will look something like Figure 9.
Figure 9: The report so far
The report’s not pretty at this point, but it does display as expected with the years going across the top as headings. The original report also includes subcategories. Go back to design view and right click on the Category group in the Row Groups section. Click Add Group Child Group… as shown in Figure 10.
Figure 10: Adding a Child Group
This brings up the Tablix group dialog. Select SubCategory as shown in Figure 11.
Figure 11: The Tablix group dialog
After clicking OK, the report should now look like Figure 12.
Figure 12: After adding SubCategory
When previewing the report, the top of it should look like Figure 13.
Figure 13: The report
You can format your report or not as desired since this article is about grouping, but at this point, I have to add some formatting for my own sanity! In this case, I have bolded the top row and the first two columns. Everything should end up bolded except for the Data cell as shown in Figure 14.
Figure 14: Bold everything except for the total
I also formatted the SalesAmt in the Data cell to be Currency with a thousands separator and no decimal places and added a heading with the report name. The default margins are too wide for this report, so I also changed them in the Report Properties and made sure that everything in the report was pulled to the left. (To learn more about formatting, see the previous articles.)
After the formatting is complete, the report looks like Figure 15.
Figure 15: The formatted report
The next step is to add some totals to the report. It is so easy to do when working with matrix reports! Right-click the Data cell that contains the summed SalesAmt and select Add Total Row. Repeat for Add Total Column. Figure 16 shows you where to find these.
Figure 16: Adding totals
Adding these two totals will leave your report canvas looking like Figure 17.
Figure 17: After adding the row and column totals
You might just guess the next step: adding a grand total in the empty cell. Hover over the cell until the column list appears and click it as shown in Figure 18. Select SalesAmt which will automatically sum.
Figure 18: Adding SaleAmt to the empty cell for a grand total
I also had to format that last cell since it didn’t pick up the previous formatting. After running the report, the first page in Print Layout mode looks like Figure 19.
Figure 19: The matrix report with totals in Print Layout mode
Obviously, you can add colors, a footer, and more to make this report look nicer, but just getting the data displayed correctly was quite simple! There’s one more thing that might be useful here. If you are in Print Layout mode and scroll, you’ll see that the top headings do not carry over from page to page. (If you are seeing alternating blank pages, go back adjust the page margins and make sure that the report edge has been pulled to the left.) To get the headings to appear on the second and subsequent pages, go to the Tablix properties and change the RepeatColumnHeaders and RepeatRowHeaders to True as shown in Figure 20. Probably the easiest way to see the Tablix properties is by selecting it from the list in the Properties window.
(Note that this method to repeat headings doesn’t work on regular table reports, and I’ll explain how to do that later in the article.)
Figure 20: The properties to get the matrix report headings to repeat
Now that you have seen how to create a matrix report, it’s time to learn how to create a regular row-grouped report.
Creating a table report
You saw a glimpse of row groups in the last section, but this time you will learn even more about row groups in reports as you create the report shown in Figure 1. To get started, create a new report in the project with the name Product Sales. It will also point to the project’s shared data source. Create a dataset using the same query that was used in the matrix report. Add a Table control to the report canvas and fill in the Data row, also known as the detail group, as shown in Figure 21. The headings should fill in by themselves.
Figure 21: The table with the detail row fields
The report is grouped by Category Sub Category OrderYear, so OrderYear is the first “parent” above the detail. In this type of report, I think it is easier to start at the detail and build out, but you can also go the other way. To add the group, right click the Details group in the Row Groups section. Select Add Group Parent Group… as shown in Figure 22.
Figure 22: Adding a parent group
This will bring up the Tablix group dialog where you will select the group’s field. Select OrderYear since it is the direct parent of the detail row. Also check Add group footer. This is where the subtotals will go. The dialog should look like Figure 23.
Figure 23: The Tablix group dialog
After you click OK to add the group, you’ll see quite a few changes to the report canvas. The Order Year group has been added to the report and to the Row Groups section. Figure 24 shows the report canvas at this point.
Figure 24: The OrderYear group added to the report
The next parent level is SubCategory. This time, I’ll show you another way to add a group. Right-click on the OrderYear cell and select Add Group Parent Group… as shown in Figure 25.
Figure 25: Another way to add a parent group
Add SubCategory as the new group and be sure to select Add group footer. Repeat the process to add Category using either method. Make sure to add the new group as a parent to SubCategory. Once all the groups are added, the report canvas should look like Figure 26.
Figure 26: All the groups added to the report
There are a lot of empty cells, and you can add totals to some of them. To figure out which grouping level a particular cell belongs to just select it. You’ll see the grouping level light up in orange as shown in Figure 27.
Figure 27: The grouping level lights up in orange
To add subtotal for each level, fill in SalesAmt for each cell underneath the SalesAmt cell. You can also add subtotals for Quantity. They will automatically sum as shown in Figure 28.
Figure 28: Adding subtotals
You can also add a grand total to the report by right-clicking the bottommost SalesAmt cell and selecting Add Total as shown in Figure 29.
Figure 29: Adding a grand total
When adding a total in this way, it is automatically added to the next parent grouping level. In this case, Category is at the top, so the total is added to the report level. You can add a grand total for Quantity. In this case, just add Quantity to the cell. The report design should look like Figure 30.
Figure 30: Adding grand totals
Preview the report. The report should look something like Figure 31.
Figure 31: The unformatted report
There is quite a bit of formatting that should be done, but I’ll leave it up to you to decide what to do except to be sure to modify the report’s margins so that all the fields fit on one page. After formatting, my report looks like Figure 32.
Figure 32: Report with some formatting
If you take a close look, it’s hard to tell what levels the total amounts refer to. To fix that, follow these steps. In the cell under ProductName, add this expression to the Value property:
|
=“Total for “ & Fields!OrderYear.Value |
Holding down the Shift key, select the two cells in row 4 and columns 4 and 5. Right-click and select Merge Cells as shown in Figure 33.
Figure 33: Merging two cells
Add this expression to the new larger cell:
|
=“Total for “ & Fields!SubCategory.Value |
Remove the word Total from the cell to the left. Merge the cells in row 5 and columns 4 and 5. Add this formula:
|
=“Total for “ & Fields!Category.Value |
Finally, merge the cells in the bottom row and columns 4 and 5. The cell should just say Grand Total. Delete the original word Total from several cells. The report design should look like Figure 34.
Figure 34: The subtotals labeled
I would like to move some of the labels more to the left, but I found that the Merge Cells option was not always available. If it is for you, then modify accordingly.
The last thing to do to make this report more usable is to have the headings repeat on new pages. If you go into Print Layout mode and scroll to page 2, you will see that the top headings do not repeat. The RepeatRowHeaders property doesn’t seem to do anything on regular table reports. There is a way to do this, though.
On the grouping section at the bottom of the page, select Advanced Mode as shown in Figure 35.
Figure 35: Turning on Advanced Mode
Make sure this is checked which will add Static sections to the row and column groups as shown in Figure 36.
Figure 36: The Static groups
Working only on the row groups, select the first Static group and open the Properties window. Change the RepeatOnNewPage property to True as shown in Figure 37.
Figure 37: The RepeatOnNewPage property
Repeat the process for the next two Static groups. Now when you run the report and scroll to page 2, you’ll see the top headings repeated. Figure 39 shows you page 2 of my report.
Figure 39: The report with repeating row headings
Summary
This article introduced adding grouping by walking you through the creation of two simple reports. This article covered a lot of ground, but there is still so much to learn. In the next article, you’ll learn more about adding expressions to reports to add even more functionality.
What’s new for SQL Server 2019 Reporting Services RC1
We are pleased to announce the first release candidate of SQL Server 2019 Reporting Services is now available in the download center. While the team has previously released several updates for SQL Server customers via Power BI Report Server, this is the first feature update to the core SQL Server Reporting Services product for the 2019 release wave.
Download SQL Server 2019 Reporting Services
With this release, we’ve added new features to help customers meet their changing business needs around cloud-readiness and accessibility. These include –
- Support for hosting your database catalog with Azure SQL Managed Instance
- Support for Power BI datasets hosted in Power BI Premium
- Using Azure AD Application Proxy with Reporting Services
- A new version of Microsoft Report Builder updated for use with SSRS 2019
- AltText (alternative text) in support of WCAG and 508 compliance for your report elements
- PDF/UA support for PDF documents (coming soon)
- Rollup of several security updates and bug fixes previously released in Power BI Report Server
Azure SQL Managed Instance support
Managed instance is a new deployment option of Azure SQL Database, providing near 100% compatibility with the latest SQL Server release. Now, for the first time, you may use a Managed Instance to host your database catalog used for SQL Server Reporting Services that is hosted either in a VM or in your data center. Please note that support is limited to using database credentials for the connection to SQL MI.
Power BI Premium dataset support
In March, the Power BI team announced support for connectivity via the XMLA endpoint to Analysis Services models hosted in Premium workspaces. For the first time, customers can connect to those models using either Microsoft Report Builder or SQL Server Data Tools and publish those reports to SSRS 2019 using the SQL Server Analysis Services connectivity option. Users will need to use a stored Windows Username/Password to enable the scenario.
Microsoft Report Builder update
Later this week, we’ll have an update to the SQL Server 2016 Report Builder release. This release updates all existing installs of that product. It is fully compatible with the 2016, 2017, and 2019 versions of Reporting Services, along with all released and supported versions of Power BI Report Server. New features include the ability to connect to a Power BI Premium dataset and enter data directly in a report.
AltText (alternative text) support for report elements
Meeting customers accessibility needs is an important part of the software development process here at Microsoft. We’ve enhanced several accessibility items for report authors and viewers in this release, including official support for Alternative Text for report elements. Using tooltips, authors may specify text for each element on the report and have this properly identified by screen reader technology as such.
Beyond what we’ve called out here, some additional features will be available prior to our GA release, including PDF/UA support for PDF documents. We will announce those as soon as they are available.
As many of you know, we recently released paginated reports in Power BI, and are continuing to add more features there. We continue to see Reporting Services as a key part of the SQL Server value proposition, and much like the Analysis Services team has done in this 2019 release, we’ll look to bring new RDL capabilities introduced in the cloud to the on-premises product in future releases.
Thanks for reading, and don’t forget to leave us your feedback in the comments below.
Reporting Services Basics: Parameters
The series so far:
- Reporting Services Basics: Overview and Installation
- Reporting Services Basics: Creating Your First Report
- Reporting Services Basics: Data Sources and Datasets
- Reporting Services Basics: Parameters
Adding parameters is one of the essential skills to learn when you are new to SSRS. Parameters give end-users the ability to change the filter of the report on-the-fly. Imagine creating a separate report for every sales year or every department. Even worse, imagine creating a report for every combination of years and departments! With parameters, you can create one report that will work for any combination of these variables.
Starting with the Product List Report
To demonstrate parameters, this article will use the list of products found in the AdventureWorks database. To get started, create a new Report Server Project called Parameters with a shared data source. (See previous articles in this series if you need help understanding how to work with projects, data sources, or datasets.) Add a report named Product List with this embedded dataset:
|
SELECT P.ProductID, P.Name AS ProductName, P.Color, P.Size, P.ListPrice, PC.Name AS Category, PS.Name AS SubCategory FROM Production.Product AS P JOIN Production.ProductSubcategory AS PS ON PS.ProductSubcategoryID = P.ProductSubcategoryID JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PS.ProductCategoryID; |
Your Solution Explorer should look like Figure 1.
Figure 1: The Solution Explorer
Your Report Data window should look like Figure 2.
Figure 2: The Report Data window
Add a Table control to the report’s canvas and add these fields to the Data row.
- ProductID
- ProductName
- Color
- Size
- ListPrice
The table on the report will look like Figure 3.
Figure 3: The table
Preview the report to make sure it works at this point. It should look something like Figure 4.
Figure 4: The report in Preview mode
Spend some time formatting the report. This is not required for learning about parameters, but if you are like me, the unformatted report is quite annoying! Your report might look something like Figure 5 when you are done.
Figure 5: The formatted report
The wizard report from the first article had a header, but you didn’t learn how to add a header to a new report. To do so, make sure that the report is in Design view. Select the report canvas, which makes the Report menu show up in the menu bar. Select Report Add Page Header, as shown in Figure 6. Also, add a page footer.
Figure 6: Add a page header and footer
Move the table so that it is close to the header and the left of the page. Also, drag the top of the footer to the bottom of the table. Drag the right side of the canvas to the table. The canvas should look like Figure 7.
Figure 7: The canvas with header and footer
In the Report Data window, expand Built-in Fields. Drag the Report Name field into the page header, as shown in Figure 8.
Figure 8: Drag the Report Name
Expand the textbox width to the size of the canvas. Increase the font size to 22 and align the text in the centre. You can also drag Execution Time and Page Number to the footer. (Note that you may need to expand the are under the table where you can temporarily drag the first footer item before adding it to the footer.) The report canvas should now look like Figure 9.
Figure 9: Formatted header and footer
Adding Simple Parameters
SSRS will automatically add parameters to your report when you have variables in the WHERE clause of the query. In this case, you’ll add a parameter to filter on color. Double-click the dataset and change the query to this:
|
SELECT P.ProductID, P.Name AS ProductName, P.Color, P.Size, P.ListPrice, PC.Name AS Category, PS.Name AS SubCategory FROM Production.Product AS P JOIN Production.ProductSubcategory AS PS ON PS.ProductSubcategoryID = P.ProductSubcategoryID JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PS.ProductCategoryID WHERE P.Color = @color; |
Click OK, and you’ll see the new parameter @color listed in the Parameters folder and the Parameters section of the report canvas, as shown in Figure 10.
Figure 10: The @color parameter
Preview the report. This time, it will not run automatically. Fill in the color “blue” and click View Report. The report should look like Figure 11 with only blue items displayed.
Figure 11: Filtered by blue
Right now, you must type in a valid color to get the report to return products. If you type in “purple,” for example, no products will be returned.
There are quite a few properties you can set to control the behaviour of parameters. You might allow a blank value, for example, or supply a list of values from which to choose. To see the properties, double-click the parameter. The Report Parameter Properties dialogue looks like Figure 12.
Figure 12: The parameter properties
There are a few interesting items to note on the General page. For example, what is the difference between Name and Prompt? The Name refers to the actual name of the parameter that is used in the SQL query. The Prompt is what the user will see. So, you might want to change the “c” in color to uppercase make it more user-friendly.
You can change the Data Type if you must restrict the type of values entered, such as numbers or dates. In fact, if you switch to date, the report will display a date picker control for the parameter when you run the report. There are three other options (Allow blank value, Allow null value, and Allow multiple values) that will be covered later in the article. And, finally, there is the visibility property of the parameter. Here is what each option means:
Visible: The parameter is shown to the end-user who can change the value
Hidden: The end-user is not prompted for the parameter, but the value is typically passed in from a calling report
Internal: The end-user is not prompted and cannot change the value. This might be used for passing in the user id to the server.
There are three more pages to this dialogue. This article will cover Available Values and Default Values. Now, you’ll learn how to provide a list of values for the parameter.
Adding a Parameter List
It can be difficult for the user to remember the valid values for a report, so report developers often provide lists of values from which to choose. To provide a list of colors, follow these steps.
Switch to the parameter’s Available Values page. Currently, the option is set to None, as shown in Figure 13, which means that no list is provided.
Figure 13: The Available Values page
Switch to Specify values and click Add. You’ll now have a space to type in the first value, as shown in Figure 14.
Figure 14: Type in values
The Label is shown to the end-user, while the Value is passed to the SQL query. In this case, both values are the same. Enter the following list:
- Black
- Blue
- Grey
- Multi
- Red
- Silver
- Silver/Black
- White
- Yellow
The screen should look like Figure 15 when you are done. Click OK to save the list.
Figure 15: The parameter list
Now when you preview the report, you’ll see a list of colors from which to choose, as shown in Figure 16.
Figure 16: The parameter with the list
Try running the report multiple times, each time with a different color to verify that the report works as expected.
If you are familiar with this data, you’ll know that there are several products with no color (NULL). How can you allow a user to see those products? You’ll learn that next.
Searching for NULL values
You may recall that there is a parameter option to allow NULL (see Figure 12), but to use this option, you must make a change to your query that causes other complications later. It gets messy quickly, so I suggest substituting the NULLs instead.
To get started, add another item to the available values in the Color parameter, N/A for both Value and Label. Double-click the ProductList dataset and change the query to this:
|
SELECT P.ProductID, P.Name AS ProductName, P.Color, P.Size, P.ListPrice, PC.Name AS Category, PS.Name AS SubCategory FROM Production.Product AS P JOIN Production.ProductSubcategory AS PS ON PS.ProductSubcategoryID = P.ProductSubcategoryID JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PS.ProductCategoryID WHERE COALESCE(P.Color,‘N/A’) = @color; |
When you rerun the report and select N/A, you should see all the items with no color. So far, you are working with a static parameter list. Now, you’ll learn how to create a dynamic list. Note that using a function like COALESCE on a column in the WHERE clause can often cause performance issues.
Using a Query for a Parameter List
What happens if AdventureWorks gets in a new purple or orange product? You would have to manually add those colors to keep the parameter list up to date. Instead, you might want to maintain the list using a query. To do so, switch back to Design view add a new Dataset called Colors to the report with this query that causes the N/A row to show up first.
|
SELECT DISTINCT COALESCE(Color,‘N/A’) AS Color, CASE WHEN Color IS NULL THEN 0 ELSE 1 END AS SortOrder FROM Production.Product AS P ORDER BY SortOrder, Color; |
Figure 17 shows how the dataset should look.
Figure 17: The Colors dataset
Click OK to create the dataset. Now open the parameter properties again and switch to the Available Values page. Select Get values from a query. Under Dataset, select Colors. In both the Values and Labels field, select Color. The properties should look like Figure 18.
Figure 18: Using a query for the parameter list
Click OK to save the change and be sure to test the report. If you don’t mind modifying a row of the Product table, run this update statement in SSMS or Azure Data Studio.
|
UPDATE Production.Product SET Color = ‘Orange’ WHERE ProductID IN (802, 803); |
If you rerun the report, you should see Orange in the parameter list and the orange items in the results. Figure 19 shows the report.
Figure 19: The orange items
You might want to give the ability to select multiple items at once. The next section will show you how to do that.
Selecting Multiple Values
The person running your report might want to see more than one color at a time, maybe even all of them. There is a setting on the General page of the parameter properties (see Figure 12) that allows you to select more than one item. If you do this, however, your query will error, and the report will not run. Figure 20 shows the error message:
Figure 20: The error message after setting Allow multiple values.
The query sent to SQL Server uses = (equal to), but the variable holds a comma-delimited list of values. To fix this issue, you’ll need to modify the ProductList query to this:
|
SELECT P.ProductID, P.Name AS ProductName, P.Color, P.Size, P.ListPrice, PC.Name AS Category, PS.Name AS SubCategory FROM Production.Product AS P JOIN Production.ProductSubcategory AS PS ON PS.ProductSubcategoryID = P.ProductSubcategoryID JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PS.ProductCategoryID WHERE COALESCE(P.Color,‘N/A’) IN (@color); |
After running, the report should look something like Figure 21 when Multi and Orange are selected.
Figure 21: Selecting multiple values
So far, you have added just one parameter to the report. Many of the reports you run in the future will have more than that. Either the dataset will have multiple predicates in the WHERE clause, or you might also use parameters to control something about how the report looks. In the next section, you’ll learn how to control the value of a textbox in the report with a parameter.
Displaying Parameter Values on the Report
Parameters are the “workhorse” of SSRS. You can do so many things with them. In this section, you’ll learn that you can display the value or label of a parameter in a textbox.
Back in Design view, add a Textbox to the header section of the report. Drag it to the left of the page and expand the width. It should look like Figure 22.
Figure 22: The new Textbox
Right-click on the new Textbox and select Expression, as shown in Figure 23.
Figure 23: Select Expression
Just about any property in an SSRS report can be controlled with an Expression, i.e., a formula. You’ll learn much more about expressions throughout these articles, but this is a simple example to get you started. Once you select Expression in the menu, the Expression dialogue should pop up, as shown in Figure 24.
Figure 24: The Expression dialogue
Any expression begins with an equal to sign (=) similar to formulas in Excel. The Category area contains dozens of built-in fields and functions that you can use to build these expressions. Change the expression to
|
=“Colors chosen: “ + |
Then, make sure your cursor is to the right of the plus sign (+), click Parameters and double-click color in the Values window, as shown in Figure 25.
Figure 25: The expression
The final expression should be:
|
=“Colors chosen: “ + Parameters!color.Value(0) |
Click OK to save the expression. The Textbox will show <<Expr>> instead of a field name. Run the report but select only one color. The report should look like Figure 26 if you selected Orange. The value you chose is visible on the report.
Figure 26: The parameter value displayed
This works great, but it will only display one item. It’s possible that you are allowing multiple values to be selected. When you select more than one, only the first item is displayed. The reason is that the parameter values are held in an array of strings. Right now, the formula displays the first item in the array.
To display all the chosen values, go back to Design view and bring up the Expression dialogue again. Change the formula to this:
|
=“Colors chosen: “ + Join(Parameters!color.Value,“, “) |
Notice that the index of the array (0) was removed. The Join function builds a string from the array values. Now when you run the report, it will look something like Figure 27.
Figure 27: Displaying multiple values
In this example, the Values and Labels of the parameter are identical. In many cases, they are different. For example, the query might need an ID number while the person running the report might like to see the name. To get around this, change the formula so that the user-friendly label is used instead of the value:
|
=“Colors chosen: “ + Join(Parameters!color.Label,“, “) |
You can also use parameters to change the properties of objects on the report. You’ll learn how to do this next.
Using Parameters to Control Properties
Just about any property in SSRS can be controlled with an expression. If you take a look at the Text Box Properties dialogue of the Textbox you created in the last section, you’ll see the fx symbol next to two properties, as shown in Figure 28.
Figure 28: The fx symbol
Clicking this symbol anywhere you see it brings up the Expression box to control the associated property. Before adding in an expression, cancel out of this dialogue. Create a new parameter called Background by right-clicking the Parameters folder and selecting Add Parameter. Add these values/labels on the Available Values page.
- Yellow
- LightBlue
- Plum
Once all the parameter properties are in place, click OK. You’ll then see the second parameter in the list of parameters. Remember that this one isn’t connected to a dataset query; it will be used to control the background, or fill, color of the Textbox.
The next step is to connect the parameter to the Textbox. Right-click and select Text Box Properties. Select the Fill page. Click the fx symbol, as shown in Figure 29.
Figure 29: The Fill property
The initial expression is “No Color.” Replace it with this formula:
|
=Parameters!Background.Value |
The Expression dialogue should look like Figure 30.
Figure 30: The background expression
Click OK twice to save the property change. Now, try running the report. If you chose LightBlue, the report should look something like Figure 31.
Figure 31: The textbox with a blue background
This simple example demonstrated how powerful expressions and parameters are. Next, you’ll see how to set default values for parameters.
Using Default Parameters
In some cases, the user is likely to select a specific value for a parameter and only rarely change it. To learn how to set up a default value for the parameter, open the properties of the Background parameter. Select the Default Values page and Specify values, as shown in Figure 32.
Figure 32: The Default Values page
Click Add. Type in your favourite of the three colors, LightBlue, Plum, or Yellow. The property should look like Figure 33. Click OK to save the changes.
Figure 33: Filling in the default value
Now when you run the report, the background color will be automatically filled in. You can also get default values from a dataset. Open the Default Values properties of the color parameter. Select Get values from a query. Fill in the Colors Dataset and Color Value field. The properties should look like Figure 34.
Figure 34: The default values from a dataset
In this case, all colors will be selected when you run the report.
One of the most common requests is to make the selection of one parameter filter another one. You’ll learn how to do that next.
Creating Cascading Parameters
Each product belongs to a subcategory, and each subcategory belongs to a category. This is the type of hierarchical relationship that works well when parameters work together with what’s called Cascading Parameters. Figure 35 illustrates how this will work.
Figure 35: The product/category hierarchy
The first step is to modify the ProductList dataset query so that it prompts for the subcategory. Change the query to this:
|
SELECT P.ProductID, P.Name AS ProductName, P.Color, P.Size, P.ListPrice, PC.Name AS Category, PS.Name AS SubCategory FROM Production.Product AS P JOIN Production.ProductSubcategory AS PS ON PS.ProductSubcategoryID = P.ProductSubcategoryID JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PS.ProductCategoryID WHERE COALESCE(P.Color,‘N/A’) IN (@color) AND P.ProductSubcategoryID = @subcategory; |
To make sure that the query works, add the SubCategory and Category fields to the report. Preview the report. Enter subcategory 31. The report should look something like Figure 36.
Figure 36: Filtering with subcategory
At this point, the products are being filtered by subcategory, but the ID must be typed in. To get around this issue, add a new dataset called Subcategory with this query:
|
SELECT PS.ProductSubcategoryID, PS.Name AS SubcategoryName FROM Production.ProductSubcategory AS PS ORDER BY SubcategoryName; |
Connect the Subcategory dataset to the subcategory parameter. The Values field should be ProductSubcategoryID, and the Label field should be SubcategoryName. In this case, the query needs the ID while the user needs the name. The Available Values properties should look like Figure 37.
Figure 37: Connect the dataset to the parameter
If you review Figure 35, you’ll see that subcategories should be filtered by category. To do this, change the Subcategory dataset so that it’s filtered by category:
|
SELECT PS.ProductSubcategoryID, PS.Name AS SubcategoryName FROM Production.ProductSubcategory AS PS WHERE PS.ProductCategoryID = @category ORDER BY SubcategoryName; |
You now have four parameters in place. Notice that Category is listed before Subcategory in Figure 38. That’s because you must choose Category before Subcategory. In this parameter area, you can rearrange the parameters by dragging them around. The report will break if Category is not located before Subcategory!
Figure 38: The four parameters
The next logical step is to add a dataset for Category. Use this query:
|
SELECT PC.ProductCategoryID, PC.Name AS CategoryName FROM Production.ProductCategory AS PC ORDER BY CategoryName; |
Connect the dataset to the new Category parameter. You guessed it! The Value field is ProductCategoryID, and the Label field is CategoryName. The Available Values page should look like Figure 39.
Figure 39: The category Available Values
If you did everything correctly, the parameters should look like Figure 40 when you preview the report. Notice that the Subcategory parameter is greyed out until you select a Category.
Figure 40: The Subcategory parameter is not available
When you select a Category, the Subcategory parameter refreshes. For example, if you select Bikes, only the available bike subcategories show up. Figure 41 shows how this looks.
Figure 41: The filtered subcategory
Configuring Cascading Parameters is one of the most challenging things to understand in SSRS. Hopefully, I’ve shown that by breaking the steps down and completing them one at a time, it is doable.
Conclusion
Learning about parameters is critical for SSRS developers. In this article, you learned many ways in which parameters are used, including with a list of available values from a query, multiple selections, defaults, cascading, and more. In the next article, you’ll learn about more features that make SSRS reports dynamic.
Reporting Services Basics: Understanding Data Sources and Datasets
Data sources and datasets are critical components of an SSRS report, but many people new to SSRS struggle to understand them. In this article, Kathi Kellenberger shows you how to create these components and explains when they should be shared vs. embedded.
The series so far:
- Reporting Services Basics: Overview and Installation
- Reporting Services Basics: Creating Your First Report
- Reporting Services Basics: Data Sources and Datasets
If you’ve been following along with this series, you have learned about the architecture of Reporting Services (SSRS) and how to build a report using the wizard. Most of the time, the wizard is not that useful, although I must admit that I used the wizard for creating Matrix reports in SSRS for quite some time. In this article, you’ll begin to learn the basics of building a report from the ground up without the help of the wizard. There are two components, data sources and datasets, that you must understand to be able to build reports, so this article spends quite some time covering them.
Start with a Project
To get started, create a new Report Server Project in Visual Studio. In the previous article, you started with the Report Server Project Wizard. The Report Server Project will create the project shell without any objects. It will be up to you to add all the necessary components.
Figure 1 shows the New Project dialogue. Be sure to fill in a Name and click OK to create the project.
Figure 1: Create a new project
Once you create the project, you’ll see the Solution Explorer, shown in Figure 2, that is empty except for some folders.
Figure 2: The Solution Explorer
Shared Data Sources
A data source is like an address for the data. It contains the connection string. This might include the server name and stored credentials to get to a SQL Server database or be something as simple as the location of a text file. You can use multiple data sources in a report if the data will come from more than one place.
You can store data sources in two ways: sharing at the project level or embedding in the individual report. My advice is always to share the data sources, especially if you will have dozens or hundreds of reports pointing to the same database. By creating shared data sources, you or the database administrator (DBA) will have fewer of them to manage once the reports are published in the Web Portal.
- To get started, right-click the Shared Data Sources folder and select Add New Data Source, as shown in Figure 3.
Figure 3: Add New Data Source
- This brings up the Shared Data Source Properties dialogue. Fill in a Name for the data source. I suggest using the database name, but your team may have a specific naming convention to use.
- Choose the Type of database source. It’s SQL Server by default and what you’ll use in this example, but you might want to review the many sources possible.
- If you know your Connection String, you can fill it in, but it’s easier to click Edit to open the Connection Properties dialogue shown in Figure 4. You’ll need to fill in your Server name, Authentication method, and Connect to a database. (If you have problems connecting, review the Connecting to Your SQL Server Instance section in this article. If you are connecting to an instance in your network, check with your DBA for help.) Click OK to save the properties.
Figure 4: The Connection Properties
- Once you save the properties, the General page should similar to Figure 5. Click OK to save the changes.
Figure 5: The Shared Data Source Properties
- You’ll see the new data source in the Solution Explorer window shown in Figure 6.
Figure 6: The new data source
- At the time of this writing, there is a bug that prevents saving the Credentials settings when first creating the data source. Open the properties again by double-clicking the data source. (Note that if you right-click and choose Properties, you’ll be able to see only the file location.) Figure 7 shows the Credentials page and the correct setting. Fill in the credentials that you used in Step 5 if you did not use Windows authentication. After changing the setting, click OK.
Figure 7: The data source credentials
That’s all there is to create the data source. In step 3, I pointed out that there are many different types of data sources, and configuring each type is different. For now, stick with SQL Server to get started with SSRS. View this article for more information about each type of data source.
Now it’s time to discuss datasets.
Shared Datasets
A dataset is the query that runs when you view the report. The type of query will depend on the data source. For example, when working with SQL Server databases, the query will be written in T-SQL, or you also have the option of calling stored procedures. Even though there is a Shared Datasets folder, most of the time, the dataset should be embedded in the report and not shared. The reason for embedding datasets is that queries are not reused that often. There are exceptions, for example, parameter lists that are reused in many reports. If a dataset is shared, then it will be published when you deploy the project. You probably won’t want to clutter the Web Portal with every dataset for dozens or hundreds of reports.
The report must first exist before you can embed a dataset. Instead of showing you how to create a dataset, first learn how to create a new blank report.
New Reports
When creating a new blank report, you must take care not to kick off the Report Wizard. Follow these steps to create a new blank report.
- Right-click the Reports folder and select Add New Item…. Be sure NOT to select Add New Report, because that launches the Report Wizard. Figure 8 shows you the menu item.
Figure 8: Add new item, not add new report
- You’ll then see the Add New Item dialogue shown in Figure 9. Select Report and give the report a name. When working on reports for your company, be sure to provide the report with a meaningful name that will make sense to the people who run the report.
Figure 9: The Add New Item dialogue
- Click OK to create the report. You should now see it in the Solution Explorer, as shown in Figure 10.
Figure 10: View the new report
- The new report may be open in Design view. If not, double-click it to open it. The report canvas will look like Figure 11.
Figure11: The report in Design view
- Now you have a new report. On the left side of the screen, you should see the Report Data window. (If you don’t see it, make sure the report canvas is selected and type CTRL+ALT+D.) Figure 12 shows you the Report Data window. Everything in this window is specific to the report you are editing. You’ll learn about the different folders throughout these articles.
Figure 12: The Report Data window
- Right-click the Data Sources folder in the Report Data window and select Add Data Source. This data source will point to a shared reference, the data source you created earlier. Give it a Name and click Use shared data source reference. Select the AdventureWorks2017 data source from the list. (Note that you can give them the same name, but for illustration purposes, I’m naming them differently.) Figure 13 shows the properties. Click OK to create the data source.
Figure 13: The data source properties
- Once you create the data source, you’ll see it in the window. Notice that it has a little arrow on the icon that designates that it is pointing to a shared data source, as shown in Figure 14.
Figure 14: The new data source
You now have everything in place to create an embedded dataset.
Embedded Datasets
As mentioned earlier, it makes sense to embed most datasets in the reports instead of sharing them. Shared datasets end up published as reusable Report Parts for creating ad-hoc reports by advanced users. Most datasets are not needed for this and sharing them will clutter up the folder and make it more difficult for end users to find what they need. In the later article about parameters, you’ll learn to create shared datasets when it makes sense.
Make sure that your data source pointing to the shared data source is in place and follow these steps to create a dataset.
- Fill in a Name that describes your query. Select Use a dataset embedded in my report. A dropdown box for the data source will appear. Select the data source you just created. At this point, the dialogue box will look like Figure 15.
Figure 15: The Datasets Properties so far
- Make sure that the Query type is set to Text and paste in this query:
|
SELECT Prod.ProductID, Prod.Name AS ProductName, Prod.Color, Prod.StandardCost, Prod.ListPrice, Sub.Name AS SubCategory, Cat.Name AS Category FROM Production.Product AS Prod JOIN Production.ProductSubcategory AS Sub ON Prod.ProductSubcategoryID = Sub.ProductSubcategoryID JOIN Production.ProductCategory AS Cat ON Sub.ProductCategoryID = Cat.ProductCategoryID; |
- There is also a Query Designer you can use to build simple queries, or you can import a query from a text file. I recommend writing the query in SSMS or ADS and pasting it here. The Dataset Properties should look like Figure 16. Click OK to create the dataset.
Figure 16: Paste in the query
- Once you have saved the dataset, you’ll see it along with all the fields in the Report Data window. Click the arrow next to the dataset to expand the fields, as shown in Figure 17.
Figure 17: The new dataset
If you have made any syntax errors in the query – and it’s a good practice to make sure it runs first in SSMS – the fields will not show up. To troubleshoot, you can double-click to open the properties again and fix the query. You may need to click Refresh Fields to see the change.
Adding Fields to the Report
You’ll learn much more about creating reports throughout this series, but to make sure that what you’ve done so far works follow these steps to create a rudimentary report:
- Right-click on the report canvas and select Insert Table as shown in Figure 18. You can also drag objects from the Toolbox window to the canvas to accomplish the same thing.
Figure 18: Insert a table
- You’ll see a small grid with a Header and Detail row shown in Figure 19.
Figure 19: The new table
- There are three ways to add fields to the table. You can drag them over to the Detail row from the dataset in the Report Data window. You can hover over a cell and select from the little popup menu, as shown in Figure 20. You could also type the field with brackets in the detail row, but the header will not be automatically filled in for you. If you try this last method, note that the fields are case-sensitive.
Figure 20: How to add a field to the table
- Now that you have added a few fields, click Preview to view the report so far. My report looks like Figure 21.
Figure 21: The report so far
Of course, there is much more work to do on this report, but this shows you that your dataset is working.
Summary
In this article, you learned how to
- Create an SSRS project
- Add a shared data source
- Add a report without launching the wizard
- Point a report data source to a shared data source
- Embed a dataset in the report
- Add a table to the report
- Add some fields to the table
I suggest that you refer to this article until you become comfortable with data sources and datasets. The concepts are confusing for many students, so having a resource you can follow will make all the difference.
The next article will build on what you know about creating reports, and you’ll get to learn about some of the other components like headers and footers that make up a report.
Reporting Services Basics: Creating Your First Report
The series so far:
- Reporting Services Basics: Overview and Installation
- Reporting Services Basics: Creating Your First Report
In the previous article in the series, I explained a bit about the history and architecture of SSRS and helped you get a development environment set up. Now it’s time to start building your first report. The reports in this article are based on the AdventureWorks2017 database, which you should have restored if you followed along with the earlier article.
Solutions and Projects
For those of you familiar with working in Visual Studio, you will already understand the concept of solutions and projects. A solution contains one or more project, and the projects may be of all the same or different project types. For example, I was working on a business intelligence project a few years ago and had four different types of projects in my solution, one of which was an SSRS project. Figure 1 shows what my solution looked like.
Figure 1: My BI project organization
In theory, you could have one solution and one project for all the work you do for years developing reports, but that is not very practical. Typically, you might have a project for one topic area, project, or department. It’s up to you (or your manager) how you organize you work.
There are two ways to create an SSRS project, either manually or with a wizard. I will show you the wizard method in this article, which sets up the project as you create a report.
Using the Report Wizard
The report wizard is not the best way to create a report, but it is a nice way for beginners to SSRS to get started. Generally, the reports you create through the wizard won’t meet any but the most basic development requirements, but do not discount it entirely. It will allow you to get a report up and running with zero code and zero property setting, and the resulting report often makes a good starting point for more complicated reports. Once you’ve created a report with the Report Wizard, you are free to modify it further as you wish, just as for a report you create from scratch, manually.
Your very first report project, MyFirstProject, demonstrates how to create a report with grouping levels, including one of the dynamic features, collapsing and expanding sections. The result is not perfect, but it is a good start.
To create a report project with the wizard, follow these steps:
- Search for and launch Visual Studio (SSDT). If this is the first time you have run Visual Studio, you’ll be greeted by this screen. You will be prompted to sign in to the online developer services. For now, just click Not now, maybe later.
- You can select which color theme you would like to use. Select one and click Start Visual Studio.
- When Visual Studio opens, you’ll see a screen that looks like this:
- To create the project, select File New Project…
- Look for the Reporting Services templates in the Business Intelligence section and select Report Server Project Wizard. (You may have to look around; when I recently installed SSRS, Reporting Services didn’t end up in that section.)
- Fill in a name for the project and location. Notice that the Solution name will automatically be the same as the project name. You can override that if you wish.
- Click OK, which will launch the wizard. Click Next on the introduction page.
- On the Select the Data Source page, you will set up a new data source. You’ll learn more about data sources and datasets in the next article in this series. Click the Edit button.
- This brings up the Connection Properties window. Enter the SQL Server name, and, if local, you can type in localhost. If you have a named instance, you will need to use this syntax: [computername]\[instancename].
- Leave the Authentication setting at Windows Authentication.
- Click the dropdown to find the AdventureWorks2017 database.
- Click OK to save the connection properties. You’ll now see the Connection string in the dialog.
- Click Next to move to the Design the Query page. There is a Query Builder which you can use, or just write your query in SSMS to make sure it runs as expected. In this case, I’m providing a query for you. Paste this query into the Query string window:
|
SELECT Ter.[Group] AS WorldRegion, CountryRegionCode, Name as Territory, SalesOrderID, OrderDate, TotalDue FROM Sales.SalesOrderHeader AS SOD JOIN Sales.SalesTerritory AS Ter ON SOD.TerritoryID = Ter.TerritoryID; |
- Click Next to move to the next screen where you can select either a Tabular or Matrix report. A Matrix report is like a pivot table in Excel. For now, select Tabular.
- Click Next to move to the Design the Table screen. I’ll tell you what to do here, but when you are creating a real report, you will need to figure out how the grouping levels, if any, need to be arranged before you start working on the report. Select the fields and move them to the proper areas.
- Click Next to move to the Choose the Table Layout page. You have the choice of Stepped or Block. Click each to see how the report layout will look. For this example, choose Stepped and also select Include subtotals and Enable drilldown.
- Click Next to move to the Completing the Wizard page. Here you should add a Report name and click Finish to build the report.
Viewing the Wizard Report
Once the wizard creates the report, you’ll see the definition and the report listed in the Solution Explorer.
To view how the report looks when it’s run, click the Preview tab at the top.
When previewing, SSRS will combine the report definition with the actual data. The interesting thing about this report is that you selected Enable drilldown, so the sections are collapsed.
Click the first plus sign to expand the section. You’ll see that there is another section that can be expanded to see the details.
Cleaning up the Wizard Report
The wizard successfully created a report, but there are some things to clean up. For example, wouldn’t it be nice to format the currency fields? You might also want to add some color to the report.
Here is a list of items that must be cleaned up before the report is useful:
- Remove totals and subtotals from Sales Order ID fields
- Format date fields
- Format currency fields
- Adjust widths
Selecting the cell you wish to modify is one of the trickiest things about formatting reports. You can select the cell, the contents of the cell, or even a portion of contents of the cell. I’ve had good luck by clicking the edge of the cell when I want to select it for formatting. It might take some practice, but don’t give up!
You need to remove all the total and subtotals from the Sales Order ID column. In addition to the header, the only one you should keep is the one in the Detail row, which is at the bottom of the report. You may be able to see that the two to be removed are formulas which start with [SUM.
Select each cell and click Delete to remove the two formulas.
The next item in the list is to format the dates. All the dates in the AdventureWorks database are the old DateTime data type and has 12:00 am for each time. There is no reason to show these times. The easiest way to format is to right-click on the cell so that the context menu pops up. Select Text Box Properties…
The Text Box Properties dialog has quite a few options. You’ll learn more about them as you read through the series, but for now select Number Date.
You’ll see a list of many date formats. Select one of them that displays only the date and click OK.
It’s a good idea to preview the report as you go, so take a look now by clicking the Preview tab. You’ll need to also expand the plus signs to see the all the changes.
Back in design view, it’s now time to format the three currency cells. The only way to use the Text Box Property dialog is to format each of them separately. I’ll show you a trick that will save you some time, especially if you have several of them to format.
Right-click on one of the Total Due cells and select Text Box Properties… Navigate to Number Currency. If you just click OK at this point, it will format the cell to your local currency, but you might want to change the Decimal Places to 0 and select Use 1000 separator or make some other changes. You’ll see a sample of the format at the top.
Click OK to save the format. In order to save time, you will copy the format to the other currency cells. While the cell you formatted is selected, bring up the Properties window by clicking F4. Note that this is not the same as the Text Box Properties. The cell’s name is shown at the top of the window.
Scroll down until you find the Format property. Select the text and copy to the Clipboard.
Now select the other currency cells. You can select multiple cells by holding down the CTRL key. You can also select entire columns if that is helpful. In this case, select the Total Due column by clicking the column handle.
Open the Properties window again and paste the formula into the Format property. Since multiple cells are selected, the name is blank.
Preview the report to view the currency cell formatting.
Switch back to design view. The last thing on the list is to adjust widths. You will probably want to expand the Sales Order ID column. Select the column and then drag the right side of it over to expand the cell slightly.
Changing Fonts and Colors
It’s simple to change font properties and background colors, and SSRS gives you three ways to do it. You can use the Text Box Property dialog box, the Properties window, or menu items at above the design window.
Select the report name text box at the top of the report. Select a different font name and size.
Select the background icon, highlighted above, and choose a different color. Click OK. You can also format entire rows or columns by selecting a handle. Select the row handle for the header row and then format with Italics. Format any other items as desired and then preview the report.
Try formatting a single cell with the Text Box Properties dialog or the Properties window. You may find that the menu items are the easiest when it comes to formatting the fonts and colors.
Print Layout Mode
One of the most frustrating things about previewing the report is that you can’t really tell how the report will look when printed or exported to PDF. To solve this issue, you can switch to Print Layout mode while viewing the report in preview.
It’s critical that you view the report this way and even scroll to the next page in order to make sure that the report width fits properly on the page, and that it doesn’t leave strange blank pages between populated pages.
This report fits perfectly on the page, so to see the problem, switch back to design view. Grab the right edge of the report and pull it several inches to the right, leaving quite a bit of white space.
Go back into preview mode. Make sure that Print Layout is toggled off so that you can expand the DE and Germany sections. Switch to Print Layout and scroll a few pages. You’ll see that every other page is blank! This is due to width of the page even though the right side is not populated with any data.
Another thing that you may need to do from time to time is to change the print layout. If the report is fairly wide, you can change to landscape or even switch to a different size paper, such as legal, if your company uses that size. Switch back to design view and select Report Report Properties from the menu. Here you can switch the Orientation between Portrait and Landscape, change the paper size, and modify the report margins.
One more tool at your disposal is the ruler which you can turn on from Report View Ruler. Make sure that your report width plus the Left and Right margins do not exceed the width of your paper.
It may take some trial and error, but don’t stop working until you are sure that the report will print properly.
Launching the Wizard from the Solution Explorer
Now that you’ve created a report with the wizard, you should be able to see it in the Solution Explorer, typically on the right of the screen.
If you close the report definition, you can open it by double-clicking the name here. Another thing you might want to do is to create another report. If you would like to launch the wizard again, all you have to do is to right-click the Reports folder and select Add new report.
Instead, if you would like to create a report manually, choose Reports Add New Item Report. Creating a new report this way will be covered in the next article.
What if you close the project and want to open it again later? There are several ways to do that.
Open Visual Studio…
- Click the name of the project on the Start Page under Recent
- From the menu, select File Open and then navigate to the solution on your hard drive.
- From the menu, select File Recent Projects and Solutions and then select it from the list.
You could also navigate using the file explorer and double-click the sln file which will also launch Visual Studio, but this is more difficult than the other methods.
Be sure to save your solution once you are done working.
Conclusion
In this article, you created a report using the wizard, learned about formatting and how to make sure that your report fits properly on the page when printed. The Report Wizard is a nice way to get started, but you will probably realize that there are many limitations soon enough and not become dependent upon it.
In the next article, you’ll learn about data sources, datasets, and create a report from scratch without using the wizard.
Australia’s First Female-Founded Brewery, Two Birds Brewing, Finds Recipe for Success in Real-Time Reporting
Australia’s First Female-Founded Brewery, Two Birds Brewing, Finds Recipe for Success in Real-Time Reporting
Posted by Kendall Fisher, Executive Producer and Host of The NetSuite Podcast
In 2011, Two Birds Brewing became Australia’s first female-owned brewery. Founded by Jayne Lewis and Danielle Allen—best mates from college—the company grew from a single beer to a range of five distinct flavors brewed year-round. They even set up a brewery, which they’ve affectionately named “The Nest,” just outside of Melbourne in Spotswood.
On this episode of “The NetSuite Podcast,” we sat down with Allen (a.k.a. “The Other Bird”) to discuss the conception of Two Birds Brewing, the journey the birds have flown with the brewery over the last eight years and the business systems that allowed them to achieve their five-year goals in just three-and-a-half.
But it wasn’t always a slice of cake…Or a beer on a hot day, in this case.
Two Birds faced unprecedented growth after the launch. Allen and Lewis quickly discovered their outdated systems and spreadsheets weren’t going to keep up. Data is crucial to the company’s success, so they sought out to find a cloud-based business management solution to keep track of vital functionalities like inventory management, payroll and account purchasing.
Allen says she and Lewis ultimately chose NetSuite due to its true cloud foundation and real-time data. At the time, the co-founders were working in different states and needed a system that could allow them to do so.
“The reporting out of NetSuite is second to none,” Allen says. “The information is at our fingertips, and because it’s live, we have all the information right there before we go in to talk to a customer.”
To hear Two Birds’ full story, tune into this podcast episode on Apple Podcasts, Soundcloud and YouTube. As always, don’t forget to rate, review and subscribe!
by NetSuite filed under
The Benefits of AI for Asset Management: Enterprise Reporting
Asset Management Firms and the Benefits of AI: Enterprise Reporting
These days, data is an integral part of any successful business. This is especially true when it comes to asset management. Without access to the right data, an asset management firm will struggle to stay competitive. But in order to take full advantage of the data available to your firm, you need to have the appropriate technology in place. That’s where Artificial Intelligence (AI) comes in. With the help of AI, your firm can increase its return on investment, streamline various processes, and more. In this 6-part video series, we explore all of the different ways AI can make your firm more competitive than ever. While it’s not necessary to view the video series in order, we recommend taking a look at the rest of the series if you haven’t seen it yet:
In Part 6 of this video series, Enterprise Reporting, we’ll look at how and put data to work for your firm.
Get More Out of Your Reports
Taking full advantage of the data available to your firm hinges upon your ability to create meaningful, accurate reports. But in asset management, the sheer amount of data combined with the number and variety of lines of business makes this difficult. How can you collect all of this data and put it into a format that’s manageable and understandable?
The reality is that assembling all of this data into an accurate, useful summary can be a real challenge. But, having access to this kind of reporting is essential to your firm’s success. Without the right reports, you’ll be at a major disadvantage when it comes to making important decisions for your business.
One of the best things about a software solution like Dynamics 365 CRM is its ability to both summarize and drill down into various data sets. You can put together reports that sort data by fees, number of clients, market value, and other variables with relative ease. But what if you could go a step further? Imagine being able to ask questions of your data: questions such as, “Which of our funds are the most profitable?”
In fact, you can get these kinds of insights from your data using the AI features included with Dynamics 365 CRM. Thanks to Dynamics 365’s AI capabilities, you can quickly and easily obtain actionable information from your data. Plus, the AI features in Dynamics 365 CRM are available via mobile devices, making it easier than ever for employees on the road to get the information they need.
AI isn’t science fiction: it’s already integrated into Dynamics 365 CRM, and you can start taking advantage of it right away. Watch the video below to find out how Dynamics 365, machine learning, and AI can work together to improve your reporting.
ABOUT AKA ENTERPRISE SOLUTIONS
AKA specializes in making it easier to do business, simplifying processes and reducing risks. With agility, expertise, and original industry solutions, we embrace projects other technology firms avoid—regardless of their complexity. As a true strategic partner, we help organizations slay the dragons that are keeping them from innovating their way to greatness. Call us at 212-502-3900!