Configuring Reporting Services

What are Reporting Services

SQL Server Reporting Services (SSRS) provides a set of on-premises tools and services that create, deploy, and manage paginated reports. Download SQL Server 2022 Reporting Services from the Microsoft Download Center.

(Retrived from https://learn.microsoft.com/en-us/sql/reporting-services/create-deploy-and-manage-mobile-and-paginated-reports?view=sql-server-ver16 on January 04, 2024)

How to setup Microsoft SQL Server (2019) Reporting Services

This documentation is specific to the developer environment. Production environment has differences in its own way.

To be able to locally modify and have access to the reports inside a genio-generated solution, you need to setup the Microsoft SSRS. First, go to this link here and download the latest version of the software. Once this is done, start the installation. Choose the free version with the "Developer" status and pursue the setup.

Once it is installed, you will need to configure the server: In most cases, you will want to connect to your local machine for testing purposes (PCNAME). Select SSRS too. First, setup the Web service URL. The web service URL is important as it is the link of communication between your local files and your application. You have nothing to do except clicking apply. It will run the web service, used to store the reports.

Screenshot_4

After, setup the report database server. Click the menu Database and enter change database.

Screenshot_5

  • You will want to create a new report server database unless you have one.
  • For the Database server, as an Authentication Type, use SQL Server Account. The credentials are the one associated with the user QUIDGEST. Test the connection to see if it works properly.

Screenshot_6

  • The Credentials are also those used for QUIDGEST's user.

To access the report portal URL, simply visit either http://yourserver:port/ReportServer or http://yourserver:port/Reports.

Within the Report Server Portal, you have the ability to configure folders and designate specific data sources, enabling easy access to view reports once they are created.

reports_url

Creating a Report

Report Builder provides a productive report-authoring environment for IT professionals and power users. The download provides a stand-alone installer for Report Builder.

Report Builder provides data visualizations that include charts, maps, sparklines, and data bars that can help produce new insights well beyond what can be achieved with standard tables and charts. Use Report Builder to create reports and shared datasets. Publish report parts, and then browse the Report Part Gallery to reuse existing report parts as building blocks for creating new reports quickly with a “grab and go” experience.

You can download in https://www.microsoft.com/en-us/download/details.aspx?id=53613.

Configuring the report builder

After opening the report builder, you can create a new report from a table, chart, or a blank report.

You can also open a pre-made report or select a new dataset. In this example, we will choose a blank report.

report_wizard

Data Source

Choose a Data Source, which should be related to a client database. For example, the client's production database or a testing database.

Here, you can select a shared database connection or an connection embedded.

The connection string should appear as follows: Data Source=”YOURSERVER”;Initial Catalog=DATABASECHOSEN

You should Test the connection. If it connects successfully, proceed.

report_datasource_connection

Dataset

Here, you can also choose a shared dataset or embed one directly into the report.

When opting for the second option (embedding in the report):

  1. Select the created data source.
  2. Then, it's necessary to query the database to retrieve the values you wish to see in the report.

If you prefer, you can use the Query Designer, which simplifies the process of inserting fields.

Use Case: I want to emit the passenger's boarding pass.

  1. First, You should implement and test the query directly in SSMS (SQL Server Management Studio)
  2. Then you can paste the implemented query directly in the editor.

Example:

SELECT
  FLIBOARD.GATE
  ,FLIBOARD.BAGGAGE
  ,FLIBOARD.FOODPREF
  ,FLIPASSE.FULLNAME
  ,FLIFLIGH.DEPDATE
  ,FLIFLIGH.DEPTIME
  ,FLIFLIGH.ARRIDATE
  ,FLIFLIGH.ARRITIME
  ,departure.NAME as Departure,
  arrival.NAME as Arrival
FROM
  FLIBOARD
  INNER JOIN FLIPASSE
    ON FLIBOARD.CODPASSE = FLIPASSE.CODPASSE
  INNER JOIN FLIFLIGH
    ON FLIBOARD.CODFLIGH = FLIFLIGH.CODFLIGH
  INNER JOIN FLIAIRPO departure
    ON FLIFLIGH.CODDAIRP= departure.CODAIRPO
INNER JOIN FLIAIRPO arrival
    ON FLIFLIGH.CODAAIRP= arrival.CODAIRPO
WHERE FLIBOARD.CODBOARD=@board_codboard
  • FLIBOARD is the table for boarding passes.
  • FLIPASSE is the table for passengers.
  • FLIFLIGH is the table for flights.
  • FLIAIRPO is the table for airports. This table is aliased as "departure" and "arrival".
  • FLIBOARD.CODBOARD is the primary key of the boarding pass table. The value inserted with '@' signifies the selection of a single boarding pass, as this character converts the value into a parameter.

report_query

If you utilize the Query Designer, you'll end up with something like this:

report_querydesigner

Don't forget to select the required fields and establish relationships with other tables. Ensure that when you have written the query, the parameters are included.

Finally, click the 'Run Query' button to ensure you have results.

Report design

The Report Builder window is designed to help you easily organize your report resources and quickly build the paginated reports you need. The design surface is at the center of the window, with the ribbon and the panes around it. The design surface is where you add and organize your report items.

ou can easily drag and drop the fields into the report and set labels or insert graphs as you desire.

Check the Microsoft documentation to learn more:https://learn.microsoft.com/en-us/sql/reporting-services/report-builder/report-design-view-report-builder?view=sql-server-ver16

In the example use case, the report has the following configuration:

report_design

Report validation

There is a 'Run' button located in the Home tab, which allows you to validate the report. If you have parameters, you need to check them first.

report_checkparameter

report_check

Store the report

If the final report meets your requirements, save it.

We recommend storing the report locally in the Genio Project folder where you are currently working.

Create a 'Reports' folder next to the GEN_CLIENT folder and store the report inside. The report file has the .rdl extension.

report_folder

Setup the WebAdmin

Inside the Webadmin, go to System Configuration and Select More. In Crystal report > Reports path, put the path to the report's templates (your local files in your PC). For the inputs in SQL Service Reporting Services:

  • URL: input the URL given in the Web Service URL inside SSRS. it should be http://{PCNAME}:{PORT}/ReportServer. Again, this will be where your local reports will be published to be used after by your application.
  • Path: where your generated report will go to from the URL. Specify something if you want a folder generated. (example: "report_folder")
  • Don't select local report checkbox
  • Domain: Not needed
  • Username: Not needed*
  • Password: Not needed*

To confirm the credentials, go to the report server URL but open it in an anonymous page. In this mode, it will prompt you for credentials, which are the same as those used in the Webadmin.

Here's what it should look like :

webadmin_reportconfiguration

Finally, you will need to deploy the report to your web service. Then, go to Report Management and Select More. You should click on the Deploy button.

webdmin_reportmanagement

Once this procedure is finished, you should have your reporting server ready to use.

On a side note, it is important that the report you are accessing are connected to the good database. enter the .rdl file(s) you will be using and verify your Data Sources to be sure it's connected to the one used by your application. {.is-info}

Genio implementation

You can print or view reports right in the form or in the menu.

Form

In the form, you can insert a button to print a pre-designed form.

Component type: Report button.

Component section

  • Identifier: This is the component identifier and is mandatory.
  • Display name: It will be the button's label.

Report section

  • Icon: You can insert an icon to display before the display name.
  • Report: Here you should refer to the report to use. Write the name without the extension. For example, if the report is named comodatos.rdl, simply write comodatos.
  • Table: This is the table that feeds the report. If you have more than one table in the report's query, define the table that you implement the FROM sentence.
  • Report type: Choose 'Reporting Services' from the list.
  • File type: Define the file type for the output: Ms Excel, Ms Word, Ms Word-Editable, PDF, or Report Viewer.
  • Format type: Define the size of the report page, ensuring it matches your report's configuration.
  • Display: Define the report's display mode: Preview or Print.
  • Generate to footer: When activated, the button will be positioned next to the Save and Cancel buttons on the footer.
  • Fields: Insert fields to pass to the report; see the parameters section for details.

report_form

You can print records from a specific menu list by creating a child report menu after the list where you want to view the report.

Menu item Type: Report

  • Table: This is the table that feeds the report. Since this report is a child of a menu list with a base table defined, this report will maintain the same table.
  • Format type: Define the size of the report page, ensuring it matches your report's configuration.
  • Preview: When activated, allows the document preview.
  • Report type: Choose 'Reporting Services' from the list.
  • Report: Here you should refer to the report to use. Write the name without the extension. For example, if the report is named boarding_pass.rdl, simply write boarding_pass.
  • File type: Define the file type for the output: Ms Excel, Ms Word, Ms Word-Editable, PDF, or Report Viewer.
  • Fields: Insert fields to pass to the report; see the parameters section for details.

report_menu

Parameters

If you add a menu of type Report to Genio there exists a few different ways to pass parameters to the report.

Glob parameters

You can pass glob fields to the report. If you put the field [GLOB->EXAMPLE] as a parameter, the application will pass a parameter with name g_example to the report. This means the report must have a parameter with that name and matching type

Record selection

If you have a menu list followed by a report and the base areas match, the application will pass the primary key of the selected record to the report.
If the area of the list is PERSO with the primary key CODPESSO, the application will pass a parameter with name pesso_codpesso

Accessing all Reports:

Within the Resources menu, you'll find a dedicated section for Reports, allowing you to review all reports utilized in your project.

This is used as a reference point, enabling you to track where each report has been employed.

How to use a QR code in a SSRS report

Prerequisites

Before configuring your SSRS report to display QR codes, it is necessary to install the required components on the computer running the Report Server:

  • Download the latest version of QuidSetups
  • Extract locally
  • Execute QuidSetups.exe
  • Follow the steps:
    • In "Type of installation" select Custom
    • In "Type of machine" select Server
    • Ensure Server > Other > Relatórios > Reporting Services > QRCoder is checked (you may uncheck all the other boxes)
    • Click Install
  • Restart the Report Server for it to detect the newly added assembly

Configuration

Assuming the required assemblies were correctly installed into the global assembly cache (GAC), it is necessary to set the reference to the assembly on the RDL (Report Definition Language). Using Microsoft Report Builder:

  • Open Report Properties by right-clicking outside the body of the report
  • Navigate to "References" tab
  • Under "Add or remove assemblies", click "Add"
  • Click the ... button to browse and select the component (QuidgestQRCoder.dll, should be located at %WINDIR%\assembly\GAC_MSIL\QuidgestQRCoder)

Usage

Once the necessary configuration is in place, converting a text field to a QR code representation is very simple:

  • Add an image to the report
  • Open Image Properties by right-clicking on it
  • Under "Use this field", click on the f(x) button
  • Set the expression value (e.g., the expression =QuidgestQRCoder.QRCode.StringToQRcode(Fields!NAME.Value) converts the text field NAME to a QR code representation)

Note that this guide assumes that the necessary Data Sources and Datasets are already configured in order to access the desired text fields.

Discussion

We found that there are multiple ways to display a QR code in a SSRS report. We believe that the method described in this guide is the most flexible of all, as it enables to quickly convert simple text fields to their correspoding QR code representation. However, there are some alternative ways:

  • Network request to get the QR code representation. It is possible to obtain the QR code representation (in bytes) of a given text field by sending a network request to a listening endpoint that offers this service. This method does not require the prerequisites described in this guide. However, it does require the endpoint to be accessible by the Report Server machine, which might not always be possible. Additionally, this method requires a more difficult configuration step, not described in this guide.
  • Database contains an image field with the QR code representation. Assuming the dataset already contains an image field with the desired QR code representation, the prerequisite and configuration steps described in this guide are no longer necessary, since displaying an image field is natively supported by SSRS. However, having multiple database fields that contain the same information (simply represented in different ways) is an additional cost that requires analysis.