How do I map lists for reporting?

This article will show you how to manage your List Mappings for the Reporting Solution, which maps/creates a Table on the EPM Live Reporting Database on SQL Server.  When the EPM Live Site App is mapped to the new Reporting Database, certain List Apps are automatically mapped for reporting.  Additionally, other List Apps may be mapped.  Any list can be mapped, in order to be able to create and run reports.

This article explains how to map and edit list mappings from the Reporting Settings page.  Note that the lists can also be mapped from the List Settings General Settings page, but then would still be edited from the Reporting Settings page.

When a list is mapped, two tables are created in the Reporting Database - the "Live" table and the "Snapshot" table.  

  • Live Table: The table matches the data in the mapped fields and will be updated every time an item is added, edited or deleted from the List App.  The Live table is titled: "LST[ListName]."
  • Snapshot Table: Each time a snapshot is taken, a copy of the live data is taken at that time.  Snapshots can be run manually or scheduled.  Reports can be created to compare data from one period to the next.  The Snapshot table is titled: "LST[ListName]Snapshot."

The following fields are always mapped automatically for each mapped List App.  All others are optional.

  • Title
  • Created
  • Created By
  • Modified
  • Modified By

1. Navigate to Reporting Settings

  1. Select the Settings Gear icon on the Icon Bar.
  2. Under the Enterprise Reporting heading, select Reporting Settings.

2. Mapped List Apps

All mapped List Apps will show on this page.  

3. Modify Existing List App Mapping

  1. To modify an existing List App Mapping, select the name of the List App.
  2. Select Edit.

3.1. Update Field Mappings for List App

For an existing List App Mapping, the List selection will be grayed out and unavailable to change.

3.2. Resource List Checkbox

Configure if the List App is a Resource List for use with Resource reporting.  This is also called a Work List, meaning that the items in the List App are "work" items, and can be assigned to Resources.

If this List App has the Work List Feature enabled, then it can also be selected here as a Resource List.  Any custom List App may be used as a Resource List with the right configuration. The Resource List checkbox will be set by default if the necessary fields are available in that List App.  When the Resource List checkbox is checked, the following required fields have to be selected for mapping to the Reporting Database: Title, Assigned To, Start Date, Due Date, and Work.  So, these fields cannot be unchecked.  However, you may choose not to map those fields.  To un-map these Resource fields individually, the List App can no longer be a Resource List unselect the Resource List checkbox first.

If the required fields are not available at the time of mapping, the Resource List option will be grayed out as unavailable. The mapping may be edited later if the required fields are added to the List App, if the List App becomes a Work List.

3.3. Select the List App Fields to Map

Each List App mapping can be edited to add or remove specific fields. When fields are added to the mapping, a column (or two columns in some cases) is added to the database table for this List App. All existing records are set to NULL for the added column. When fields are removed from a mapping, the corresponding table column(s) are immediately deleted. These table adjustments are processed on both the mapped table and the snapshot table.

  1. For quicker selection of fields to map, use the Check All or Uncheck All boxes.
  2. The Title field is mandatory to be checked/mapped for all List Apps, so it is grayed out.
  3. If desired, check the boxes individually for the fields to map for this List App.  

4. Save and Close List App Mapping

When finished, click the Save Settings button.

5. Add a New List Mapping

Note: In the List App Settings, the option to Enable Reporting automatically maps that List App to the Reporting Database, and adds that mapped List App to this page.  Likewise, when a List App is manually added to this page, the setting for Enable Reporting gets automatically gets checked in the List App Settings.

  1. Select the Actions drop down menu.
  2. Select Add List.

5.1. Select the List App to Map

  1. Select the desired List App Name for mapping.
  2. Select the fields to map.

6. Delete a List Mapping

  1. To delete an existing List App Mapping, select the name of the List App.
  2. Select Delete.

Note: All data associated with that mapping will be deleted from the Reporting Database, including the snapshot table.  However, the List App's content will still exist in the SharePoint Content Database.

Note: If the List App has a look up field checked in another List App's mapping, you may need to uncheck that field in the child List App's mapping first.



Please sign in to leave a comment.