Part Two: The Query

In my previous post, I described the business problem:

Finding filers who made “backwards calculations”. “Backwards” in the sense that they’ve said that us-gaap_ProfitLoss is a calculation parent of the element of us-gaap_NetIncomeLoss. Remember, the former includes income attributable to the noncontrolling interest, and the latter does not. This is backwards because accountants typically subtract us-gaap_NetIncomeLossAttributableToNoncontrollingInterest from us-gaap_ProfitLoss to arrive at a total of us-gaap_NetIncomeLoss. But the above calculation relationship suggests that us-gaap_ProfitLoss is the total. Wrong element.

Confused? I’ll cover that in Part Four. For now, bear with me. Assume it’s “backwards” to go from us-gaap_ProfitLoss to us-gaap_NetIncomeLoss. So, how do we find who’s backwards?

Enter the XBRL-US Database!
To find these filings, I ran this query. I’ve bolded the important parts.

SELECT relationship.relationship_id, accession.sec_html_url, uri.uri, entity.entity_name, relationship.tree_sequence, relationship.tree_depth
FROM relationship
INNER JOIN
network
ON
network.network_id= relationship.network_id
INNER JOIN
accession
ON
network.accession_id=accession.accession_id
INNER JOIN
uri
ON
network.extended_link_role_uri_id = uri.uri_id
INNER JOIN
entity
ON
accession.entity_id=entity.entity_id
WHERE
relationship.from_element_id=13572 AND
relationship.to_element_id= 16708
ORDER BY
accession.filing_date DESC

The first bold part, “FROM relationship” starts the Query by choosing one of many tables in the XBRL US database. This table holds a field (a row) for every relationship. That explains its title, “relationship”. Relationships are XBRL arcs, like a calculation arc.  A calculation arc connects two accounting concepts. While it’s called an “arc” (an XLink concept), it’s really just a connector. A connector in the sense of a network graph that goes from some resource, to another resource. As I explained above, the syntactical way to express that ProfitLoss is a “backwards” total of NetIncomeLoss, is an arc going from the former, to the latter.

The second bold part of the query, “WHERE relationship.from_element_id=13572 AND relationship.to_element_id=16708” is the condition for the results of the Query. “WHERE” filters the results, so I get rows that meet the conditions I specify. The IDs (from_element_id) represent the two elements (aka concepts) that are a part of every arc. Knowing that arcs go from some element, to another one, we can appreciate the name for their id’s: from_element_id, and to_element_id

But what does 13572 represent? Why can’t I say, WHERE relationship.from_element_id = ‘us-gaap_ProfitLoss‘? I can’t use the schema-defined XBRL element ID, provided by us-gaap when they created the schema because from_element_id refers to the XBRL US’ database ID. In the XBRL US database, this id represents a foreign key; an arbitrary but unique ID identifying this element. A “foreign key” means that it points to a different table to get the element information. After all, the relationship table doesn’t hold detailed information about each element — just about the relationship between elements. The foreign key is the index for elements in the “element” table. I was surprised to find that this element table contains more than one field for the id ‘us-gaap_ProfitLoss’. But then I realized, that the us-gaap_ProfitLoss defined in the 2009 taxonomy is technically different than the element with the same name in the 2011, or 2012 taxonomies. So it makes sense to have at least three different element rows in the table — one for each taxonomy. More on this later.

In a second-part followup, I’ll discuss the rest of the query, and the results.

In a third-part followup, I’ll discuss one theory for the confusion and “backwards” reporting– because I was confused, too!

In a fourth-part followup, I’ll discuss some of the actual findings when Phil actually inspected these “backwards” reporting — which surprised him.

Advertisements
Tagged , , , , , , , , , , , , , ,

One thought on “Part Two: The Query

  1. […] part one, I framed the business question. In part two, I described the query. A query on its own may not […]

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

%d bloggers like this: