Thursday, January 19, 2012

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 ....
Post a Comment