This article shows how to add the dataset(s) to your report. Note: Add a separate Dataset for each table of data added to your report. For example, if you want Project information as well as Risks information, you would add a Dataset for the Project Center List App and a Dataset for the Risks List App.
1. Add Dataset
- Right click on Datasets.
- Select Add Dataset.
2. Enter Dataset Properties on the Query Tab
- Enter a Name for your Dataset. It is recommended to name your Dataset so that it's clear what data is in your Dataset. For example, this dataset will be using the Project Center List as the Dataset, so the Dataset was named ProjectCenter as well. Note: Your Dataset name cannot have any spaces.
- Select the radio button for Use a Dataset embedded in my report. Typically each report has its own Dataset, since each report has its own data that it's showing. However, if you have multiple reports using the same Lists and Fields, you may want to use a shared Dataset.
- Select Data Source. If needed, refer to the previous lesson for Adding a Data Source. If you don't have a Data Source yet, you may also create a new Data Source by clicking the New button.
- Query Type: Select Text. Note: If you are familiar with writing Stored Procedures for SQL, you may select Stored Procedure.
- Select Query Designer to begin building your data query.
3. Enter Data Source Credentials
- Enter your Reporting Database User Name and Password. Note: This is NOT your user credentials for the site collection; this is the Database credentials. This verifies that you have access to the data in the Data Source.
- Do NOT select Use as Windows Credentials unless the credentials for the Reporting Database are a windows account. (This is usually only a possibility for on-premise customers, depending on how the Reporting Database was setup. If using a Windows Credential, usually you would also need to enter your domain). By leaving it unchecked, Report Builder knows it's a SQL account.
- Click the OK button.
4. Expand the Tables Folder
Expand the Tables folder. You will see all the Reporting Tables.
5. Reporting Database Tables
- For each mapped List, there is a "Refresh" table, which is the live table, as well as a "Snapshot" table, which stores any saved snapshots for time-phased/trending reporting. For example, for the Issues List App, there are LSTIssues and LSTIssuesSnapshot.
- For the Portfolio Resource and Cost Planning tools, there are "EPG" tables.
- For the Timesheet Data, there is a "RPTTSData" table. Note: The Timesheet Table will not show until there is approved time stamped onto the database, and after the EPM Live Timer runs.
- There is a "RPTWork" table that already includes all List Apps that have been specified as a "Work" List.
6. Build Dataset Query
Expand the Table for the desired List. If you select the checkbox to the left of the List name, all fields in that List will be selected. However, please note the following two reasons why it is recommended to only select the individual fields needed for this report (rather than all fields in the List App).
- Each time a report is opened by the end user, the report looks to the Database for all the fields & values that are in the Dataset, even if not all of the fields are in use in the report itself. Therefore, to reduce the time it takes for the report to load, only select the fields that you will use in your report.
- If a field is added to a report's Dataset and that field gets deleted from the EPM Live Site, the report will show an error message and won't load.
Note: The field names are the internal/back-end field names. If you are unsure of a field, you may go to your EPM Live Site and lookup any field's internal field name.
7. Selected Fields
As you check/uncheck the fields, they appear in the Selected Fields window. For this report, the following reporting table and fields were selected:
- LSTProjectCenter (List Table)
- Title (Field)
- ProjectManagersText (Field)
- State (Field)
Note: If you want to include data from multiple List Apps in this report, add a separate Dataset for each List App.
8. Run Query
- To run a quick test, select the Run Query button.
- The Query results will launch to show the data for the selected fields.
- Click the OK button.
9. Query Text Added
- The query will show as text in the Query window.
- Click the OK button to close the Dataset Properties window.
10. Confirm Dataset Added
On the left hand side, your Dataset will be listed under the Datasets folder.