We have mapped ‘Drug Exposure’ according to OMOP CDM format. However, we didn’t create ‘Drug_Era’ derived table. We would like to view the Dashboard ‘Tree Graph’ for ‘Drug Exposure’ table. However we don’t see any graph being generated. Can this be due to the fact that we din’t create ‘Drug_Era’ table? The data has been mapped successfully and ETL’ed as per OMOP CDM standards. Can anyone help us? Please note that we don’t get any error messages, instead it displays ‘No Data’ even though we have uploaded the data to database. On the other hand, we were able to generate reports under Dashboard section for all other tables.
Looks like your sources are set up correct if you see data in the other reports.
The drug_era reports are distinct from the drug_exposure reports, so if you don’t have drug_era populated, that will not impact the drug_exposure reports, so we can put the drug_era table aside for now.
this is the query for querying the treemap for drug exposures:
SELECT
concept_hierarchy.concept_id AS concept_id,
CONCAT(
isnull(concept_hierarchy.level4_concept_name, 'NA'), '||',
isnull(concept_hierarchy.level3_concept_name, 'NA'), '||',
isnull(concept_hierarchy.level2_concept_name, 'NA'), '||',
isnull(concept_hierarchy.level1_concept_name, 'NA'), '||',
concept_hierarchy.concept_name
) AS concept_path,
ar1.count_value AS num_persons,
round(1.0 * ar1.count_value / denom.count_value, 5) AS percent_persons,
round(1.0 * ar2.count_value / ar1.count_value, 5) AS records_per_person
FROM (SELECT *
FROM @results_database_schema.achilles_results WHERE analysis_id = 700) ar1
INNER JOIN
(SELECT *
FROM @results_database_schema.achilles_results WHERE analysis_id = 701) ar2
ON ar1.stratum_1 = ar2.stratum_1
INNER JOIN
@results_database_schema.concept_hierarchy concept_hierarchy
ON CAST(CASE WHEN isNumeric(ar1.stratum_1) = 1 THEN ar1.stratum_1 ELSE null END AS INT) = concept_hierarchy.concept_id
AND concept_hierarchy.treemap='Drug'
,
(SELECT count_value
FROM @results_database_schema.achilles_results WHERE analysis_id = 1) denom
ORDER BY ar1.count_value DESC
Replace these tokens with your own CDM/Results schema, and execute parts of the query to determine which part is not returning records.