Report development is a multi-step process. We started with exploring the AF database which
we would like to use as our data source, we created a query which acquires data from the
AF database using PI SQL client. Now the next step is to use a tool to create the report.
We can choose between two tools. The first one is report builder, available as ClickOnce application
or what we will use is Visual Studio 2019 with the Microsoft Reporting Services Projects extension.
Let's navigate to our learning course virtual machine and open
Visual Studio 2019 from the task bar. For this demonstration we have created a sample
reporting services solution located in the PI SQL client SSRS folder. In the solution
explorer you may notice that the solution contains just one item: wind farm summary
RDL. The wind farm summary RDL is a sample report which we will populate with data.
We need to create a data source, which in simple words is a connection string. The content of the
string depends on the data provider we will use. It will be different for PI SQL client ODBC and
it will be different for PI SQL client OLEDB. Let's right-click on the shared data source
and select the add new data source option. Let's name the data source according to our
AF database that we will use namely wind farm. We will use PI SQL client OLEDB. So let's change
the provider type to OLEDB and finally let's enter a PI SQL client OLEDB connection string.
It consists of the provider it should use, the data source location and identification
type. After we have created our data source our next step is to create a data set which
basically is a query which is used to get the data for our report from our data source. To
create a data set right click on the shared data sets folder and select the add new data
set option. The wind farm summary RDL is configured to use a data set with the name
wind farm summary so let's enter it. Make sure the wind farm data source is being used which
we have created in the previous steps and use the default query type which is set to text.
Copy and paste the query created in PI server commander lite to the query
field. We can validate the query by pressing the Refresh fields button. Navigate to the
fields type to check the available fields from our query. The precreated wind farm
summary RDL report is configured to use those specific fields. Please make sure
your selected list has the exact field names as it is displayed on this screen.
Finally, to validate our report you can open wind farm summary RDL item
from the solution explorer and in the open and report window navigate to
the preview tab and make sure that the data was correctly retrieved.
In the next few steps we will create a drill down report using the SQL server
reporting services wizard. To start right click the reports folder and select add new
report. Set the shared data source to the previously created wind farm data source
and press the next button. Now we need a query which will be used in the report.
Navigate back to the PI SQL commander lite we'll make a small modification to the query
we have used for our previous data set. We'll add another restriction by the wind farm name;
we'll make sure that the final restriction is based on a parameter by using a question mark.
Press the F5 button to test a modified query and in the parameter pop up enter our sample wind farm
name. Press the OK button to execute the query. If the query works correctly, copy it and move
back to visual studio. Now paste the query from PI SQL commander lite with the parameter restriction,
move to the next step. Let's consider two types of reports: the table and the matrix.
The table has fixed columns and dynamic rows. The matrix has dynamic columns and
rows. Both are subject to change when the underlying data changes. For this example
we'll use a matrix. We'll make a simple layout by moving region and state to the page section,
wind farm to the columns section, name to the rows section and the rest of the fields
to the details section. We don't need to include the duration in seconds field,
since we are not performing any calculation on that field and SQL Server Reporting Services
handles displaying of the duration column well even if the duration is greater than one day.
On the final page set the report name to inform summary details and press the finish button.
In the report data section on the left we can verify what was generated by the
wizard. First let's expand the parameters node. We can recognize that the parameter
of the name parameter 1 was generated. Let's rename it by right-clicking on the item and
selecting the properties option. Change the name to wind farm name and change the
prompt of the parameter to something more meaningful. Press the OK button and next
expand the datasets node. A data set one was generated by the wizard. Let's navigate to the
properties of the data set to change the name and to apply the renamed parameter value name.
Set the name to wind farm summary details.
Then navigate to the parameters tab to update our parameter of value to the renamed wind farm
name which is mapping to our question mark parameter in the query. Press the OK button.
Now we can verify our generated report by navigating to the preview tab; the preview
will not display any data because it is expecting a parameter value. Enter a random wind farm name
like Limon and press the view report button to view the report for a specific wind farm.
Our next goal is to configure the drill down report. First move back to the design view by
pressing the design tab. Next select the wind farm summary RDL report to configure a link to the sub
report. Since our generated report is expecting wind farm name parameter let's right-click on
the blue wind farm field and select the textbox properties item. We will configure the item to
open a sub report. In the open property window navigate to the action tab. Select the go to
report option and change the specify a report to the created wind farm summary details from
the drop-down list. Specify wind farm field as the value it should be used to set the
wind farm name parameter. Press the OK button. Now we could navigate again to the preview tab
to test the report but instead let's publish the report to the server and view it in the browser.
To publish the wind farm report right-click the PI SQL client SSRS solution and select
the deploy context menu item. At the bottom of the output window make sure that the solution
was built and published without any errors. Navigate to the deployment URL displayed in
the output window and select the PI SQL client SSRS directory to view its
content. Select the wind farm summary report. Navigate to a specific wind farm by selecting
any wind farm name. As a result we should be redirected to our wind farm summary details
which displays event frames with very light wind speed captured in the selected wind farm.
In this module you have created a query from scratch by leveraging the knowledge
gathered in the previous lessons. Finally, you learned how to create
a report using SQL Server Reporting Services utilizing PI SQL client.