We are on demand (SaaS). As part of our new requirement we are trying to find out if we can develop a Jaspersoft Report which would found all due timesheet for non-closed (past + present time periods and not future open time periods) and show the data based on that (e.g. we want to show 3 months in past if there is any overdue timesheet for a user opened for time entry)
How Can we achieve this ?
You can still develop reports in SAAS also, sorry I am not able to get the exact problem. Are you looking for the query?
Sorry for the delay Suman. I am not looking for any query. Let me explain I have created a custom timesheet overview portlet which is beneficial for not only Resource Managers but also for Project Managers to see data across project / resources / timeperiod / pm / rm wise. This portlet also has the option to see old posted timesheet data even from 2012. But as per another new requirement business wants that keeping this functionality intact but with that what they want is to when they access the report by default it should show the current week including past weeks of current month + last 3 months time period data. If I put nsql param for timeperiod start / end date is is limiting the portlet as then even if i am filtering the data for the year 2012 / 2013 / 2014 / 2015/ 2016 it is returning nothing
If it is possible to solve via portlet let me know as it must be accessible for resources / managers
If its the data not showing and your SQL remains the same then it report won't show if you take exact SQL, if its slice data then you may need to set the slices accordingly.
The data is showing in the portlet for past years when i'm not adding the run time nsql params for prstart column as when it is used it only fetches the records put under param condition (tp.prstart BETWEEN ADD_MONTHS(@SYSDATE@,(@NVL@(@WHERE:PARAM:USER_DEF:INTEGER:months@,-3))) AND @SYSDATE@) and if i try to filter anything manually nothing other than the same set of result shows up
Have a look at the Query "My Timesheets" - remove:
AND r1.user_id = @WHERE:PARAM:USER_ID@
located twice as there is a UNION in the query.
You should have what you need, and it could be completed in a Portlet, rather than JasperSoft. There is even a hidden parameter 'months' which determines the number of months to pull back - if not set, it will pull back 3 months in the past.
It does not fix my problem as it limits the data completely in the portlet not only in run time
Hi Avuk, unclear what is your problem that you are trying to fix.
You can copy the NSQL, quickly convert to SQL by removing the NSQL tags, and paste into a new JasperSoft report.
Your choice if you must run as JasperSoft or a Portlet, but either way the logic of the required SQL is in the 'My Timesheet' query which you have access to.
As a SAAS client as well, we have developed several custom JasperSoft reports, as well as several custom Portlets.