I have 3 diagnosis for which I want to get the total of all diagnosis recorded for patients in particular age groups. At first I was trying to add a row in a CohortCrossTabDataSetDefinition report where it had columns for each age/gender group and the rows where diagnosis and a cell contained the number of recorded diagnosis for each age group/gender. The table looked like below
The columns are a composition of gender and age cohort queries. And the rows CodedObsCohortDefinition for each diagnosis and the CohortCrossTabDataSetDefinition now get’s an intersection to produce the result as a Cohort.
Now I want to count the total number of diagnosis for a particular age/gender group. I tried adding a new row but the counts are not what I want. If person 1 is diagnosed for MALARIA and diabetes, the result is one which makes sense since just one patient has been added to the cohort. But I need to the result to be 2 reflecting that person 1 was diagnosed of 2 conditions.
The solution we decided to use was build separate report with just one row and then later combine the two reports to get the desired result.
Now I’m having a little trouble choosing a data set to use for the second report. I thought of using the sql data set where our report definition will be made up of multiple sql data sets for each age/gender category. But my concern with this approach is that I have to calculate age from birthdate inside the SQL query. This doesn’t sound like a very good idea.
Is it possible to use the AgeCohort together with the SQL Dataset? I mean like first filtering the patients using the age cohort and the sql data set is run on the result instead of running on the actual database table. Or which dataset is best for my use case?