Skip to main content

Looking to add a CUSTOM long text data field to the ODATA feed that I can use on my Power BI portfolio dashboard.  I’d like to add it to the Project Dimension but I cannot get the SQL working correctly. 

 I have tried this

, isNull((select s.description
    from ip.structure s
    where s.structure_code=pv.overall_stat_comm), 'Unassigned') as iOverall Status Assessment]

 and I have tried this

,(select STRING_AGG(CONVERT(NVARCHAR(max),line_text), '') within group (order by isnull(line_number, 1) asc)
from ip.long_text
where key1 = 'overall_stat_comm' and key2 = pv.ppl_code) as pOverall Status Commentary]

As you can see, i’m feeling around in the dark here so any assistance greatly appreciated.

Would it make more sense to add it to the Project Assessment Dimension?  That dimension contains the Overall Status details.  We have some custom fields for status reports and that is where we added them

I have added a custom long text field to my Project Dimension and it is is similar to this one.

,(select STRING_AGG(CONVERT(NVARCHAR(max),line_text), '') within group (order by isnull(line_number, 1) asc)
from ip.long_text
where key1 = 'overall_stat_comm' and key2 = pv.ppl_code) as [Overall Status Commentary]

What error do you receive?

Thanks ​@Mario.munoz . that worked when I added it to the Project Assessment Dimension.  I agree with ​@Thbutler that this makes more sense but not sure why it didn’t work for the Project Dimension. Also, the only change I had to make was to add the underscores Overall_Status_Commentary

Cheers

Reply


Terms of UseCookie settings
All Data Subject Access Requests (DSARs), and requests of “Do Not Sell My Personal Information” according to the CCPA, must be submitted through the Planview DSAR portal located here

Planview has appointed a Data Privacy Officer (DPO) to be responsible for overseeing our Privacy Management Program and related privacy compliance measures. DPO can be contacted at privacy@planview.com