Skip to Content
0
Former Member
Mar 31, 2014 at 07:01 AM

Replace KPI Default "INR" to "Lacs" for Subvalue1 & Subvalue 2

74 Views

Hi,

We wrote a query for KPI as per given below: On Key Performance Indicataor Page Value is Coming In INR whereas we want to replace INR With Lacs.

Query :

DECLARE @Today DATETIME, @FirstDayOfLastMonth DATETIME, @FirstDayOfThisMonth DATETIME, @FirstDayOfNextMonth DATETIME

SELECT @Today = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

SELECT @FirstDayOfLastMonth = DATEADD(m,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()), 0))

SELECT @FirstDayOfThisMonth = DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,0,@Today))

SELECT @FirstDayOfNextMonth = DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,1,@Today))

DECLARE @LastMonth FLOAT, @ThisMonth FLOAT, @Gauge FLOAT, @Indicator VARCHAR(1)

SELECT @LastMonth = ((select SUM(Quantity) FROM INV1 a, oinv b,OCRD c WHERE a.docentry=b.docentry and b.DocDate>=@FirstDayOfLastMonth

and b.DocDate<@FirstDayOfThisMonth and canceled='N' and c.CardCode=b.CardCode and c.GroupCode=10 and WhsCode in ('FG-A-PR','FG-A-UP','FG-HF-PR','FG-HF-UP'))

-

(isnull((select SUM(Quantity) FROM RIN1 a , orin b ,OCRD c WHERE a.docentry=b.docentry and b.DocDate>=@FirstDayOfLastMonth

and b.DocDate<@FirstDayOfThisMonth and canceled='N' and c.CardCode=b.CardCode and c.GroupCode=10

and WhsCode in ('FG-A-PR','FG-A-UP','FG-HF-PR','FG-HF-UP')),0)))/100

SELECT @ThisMonth =( (select SUM(quantity)

FROM INV1 a, oinv b,OCRD c WHERE a.docentry=b.docentry and b.DocDate>=@FirstDayOfThisMonth and b.DocDate<@FirstDayOfNextMonth

and canceled='N' and c.CardCode=b.CardCode and c.GroupCode=10 )-

ISNULL( (select SUM(quantity)

FROM rin1 a, orin b,OCRD c WHERE a.docentry=b.docentry and b.DocDate>=@FirstDayOfThisMonth and b.DocDate<@FirstDayOfNextMonth

and canceled='N' and c.CardCode=b.CardCode and c.GroupCode=10 ),0))/100

IF @LastMonth IS NULL SET @LastMonth = 0 IF @ThisMonth IS NULL

SET @ThisMonth = 0 IF @LastMonth > 0 SET @Gauge = (@ThisMonth-@LastMonth)/@LastMonth * 100 ELSE IF @ThisMonth > 0

SET @Gauge = 100 ELSE SET @Gauge = 0 SET @Indicator = 'P'

SELECT 'Export Dispatch' AS Title, CAST(CAST(@Gauge AS DECIMAL(20,2)) AS VARCHAR(20)) +'%' AS Gauge,

@Indicator AS Indicator, 'This Month' AS SubTitle1, CAST(CAST(@ThisMonth AS DECIMAL(20,2)) AS VARCHAR(20)) AS SubValue1,

'Last Month' AS SubTitle2, CAST(CAST(@LastMonth AS DECIMAL(20,2)) AS VARCHAR(20)) AS SubValue2

Help Required to Replace INR to Lacs