ReportPro 2 SQL tables triggered Section

[dirk@triviant.be]

Hello All,

I don't find any good way to work with triggered section when using SQL-tables.

I explain myself: I have 2 tables:

First table : A customer-table containing 26 rows (for example the name of the customer is A, B, C etc)
Second table : Invoices : Some customers has a few invoices, but there exist customer which has no invoice at all.

Now i want a report. This report must give me 26 rows (the name of the customer) and if the customer has invoices, I want to list of the invoices.
Very important: Also customers with no invoices must be on that list.

So, the main table must be the customer table.
How do I configure the triggered section?

Any feedback is appreciated.

Dirk

[dirk@triviant.be]

Hello All,

Nobody has any idea ?
Where can I find support for Report Pro? Maybe paid?

Cheers, Dirk

[robert]

Dirk,
I did not have the time to look into this.
What you ask for sounds a lot like what the standard Customers example file is doing.
It has a customers table in the main report and triggered sections for contracts, equipment etc.
The trigger in each section is the unique customer number. Whenever that number is about to change, then the triggered section will be printed.


Robert

[dirk@triviant.be]

Hi Robert,

I have report pro 2.17 installed and I see a sample report with the name customer.rpt.

This report is almost running the way I want with DBF-files.
So, I change the report that the trigger runs correct (because using the original report, the trigger runs incorrect).

The I try to change the DBF-files to SQL-tables.
But the SQL-report will not work.

How can we clear this out? What do you need to figure this out?
Can I send you some files?

Kind regards, Dirk

[robert]

Dirk,
What do you mean with

But the SQL-report will not work.

Robert

[dirk@triviant.be]

Hi Robert,

All sample replorts works with DBF-files.
So, I change the section properties from DBF to SQL, (and I solve all problems regarding the name of the fields).

Then, I press the preview button, and I got the message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Executing SQL directly: no cursor.

When I go back to the section properties, I can go to the SQL Properties of the standard section. Then I do 'Show SQL', and I see: SELECT "dbo"."Customer"."SYSCSTNO", "dbo"."Customer"."NAME" FROM "dbo"."Customer" ORDER BY "dbo"."Customer"."NAME". Running this select-statement results in 'SQL executed without error."

Second, I go to the SQL properties of the triggered section. The [Show SQL] button gives me the following "SELECT "dbo"."Contract"."SYSCSTNO", "dbo"."Contract"."CONTRACT" FROM "dbo"."Contract" WHERE "dbo"."Contract"."SYSCSTNO"==Dbo.Customer.Syscstno ORDER BY "dbo"."Contract"."SYSCSTNO". Running this select-statement results in [Microsoft][SQL Server Native Client 11.0][SQL Server]Executing SQL directly: no cursor.

When I delete the filter condition of the triggered section, then the report runs fine, but I got all rows of the contract table for each customer. So, this is a bad result.

I hope this is clear. If not, please let me known.

Cheers, Dirk

[robert]

Dirk,
The SQL query is this:

Code: Select all

"SELECT "dbo"."Contract"."SYSCSTNO", "dbo"."Contract"."CONTRACT" FROM "dbo"."Contract" 
WHERE "dbo"."Contract"."SYSCSTNO"==Dbo.Customer.Syscstno ORDER BY "dbo"."Contract"."SYSCSTNO"

Dbo.Customer.Syscstno is not part of the FROM list, so this will not work.
What did you put in the trigger ?

Robert

[dirk@triviant.be]

Hi Robert,

Indeed,

I have written the field name "Dbo.Customer.Syscstno" (without quotes) in the trigger.
In the DBF-example, the trigger is also filled with the field "Customer.Syscstno".

So, how to make a correct trigger?

Dirk

[dirk@triviant.be]

Hi Robert,

Do you have any idea how to make a correct trigger in SQL?

Cheers, Dirk

[robert]

Dirk,
This seems to be a problem in both RP2 for VO and X#.
I will take a look at this, but to be honest, it does not have a very high priority, since the problems exists for decades, and you're the first to report it.
As a workaround, I would recommend to download the SQL data into temporary DBF files, and then build the report on the DBF files.

Robert