1 2 Previous Next 21 Replies Latest reply on Jun 25, 2015 6:56 AM by Saro

    Case statement issue for Null condition

    Saro

      Hi friends,

       

      I have a analysis with the following columns say:

       

      Effective_date and the header_id column

       

      The results of this analysis will be like for each and every effective date there will be header id and some of the effective date will not have header id and it will be null(i.e) same like below

       

       

      Over above you can see 31/12/2008 have header id and the remaining dates have null header_id.

       

      Suppose if i select a date from the prompt through presentation variable say: June 18 2015 that is today then the analysis has to results from the dates 1-1-2006(which is the default starting year date) upto june 18 2015(that is the year that im selecting from the prompt) and the below filter does that

      Ahmed Scenario2.PNG

      But my problem is that i have a header id column, and the result as to appear as for the dates between (1-1-2006 to 31-5-2015) it has to show the header id only with the null values and not the header id that is not null values and for the remaining dates say from (1st june 2015[01-06-2015] to 18th june 2015[18-06-2015]) it has to show all the header id values(that has both the null values as well as not null values)

       

      Hence for the above condition i substituted the filter like below which is not working, as it is resulting in No results.


      I have written a case for the header id filter like below in the sql expression

      Ahmed Scenario3.png

       

      SQL Expression: case when "Fact_gl_jv"."Jvl_effective_date" <= Date '2006-01-01' and "Fact_gl_jv"."Jvl_effective_date" >= TIMESTAMPADD(SQL_TSI_DAY,-DAYOFMONTH(Date '@{v_date}{2015-06-18}'), Date '@{v_date}{2015-06-18}') THEN null ELSE "Dim4_gl_jvh"."Jvh_accrual_rev_je_header_id" END


      Im not sure, what im missing in my condition. Kindly guide me with this issue friends.


      Thanks in advance.


      Regards,

      Saro

        • 1. Re: Case statement issue for Null condition
          asim cholas

          Can you please reverse greater than with less than and vise versa.. something like below

           

          case when "Fact_gl_jv"."Jvl_effective_date" >= Date '2006-01-01' and "Fact_gl_jv"."Jvl_effective_date" <= TIMESTAMPADD(SQL_TSI_DAY,-DAYOFMONTH(Date '@{v_date}{2015-06-18}'), Date '@{v_date}{2015-06-18}') THEN null ELSE "Dim4_gl_jvh"."Jvh_accrual_rev_je_header_id" END


          Thanks

          Aj

          1 person found this helpful
          • 2. Re: Case statement issue for Null condition
            Saro

            Hi AJ,

             

            Nice to get a reply from you again..

             

            Sorry AJ i dint noticed that lesser than/greater than condition on changing the condition, now i can see the analysis results only with the header id(containing not null values) for the dates from 1-06-2015 to 18-06-2015.

             

            But it is not showing the null values of the header id prior to the date 1-1-2006 to 31-05-2015 and also the null values for the dates 1-06-2015 to 18-06-2015.

             

            the analysis is omitting the null values for my above dates. Is im missing something.

             

            Thanks,

             

            Regards,

            Saro

            • 3. Re: Case statement issue for Null condition
              asim cholas

              im little bit confused..:(.

               

              Do you have any data for those dates? Do you want to display all dates even though there is no header id from 2006? While joining your header table and fact it will omit records from both the  table if there is no keys available for those dates.

               

              Thanks

              Aj

              • 4. Re: Case statement issue for Null condition
                Saro

                Hi AJ,

                 

                Yes i have the respective GL balance for header id with null as well as the not null values.

                 

                 

                 

                Also my effective date column is from the fact and the header id column is from the dimension and i can fetch both the null and not null values of header id with only the below filter condition for my analysis. Take a look @ the below screenshot.

                 

                Filter:

                AJ1.PNG

                Analysis:

                 

                But in my case i do want to display only the null header id values b/w the dates(1-1-2006 to 31-5-2015) and header id values with both(null and not null values) for the dates from (1-06-2015 to 18-06-2015). Since i have selected the prompt as 18-06-2015.

                 

                Regards,

                Saro

                • 5. Re: Case statement issue for Null condition
                  asim cholas

                  case when "Fact_gl_jv"."Jvl_effective_date" between Date '2006-01-01' and TIMESTAMPADD(SQL_TSI_DAY,-DAYOFMONTH(Date '@{v_date}{2015-06-15}'), Date '@{v_date}{2015-06-15}')  then NULL else "Dim4_gl_jvh"."Jvh_accrual_rev_je_header_id" end

                   

                  Please check the above formula and let me know..

                   

                   

                  Thanks

                  Aj

                  1 person found this helpful
                  • 6. Re: Case statement issue for Null condition
                    Saro

                    Hi AJ,

                     

                    Then also No change the result is showing the records only with header_id from 1-06-2015 to 15-06-2015 and it not showing the null header_id results from 1-1-2006 to 31-5-2015 and null header_id results for 1-06-2015 to 15-06-2015.

                     

                    This is the screenshot of the filter i applied to my analysis

                     

                    AJ4.PNG

                    Thanks,

                     

                    Regards,

                    Saro

                    • 7. Re: Case statement issue for Null condition
                      asim cholas

                      .. Paste that formula in your header id column

                      case when "Fact_gl_jv"."Jvl_effective_date" between Date '2006-01-01' and TIMESTAMPADD(SQL_TSI_DAY,-DAYOFMONTH(Date '@{v_date}{2015-06-15}'), Date '@{v_date}{2015-06-15}')  then NULL else "Dim4_gl_jvh"."Jvh_accrual_rev_je_header_id" end


                      Thanks

                      Aj

                      1 person found this helpful
                      • 8. Re: Case statement issue for Null condition
                        Saro

                        Hi AJ,

                         

                        I have a doubt in substituting the formula with in the Edit formula expression of the header id column. On substituting that, will it make the header id to appear as null for the dates from (1-1-2006 to 31-5-2015) even if the header id with not null exist in those dates originally.

                         

                        Im in doubt that this formula is making null for all the header id that is between the dates (1-1-2006 to 31-5-2015). But in my case i need to fetch the records with the null header id that exist originally in the database for those dates and i dont want to make the not null header id to be changed to null which exist in the dates (1-1-2006 to 31-5-2015) to fetch in the result.

                         

                        Im confused here

                         

                        Regards,

                        Saro

                        • 9. Re: Case statement issue for Null condition
                          asim cholas

                          oh..  i got it now.. Create a new report with only one column which is your header_id.. Create  filters for that report like below.

                           

                          Filter 1) "Fact_gl_jv"."Jvl_effective_date" between Date '2006-01-01' and TIMESTAMPADD(SQL_TSI_DAY,-DAYOFMONTH(Date '@{v_date}{2015-06-15}'), Date '@{v_date}{2015-06-15}')


                          Filter 2) "Dim4_gl_jvh"."Jvh_accrual_rev_je_header_id" is  not null

                          Save it with some name..


                          Now open your current report


                          Keep a filter like


                          "Dim4_gl_jvh"."Jvh_accrual_rev_je_header_id" is based on another analysis. Choose your second analysis from browse for report. make the condition as Not In


                          Thanks

                          Aj




                          1 person found this helpful
                          • 10. Re: Case statement issue for Null condition
                            Saro

                            Hi AJ,

                             

                            Now my report is behaving abnormally.

                             

                            Like you said i have created one report only with header id column with these two filters

                            AJ5.PNG

                            And as a result im fetching the not null header_id records from the date 01-01-2006 to 20-06-2015 .and that is fine. and i saved this analysis.


                            Now i went to my main report and in that report for the header_id column i applied the below filter based upon another analysis like

                            AJ6.PNG

                            Rather than the above filter for header id i dint substitute any case expression inside the edit formula of the header id. And now the results is fetching like

                            as it is showing the not null header id records of only for the june month. other than this i couldnt see any records in the report AJ.

                             

                            Regards,

                            Saro

                            • 11. Re: Case statement issue for Null condition
                              Saro

                              Hi AJ,

                               

                              With this condition, what bi is doing is that it is neglecting both the null as well as the not null header id between the date 1-1-2006 to 31-05-2015 and it is fetching only the not null header id from the date 1-06-2015 upto the dates passed in the presentation variable.

                               

                              Regards,

                              Saro

                              • 12. Re: Case statement issue for Null condition
                                Saro

                                Yes AJ, it is filtering out the not null header id. But im afraid that it is filtering the null header id's also between the dates 1-1-2006 to 31-5-2015.

                                 

                                Here is the snap FYR.

                                 

                                 

                                The above results fetches the not null header id from the dates 1-6-2015 to the current date in the prompt.

                                 

                                Regards,

                                Saro

                                • 13. Re: Case statement issue for Null condition
                                  asim cholas

                                  I hope in the subreport it is filtering wherever header id is not null for the period 1-Jan-2006 till 31-May-2015. These records you are in need of excluding in the main report. Is that correct?

                                   

                                  1)YOu need null header id records from 1-Jan-2006 till 31st May 2015

                                  2)You need both null and not null header id records for the current month.

                                   

                                  Is this correct?

                                  • 14. Re: Case statement issue for Null condition
                                    Saro

                                    Yes AJ correct!!! and this is the exact requirement.

                                    1 2 Previous Next