Results display on seaparate rows and parameter search problems 1

[tellamugali]

I am using Crystal Reports 8.5 to query a Pathology Information system written in cache, I am a newbie to Crystal reports and any knowlege I have is either from trial and error or thanks to Tek-Tips.

I am using the below formulae extract data my client wants from a patient episode. I am extracting the result (VISTD_TestData) based on the contents of another field in the table (TestCode_DR). I separated into separate Formula Fields for troubleshooting purposes.

@ProdImpCheck
{EP_VisitTestSetData.VISTD_TestCode_DR} in "B0056"
@Product
If {@ProdImpCheck} THEN (EP_VisitTestSetData.VISTD_TestData}

@ResCheck
{EP_VisitTestSetData.VISTD_TestCode_DR} in "B3500"
@Result
If {@ResCheck} THEN {EP_VisitTestSetData.VISTD_TestData}

@TimeCheck
{EP_VisitTestSetData.VISTD_TestCode_DR} in "B3516"
@HTime
If {@TimeCheck} Then {EP_VisitTestSetData.VISTD_TestData}
@Time
local stringvar tim2txt:=totext({@HTime});
local stringvar tim2num:= if istime(tim2txt) then tim2txt else "";
if tim2num <> "" then ctime(tim2num)

@DateCheck
{EP_VisitTestSetData.VISTD_TestCode_DR} in "B3517"
@Hdate
If {@DateCheck} THEN {EP_VisitTestSetData.VISTD_TestData}
@Date
//Converts date from horlog to date format
local stringvar hor2txt:=totext({@HDate});
local datevar horstart:=date(1840,12,31);
local numbervar hor2num:=if isnumeric(hor2txt) then tonumber(hor2txt) else 0;
if hor2num>0 then horstart+hor2num

I have 3 problems:

1.The results are displayed on separate rows for the group, grouped by Episode

Episode____Time____Date___Product___Result___Comment
Epis1
_____________________________________Res1____Blob or Memo
__________________________Prod1
__________________21/12/2006
__________15:00PM


I have tried the solution posted by synapsevampire as a response to “Repeating Rows” and modifying it for my report.

Group header (used to reset it)
whileprintingrecords;
stringvar test:=””
@test
whileprintingrecords;
stringvar test:
If {@ResCheck} THEN {EP_VisitTestSetData.VISTD_TestData}
Now in the group footer use:
whileprintingrecords;
stringvar test

The problem is that the group footer is displaying that which I define in the group header, I have confirmed this by setting the header formula to stringvar test:= “testing”. @Test in the details area is the same as @Results (as expected), but does not repeat for the whole group, another complication is that all of the formula fields and the Comments field (Blob\Memo) can have multiple results for a group.

2. I am using most of the fields for parameter searches below is the formula thanks to Tek-Tips forums and members, however if I search by ?Time Period the result is only that one field displaying in the group and none of the other information, as opposed to searching by ?HRN or ?Site which are basic {table.fields} and not using an if statement in the formula.

3. If I search by ?Date Period I get an ODBC database error.

Report>Record Formula
{EP_VisitTestSet.VISTS_TestSet_DR} = "B350"and
(
if {?Site} <> "ALL" then
{EP_VisitNumber.EPVIS_VisitNumber} startswith {?Site} else
if {?Site} = "ALL" then
true
) and
(
if {?HRN} <> "ALL" then
{EP_VisitNumber.EPVIS_DebtorNumber_DR} = {?HRN} else
if {?HRN} = "ALL" then
true
) and
(
if minimum({?Date Period}) <> date(9999,09,09) then
{@Date} = {?Date Period} else
if minimum({?Date Period}) = date(9999,09,09) then
true
) and
(
if minimum({?Time Period}) <> time(11,11,11) then
{@Time} = {?Time Period} else
if minimum({?Time Period}) = time(11,11,11) then
true
)

 

[synapsevampire]

Where your concatenation for this:

I have tried the solution posted by synapsevampire as a response to “Repeating Rows” and modifying it for my report.

Group header (used to reset it)
whileprintingrecords;
stringvar test:=””

Presumably you meant this is in the details, change it to:
@test
whileprintingrecords;
stringvar test:
If {@ResCheck} THEN
test:=tes & {EP_VisitTestSetData.VISTD_TestData}

Now in the group footer use:
whileprintingrecords;
stringvar test

You need to concatenate the values into the test variable.

Crystal isn't responsible for ODBC errors, it means that you're passing something the ODBC incorretly handles, so you need to resolve that accordingly. Since you decide not to post what the error is, you must not want us to address it.

As for the dates, presumably you wish to show all rows for a given set of information providing one of the dates matches the date your passing, or the last date, or the first date, or?

You state incorrect behaviors, but you don't state the required.

Not sure why the VISTS_TestSet_DR is hardcoded.

Record selection->Record;
{EP_VisitTestSet.VISTS_TestSet_DR} = "B350"and
(
if {?Site} <> "ALL" then
{EP_VisitNumber.EPVIS_VisitNumber} startswith {?Site} else
if {?Site} = "ALL" then
true
) and
(
if {?HRN} <> "ALL" then
{EP_VisitNumber.EPVIS_DebtorNumber_DR} = {?HRN} else
if {?HRN} = "ALL" then
true
)

Record seletion->Group

(
if minimum({?Date Period}) <> date(9999,09,09) then
{@Date} = {?Date Period} else
if minimum({?Date Period}) = date(9999,09,09) then
true
) and
(
if minimum({?Time Period},{table.group}) = time(11,11,11) then
minimum({@Time},{table.group) = {?Time Period} else
if minimum({?Time Period}) = time(11,11,11) then
true
)

Perhaps the above will work, which should return all rows where the minimum date matches what was entered. Note that part of the record selection isin the record area, the latter which needs to be tweaked for your grouping field, is in the group area of the record selection.

-k

 

[tellamugali]

Hi Synapsevampire thanks for the response and apologies for the slow feedback.

The concatenation has fixed the basic fields that are straight out of a table but I have not managed to get the date and time fields reporting on the same line, I suspect this may be a lost cause due to the “blob memo” field, which I will not be able to apply any formulas to, however I would like to crack it for future reports.

@header
whileprintingrecords;
stringvar result:="";
stringvar ProdImp:="";
timevar mytime:=ctime(0,0,0)

@TimeDetails
local stringvar tim2txt;
local numbervar tim2num;
timevar mytime;
tim2txt:=totext({@HTime});
tim2num:=if istime(tim2txt) then tonumber(tim2txt) else 0;
if tim2num <> 0 then (mytime + tim2num)

@TimeFooter
whileprintingrecords;
timevar mytime

I get the String is not numeric for local stringvar tim2txt;
Below is the original formula that was working but displaying on a separate line.

@OldTime
local stringvar tim2txt;
local stringvar tim2num;
tim2txt:=totext({@HTime});
tim2num:= if istime(tim2txt) then tim2txt else "";
if tim2num <> "" then ctime(tim2num)

Also

(
if minimum({?Time Period},{EP_VisitNumber.EPVIS_VisitNumber}) = time(11,11,11) then
minimum({@Time},{EP_VisitNumber.EPVIS_VisitNumber}) = {?Time Period} else
if minimum({?Time Period}) = time(11,11,11) then
true
)

returns an error that Too Many Arguments have been given to this function and points to the minimum function.

 

[synapsevampire]

You might be able to use a SQL Expression against the blob fields.

Perhaps you should have posted your database type and connectivity rather than the prgramming language the application is written in since all of your issues are involved with extracting data and we don't care what language the app is written in, it has NO impact on anything...

Or do you mean that the data is Intersytems Cache database?

...

-k

 

[tellamugali]

Yeah

Database is Intersytems Cache database and I have no control over field lengths etc: it is managed by a 3rd party vendor. It took about 6 months to get 1 field length changed and I had to troubleshoot the problem with Intersystems not the vendor.

Connection is via an ODBC System DSN Data Source using Intesystems ODBC 4.00.836.00 driver as we are still running Cache 3.0 FDBMS database

I have attempted many of the posted solutions to formlas with "Blob Memo" fields including SQL expressions with no luck.