In this video we're going to use Microsoft SQL server reporting services or SSRS to create a
report on the utilities usage on each piece of equipment around the NuGreen enterprise. We're
going to provide a name of a plant such as Houston or Wichita and the report is going to generate a
list of all the equipment and their utility usage for the last day both minimum and maximum as well
as average and total for utilities usage rate, as well as grouping them by the different types
of equipment such as boilers, compressors, heaters and pumps. Let's begin by opening up Visual Studio
and let's click on file, new, project. Under business intelligence templates we see reporting
services. If you do not have the business intelligence templates reporting services is
missing you'll need to install a SQL server data tools first. As mentioned in a previous video the
way to install this would be to find the Microsoft documentation such as this uninstalling SQL server
data tools which will lead you to the installation file for your version of Microsoft Visual Studio.
For convenience on these course VMs we have included the SSDT installation package and
the install kit folder. If we open this we can see SQL server reporting services. This
is already installed so it's already checked. Now we'll return to visual studio to create
the project and we'll click OK. I'll click Next and we'll provide the data source which
is our AF server. Our type will be OLEDB and click Edit to create the connection string.
For the provider we'll select PI SQL client so we'll be using the RTQP query that we've
created in a previous video I will put in NuGreen with a location of PSRV01 because
NuGreen is located on PSRV01. We will use integrated security and our initial catalog
will be guided project. Test the connection and click OK. For the query we'll use the
query that we generated in the first video of the guided project. To get that query
let's open up PI SQL commander. We'll connect to the NuGreen database under PI SQL client,
we'll expand guided projects SSRS, views and will execute the utility usage report.
In this output we have the total, average, minimum and maximum of the utilities usage
across each of the elements, there's also the plant they're located at and
what type of equipment it is such as boiler or heater. As we scroll through you can see all
the different plants are listed as well as all the different equipment types and the different
element names. Now since the NuGreen database uses all the default tags such as sinusoid,
all the values are the same but if you're doing this with the data at your plant each
value should be different. Let's grab this query and let's put it into the SQL server reporting
services configuration. Let's give it a top 100 even though there's only currently 91 elements
it still would be helpful if in the future more are added it won't get left off the report. I'll
click Next. And we'll use a tabular format. This is where we configure how all the data
appears within the table, so for equipment type that's how we wanted to have it grouped. Then
we'll have a group of boilers and a group of heaters and a group of compressors and pumps.
We'll select everything else except for plant and put it under details. This is because plant
is in the query so that we can slice it but we don't actually need to see the plant name
in a table. So I'll click Next and we'll click finish. Here we see the design in the report
has generated by the wizard. Clicking preview should generate the report itself with the data
from the OLEDB data source so let's do that now. Here we see that everything is grouped by boiler
and we have all of our boilers listed with all the data we were interested in and as we scroll
down we can see compressors, heaters and if we kept going we could see pumps, this is just a
preview. Now what we want to do is generate the report specifically just for one location. So
let's go back into the report data, data sets right click on data set 1 and go to properties.
On the query let's add a where clause, where where plant equals question mark. Now this parameter is
going to be filled in under the parameters tab and we'll give it a value of at plant name. This means
that we'll be prompted for a plant name when we generate the report and that name will take the
place of the parameter in our query filling in the where clause. So let's click OK and we see plant
name is added to our list of boxes. If we click on preview again will be prompted to fill in a plant
name, so we'll type in Houston and view report. Now we see a much shorter list because these are
only the boilers, compressors, heaters and pumps located in Houston. As we type in some other name
such as Wichita we'll see a different list. Great, now let's make the report a little bit nicer.
Go into design let's expand out some of these columns because some of these values are being
shifted to the next line. Let's see if there is enough space. We'll type in Houston and
view the report. Great now everything is spaced enough data stays on one line. Let's change the
report name so that it shows the plant that we've selected. Let's go to the tool box and
let's add an image. We'll name it OSIsoft logo and we'll import an image at the OSIsoft logo
that I grabbed from the Internet and click OK. Move the logo till we find a good place
for it. Let's see how this looks. Type in Wichita and click view report. Great now
we see that the value of Wichita which was from the plant name is now part of the title
and we see the OSIsof logo, if we type in a different value for plant name we can see a
change not only the different values that are being rendered but also the title of the report.
There's a lot of customization that can go into these reports using things in the toolbox as well
as some of the different font options that are on the top. Within each of the reports you have
different data sources, different data sets within the data sources, you can have multiple images,
you can have a lot of different parameters that you're passing. The SSRS reports are
highly customizable. For a complete list of all the options that you can use in an SSRS report
you should find the Microsoft documentation. The last step in this process is to deploy
this SSRS report to the website where users can generate their own reports outside of visual
studio. Before you begin you need to make sure that SQL server reporting services is installed
as part of your SQL server and the steps on how to do this can be found from Microsoft
documentation. For example this document for Microsoft explains step by step how to install
SSRS. This has already been done on the VMs used for this class so I'll jump ahead to that.
For installing SSRS on your own machines you'll need to follow documentation from Microsoft
or contact them for support. From here the deployment is straightforward by going to build,
deploy solution. When we deployed here we see an error message that says the target server version
is older than the version being deployed against. This is something to watch out for as the version
of SSDT that you're using may not match the version of SQL server reporting services that
you're deploying to. In this case let's open up a solution Explorer, right-click on the project
go to properties under target server version I'll select 2014. I'll click OK and I'll go
back to build, deploy solution. Here we see the URL that it's deploying the project to.
Now that it's finished we'll go to that location. Here we see all of our deployed reports and I'll
click on the one we just created. I'll click on report and here it's prompting us for a plant
name. Type in Houston and click view report. And here we see all the values that we had
generated for the utility minimum, maximum, average and total across all the boilers,
compressors, heaters and pumps in Houston location as grouped by their type. And if we
type in Wichita for example, so we generated the report as well. From here we can do things like
printing and saving or just send a link to any person that we would like to show the report to.
This concludes the video on how to build and deploy SSRS reports that showcase PI
System data obtained through the PI SQL client and RTQP engine.