Skip to Content
author's profile photo Former Member
Former Member

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Dec 19, 2008 at 01:06 PM

    Not possible to display in B1 Screens.

    But possible in PLD. Check this thread

    [How to convet Amount To Words in PLD;

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      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

      go

      -- select dbo.AmountToWords (4980)

      ALTER function [dbo].[AmountToWords]

      (

      @InNumber Numeric(18,2)

      )

      --Returns the number as words.

      returns VARCHAR(2000)

      as

      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

      go

      ALTER function [dbo].[GetTextValue]

      (

      @num varchar(20)

      )

      returns VARCHAR(2000)

      as

      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

      go

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

      -- Author: <Author,,Name>

      -- Create date: <Create Date,,>

      -- Description: <Description,,>

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

      ALTER TRIGGER [AmountToWordsTrigger]

      ON [dbo].[OCHO]

      AFTER INSERT,UPDATE

      AS

      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..

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.