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!

Matrix report value not showing

Discussion in 'Oracle Financials' started by assaad.slm, Nov 14, 2014.

  1. assaad.slm

    assaad.slm Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hello, i created a matrix report but the data is not expanding and fetched under the period column can you tell me the issue plz ?
    im not able the attach the mail any mail where i can send the rtf with the xml ?
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Assaad,

    It's impossible to provide any help or guide to your problem without additional details.

    I suppose it's an XML publisher custom report. If you're not able to attach the XML data template, then at least copy / paste and post the content in the meantime. May be the issue is coming from the query in the XML ?

    If data is coming properly, then it's most probably the RTF which would be faulty....
     
    assaad.slm likes this.
  3. assaad.slm

    assaad.slm Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Dear rajenb plz find attached the files the data is expanding now horizontaly but as u can see the order of the date is switching and not showing by order and the data is putting randomly not in the proper date .. Please help and advice

    Regards,
    Assaad Sleiman
     

    Attached Files:

  4. assaad.slm

    assaad.slm Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    and please find also the xml file to be able to generate the data
     

    Attached Files:

  5. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Now that's better Assaad :) I see the issue you're facing exactly.

    The solution is quite simple: in a matrix report (XML Publisher report), even if you don't have data for your "horizontal axis", you need to dump it in your XML file and the order of the content should match for every record of the "vertical axis".

    For example, your periods start from "Jul-14" till "Oct-14" (in the sample provided) and for some customer(s) you don't have data for all the 4 months (most probably no Sales during that particular month). You should make sure data for these 4 periods appear in the records of all the customers AND in the same order (Jul - Aug - Sep - Oct). If you don't have any sales for any particular period, then output "0".

    Your sample corrected file is attached.

    Please test and see if Ok.
     

    Attached Files:

  6. sambuduk

    sambuduk Forum Advisor

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

    If BI publisher desktop is installed in your PC , Pivot table creation option is available in BI desktop. Through Pivot layout you can easily achieve matrix functionality.
    Here I have attached sample layout and .xml file.

    Regards
    Sambasiva Reddy.K
     

    Attached Files:

    assaad.slm likes this.
  7. assaad.slm

    assaad.slm Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Dear Rajen,

    I loaded the xml data and preview it there is no change,
    Please note that xml data is generated for the oracle report developer, as you can see i guess the problem is in term of the rtf file wrong structure or in the oracle report and im not able to see data or know how to insert a 0 value where there is no net sale, Kindly find attached a zip folder that will contant the rdf, rtf, xml, Script for the table creation and the data to be able to insert it and test it,, please note im new to such domain. Please test it and advice

    Best Regards,
    Assaad Sleiman
     

    Attached Files:

  8. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Assaad,

    I currently have some issues with my Report Builder :confused: ... But I managed to extract the Query from your source.

    Please try the following query instead:

    Code (SQL):
    WITH periods AS (
      SELECT DISTINCT period period_name, g.period_year, g.period_num
      FROM temp_family, gl_periods g
      WHERE period = g.period_name
      ORDER BY g.period_year, g.period_num
    )
    SELECT family_category,
      customer_name,
      rep_name,
      customer_number,
      p.period_name,
      SUM(
      CASE
        WHEN p.period_name = t.period
        THEN t.net_sale
        ELSE 0
      END) total_sell,
      period_year,
      period_num
    FROM temp_family t, periods p
    GROUP BY family_category,
      customer_name,
      rep_name,
      customer_number,
      p.period_name,
      period_year,
      period_num
    ORDER BY customer_number,
      period_year,
      period_num;
     
  9. upppppppppppppppppppppp phụ chủ thớt
     
  10. athar

    athar Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    pakistan
    is it possible to make a matrix report without aggregation???