1 Reply Latest reply on Jun 23, 2010 4:13 PM by karenpeacock

    Crystal Report for category logging

    DipeshLBH Apprentice

      Hello,

       

      Wondering if anyone has created a Cross Tab Crystal Report that shows by each category, the number of Incidents logged by Support Group.

       

      I've got a nice-ish report but I'm struggling to pull in results for categories against which no Incidents have been logged. I thought a right outer join on the IM Incident table would pull them in but then it conflicts with the 'Date Logged' paramater in place, e.g.

       

      If you enter a date range for all of the month of May

      But no Incidents have been logged for category 'Modem'

       

      then nothing is returned...but I'd really like Modem to be returned showing no Incidents logged.

       

      Any advice gratefully received!!!

       

      Thanks, Dipesh

        • 1. Re: Crystal Report for category logging
          karenpeacock SupportEmployee

          Hi Dipesh

           

          I think you've got to start by ensuring the incident data with no categories against shows on the report without adding the cross tab.  This will prove that your linking is working the way that you want it to.  Just drag something like incident id and category onto the details for this.  Then try it with the date parameter set too.

           

          If it's not coming through, the way I'd do the linking is to have the category table first on the left, as this is the table that you always want to appear irrespective of the incidents, and then link incident to this with a left outer join.  This should work the same as a right outer I know but I just know that this is how I've done this in the past and its worked.  As you have more tables then you'd need to start on the left with category, then incident, then process and then the other tables off this.  Do you have all the other joins set to left outer?

           

          Once you've checked the data is being displayed, then it should work ok.  I am assuming its a count on im_id that you are doing in the cross tab and so it should return a count of 0 if it finds none.  There are also some settings on the cross tab itself for Suppress Empty Rows and Suppress Empty Columns that you need to ensure aren't checked.

           

          Another thought that I had of a different approach is to use the IsNull function in a formula to replace blanks with a 0 then use the results of this formula in the crosstab.  If you group your report first and then create a formula to test the theory out and place it on the group header.  The formula would be something like:

           

          If IsNull (Count ({im_incident.im_id},{im_category.im_title})) then 0 else

          Count ({im_incident.im_id},{im_category.im_title})

           

          Hope this is some help - there was also some advice on the business objects forums that I spotted online which might be useful too

          Regards

          Karen