none
OData custom Risk fields SSIS RRS feed

  • Question

  • HI, does the OData to SSIS approach expose custom fields created for Risks?

    Thanks

    Dan

    Tuesday, June 13, 2017 7:56 AM

Answers

All replies

  • Hi Dan,

    Please find an approach to retrieve risk custom colums via odata:

    https://pwmather.wordpress.com/2016/01/05/want-to-query-cross-project-site-sharepoint-lists-in-projectonline-projectserver-powerbi-powerquery-bi-office365-excel-ppm/


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller

    Tuesday, June 13, 2017 8:20 AM
    Moderator
  • Hi Guillaume, I am trying to follow Paul's post but am getting the below token comma error?  Any ideas?  Dan.

    Tuesday, June 13, 2017 9:02 AM
  • No idea.. Maybe Paul will pass around and give you some hint.

    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller

    Tuesday, June 13, 2017 9:54 AM
    Moderator
  • Hi Dan

    Not sure about the error there could be multiple issues like missing brackets commas, usually is a syntactical error, however here is another sample that i have used before to gather custom fields using API

    Let me know if this helps

    Note: this is not a full construct and you will need to add necessary code to execute this but should give you an idea on how to construct it

    function LoadRisks() {
    		var data = $.ajax({url:ProjectWorkspacehref + "/_api/web/lists/GetByTitle('Risks')/Items?"
    						+ "$select=ID,Title,DueDate,Exposure,Status,Escalation,Rating,Category,ContentType/Name,AssignedTo/Id,AssignedTo/FirstName,AssignedTo/LastName&$expand=ContentType,AssignedTo/Id&$orderby=DueDate&$filter=Status eq 'Active'",   
    						type: "GET",
    						dataType: "json",
    						headers: {Accept: "application/json;odata=verbose"	}       
    						});
    									
    		data.done(function (data,textStatus, jqXHR){
    					
    			for (var xi=0;xi<data.d.results.length;xi++){
    				data.d.results[xi].AssignedTo.Name = data.d.results[xi].AssignedTo.FirstName+" "+data.d.results[xi].AssignedTo.LastName;
    				//data.d.results[xi].Score = parseFloat(data.d.results[xi].Probability * data.d.results[xi].Impact).toFixed(1);
    				data.d.results[xi].DueDate = moment(data.d.results[xi].DueDate).format('DD-MMM-YY');
    				data.d.results[xi].Title = '<font color="blue"><u><a href="'+ProjectWorkspacehref+'/Lists/Risks/DispForm.aspx?ID='+data.d.results[xi].Id+'">'+data.d.results[xi].Title+'</a></u></font>';
    			}
    													
    			var FilteredRisks = data.d.results;
    
    			var CountBlackRisk = $(data.d.results).filter(function (i,n){return n.ContentType.Name=='Risk' & n.Status =='Open' & n.Rating=='Black';}).length;
    			var CountRedRisk = $(data.d.results).filter(function (i,n){return n.ContentType.Name=='Risk' & n.Status =='Open' & n.Rating=='Red';}).length;
    			var CountYellowRisk = $(data.d.results).filter(function (i,n){return n.ContentType.Name=='Risk' & n.Status =='Open' & n.Rating=='Yellow';}).length;
    			var CountGreenRisk = $(data.d.results).filter(function (i,n){return n.ContentType.Name=='Risk' & n.Status =='Open' & n.Rating=='Green';}).length;
    							
    			$('#RedRisk').html(CountRedRisk);
    			$('#YellowRisk').html(CountYellowRisk);
    			$('#GreenRisk').html(CountGreenRisk);
    			
    			$('#Risks').dataTable(	{
    					"bDestroy": true,
    					"bFilter": false,
    					"bInfo": false,
    					"bProcessing": true,
    					"bPaginate": true,
    					"bLengthChange" : false,
    					"pagingType": "simple",
    					"pageLength": 5,
    					"order": [[ 3, "asc" ]],
    					"aaData": FilteredRisks,
    					"aoColumns": [
    						{ "mData": "ID","sClass": "center", "sWidth":"10%" },
    						{ "mData": "Title", "sWidth":"40%" },
    						{ "mData": "Category","sClass": "center", "sWidth":"15%" },
    						{ "mData": "Escalation","sClass": "center", "sWidth":"15%" },
    						{ "mData": "DueDate","sClass": "center", "sWidth":"10%"},
    						{ "mData": "AssignedTo.Name","sClass": "center", "sWidth":"15%"}
    					]});
    										
    				});
    		
    		data.fail(function (jqXHR,textStatus,errorThrown){
    			waitDialogProjData.close();
    			alert("Error retrieving issues: " + jqXHR.responseText + "\n\n Issue and Risk data will not load");
    		});
    	  
        }


    Thanks | epmXperts | http://epmxperts.wordpress.com


    • Edited by epmXperts Thursday, June 15, 2017 7:06 PM
    Thursday, June 15, 2017 4:33 PM
  • Hello,

    No it doesnt as the columns are not in the Project OData API - you could however use the SharePoint API to get the Risk data in to a SQL table, an example can be found here: https://nearbaseline.com/2014/04/project-site-custom-list-reporting-using-ssis-odata-connector/

    Another example if you just want to create a report on a page is this: https://pwmather.wordpress.com/2017/05/05/want-to-report-across-projectonline-project-sites-for-sharepoint-list-data-ppm-javascript-office365-rest-odata/

    Regarding the Power Query, you are not using the site variable and also your issues list is in double quotes. See this image for the correct query: https://pwmather.files.wordpress.com/2016/01/image5.png

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Thursday, June 15, 2017 6:44 PM
    Moderator
  • Hi Paul, I got it to work in the end.   The only issue now is that Power BI does not seem to allow queries with functions to work when the report is published into the BI web app.   The report works fine if I just use Power BI for desktop.  Is this a known limitation or have I missed something?

    Thanks

    Dan

    Tuesday, June 20, 2017 8:25 AM
  • Hello,

    Yes it will only work in Power BI Desktop.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Tuesday, June 20, 2017 8:59 AM
    Moderator