Reports (Page 1 of 2)

Hello Adam,

In sql, the last element in your select (just before the FROM) does not need the comma. Remove the last comma before the FROM it it should work

Cheers

Math

Hello Adam,

You can not add more than one query for a SQL Report, which means if you "chain" them (more than one SELECT... FROM...) only the first one will be taken into account. This is why you don't see all your fields in the report designer.

But you tables are linked together with the id you have specified so a JOIN command can solve this.

The SQL Query I setup in your button looks like :

SELECT
    SaleInv.invDate,
    SaleInv.invCode,
    SaleInv.subTotal,
    SaleInv.taxTotal,
    SaleInv.grossTotal,
    SaleInv.discount,
    SaleInv.delivery,
    SaleInv.InvTotal,
    Customer.name,
    Customer.surname,
    Customer.street,
    Customer.city,
    SaleProduct.name,
    SaleInvItem.Qty,
    SaleInvItem.unitPrice,
    SaleInvItem.netPrice,
    SaleInvItem.taxAmt,
    SaleInvItem.totalAmt
FROM
    SaleInv
INNER JOIN Customer ON Customer.id = SaleInv.id_Customer,
 SaleProduct
INNER JOIN SaleInvItem ON SaleInvItem.id_SaleInv = SaleInv.id
AND SaleProduct.id = SaleInvItem.id_SaleProduct

All the fields I want are listed at the beginning of the query after the SELECT command, and then, in the FROM section, tables are listed with their links.

If you correct you SQL Query button with this, you'll see all the listed fields available for your report.

One precision though : Company table and SaleInv table are linked together twice on :
SaleInv.id = Company.id_SaleInv
and
Company.id = SaleInv.id_company

Why do you link those tables twice ? Once should be enough I think.

Furthermore, the SaleInv.id_company is NULL everywhere AND same goes for Company.id_SaleInv, which means that if you include them in the query, you won't get any results on the total query because all conditions have to be met, and this one excludes all the others.

Now, before modifying you database structure, what do you mean by Company ? Is it a provider for products you sell, in which case I'd probably link this table with the SaleProduct table, or is it a customer company, in which case I'd link the Company table with the Customer table.

Concerning  "SaleTransHist" report, you don't see company fields because the link from and to this table are NULL : the link exists, it is referenced in both SaleInv and Company tables, but has no value on either sides.

Hope this helps a bit, because that's a great piece of software you are building there, very nice.

Cheers

Mathias

Hello again Adam,

First of all :

Here is a "graphical" explanation of what I meant :

The relationships between SaleInv and Company :

As you can see each table is linked to the other one, but, if you look into the tables :

id_company is linked but empty (NULL) and

it is the same for id_SaleInv on the Company table : linked but empty (NULL)

What is the consequence ?

Look at the previous query I posted, there is at least one "AND", meaning all the conditions must be fulfilled. As a consequence, you don't return anything because there is nothing to return.

WORKAROUND :
To solve that problem, and removed the links between SaleInv and Company, and linked customers with Company (assuming ONE Company can be linked with MANY customers) - that's my example, but you'll build your own with the same idea.

Here is how it looks now :

And the sample data I put into customers to link them with company (again, just my example) :

Now you can query the whole table system, get your data and put them into the report with :

SELECT
    Company.coLogo,
    Company.coName,
    SaleInv.invDate,
    SaleInv.invCode,
    SaleInv.subTotal,
    SaleInv.taxTotal,
    SaleInv.grossTotal,
    SaleInv.discount,
    SaleInv.delivery,
    SaleInv.InvTotal,
    Customer.name,
    Customer.surname,
    Customer.street,
    Customer.city,
    SaleProduct.name,
    SaleInvItem.Qty,
    SaleInvItem.unitPrice,
    SaleInvItem.netPrice,
    SaleInvItem.taxAmt,
    SaleInvItem.totalAmt
FROM
    SaleInv
INNER JOIN Customer ON Customer.id = SaleInv.id_Customer,
 SaleProduct
INNER JOIN SaleInvItem ON SaleInvItem.id_SaleInv = SaleInv.id
    AND SaleProduct.id = SaleInvItem.id_SaleProduct
INNER JOIN Company ON Customer.id_Company = Company.id

All the data you wanted are in a Dataset, now, how do I format the report to get the logo image ?

Simple steps :
You logo is in the dataset

but it's an image blobed into the database so add a master data band like this

set company name field  like this

now add an image (not the coLogo) as shown below  and click OK (green check mark)
:

Finally, with the image selected in the report set :
1- DataSet = Report
2- DataField = coLogo

like this

If you don't set the DataSet first, you won't be able to select the DataField

Now let's preview your report :

Yes, it's ugly like that, but it works. Just arrange the size of your fields, the filters you want, add the other ones, organize them as you wish and you have your report.

Example of very small page work :

And end result on the report :

Project attached

Hope it helps

Cheers

Mathias

Great stuff Mathias.... Thank you so much......................

Now, I need to get down to learn using report designer.

At this point only thing is remaining on this topic is displaying image in a tGrid cell. Please see form1 company tab.
Perhaps Dmitry could offer solution for it.

Images are using "LinkFile" type.
What's causing the issue of not working on my example?
Perhaps there is a workaround...

Hi Dmitry,

Please see sample project "ReportTest2.zip" on post #3 where LinkFile type used.

http://myvisualdatabase.com/forum/misc. … download=1

No, I get file path instead of image itself

Thanks a lot Dmitry...............

iT works fine now..
I was also making mistake of setting sExt cell number to 2 like below:
Form1.tgCompany.Cell[2, i].ObjectReference := G;

Hi Math,

Lets assume user operates more than one business and wants to assign each sale to one of his company.
For this I linked SaleInv db table to Company db table. It seems to be working OK with Report but I couldn't get the SQL script right to make it work with with ReportSQL.

Script I've tried and didn't work:

INNER JOIN Company ON SaleInv.id_Company =Company.id
WHERE SaleInv.invCode = '{edSaleInvCode}'

Please see reference images below:
DB Schema
http://prnt.sc/dwq5os

SaleInv Form
http://prnt.sc/dwq7nb

Hi EHW,

Thanks a lot for the info...

I was thinking in practical terms. I agree that multiple companies cannot share same invoice/s but shouldn't they be able to share same customer/s?

SELECT
    Company.coLogo,
    Company.coName,
 Company.coAddress,
 Company.coPhone,
    SaleInv.invDate,
    SaleInv.invCode,
    SaleInv.subTotal,
    SaleInv.taxTotal,
    SaleInv.grossTotal,
    SaleInv.discount,
    SaleInv.delivery,
    SaleInv.InvTotal,
    Customer.name,
    Customer.surname,
    Customer.street,
    Customer.city,
    SaleProduct.name,
    SaleInvItem.Qty,
    SaleInvItem.unitPrice,
    SaleInvItem.netPrice,
    SaleInvItem.taxAmt,
    SaleInvItem.totalAmt
FROM
    SaleInv
INNER JOIN Customer ON Customer.id = SaleInv.id_Customer,
 SaleProduct
INNER JOIN SaleInvItem ON SaleInvItem.id_SaleInv = SaleInv.id
    AND SaleProduct.id = SaleInvItem.id_SaleProduct
INNER JOIN Company ON Customer.id_Company = Company.id
WHERE SaleInv.invCode = '{edSaleInvCode}'
INNER JOIN Company ON SaleInv.id_Company =Company.id
WHERE SaleInv.invCode = '{edSaleInvCode}'

The above produces syntax error.
If I add semicolon after customer lines like below it doesn't produce error but it doesn't display correct company on report preview. I'm thinking script lines I added are wrong.

FROM
    SaleInv
INNER JOIN Customer ON Customer.id = SaleInv.id_Customer,
 SaleProduct
INNER JOIN SaleInvItem ON SaleInvItem.id_SaleInv = SaleInv.id
    AND SaleProduct.id = SaleInvItem.id_SaleProduct
INNER JOIN Company ON Customer.id_Company = Company.id
WHERE SaleInv.invCode = '{edSaleInvCode}';  
INNER JOIN Company ON SaleInv.id_Company =Company.id
WHERE SaleInv.invCode = '{edSaleInvCode}'

Sample project attached:

Adam,

Since multiple companies can share customers, I removed the relationship between customers and companies. I fixed your ReportSql and put in the report bands for the invoice. I did not format anything in the report bands. Just wanted to show you the placement of the data in the bands.

Adam,

You have hit on a nuance, qwirk, snafu or whatever you want to call it with Fast Report and MVD. For some reason, you can place calculated totals in the footer, but you cannot place report fields in there. But all is not lost. You have to go around the woods to get to grandmother's house in this case. In a nutshell, you have to place the report fields that you want in the Group Footer into the Group Header. In your case you don't want to see these fields in the Group Header, so you have to hide them (white text font). Then you can define label fields (memo in Fast Report terms) in the Group Footer. Then using Pascal script in Fast Report you can populate and format the Group Footer fields with the hidden fields in the Group Header.

I have screenshots, but not sure how to place them here in the post. I'm winging it. Hopefully they show.

Well they did not show. Maybe somebody can enlighten me in how to place images in the post.