formula evaluation after printing records initially

[lkh5650]

Hi I need some help..

so initially my report will be printed
and it will be divided into couple of groups
case ID
source
event seq #
drug

now I have to evaluate this formula
similarcases after it is all printed.
usually the reported is generated within the interest of time period

then within the generated reports, i wanna count similar cases
the logic behind counting similar cases is
within the same drug, different case id, if the event description (which is diff from event seq#) is the same/similar then increase the counter from 1 => 2 and print it beside both data rows..

so for instance, when the report is just generated, it will be like this
case id source event seq # event drug
abc L 1 dizzy clozapine
..
.
.
.
.
.
.
.
lhk L 3 dizzy clozapine


then after the evaluation, i want to generate the report again but with added column called similar cases


case id source event seq # event drug similar
abc L 1 dizzy clozapine 2
..
.
.
.
.
.
.
.
lhk L 3 dizzy clozapine 2


I hope someone can help me out in this..

thank you

 

[lbass]

I think the simplest approach would be to add a subreport that is linked on drug and the event description and which uses the same date interval. Then you could do a distinctcount of case ID. Suppress all sections of the sub except the one containing the result, and then place the sub in the detail section.

If you aren't using a ton of tables, you might be able to accomplish the same thing with a SQL expression, but the dates used could not be based on a parameter.

-LB

 

[lkh5650]

Thank you for the reply
and I tried the way you told me to do
and it works perfectly

However, when I try to export the data out to excel format it takes a very very long time... is there anyway I can try to shorten this time?
I think the problem is, the sub reported is generated for each row of data and there can be as many as thousands of rows
and for each row, the subreport will access the data base
any thoughts?

 

[lbass]

You could try the SQL expression approach, if your table structure isn't too complicated and if your selection formula doesn't require parameters. Or you could create a command that would pull this info, but if you link on it, the linking will occur locally, and again, would take a long time.

You could decide to use a command as your sole datasource and build in the calculation in a subquery--again if it isn't too complex. Maybe you should post your SQL query (database->show SQL query) so we can get an idea of the best approach.

-LB

 

[lkh5650]

Hi
Here is the sql query... it is very long

===========

SELECT DISTINCT "CASE_LIST_DTLS"."CASE_LIST_NAME", "CASE_LIST_DTLS"."PICKED_FLAG", "AE_CASES"."CASE_ID", "AE_REPORT_SOURCES"."RPT_SOURCE_TYPE", "AE_CASES"."EV_OCC_COUNTRY_CD", "AE_CASES"."CASE_CREATE_DT", "AE_CASES"."PATIENT_ID", "AE_CASES"."OTH_PATIENT_ID", "AE_CASES"."PT_SEX_CD", "AE_CASES"."PT_DOB", "AE_CASES"."PT_AGE", "AE_CASES"."PT_AGE_UNIT", "AE_CASES"."MEDICAL_ERROR_FLAG", "AE_CASES"."MEDICAL_ERROR_TYPE", "AE_EVENTS"."MEDICALLY_CONFIRMED_FLAG", "AE_EVENTS"."SERIOUS_FLAG", "AE_CASES"."CORE_UNEXPECTED_FLAG", "AE_CASES"."US_UNEXPECTED_FLAG", "AE_CASES"."UNEXPECTED_IN_ORIG_COUNTRY", "AE_SUSPECT_DRUGS"."DRUG_COMPANY_FLAG", "AE_SUSPECT_DRUGS"."SUSP_DRG_SEQ_NBR", "DE_SUSPECT_DRUGS"."DRUG_NAME", "DE_SUSPECT_DRUGS"."PREFERRED_NAME", "AE_CASES"."SERIOUS_FLAG", "AE_EVENTS_TO_DRGS"."CORE_UNEXPECTED_FLAG", "AE_EVENTS_TO_DRGS"."UNEXPECTED_IN_ORIG_COUNTRY", "AE_EVENTS_TO_DRGS"."US_UNEXPECTED_FLAG", "AE_EVENTS_TO_DRUGS_DETAILS"."ASSESSMENT", "AE_EVENTS_TO_DRUGS_DETAILS"."ASSESSMENT_SOURCE", "AE_EVENTS"."DISPLAY_NBR", "AE_EVENTS"."EVENT_LLT", "AE_EVENTS"."PREFERRED_EVENT_TERM", "AE_EVENTS"."MID_LVL_TERM", "AE_EVENTS"."SUB_BODY", "AE_EVENTS"."BODY_SYSTEM", "AE_CASES"."PROJECT_DRUG_NAME", "AE_DOSE_LOTS"."EXPIRATION_DT", "PC_INVESTIGATIONS"."TRACKING_NUMBER", "PC_INVESTIGATIONS"."INVESTIGATION_INSTRUCTIONS", "PC_INVESTIGATIONS"."FINDINGS", "AE_SUSPECT_DRUGS"."DRUG_INTERACTION_FLAG", "AE_CASES"."PT_AGE_YRS", "AE_CASES"."PT_AGE_GROUP", "AE_CASES"."FIRST_RCVD_DT", "CODE_LIST_DTLS_COUNTRY_GROUP"."LANG_CD", "CODE_LIST_DTLS_COUNTRY_GROUP"."CODE_LIST_NAME", "CODE_LIST_DTLS_COUNTRY_GROUP"."CODE_VALUE_TEXT", "AE_CASES"."PREGNANCY_EXPOSURE_FLAG", "CODE_LIST_DTLS_AGE_GROUP"."CODE_LIST_NAME", "CODE_LIST_DTLS_AGE_GROUP"."CODE_VALUE_TEXT", "CODE_LIST_DTLS_AGE_GROUP"."LANG_CD", "CASE_OUTCOME_DESC"."LANG_CD", "CASE_OUTCOME_DESC"."CODE_LIST_NAME", "CASE_OUTCOME_DESC"."CODE_VALUE_TEXT", "AE_SUSPECT_DRUGS"."INVESTIGATION_NOT_REQ_FLAG", "AE_SUSPECT_DRUGS"."INVESTIGATION_NOT_REQ_REASON", "AE_CASES"."PRODUCT_COMPLAINT_FLAG", "AE_CASES"."PRODUCT_PROBLEM_FLAG", "AE_SUSPECT_DRUGS"."FDA_MFG_SITE_ID", "AE_CASES"."LACTATION_EXPOSURE_FLAG", "AE_CASES"."PT_PREG_STS", "CODE_LIST_DTLS_PT"."CODE_LIST_NAME", "CODE_LIST_DTLS_LLT"."CODE_LIST_NAME", "AE_CASES"."LF_THREAT_FLAG", "PC_INVESTIGATIONS"."INVESTIGATION_STATUS", "AE_CASES"."DIED_FLAG", "AE_DOSE_LOTS"."LABEL_LOT_NUMBER", "AE_DOSE_LOTS"."LOT_SEQ_NBR", "AE_DOSE_LOTS"."DSG_SEQ_NBR", "AE_EVENTS"."EVENT_SEQ_NBR", "PC_INVESTIGATIONS"."INVESTIGATION_SEQ_NBR", "COMPANY_DRUGS_REG"."PROD_CD"
FROM (((((((((((((((("ENSITE"."CASE_LIST_DTLS" "CASE_LIST_DTLS" LEFT OUTER JOIN "ENSITE"."AE_CASES" "AE_CASES" ON "CASE_LIST_DTLS"."CASE_ID"="AE_CASES"."CASE_ID") LEFT OUTER JOIN "ENSITE"."AE_REPORT_SOURCES" "AE_REPORT_SOURCES" ON "AE_CASES"."CASE_ID"="AE_REPORT_SOURCES"."CASE_ID") LEFT OUTER JOIN "ENSITE"."AE_SUSPECT_DRUGS" "AE_SUSPECT_DRUGS" ON "AE_CASES"."CASE_ID"="AE_SUSPECT_DRUGS"."CASE_ID") LEFT OUTER JOIN "ENSITE"."PC_INVESTIGATIONS" "PC_INVESTIGATIONS" ON ("AE_CASES"."CASE_ID"="PC_INVESTIGATIONS"."CASE_ID") AND ("AE_SUSPECT_DRUGS"."SUSP_DRG_SEQ_NBR"="PC_INVESTIGATIONS"."SUSP_DRG_SEQ_NBR")) LEFT OUTER JOIN "ENSITE"."AE_DOSE_LOTS" "AE_DOSE_LOTS" ON "AE_CASES"."CASE_ID"="AE_DOSE_LOTS"."CASE_ID") LEFT OUTER JOIN "ENSITE"."AE_EVENTS" "AE_EVENTS" ON "AE_CASES"."CASE_ID"="AE_EVENTS"."CASE_ID") LEFT OUTER JOIN "ENSITE"."AE_EVENTS_TO_DRGS" "AE_EVENTS_TO_DRGS" ON (("AE_CASES"."CASE_ID"="AE_EVENTS_TO_DRGS"."CASE_ID") AND ("AE_SUSPECT_DRUGS"."SUSP_DRG_SEQ_NBR"="AE_EVENTS_TO_DRGS"."SUSP_DRG_SEQ_NBR")) AND ("AE_EVENTS"."EVENT_SEQ_NBR"="AE_EVENTS_TO_DRGS"."EVENT_SEQ_NBR")) LEFT OUTER JOIN "ENSITE"."AE_EVENTS_TO_DRUGS_DETAILS" "AE_EVENTS_TO_DRUGS_DETAILS" ON (("AE_CASES"."CASE_ID"="AE_EVENTS_TO_DRUGS_DETAILS"."CASE_ID") AND ("AE_SUSPECT_DRUGS"."SUSP_DRG_SEQ_NBR"="AE_EVENTS_TO_DRUGS_DETAILS"."SUSP_DRG_SEQ_NBR")) AND ("AE_EVENTS"."EVENT_SEQ_NBR"="AE_EVENTS_TO_DRUGS_DETAILS"."EVENT_SEQ_NBR")) LEFT OUTER JOIN "ENSITE"."DE_SUSPECT_DRUGS" "DE_SUSPECT_DRUGS" ON ("AE_CASES"."CASE_ID"="DE_SUSPECT_DRUGS"."CASE_ID") AND ("AE_SUSPECT_DRUGS"."SUSP_DRG_SEQ_NBR"="DE_SUSPECT_DRUGS"."SUSP_DRG_SEQ_NBR")) LEFT OUTER JOIN "ENSITE"."CODE_LIST_DTLS" "CODE_LIST_DTLS_COUNTRY_GROUP" ON "AE_CASES"."EV_OCC_COUNTRY_CD"="CODE_LIST_DTLS_COUNTRY_GROUP"."CODE") LEFT OUTER JOIN "ENSITE"."CODE_LIST_DTLS" "CODE_LIST_DTLS_AGE_GROUP" ON "AE_CASES"."PT_AGE_GROUP"="CODE_LIST_DTLS_AGE_GROUP"."CODE") LEFT OUTER JOIN "ENSITE"."CODE_LIST_DTLS" "CASE_OUTCOME_DESC" ON "AE_CASES"."OUTCOME_CD"="CASE_OUTCOME_DESC"."CODE") LEFT OUTER JOIN "ENSITE"."COMPANY_DRUGS" "COMPANY_DRUGS_REG" ON "DE_SUSPECT_DRUGS"."REGULATORY_GROUP_NAME"="COMPANY_DRUGS_REG"."DRUG_NAME") LEFT OUTER JOIN "ENSITE"."V_EVENTS" "V_EVENTS_PT" ON "AE_EVENTS"."PREFERRED_EVENT_TERM"="V_EVENTS_PT"."PT") LEFT OUTER JOIN "ENSITE"."V_EVENTS" "V_EVENTS_LLT" ON "AE_EVENTS"."EVENT_LLT"="V_EVENTS_LLT"."LLT") LEFT OUTER JOIN "ENSITE"."CODE_LIST_DTLS" "CODE_LIST_DTLS_LLT" ON "V_EVENTS_LLT"."LLT_CODE"="CODE_LIST_DTLS_LLT"."CODE") LEFT OUTER JOIN "ENSITE"."CODE_LIST_DTLS" "CODE_LIST_DTLS_PT" ON "V_EVENTS_PT"."PT_CODE"="CODE_LIST_DTLS_PT"."CODE"
WHERE "CASE_LIST_DTLS"."CASE_LIST_NAME"='GPV CREATED 20110101-20110131' AND "AE_SUSPECT_DRUGS"."DRUG_COMPANY_FLAG"<>'N' AND "CODE_LIST_DTLS_COUNTRY_GROUP"."LANG_CD"='ENG' AND ("CODE_LIST_DTLS_AGE_GROUP"."CODE_LIST_NAME" IS NULL OR "CODE_LIST_DTLS_AGE_GROUP"."CODE_LIST_NAME"='AGE_GROUP_LIST' AND "CODE_LIST_DTLS_AGE_GROUP"."LANG_CD"='ENG') AND ("CODE_LIST_DTLS_COUNTRY_GROUP"."CODE_LIST_NAME" IS NULL OR "CODE_LIST_DTLS_COUNTRY_GROUP"."CODE_LIST_NAME"='COUNTRIES') AND ("CASE_OUTCOME_DESC"."CODE_LIST_NAME" IS NULL OR "CASE_OUTCOME_DESC"."CODE_LIST_NAME"='OUTCOME_LIST' AND "CASE_OUTCOME_DESC"."LANG_CD"='ENG') AND "CASE_LIST_DTLS"."PICKED_FLAG"='Y'
ORDER BY "AE_CASES"."CASE_ID"

======================
Thanks!

 

[lbass]

Which field is the events description field?

-LB

 

[lkh5650]

AE_EVENTS.BODY_SYSTEM <= event description
COMPANY_DRUGS_REG.PROD_CD <= drug


also three of these are used in sub reports to count similar cases by mid lvl term/pt term/sub body term
AE_EVENTS.MID_LVL_TERM
AE_EVENTS.PREFERRED_EVENT_TERM
AE_EVENTS.SUB_BODY

but its like this
pt term < mid level term < sub body < body system
hmm like.. if the case a and b is counted as similar case by pt term, then it will be also counted as mid level term, sub body yerm and body system term.

and this is my sql query for specific row for my sub report

SELECT DISTINCT "AE_EVENTS"."BODY_SYSTEM", "COMPANY_DRUGS_REG"."PROD_CD", "AE_CASES"."CASE_ID", "AE_CASES"."DELETE_FLAG", "AE_EVENTS"."PREFERRED_EVENT_TERM", "AE_EVENTS"."SUB_BODY", "AE_EVENTS"."MID_LVL_TERM"
FROM (("ENSITE"."AE_CASES" "AE_CASES" LEFT OUTER JOIN "ENSITE"."AE_EVENTS" "AE_EVENTS" ON "AE_CASES"."CASE_ID"="AE_EVENTS"."CASE_ID") LEFT OUTER JOIN "ENSITE"."DE_SUSPECT_DRUGS" "DE_SUSPECT_DRUGS" ON "AE_CASES"."CASE_ID"="DE_SUSPECT_DRUGS"."CASE_ID") LEFT OUTER JOIN "ENSITE"."COMPANY_DRUGS" "COMPANY_DRUGS_REG" ON "DE_SUSPECT_DRUGS"."REGULATORY_GROUP_NAME"="COMPANY_DRUGS_REG"."DRUG_NAME"
WHERE "AE_EVENTS"."BODY_SYSTEM"='Nervous system disorders' AND "COMPANY_DRUGS_REG"."PROD_CD"='TAMOXIFEN' AND ("AE_CASES"."DELETE_FLAG" IS NULL OR "AE_CASES"."DELETE_FLAG"='N')

on the report footer, i just perform distinct count on case id for body system, sub body, mid lvl term, and pt term

 

[lbass]

I think this is too complex to be a candidate for a SQL expression. I didn't even really see a field that you are calling event description--it appears to be a series of fields.

-LB

 

[lkh5650]

aw.. that sucks..

Thanks !