Extract Query Results

This example demonstrates how to run queries and slicers (parameters) programmatically to extract results.

The example uses API authentication driven from JavaScript. See Authentication APIs for alternatives.

// URL of the Pyramid installation and the path to the API 2.0 REST methods
var pyramidURL = "http://mysite.com/api2/";


// step 1: authenticate admin account and get token
// NOTE: callApi method is a generic REST method shown below.
let token = callApi("auth/authenticateUser",{
	"data":{
		"userName":"adminUser",
		"password":"abc123!"
	}
},false);


//step 2: find a parameter definition called "param x". Parameters are saved items to drive slicers
let calculation = callApi("content/findContentItem",{
	"searchParams": {
		"searchString": "param x",
		"filterTypes": [2],
		"searchMatchType": 2,
		"searchRootFolderType":0
	},
	"auth": token // admin token generated above
});

let paramId = calculation.data[0].id;

//step 3: get the parameter's elements
let parameterElements = callApi("query/getParameterElements",{
   "parameterId": paramId,
   "auth": token
});

let members = parameterElements.data.members // list for members
console.log(members);

//step 4: find a data discovery called "report x"
let dataDiscovery = callApi("content/findContentItem",{
	"searchParams": {
		"searchString": "report x",
		"filterTypes": [3],
		"searchMatchType": 2,
		"searchRootFolderType":0
	},
	"auth": token // admin token generated above
});


let reportId = dataDiscovery.data[0].id;

//step 5: extract the report results as JSON that have been filtered by the first member of the parameter found in step 3
let queryResult = callApi("query/extractQueryResult",{
   "data": {
      "itemId":reportId,
      "exportType":0, //JSON
      "exportOptions": {},
      "externalParameters": {
        "reportFilters" : [
         {
         "value": members[0].uniqueName
         }
        ]
      }
   },
   "auth": token
});

console.log(JSON.parse(queryResult.data));


// ##### optional generic logging method for debugging ##############
function log(msg){
	document.write(msg);
	console.log(msg);
}

// ##### generic REST API calling method ##############
function callApi(path,data,parseResult=true){
	var xhttp = new XMLHttpRequest();
	xhttp.open("POST", pyramidURL+path, false);
	xhttp.send(JSON.stringify(data));
	if(parseResult){
		return JSON.parse(xhttp.responseText);
	}else{
		return xhttp.responseText;
	}
}

		

This example demonstrates how to authenticate users with Windows Authentication and run a query programmatically.

using System;
using System.Linq;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Net.Http;
using System.Text;
using System.Threading.Tasks;

namespace CsWebSite
{
	public partial class WinAuth : System.Web.UI.Page
	{
		public const String API_PATH = "http://mySite.com/api2/";
		protected void Page_Load(object sender, EventArgs e)
		{
			//logging the current user with windows auth
			String userToken = getToken("authenticateUserWindows", null);

			Response.Cookies.Add(new HttpCookie("PyramidAuth", userToken));

			//running a query. The user needs to be an admin user to access this API.
			JToken result = callApi("query/extractQueryResult", new
			{
				data = new
				{
					itemId= "9185ea22-bf14-4606-a955-4bbd73a88c38", //content items ID
					exportType =0,//export result as json, we can do xml(1) and CSV(2) as well
					exportOptions=new
					{
						showUniqueName=true
					}
				},
				auth = userToken
			});
			//the result is passed as a json string, needed to be deserialized again to read the values
			JToken document = JsonConvert.DeserializeObject>JObject<(result.ToString());
			String firstResult = document["Document"]["queries"][0]["result"]["data"][0][0].ToString();
		}

		//this method is diffrent then the normal to pass windows credentals UseDefaultCredentials=true
		private String getToken(String service, Object data)
		{
			HttpClient client = new HttpClient(new HttpClientHandler()
			{
				UseDefaultCredentials = true
			});

			StringContent content = null;
			content = new StringContent(JsonConvert.SerializeObject(data), Encoding.UTF8, "application/json");
			Task<HttpResponseMessage> response = client.PostAsync(API_PATH + "auth/" + service, content);

			return response.Result.Content.ReadAsStringAsync().Result;
		}
		
		//generic method for calling REST methods
		private JToken callApi(String service, Object data)
		{
			HttpClient client = new HttpClient();

			StringContent content = null;
			content = new StringContent(JsonConvert.SerializeObject(data), Encoding.UTF8, "application/json");
			Task>HttpResponseMessage< response = client.PostAsync(API_PATH + service, content);

			String resultStr = response.Result.Content.ReadAsStringAsync().Result;
			if (resultStr.Count() == 0)
			{
				return null;
			}
			return JsonConvert.DeserializeObject>JObject<(resultStr)["data"];
		}
	}
}