Clarity

I want to create a portlet which shows resource calendar (When Filtered by Resource and Start Day and End Day).
The sample data is as :

Resource _________________Week of 14/Jan/201__________________

14/Jan 15/Jan 16/Jan 17/Jan 18/Jan
Kaushal Holiday Available Vacation Available Vacation
I can get Whether the date is holiday or not by using the query (Which is giving the holidays of resource):-

SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:s.ID:SliceId@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:s.PRJ_OBJECT_ID:ResorceID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:s.FIRST_NAME:FirstName@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:s.Last_NAME:LastName@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:s.SLICE_DATE:NonWorkingDays@
FROM
(

select s.ID,s.PRJ_OBJECT_ID, R.FIRST_NAME, R.LAST_NAME, s.SLICE_DATE from prj_blb_slices s
JOIN SRM_RESOURCES R on R.ID = S.PRJ_OBJECT_ID
where slice = 0

) s

Where slice_date BETWEEN @where:param:user_def:date:paramstartdate@ AND @where:param:user_def:date:paramenddate@


AND @FILTER@






Someone who has done this earlier can help me achieve this.

Regards
Kaushal

Hi All,

Any ideas here for Kaushal?

Thanks!
Chris

Someone who can help ?

If you take your NSQL query, and just make the SLICE_DATE into another DIMENSION, then that will allow you to build a 2-dimensioned portlet (dimension 1 = resources and that reads down the screen, dimension 2 = slice date and that reads across the page).

That will look like what you want.

Thanks Dave.

Dave wrote:

If you take your NSQL query, and just make the SLICE_DATE into another DIMENSION, then that will allow you to build a 2-dimensioned portlet (dimension 1 = resources and that reads down the screen, dimension 2 = slice date and that reads across the page).

That will look like what you want.

Please help on how can I get data from two queries in one

say I want to represent data as :-

Resource 14/Jan 15/Jan 16/Jan 17/Jan 18/Jan
Kaushal Holiday Available Vacation Available Vacation

Where I can get whether it is a holiday or available (but through two different queries).

I know a workaround ( by creating a function) but that is taking too much time and for efficiency I need to run two separate queries.

Is there any way for this or any other Workaround.

Thanks in Advance
Regards
Kaushal Kishore

I'm saying that the query you already have will almost tell you everything you need to know;

SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:s.ID:SliceId@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:s.PRJ_OBJECT_ID:ResorceID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:s.FIRST_NAME:FirstName@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:s.Last_NAME:LastName@,
[color=#ff0000],@SELECT:DIM:USER_DEF:IMPLIED:SDATE:s.SLICE_DATE:SliceDate@[color]

FROM
(

select s.ID,s.PRJ_OBJECT_ID, R.FIRST_NAME, R.LAST_NAME, s.SLICE_DATE from prj_blb_slices s
JOIN SRM_RESOURCES R on R.ID = S.PRJ_OBJECT_ID
[color=#ff0000]/* where slice = 0 remove this line */[color]
[color=#ff0000]WHERE SLICE_REQUEST_ID = 1 /* this is the DAILYRESOURCEAVAILCURVE slice */[color]
) s

Where slice_date BETWEEN @where:param:user_def:date:paramstartdate@ AND @where:param:user_def:date:paramenddate@


AND @FILTER@

This will give you a grid of data;

          1/1     2/1     3/1     4/1     5/1
DAVE      7.5     7.5     7.5     7.5     7.5
Kaushal    8       0       0       0       8

You could use DECODE or CASE to convert '0' to 'Holiday' and non-zero to 'Working' if you liked, but you don't need another query to get what you need.

--

(Note though that your solution will only report data where your availability slice (DAILYRESOURCEAVAILCURVE) is defined)

kaushal.kishore wrote:

Please help on how can I get data from two queries in one

Getting "2 queries into 1" really is just a SQL issue ; you just need to JOIN your data such that you can write 1 query, you shouldn't need a "function" to do this unless the problem is very complicated.

(How you "JOIN" your data will of course all depend on what that data is - there is not a simple 1-line answer to that)

Thanks Dave For your Quick Response.

Regards
Kaushal Kishore

Hi Dave,

Please guide me on displaying the data in the way I want shown in attached image (DemoResourceCalendar.jpg).
At present It is displaing like the one shown in attached image (presentResourceCalendar.jpg),

Thanks in advance
Regards
Kausal Kishore

You could get something "close" to that, by formatting your SQL to produce the "grid" of data rather than a line - but its still never going to look exactly like an "outlook calendar" view.

You could perhaps reformat the output that you already have with a macro in Excel? (i.e. "Export to Excel" what you have, run a macro to reformat it)

--

By the way, have you looked at the "out of the box" screens that present very similar data to what you appear to be trying to build a portlet to show? (the "Resource Planning" screens, or even the Resource / Allocations / Details screens - you can "see" availability as one of the grid values in those screens, along with lots of other interesting information). Of course this really depends on YOUR functional use-case whether they would work for you.