The data are shown repeated

elarys · January 2010

I have 3 tables and this is my query
<div class='sqltop'>SQL</div><div class='sqlmain'>SELECT SUM(FeeAmount) Total, C.Description, D.Number, D.PrintDate, D.TotDuty, D.TotTax,
S.Importer, S.Broker, S.EstDuty, S.EstTax, M.Entry, M.Source
FROM DailyStatements D
LEFT JOIN Shipment_ABI S ON (D.Number = S.Number)
LEFT JOIN ABI_STMT_MSG M ON (D.Number = M.Number)
LEFT JOIN Shipment_ABIFees F ON (D.Number = F.Number)
LEFT JOIN BASICO.dbo.Codes C ON (C.Code=F.FeeCode)
WHERE D.Number <> '' AND D.Number ='1709258096'
GROUP BY F.FeeCode, C.Description, D.Number, D.PrintDate, D.TotDuty, D.TotTax,
S.Importer, S.Broker, S.EstDuty, S.EstTax, M.Entry, M.Source
ORDER BY Number </div>

it shows me
[img][/img] http://cid-f2aaf9cbf65dd5ae.skydrive.live....ic/consulta.gif

My report is bad

[img][/img] http://cid-f2aaf9cbf65dd5ae.skydrive.live....lic/report1.gif

This is true
[img][/img] http://cid-f2aaf9cbf65dd5ae.skydrive.live....lic/report2.gif

My report design
[img][/img] http://cid-f2aaf9cbf65dd5ae.skydrive.live....lic/report3.gif

Now, if I show more than one records
Is bad
Page 1
1?? Record
2?? Record
3?? ...

Page 2
1?? Record
2?? Record
3?? ...

Page 3
1?? Record
2?? Record
3?? ...

I want to look like this
Is true
1?? Record
Page 1
Page 2
Page 3

2?? Record
Page 1
Page 2
Page 3

3?? Record
...

What am I doing wrong?
What is my error?

pd1: How do I show the pictures and not the link?
pd2: Sorry about my english, I speak spanish

Anu de Deus · January 2010

I think the main problem is in your query.
The easier solution would be to group it in a way that it will show one record per line as you want it in the report, and all should be fine.
Your report will show one line per record, so if your query is returning repeated data, it will only reflect that, unless you change your report.
I'm afraid I don't have enough time to help you more than that, I hope it gives you some ideas.

elarys · January 2010

I did a query for page

<div class='sqltop'>SQL</div><div class='sqlmain'>SELECT D.Number, D.PrintDate, D.TotDuty, D.TotTax, S.Importer, S.Broker, S.EstDuty, S.EstiTax,
FROM DailyStatements D
LEFT JOIN Shipment_ABI S ON (D.Number = S.Number)
WHERE D.Number ='170925'
ORDER BY Number </div>

<div class='sqltop'>SQL</div><div class='sqlmain'>SELECT SUM(FeeAmount) Total, C.Description, D.Number, D.PrintDate, D.TotDuty, D.TotTax
FROM DailyStatements D
LEFT JOIN Shipment_ABIFees F ON (D.Number = F.Number)
LEFT JOIN BASICO.dbo.Codes C ON (C.Code=F.FeeCode)
WHERE D.Number ='170925'
GROUP BY F.FeeCode, C.Description, D.Number, D.PrintDate, D.TotDuty, D.TotTax,
ORDER BY Number </div>

<div class='sqltop'>SQL</div><div class='sqlmain'>SELECT D.Number, D.PrintDate, D.TotDuty, D.TotTax, M.Entry, M.Source
FROM DailyStatements D
LEFT JOIN ABI_STMT_MSG M ON (D.Number = M.Number)
WHERE D.Number ='170925'
ORDER BY Number </div>

This works fine, but changing the WHERE and pass many records
This shows
Page 1
1?? record
2?? record
...

Page 2
1?? record
2?? record
...

Page 3
1?? record
2?? record
...

And I have to display the report like this:
1?? record
Page 1
Page 2
Page 3

2?? record
Page 1
Page 2
Page 3

so many records

gordk · January 2010

are you using more than 1 design page?
or are you trying to group one mdband like the nested groups demo report in the main demo,
if this is the case then connect your dataset to the band not the page, also
just use the orderby clause in the query to get the data ordered by the conditions of the headers.

The data are shown repeated

Comments

Leave a Comment