Skip to main content

Hi 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.

Are 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.


@Thbutler No, I am not using PowerBI. Yes, if its PowerBI there are different ways to get that. This one I am talking directly in the SSRS report. I am referring different SQL queries. Worst Case, I will have to do the math myself to calculate based on the conditions around the data which can be retrieved from the financial data cube.


I ran into the same issue writing a query for a customized tile. Following to see what responses you get to this question. Thanks!


The 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.


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 


Thank you ​@mserafinowski 


Reply