#Trying to create a new portlet , where an assignment label/field needs to be pulled into the portlet.
The availability of column(assignment label) is dynamic , then only if user enters teh value, teh column shall be available in DB, in such instance, can an NSQL be generated ..?
Able to create SQL, but unable to convert such dynamic NSQL.
Please provide more details.
Using left joins it is possible to get data columns where there are no specific values.
Are you saying you have the SQL? If so please share it.
The requirement is suppose there are category A, B and C in database so i want to display amount for category A, B and C column wise, if anyone adds new category D then my query automatically takes category D as 4th column in query output. I prepared the SQL where i have taken category values from database in a variable and passed that into PIVOT, but now i am unable to convert it into NSQL.
Output should be displayed as
DECLARE @cols AS VARCHAR(1000)='';DECLARE @query AS VARCHAR(1000)='';SELECT @cols = @cols + QUOTENAME(PRCATEGORY) + ',' FROM (select distinct PRCATEGORY from PRJ_RESOURCES ) as tmpset @query = , N'SELECT * from ( select ptt.PRPROJECTID,prj_resources.PRCATEGORY , PAA.ACTCOST_SUM from Niku.PrTask PTT inner Join niku.prassignment PAA ON PAA.prTaskId=PTT.prid inner join niku.prj_resources on PAA.PRRESOURCEID = prj_resources.prid inner join niku.srm_resources on PAA.PRRESOURCEID = srm_resources.id where ptt.PRISTASK=1 and prj_resources.PRCATEGORY is NOT NULL and srm_resources.RESOURCE_TYPE in (0,1,2,3)) srcpivot ( SUM(PAA.ACTCOST_SUM) for PAA.PRCATEGORY in ( ' + @cols + ')) piv'
Any help would be much appreciated.
You could consider doing this as a JasperSoft report, instead of a Portlet.
Try creating a 2 dimensional portlet. The columns on the second dimension are dynamic based on query output, as the Category gets added or removed, it will be reflected in the portlet.
You will need atleast one column on the first dimension. If you don't have any column for the first dimension, try using a fixed text - "Resource Cost by Category".