Can subreport fields be used to create groups on main?

[ehk]

Crystal 8.5
Datasource: Advantage SQL
Application: Medical office management software

Creating an A/P Check Log for patient refund payments. Main report has A/P tables to report check information. Invoice number assigned to each refund check is equal to a patient’s account number. Linked a subreport to provide patient ledger specific details associated with each refund.

Main Report
{Payacnt} – to report A/P account name
{Payldgr} – to report Invoice # (equal to patient’s account), Check #, Check Amount, Paid Date

Subreport – linked {Payldgr.Invoice} to {Ledger.Billnum} = Patient’s Account#
{Ledger} – select expert filtering for refund adjustment transactions
{Payhist} – to locate charge transaction the refund was applied to
{Ledger_1} – alias of Ledger cycles back to Ledger table to report Location/Department/Doctor associated with the charge

I want to create subtotals on the Check Log by Location, Department, Doctor associated with charge the refund was applied to. Believe I would need to establish groups on the Main report. Is this possible since the fields reside on the subreport?

Thanks, Ellen

 

[synapsevampire]

It soubds as though you may have the wrong report as the main report.

If the subreport has the logical grouping you require for the overall reporting process, it generally should be the main report.

You can do a file->save subreport as another name, then save your current main report to another name. Open the current report which was renamed and delete the subreport, then save it under anotehr name (this is to provide backups of the current reports).

Now open the old subreport as the main report, create the required groupings, and then link in the old main report as the subreport.

-k

 

[ehk]

Thank you for your response.

The data from patient ledger currently being called in the subreport are only meaningful when the Main Report finds an A/P account where a refund check has been posted. That was my reasoning for using A/P tables on the main report.

I currently have the main report grouped by A/P Account where the {Payacnt.Name} includes "Refund" in the string. It is reporting checks paid within a Start & End Date range (or check number range).

When a refund check is paid, I'm going to the patient ledger tables in the subreport to pull detail from the last charge the refund adjustment was applied to. Does it make sense to make patient ledger the main report in this case?

Secondary question: I created the subreport because of problems I was having linking all of the tables together in one. The A/P invoice represents the patient's account number but the fields are different types. {Payldgr.Invoice} is a string and {Ledger.Billnum} is numeric. As soon as I brought one or more fields from Ledger into the report and tried to preview it returned:

Error 7200: AQE Error: States S0000; NativeError=2124; [Extended Systems] [Advantage SQL Engine] Invalid Operand for operator =

I don't know how to link fields of different types in the Visual Linking Expert. Is this possible?

Ellen

 

[Madawc]

A value returned by a subreport cannot be used until the section after the section that contains the subreport. That's why you can's use it for a group - if it accepted the field, it would still contain nothing useful at the time when grouping was performed.

Madawc Williams (East Anglia, UK)

 

[synapsevampire]

If possible, create a View and CAST one of the data types to a matching type for the other table, and use the View in your report.

As you must realize, that is indicative of someone who should change from being a dba to a...well, they're probably not qualified for anything

As a last resort, you can manually create the SQL for the report and paste it into the ADO/RDO connection command, and within that SQL you can CAST one of the values to a common type. I dislike this approach as it embeds the sql into the connection and increases maintenance and decreases flexibility.

-k

 

[ehk]

Thanks again...

Being such a Crystal newbie (and a non-programmer), I have not created a View or used CAST. I will do some research and give this a try.

Any links you can suggest for me to get this information would be appreciated. The manuals I have don't reference either term. I do see documentation about the SQL Designer.

Ellen

 

[synapsevampire]

Don't use the SQL Designer, it is no longer supported and was never a good thing.

A slower alternative is to create a subreport for one of the tables, and then create a formula such as totext({table.accountnumber}) to convet a numeric to a string, and then you can link on the formula, but again you have to understand how to use your tables.

A View would be created on your database, and your dba should already have done so to correct the oversight during design of using different data types.

A View is a database object, not a Crystal object.

-k