Hello,
I have an issue with one Tile, it has one small modifitation from the DailyUtilisation Tile from BluePrism, the idea of the SP is to show only the totals of the range of days.
But for some reasons is working in the managment studio, but when I try to use in BluePrism in the tile is not working is not showing anything and I test with the same values.
Please if anyone have a similar error, I really appreciate any help or guidance.
Here is the script:
ALTER procedure [dbo].[DS_TotalUtilizationByResource]
@BPResourceName nvarchar(max) = null,
@NumberOfDays int = 7,
@DisplayUnits nvarchar(max) = 'minute',
@MaxResourceHours int = 24
as
if @NumberOfDays < 1 or @NumberOfDays > 31
raiserror('@NumberOfDays must be between 1 and 31', 11, 1);
else if @DisplayUnits not in ('second', 'minute', 'hour', 'percentage')
raiserror('@DisplayUnits must be second, minute, hour or percentage', 11, 1);
else if @MaxResourceHours < 1 or @MaxResourceHours > 24
raiserror('@MaxResourceHours must be between 1 and 24', 11, 1);
else
select
case
when @DisplayUnits 'percentage' then SUM(a.Total)
else CAST(SUM(a.Total*60)/(36*@NumberOfDays*1*@MaxResourceHours) as decimal (12,2)) end as "Total"
from(
select
DATENAME(day, u.TheDate) + '-' + DATENAME(month, u.TheDate) as "Day",
case when @DisplayUnits = 'second' then CAST(Total as decimal(12,2))
when @DisplayUnits = 'hour' then CAST(Total/3600 as decimal(12,2))
--when @DisplayUnits = 'percentage' and Resources > 0 then CAST(Total/(36*Resources*@MaxResourceHours) as decimal(12,2))
else CAST(Total/60 as decimal(12,2)) end as "Total"
from (
select
dates.TheDate,
CAST(ISNULL(SUM(d.hr0 + d.hr1 + d.hr2 + d.hr3 + d.hr4 + d.hr5 + d.hr6 + d.hr7 +
d.hr8 + d.hr9 + d.hr10 + d.hr11 + d.hr12 + d.hr13 + d.hr14 + d.hr15 +
d.hr16 + d.hr17 + d.hr18 + d.hr19 + d.hr20 + d.hr21 + d.hr22 + d.hr23), 0) as float)
as "Total",
COUNT(distinct(d.resourceid)) as "Resources"
from ufn_GetReportDays(@NumberOfDays) dates
left join BPMIUtilisationDaily d on d.reportdate = dates.TheDate
left join BPAResource r on d.resourceid = r.resourceid
where @BPResourceName is null or @BPResourceName = r.name
group by dates.TheDate
) as u
)as a;
return;
