Building a report showing graphical stats from a SharePoint Issues List
Building a report showing graphical stats from a SharePoint Issues List
In this sample we will cover the basic tasks of building a report presenting graphical statistics from a SharePoint issues list.

Issues list
We will be using a classic SharePoint issues list.

We have just added a few fields (Component, Version and Severity).


In our example, the issues list is centered around software issues/bugs but we could use a similar approach with issues related to help desk.

Graphical Statistics
The idea is to build a report that will display graphical statistics about the issues like shown in the following image :

Report Server project
Open Visual Studio and create a Report Server project or open an existing one.

Adding a shared Data Source
Although it's not really necessary to add a shared Data Source for the sake of building one report, it's usually a good idea if you will be creating several reports within the same project.
Using a shared Data Source you will be able to use the same data source for every report's dataset. In case you need to change the SharePoint server, you just have to modify the shared data source in one place.
To add a new Shared Data Source, right click on "Shared Data Sources" in the solution explorer window and select "Add New Data Source" as shown in the following image:

In the Shared Data Source dialog box, name the data source. A logic choice is the name of your SharePoint server (Sps or Wss).
Set the data source type to "Enesys SharePoint Lists".
Set the connection string to "server=<url of your sharepoint server>"




Click on the Credentials tab and select "Use Windows Authentication" as shown in the following image:
Shared Data Source Credentials


Adding a new report
Right click in the solution explorer area and select "Add - New item...".
Select a Report template as shown in the following image, name your report IssuesGraphicalStats (of course the report's name could be whatever you want) and click OK to create the report.


Adding a Dataset
We'll now create a Dataset in order to collect the issues list items.
Click on the Dataset dropdown list and select <New Dataset...>.
The various fields should be set as explained in the following table:

Field
Description
Name
A name given to the Dataset. "Issues" fits perfectly.
Data source
The data source that will be used by the dataset to collect data. We will select the shared Data Source that we have created earlier.
Command typeShould always be set to text for using Enesys SharePoint Lists data sources. 
Query string
You can leave this field empty and enter the query string later on in the report's data panel.


Dataset Dialog box


After setting the fields like shown in the previous image, click OK to create the dataset.

Adding a query string
In order to collect the SharePoint issue list items, we need to write a specific Query string. Please consult the documentation for more information about writing query strings for Enesys RS Data Extension.
The data panel let's you design queries for datasets defined within the reports.

Write the following query in the data panel:

<?xml version="1.0" encoding="utf-8" ?>
<root>
    <list title="Issues" relativeSiteUrl="/sites/demo/" tableName="results">
      <fields>ID, Title,AssignedTo, Component, Status, Version, Category, Severity, Priority</fields>
       <query>
         <Where>
           <Eq>
              <FieldRef Name="IsCurrent" />
              <Value Type="Boolean">1</Value>
           </Eq>
         </Where>
       </query>
    </list>
</
root>

You should replace the title and relativeSiteUrl attributes values so that it points to an existing list on your SharePoint server. Note that a template is provided for this list when you download the sample files.

The query is rather simple. We are specifying the fields needed withing the <fields> element. It would have been also possible to leave the <fields> element empty to retrieve all the fields from the SharePoint list.
It's interesting to note though that an issue may have several list items associated with it in order to maintain the issue history.  As we are only interested with the current situation of each issues we have added a CAML Query in order to retrieve the items that represent the current situation of each issue.
IsCurrent is a field that is automatically maintained by the SharePoint list when one edit an issue to change it's status or anything else.

Click the run icon Run query icon to test the query.
The issues should be retrieved from the SharePoint list as shown in the following image.


Designing the layout
Now that we have a dataset, it's time do design the layout.
Select the Report's Layout panel.
Open the Toolbox and drag a chart item on the body section of the report.
Report Designer Toolbox


By default a simple column chart type is displayed. Change the chart type to simple pie by right clicking on the graph.

If you click twice on the chart you can see three areas used for dropping data, category and series fields. Those areas will also be automatically displayed when you drag a field over the chart.
Drag the Category field from the Issues Dataset and drop it on the series area.
Drag the ID field and drop it on the data area. Note that the report designer is smart enough to understand that you want to cound the ID occurences and provides the correct expression automatically : Count(Fields!ID.Value).

You should have a layout similar as what is shown in the following image:

Though that's all there needs to be for having a functioning report, the result is not yet very appealing. We will now make some cosmetics change to the chart.

Setting a title for the chart
Right click on the chart and select Properties to open the Chart properties dialog box.
Set the title as shown in the following image :


Showing point labels
select the Data tab in the Chart properties dialog box,
click the Edit... button next to the values, the Edit Chart Value dialog box will be displayed,
select the Point Labels tab
Check the Show point labels check box,
Enter "=Count(Fields!ID.Value)" in the Data label field for displaying issues count on the chart,
Show Point Labels
Optionally set the font weight to bold by clicking on Label style...
Click OK.

Showing legends below the chart
select the Legend tab in the Chart properties dialog box,
select a Table layout,
set the new position by clicking the bottom middle square

Changing borders and background colors
On the Chart Properties dialog box, click on the Chart Area Style... button,
set the border style to "Solid",
select the Fill tab
set the color to WhiteSmoke an click OK
Click on the Plot Area Style... button
set the border style to none,
select the Fill tab
set the color to WhiteSmoke and click OK

After those various modifications, you should have a layout similar as the following one :

Adding the other charts
The three other charts we would like to add to the report are very similar. Rather than adding three charts and repeating the stylish modifications we have already made, we will copy the existing chart and paste it three times into the body section of the report layout.
For each chart that has been copied, you should modify the field series by deleting the Category field and dropping respectively Status, Assignement and  Priority fields from the Issues Dataset. You should also modify the title accordingly.
When you are done, your layout should somewhat look like the one presented at the beginning of this article.
Select the preview panel so that the charts are displayed with the real data.

The report is now ready to be deployed on the report server.

Downloading the sample files
The sample files are available for download.

Conclusion
This article was aimed to demonstrate how you can easyly add some value to your SharePoint data by using Enesys RS Data Extension and Microsoft SQL Reporting Services.

Building a report showing graphical stats from a SharePoint Issues List
In this sample we will cover the basic tasks of building a report presenting graphical statistics from a SharePoint issues list.

Issues list
We will be using a classic SharePoint issues list.

We have just added a few fields (Component, Version and Severity).


In our example, the issues list is centered around software issues/bugs but we could use a similar approach with issues related to help desk.

Graphical Statistics
The idea is to build a report that will display graphical statistics about the issues like shown in the following image :

Report Server project
Open Visual Studio and create a Report Server project or open an existing one.

Adding a shared Data Source
Although it's not really necessary to add a shared Data Source for the sake of building one report, it's usually a good idea if you will be creating several reports within the same project.
Using a shared Data Source you will be able to use the same data source for every report's dataset. In case you need to change the SharePoint server, you just have to modify the shared data source in one place.
To add a new Shared Data Source, right click on "Shared Data Sources" in the solution explorer window and select "Add New Data Source" as shown in the following image:

In the Shared Data Source dialog box, name the data source. A logic choice is the name of your SharePoint server (Sps or Wss).
Set the data source type to "Enesys SharePoint Lists".
Set the connection string to "server=<url of your sharepoint server>"




Click on the Credentials tab and select "Use Windows Authentication" as shown in the following image:
Shared Data Source Credentials


Adding a new report
Right click in the solution explorer area and select "Add - New item...".
Select a Report template as shown in the following image, name your report IssuesGraphicalStats (of course the report's name could be whatever you want) and click OK to create the report.


Adding a Dataset
We'll now create a Dataset in order to collect the issues list items.
Click on the Dataset dropdown list and select <New Dataset...>.
The various fields should be set as explained in the following table:

Field
Description
Name
A name given to the Dataset. "Issues" fits perfectly.
Data source
The data source that will be used by the dataset to collect data. We will select the shared Data Source that we have created earlier.
Command typeShould always be set to text for using Enesys SharePoint Lists data sources. 
Query string
You can leave this field empty and enter the query string later on in the report's data panel.


Dataset Dialog box


After setting the fields like shown in the previous image, click OK to create the dataset.

Adding a query string
In order to collect the SharePoint issue list items, we need to write a specific Query string. Please consult the documentation for more information about writing query strings for Enesys RS Data Extension.
The data panel let's you design queries for datasets defined within the reports.

Write the following query in the data panel:

<?xml version="1.0" encoding="utf-8" ?>
<root>
    <list title="Issues" relativeSiteUrl="/sites/demo/" tableName="results">
      <fields>ID, Title,AssignedTo, Component, Status, Version, Category, Severity, Priority</fields>
       <query>
         <Where>
           <Eq>
              <FieldRef Name="IsCurrent" />
              <Value Type="Boolean">1</Value>
           </Eq>
         </Where>
       </query>
    </list>
</
root>

You should replace the title and relativeSiteUrl attributes values so that it points to an existing list on your SharePoint server. Note that a template is provided for this list when you download the sample files.

The query is rather simple. We are specifying the fields needed withing the <fields> element. It would have been also possible to leave the <fields> element empty to retrieve all the fields from the SharePoint list.
It's interesting to note though that an issue may have several list items associated with it in order to maintain the issue history.  As we are only interested with the current situation of each issues we have added a CAML Query in order to retrieve the items that represent the current situation of each issue.
IsCurrent is a field that is automatically maintained by the SharePoint list when one edit an issue to change it's status or anything else.

Click the run icon Run query icon to test the query.
The issues should be retrieved from the SharePoint list as shown in the following image.


Designing the layout
Now that we have a dataset, it's time do design the layout.
Select the Report's Layout panel.
Open the Toolbox and drag a chart item on the body section of the report.
Report Designer Toolbox


By default a simple column chart type is displayed. Change the chart type to simple pie by right clicking on the graph.

If you click twice on the chart you can see three areas used for dropping data, category and series fields. Those areas will also be automatically displayed when you drag a field over the chart.
Drag the Category field from the Issues Dataset and drop it on the series area.
Drag the ID field and drop it on the data area. Note that the report designer is smart enough to understand that you want to cound the ID occurences and provides the correct expression automatically : Count(Fields!ID.Value).

You should have a layout similar as what is shown in the following image:

Though that's all there needs to be for having a functioning report, the result is not yet very appealing. We will now make some cosmetics change to the chart.

Setting a title for the chart
Right click on the chart and select Properties to open the Chart properties dialog box.
Set the title as shown in the following image :


Showing point labels
select the Data tab in the Chart properties dialog box,
click the Edit... button next to the values, the Edit Chart Value dialog box will be displayed,
select the Point Labels tab
Check the Show point labels check box,
Enter "=Count(Fields!ID.Value)" in the Data label field for displaying issues count on the chart,
Show Point Labels
Optionally set the font weight to bold by clicking on Label style...
Click OK.

Showing legends below the chart
select the Legend tab in the Chart properties dialog box,
select a Table layout,
set the new position by clicking the bottom middle square

Changing borders and background colors
On the Chart Properties dialog box, click on the Chart Area Style... button,
set the border style to "Solid",
select the Fill tab
set the color to WhiteSmoke an click OK
Click on the Plot Area Style... button
set the border style to none,
select the Fill tab
set the color to WhiteSmoke and click OK

After those various modifications, you should have a layout similar as the following one :

Adding the other charts
The three other charts we would like to add to the report are very similar. Rather than adding three charts and repeating the stylish modifications we have already made, we will copy the existing chart and paste it three times into the body section of the report layout.
For each chart that has been copied, you should modify the field series by deleting the Category field and dropping respectively Status, Assignement and  Priority fields from the Issues Dataset. You should also modify the title accordingly.
When you are done, your layout should somewhat look like the one presented at the beginning of this article.
Select the preview panel so that the charts are displayed with the real data.

The report is now ready to be deployed on the report server.

Downloading the sample files
The sample files are available for download.

Conclusion
This article was aimed to demonstrate how you can easyly add some value to your SharePoint data by using Enesys RS Data Extension and Microsoft SQL Reporting Services.