Question Status

Verified
Vikash Jain asked a question on 24 Mar 2015 2:25 AM

Hi All,

How to convert Numeric  value in word in SSRS report,  any function is there or by a code 

Regards ,

Vikash

Reply
Suggested Answer
Sohaib responded on 24 Mar 2015 2:28 AM

On SSRS level this will be very tough to manage, as Microsoft has stopped use of functions since arrival of AX2012-SSRS. in normal SSRS functions are still supported.

So you cannot write VB script on SSRS level for AX2012.

The easy solution is to manage it in AX X++ and send amount in words to your Dataset and just display it on SSRS.

Reply
Verified Answer
manikumar responded on 24 Mar 2015 2:30 AM

see below example:

_num = mtSalesPurchInvoiceHeaderFooterTmp.Ecess;

   returntxt = Global::amountInWordsIND(mtSalesPurchInvoiceHeaderFooterTmp.Ecess);

   DecNo = _num;

   _num  = _num - DecNo;

   _num = _num * 100;

   if(_num>0)

   {

       returntxt = returntxt +" and ";

       returntxtDec =  Global::amountInWordsIND(_num);

       returntxt = returntxt + returntxtDec+ " Paise" ;

   }

    mtSalesPurchInvoiceHeaderFooterTmp.AmountinwordsEcess = returntxt +" "+"Only)";

Reply
Suggested Answer
Mallika Roy responded on 24 Mar 2015 2:32 AM

Hi Vikas,

Use the following:

public TempStr numeralsToTxt(real _num)

{

   int     numOfPennies = (decRound(frac(_num), 2) * 100) mod 100;

   real    test         = _num - frac(_num);

   int64   numOfTenths;

   str 20  ones[19], tenths[9], hundreds, thousands, millions, billions, trillions;

   int64   temp;

   str 200 returntxt;

   int64   testLoc;

   real modOperator(real a1, real a2)

   {

   int tmpi;

   real tmp1, tmp2;

   tmp1 = a1 / a2;

   tmpi = real2int(tmp1);

   tmp2 = tmpi;

   return (tmp1 - tmp2)*a2;

   }

   real checkPower(real  _test, int64 _power)

   {

       int64   numOfPower;

       if (_test >= _power)

       {

           testLoc = real2double(_test);

           numOfPower = testLoc div _power;

           if (numOfPower >= 100)

           {

               temp = numOfPower div 100;

               returntxt = returntxt + ' ' + ones[temp] + ' ' + hundreds;

               numOfPower = numOfPower mod 100;

           }

           if (numOfPower >= 20)

           {

               temp = numOfPower div 10;

               returntxt = returntxt + ' ' + tenths[temp];

               numOfPower = numOfPower mod 10;

           }

           if (numOfPower >= 1)

           {

               returntxt = returntxt + ' ' + ones[numOfPower];

               numOfPower = numOfPower mod 10;

           }

           switch(_power)

           {

               case 1000000000000 :

                   returntxt = returntxt + ' ' + trillions;

                   _test = modOperator(_test, 1000000000000.00);

                   break;

               case 1000000000 :

                   returntxt = returntxt + ' ' + billions;

                   _test = modOperator(_test, 1000000000);

                   break;

               case 1000000 :

                   returntxt = returntxt + ' ' + millions;

                   _test = modOperator(_test, 1000000);

                   break;

               case 1000 :

                   returntxt = returntxt + ' ' + thousands;

                   _test = modOperator(_test, 1000);

                   break;

               case 100 :

                   returntxt = returntxt + ' ' + hundreds;

                   _test = modOperator(_test, 100);

                   break;

           }

       }

       return _test;

   }

   ones[1] = "@SYS26620";

   ones[2] = "@SYS26621";

   ones[3] = "@SYS26622";

   ones[4] = "@SYS26626";

   ones[5] = "@SYS26627";

   ones[6] = "@SYS26628";

   ones[7] = "@SYS26629";

   ones[8] = "@SYS26630";

   ones[9] = "@SYS26631";

   ones[10] = "@SYS26632";

   ones[11] = "@SYS26633";

   ones[12] = "@SYS26634";

   ones[13] = "@SYS26635";

   ones[14] = "@SYS26636";

   ones[15] = "@SYS26637";

   ones[16] = "@SYS26638";

   ones[17] = "@SYS26639";

   ones[18] = "@SYS26640";

   ones[19] = "@SYS26641";

   tenths[1] = 'Not used';

   tenths[2] = "@SYS26643";

   tenths[3] = "@SYS26644";

   tenths[4] = "@SYS26645";

   tenths[5] = "@SYS26646";

   tenths[6] = "@SYS26647";

   tenths[7] = "@SYS26648";

   tenths[8] = "@SYS26649";

   tenths[9] = "@SYS26650";

   hundreds    = "@SYS26651";

   thousands   = "@SYS26652";

   millions    = "@SYS26653";

   billions    = "@SYS26654";

   trillions   = "@SYS101697";

   test = checkPower(test, 1000000000000);

   test = checkPower(test, 1000000000);

   test = checkPower(test, 1000000);

   test = checkPower(test, 1000);

   test = checkPower(test, 100);

   if (test >= 20)

   {

       testLoc = real2double(test);

       numOfTenths = testLoc div 10;

       returntxt = returntxt + ' ' + tenths[numofTenths];

       numOfTenths = numOfTenths mod 10;

       test = real2int(test) mod 10;

   }

   if (test >= 1)

   {

       numOfTenths = real2int(test);

       returntxt = returntxt + ' ' + ones[numOfTenths];

   }

  if (numOfPennies)

   {

       returntxt = '***' + returntxt + ' ' + "@SYS5534" + ' ' + num2str(numOfPennies,0,0,0,0) + '/100';

   }

   else

   {

       returntxt = '***' + returntxt + ' ' + "@SYS5534" + ' ' + '00/100';

   }

   return returntxt;

}

Reply
Vikash Jain responded on 24 Mar 2015 11:18 PM

Thanks  Sohaib,Manikumar,Mallika

Regards ,

Vikash

Reply
Verified Answer
manikumar responded on 24 Mar 2015 2:30 AM

see below example:

_num = mtSalesPurchInvoiceHeaderFooterTmp.Ecess;

   returntxt = Global::amountInWordsIND(mtSalesPurchInvoiceHeaderFooterTmp.Ecess);

   DecNo = _num;

   _num  = _num - DecNo;

   _num = _num * 100;

   if(_num>0)

   {

       returntxt = returntxt +" and ";

       returntxtDec =  Global::amountInWordsIND(_num);

       returntxt = returntxt + returntxtDec+ " Paise" ;

   }

    mtSalesPurchInvoiceHeaderFooterTmp.AmountinwordsEcess = returntxt +" "+"Only)";

Reply
Suggested Answer
Sohaib responded on 24 Mar 2015 2:28 AM

On SSRS level this will be very tough to manage, as Microsoft has stopped use of functions since arrival of AX2012-SSRS. in normal SSRS functions are still supported.

So you cannot write VB script on SSRS level for AX2012.

The easy solution is to manage it in AX X++ and send amount in words to your Dataset and just display it on SSRS.

Reply
Suggested Answer
Mallika Roy responded on 24 Mar 2015 2:32 AM

Hi Vikas,

Use the following:

public TempStr numeralsToTxt(real _num)

{

   int     numOfPennies = (decRound(frac(_num), 2) * 100) mod 100;

   real    test         = _num - frac(_num);

   int64   numOfTenths;

   str 20  ones[19], tenths[9], hundreds, thousands, millions, billions, trillions;

   int64   temp;

   str 200 returntxt;

   int64   testLoc;

   real modOperator(real a1, real a2)

   {

   int tmpi;

   real tmp1, tmp2;

   tmp1 = a1 / a2;

   tmpi = real2int(tmp1);

   tmp2 = tmpi;

   return (tmp1 - tmp2)*a2;

   }

   real checkPower(real  _test, int64 _power)

   {

       int64   numOfPower;

       if (_test >= _power)

       {

           testLoc = real2double(_test);

           numOfPower = testLoc div _power;

           if (numOfPower >= 100)

           {

               temp = numOfPower div 100;

               returntxt = returntxt + ' ' + ones[temp] + ' ' + hundreds;

               numOfPower = numOfPower mod 100;

           }

           if (numOfPower >= 20)

           {

               temp = numOfPower div 10;

               returntxt = returntxt + ' ' + tenths[temp];

               numOfPower = numOfPower mod 10;

           }

           if (numOfPower >= 1)

           {

               returntxt = returntxt + ' ' + ones[numOfPower];

               numOfPower = numOfPower mod 10;

           }

           switch(_power)

           {

               case 1000000000000 :

                   returntxt = returntxt + ' ' + trillions;

                   _test = modOperator(_test, 1000000000000.00);

                   break;

               case 1000000000 :

                   returntxt = returntxt + ' ' + billions;

                   _test = modOperator(_test, 1000000000);

                   break;

               case 1000000 :

                   returntxt = returntxt + ' ' + millions;

                   _test = modOperator(_test, 1000000);

                   break;

               case 1000 :

                   returntxt = returntxt + ' ' + thousands;

                   _test = modOperator(_test, 1000);

                   break;

               case 100 :

                   returntxt = returntxt + ' ' + hundreds;

                   _test = modOperator(_test, 100);

                   break;

           }

       }

       return _test;

   }

   ones[1] = "@SYS26620";

   ones[2] = "@SYS26621";

   ones[3] = "@SYS26622";

   ones[4] = "@SYS26626";

   ones[5] = "@SYS26627";

   ones[6] = "@SYS26628";

   ones[7] = "@SYS26629";

   ones[8] = "@SYS26630";

   ones[9] = "@SYS26631";

   ones[10] = "@SYS26632";

   ones[11] = "@SYS26633";

   ones[12] = "@SYS26634";

   ones[13] = "@SYS26635";

   ones[14] = "@SYS26636";

   ones[15] = "@SYS26637";

   ones[16] = "@SYS26638";

   ones[17] = "@SYS26639";

   ones[18] = "@SYS26640";

   ones[19] = "@SYS26641";

   tenths[1] = 'Not used';

   tenths[2] = "@SYS26643";

   tenths[3] = "@SYS26644";

   tenths[4] = "@SYS26645";

   tenths[5] = "@SYS26646";

   tenths[6] = "@SYS26647";

   tenths[7] = "@SYS26648";

   tenths[8] = "@SYS26649";

   tenths[9] = "@SYS26650";

   hundreds    = "@SYS26651";

   thousands   = "@SYS26652";

   millions    = "@SYS26653";

   billions    = "@SYS26654";

   trillions   = "@SYS101697";

   test = checkPower(test, 1000000000000);

   test = checkPower(test, 1000000000);

   test = checkPower(test, 1000000);

   test = checkPower(test, 1000);

   test = checkPower(test, 100);

   if (test >= 20)

   {

       testLoc = real2double(test);

       numOfTenths = testLoc div 10;

       returntxt = returntxt + ' ' + tenths[numofTenths];

       numOfTenths = numOfTenths mod 10;

       test = real2int(test) mod 10;

   }

   if (test >= 1)

   {

       numOfTenths = real2int(test);

       returntxt = returntxt + ' ' + ones[numOfTenths];

   }

  if (numOfPennies)

   {

       returntxt = '***' + returntxt + ' ' + "@SYS5534" + ' ' + num2str(numOfPennies,0,0,0,0) + '/100';

   }

   else

   {

       returntxt = '***' + returntxt + ' ' + "@SYS5534" + ' ' + '00/100';

   }

   return returntxt;

}

Reply