1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need MM-YYYY format instead of MMM-YY format while getting RTF output in EXCEL

Discussion in 'Oracle Forms and Reports' started by SAGAR_GABA, Aug 25, 2012.

  1. SAGAR_GABA

    SAGAR_GABA Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    I want PERIOD_NAME as one of the columns in AP module. I am using PERIOD_NUM and PERIOD_YEAR(asked to use) from GL_PERIODS TABLE and concatenating them to get the PERIOD_NAME in MMM-YY fromat.
    I can get the PERIOD_NAME changed in the MM_YYYY format in the query but when I try to view the output in Excel format the format automatically gets converted into MMM_YY format.
    Please suggest me any solution to this.
    Mail me to : sagargaba05@gmail.com
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Sagar,

    Please post some sample data for both scenarios. By that we can guide you in right way.
     
  3. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi Sagar, Excel automatically takes starting date of the month. There is work around solution for this.

    try to concat aphostraphe (') to the field. When you concat the field is treated as text.

    Ex: '''||gl_period . I am also using the same trick.
     
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Kiran,

    Is it an standard solution for this?
     
  5. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Bharat,

    Ex: 08-2012 will be taken as 01-AUG-2012. Excel will convert automatically. Correct format will be shown other than excel.

    so with this trick (adding apostrophe), will be treated as text, otherwise as Date.

    This is not standard solution. you can add any character.

    Ex: if you want to display 0000001, trailing zeros, excel will not allow zeros and will display as 1 only.

    can be display the original value by adding this special characters.

    Hope got my point.
     
  6. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi Friends

    No need to add the space or apostrophe. Try with below tag

    <fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?PERIOD_NAME?></fo:bidi-override>

    Use it in the RTF template.

    Regards
    Sambasiva Reddy
     
  7. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi ,

    I hope the issue belongs to excel output format. That was related to excel one. I think using this tag in RTF doesn't solve the problem.
     
  8. SAGAR_GABA

    SAGAR_GABA Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    hi all,
    i need to get eg. 07-2012 format and i am getting in JUL-12 format when i try and see the output in Excel output.
    What i did is i got PERIOD_NUM(i.e.07) and PERIOD_YEAR(i.e.2012) in two different fields in the RTF and a field for '-' in the middle. the trik i used is i concatenated a space with PERIOD_YEAR(i.e. PERIOD_YEAR || ' '). So, now it doesnt get converted.
    i hope it doesnt create any problem when i try to sort it by PERIOD.

    Thanks,
    Sagar
     
  9. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi Sagar,

    use the following code and let me know ''''||PERIOD_YEAR.
     
  10. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Sagar,

    That's good solution. Its better idea to use space there.