Xcelsius dashboard panel missing results from WSDL query .

[Anon Anon]

SELECT @select:dim:user_def:implied:task:t.prid:task_int_id@,

@select:dim_prop:user_def:implied:task:t.prexternalid:task_id@,

@select:dim_prop:user_def:implied:task:NVL(t.prwbssequence,0):wbs_sequence@,

@select:dim_prop:user_def:implied:task:t.prname:task_name@,

@select:dim_prop:user_def:implied:task:inv.code:inv_code@,

@select:dim_prop:user_def:implied:task:t.prstart:start_date@,

@select:dim_prop:user_def:implied:task:cop_calc_finish_time_fct(t.prfinish):finish_date@,

@select:dim_prop:user_def:implied:task:NVL(t.priscritical,0):critical@

FROM   prtask t

LEFT OUTER JOIN INV_INVESTMENTS inv ON  t.prprojectid = inv.id

WHERE  t.pristask <> 0

AND t.prstatus <> 2

AND t.prismilestone = 1

AND    @WHERE:SECURITY:PROJECT:t.prprojectid@

AND    @FILTER@

AND @NVL@(INV.PURGE_FLAG,0) = 0

AND INV.IS_ACTIVE=1

HAVING @HAVING_FILTER@

ORDER BY wbs_sequence

 

The above query is being used through an Xcelsius dashboard panel using WSDL query and it does NOT bring back any data.

The very same query added to a portlet works just fine and returns data.

If I execute the SQL of the query using my database login in SQLExplorer, the query does not execute and actually returns "an invalid identifier message" around the "cop_calc_finish_time_fct" function.

 

This is a CA on-demand hosted application, a support issue was opened and I am told that the database user "niku" has rights to this "cop_calc_finish_time_fct" function. What else needs to be checked at DB level or Xcelsius to have this working? Perhaps I am missing some basic requirement, but hard to find.

 

To debug the issue, I have created an Xcelsius dashboard that simply pulls data using a Web service connection into a spreadsheet container in Xcelsisus with a different query and it works just fine. I had to set the Flash variables to match global variables of interactive portlets defined in the system.

Then I created another XLF file. Using the query above built another dashboard, used the same layout and flash variables and it does NOT display data. At run time, exporting the spreadsheet shows that query does not produce any result.

 

I would appreciate your responses to help resolve this issue.

 

Thanks!

[Sridhar Bandaru]

I can see that this query uses security clause. Have you verified the access rights of the user whose session is being used to invoke this query?

[Anon Anon]

Sridhar,

Yes, I have access to the project and the same query when displayed by a portlet displays all the projects that meet the criteria.

[David Morton]

If you remove the security clause from the SQL, does it work?  (Just to eliminate/prove access-rights being the problem)

[Anon Anon]

David,

I don't believe this is a security issue and here is why -

Using Xcelsius 2008 SP6, I was able to create another dashboard that uses Web Service URL and the same query to pull the data into a scorecard container and it just simply runs and gets the data no errors, no issues. So same query is running in another container in different dashboard. I also converted it to SWF file and created an interactive portlet to host the SWF and it renders correctly.

 

Things to rule out next -

1. Issue with the Stacked Bar Chart or its configuration, or perhaps corruption.

2. Issue with the Web Service query entered in the Xcelsius file.

[Sridhar Bandaru]

Can you try SOAP UI to test the web service?