0 Replies Latest reply: Nov 11, 2014 1:18 AM by Apoorv Jain RSS

    Problem with null values in total in adhoc report

      Hi,

       

      I have made an ad hoc report with 12 dimensions and 6 measures for the users to select from, along with a dynamic total (if the user selects dimension 1,3 & 4 then the total is displayed as measure 1+3+4  as so on).

      the problem i am facing is that when the measures values are mapped against a null value in dimension, the value is shown in the individual columns but not in the dynamic total. please find a screenshot below and also attached, along with the expression I am using.

      screenshot.png

      Notice the value in Last Row (#N/A)

       

      The total 1 expression is

      =

      if(SubStringCount(Concat(ID2,'|'),'M6')>0,[Sales Till Last Cutoff],0)

      +

      if(SubStringCount(Concat(ID2,'|'),'M2')>0,[In Progress],0)

      +

      if(SubStringCount(Concat(ID2,'|'),'M3')>0,[Credit Hold],0)

      +

      if(SubStringCount(Concat(ID2,'|'),'M4')>0,[Complaince Hold],0)

      +

      if(SubStringCount(Concat(ID2,'|'),'M5')>0,[Error],0)

      +

      if(SubStringCount(Concat(ID2,'|'),'M1')>0,[Invoiced Since Last Cutoff],0)

       

      with Sum of Rows as Total Mode

       

      The Total 2 has same expression as the Total 1, but with Expression Total as Total Mode. This is considering the values in the total but not displaying them.


      Total 3 is column(1)+column(2)+column(3)+column(4)+column(5)+column(6)

       

      I want the values to display in the last row in Total 1 or Total 2.

       

      Please help.

       

      Regards

      Apoorv Jain