AnsweredAssumed Answered

JasperSoft Studio Report

Question asked by lawang on Jul 10, 2018
Latest reply on Jul 12, 2018 by lawang

I am creating a new Timesheet Report. I got the query from the OOTB My Timesheet Report and modified as per my requirements. The query is executing in MS SQL Studio without any errors and returning the rows. But when I am running the same query in JasperSoft Studio (preview tab) it is not returning any rows.

If I run a simple query in JasperSoft Studio  -- it shows the results.. this means that there is a database connection. 

 

Attached screen shots of SQL Studio and JasperSoft Studio. 

 

Here is the query :

SELECT r.resource_key AS resource_key,           
       r.resource_name AS resource_name,
       te.investment_name AS investment_name,
       te.incident_name AS incident_name,      
       te.task_name AS task_name,
             tp.start_date AS period_start_date,
       tp.finish_date  AS period_end_date,
       n.timeentry_key AS timeentry_key,
       n.timeentry_note AS timeentry_note,
       n.category AS note_category,      
       SUM(CASE WHEN c.period_start_date = tp.start_date THEN pf.time_hours ELSE 0 END) day_1,
       SUM(CASE WHEN c.period_start_date = tp.start_date+1 THEN pf.time_hours ELSE 0 END) day_2,
       SUM(CASE WHEN c.period_start_date = tp.start_date+2 THEN pf.time_hours ELSE 0 END) day_3,
       SUM(CASE WHEN c.period_start_date = tp.start_date+3 THEN pf.time_hours ELSE 0 END) day_4,
       SUM(CASE WHEN c.period_start_date = tp.start_date+4 THEN pf.time_hours ELSE 0 END) day_5,
       SUM(CASE WHEN c.period_start_date = tp.start_date+5 THEN pf.time_hours ELSE 0 END) day_6,
       SUM(CASE WHEN c.period_start_date = tp.start_date+6 THEN pf.time_hours ELSE 0 END) day_7               
FROM   ppm_dwh.DWH_TME_PERIOD tp
       INNER JOIN  ppm_dwh.dwh_tme_sheet ts ON tp.time_period_key = ts.time_period_key
       INNER JOIN  ppm_dwh.dwh_res_resource r ON ts.resource_key = r.resource_key
       INNER JOIN  ppm_dwh.dwh_tme_entry te ON ts.timesheet_key = te.timesheet_key
       LEFT OUTER JOIN ppm_dwh.dwh_tme_entry_note n ON te.timeentry_key = n.timeentry_key                                         
       INNER JOIN  ppm_dwh.dwh_tme_entry_facts pf ON  te.timeentry_key = pf.timeentry_key
       INNER JOIN  ppm_dwh.dwh_cmn_period c ON  c.period_key = pf.period_key  
                                                                               
                                    AND c.period_type_key = 'DAILY'                                      
WHERE  1=1
AND    r.employment_type_key <> 0
AND    r.resource_type_key = 0 
and    r.resource_key = '5015276'
and te.time_record_type_key = 'TASK'
GROUP BY r.resource_key, r.resource_name, te.investment_name, te.task_name, tp.start_date, tp.finish_date, te.incident_name,
te.time_record_type_key,      
 n.timeentry_key, n.timeentry_note, n.category

Outcomes