We recently released a new version of SQL Server 2012 Report Builder that contained a brand new feature – the ability to enter data directly into an RDL report as an embedded dataset.
This new feature is similar to the one found in Power BI Desktop. Now, customers can either type the data in directly into a dataset in their report, or paste it in from another program like Microsoft Excel. Once users have created their dataset by entering data, they can use it just like they would any other embedded dataset they’d created. This feature is especially useful for small, static datasets you might need to use in your report like report parameters.
- Download SQL Server 2016 Report Builder
How to get started
Once you’ve downloaded and installed Report Builder, you’ll follow the same workflow you’ve previously used to add an embedded data source and dataset to your report. Under Data Sources, you’ll see a new option labeled “Enter Data” that you’d select. You only need to set this data source up once, since you’ll the be able to create multiple tables of entered data as separate datasets using that single data source.
To begin entering data in a new table, create a new dataset in your report and hit the “Query Designer” button to start entering data into the table that appears.
You can set the column headers by double-clicking the cell or using the option in the right-click menu to change the name. By default, the data type for each column is a String. You can optionally right-click the column header and set it to other data types like Date or Integer. Once you’ve finished creating the table, click OK. Note: Long time users of Report Builder will notice that the query that’s generated is the same as you’d see with an XML data source. Under the covers, that’s what we’re using as the data provider. We’ve just re-purposed it to enable this scenario as well.
Once you’ve finished creating your report, you’ll probably want to publish that to your report server. We’re planning to enable this new data extension automatically on the server in the next update of Power BI Report Server and next release of SQL Server, but you don’t need to wait until then to use this. If you’re using SQL Server Reporting Services 2016 or 2017 (or Power BI Report Server), you can add the following item to your RsReportServer.config as an additional data extension. Please note: You should back up your RsReportServer.config file prior to making the change in case you run into any issues.
