Data exists within database but doesnt show up on webi report

Question

Hi I am building a v simple report as below; when I include the customer reference number and filter for another column (resolved time) being NULL then the number of incidents are reduced. (I know they exist within the database with the same filters)

(only INC1988464 is showing when more incidents should be showing)

Is there a way to test issues such as this in webi? Or a way to resolve this? Thanks in advance

Here is the sql used to make the report:

SELECT
  'INC'||TRIM(to_char(ead_incident.incident,'0000000')),
  ead_incident_credit.circuit_ref,
  ead_incident_credit.customer_ref,
  ead_incident_credit.data_rate,
  ead_incident_credit.connection_type,
  ead_incident_credit.completed_date,
  ead_incident_credit.wholesaler_name,
  ead_incident_credit.opened_datetime,
  ead_incident_credit.resolved_datetime,
  ead_incident_credit.resolution_details,
  ead_incident_credit.resolution_duration_seconds,
  ead_incident_credit.access_circuit_core_network,
  ead_incident_credit.charge_amount,
  ead_incident_credit.or_cost,
  ead_incident_credit.sky_cost,
  ead_incident_credit.service_credit_applicable,
  ead_incident_credit.service_credit_due,
  CASE WHEN COALESCE(COALESCE(ead_incident.actual_end_datetime,ead_incident.impact_end_datetime),ead_incident.resolved_datetime) IS NOT NULL THEN CASE WHEN ead_incident.impact_type = 'Full Outage' AND COALESCE(ead_incident.cause_classification,'') <> 'Resolved - No fault found' AND COALESCE(odwh_data.ead_within_sla('TTR',ead_incident.opened_datetime, COALESCE(COALESCE(ead_incident.actual_end_datetime,ead_incident.impact_end_datetime),ead_incident.resolved_datetime)),'f') = 'f' THEN 'Y' ELSE 'N' END ELSE NULL END,
  CASE WHEN ead_incident.correlation = 't' THEN 'Y' ELSE 'N' END,
  odwh_system.kpi_nextweek_startdate(),
  odwh_system.kpi_currentweek_startdate()
FROM
  odwh_data.ead_incident  ead_incident INNER JOIN odwh_data.ead_incident_credit  ead_incident_credit ON (ead_incident_credit.incident=ead_incident.incident)
  
WHERE
  (
   CASE WHEN COALESCE(COALESCE(ead_incident.actual_end_datetime,ead_incident.impact_end_datetime),ead_incident.resolved_datetime) IS NOT NULL THEN CASE WHEN ead_incident.impact_type = 'Full Outage' AND COALESCE(ead_incident.cause_classification,'') <> 'Resolved - No fault found' AND COALESCE(odwh_data.ead_within_sla('TTR',ead_incident.opened_datetime, COALESCE(COALESCE(ead_incident.actual_end_datetime,ead_incident.impact_end_datetime),ead_incident.resolved_datetime)),'f') = 'f' THEN 'Y' ELSE 'N' END ELSE NULL END  =  'Y'
   AND
   (
    CASE WHEN ead_incident.deleted = 't' THEN 'Y' ELSE 'N' END  =  'N'
    AND
    (
     CASE WHEN ead_incident.wholesaler = 't' THEN 'Y' WHEN  ead_incident.wholesaler = 'f' THEN 'N' END  =  'Y'
     OR
     CASE WHEN ead_incident.wholesaler = 't' THEN 'Y' WHEN  ead_incident.wholesaler = 'f' THEN 'N' END  Is Null  
    )
   )
  )

Answer 1

If you can create a free-hand SQL query which exhibits your issue like this...

SELECT
    'INC1988464'                              AS [Incident Number]
  , 'SKY-COLT-003'                            AS [Customer Reference]
  , CONVERT (DATETIME, '2022-05-08 11:17:49') AS [Resovled Time]
UNION
SELECT
    'INC1988464'                             AS [Incident Number]
  , 'SKY-COLT-005'                           AS [Custome rReference]
  , CONVERT (DATETIME, '2022-05-09 9:03:21') AS [Resovled Time]
UNION
SELECT
    'INC1988464'   AS [Incident Number]
  , 'SKY-COLT-007' AS [Customer Reference]
  , NULL           AS [Resovled Time]
UNION
SELECT
    'INC1988464'                              AS [Incident Number]
  , 'SKY-COLT-009'                            AS [Customer Reference]
  , CONVERT (DATETIME, '2022-05-09 10:17:02') AS [Resovled Time];

Then we can take that query, put it into a WebI report, replicate the issue, and possibly resolve it.

Answer 2

First and easiest thing to check. Do you have any filters already on your report page or are you starting on a brand new page? The filters may be against the page or the individual report block.

If you have, then remove them and see if the issue is resolved. Note that there may also be a ranking in there, returning the top 1 incidents.

If not, then there will be something in the report logic preventing the records from being returned. Comment out the where clauses and make sure to add the columns from the where clauses into your result set. From here you have two things to check:

1/ All rows are being returned 2/ You can see which rows are going to be filtered by your where clause based on what you see on your report based on your logic. You have nested case expressions which may not be doing what you expected for example.