Working with Data Sources in Reports

About Data Sources

Many data sources that are available in the designer are prepared by a data administrator or analyst so that you can easily define a report. Data administrators load tables into memory using SAS Visual Analytics Administrator (the administrator). Analysts can use SAS Visual Data Builder (the builder) to design queries that load tables into memory too.
The Add Data Source window can be used to add or import data sources. If you have the Import and Load Data capability, then you can import data from a file into the designer. Supported files are SAS data sets, Microsoft Excel spreadsheets, and delimited text files (such as CSV files). If you can import data sources, the Add Data Source window has Import Data in the right pane.
All data sources contain data items, which can refer to calculations or columns in physical data (tables). Reports can include query results from more than one data source.

Import a Data Source for a Report

If you have the Import and Load Data capability, then you can import a data source into the designer using either the Add Data Source window or the Change Data Source window. When you import data, the data source is automatically added to the open report. For more information about importing, see Importing Local Data Files or Importing Data from Servers.
To import a data source for a report using the Add Data Source window:
  1. On the Data tab, click the Menu Down Arrow button beside the Select a data source text to display the Add Data Source window.
    Add Data Source Window with the Import Data Pane
  2. In the Import Data pane, select a data source that you want to import.
    Tip
    If you select a delimited text file, then you can specify additional options. For example, you can specify the delimiter, whether the first row contains column heading names, and where the data rows begin.
    Tip
    If you select a spreadsheet, then you can specify additional options. For example, you can specify which worksheets to import, whether the first row contains column heading names, and where the data rows begin.
  3. Click Add.

Add a Data Source to a Report

You can use one or more data sources in a report in the designer.
To add a data source to a report:
  1. On the Data tab, click the Menu Down Arrow button beside the Select a data source text.
    Data Tab
    The Add Data Source window is displayed.
  2. In the Add Data Source window, select one or more data sources.
    Tip
    Use the Search field to narrow the list of data sources that are displayed in the Add Data Source window. The search searches the Name and Description fields. It is a “begins with” search rather than a “contains” search. If you receive a message that a data source is not available, contact your data administrator.
  3. Click Add. The list of available data items is displayed on the Data tab.
  4. (Optional) To add additional data sources, click the Add Data Source button on the Data tab, which displays the Add Data Source window. Select the data sources that you want, and then click Add. The Data tab is populated with a list of all of the data items that are in the data sources.
    When you add multiple data sources, the last data source that you selected is displayed on the Data tab. If one of the data sources that you selected is not available, the last available data source that you selected is displayed on the Data tab.
    Note: When you open a saved report that has multiple data sources, the designer displays the same data source that was displayed in the Data tab when the report was saved.

Refresh a Data Source for a Report

You can refresh the columns in a data source in the designer at any time. Be aware that refreshing a data source means that all live report objects that are connected to that data source will have their queries rerun.
Note: Refreshing a data source adds any new columns that have been added to the table metadata in the SAS LASR Analytic Server. The default formats and names of existing columns will be updated the next time you open the report.
Note: Data is refreshed from the table that is currently loaded into the SAS LASR Analytic Server.
To refresh a data source for a report, on the Data tab, select the data source, and then click the Refresh Data button.
To refresh the list of data sources when you are using the Add Data Source window, click the Refresh Data button beside the search field. This updates the list of all possible data sources. Individual data sources are not refreshed.
When you refresh a data source for a report, columns that have been deleted from the table metadata in the SAS LASR Analytic Server are automatically removed if they do not impact any objects in the report. If deleted columns do impact objects in the report, then the Repair Report window is displayed so that you can repair the objects that are impacted by the deleted columns. For more information, see Troubleshooting in SAS Visual Analytics Designer.

Remove a Data Source from a Report

You can remove all references to a data source from a report in the designer. Be aware that removing a data source means that all related data items are also removed from the report objects within the report.
Note: Other reports that use the same data source are not affected when you remove a data source from a report.
To remove a data source from a report:
  1. On the Data tab, select the data source, and then click the Delete button.
  2. Click Delete in the confirmation message that is displayed.

Change a Data Source in a Report

Note: The currency format will not adjust the locale if you change the data source and the locale associated with the second data source is different from the first data source.
To change a data source in a report:
  1. On the Data tab, click the Action button, and then select Change Data Source. The Change Data Source window is displayed.
  2. In the Change Data Source window, select a data source.
  3. Click Change.
    If a data item with the same name does not exist in the replacement data source, then it is automatically removed from the replacement data source if there are no report objects that use the data item. If there are report objects that use the data item, then those report objects will not work. The Repair Report window is displayed so that you can repair the objects that use the data item. For more information about repairing reports, see Troubleshooting in SAS Visual Analytics Designer.
    Data items in the replacement data source that have names that do not exist in the original data source are added to the Data tab automatically.
    Note: For data item names, the case is ignored when data sources are compared by the designer.

View Measure Details

To view the details about all of the measures in a data source:
  1. On the Data tab, click the Action button, and then select Measure Details. The Measure Details window is displayed.
  2. Click Close.

Show or Hide Data Items on the Data Tab

You can specify which data items you want to see for the data source on the Data tab. Your selections for which data items are shown or hidden are stored with the report. For example, suppose that you hide data items in one report, and then you open a second report that uses the same data source. The data items in the second report are not hidden unless you specifically hide them in that report, too.
Note: Hiding data items on the Data tab does not hide them in the entire report. This feature is not a way to implement column-level security.
To show or hide data items:
  1. On the Data tab, click the Action button, and then select Show or Hide Items. The Show or Hide Data Items window is displayed.
  2. Select the data items that you want to appear on the Data tab. If there are data items that you do not want to see on the Data tab, then clear the check box (or check boxes) for that data item (or data items).
  3. Click OK. The Data tab is updated.
Alternatively, you can select a data item that you want to hide on the Data tab. Right-click the data item, and select Hide Data Item.
Tip
Starting in the 7.2 release, you can hide all of the data items that are not used in the current report. On the Data tab, click the Action button, and then select Only Show Used Items.
You can also use a data source filter to restrict the data that is displayed in a report. For more information, see Use a Data Source Filter in a Report.

Sort Data Items on the Data Tab

To sort data items on the Data tab, click the Options button, and then select one of the following:
  • Sort Itemsthen select Ascending By Name
  • Sort Itemsthen select Descending By Name
The data items are sorted on the Data tab within each grouping. The default sort is Ascending By Name.
For information about sorting data values in report objects, see Sorting Data in Reports.

Group Data Items on the Data Tab

To group data items on the Data tab, click the Options button, and then select one of the following:
  • Group Itemsthen select By First Letter
  • Group Itemsthen select By Data Type
  • Group Itemsthen select By Classification
  • Group Itemsthen select By Format
  • Group Itemsthen select By Aggregation
The data items are grouped on the Data tab. The default is grouping By Role.