Help with Value Lists and Summaries.

[BrandonC]

Unfortunately, I'm the closest thing my department has to an IT guy. Hopefully this is a no-brainer to someone else, but it's killing me.

 

I have a database for patients and one for procedures - each patient can have multiple procedures. I have a value list that I defined for procedures:

 

Procedure A

Procedure B

Procedure C, etc...

 

When I run a summary report, I can get it to work fine for procedures that are entered (further divided by "planned" vs. "completed"). However, I really want a report to show a 0 where there is a null value. I can't figure out how to do this. If there are no Procedure Bs for example, the report will simply skip it. I understand why it does it, what I can't figure out is how to fix it. Is there any way to force all the value list line items to show up with a zero instead of getting skipped altogether? I'd be very appreciative of any help I could get.

[AHunter3]

It's kind of like the teacher asking "OK now will all the people NOT present please raise their hands and explain why they are not here today?"

 

It can be done (comment a) but the means by which you run such a report is totally not akin to how you run a traditional subsummary report. They have nothing in common. So if you want this capacity I will explain how to do it but it means starting over from scratch with your report. A subsummary report is never going to contain that data that isn't there.

[BrandonC]

I'm with you so far...I have no fond attachment to the old report. Even if its unconventional, I'm open to any ideas you have.

[BrandonC]

Not sure if my other reply went through or not, but if your link was exactly what I am trying to do. If you could show me what you did, you would be a hero to me.

[AHunter3]

In your Procedures Performed table (or whatever it's called) each record is a record some some procedure ("Procedure A" or "Procedure B" or whatever) being performed on a specific date? And you'd want to run your report for a specified date RANGE like July of '08 or all of 2008 or whatever?

 

OK create a new table, ReportLines. In that table create a text field, Procedure Name, a pair of date fields Start Date and End Date, and a number field ProcedureCount, and another number field Report ID.

 

Create also a table Reports with Serial Number, Run Date, Start Date, End Date, and Description.

 

In your Procedures Performed table create a calc field of result type "Number", name it "One" defined simply as 1

 

Create a relationship between Reports and ReportLines based on Reports::Serial Number = ReportLines::Report ID.

 

Create a relationship between ReportLines and Procedures Performed like so:

 

ReportLines::StartDate ≤ Procedures Performed::Date AND

ReportLines::EndDate ≥ Procedures Performed::Date AND

ReportLines::Procedure Name = Procedures Performed::Procedure Name

 

 

Create a value list of all values of the Procedures you wish to report on; this can be a custom value list where you simply type them all in, or you can create a table of Procedures where each record is a named Procedure; the procedures in Procedures Performed should draw their values from this value list however you create it. Let's say this value list is named "ProcedureList".

 

 

In layout mode, set up a Printable Reports layout like so:

 

Native table of Printable Reports = ReportLines

 

Info about the report up top from the parent Reports table: Reports::Start Date Reports::End Reports::RunDate and Reports::Description if you wish it.

 

Below that a skinny body part (you will be viewing it in List View. Or alternatively you could view this layout in Table View as long as you enable the header part in the Layout Setup / Views, so you can display the header fields from Reports.

 

In your body part you put:

 

ReportLines::Procedure Name; ReportLines::ProcedureCount

 

 

 

 

 

 

Now, finally, your report script:

 

Set Variable [$AllProcedures. "¶" & ValueListItems (Get(FileName), "ProcedureList" & "¶"]

Set Variable [$Pos, 1]

Go to Layout [Reports]

New Record

Set Field [Reports::Run Date, Get(CurrentDate)]

Show Custom Dialog ["Specify Date Range Please" provide field entry Report::Start Date and Report::End Date]

Set Variable [$ReportSerial, Reports::Serial Number]

Go to Layout [ReportLines]

Loop

Exit Loop If [GetAsNumber ($Pos)+1 > PatternCount ($AllProcedures, "¶")]

New Record/Request

Set Field [ReportLines::Report ID, $ReportSerial]

Set Field [ReportLines::Start Date, Reports::Start Date]

Set Field [ReportLines::End Date, Reports::End Date]

Set field [ReportLines::Procedure Name, Middle ($AllProcedures, Position ($AllProcedures, "¶", 1, $Pos)+1, Position ($AllProcedures, "¶", 1, $Pos+1) - Position ($AllProcedures, "¶", 1, $Pos)-1]

Set Field [ReportLines::ProcedureCount, Sum (Procedures Performed::One)

Commit Record

Set Variable [$Pos, $Pos + 1]

End Loop

Go to Layout [Reports]

Show Custom Dialog ["Specify a Report Description (optional)" provide field access to Reports::Description / OK]

Go to Related Records [show only related, ReportLines, using layout Printable Reports]

Print Setup [restore, no dialog]

Print

Go to Layout [original layout]

[BrandonC]

awesome....thank you so much

[AHunter3]

I received your db back-channel and sent it back with the necessary repairs, did you receive it?