SOLVED

SharePoint Custom Columns and OData feed

%3CLINGO-SUB%20id%3D%22lingo-sub-364514%22%20slang%3D%22en-US%22%3ESharePoint%20Custom%20Columns%20and%20OData%20feed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-364514%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20to%20be%20able%20to%20add%20columns%20to%20the%20risks%20and%20issue%20lists%20on%20each%20project%20site.%20Whilst%20that%20is%20easy%20to%20do%2C%20my%20question%20is%2C%20with%20the%20oData%20feed%2FPower%20BI%20integration%20would%20these%20custom%20columns%20be%20available%20for%20reporting%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20isn't%20much%20documentation%20on%20project%20sites%20on%20the%20impact%20or%20customisation%20or%20project%20sites%20SharePoint%20vs%20PowerBI%2FOData.%20(That%20I%20can%20find)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EPaul%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-366599%22%20slang%3D%22en-US%22%3ERe%3A%20SharePoint%20Custom%20Columns%20and%20OData%20feed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-366599%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20%3D)%3C%2Fimg%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-365184%22%20slang%3D%22en-US%22%3ERe%3A%20SharePoint%20Custom%20Columns%20and%20OData%20feed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-365184%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENo%20need%20to%20say%20sorry%20for%20posting%20here!%20This%20is%20the%20tech%20community%20for%20Project%2C%20Microsoft's%20PPM%20tool.%20For%20your%20query%20maybe%20try%20one%20of%20the%20communities%20below%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FInternet-of-Things-IoT%2Fct-p%2FIoT%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FInternet-of-Things-IoT%2Fct-p%2FIoT%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FAzure%2Fct-p%2FAzure%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FAzure%2Fct-p%2FAzure%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%3C%2FP%3E%0A%3CP%3EPaul%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-364797%22%20slang%3D%22en-US%22%3ERe%3A%20SharePoint%20Custom%20Columns%20and%20OData%20feed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-364797%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20for%20posting%20here%2C%20I%20have%20a%20new%20account%20and%20I%20can't%20post%20a%20new%20topic%20yet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20question%2C%20and%20need%20your%20opinion%20or%20solution%20if%20you%20have%20one.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20work%20in%20a%20company%20that%20is%20trying%20to%20make%20smart%20heating%20systems%20in%20building%20using%20IoT%20and%20more%20precisely%20Microsoft%20Azure.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20followed%20the%20tutorials%20that%20Microsoft%20Azure%20offers%2C%20until%20it%20came%20to%20PowerBi...%20And%20i%20found%20two%20elements%20that%20made%20me%20lose%20alot%20of%20time..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20element%3A%20Sending%20data%20directly%20from%20Azure%20Storage%20Blob%20doesn't%20work%20on%20PowerBI%2C%20i%20had%20to%20recreate%20an%20other%20query%20to%20make%20it%20work%20but%20it%20took%20lots%20of%20time%20to%20find%20the%20solution%20on%20the%20blog.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESecond%20element%3A%20I'm%20surprised%20that%20there%20is%20only%20commercial%20units%20for%20data%20in%20PowerBI.%20Isn't%20it%20supposed%20to%20be%20monitoring%20devices%20in%20a%20building%20as%20well%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWell%20i%20hope%20that%20i%20didn't%20annoy%20you%20with%20my%20questions%20but%20you%20seem%20more%20experienced%20that%20me%2C%20so%20i%20decided%20to%20ask%20while%20waiting%20to%20be%20able%20to%20post%20my%20own%20topics.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPS%3A%20Azure%20IoT%20central%20is%20very%20limited%20and%20not%20smart%20at%20all...%20i%20had%20to%20use%20something%20else%20like%20PowerBI.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-364688%22%20slang%3D%22en-US%22%3ERe%3A%20SharePoint%20Custom%20Columns%20and%20OData%20feed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-364688%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20Paul!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-364684%22%20slang%3D%22en-US%22%3ERe%3A%20SharePoint%20Custom%20Columns%20and%20OData%20feed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-364684%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Paul%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAdding%20new%20custom%20columns%20to%20the%20default%20Issues%20and%20Risks%20lists%20and%20adding%20new%20lists%20to%20the%20project%20sites%20is%20very%20simple%20as%20you%20have%20mentioned%2C%20reporting%20is%20a%20bit%20more%20involved%20if%20you%20wanted%20to%20create%20a%20portfolio%20issues%20reports%20and%20include%20those%20new%20columns%20etc.%20The%20default%20Project%20Online%20OData%20Reporting%20API%20(_api%2FProjectData)%20only%20includes%20the%20default%20Issues%20and%20Risks%20columns%2C%20so%20custom%20columns%20will%20not%20be%20available%20here%20for%20reporting.%20For%20an%20out%20of%20the%20box%20approach%2C%20you%20would%20need%20to%20use%20the%20SharePoint%20List%20REST%20APIs%20to%20get%20this%20data%20in%20Power%20BI.%20See%20an%20example%20here%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fpwmather.wordpress.com%2F2016%2F01%2F05%2Fwant-to-query-cross-project-site-sharepoint-lists-in-projectonline-projectserver-powerbi-powerquery-bi-office365-excel-ppm%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fpwmather.wordpress.com%2F2016%2F01%2F05%2Fwant-to-query-cross-project-site-sharepoint-lists-in-projectonline-projectserver-powerbi-powerquery-bi-office365-excel-ppm%2F%3C%2FA%3E%26nbsp%3BPlease%20note%2C%20this%20will%20only%20refresh%20in%20the%20Power%20Bi%20Desktop%20client%2C%20the%20data%20will%20not%20refresh%20in%20the%20Power%20BI%20app%20service%20(well%20it%20didn't%20the%20last%20time%20I%20looked%20at%20this!).%20Another%20approach%20is%20to%20look%20at%20extracting%20the%20data%20out%20into%20a%20SQL%20Database%20for%20example%2C%20them%20the%20Power%20BI%20report%20just%20queries%20the%20SQL%20database.%20You%20can%20either%20build%20something%20yourself%20or%20there%20are%20many%20third%20party%20products%20that%20do%20this.%3C%2FP%3E%0A%3CP%3EPaul%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Hi

 

I am looking to be able to add columns to the risks and issue lists on each project site. Whilst that is easy to do, my question is, with the oData feed/Power BI integration would these custom columns be available for reporting?

 

There isn't much documentation on project sites on the impact or customisation or project sites SharePoint vs PowerBI/OData. (That I can find)

 

Thanks

Paul

5 Replies
Highlighted
Solution

Hi Paul,

 

Adding new custom columns to the default Issues and Risks lists and adding new lists to the project sites is very simple as you have mentioned, reporting is a bit more involved if you wanted to create a portfolio issues reports and include those new columns etc. The default Project Online OData Reporting API (_api/ProjectData) only includes the default Issues and Risks columns, so custom columns will not be available here for reporting. For an out of the box approach, you would need to use the SharePoint List REST APIs to get this data in Power BI. See an example here: https://pwmather.wordpress.com/2016/01/05/want-to-query-cross-project-site-sharepoint-lists-in-proje... Please note, this will only refresh in the Power Bi Desktop client, the data will not refresh in the Power BI app service (well it didn't the last time I looked at this!). Another approach is to look at extracting the data out into a SQL Database for example, them the Power BI report just queries the SQL database. You can either build something yourself or there are many third party products that do this.

Paul

Highlighted

Thank you Paul!

Highlighted

Hello,

 

Sorry for posting here, I have a new account and I can't post a new topic yet.

 

I have a question, and need your opinion or solution if you have one.

 

I work in a company that is trying to make smart heating systems in building using IoT and more precisely Microsoft Azure.

 

I followed the tutorials that Microsoft Azure offers, until it came to PowerBi... And i found two elements that made me lose alot of time..

 

First element: Sending data directly from Azure Storage Blob doesn't work on PowerBI, i had to recreate an other query to make it work but it took lots of time to find the solution on the blog.

 

Second element: I'm surprised that there is only commercial units for data in PowerBI. Isn't it supposed to be monitoring devices in a building as well? 

 

Well i hope that i didn't annoy you with my questions but you seem more experienced that me, so i decided to ask while waiting to be able to post my own topics. 

 

PS: Azure IoT central is very limited and not smart at all... i had to use something else like PowerBI. 

Highlighted

Hello,

 

No need to say sorry for posting here! This is the tech community for Project, Microsoft's PPM tool. For your query maybe try one of the communities below:

 

https://techcommunity.microsoft.com/t5/Internet-of-Things-IoT/ct-p/IoT

https://techcommunity.microsoft.com/t5/Azure/ct-p/Azure

 

Thanks

Paul

Highlighted

Thank you =)