Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    Sudhir K
    Filtering Issue on Data Model
    Topic posted May 19, 2017 by Sudhir KRed Ribbon: 250+ Points, tagged BI Publisher, Fusion Financial reporting, OTBI, Reports, Sample Reports 
    208 Views, 12 Comments
    Title:
    Filtering Issue on Data Model
    Summary:
    The results of the data model are not being filtered
    Content:

    we are trying to create a simple data model with a variable. But the data is not being filtered as per the value chosen. attached screen shot has all the detailed steps.

    Comment

     

    • Manish Pesswani

      Hi Sudhir,

      It seems filter is not working due to the left join which you have used in your query.

      If you have noticed,line level data is coming null in rest of the sources & line level data is coming proper in the source which you have passed in parameter.

      Remove the Left keyword from your code & check the results.

      -- Manish Pesswani (Technical Consultant)

       

      • Sudhir K

        Hi Manish,

        Thank you for your help. When I remove the LEFT keyword, it is working fine. But the reason I have added the LEFT join is to include in the report the invoices which do not have lines created yet. If I remove this LEFT join how can I make sure that the invoices without the lines are also listed in the report?

         

        -Sudhir

    • Manish Pesswani

      Hi Sudhir,

      Try to build query in below manner,

      select invoice_id , invoice_line_id

      from ap_invoices_All a , ap_invoice_lines_all b

      where a.invoice_id = b.invoice_id(+);

      "(+)" will work as outerjoin.

    • Manish Pesswani

      Also, you can try below modified query, in which filter & outer join both will work.

      select * from (  SELECT DISTINCT a.source ,
                        a.invoice_num,
                        a.invoice_date,
                        a.creation_date,
                        b.line_number,
                        b.line_type_lookup_code,
                        b.description "Line Description",
                        b.amount "Line Amount",
                        b.period_name,
                        b.accounting_date
          FROM ap_invoices_all a
               LEFT JOIN ap_invoice_lines_all b ON (a.invoice_id = b.invoice_id)) Z
               where   z.source = nvl(:p_source ,z.source)
      ORDER BY invoice_num, line_number

    • Sudhir K

      Hi Manish,

      Thank you so much for your help on this issue. it works fine except with one issue. When I choose multiple sources from the drop down, I get the attached error. I modified the query a little bit to replace '=' with 'in' but still no luck.

      select * from (  SELECT DISTINCT a.source ,
                        a.invoice_num,
                        a.invoice_date,
                        a.creation_date,
                        b.line_number,
                        b.line_type_lookup_code,
                        b.description "Line Description",
                        b.amount "Line Amount",
                        b.period_name,
                        b.accounting_date
          FROM ap_invoices_all a
               LEFT JOIN ap_invoice_lines_all b ON (a.invoice_id = b.invoice_id)) Z
               where   z.source in nvl(:p_source, z.source)
      ORDER BY invoice_num, line_number
       

    • Manish Pesswani

      Hi Sudhir,

      The error is due to the invalid number of arguments passed in NVL function.

      When multiple selections are done, it will pass a value like ('a','b','c', 'd') & when such string is passed in NVL function it will give an error.

      in the case of multiple selections, you need to remove the NVL function from your query.

    • Sudhir K

      Hi Manish,

      I was able to get filtering issue for 3 of the parameters fixed but 2 of them still have issue. I am attaching the document which has the complete details including the SQL query and the filtering issue we are facing.

      -Sudhir

    • Manish Pesswani

      Hi Sudhir,

      As per my knowledge if you are keeping "multiple select" ON for the parameter, then you can't keep 'can select all' enabled if values in parameter are more than 2000.

      In Such case, you have to keep parameter mandatory & user has to select at least one value.

      I will try if by using SQL we can handle such things & revert you

      • Sudhir K

        Hi Manish,

        Your inputs were very helpful for me to reach the solution. I was able to find that below function is the best way to meet my requirement. I thought I would share with you.

                coalesce(NULL,:vendor_alt_name) IS NULL
            ) OR (
                d.vendor_name_alt IN (
                    :vendor_alt_name
                )

        Thank you so much once again.
        -Sudhir

    • Sudhir K

      Thank you Manish,

      I was not able to find a way to make the parameter mandatory on the BI Report. Can you please let me know how to make a parameter mandatory?

      -Sudhir

    • Manish Pesswani

      Hi Sudhir,

      To make parameter mandatory, u can untick "Can Select All". Hence if the user doesn't select any value, the report will not retrieve any data.

      Moreover, you can append "*" to the name of parameter so that user can identify it as mandatory smiley

      Unfortunately, there is no mechanism or feature available which will give a message to the users that 'select mandatory parameters first ' or disables the apply button if mandatory parameters are not selected.

      Do let me know in case of any concerns.

       

    • Jairo Rojas Mendez

      Great!