Select based on Missing Field Value

[v45]

I need to develop a report where patients are listed that do not have a treatment plan developed. I have put together a report that lists patients based on intake date with columns for Patient ID, Episode ID, Name and Completed Documents. Episode is usually the number of times the patient has been opened for service. The only patients that should be listed are those without a treatment plan.

I've not written a formula with "If" or variables, so I'm really screwing this up. I was trying to put it in the detail supress area. So far I have:

WhilePrintingRecords;

stringVar Id := {Patient.patient_id};
stringVar Episode := {Patient.episode_id};
stringVar Print := "Yes"

while Id := next({Patient.patient_id} and Episode := Next({Patient.episode_id}) do
stringVar Document := {Document.name};
If Document <> &quot;Treatment Plan&quot; then Print := &quot;Yes&quot;;

Can someone point me in the right direction, or in another direction?

Thanks.

 

[butkus]

Write a formula that tests {Document.name} for null or anything other than &quot;Treatment Plan&quot; and return that to the report in place of the value contained in {Document.name}

example
Formula Name = DocName Test

If isnull({Document.name}) then &quot;No Treatment Plan&quot; else
If {Document.Name} <> &quot;Treatment Plan&quot; then {Document.Name}

The rest of your report can call the other element you listed.

James

 

[RobHVB6Sql]

Real answer not Integer please.

I have written a stored procedure that should be simple.
The problem is I get an integer answer (0 or 1) when dividing two real variables -I want a two decimal place answer, you can see I have experimented with CAST and data types and I doubt I need as many variables as I have.

I have cheated and set values in the proc below so you can run it. The answer should be 66.6% (1000/1500).
Any help appreciated, thanks. (Using SQL 7.0 /T-SQL)

/*********************************************************/
/* spShowConvert */
/* Creates print statements to show conversion progress. */
/*********************************************************/
if exists (select * from sysobjects where id = object_id('spShowConvert') and sysstat & 0xf = 4)
drop procedure spShowConvert
GO

PRINT 'Creating spShowConvert stored procedure'
GO

CREATE PROCEDURE spShowConvert
(
@FromTable VARCHAR(50)
)
AS

DECLARE @ToTable VARCHAR(50)
DECLARE @NoToConvert AS float(24)
DECLARE @NoConverted AS float(24)
DECLARE @TotalRowsToConvert AS float(24)
DECLARE @PercentageDone AS float(24) --INTEGER

IF @FromTable = 'tProcedureCOPY'
BEGIN
SELECT @NoToConvert = COUNT(ProcID) FROM tProcedureCOPY
-- CHEAT FOR tek-tips below
SELECT @NoToConvert = 5000
SELECT @ToTable = 'tblProcedure'
SELECT @NoConverted = COUNT(ProcedureID) FROM tblProcedure
-- CHEAT FOR tek-tips below
SELECT @NoConverted = 10000
END

/* Show the results */
PRINT ''
PRINT 'Records to convert (' + @FromTable + ') :' + STR(@NoToConvert)
PRINT 'Records converted (' + @ToTable + ') :' + STR(@NoConverted)
SELECT @TotalRowsToConvert = (@NoConverted + @NoToConvert)
SELECT @TotalRowsToConvert = CAST(@TotalRowsToConvert AS float(24))
PRINT '------------------------------------------------------------'
PRINT 'Total Rows To Convert :' + STR(@TotalRowsToConvert)
SELECT @NoConverted = CAST(@NoConverted AS float(24))
--SELECT @PercentageDone = @NoConverted / @TotalRowsToConvert
SELECT @PercentageDone = CAST(@NoConverted AS float(24)) / CAST(@TotalRowsToConvert AS float(24))
SELECT @PercentageDone = ROUND(@PercentageDone,4)
PRINT 'Percentage Done :' + STR(@PercentageDone) + '%'
GO

spShowConvert 'tProcedureCOPY'

Rob Hasard
Data Manager -Genetic Services
(VB6 /SQL 7.0)

 

[dgillz]

Rob,

One, I do not see how your post relates to this thread. Two, is this a crystal reports question? It does not look like it.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Madawc]

butkus's suggestion would certainly work. But if all you need are patients without a treatment plan, it ought to be possible to test for this on the selection formula, using
If isnull({treatment.whatever})

Madawc Williams
East Anglia, Great Britain