Chat Icon
Webinar Rendering Millions of Records Fast: Printable Reports for Data-Driven Decisions —Thursday, June 27, 10:00 A.M. ET SIGN UP NOW
Created with Sketch.
Login-icon
Creating an Invoice Report with Bold Reports Designer
Invoice Reporting

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.

invoice report
invoice report

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.

    New Report
    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.

create a new parameter
Create a new parameter

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.

Default Value
Default Value

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

Parameter
Parameter

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.

    DataSource connection
    DataSource connection
  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.

    Data Source
    Data Source

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
  1. Add a Price field below the calculated field in the menu.
    =Round((Fields!Quantity.Value*Fields!UnitPrice.Value)-Fields!DiscountAmount.Value,2)
Fields
Fields

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

Datasets
Datasets

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.

    Text box
    Text box
  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.

     Text boxes with Rectangle
    Text boxes with Rectangle

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.

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

    Shipment dataset
    Shipment dataset
  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.

    shipment details
    Shipment details

Product details table

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

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

    Order Details
    Order Details
  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.

    Product details table
    Product details 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))
Total price
Total price
  1. At the end of the report, include a declaration message for the invoice details.

    invoice details
    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.

preview report
Preview report

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.

Available Value
Available Value

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.

Report Parameters
Report Parameters

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.

Publish
Publish

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.

Publish Report
Publish 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.

Tags:

Share this blog

Leave a Reply

Your email address will not be published. Required fields are marked *