SSRS Report Builder Part 4.7 - Entering and Copying Data into a Report
Education
Introduction
In this tutorial, we will explore how to enter and copy data into a SSRS report's data set using Report Builder. We will cover creating a data source for manual data entry, typing in data directly, configuring the report server for deployment, and the process of copying and pasting data from various sources like websites, Excel workbooks, and text files.
Creating a Data Source for Manual Data Entry
To start, ensure you have a new blank report in the Report Builder. Begin by disconnecting from any report server if connected, as it's important for entering manual data. Right-click on the Data Sources folder in the report data window, select “Add Data Source,” and label it “Enter Data.” Use an embedded connection and change the connection type from SQL Server to “Enter Data.” Click OK to create the data source.
Creating a Data Set
Next, we will create a data set to begin entering data. Right-click the newly created data source and choose “Add Data Set.” Click on the “Query Designer” button to open the query designer tool. Here, you can start typing your data into an empty table.
For our example, we will use a recent story about a woman who spent £16 million in Harrods. Our data set will include the following items:
- Boucheron Jewelry
- Cartier Jewelry
- Tom's Sandwich (not an actual sandwich but a high-end item for £332,000)
- Dennis Basso Fashion
When entering data, keep in mind that pressing Enter moves down to the next row but does not create a new row. To create a new row, click on the little asterisk button or press F2 on your keyboard. Similarly, after entering data, you can rename the columns by double-clicking on them and adjust the data types as needed.
Configuring Report Server for Deployment
After creating the data set, it's essential to display it in your report. We will use a bar chart to visualize the data. Make sure you are disconnected from the report server before running the report to avoid any configuration errors.
If you attempt to run the report while connected to a report server, you may encounter a message indicating that the “Enter Data” data extension is not configured. To enable this, you must modify the report server config file. Find the config file location on the Microsoft documentation page based on your version of Reporting Services. Open the XML file with a text editor and insert the required XML to register the "Enter Data" data extension, save the changes, and close the file.
Copying and Pasting Data from Various Sources
After ensuring the data extension is set up correctly, test the report again. If everything is in place, you can begin copying and pasting data from various sources.
To demonstrate this, we will copy data from the Rotten Tomatoes website's list of top movies. Select the data, copy it (CTRL + C), and return to Report Builder. Right-click on the “Enter Data” data source, choose to add a new data set, and paste the copied data. Edit the column names as needed, ensuring to avoid spaces in the column names. Repeat the process for an Excel workbook or a text file.
Conclusion
Throughout this process, you will have successfully entered custom data into your report data set, configured the report server, and learned how to copy and paste data from various external sources into your reports.
Keyword
- SSRS Report Builder
- Manual Data Entry
- Data Source
- Data Set
- Report Server
- Copy and Paste Data
FAQ
Q1: How do I create a data source for manual data entry in SSRS Report Builder?
A1: Right-click on the Data Sources folder, select “Add Data Source,” name it (e.g., “Enter Data”), choose an embedded connection, and change the connection type to “Enter Data.”
Q2: Can I enter data directly into a data set in Report Builder?
A2: Yes, after creating a data source, you can create a data set and access the query designer to manually input your data into an empty table.
Q3: What do I need to do if I want to deploy my report to a report server?
A3: Ensure that the “Enter Data” option is configured in the report server config file. This involves modifying the XML to register the data extension.
Q4: How can I copy and paste data from external sources into a data set?
A4: You can copy data from websites, Excel workbooks, or text files, then add a new data set in Report Builder and paste the data into the query designer.
Q5: What should I do if the report fails to run while connected to a report server?
A5: Disconnect from the report server before running the report as the “Enter Data” feature may not be registered for use with report servers by default.
