Connecting facts with statements (SQL)

[David Tauriello]

A Public Filings Database User writes:

I wonder if there is a flag that differentiates among the Balance Sheet, Income Statement and Cash flow statements in the data base?. Is there a way to tie each value in the fact table with one of the statements?

The relationship table holds information about the structure of the report (sections, order of line items as defined in the presentation linkbase), and the fact table holds the data corresponding to the concepts that are the report’s line items.

One of the recent enhancements to the XBRL Filed Data spreadsheet extension was the addition of a new custom function – =XBRL.showSQL() – that translates XBRL API queries back into SQL statements that can be used with the Public Filings Database.

You can use the Full ESEF or SEC Report template (https://xbrl.us/wp-content/uploads/2022/08/FullReport-DimensionPivot-Template.xlsx) to generate the following queries for facts and relationship. The data rows are ‘lined up’ in the report with a lookup on concept.local-name which is synonymous with relationship.target-name (fact.element_local_name and el_qname.local_name in the SQLs). Hopefully, that gets you on the path to a single SQL query that accomplishes your goal.

SQL #1

SELECT  (fact.fact_value)::text AS "fact.value"
    							    , (fact.element_local_name)::varchar AS "concept.local-name"
    							    , (re.is_base)::boolean AS "concept.is-base"
    							    , (fact.fact_id)::int AS "fact.id"
    							    , (fact.fiscal_year)::int AS "period.fiscal-year"
    							    , (fact.fiscal_period)::text AS "period.fiscal-period"    							    
				 FROM fact
				 JOIN element AS element ON fact.element_id = element.element_id
				 JOIN report AS fact_report ON fact.accession_id = fact_report.report_id
				 JOIN report_element AS re ON fact.element_id = re.element_id AND re.report_id = fact_report.report_id AND re.report_id = fact.accession_id				 
	 WHERE  fact_report.report_id  in ('496120')
			ORDER BY  (fact.element_local_name)::varchar ASC

SQL #2

SELECT  (target_qname.local_name)::varchar AS "relationship.target-name"
    							    , (dts_relationship.to_element_id)::int AS "relationship.target-concept-id"
    							    , (label_uri.uri)::varchar AS "relationship.preferred-label"
    							    , (rel_dts_network.description)::varchar AS "network.role-description"
    							    , (dts_relationship.tree_sequence)::int AS "relationship.tree-sequence"
    							    , (dts_relationship.tree_depth)::int AS "relationship.tree-depth"    							    
				 FROM dts_relationship
				 JOIN element AS to_element ON dts_relationship.to_element_id = to_element.element_id
				 JOIN qname AS target_qname ON to_element.qname_id = target_qname.qname_id
				 LEFT JOIN uri AS label_uri ON dts_relationship.preferred_label_role_uri_id = label_uri.uri_id
				 JOIN dts_network AS rel_dts_network ON rel_dts_network.dts_network_id = dts_relationship.dts_network_id
				 JOIN qname AS el_qname ON rel_dts_network.extended_link_qname_id = el_qname.qname_id				 
	 WHERE  rel_dts_network.dts_id  in ('624413') AND  LOWER(el_qname.local_name)  in ('presentationlink')
			ORDER BY  (rel_dts_network.description)::varchar ASC,  (dts_relationship.tree_sequence)::int ASC

The raw output from =XBRL.showSQL() can be cleaned and ready to use by wrapping the formula in =CLEAN() – this will remove the beginning and ending quotes and change all double quotes to singles … just remove the offset and limit and you’re good to go.

[Author]

Posts