Clarity

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

Looks ok, i will suggest running report by placing fields from sql to detail section to check further.

I tried that. It is generating a blank report.

check db connection string and compare that with your studio one, something seems to be wrong with connection 

When I did a simple query :

SELECT r.resource_key AS resource_key,

       r.resource_name AS resource_name,

  FROM

    ppm_dwh.dwh_res_resource r,

It is showing the results.

As soon as I join a tables it is not returning any rows.

SELECT r.resource_key AS resource_key,

       r.resource_name AS resource_name,

       ts.timesheet_key

  FROM

    ppm_dwh.dwh_res_resource r,

    ppm_dwh.dwh_tme_sheet ts

    where

      ts.resource_key = r.resource_key

No Results.

Naveen

You might have already checked that, but just incase the screenshot Data Adapter says CA PPM, is this pointing to DWH?

Another way to troubleshoot the query is try creating a lookup where datasource to be "DWH", if the version is 15.3 and above.

I am using the following connection.:

jdbc:jaspersoft@http://vlaxpctyjr01:8080/reportservice;datasource=/ca_ppm/data_sources/CA_PPM_DWH_BEAN;pagesize=500

Also I am able to execute the queries against this Data Adapter. It means that the connection exist.