Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
SQL Server Transparent Data Encryption Alternative - Free Webinar
 

SQL Server Reporting Services Report Builder Manual Data Sources


By:   |   Last Updated: 2018-11-08   |   Comments   |   Related Tips: > Reporting Services Report Builder

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.

New Report

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.

New data source

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.

Data Source Setup

Now, we must create a data set based on this newly created data source.

New Data Set


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.

Data Set Properties

Upon selecting Query Designer, the data entry window opens, similar the below screen print.

Data Entry Window

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.

Change Name

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.

Change Data Type

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.

New Rows and Columns

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.

Delete and Cut/Copy/Paste

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.

Finished Data Set

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.

Using Data Set

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).

Copy Paste from Excel.

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.

Publish Error

However, once the extension is added and the SSRS service is restarted, the Report Builder report can easily be published to Report Server.

Published Report

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


Last Updated: 2018-11-08


next webcast button


next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools