The first thing we need to do is build our report. To do this we will use Visual Studio 2017. It could show in your Programs as Visual Studio 2017 (SSDT). This is a standalone version of Visual Studio with the SQL Server Data Tools, the SSDT portion, built in.
Now let's create a new Project. When the New Project window opens we can select a project template. We need to use the Report Server Project under the Business Intelligence category.Give your Project a name and specify a location where to store the report files etc.
Now that we have our Project, let's add a report. On the right hand side in the Solution Explorer you will see a Reports folder, right click that and select Add New Report.
You will be presented with a Wizard to guide you through creating the data source and report.
Now give your data source a name, I called mine Northwind as it will be a connection to the Northwind database on my local SQL Instance.
If you know the connection string details and proper formatting you can type it in. If you do not simply click Edit. I am using a local SQL copy so I am using ".\" as my server. Generally this would be a server name or IP address of your SQL server. Also keep in mind if you have a specific instance name for SQL you will need to include that. Then select the Northwind database as the database to connect to.
An example would be "192.1.1.100\MSSQLSERVER"
Once you have your details filled in make sure to Test the Connection. It will validate you can connect with the credentials and the database is accessible.
Now click OK to close the Test Connection windows, and OK again on the Connection Properties Window. Click Next to move on from that data source. It will now ask us for the query code we want to use to return data for the report. This example will create a New Order form report so let's pull in Order Information from the Orders table. Click Next once you have pasted the query code in.
We are using the following SQL code:
--Retrieve Order Information
SELECT [OrderID]
,a.[CustomerID]
--Pull Actual Company name from joined Customers table
,c.CompanyName
--Pull the First and Last Name together instead of the EmployeeID Number
,b.FirstName + ' ' + b.LastName as EmployeeName
,[OrderDate]
,[RequiredDate]
,[ShippedDate]
-- Get the Shipping company name instead of the ID number
,d.CompanyName as ShipVia
,[Freight]
,[ShipName]
,[ShipAddress]
,[ShipCity]
,[ShipRegion]
,[ShipPostalCode]
,[ShipCountry]
FROM [Northwind].[dbo].[Orders] a
inner join Employees b on a.EmployeeID = b.EmployeeID
inner join Customers c on a.CustomerID = c.CustomerID
inner join Shippers d on a.ShipVia = d.ShipperID
-- Used to only return the single order we want using a parameter called @OrderID.
where OrderID = @OrderID
On the next screen we will be using the Tabular report style. Click next.
On the Design screen, leave this blank and click next.
Now give your Report a name, I used "New Order". Click Finish. You will be presented with the generated report.
*** TIP - On the toolbar select Report > View > Ruler. This will give you a visible ruler on the page to help size your report items appropriately.
While technically the report could be used now. It isn't laid out well and could use a lot of work to make something you would want to use. Let's start by removing the table and resizing the layout window. Select the table and delete it. Now resize the text box for New Order to less than 8 in. This needs to be done to shrink the page size itself. For the page size select an area of white space below New Order where the table was, this is the page area. You should be able to set the size properties on the right hand side. Once the page is set to 8.5in x 11in you can resize the text box for "New Order" to the full width. You should end up with something like this.
Now we can begin by putting back the data in a more readable format. Let's insert a new table. Right click on that page area > select insert > select table.
Now we do not want to use this like a repeating line grid of data, mainly for row and column alignment so let's delete the "data" row.
You will end up with a basic table. Here you can lay this out as you wish. I added some columns, a section title area, and started dropping in fields. I used the first column as a label where I would type the data label. The second column is for the data itself. You can click the right icon in each field. You should be presented with a list of available columns from the SQL code you entered in the beginning. Continue adding rows or columns and filling in the fields to represent how you would like it to be displayed.
I ended with something like this.
Now click preview and it will build the report and ask you for an Order ID. Enter an ID and click View Report.
Looks a lot better than the original we started with. You can see though that the date fields are also showing times, which are unnecessary. Let's fix that. Right click on the field for order date and select Expression.
Now let's add a function to the existing formula. Currently it is just pulling the data. Let's tell it to only pull the left 10 characters or just the date portion.
Do that for each of the date fields. When you are done preview the changes. Much better.
Now we could be done and publish and the report would work, however I am going to also add in the Order details to this report. To do this right click on datasets and add dataset.
Give the Dataset a name. Choose to use an embedded Dataset and select Northwind from the Datasource dropdown. Add in the following SQL code in the query box. Then click OK.
SELECT [OrderID]
,a.[ProductID]
,b.ProductName
,a.[UnitPrice]
,[Quantity]
,[Discount]
,(a.Quantity * a.UnitPrice) * a.Discount as DiscountAmt
,(a.Quantity * a.UnitPrice) - ((a.Quantity * a.UnitPrice) * a.Discount) as ExtendedPrice
FROM [Northwind].[dbo].[Order Details] a
inner join Products b on a.ProductID = b.ProductID
where a.OrderID = @OrderID
Now Add another table like before. This time we will NOT remove that data row. Instead put your data fields there and your column labels in the header section. You should get to something similar to this. (You can add colors and formatting much like Office)
We do need to format the numbers to make them easier to read. to do this we right click our data field and select "Text box Properties"
Now Select the Number section and then the type of formatting the field requires. Unit Price, Discount Amt., and ExtendedPrice should all be currency. Quantity should just be a number. Discount should be a Percentage.
The last addition we are going to do is add the order total. Right click the data row > select insert row > Outside group - below. Now right click that row and add two more rows below it for a total of 3 blank rows.
For the first line we will do a Subtotal. When you select a field now it will automatically SUM number values. This will give us an expression of "=Sum(Fields!ExtendedPrice.Value)"
Next let's add details for Freight. Open the Expression box for the field directly below the SUM of Extended Price you just made. The Expression here is "=Sum(Fields!Freight.Value, "DataSet1")" This gets the value of Freight from the dataset named DataSet1. That is the Order information we setup during the wizard.
Finally on the last line we setup the Order Total. The expression is simple as it's the subtotal expression + the Freight expression or simply "=Sum(Fields!ExtendedPrice.Value) + Sum(Fields!Freight.Value, "DataSet1")" Now you can format these 3 numbers like we did earlier for currency values.
The designer should look like this.
Once you preview it the report should look like this.
That's it!! In Part 2 I will cover deploying the report and validating operation in SSRS before moving on to implementing this report inside Radzen. Stay tuned.
