ReportPro 2 SQL tables triggered Section

This forum is the place to discuss issues related to ReportPro, Xs2Ado, Vo2Ado, bBrowser and other 3rd party products
Post Reply
dirk@triviant.be
Posts: 13
Joined: Tue Sep 27, 2016 7:49 am

ReportPro 2 SQL tables triggered Section

Post by 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
Posts: 13
Joined: Tue Sep 27, 2016 7:49 am

Re: ReportPro 2 SQL tables triggered Section

Post by dirk@triviant.be »

Hello All,

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

Cheers, Dirk
User avatar
robert
Posts: 4554
Joined: Fri Aug 21, 2015 10:57 am
Location: Netherlands

Re: ReportPro 2 SQL tables triggered Section

Post by 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
XSharp Development Team
The Netherlands
robert@xsharp.eu
dirk@triviant.be
Posts: 13
Joined: Tue Sep 27, 2016 7:49 am

Re: ReportPro 2 SQL tables triggered Section

Post by 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
User avatar
robert
Posts: 4554
Joined: Fri Aug 21, 2015 10:57 am
Location: Netherlands

Re: ReportPro 2 SQL tables triggered Section

Post by robert »

Dirk,
What do you mean with
But the SQL-report will not work.

Robert
XSharp Development Team
The Netherlands
robert@xsharp.eu
dirk@triviant.be
Posts: 13
Joined: Tue Sep 27, 2016 7:49 am

Re: ReportPro 2 SQL tables triggered Section

Post by 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
User avatar
robert
Posts: 4554
Joined: Fri Aug 21, 2015 10:57 am
Location: Netherlands

Re: ReportPro 2 SQL tables triggered Section

Post by 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
XSharp Development Team
The Netherlands
robert@xsharp.eu
dirk@triviant.be
Posts: 13
Joined: Tue Sep 27, 2016 7:49 am

Re: ReportPro 2 SQL tables triggered Section

Post by 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
Posts: 13
Joined: Tue Sep 27, 2016 7:49 am

Re: ReportPro 2 SQL tables triggered Section

Post by dirk@triviant.be »

Hi Robert,

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

Cheers, Dirk
User avatar
robert
Posts: 4554
Joined: Fri Aug 21, 2015 10:57 am
Location: Netherlands

Re: ReportPro 2 SQL tables triggered Section

Post by 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
XSharp Development Team
The Netherlands
robert@xsharp.eu
Post Reply