Display Reports in millions and Lakhs

I want to display the financial reports in millions and Lakhs instead of rupees.

How it could be possible.

SAP Managed Tags:
SAP Business One

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (0)

Answers (1)

Not possible to display in B1 Screens.

But possible in PLD. Check this thread

[;

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hi Sridharan,

I want to display the currency itself in millions or Lakhs.

Ex:

Assets Amount(in Lakhs)

Fixed Assets 10

Thanks

Not possible.

Is there any work around procedure or can we do using sdk.

Post this question to SDK Forum so that SDK experts can comment on this.

Thank You

I think using FMS, you can pass the value into a SP which generates the LAKHS amount and show it up on PLD.

This might save you SDK !

Hi,sandy disouza.

First Create 2 funtions in SQL server: and apply trigger for system filed you can get data automatically amount in words when ever user is adding Outgoing payment.

1.set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

-- select dbo.AmountToWords (4980)

ALTER function [dbo].[AmountToWords]

(

@InNumber Numeric(18,2)

)

--Returns the number as words.

returns VARCHAR(2000)

BEGIN

--SEt NoCount ON

Declare @Num Varchar(20)

Declare @Dec Varchar(3)

Declare @Return Varchar(2000)

Set @Dec = SubString(Convert(Varchar(20),@Innumber),Len(Convert(Varchar(20),@Innumber))-2,3)

Set @Num = SubString(Convert(Varchar(20),@Innumber),1, Len(Convert(Varchar(20),@Innumber))-3)

Declare @Hundred Char(8)

Declare @HundredAnd Char(12)

Declare @Thousand Char(9)

Declare @Lakh Char(5)

Declare @Lakhs Char(6)

Declare @Crore Char(6)

Declare @Crores Char(7)

Set @Hundred = 'Hundred '

Set @Thousand = 'Thousand '

Set @Lakh = 'Lakh '

Set @Lakhs = 'Lakhs '

Set @Crore = 'Crore '

Set @Crores = 'Crores '

Set @HundredAnd = 'Hundred '

if Len(@Num) = 1 -- One

Begin

Set @Return = dbo.GetTextValue(@Num)

End

Else if Len(@Num) = 2 -- Ten

Begin

Set @Return = dbo.GetTextValue(@Num)

End

Else if Len(@Num) = 3 -- Hundred

Begin

Set @Return = dbo.GetTextValue(SubString(@Num,1,1)) + @Hundred

IF SubString(@num,2,2) <> '00'

Set @Return = @Return + ' '

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,2,2))

End

Else if Len(@Num) = 4 -- thousand

Begin

Set @Return = dbo.GetTextValue(SubString(@Num,1,1)) + @Thousand

If SubString(@Num,2,1) <> '0'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,2,1)) + @Hundred

IF SubString(@num,3,2) <> '00'

Set @Return = @Return + ''

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,3,2))

End

Else if Len(@Num) = 5 -- Ten Thousand

Begin

Set @Return = dbo.GetTextValue(SubString(@Num,1,2)) + @Thousand

If SubString(@Num,3,1) <> '0'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,3,1)) + @Hundred

IF SubString(@num,4,2) <> '00'

Set @Return = @Return + ''

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,4,2))

End

Else if Len(@Num) = 6 -- Lakh

Begin

If SubString(@Num,1,1) = '1'

Set @Return = dbo.GetTextValue(SubString(@Num,1,1)) + @Lakh

Else

Set @Return = dbo.GetTextValue(SubString(@Num,1,1)) + @Lakhs

If SubString(@Num,2,2) <> '00'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,2,2)) + @Thousand

If SubString(@Num,4,1) <> '0'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,4,1)) + @Hundred

IF SubString(@num,5,2) <> '00'

Set @Return = @Return + ''

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,5,2))

End

Else if Len(@Num) = 7 -- Ten Lakhs

Begin

Set @Return = dbo.GetTextValue(SubString(@Num,1,2)) + @Lakhs

If SubString(@Num,3,2) <> '00'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,3,2)) + @Thousand

If SubString(@Num,6,1) <> '0'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,5,1)) + @Hundred

IF SubString(@num,6,2) <> '00'

Set @Return = @Return + ''

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,6,2))

End

Else if Len(@Num) = 8 -- Crore

Begin

Set @Return = dbo.GetTextValue(SubString(@Num,1,1)) + @Crore

If SubString(@Num,2,2) <> '00'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,2,2)) + @Lakhs

If SubString(@Num,4,2) <> '00'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,4,2)) + @Thousand

If SubString(@Num,6,1) <> '0'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,6,1)) + @Hundred

IF SubString(@num,7,2) <> '00'

Set @Return = @Return + ' '

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,7,2))

End

Else if Len(@Num) = 9 -- Ten Crore

Begin

Set @Return = dbo.GetTextValue(SubString(@Num,1,2)) + @Crores

If SubString(@Num,3,2) <> '00'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,3,2)) + @Lakhs

If SubString(@Num,5,2) <> '00'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,5,2)) + @Thousand

If SubString(@Num,7,1) <> '0'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,7,1)) + @Hundred

IF SubString(@num,8,2) <> '00'

Set @Return = @Return + ''

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,8,2))

End

Else if Len(@Num) = 10 -- Hundred Crore

Begin

Set @Return = dbo.GetTextValue(Substring(@Num,1,1)) + @Hundred

IF Substring(@Num,2,2) <> '00'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,2,2))

Set @Return = @Return + @Crores

If SubString(@Num,4,2) <> '00'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,4,2)) + @Lakhs

If SubString(@Num,6,2) <> '00'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,6,2)) + @Thousand

If SubString(@Num,8,1) <> '0'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,8,1)) + @Hundred

IF SubString(@num,9,2) <> '00'

Set @Return = @Return + ''

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,9,2))

End

Else if Len(@Num) = 11 -- Thousand Crore

Begin

Set @Return = dbo.GetTextValue(Substring(@Num,1,1)) + @Thousand

IF SubString(@Num,2,1) <> '0'

Set @Return = @Return + dbo.GetTextValue(Substring(@Num,2,1)) + @Hundred

IF Substring(@Num,3,2) <> '00'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,3,2))

Set @Return = @Return + @Crores

If SubString(@Num,5,2) <> '00'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,5,2)) + @Lakhs

If SubString(@Num,7,2) <> '00'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,7,2)) + @Thousand

If SubString(@Num,9,1) <> '0'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,9,1)) + @Hundred

IF SubString(@num,10,2) <> '00'

Set @Return = @Return + ''

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,10,2))

End

Else if Len(@Num) = 12 -- Ten thousand Crore

Begin

Set @Return = dbo.GetTextValue(Substring(@Num,1,2)) + @Thousand

IF SubString(@Num,3,1) <> '0'

Set @Return = @Return + dbo.GetTextValue(Substring(@Num,3,1)) + @Hundred

IF Substring(@Num,4,2) <> '00'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,4,2))

Set @Return = @Return + @Crores

If SubString(@Num,6,2) <> '00'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,6,2)) + @Lakhs

If SubString(@Num,8,2) <> '00'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,8,2)) + @Thousand

If SubString(@Num,10,1) <> '0'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,10,1)) + @Hundred

IF SubString(@num,11,2) <> '00'

Set @Return = @Return + ''

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,11,2))

End

Else if Len(@Num) = 13 -- Lakh Crore

Begin

Set @Return = dbo.GetTextValue(Substring(@Num,1,1)) + @Lakh

If Substring(@Num,2,2) <> '00'

Set @Return = @Return + dbo.GetTextValue(Substring(@Num,2,2)) + @Thousand

IF SubString(@Num,4,1) <> '0'

Set @Return = @Return + dbo.GetTextValue(Substring(@Num,4,1)) + @Hundred

IF Substring(@Num,5,2) <> '00'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,5,2))

Set @Return = @Return + @Crores

If SubString(@Num,7,2) <> '00'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,7,2)) + @Lakhs

If SubString(@Num,9,2) <> '00'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,9,2)) + @Thousand

If SubString(@Num,11,1) <> '0'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,11,1)) + @Hundred

IF SubString(@num,12,2) <> '00'

Set @Return = @Return + ''

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,12,2))

End

Else if Len(@Num) = 14 -- Ten Lakh Crore

Begin

Set @Return = dbo.GetTextValue(Substring(@Num,1,2)) + @Lakhs

If Substring(@Num,3,2) <> '00'

Set @Return = @Return + dbo.GetTextValue(Substring(@Num,3,2)) + @Thousand

IF SubString(@Num,5,1) <> '0'

Set @Return = @Return + dbo.GetTextValue(Substring(@Num,5,1)) + @Hundred

IF Substring(@Num,6,2) <> '00'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,6,2))

Set @Return = @Return + @Crores

If SubString(@Num,8,2) <> '00'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,8,2)) + @Lakhs

If SubString(@Num,10,2) <> '00'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,10,2)) + @Thousand

If SubString(@Num,12,1) <> '0'

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,12,1)) + @Hundred

IF SubString(@num,13,2) <> '00'

Set @Return = @Return + ''

Set @Return = @Return + dbo.GetTextValue(SubString(@Num,13,2))

End

If @Dec <> '.00'

Set @Return = @Return + ' And ' + dbo.GetTextValue(SubString(@Dec,2,2)) + 'Paise'

Return @return

END

2.This is for Get texl vavlue funtion:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER function [dbo].[GetTextValue]

(

@num varchar(20)

)

returns VARCHAR(2000)

BEGIN

Declare @Return varchar(2000)

Declare @temp char(1)

Declare @tempZero char(1)

Declare @Zero char(1)

Declare @One char(4)

Declare @Two char(4)

Declare @Three char(6)

Declare @Four char(5)

Declare @Five char(5)

Declare @Six char(4)

Declare @Seven char(6)

Declare @Eight char(6)

Declare @Nine char(5)

Declare @Eleven char(7)

Declare @Twelve char(7)

Declare @Thirteen char(9)

Declare @Fourteen char(9)

Declare @Fifteen char(8)

Declare @Sixteen char(8)

Declare @Seventeen char(10)

Declare @Eighteen char(9)

Declare @Nineteen char(9)

Declare @Ten char(4)

Declare @Twenty char(6)

Declare @Thirty char(7)

Declare @Forty char(6)

Declare @Fifty char(6)

Declare @Sixty char(6)

Declare @Seventy char(8)

Declare @Eighty char(7)

Declare @Ninety char(7)

set @tempZero = '0'

set @Zero = ''

set @One = 'One '

set @Two = 'Two '

set @Three = 'Three '

set @Four = 'Four '

set @Five = 'Five '

set @Six = 'Six '

set @Seven = 'Seven '

set @Eight = 'Eight '

set @Nine = 'Nine '

set @Eleven = 'Eleven '

set @Twelve = 'Twelve '

set @Thirteen = 'Thirteen '

set @Fourteen = 'Fourteen '

set @Fifteen = 'Fifteen '

set @Sixteen = 'Sixteen '

set @Seventeen = 'Seventeen '

set @Eighteen = 'Eighteen '

set @Nineteen = 'Nineteen '

set @Ten = 'Ten '

set @Twenty = 'Twenty '

set @Thirty = 'Thirty '

set @Forty = 'Forty '

set @Fifty = 'Fifty '

set @Sixty = 'Sixty '

set @Seventy = 'Seventy '

set @Eighty = 'Eighty '

set @Ninety = 'Ninety '

IF Len(@num) = 2

Begin

If SubString(@num,2,1) <> '0'

Begin

Set @temp = SubString(@num,1,1)

Set @Return = dbo.GetTextValue(@temp+@tempZero)

Set @Return = @Return + dbo.GetTextValue(SubString(@num,2,1))

End

END

If @num = 0

Begin

set @Return = @Zero

End

Else if @num = 1

Begin

set @Return = @One

End

Else if @num = 2

Begin

set @Return = @Two

End

Else if @num = 3

Begin

set @Return = @Three

End

Else if @num = 4

Begin

set @Return = @Four

End

Else if @num = 5

Begin

set @Return = @Five

End

Else if @num = 6

Begin

set @Return = @Six

End

Else if @num = 7

Begin

set @Return = @Seven

End

Else if @num = 8

Begin

set @Return = @Eight

End

Else if @num = 9

Begin

set @Return = @Nine

End

Else if @num = 10

Begin

set @Return = @Ten

End

Else if @num = 11

Begin

set @Return = @Eleven

End

Else if @num = 12

Begin

set @Return = @Twelve

End

Else if @num = 13

Begin

set @Return = @Thirteen

End

Else if @num = 14

Begin

set @Return = @Fourteen

End

Else if @num = 15

Begin

set @Return = @Fifteen

End

Else if @num = 16

Begin

set @Return = @Sixteen

End

Else if @num = 17

Begin

set @Return = @Seventeen

End

Else if @num = 18

Begin

set @Return = @Eighteen

End

Else if @num = 19

Begin

set @Return = @Nineteen

End

Else if @num = 20

Begin

set @Return = @Twenty

End

Else if @num = 30

Begin

set @Return = @Thirty

End

Else if @num = 40

Begin

set @Return = @Forty

End

Else if @num = 50

Begin

set @Return = @Fifty

End

Else if @num = 60

Begin

set @Return = @Sixty

End

Else if @num = 70

Begin

set @Return = @Seventy

End

Else if @num = 80

Begin

set @Return = @Eighty

End

Else if @num = 90

Begin

set @Return = @Ninety

End

Return @Return

END

3. Create trigger for OCHO table:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER TRIGGER [AmountToWordsTrigger]

ON [dbo].[OCHO]

AFTER INSERT,UPDATE

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

UPDATE [dbo].[OCHO]

SET TotalWords = [dbo].[AmountToWords] (LinesSum)+'Only'

WHERE CheckKey IN ( SELECT CheckKey From Inserted)

END

it will update exisitng filed i.e.,Amount in words. call this field in your PLD.

Regds,

sampath kumar devunuri..

Ask a Question

Top Q&A Solution Author

User

Count

107