Too many arguments error

[reitter]

I'm using Crystal 9.0 with a SQL database. My end result is a 3 page form with fields that would be filled in based on specific data elements. So far I've made my form in the report header using 3 sections with a new page after each section.
I have 1 group in the report which I've hidden and I've hidden the detail segments.

Here is my SQL statement:

SELECT DISTINCT "PcsPocWorkTdResults"."QueryID", "AdmVisits"."AccountNumber", "AdmVisits"."Name", "PcsPocWorkTdResults"."DateTime", "PcsPocWorkTdResults"."Response", "PcsPocWorkTdResults"."VisitID"
FROM "livedb"."dbo"."AdmVisits" "AdmVisits" INNER JOIN "livedb"."dbo"."PcsPocWorkTdResults" "PcsPocWorkTdResults" ON ("AdmVisits"."VisitID"="PcsPocWorkTdResults"."VisitID&quot AND ("AdmVisits"."SourceID"="PcsPocWorkTdResults"."SourceID&quot
WHERE &quot;AdmVisits&quot;.&quot;AccountNumber&quot;='V00015867534' AND &quot;PcsPocWorkTdResults&quot;.&quot;Response&quot;<>''
ORDER BY &quot;PcsPocWorkTdResults&quot;.&quot;QueryID&quot;


The formula works fine if there is only one response. However most of the data I want on the form has multiple entries and I'm only interested in the last entry. I've made the following formula:


if {PcsPocWorkTdResults.QueryID} = &quot;APS.ACTAB2&quot;and
{PcsPocWorkTdResults.DateTime}=MAXIMUM({PcsPocWorkTdResults.DateTime},{PcsPocWorkTdResults.QueryID})
then {PcsPocWorkTdResults.Response}

Which works great if the formula is placed in the detail or even group sections.

I've tried moving my form pages to the report footer so that the formula is evaluated first with no luck.

My next thought is an evaluation statement but when I use them I get the following error:

&quot;Too many arguments have been given to this funciton&quot;


Does anyone have any thoughts on how to do this?

 

[synapsevampire]

By SQL Database, do you mean SQL Server?

If so, there is a select top N with SQL Server, so something like:

select top 1 * from table order by &quot;PcsPocWorkTdResults&quot;.&quot;VisitID&quot; descending

Might handle this.

Or use whatever field for the descending sortation that demonstrates the last row.

-k

 

[Ngolem]

you say that you are only interested in the last record of a group .... am I correct there?

If so the simple solution is to suppress the detail section and do all of your display in the group footer. This is the last record of the group....if you wanted the first record then use the group header.

there isn't any need to get rid of the unwanted data ... unless there is a requirement for a faster report.

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[reitter]

Thanks for both of these answers. Neither of these options work. By using group1, I'll end up with approximately 40+ groups, so the data in the group footer continues to repeat. I've also tried the topN funtion and I can't get that to give me any data.

I've put the TopN formula:

if {PcsPocWorkTdResults.QueryID} = &quot;CARD.CAP&quot; and
NthLargest (1, ({PcsPocWorkTdResults.DateTime})) = {PcsPocWorkTdResults.DateTime} then
({PcsPocWorkTdResults.Response})

The formula editor says has &quot;no errors&quot; and I've put it in the report header, page header, group header, detail, group footer, and both page and report footers and I don't get any data.

I'm about ready to say this can't be done, but it seems like there must be some way to pull the data.

Thanks, Kim

 

[Ngolem]

well other than an sql formula you don't really describe your report to us.

Show us how your report is grouped and a sample of the current report and another sample of how you really want it to look.....then MAYBE we can give a better answer

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[reitter]

Right now my report looks like

rh section A

Logo

field <patient name>

various manual fields to be filled in after printing.
(new page after)
rh section B

Name: field <patient name>
Does the patient have a living will? field<yes/no>
Does the patient have an IV? field <yes/no>
How many people does it take to move patient? field <#>

A whole page of similar questions that we already capture the answers to. The idea of this form is to keep the nurses from duplicating work.

(new page after)

rh section C
Name: field <patient name>
Mental Status: field <mental status>
Does the patient ambulate? field <ambulation>

Again another page of similar questions that I want to automatically fill in.

Group1 Header (hidden)(PcsPocWork.QueryID) each of the above fields is one of this group. With potentially mutiple answers based on the date and time of the assessment.

details (hidden)
DateTime QueryResponse FormulaField=(last response)

The Formula field works if it is in the detail section. But when I move the formula field to the corresponding area on the form it doesn't show up.

GroupFooter1 (hidden)
Page Footer (hidden)
Report Footer (hidden)


When I put the form sections into group footer I get 1 answer per page and the form repeats on the next page with the next answer filled in.

Detail also repeats for each answer. I've tried this in the page footer


Currently my formulas only want to pull data when they are in the detail section. Is there another way to automatically fill in a form??


I only want the 3 pages of questions and answers to print.


Thanks, Kim

 

[synapsevampire]

Sorry, this all conflicts in my mind, you state:

&quot;and I'm only interested in the last entry.&quot;

yet seemingly you want all of the details (questions)?

This is dependent upon the table layouts, and you've done a great job in describing the report layout, with a little example data and expected output we should have this finished up.

-k

 

[reitter]

For each PcsPocQueryID (such as IV.Assesment) there would be many entries, at least once per shift that the nurse documented. I'm only interested in the last entry made for each specific query, which would be reflective of the actual patient condition.

My tables in this example would be:

DateTime QueryID PatientID Response
20040128 08.05.00:00 IV.Assesment 12345678 NO
20040128 09.05.00:00 IV.Assesment 12345678 NO
20040128 10.05.00:00 IV.Assesment 12345678 NO
20040128 11.05.00:00 IV.Assesment 12345678 YES


In this case I want my report to pull in the &quot;YES&quot; response to the appropriate field on the report.

Thanks for taking the time to look at this,

Kim

 

[Naith]

This is what group selection is for.

You could insert something like Maximum Time grouped by Date in your group selection criteria, which would save you processing all the unnecessary rows.

Ngolem's solution also works, so I can only think that you perhaps misunderstood what he was implying.

Had you grouped by the QueryID, Patient ID, and the Date portion of your DateTime, and place your information in the group footer of the lowest group - as long as your report is ordered by DateTime ascending, you would always get the most recent row. This approach does mean you would also return extraneous rows though.

All the best,

Naith

 

[Ngolem]

Thankyou Naith...Yes my approach is the simplest in view of the fact that this report seems to be run for a single patient (all patient details in the report header)

**********************************************
Group1 Header (hidden)(PcsPocWork.QueryID) each of the above fields is one of this group. With potentially mutiple answers based on the date and time of the assessment.

details (hidden)
DateTime QueryResponse FormulaField=(last response)

The Formula field works if it is in the detail section. But when I move the formula field to the corresponding area on the form it doesn't show up.

GroupFooter1 (hidden)
Page Footer (hidden)
**********************************************

First....I would have a second Grouping based on DateTime(suppressing the footer/header) or sort based on DateTime after Group1...this will order your data for a given QueryID by dateTime

THen I would move your display from the details section and completely suppress the details.

I would then move your display to the Group 1 footer and all should work.

I think the reason you are not seeing this work is that you are not sorting the records for the given QueryID


Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!