Teri Patsilaras

7月 312020
 

In this SAS demo, I demonstrate how to prompt for a date range in a SAS Visual Analytics report. We walk through how to configure prompts using four types of control objects:

Slider (range)

Slider (single value)

Drop-down list

Text input

Then we cover how to define parameters and configure filters for report objects using the date prompted boundaries. These techniques are demonstrated using SAS Viya 3.4 SAS Visual Analytics 8.4 but can be applied to earlier releases.

I've also written accompanying articles in a four-part series. Below the video see the links to the articles and their corresponding timestamps in the video.

01:33 – 1st Example: Slider (Range) - How to prompt for a date range in a SAS VA report – Example 1 Slider (Range)

03:01 – 2nd Example: Slider (Single Value) - How to prompt for a date range in a SAS VA report – Example 2 Slider (Single Value)

14:04 – 3rd Example: Drop-down list - How to prompt for a date range in a SAS VA report – Example 3 Drop-down list

19:33 – 4th Example: Text input - How to prompt for a date range in a SAS VA report – Example 4 Text input

Other References

How to prompt for a date range in a SAS Visual Analytics Report - Four Part Series was published on SAS Users.

4月 102020
 

First introduced in SAS Visual Analytics 8.3, common filters are filters that can be shared between objects in your reports.

Common filter benefits include:

  • Easy to assign the same filter conditions to other report objects.
  • When you edit a common filter, it is updated everywhere that the common filter is used.
  • A common filter is available for the entire report, across pages.

Common filter limitations include:

  • Objects must share the same data source as the common filter definition.
  • Common filters are not available across multiple reports.

While the benefits speak for themselves, common filters also expedite designing reports and exploring data by quickly reusing the same filter conditions with only a few mouse clicks.

Let’s look at some examples of using common filters. In the screenshot below, I defined a filter to return data for the last 30 days. I converted that filter to a common filter and now the Last 30 Days common filter can be applied to any object in this report that uses the same data source. Then I applied it to the bottom treemap object.



In my second example, I parameterized a text input control to capture a user-defined value. Then I defined a parameter-driven filter for the bar chart object. Next, I converted that filter to a common filter named Text Input Contains Filter and applied it to the list table object below.



In my third example, I parameterized two drop-down list controls to capture date boundaries. Then I defined a parameter-driven filter for the waterfall chart object. Next, I converted that filter to a common filter named FromToWeekFilter and applied it to the key value object.



Pro tip: Embrace meaningful filter names. Recall that these common filters are associated with the data source for which they are defined. Being able to quickly identify a common filter definition by its name will save you additional mouse clicks in the long run.

Example 1: Last 30 Days Filter

Let’s pick up where we need to define the filters. Therefore, both objects have been added to the report page with roles assigned.

Next, select the line chart object and open the Filters pane. Click + New filter then select Advanced filter.

Now we will use the built-in filter conditions offered in SAS Visual Analytics. First, select the date/datetime data item, Day. Second, scroll down in the available conditions until you see Last 30 days, then double click on the condition to add it to the expression editor. Third, give the filter condition a meaningful name and as a final step, be sure the number of returned observations is expected. Click OK.

Now we need to convert this filter to a common filter. With the line chart object still selected, on the Filters pane, use the Last 30 Day overflow menu and select Change to common filter.



You should now see the Last 30 Days listed as a Common Filter in the Data pane.

Lastly, we can apply this common filter to the treemap object. Select the treemap object, then open the Filters pane. Next click + New Filter and select the common filter Last 30 Days.



It’s good practice to title your objects to reflect any filters that may be defined for them. Especially if there are no prompts, i.e. control objects, driving the subset of data. This is so your report consumers quickly understand that they are only seeing, in this case, the last 30 days of data.

Example 2: Text Input Contains Filter

This next example will define a parameter-driven common filter. If you are not familiar with using parameters in SAS Visual Analytics, start with this article and refer to the additional materials at the end.

See the screenshot below for more information on the data item role assignments for each object. The most important role that will drive the parameter-driven common filter is the parameter. The parameter, TextInputParameter, is a character parameter assigned to the text input control object and it is the only role assignment. If there is nothing entered in this prompt, i.e. control object, then the filter will return all of the data. We will define a contains expression to only return data rows that contain the entered text.

With the bar chart object selected, open the Filters pane and click + New filter and select Advanced filter.



Now we need to build our parameter-driven filter expression. In this filter we will be checking if the Product Description data item Contains the entered text stored in the TextInputParameter. I wrapped each string expression in an UpCase function so that mixed case is ignored. I could have just as easily used the LowerCase function to get the same result.

And finally, remember to give your filter a meaningful name. The returned observations number is not reflective of an applied filter since I do not have any text entered in the control object. If you had text entered there, then your returned observation number may show a subset of matched rows.



Now we need to convert this object-level filter to a common filter. With the bar chart still the active object, open the Filters pane and use the Text Input Contains Filter overflow menu and select Change to common filter.

You should now see the Text Input Contains Filter in the Data pane.



To add this common filter to the lower list table object, select the list table object and open the Filters pane. Click on the + New filter and select the Text Input Contains Filter.



Now let’s test the filter. In the screenshot below, I’ve typed the word red. Recall that our filter expression is to return rows where Product Description contains the entered text. Notice that even though I do not have the data item Product Description assigned as a data role in the bar chart object, SAS Visual Analytics is still able to apply the filter appropriately. The list table object does have a role assigned for Product Description so that filter application is easier to identify.



I used the contains operator instead of the equals to return a partial match to the Product Description to help identify trends across multiple Products. In these next examples, I entered the text (F) and (M) to return the rows where the Product Description indicates gender-specific products.

Example 3: From – To Week Filter

This last example will also define a parameter-driven common filter. If you need a more step-by-step guide to similar examples, refer to this YouTube video tutorial:

See the screenshot below for more information on the data item role assignments for each object. This example differs from the last in that these control objects, the drop-down lists, use both Category and Parameter Role assignments. The Year-Week data item will provide the available values and the selections will be stored in the parameters FromWeekParameter and ToWeekParameter. I will then create a common filter for an inclusive between of the selected year week values to apply to both the key value object and the waterfall chart.



With the waterfall chart object selected, open the Filters pane and click + New filter and select Advanced filter.



Now we need to build our parameter-driven filter expression. In this filter, I will subset the Year-Week date values which are inclusively between the FromWeekParameter and ToWeekParameter boundaries. Follow these steps:

  1. Select the date/datetime data item, Year-Week.
  2. Scroll down in the available conditions till you see Year-Week BetweenInclusive(‘x’,’y’), then double click on the condition to add it to the expression editor.
  3. Drag the FromWeekParameter and ToWeekParameter from the parameter data items list to the expression.
  4. Give the filter condition a meaningful name. Click OK.



Test the filter by adjusting the values in the drop-down list controls.



Now we need to convert this object-level filter to a common filter. With the waterfall chart still the active object, open the Filters pane and use the FromToWeekFilter overflow menu and select Change to common filter.

You should now see the FromToWeekFilter in the Data pane.



To add this common filter to the key value object; select the key value object and open the Filters pane. Click on the + New filter and select the FromToWeekFilter.

Conclusion

Remember that once a common filter is defined, it can be used for any object in the report that uses the same data source. In these examples, I applied the common filter on the same report page but you can use a common filter on any page within the report. Recall that all of the common filters are listed and available when I click to apply a new filter to an object.

Hopefully these examples have shown you new ways to explore data faster!

Additional materials for using parameters in SAS Visual Analytics:

Using common filters in SAS Visual Analytics was published on SAS Users.

11月 152019
 

Designing interactive reports can be a fun and unique challenge. As user interface experience designers can attest, there are several aspects that go into developing a successful and effective self-service tool. Granted I’m not designing the actual software, but reports require a similar approach to be sure that visualizations are clear and that users can get to the answers they are looking for. Enter prompts.

Reports prompt users to better understand trends, how their data points compare to the whole, and to narrow the scope of data. Being able to pick the placement of these prompts quickly and easily will open the possibilities of your report layouts! I’m specifically speaking about Report and Page level prompts. Traditionally, these global prompt controls were only able to be placed at the top; see the yellow highlighted areas below.

Let’s take a look at an example report with the traditional Report and Page prompt layout. The Report prompts are extremely easy to pick out, since they sit above the pages, but the Page prompts can sometimes blend in with other prompts contained in the report body.

Introduced in the SAS Visual Analytics 8.4 release is the ability to control the layout position of these prompts. Using my example report, let’s change the placement of these prompts. In Edit mode, open the Options pane and use the top level drop-down to select the report name. This will activate the report level, and the report level Options will display. Next, under the Report Controls subgroup, move the placement radio button to the west cardinal point.

Depending on the type of control objects you are using in your report, you may not like this layout yet. For instance, you can see here that my date slider is taking up too much space.

When you activate the slide control, use the Options pane to alter the Slider Direction and Layout. You can even use the Style option to change the font size. You can see that after these modifications, the Report prompt space can be configured to your liking.

Next, let’s change the placement for the Page prompts, for demonstration purposes. From the Options pane, use the top drop-down to select the page name. This will activate the page level, and the page level Options will display. Next, under the Page Controls subgroup, move the placement radio button to the west cardinal position.

You can see that the direction of the button bar control was automatically changed to vertical. Now we can clearly see which prompts belong to the page level.

If I switch to view mode, and adjust the browser size, you can get a better feel for the Report and Page prompt layout changes.

But as with many things, just because you can, doesn’t mean you should. This is where the report designer’s creativity and style can really take flight. Here is the same report, but with my preferred styling.

Notice that I kept the Report prompts along the top but moved the Page prompts to the left of the report. I also added two containers and configured a gray border for each container to better separate the objects. This helps the user quickly see that the drop-down will filter the word cloud is only. I also used the yellow highlighting through styling and a display rule to emphasize the selected continent. The bar chart is fed from an aggregated data source which is why the report prompt is not filtering out the other continents.

Feel free to send me your latest report design ideas!

Additional material related to Report and Page prompts:

New control prompt placement option in SAS Visual Analytics was published on SAS Users.

7月 252018
 

In SAS Visual Analytics 8.3, a Data View is a reusable and shareable template for a data source. That means that the data view is tied to the data source, and not to the report. If you update a data view it will not automatically propagate those changes into a report.
 
So, what can a data view do for you? Plenty! Here are just a few of the settings and customizations that a data view can save for a data source: (taken from documentation here):

  • Data item settings such as names, formats, classifications, and aggregations
  • Data source filters
  • Hierarchies
  • Derived data items
  • Calculated items
  • Custom categories
  • Duplicate data items
  • Show / hide status for data items
  • Unique row identifier selection

Create a Data View

Now you must be wondering, how do you save all these wonderful customizations for your data source? Answer: by creating a Data View.
 
To get started, use the Data Source menu and select Save data view…. In this example, I created a hierarchy for the SASHELP CARS data set but as you can see from the list above you could have created many more calculations, custom categories, etc.
 
 

 
Then give the Data View a name. A few other things you may notice about this Save Data View dialogue are the options for: Default data view and Shared data view.
 
 

Default data view

A default data view is automatically applied whenever the data source is added to the report.
 
Each user can create their own data view of the source data and select their own default data view. This could lead to each user having a personalized default view. But, what if you want share your data views with others on your team? Or have everyone start with the same default view? That is when you need to first be an Application Administrator and second use the Shared data view option.

Shared data view

In order to be able to share a data view, you must be an Application Administrator. Then the option to share a data view will be available. Once a data view is shared for a data source, other users with access to that data source will be able to apply that data view.

Apply a Data View

Data views are templates of saved settings, hierarchies, custom categories, calculated data item, etc. which can be combined in an infinite amount of ways. Therefore, it follows that multiple data views can be applied to the same data source. In the example above, I created a new hierarchy for the SASHELP CARS data set. But I could also create a new data view which changes the aggregation of the MPG measures to reflect the average aggregation and not the default sum aggregation.

To apply a data view: open a new report, select your data source, then use the Data Source menu and select Data views…. You will see any individually created data views as well as any shared data views. Highlight the data view you wish to apply, then select Apply. Repeat for all of the data views you wish to apply.

If any data items are duplicated with the addition of data views then, as shown below, those data items are given a (n) after their names.

Administrator-controlled Default Data View

We've learned what Data Views are and that we can share them. How can we ensure that all the users who select a data source get the same starting point with a particular data view? To set this up, you must be an Application Administrator and the Data View must be Shared.
 
Once these two criteria are met, you can navigate to the report's overflow menu and select Edit administration settings. Then select the data source and which data view to apply as the default for all users.


 
Caution: If the user has already selected a personal default data view, then the personal default data view overrides the administrator-set default data view. Remember that an individual user can apply a personal or another shared data view and override the default data view.

Conclusion

Data Views are just one of the exciting new features in SAS Visual Analytics 8.3. A few key points to remember:

  • Data Views are tied to a data source, not a report. If a data view is edited, those edits do not propagate to the reports that applied that Data View.
  • A data source can have multiple Data Views applied.
  • Only an Application Administrator can share a data view with other users as well as define a default data view for a data source for all users. Any personal defined default data views override the administrator-set default data view.
  • Data Views are a template of data settings and edits – not a fully robust semantic layer where updates are pushed to all instances of usage. While Data Views can be used to assist in defining commonly used calculations and custom categories, remember that each user can still create their own data views and thus override the administrator-set default.

Using Data Views in SAS Visual Analytics was published on SAS Users.

6月 012018
 

You will not find an object in SAS Visual Analytics named Dynamic Text. Instead, you will find a Text object that allows you to insert dynamically driven data items. By using the Text object’s dynamic capabilities you can build custom report titles, object titles, emphasize measures and even supply the last modified time of the data source in your SAS Visual Analytics Report. In this post, I will outline the ways how you can leverage the Text object’s dynamic capabilities.

In this example report below, I have used a red font color to indicate the dynamically driven text.
Dynamic Text in a SAS Visual Analytics Report

Let’s take a look the available dynamic roles in the Text object. You can see from the Objects pane that the Text object is grouped under Other.

From the Data pane we have the ability to add both Measure and Parameter data items. From the interactive editor of the Text object shown below, we also have the ability to insert the Table Modified Time and Interactive Filters.

The following sections will demonstrate how to configure each of these dynamically driven elements of the Text object.

Interactive Filters

The out of the box display for Interactive Filters includes the selected values for control objects added to either the Report or Page Prompt areas.

To edit, be sure you are in Edit mode of Explore and Visualize. Click on the Text object to make it the active window and double click inside, then the interactive editor will open. Next, click on the Interactive Filters button. Use your cursor to position where you would like to add static text. In this case, I added the qualifier Default filter information:.

Multiple control object values are separated by a comma and also accommodates multi-value control objects.

Parameters

While the Interactive Filter functionality is extremely useful, you may want to use prompt values more granularly to create custom report titles or even object titles. To do this, you must first create a parameter to hold the value selected in the control object, then use that parameter in the Text object.

In my example report, I have two prompts and two custom object titles leveraging parameters. Let’s look at each one individually.

First is the Report Prompt, which prompts for year.

1.     Create your prompt by using the Control object of your choice and assigning the desired data role.
2.     Create a parameter that corresponds to the data type and assign it to the Control object’s Parameter Role.
3.     For the Text object, assign the same parameter to the Text object’s Parameter Role.
4.     Double click on the Text object, use your cursor to add static text as you like.

The steps are similar for the Page Prompt, which prompts for region.

1.     Create your prompt by using the Control object of your choice and assigning the desired data role.
2.     Create a parameter that corresponds to the data type and assign it to the Control object’s Parameter Role.
3.     For the Text object, assign the same parameter to the Text object’s Parameter Role.
4.     Double click on the Text object, use your cursor to add static text as you like.

Even though I demonstrate how to do this for both Report and Page Prompts, this same technique can be used for report canvas prompts. You just have to be sure you store the selected value(s) in a parameter that you can then use in the Text object’s Parameter Role.

Measures

Very much the same way the Text object’s Roles are used to assign the Parameter values, we can do the same thing with a measure. This measure will be affected by any Report or Page Prompts automatically, but if you want to use a report canvas prompt you will need to create the Actions to the Text object appropriately.

Here you can see we are using the measure TotalExpense which is an aggregated measure of Expenses. Like in the previous examples, be sure to assign the measure to the Text object then double click to open the editor and use your cursor to add the static text.

The only applied filters for this aggregated measure are the selected year and region, therefore this Sum _ByGroup_ will return the Total Expenses for that Year and Region.

Table Modified Time

The last capability of dynamic text available in the Text object is the Table Modified Time.

The out of the box display uses the fully qualified datetime stamp and cannot be altered to a different format. To edit, double click inside the Text object and the editor will open. Then click on the Table Modified Time button. Next, use your cursor to position where you would like to add static text. In this case, I added the qualifier Data last updated:.

Conclusion

There are two main takeaways from this blog post. First is that you can easily build dynamic customizable titles, emphasize measures or parameter values.

Second, look to use the Text object for your dynamic text needs.

Here is a quick mapping as a review of what was detailed in the steps above.

 

Using Dynamic Text in a SAS Visual Analytics Report was published on SAS Users.

3月 132018
 

SAS Visual Analytics 8.2 introduces the Hidden Data Role. This role can accept one or more category or date data items which will be included in the query results but will not be displayed with the object. You can use this Hidden Data Role in:

  • Mapping Data Sources.
  • Color-Mapped Display Rules.
  • External Links.

Note that this Hidden Data Role is not available for all Objects and cannot be used as both a Hidden Data Role and Data tip value, it can only be assigned to one role.

In this example, we will look at how to use the Hidden Data Role for an External Link.

Here are a few applications of this example:

  • You want to show an index of available assets, and you have a URL to point directly to that asset.
  • Your company sells products, you want to show a table summary of product profit but have a URL that points to each Product’s development page.
  • As the travel department, you want to see individual travel reports rolled up to owner, but have a URL that can link out to each individual report.

The applications are endless when applied to our customer needs.

In my blog example, I have NFL data for Super Bowl wins. I have attached two columns of URLs for demonstration purposes:

  • One URL is for each Super Bowl event, so I have 52 URLs, one for each row of data.
  • The second URL is for each winning team. There have been 20 unique Super Bowl winning teams, so I have 20 unique URLs.

Hidden Data Role in SAS Visual Analytics

In previous versions of SAS Visual Analytics, if you wanted to link out to one of these URLs, you would have to include it in the visualization like in the List Table shown above. But now, using SAS Visual Analytics 8.2, you can assign a column containing these URLs to the Hidden Data Role and it will be available as an External URL.

Here is our target report. We want to be able to link to the Winning Team’s website.

In Visual Analytics 8.2, for the List Table, assign the Winning Team URL column to the Hidden Data Role.

Then, for the List Table, create a new URL Link Action. Give the Action a name and leave the URL section blank. This is because my data column contains a fully qualified URL. If you were linking to a destination and only needed to append a name value pair, then you could put in the partial URL and pass the parameter value, but that’s a different example.

That is using the column which has 20 URLs that matches the winning team in the Hidden Data Role. Now, what if we use the column that has the 52 URLs that link out to the individual Super Bowl events?

That’s right, the cardinality of the Hidden Data Role item does impact the object. Even though the Hidden data item is not visible on the Object, remember it is included in the results query; and therefore, the cardinality of the Hidden data item impacts the aggregation of the data.

Notice that some objects will just present an information warning that a duplicate classification of the data has caused a conflict.

In conclusion, the Hidden Data Role is an exciting addition to the SAS Visual Analytics 8.2 release. I know you'll enjoy and benefit from it.

The power behind a Hidden Data Role in SAS Visual Analytics was published on SAS Users.

12月 222017
 

Another report requirement came my way and I wanted to share how to use our Visual Analytics’ out-of-the-box relative period calculations to solve it.

Essentially, we had a customer who wanted to see a metric for every month, the previous month’s value next to it, and lastly the difference between the two.

Relative Period Report in SAS Visual Analytics

To do this in SAS Visual Analytics, which is available in versions 7.3 and above, use the relative periodic operators. I am going to use the Mega_Corp data which has a date data item called Date by Month using the format: MMMYYYY. SAS Visual Analytics supports relative period calculations for month, quarter and year.
The first two columns, circled in red, are straight from the data. The metric we are interested in for this report is Profit.

Next, we will create the last column, Profit (Difference from Previous Period), which is an aggregated measure that uses the periodic operators.

From the Data pane, select the metric used in the list table, Profit. Then right-click on Profit and navigate the menus: Create / Difference from Previous Period / Using: Date by Month.

A new aggregated measure will be created for you:

If you right-click on the aggregated measure and select Edit Aggregated Measure…, you will see this relative period calculation, where it is taking the current period (notice the 0) minus the value for the previous period (notice the -1).

Okay – that’s it. This out-of-the-box relative period calculation is ready to be added to the list table. Notice the other Period Operators available in the list. These support SAS Visual Analytics’ additional out-of-the-box aggregated measure calculations such as the Difference between Parallel Periods, Year to Date cumulative calculations, etc.

Now we have to create the final column to meet our report requirement: the Previous Period column.

To do this we are going to leverage the out-of-the-box functionality of the relative period calculation. Since this aggregated measure calculates the previous period for the subtraction – let’s use this to our advantage.

Duplicate the out-of-the-box relative period calculation by right-clicking on Profit (Difference from Previous Period) and select Duplicate Data Item.

Then right-click on the new data item, and select Edit Aggregated Measure….

Now delete everything highlighted in yellow below, remember to also delete the minus sign. And give the data item a new name. Click OK. This will create an aggregated measure that will calculate the previous period.

The final result should look like this from either the Visual tab or Text tab:

Now we have all the columns to meet our report requirement:

Now that I’ve piqued your interest, I’m sure you are wondering if you could use this technique to create aggregated data items to represent the Period -1, -2, -3 offset? YES! This is absolutely possible.
Also, I went ahead and plotted the Difference from Previous Period on a line chart. This is an extremely useful visualization to gage if the variance between periods is acceptable. You can easily assign display rules to this visualization to flag any periods that may need further investigation.

Relative Period Report in SAS Visual Analytics was published on SAS Users.

11月 142017
 

SAS Visual Analytics 7.4 has added the support for date parameters. Recall from my first post,  Using parameters in SAS Visual Analytics, a parameter is a variable whose value can be changed at any time by the report viewer and referenced by other report objects. These objects can be a calculated item, aggregated measure, filter, rank or display rule. And remember, every time the parameter is changed, the corresponding objects are updated to reflect that change.

Here is my updated table that lists the supported control objects and parameter types for SAS Visual Analytics 7.4. The type of parameter is required to match the type of data that is assigned to the control.
Notice that SAS Visual Analytics 7.4 has also introduced the support for multiple value selection control objects. I’ll address these in another blog.

Using Date Parameters in your SAS Visual Analytics Reports

Let’s look at an example of a SAS Visual Analytics Report using date parameters. In this fictitious report, we have been given the requirements that the user wants to pick two independent date periods for comparison. This is not the same requirement as filtering the report between a start and end date. This report requirement is such that a report user can pick two independent months in the source data to be able to analyze the change in Expense magnitude for different aggregation levels, such as Region, Product Line and Product.

In this example, we will compare two different Month,Year periods. This could easily be two different Quarter,Year or Week,Year periods; depending on the report requirements, these same steps can be applied.

In this high level breakdown, you can see in red I will create two date parameters from data driven drop-down lists. From these parameter values, I will create two calculated data items, shown in purple, and one aggregated measure that will be used in three different report objects, shown in green.

Here are the steps:

1.     Create the date parameters.

2.     Add the control objects to the report and assign roles.

3.     Create the dependent data items, i.e. the calculated data items and aggregated measure.

4.     Add the remaining report objects to the canvas and assign roles.

Step 1: Create the date parameters

First we will need to create the date parameters that will hold the values made by the report viewers. From the Data Pane, use the drop-down menu and select New Parameter….

Then create your first parameter as shown below. Give it a name.

Next, select minimum and maximum values allowed for this parameter. I used the min and max available in my data source, but you could select a more narrow range if you wanted to restrict the users to only have access to portions of the data, just so long as the values are in your data source since, in this example, we will use the data source to populate the available values in the drop-down list.

Then select a current value, this will serve as the default value that will populate when a user first opens the report.

Finally, select the format in which you want your data item to be formatted. I selected the same format as my underlying data item I will be using to populate the drop-down list.

Notice how your new parameters will now be available from your Data Pane.

Step 2: Add the control objects to the report and assign roles

Next, drag and drop the drop-down list control objects onto the report canvas. In this example, we are not using the Report or Section Prompt areas since I do not want to filter the objects in the report or section automatically. Instead, I am using these prompt values and storing them in a parameter. I will then use those values to create new calculated data items and an aggregated measure.

Once your control objects are in the report canvas, then use the Roles Pane to assign the data items to the roles. As you can see from the screenshot, we are using the Date by Month data item to seed the values of the drop-down list by assigning it to the Category role, this data item is in our data source.

Then we are going to assign our newly created parameters, Period1Parameter and Period2Parameter to the Parameter role. This will allow us to use the value selected in our calculations.

Step 3: Create the dependent data items, i.e. the calculated data items and aggregated measure

Now we are free to use our parameters as we like. In this example, I am prompting the report viewer for two values: Period 1 and Period 2 which are the two periods the user would like compared in this report. So, we will need to create two calculated data times from a single column in our source data. Since we want to display these as columns next to each other in a crosstab object and use them for an aggregated measure, this technique can be used.

Calculated Data Item: Period 1 Expenses

From the Data Pane, use the drop-down menu and select New Calculated Item…. Then use the editor to create this expression: If the Date by Month for this data row equals the parameter value selected for Period 1, then return the Expenses; else return 0.

Calculated Data Item: Period 2 Expenses

Repeat this using the Period2Parameter in the expression.

Aggregated Measure: Period Difference

Next, we want to calculate the difference between the two user selected Period Expenses. To do this, we will need to create an aggregated measure which will evaluate based on the report object’s role assignments. In other words, it will be calculated “on-the-fly” based on the visualization.

Similar to the calculated data items, use the Data Pane and from the drop-down menu select New Aggregated Measure…. Use the editor to create this expression. Notice that we are using our newly created calculated data items we defined using the parameter values. This expression does not use the parameter value directly, but indirectly through the calculated data item.

Step 4: Add the remaining report objects to the canvas and assign roles

No that we have:

  • our Control Objects to capture the user input.,
  • the Parameters to store the values.,
  • and the Calculated Data Items and Aggregated Measure created…

we can add our report objects to the canvas and assign our roles.

You can see I used all three new measures in the crosstab object. I used the aggregated measure in the bar chart and treemap but notice the different aggregation levels. There is even a hierarchy assigned to the treemap category role. This Period Difference aggregated measure calculation is done dynamically and will evaluate for each visualization with its unique role assignments, even while navigating up and down the hierarchy.

Here are some additional screenshots of different period selections.

In this first screenshot you can see the parallel period comparison between December 2010 and 2011.

In these next two screenshots, we are looking at the Thanksgiving Black Friday month of November. We are comparing the two years 2010 and 2011 again. Here we see that the Board Product from the Game Product Line is bright blue indicating an increase in magnitude of Expenses in the most recent period, Nov2011.

By double clicking on Board in the treemap, we are taken to the next level of the hierarchy, Product Description, where we see a the largest magnitude of Expenses is coming from Backgammon and Bob Board Games.

In these final two screenshots we are comparing consecutive periods, November 2011 with December 2011. We can see from the bar chart easily the Region and Product Line where there is the greatest increase in Expenses.

I’ve configured a brush interaction between all three visualizations so that when I select the tallest bar it will highlight the corresponding data values in the crosstab and treemap.

Conclusion

Now you can use date parameters in your Visual Analytics Reports. There are several applications of this feature and this is only one way you can use parameters to drive business intelligence. Using this technique to create columns based on a user selected value is great when you need to compare values when your source data isn’t structured in this manner.

Using Date Parameters in your SAS Visual Analytics Reports was published on SAS Users.

7月 262017
 

In a previous blog, I describe how there are a few new features related to report and page prompts in SAS Visual Analytics 8.1; namely the ability to configure cascading prompts in VA 8.1: Cascading Prompts as Report and Page Prompts.

In this blog, I will cover how to configure prompts, either report, page, or report canvas prompts, that use different data sources.

Different Data Sources with overlapping data values

First, you must have two different data sources added to your Visual Analytics report. These data sources must have values that overlap that you wish to prompt on. All of the values do not need to map, but they must have some values in common if you wish to use a shared prompt.

In this example, we will prompt for Product Line. Let’s examine the column values:

I’ve color coded the values that I would like to map together. I see that the only values that match “out-of-the-box” is Game.

One work around to get all of the values to match will be to create a Custom Category and use that column for the mapping.

In a “real world” scenario, this may not be ideal. The cardinality of the two columns may be so large that you may have to go back to either the source data or ETL job to produce better matching values.

However, if you are using date columns as the mapping columns things are considerably easier as year, month, and quarter are standard values that match without extra steps.

Here is my new Custom Category that I will use for my mapping:

Here are my mappings now. I will be using Product Line (New) for the Insight Toys data source moving forward.

Add prompts

There are two different locations where you can add prompts, i.e. Control Objects, which means there are two different ways to configure prompts with different data sources:

1.     Report and Page Prompts

2.     Report Canvas Prompts

Report and Page Prompt configuration for different data sources

For this first example, I will configure a Button Bar object placed in the Page Prompt area to filter two different data sources. For the Button Bar’s Category Role, I will use the data source with the largest available selection, in this case, the Product Line (New) from Insight Toys.

Now let’s configure this button bar to filter both data sources. You must activate the button bar by clicking on it, then right-mouse click and select Edit data source mappings

Then you simply have to pick your source table’s column to map to your target table’s column.

That’s it. The mapping is complete. Here is what the report would look like with different selections made for the button bar. Notice, that since I used the Insight Toys data source for the Role assignment, and it has more values than available in the Mega Corp data. If a selection is made where nothing matches in Mega Corp, as in the Gift example, then the Mega Corp bar chart is blank.

Report Canvas Prompt configuration for different data sources

In this second example, I am going to use a List Control object within the report canvas to filter two different data sources. Again, I will use the Insight Toys’ Product Line (New) column as the List Role Category assignment since it has the most values.

Now to configure the list to filter both bar charts. Click on the list control object to activate the window. Then select the Actions pane, and use the Add button to select Add filter.

Then select both bar charts as the target of the filter Action.
Next, select the Map data option.

Select the source data’s column to map to the target data’s column. Use the + to add additional column mapping criteria.

Here is how the report would look with a few of the values selected from the list table. You can see how both Mega Corp and Insight Toys display overlapping values for Product Line but for any unique Product Lines, such as Gift, its values are only displayed on the Insight Toys bar chart.

Now you know how to configure your control objects for multiple data sources. This works no matter how many data sources you add to your report, simply use the Map data option and select the mappings between the source data and target data.

As I mentioned earlier, a frequently used application of mapping prompts for multiple data sources is for date columns. Here is a screenshot of one example using year and month. I also styled the button bar’s selected background and text color to coordinate with the graphs.

 

SAS Visual Analytics 8.1: Configuring prompts with different source data was published on SAS Users.

6月 022017
 

There are several exciting new features in SAS Visual Analytics 8.1 that I know will excite you, including that you can now configure cascading prompts for the Report Prompt and Page Prompt areas! Prior to SAS Visual Analytics 8.1 there was a way to use parameters to configure cascading prompts for report and section prompts, but now all we have to do is define a Filter from the Actions Pane. It’s a lot less work and there are even a couple of different ways it can be done.

Method 1

Steps:
1.      Add Control Objects to Report or Page Prompt area.
2.      Assign Roles to Control Objects.
3.      Define a Filter from the Actions Pane.
4.      Test your cascading prompts and enhance controls.

Method 1, Step 1
Add the desired Control Objects to either the Report or Page Prompt areas.

Method 1, Step 2
Assign Roles to your Control Objects.

Method 1, Step 3
Create the cascading prompt by defining a Filter from the Action Pane. This means that for the selected data value in the source object, it will use that value to filter the target, i.e. return the rows where source prompt = selected value.

In this example, we are using our SASHELP Cars data set. Our source is the car’s Origin and the target is the car’s Make. If you pick Europe for Origin it will return the corresponding car Makes such as Audi, BMW, Saab, Volkswagen, etc.

Click on the source Control object, Origin, and from the Actions Pane, select Add filter.

Next, select the target object to filter and click OK.

You can check to be sure your action is defined by clicking on the Objects and looking at the Actions Pane.

Method 1, Step 4
Enhance your controls using the Options Pane and then test out your configured cascading prompts!

In my example, I made the Origin button bar required and changed the Selection background color and text color. I also added a bar chart to my report.

Method 2

Steps:
1.      Define a prompt hierarchy.
2.      Drag the prompt hierarchy to the Report or Page Prompt area.
3.      Test your cascading prompts.
4.      (Optional) Enhance controls.

Method 2, Step 1
Create a hierarchy for the data items for which you wish to create cascading prompts.

Method 2, Step 2
Drag your prompt hierarchy to either the Report or Page Prompt area.

Method 2, Step 3
Test your automatically configured cascading prompts! That’s right – this is another new feature. Well, it’s two combined, first is the Auto Controls. Second, if you drag a hierarchy data item to either the Report or Page Prompt area, SAS Visual Analytics automatically defines the Filter Actions for you.

Method 2, Step 4 (Optional)
Go back and enhance your Control Objects using the Options Pane as you like.

There you have it – cascading prompts anywhere in SAS Visual Analytics 8.1 reports: in Report Prompts, Page Prompts and of course still anywhere in the report canvas; and it’s as easy as defining Filter Actions. You may be wondering, is it possible to configure cascading prompts for different data sources? Not to worry – it is and I’ll show you in in my next blog.

Cascading Prompts as Report and Page Prompts in SAS Visual Analytics was published on SAS Users.