Problem
What is this new feature to enter data directly into a Report Builder RDL file and how would I use it?
Solution
Some have said that SQL Server Reporting Services (SSRS) and the quick report development tool Report Builder have languished and not kept up with the times. I would say with the advent of Power BI, some even thought SSRS was headed for the decommission shelf. However, Microsoft has been reinvigorating SSRS and, appropriately so, integrating it with Power BI. Furthermore, some features from Power BI are making their way into SSRS. One of these features is the ability to add an embedded data set manually in Report Builder; this process allows for direct entry of data into column and rows.
Although maybe not as handy as a data set that is based on, say for instance, a database query, several main uses could be sourced from a manually entered data source. First, it easily could be used for setting several core values that may be used by report, similar to variables. Furthermore, it could be used to populate a parameter list. Finally, I could see it being utilized in situations where maybe access is restricted to a data source / data set, but a Report Developer needs to develop a report based on that restricted data set.
First to get started you will need the latest version of Report Builder. Additionally, a functioning version SQL Server Reporting Service 2016 or higher is needed to publish the report online. SQL 2017 Report Server can be downloaded from here.
SQL Server Reporting Services Report Builder 2016
To begin the development process, we first must open Report Builder and create a new report by clicking on the Blank Report option.
Once a new report is created, the next step is to create a new data source. Remember for this feature, you must actually create a data source within the report, and it will be only available to this report.
The data source must be assigned a name and we must select “Use a connection embedded in my report” as shown below. Finally, from the Connection Type List, ENTER DATA should be selected and then you click OK.
Now, we must create a data set based on this newly created data source.
Similar to the data source, we must define the data set name, select “Use a dataset embedded in my report”, and then select the Data Source we just created with the ENTER DATA type. Finally, we can “design” or enter our manual data by selecting the Query Designer option.
Upon selecting Query Designer, the data entry window opens, similar the below screen print.
We can just start entering data, using the tab button or mouse to navigate and add new values and rows.
The column names can easily be changed by right clicking on the column header and selecting Change Name. Likewise, double clicking on the column header to allows the report designer to change the field name. Note that spaces and many special characters are not allowed.
Report Builder sets a default data type based on the data in the column; however as shown below, we can also assign a data type to a field. The various types include:
- Boolean
- Date
- Float
- Integer
- String
Of course, assigning data types is important if this data will be used as a source for such things as parameters or potentially utilized in a lookup capacity.
To add new rows, you can click on the * in the bottom left of the data entry window. Correspondingly, to add a new column, you would click on the * in the upper right corner of the data entry window.
Rows and columns can easily be deleted or cut/copy/pasted by right clicking either the row header or column header. Please note that there is NO confirmation that you want to delete the data.
When you are done entering data, you would simply click OK. On the data set properties screen, you will notice the data is XML embedded in the Query text, as illustrated below.
From this step, you can now use the data set as you would any other data set within your report, such as adding the fields to table object on the design grid.
In addition to the manual entry, data can also be easily cut and pasted from Excel. As illustrated in the next screen print, rows and columns from Excel can be copied and then using a right mouse click, the same rows and columns can be pasted into the Query Designer window. Report Builder maintains the rows and columns, but the column headers must be adjusted (note: if you copy the column headers, then will be copied into the Query Designer as the first row).
Once we are finished adding our manually entered data sets and would like to publish the report to a Report Server, you will need to make one modification to the rsreportserver.config file which generally would reside with a default install in: C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\ReportServer. If you installed SSRS to a different location, you will have to determine the exact directory location for the config file. As of right now, the following lines must be added to the <DATA> <EXTENSION> portion of the config file:
<Extension Name="ENTERDATA" Type="Microsoft.ReportingServices.DataExtensions.XmlDPConnection,Microsoft.ReportingServices.DataExtensions"> <Configuration> <ConfigName>ENTERDATA</ConfigName> </Configuration>
GO
Furthermore, I have found a restart of the SSRS Service is required for this change to take effect. If the noted change is not made to the reportserver.config file, the following error will result.
However, once the extension is added and the SSRS service is restarted, the Report Builder report can easily be published to Report Server.
Finally, you may be asking, just as I have, when will this functionality be available in SSDT for Visual Studio. Microsoft has stated that it should be available in one of the upcoming releases, but a certain time has not been specified.
Next Steps
- Check out these resources:
I have a passion for crafting Business Intelligence Solutions for my user groups. My experience includes almost 15 years of SQL Server involvement with the last 12 years focused specifically on Business Intelligence, SharePoint, OLAP, SSRS, and Decision Support solutions. Currently, I am a Business Intelligence Architect in the healthcare industry, and I also teach database and analytics classes for Kennesaw State University, Southern New Hampshire University, and Reinhardt University. My education includes an MBA and an undergraduate in Accounting (yes I am a reformed accountant!), both from Kennesaw State University. I enjoy every day by trying to grow my faith and spend precious time with my family. I have been happily married to my wife of over 20 years, and we have two teenagers one who we home school with the help of a University Model School, Cornerstone Prep in Acworth, GA (cornerstoneprep.org). Our other child is a Construction Management major at KSU’s Southern Poly / Marietta campus. We are a soccer and Cross Country (XC) family who play, coach, and referee soccer or run for fun most every day. For several years, our family has volunteered (and played with the dogs and cats) at Etowah Valley Humane Society in Cartersville, GA.
- MSSQLTips Awards: Champion (100+tips) – 2016 | Author of the Year – 2015 | Author Contender – 2014, 2016-2021