How to slice date >= 'current month' in NSQL???

[Anon Anon]

Hello everyone,
Can anyone please let me know how i need to give slice date < = 'CURRENT MONTH' to pass the month value into the NSQL WHERE clause?

When i query in database i can hard code it to use slice date < = '2010/01/08' but i'm creating a portlet for the user to track hours for every month. Please let me know. I really appreciate your help.

>
SELECT
SUM(SLICE) AS ACTUAL_HRS,
R.FULL_NAME,
P.NAME PROJECT
FROM
NIKU.PRJ_BLB_SLICES S,
NIKU.PRASSIGNMENT A,
NIKU.PRTASK T,
NIKU.SRM_RESOURCES R,
NIKU.SRM_PROJECTS P
WHERE
S.PRJ_OBJECT_ID = A.PRID AND
A.PRTASKID = T.PRID AND
T.PRPROJECTID = P.ID AND
A.PRRESOURCEID = R.ID AND
SLICE_REQUEST_ID = 4
AND MONTH(S.SLICE_DATE) = 06 -- instead of hardcoded value i need pass current month value
GROUP BY
FULL_NAME,
P.NAME



Does anyone have any documentation on NSQL queries please let me know. I'm a beginner to clarity.

Thank you
RM

[Anon Anon]

Hello Riya

You need to get the function to resolve the current date on your DBMS. on SQLServer it is getDate().

Your code would look like this:

SELECT
SUM(SLICE) AS ACTUAL_HRS,
R.FULL_NAME,
P.NAME PROJECT
FROM
NIKU.PRJ_BLB_SLICES S,
NIKU.PRASSIGNMENT A,
NIKU.PRTASK T,
NIKU.SRM_RESOURCES R,
NIKU.SRM_PROJECTS P
WHERE
S.PRJ_OBJECT_ID = A.PRID AND
A.PRTASKID = T.PRID AND
T.PRPROJECTID = P.ID AND
A.PRRESOURCEID = R.ID AND
SLICE_REQUEST_ID = 4
AND MONTH(S.SLICE_DATE) = month(getDate())
AND YEAR(S.SLICE_DATE) = YEAR(getDate()) -- be careful with selecting only the month
GROUP BY 
FULL_NAME,
P.NAME

Kind regards

--
rafa

[Anon Anon]

how can i write this to do a monthly for that year. I think this is multi dimensional query. Hours is in sliced

something like

project name jan feb mar.....

1) abc 10 15 12
2)adc 10 15 12

Any help is really appreciated

[Anon Anon]

Hello

You need to select the Slice and the corresponding date. (and the month of course). On the NSQL you must specify that there are 2 dimensions, one are the projects and the other is the months. The portlet will show you the rows and collumns accordingly.

Please let me know if that helps or if you need additional assistance.

Regards

Rafa

[Anon Anon]

I really apprecite your help. I created a 2D portlet but there are some flaws.

Right now my report is showing different rows for each timeperiod. Ignore my ' ------'

EG

Resource id 2010/01/1 2010/02/01 2010/03/01 2010/04/01

123 ------------- 5.5
123 ----------------------------- -------------------- 20
123 ----------------- ----- 5
123 ---------------------------------------- ---------------------------- 8



i'm looking for something like this. Any help is really appreicated

>
Resource id 2010/01/1 2010/02/01 2010/03/01 2010/04/01
123 ----- 5.5 ------ 5 ------ 20 ------ 8


Please let me know

[Anon Anon]

Hello Riya

That thing happens when the dimension value for the rows (resource in this case) is different for each row (even when the resource is the same.

Could you post the full NSQL query here so i can help you a little more and try to troubleshoot the problem?

Kind regards.

Rafa

[Anon Anon]

Thank you RAFA,
I sql_wrapped my query. It works . I just made some small change.
So now this leads me to another question How to do i sub total per project. I'm using 7 projects in my list. They want to know the subtotals. Is it possible in NSQL?

eg: right now i'm showing
>
---------------------------- actuals per each month [color=#3E2B85]TOTAL[color]
prjname, resname jan -------feb--------mar
p1 r1 2.5 -------3.5---------0.5
p1 r2 ..
p1 r3
[color=#534978]SUBTOTAL[color]
p2 r1
p2 r1
[color=#534978]SUBTOTAL[color]

TOTAL - did it using portlet

but need help with sub totals and row total

[Anon Anon]

Hello Riya

I don't know if making subtotals is possible using NSQL queries. (Some expert her could guide us perhaps?).

What I would do is create a portlet with the project totals and linking the portlet to another portlet with the Totals for It.


Something like this:

Portlet 1:

NAME
      MONTH1
MONTH2
MONTH3
Project 1
10
20
30
Project 2
40
23
12
Project 3
20
10
15

And creating a link, so if i click a project1 Name, it will show the details for the project:

NAME
      MONTH1
MONTH2
MONTH3
Resource 1
3
6
15
Resource 2
3
12
5
Resource 3
6
6
10

Hope it helps, Kind regards

Rafa

[Anon Anon]

This sounds interesting! Any NSQL sample that i can refer too?

Let me know

[Anon Anon]

Riya,

Even Though it is not exactly what you expect.Have you tried the Aggregation Option
in Portlet.(Admin-->Portlet-->List Column-->Aggregation.



Cheers,
sundar

Attachment(s)

Portlet Aggregation.zip   10 KB 1 version

[Anon Anon]

Thank you for your reply Sundar!
I tried to use it but it creates total for all the projects. I need sub total for each project and then total for all the projects.

[Anon Anon]

Hello Riya

Again, i don't really know if subtotals could be added to a NSQL sentence. If you are interested in my approach (2 portlets) you could do this (and again, that is the way I would do it, don't know if it helps you):

You have to do two different porlets (with queries and pages!!), Portlet 1 will have Information on the project totals, Portlet 2 will have the information on resource totals on an specific project,

You should create the Portlet 2 first, because you will need to know which info do you need to filter.

You should create your NSQL for portlet 2 normally (query all the resource information for the projects), however, you should include a condition for the query to filter on an specific project. Lets say this Query will have the folowing condition on the WHERE clause:

[color=#FD0B0B]srm_projects.id [color]= @WHERE:PARAM:XML:INTEGER:/data/[color=#FD0B0B]id_project[color]/@value@

Remember the id_project part, you will need it later.

After you have created the NSQL 2 and the portlet 2, you should create the page for the Portlet. When you create this page, you should create it with the template option: "Popup page template". Clic on Save and continue. On the creation page, select the "Linkable" option and clic Save and continue.

On the Link Parameters Options you should create a new parameter. The parameter name could be anything you want, the parameter id should be the same code you used on the NSQL, on this case should be [color=#FF0000]id_project[color]. The rest of the process to create the Page is the same.

Now, lets do the Portlet 1 NSQL. You should create this Query normally (in this case you will query the slices for all the projects). You must select in one of the columns the Id of the project. Remember how will you name that coulmn. After the NSQL is validated you must clic on "Linking" Option. Then Click on New:

On the Link creation page, you should put a Name and ID (put the name and id that you want). On the "Action" drop down, look for the NAME of page you created for Portlet 2. When you select that option it will show an aditional Drop Down, in this case, it will be the id of the link of the page you are linking. On the second drop down you must select the column that holds the Project ID.

When you create the Portlet 1, on the Fields Option, you can add the link you created on the NSQL, just click on the "properties" link on the field and add it as a Link. A good field to put it is on the Project Name, however you could put the link on any field you want.

The rest of the process is the same as on any portlet/Portlet page.

Remember one thing, you only need to put the portlet 1 on the Menu, because the portlet 2 will open when you click on the link on portlet 1.

Please let me know if this helps.

Kind Regards

Rafa.

[Anon Anon]

Thank you Rafa,
For taking time and expaining me the process. I will surely try this approach. As i said i'm a new bee to the clarity programming. So i will be having many questions in my future posts.

Thank you and i appreciate all who have replied.
RK

[Deactivated User]

You can get the subtotals with something like (starting from Rafa's sample)

SELECT
' ' sort,
P.NAME PROJECT,
R.FULL_NAME,
(SUM(SLICE)/3600) AS ACTUAL_HRS
FROM
NIKU.PRJ_BLB_SLICES S,
NIKU.PRASSIGNMENT A,
NIKU.PRTASK T,
NIKU.SRM_RESOURCES R,
NIKU.SRM_PROJECTS P
WHERE
S.PRJ_OBJECT_ID = A.PRID AND
A.PRTASKID = T.PRID AND
T.PRPROJECTID = P.ID AND
A.PRRESOURCEID = R.ID AND
SLICE_REQUEST_ID = 4
AND MONTH(S.SLICE_DATE) = month(getDate())
AND YEAR(S.SLICE_DATE) = YEAR(getDate()) -- be careful with selecting only the month
GROUP BY
FULL_NAME,
P.NAME


UNION

SELECT
sort,
PROJECT,
FULL_NAME,
SUM(ACTUALHRS) ACTUAL_HRS
From
(SELECT
'Sub total' sort,
P.NAME PROJECT,
' ' FULL_NAME,
(SUM(SLICE)/3600) AS ACTUALHRS
FROM
NIKU.PRJ_BLB_SLICES S,
NIKU.PRASSIGNMENT A,
NIKU.PRTASK T,
NIKU.SRM_RESOURCES R,
NIKU.SRM_PROJECTS P
WHERE
S.PRJ_OBJECT_ID = A.PRID AND
A.PRTASKID = T.PRID AND
T.PRPROJECTID = P.ID AND
A.PRRESOURCEID = R.ID AND
SLICE_REQUEST_ID = 4
AND MONTH(S.SLICE_DATE) = month(getDate())
AND YEAR(S.SLICE_DATE) = YEAR(getDate()) -- be careful with selecting only the month
GROUP BY
FULL_NAME,
P.NAME
) SUB
GROUP BY
FULL_NAME,
PROJECT
sort
order by project, sort, full_name

Use the SQL wrapper, that is you put the above as the From section for the NSQL

Are you convinced that your monthly slices are accurate?
I always prefer to use daily slices.

Martti K.