UFN_KPIINSTANCEGOAL_GETIMAGEKEY

Returns the appropriate image key for a given KPI value based on its target values.

Return

Return Type
nvarchar(15)

Parameters

Parameter	Parameter Type	Mode
@VALUE	decimal(20, 5)	IN
@GOALAIMTYPECODE	tinyint	IN
@GOALUPWARDLOWVALUE	decimal(20, 5)	IN
@GOALUPWARDMIDVALUE	decimal(20, 5)	IN
@GOALDOWNWARDMIDVALUE	decimal(20, 5)	IN
@GOALDOWNWARDHIGHVALUE	decimal(20, 5)	IN

Definition

Copy



CREATE function dbo.UFN_KPIINSTANCEGOAL_GETIMAGEKEY(
  @VALUE decimal(20,5),
  @GOALAIMTYPECODE tinyint,
  @GOALUPWARDLOWVALUE decimal(20,5),
  @GOALUPWARDMIDVALUE decimal(20,5),
  @GOALDOWNWARDMIDVALUE decimal(20,5), 
  @GOALDOWNWARDHIGHVALUE decimal(20,5)  
  )
returns nvarchar(15)
with execute as caller
as begin

declare @IMAGENAME nvarchar(15);

  if @VALUE is null 
    set @IMAGENAME = 'RES:roadblank'

    if @GOALAIMTYPECODE = 0        
    begin
      if @VALUE < dbo.UFN_KPIINSTANCEGOAL_GETYELLOWTARGET(@GOALAIMTYPECODE, @GOALUPWARDLOWVALUE, @GOALUPWARDMIDVALUE, @GOALDOWNWARDMIDVALUE, @GOALDOWNWARDHIGHVALUE)
          set @IMAGENAME = 'RES:road0';
      else
        begin
                  if @VALUE < dbo.UFN_KPIINSTANCEGOAL_GETGREENTARGET(@GOALAIMTYPECODE, @GOALUPWARDLOWVALUE, @GOALUPWARDMIDVALUE, @GOALDOWNWARDMIDVALUE, @GOALDOWNWARDHIGHVALUE)
            set @IMAGENAME = 'RES:road1';
          else
                    set @IMAGENAME = 'RES:road2';
        end
    end
  else
    begin
    if @VALUE > dbo.UFN_KPIINSTANCEGOAL_GETYELLOWTARGET(@GOALAIMTYPECODE, @GOALUPWARDLOWVALUE, @GOALUPWARDMIDVALUE, @GOALDOWNWARDMIDVALUE, @GOALDOWNWARDHIGHVALUE)
      set @IMAGENAME = 'RES:road0';
    else
      begin
            if @VALUE > dbo.UFN_KPIINSTANCEGOAL_GETGREENTARGET(@GOALAIMTYPECODE, @GOALUPWARDLOWVALUE, @GOALUPWARDMIDVALUE, @GOALDOWNWARDMIDVALUE, @GOALDOWNWARDHIGHVALUE)
        set @IMAGENAME = 'RES:road1';
      else
              set @IMAGENAME = 'RES:road2';
            end
    end
        return @IMAGENAME;
end