Recently I had a client ask me to build them some lightweight BI reports because their enterprise team was too consumed with other priorities. I started to think about pulling in a 3rd party JavaScript charting library since they mostly wanted to report against 1-2 columns of a list. However one of the challenges I faced was this library had close to 10,000 items and there would be views which would contain more than 5,000 items.So instead of leveraging the lists endpoint for the REST API, I decided to get the data via the Search API which would overcome that 5,000 item limit.

Since I can’t disclose client data, I mocked up the same solution using data I found on the CT State Data site. I created a new list from the CSV and let it go until I hit over 30,000 list items:

state-listitems

The list had a few interesting columns – Agency, Department, Job Title, Compensation Type, & Amount.

state-listdata

I thought it might be interesting to build a quick report for how my tax dollars were being spent by agency. Since I would be leveraging Search for pulling in the data, I first needed to map a crawled property to a managed property. I picked one of the out of the box managed properties and mapped it to OWS_Agency:

state-managedproperty

After a few hours the managed property became available as part of the Search index.

Next, was the easy part – I added a Content Editor Webpart to a page and pointed it at an HTML file which would pull together the solution.

The complete details of the HTML file can be found below but I just want to highlight a couple of the important parts.

1. When I get data from the Search API I specify that I want ContentTypes of type Item (would support custom as well) and then say I want to pull back RefinableString102 as part of the refiners set so I can see how many items per State agency
/_api/search/query?querytext=’ContentType=Item’&refiners=’RefinableString102′

The refiners work the exact same was using the Search REST API as they do when you perform an actual SharePoint Search – it returns results that match the criteria you specify. One of the bonus features is by saying you want to pull back refiners, you immediately get a summary of the different values for that refiner. So for our instance, I’m able to report on all the different state agencies by using the RefinableString102 refiner. Within that, it found 67 different agencies and then gave me the count of how many expenditures there are per agency.

In a production environment, you would probably want to also limit the query to only return results from that particular site collection or site, but the purpose of this blog post is to hopefully give you a very easy to follow along with example.

2. When I make the AJAX call the JSON returned back is in a complicated hierarchy. I took a screenshot so you can see the structure:

state-xdata

xData.d.query.PrimaryQueryResult.RefinementResults.Refiners.results[0].Entries.results[0].RefinementCount gives me access to 3999 which is how many expenditures were issued to the Board of Regents agency.

RefinementName – the agency name from the data set
RefinementValue – the number of items that are set to that particular agency

3. Because I’m creating the table from data returned from the Search API – I needed to initiate a new DataTable for Google Charts and then iterate over my JSON data to fill in the cells.I told it how many rows I would have (the length of the results) and then I used a for loop to fill in the table values.

4. I set the title of the chart of “State Budget” but creating an Options object.

5. I then create a new variable called chart which initiates the google.visualization.ScatterChart function and pass it the div on the page where you want to render the chart.

Here’s what the Google chart looks like with all the state data loaded:

state-googlechart

Full HTML file on Github

state-html-file.PNG

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s