Creating an Invoice Report with Bold Reports Designer

A sales invoice is an important business document that lists the items purchased and their payment details. It’s like a shopping bill that sellers send you after they send you the stuff, and it shows how much to pay. Utilizing the Bold Reports Designer simplifies the process of making professional invoices.

In this blog, we will create an invoice report that displays dynamic text, a billing address, a shipping address, a shipment table, and a product details table.

The report displays information about the items purchased. It contains details about the billing address, shipping address, and tables with detailed information about shipping and payment details.

Prerequisites

To create a new report, you can use either the Bold Reports Server’s designer or the standalone Report Designer.

Create new report

1. Launch the Bold Reports Server and log in to your account.

2. Navigate to the Reports

3. Click + in the left panel to create a new report.

   

Create a parameter with the default value

The invoice report needs to be generated based on the Invoice ID. So, we create a parameter for the report either to get the invoice number for the report generation or to input the invoice ID on the execution of the report.

To create a new parameter for Invoice ID, click the Parameter panel and enter InvoiceID as the name and Invoice ID as the prompt.

As I prefer to run the invoice report with one of the default values, I am adding this default value to the parameter. Click Assign Values in the Parameter panel. Then, select Specify under the Default Value tab and enter the value in the Value text box.

Click OK to save the parameter. In the report preview, the invoice report will automatically generate with OrderID – 10252. The invoice parameter is created in the following image.

Create data source

To add the necessary data to the report, create a new data source. In this case, I’m using the Northwind Database, but you can use your own database, instead. Follow these steps to create a data source:

1. Click the data icon in the configuration panel.

2. Go to the DATASOURCE configuration panel and then NEW DATASOURCE.

3. In the connection type panel, select the data source type you want to connect. Here, I’ve connected to the Microsoft SQL Data Connector for demonstration purposes.

   

4. Enter the credentials needed to connect to the instance of the database engine.

5. Click Save and the new embedded data source will be added to the data source list.

   

Create data set

We are creating a data set with shipping details and ordered product information for an invoice report using the Northwind database we’ve previously set up.

Analyze the documentation on data set creation, and then generate data sets for the invoice report using the following queries.

Shipment details data set query

Create a data set query to fetch the shipment details for the order by using the InvoiceID parameter. The InvoiceID parameter is added to the query with the syntax of @, which I have already added in the report.

SELECT  Orders.OrderDate ,Orders.RequiredDate ,Orders.ShippedDate ,Orders.ShipVia ,Orders.Freight ,Orders.ShipName ,Orders.ShipAddress ,Orders.ShipCity ,Orders.ShipRegion ,Orders.ShipPostalCode ,Orders.ShipCountry FROM Orders where Orders.OrderID=@InvoiceID

Order details data set query

Create the data set query to fetch the order details for the order using the InvoiceID parameter. The InvoiceID parameter is added to the query with the syntax of @, which I have already added in the report.

SELECT  [Order Details].ProductID, Products.ProductName, [Order Details].Quantity, [Order Details].UnitPrice, [Order Details].Discount FROM [Order Details] INNER JOIN Products ON [Order Details].ProductID = Products.ProductID WHERE ([Order Details].OrderID = @InvoiceID)

Calculate item price with data set

Create new fields in the order details data set for calculating product discounts and product prices. See here for field creation.

1. Add a Discount Amount field below the query field in the menu.

    =Fields!Discount.Value*Fields!Quantity.Value*Fields!UnitPrice.Value
   

2. Add a Price field below the calculated field in the menu.

    =Round((Fields!Quantity.Value*Fields!UnitPrice.Value)-Fields!DiscountAmount.Value,2)
   

In the DATASET panel, your created data sets are shown like in the following image.

Add a text box to show order details

1. Drag and drop the text boxes to the design surface and assign data to them.

2. Add text boxes for Order ID, Order Date, and Shipping Date, then add separate text boxes with the expressions.

   

3. Add a line to separate the text box details.

4. Add text boxes for the Billing Address and Shipping Address and set expressions to dynamically populate these details from the data set.

5. Use a rectangle to improve the layout and group of text boxes aligned with it.

   

Add a table to show invoice details

Shipment Details table

1. Drag a table to the design surface and assign data to it.

2. Add shipment details table with fields for Name, Address, Freight, Date, City, and Country**.**

   

3. Add a shipdetails data set to the shipment details table through the table settings.

   

4. To display the sales shipment details in the table, refer to the Assign expression in tablix cell documentation and assign the data to the table.

   

Product details table

1. Add a details table with fields for Product ID, Product Name, Quantity, Unit Price, Discount, and Price.

   

2. Add an Orderdetails data set to the product details table through the table settings.

   

3. To display the product details in the table, refer to the Assign expression in tablix cell documentation and assign the data to the table.

   

4. Add a new row to calculate the total price for all products. To insert a new row at the end of the table, select Insert Row followed by Outside Group Below. Use the following query and calculate the total.

       =Sum(CDec(Fields!Price.Value))

1. At the end of the report, include a declaration message for the invoice details.

   

Preview report

We can preview the report by toggling from design to preview. We can also export the report in PDF, Excel, Word, HTML, PowerPoint, XML, and CSV formats.

Define available values for a parameter (optional)

We have displayed the invoice report with the default parameter value. You can also view the invoice based on the available Invoice ID in the database. When previewing the report, you have the option to choose parameter values dynamically. To do that, we need to create a data set that lists the available values. These values will be displayed in a drop-down list when previewing the report.

To create a new data set for the invoice ID parameter, use the following query.

Invoice details data set query

SELECT DISTINCT OrderID FROM [Order Details]

Click on the Parameter panel, then click Assign Values and choose Select Query Value under the Available Value tab. Then choose Invoice Details from the data set dropdown. Select Order ID in the Value and Label fields from the dropdowns.

When previewing the report, the available values defined for the parameter will be listed in the dropdown list as shown in the following image. You can select a value from the dropdown list and click Preview Report.

Publish report

Now, we are going to publish our invoice report. We can do this publicly or privately. A public report allows access to anonymous users, while private reports provide access only to registered users.

Once the Publish button is clicked, a pop-up window opens. Fill in the fields: category, name, and description. Click Publish to publish the report.

Conclusion

I hope this blog helped you figure out how to make an invoice report using the Bold Report Designer. To learn more, check out our sample reports and Bold Reports documentation. I have attached a demonstration report RDL file here for your future use.

If you have any questions, please post them in the comments section. You can also contact us through our contact page or, if you already have an account, you can log in to submit your support question.

Bold Reports offers a 15-day free trial without the need for a credit card. We welcome you to start a free trial and experience Bold Reports for yourself. Let us know what you think!

Stay tuned to our official Twitter, Facebook, and LinkedIn, pages for announcements about upcoming releases.