Okay, here is my problem

I have a database structured as follows (to simplify it radically!)

Table: Matchfield

Tx_Modality_1:

Main:

Tx_Modality_2:

I want to report like this:

Number of patients by Tx Modality and Year ()

..........Jan.....Feb......(etc)

Tx_1....N_Jan...N_Feb

Tx_2....N_Jan...N_Feb

..etc

TOTAL...NJTot...NFTot

Alongside the ID variables in Tables Tx_1 and Tx_2 I also have:

-----(Date)

-----(Unstored Calc. =Year(Year_of_Admission) )

----(Unstored Calc. =Month(Year_of_Admission) )

-----------(Number, Unstored Calc. = Main::Year_Select, Field format:Dropdown list of years)

---------------(Unstored Calc. =Get(TotalRecordCount) )

------(Unstored Calc. = If(Check_Duplicates = "Unique"; 1; "") ...obviously I also have and with duplicate Tx tables...

--------------( Summary Count of Unique_Indicator)

I want a report layout (set up as above) that auto updates when a year is selected (using total Ns, not Unique Ns):

I thought I could:

(Unstored Calc. = If( (Month_of_Admission = 1) and (Year of Admission = Year_Select); Count(T1_ID);0)

(Which gives me with 1 entered whenever January is the month of admission for whatever year is selected. It works just fine - and I prospectively can repeat for both tables and all months (giving 24 variables in total)).

Then I want to get a total of these "1"s in to make fill into as a total.

I can do this by:

(Summary, Total of N_Jan_Indicator)

(Which gives me the total I want).

Now on the REPORT layout... do you think I can get to display the total? No I cannot!

...and of course the Grand totals will not display either.

I have tried getting as a calculation variable - but to no avail.

If I make it global (rather than unstored) ... it works once only, then does not update.

I am obviously missing something pretty basic here...but my brain must have got into some sort of loop that it cannot escape.

Any suggestions?

Edited August 18, 200916 yr by Guest
Changed thje "" in (Unstored Calc. = If( (Month_of_Admission = 1) and (Year of Admission = Year_Select); Count(T1_ID);"") to 0...otherwise does not update when different year is selected .

Okay... I think I have a "workaround" using scripts...

I create all the , , etc. and the , , etc. fields in the two Tx Tables - just as I stated above (actually I have 12 of these tables to do it in - but I was going to do that anyway...).

Then I create another table REPORT_Tx_1 with matchfield to Tx_1 = , and repeat all the as simple number fields (rather than Summary totals as above.

Then I create a script ("Number of Admissions by Treatment Modality and Year") which is structured as follows and is triggered by OnObjectModify in the field in the title if the report.

Go to Layout [("Tx_1" (Tx_1)]

Copy [select; Tx_1::N_Jan]

Go To Layout {REPORT: "Admissions by Tx Modality and Year" (Main)]

Paste [select; REPORT_Tx_1::N_Jan]

Go to Layout [("Tx_1" (Tx_1)]

Copy [select; Tx_1::N_Feb]

Go To Layout {REPORT: "Admissions by Tx Modality

Go To Layout {REPORT: "Admissions by Tx Modality and Year" (Main)]

Paste [select; REPORT_Tx_1::N_Feb]

{repeat 12 times for each month, then }

Go to Layout [("Tx_2" (Tx_2)]

Copy [select; Tx_2::N_Jan]

{etc for all Tx modalities

I have tested it on the first field in the first Tx modality table and it works. Now I will construct the whole thing for all Tx modalities (x12 ...Obviously I will be here for a few hours yet!) I DO hope it does not take too long to run the whole thing once constructed...

I wish I could have a simple calculation field do the job... maybe someone has a suggestion?(perhaps the script can be "normalized", but I just NEED to get a solution out ASAP and ANY solution is better than none for now)

Phew!