Clarity

 View Only
Expand all | Collapse all

help setting up my graph portlet to show stacked values as project names

  • 1.  help setting up my graph portlet to show stacked values as project names

    Posted Jul 20, 2009 01:14 PM
    hi,my result set consists of project name, allocation, week (week23, week24 etc...)  I'm trying to display my query in stacked column where my Y axis is the allocations and X axis is my projects stacked with Week no as x-axis label.  i didn't see anything similar in my system graph portlet and I hope it can be done. i was able to do something similar but the values ( projects and weeks) were set values and not dynamic where this one is i'm showing all projects for the current week and next 12.  thank you for your help.  


  • 2.  Re: help setting up my graph portlet to show stacked values as project nam

     
    Posted Jul 22, 2009 11:33 AM
    Hi,  I am checking with someone on this.   I will let you know what they come back with.   You may also want to check with Support to see if they have an answe unless another user can assist here.  Regards,Chris


  • 3.  Re: help setting up my graph portlet to show stacked values as project nam

    Posted Jul 22, 2009 12:34 PM
    Looking to hear back from you.I looked through all the system graph portlets and didn't see anthing similar. it's very simple chart that I'm trying to do.  waiting for your response.thanks


  • 4.  Re: help setting up my graph portlet to show stacked values as project nam

    Posted Jul 22, 2009 12:42 PM
      |   view attached
    just added a sample chart that i'm trying to accomplish.thanks


  • 5.  Re: help setting up my graph portlet to show stacked values as project nam

    Posted Jul 23, 2009 12:15 AM
    You can definately do this sort of thing if the list of "slices" is fixed (i.e. you know your list of projects to start with)  The trick is constructing your NSQL query correctly though.  In your case it needs to retrurn data in the format;  MONTH Count(P1) Count(P2) Count(P3) ... Count (Pn)7/13/2009       5                   4                       10                     ...         137/20/2009     6           5             9             ...     4  etc.  --  BUT I don't have quick answer for how you can do this when the number of PROJECTS is "variable" (i.e. you don't know "n")  I'm sure we can construct a SQL query that could cope with that, but I'm not convinced that we can convert that into NSQL easily (beacuse of the variable number of columns).  The workaround would be to have a whole bunch of "dummy columns" at the end of the SQL, i.e. "n" in my example exceeeds the number of projects that you expect to report upon so you could write NSQL with a fixed number of columns (many of which would be empty)....  Thats all a bit messy but that would work!    David MortonCapgemini  


  • 6.  Re: help setting up my graph portlet to show stacked values as project nam

    Posted Jul 23, 2009 06:38 AM
    Thank you Dave. the tricky part is the variable number of projects which makes  specifying the number of columns  impossible sinceI hight have up to 50 columns(projects) at one time.    I'll keep investigating.thanks


  • 7.  Re: help setting up my graph portlet to show stacked values as project nam

    Posted Jul 23, 2009 06:44 AM
    ^ yeah - in that case I'd have >50 "dummy columns" in my NSQL/SQL (say a 100?)  Messy, but it would work.  Dave.    


  • 8.  Re: help setting up my graph portlet to show stacked values as project nam

    Posted Jul 23, 2009 06:47 AM
    thank you. do you have sample sql code that converts the row to column?thanks


  • 9.  Re: help setting up my graph portlet to show stacked values as project nam

    Posted Jul 23, 2009 07:01 AM
    ;-) no not really!  I'm sure I can build some SQL that does this (but I'm also sure its going to be "fiddly" and would take me a while to get right)  ---  BUT    I am thinking of a SQL statment that would be along the lines of  (Select count(whatever) From Wherever where project =   PROJECT_1   )   As column 1,(Select count(whatever) From Wherever where project =   PROJECT_2   )   As column 2,(Select count(whatever) From Wherever where project =   PROJECT_3   )   As column 3...,(Select count(whatever) From Wherever where project =   PROJECT_100   )   As column 100  but this has to be driven off a clever way of detemining (i.e. NOT hard coding) PROJECT_n      Good luck!  
       


  • 10.  Re: help setting up my graph portlet to show stacked values as project nam

    Posted Jul 23, 2009 07:04 AM
    Dave,but this requires me to also know the name of the projects.... Now this is impossible... the number of projects could go up and down and the names could changed and so on......  I was thinking of maybe some kind of stored procedure that loops through my table and pivot it...  I don't know but will keep working on it.  thank you.   Message Edited by waely on 07-23-2009 11:05 AM [left]


  • 11.  Re: help setting up my graph portlet to show stacked values as project nam

    Posted Jul 23, 2009 08:01 AM
    Yes thats what I was trying to get at with "but this has to be driven off a clever way of detemining (i.e. NOT hard coding) PROJECT_n"  You certainly DON'T need to know anything about the projects, you can stiull build that up withing the SQL (but this is where it starts to get tricky!   :-) )  --  BUT...  Your idea of a stored procedure is actually a very good one...  It is fairly simple (in Oracle) to build a SQL query (i.e. an NSQL query) that retrieves data directly from an Oracle stored procedure (well a FUNCTION actually) as if it were a query....  you define your NSQL as selecting from table( >) in place of where you would usually put the Oracle tables  you define an Oracle type which matches your columnsand then define your   > as a PIPELINED function returning a TABLE of data (returning that new Oracle type)  then all the complicated stuff you can do in PL/SQL in the function rather than trying to do it in the SQL.  that all might sound a bit complicated, but its REALLY powerful, being able to build queries on complex PL/SQL logic rather than just SQL.  (if that all sounds too complicated - post another Q on the forum asking how to do it and I'll write a better answer to (it at a later date))  Dave.


  • 12.  Re: help setting up my graph portlet to show stacked values as project nam

    Posted Jul 23, 2009 08:10 AM
    Thank you Dave.I'm using sql server  2005 so I investigate how to accomplish that.  waely


  • 13.  Re: help setting up my graph portlet to show stacked values as project nam

    Posted Jul 23, 2009 08:15 AM
    Unlucky - migrate to Oracle and then I could have helped!  :smileysad:  Good luck then!