SSRS: Creating a Simple Report With An Embedded Dataset

The purpose of this blog post is to put together a simple guide to producing a "test" report. I'm doing this as a separate post so that I can re-use it in Other Posts (rather than having to include basic setup information every time).

Other Report Builder 3 and select "New ..." to trigger the New Report of Dataset wizard;

Report Builder 3: New Report of Dataset Wizard

"New Report" report will be automatically selected on the left, on the right select "Table or Matrix Wizard" (the top item);

Report Builder 3: New Table or Matrix Report Wizard

At the bottom left check the "Create a dataset" radio group and then click "Next >" at the bottom right;

Report Builder 3: Choose a Data Source

From the list you need to select a Data Source connection to use and then click "Next" at the bottom right;

Report Builder 3: Design A Query

Now you can enter the SQL. As this is just a simple test I'm going to use the following (Oracle) SQL;

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') AS DATE_TODAY,
       TO_CHAR(TRUNC(SYSDATE, 'MM'), 'DD-MON-YYYY') AS DATE_FIRST_OF_MONTH,
       TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1) - 1, 'DD-MON-YYYY') AS DATE_LAST_OF_MONTH
  FROM DUAL

This simple piece of SQL just gives us a single row with today's date as well as the first and last days of the current month. Click the Run button (red exclamation mark above where you entered the SQL) to check the SQL works;

Design A Query: Testing the SQL

Once everything works click "Next";

Report Builder 3: Arrange Fields

The three fields in the SQL we've just added are in the "Available Fields" box on the left of the dialog. Drag/Drop them into the "Values" box at the bottom right;

Report Builder 3: Arrange Fields

Click "Next";

Report Builder 3: Choose the Layout

We don't want to make any changes here so just click "Next";

Report Builder 3: Choose a Style

Similarly here we don't want to make any changes so just click "Finish".

The report has now been completely generated and you will be presented with something similar to;

Report Builder 3: Fully Generated Report

Click on "Run" at the top left (to test the report);

Report Builder 3: Testing Generated Report

And you're done ....