Using a Reporting Web Service

SqBx does not contain a built in report editor, instead we support external tools using web services. There are 2 types of web based report editors, JSON and SOAP. For the purposes of this example, we will focus on the SOAP Web Service.

Two reporting tools that support a standard SOAP Web Service are BIRT and Crystal Reports.

SOAP Web Services are easily interfaced with vb.net or C# Applications. The JSON Web Service is used for reporting purposes.

Making a report using BIRT

To use the BIRT service visit their download page and download the RCP Designer. This program does not use an installer, instead you will have to unzip the file to a folder on your PC where you can execute programs.

Note: This folder should be located somewhere safe where you will remember, as it will contain the BIRT application that you will use to run and design your reports.

Run the BIRT file to open the Report Editor. To create a report, BIRT will need a connection to SqBx to retrieve the necessary data.

Note: You will need to create a report template before continuing with the next steps.

Once BIRT is opened, click on the File menu and choose “New – New Report” which will open the New Report Wizard. Enter a new File Name, press Next, and You’ll then be asked to select a Report Template. Choose your template and press Finish. A Report should now appear in the designer screen, but before you can run the report you will need to point the Data Source to the Web Service.

Note: You can include this Data Source pointing onto the template so that you will not have to repeat the last step in the future.

Pointing to Your SqBx

On the Data Panel, on the left of the screen in SqBx, you should see an item named Data Sources. Inside there is an entry named SqBxReporting. Right click on the SqBxReporting Data Source and select “Edit” which will open the “Edit Data Source” window. Two items will need to be populated within this screen, the WSDL and the SOAP EndPoint.

Note: You may have a different server name than the one listed in this guide

The server you need is the URL you use to connect to your SqBx site. For example if you use www6.sqbx.com, your server will be www6.sqbx.com.

Enter http://[WWW6.SQBX.COM]/services/sqbxreporting.php?wsdl into the WSDL URL box, replacing www6.sqbx.com with your server name.

Enter http://[WWW6.SQBX.COM]/services/sqbxreporting.php into the SOAP End Point box, again replacing www6.sqbx.com with your server name.

Click “Test Connection” and if you receive a message saying “Ping Succeeded” then everything has been set up correctly. If you do not receive the “Ping Succeeded” message double check the details you entered into the WSDL and SOAP fields to verify that they are correct.

Running the Report

Now that you have established a connection you can run the report in a few different ways. You can get a preview on the Designer page or you can use the “Run” menu at the top of the screen and select “View Report” which will give you additional report options.

Report Parameters

Before the report can be fully executed, it will need to gather some data from you. A Parameters window will open for you to enter the criteria for your report. This parameter window is similar to the Find Package search screen in SqBx. Like the Find Package screen you can enter any combination of parameters to get the data you want. You will be able to chose a date range using the date commands listed below.

• StartOfMonth
• EndOfMonth
• StartOfLastMonth
• EndOfLastMonth

The Report User Key

This field is the report’s login to SqBx and represents the user key of a report user. To create a Report User you must be a System Administrator and navigate to the SqBx Admin screen. Once in the Admin screen locate the Security section and select User Admin. Create a new user using the “View Only” option and modify the new user making sure the “Report User” checkbox is checked. Press either Generate Key or Enter a Phrase for your login credentials.

Note: If you believe that your User Key has been compromised, you can always Generate a new key to be used in the next report.

With a valid User Key entered and some criteria completed, pressing Run or Preview will return you a populated report.

Note: The preview is limited to 500 records.

Power Users and the Web Service

Users who may have used reporting tools in the past may be asking, how can I define my query, sort my results, or choose my fields? With query based reporting you have the opportunity to do that using the Query Builder or by writing an SQL script. Since you do not have access to the underlying query within a Web Service you would not be able to accomplish this.

Instead you have multiple WSDL Operations to choose from. These Operations require parameters and return data that are essentially Black Boxes, where you have no control over their internal workings. for power users, this may feel a little frustrating however you do have a lot of options when using a Web Service reporting system. Once the data has been returned you can do a lot with it.

Sorting your Data

The Template report contains a table with the reports inside. Move your mouse anywhere over the table and a “Table” icon will appear. Right click on this icon and choose Properties, which allows you to adjust many features of the Table. Click on the Sorting Tab and select “Add” which will prompt you for a Key. Select the drop-down list and choose the field you want to sort by.

Note: The names of the fields within the report may differ from the field names that you set up in SqBx. You may need to refer to the Custom Fields admin page to know which fields are which.

Filtering Data

Using the same method you used for Sorting data, you can define a filter to focus your report on a subset of the data you retrieved from the Web Service. On the “Filters” tab, select “Add” and set a new filter condition.