+1Hi Experts,
I know how we can access the different financial versions and values present in them. I am interested in reading the Financial Subtotals value. I understand that they are virtual columns. Is there a way we can access through SQL Query?
This is for Planview portfolios.
Thanks
Sakthi.
Best answer by mserafinowski
Hi Sakhti,
here is an example of a query you can run against your db:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
select
structure_code as ppl_code,
C1125 as Costs_At_Complete_Baseline
, C1011 as Costs_At_Complete_Forecast
, C1105 as Total_Benefits_Baseline
, C1013 as Total_Benefits_Forecast
, C1126 as NPV_Baseline
, C1131 as ROI_Baseline
, C1134 as IRR_Baseline
, C1127 as NPV_Forecast
, C1132 as ROI_Forecast
, C1135 as IRR_Forecast
, C1128 as NPV_Variance
, C1133 as ROI_Variance
from vw_fm_columns_plan
Please adjust the codes based and have a look. Hope it helps 🙂
Best,
Michal
+2Are you using something like Power BI? If so you can use the Project Portfolio Dashboard as a base. It has several of SQL queries used by the dashboard. I’ve modified the Financial Facts table to include a few other versions that we are capturing and generate reports based on that.
+1
I ran into the same issue writing a query for a customized tile. Following to see what responses you get to this question. Thanks!
+2The success center has documentation on the database schema (https://success.planview.com/Planview_Portfolios/Analytics_and_Reporting/Database_Schema_Guide) that might help you. You can also test your queries by going to Administration → Reporting → Dataset Management. This will allow you to run SQL queries against the backend database.
+2Hi Sakhti,
here is an example of a query you can run against your db:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
select
structure_code as ppl_code,
C1125 as Costs_At_Complete_Baseline
, C1011 as Costs_At_Complete_Forecast
, C1105 as Total_Benefits_Baseline
, C1013 as Total_Benefits_Forecast
, C1126 as NPV_Baseline
, C1131 as ROI_Baseline
, C1134 as IRR_Baseline
, C1127 as NPV_Forecast
, C1132 as ROI_Forecast
, C1135 as IRR_Forecast
, C1128 as NPV_Variance
, C1133 as ROI_Variance
from vw_fm_columns_plan
Please adjust the codes based and have a look. Hope it helps 🙂
Best,
Michal
+1Thank you
Already have an account? Login
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
