SQL Server Reporting Services 2008 R2 introduced many features in the area of graphical display of information. According to MSDN, there were three new features introduced in SQL Server 2008 R2—maps, sparklines and data bars and indicators. This final article of a three-part series on data visualization will explore the features of indicators as we build reports using Dynamics GP data.
Setting the Stage
Indicators are a way of providing immediate visual clues about the status of something. For example, while bar charts are a nice way to compare things to each other, sometimes you want to know the status of things right now so you can take immediate action. Are sales up or down right now? Is there an inventory shortage? By using indicators, you can provide those immediate clues to users that allow them to see the status and then look at the details.
According to Microsoft’s TechNet website, here are four benefits of indicators:
- Trends by using trending-up, flat (no change) or trending-down arrows
- State by using commonly recognized symbols, such as checkmarks and exclamation marks
- Conditions by using commonly recognized shapes, such as traffic lights and signs
- Ratings by using commonly recognized shapes and symbols that show progress, such as number of quadrants in a square and stars
In this example, let’s build a report that provides indicators based on how much of each item we are selling. The report is going to be grouped by customer class and item class.
First, once we launch Report Builder, we’ll create a data source.
Second, we create a dataset. Here is the query for the dataset.
Once the dataset is created, we will use the toolbar in Report Builder to insert a matrix.
The next step is to add a column where the indicator will be placed.
Click in the matrix and then highlight the Extended Price column. Right-click and choose “Insert Column,” then “Right.”
From the toolbar, click “Indicator.”
Click the new column in the Item Description row.
A window showing indicators appears. In our case, we’re selecting the five-star indicator in the Ratings group.
Once you have placed the indicator in the matrix, right-click on it and choose “Indicator Properties.”
Click on “Values and States.”
Enter the ranges that will determine which star will appear. You will see that there are various options for customizing the indicator on this window.
Enter the values and click “OK.”
Here’s how the report appears in Design mode.
Click the “Run” button to view the report.
Expand the report to view details by Item Description. Notice the varying amount of colors in the stars based on the sales value.
As you can see, the report indicates (Notice how I used the word?) the Cantata FaxPhone 9800 is doing well, whereas the Shoulder Rest – Deluxe White is not.
This article just scratches the surface of adding some visualizations using indicators to your SSRS reports. In just a few steps, you can add these visual elements to your reports. Feel free to experiment using Report Builder or Visual Studio.
For more information, please contact our Microsoft Dynamics GP Support Center.