Data sources

SQL Stored Procedures provide the data used by a tile, and in order for them to be detected by the tile configurator, the stored procedures must be named appropriately:

  • BPDS_I: Data sources with a naming prefix of BPDS_I are reserved to represent internal data sources that are built-in to the software.
  • BPDS: Data sources with a naming prefix of BPDS are reserved to represent SQL Server stored procedures provided with the software.
    • Custom stored procedures must not be named with this prefix and the existing stored procedures must not be modified in anyway except if advised by Blue Prism.
    • Blue Prism will not recognise stored procedures that are prefixed BPDS unless they have been created by Blue Prism.
    • Any manual changes to these procedures may be subsequently overwritten by a future product update. If any custom changes are required to these stored procedures, create a copy of the procedure with the appropriate prefix for a custom data source.
  • DS: The prefix of DS is the required prefix for any custom stored procedures that are created for dashboard tiles.

It is possible to parameterise these procedures, enabling them to be associated with multiple chart tiles, each specifying a different set of parameters – for example to provide the same view for different groups of queues.

Create a custom data source

Where a new stored procedure is manually created in the database, it is necessary to ensure that Blue Prism users can use it. This can be achieved by extending the Blue Prism SQL Security Role named bpa_ExecuteSP_DataSource_custom.

GRANT EXECUTE ON OBJECT::[Stored Procedure Name] TO bpa_ExecuteSP_DataSource_custom;

e.g.

GRANT EXECUTE ON OBJECT::dbo.DS_DailyProductivity TO bpa_ExecuteSP_DataSource_custom;

Alternatively exporting the Tile from one Blue Prism environment and importing it into another, as this will extend the security role in the target environment automatically.

Tile data format

The data used by tiles is derived from a stored procedure which must exist in the Blue Prism database. The stored procedure must return a data table the stored procedure used for a chart tile to return data in a table. The first column in the returned data represents the chart labels and subsequent columns represent 1 – n series of data.

For example a procedure to return queue volumes by status might be created as:

Copy 
with results as (
    select a.name as Queue, b.state, COUNT(*) as number
    from BPAWorkQueue a inner join BPVWorkQueueItem b on b.queueid=a.id
    group by a.name, b.state)
    
select Queue, [1] as Pending, [3] as Deferred, [4] as Completed
from results pivot (sum(number) for state in ([1], [3], [4])) as number;

When executed by Blue Prism, this would return the following:

Example datasource output

Before any new stored procedure is used within a Production environment the performance of the query should be tested on a realistic data set to avoid any system performance problems.