order by total in a group report

rbuser

Hi,

I've done a report group by supplier and then by product, to show the total
quantity and the total price sold for each product of that supplier. Now I
want to order by total quantity (or by total price) to know what were the
most selling products. I can I do that. The report is ordered by supplier
and by product, so the groups can work. How can I do that?

Thank you

Imendes

nicocizik

Hi Imendes,

I'm a bit unclear what you are trying to do. Would you like to order each
individual group by total quantity or total price? If so, you can simply
add these fields to the ORDER BY clause after the other two. If you would
like to order the entire dataset by total quantity or total price, you will
loose your grouping fields and your groups will no longer work correctly.
Perhaps a little more information on what you would like your report to look
like will help me understand what you need.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

This is how my report looks like:

Product Total Qty Total Price
----------------------------------------------------------------
Supplier: 1-Supplier xxx

xpto - product xpto 58 985,00
ptox - product ptox 152 625,00
----------------------------------------------------------------
Totals 210
1610,00

Supplier: 2-Supplier 2

blabla - product bla bla 89 859,00
xxxxx - product xxxxx 158 829,00
-----------------------------------------------------------------
Totals 247
1718,00

Total Qty is a DBCalc - sum(qty) and this is the field which I want to order
by (descending), so I can see
at first glance which were the best selling products.

Thank you.

IMendes

(Sorry for my english)

nicocizik

Hi Isildo,

ReportBuilder simply traverses the data that you give it top to bottom, it
does not have any way of internally sorting your data. In order to
accomplish this you will need to calculate the total quantity inside your
dataset and sort each supplier group based on that value.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Nico Cizik (Digital Metaphors) wrote:


As far as I can understand, Isildo don't want any RB internal sorting
but visual ways, by using RAP, which translates into SQL order by, and
you are saying that he can not order by a calculated field, when using
grouping, and he must use pre-defined VIEWS or other SQL solutions
because RAP is limited on that field, is that correct?

Marcelo Bar

nardmoseley

1. Any sorting must be done by the dataset. The standard way to do this is
via SQL.

2. You can use the Query Designer available from the Data workspace to
define a calculated field. You can sort on the calculated field. This is
supported by standard SQL. (RB generates the SQL string and assigns it to
the query dataset (for example TADOQuery if you are using ADO). The sql is
executed by the database engine.

3. RB 9 adds a new TdaSQLBuilder class that can be used from the
Report.BeforeOpenDataPipeline event. TdaSQLBuilder enables SQL to be defined
programmatically. It essentiall provides a run-time interface that is
organized like the query designer. (SelectFields[], CalcField[], etc). See
the RBuilder.hlp for TdaSQLBuilder. There is sample code in the subtopics.

rbuser

Well, of course I can do that in SQL (grouping, etc.), but the problem is to
put that in a good visual approach for the user. I can do
a SQL to show what I want, but it will repeat de supplier each line, and
other fields. Is there a way in report builder to
'emulate' the visual groups? I just want to show the supplier once, and then
all the products of that supplier and the total quantity.
Is there any way to do this in RAP ?

Thanks again.

IMendes

rbuser

Nard Moseley (Digital Metaphors) wrote:


Of course.


But the problem seems to be that if we have a Master-Detail relation,
where the Master are the suppliers and the Detail are articles and
associated total selling values, we can not order by this calculated
field, only by article code. At least by using RAP and the grouping
feature. Is this possible by using only RAP?

Marcelo Bar

rbuser

Isildo,

I believe that Nard is trying to tell you that RB does not, unlike other
reporting products, do any sorting. The SQL Select statement is all you
have available. If you can live with a design time sort order, use DADE and
if you need your sort to be run-time based, use TdaSQLBuilder from RAP.

As far as designing the report, from what you have told us it seems fairly
straight forward.

Assuming that an item only has one vendor, use a single dataview sorted
first by vendor and then by the sum of the value. In the design tab, add a
group for the vendor with the vendor name in the header and DBCalc objects
for the quantity and dollar value in the footer. Populate he detail bands
with the item code, quantity, and dollar value.

If the item is purchased from multiple vendors (i.e., it will be printed
more than once), the process is more complex so if you need this solution,
please post back.

--

Bob

rbuser

The problem is that I have two tables to build the report. The first one is
the vendor's references, which have the
vendor code and the product code, this table stores the items that each
vendor supplies. One vendor can supply more than
one item (and yes, the item could have several vendors). Then, I have
another table that stores the invoices. In this one I want to show the
quantity and the price. This table is linked with the other one by Item
code. The report must group for vendor, and then in the detail, must group
by item. In Bob's approach, with this set of tables, the item repeats
several times for each vendor. If I have 1000 records for the same product
for that vendor in the invoices table, it will show 1000 times the same
product for that vendor. I want to group for item too, so the product just
appears once for each vendor, with the totals (quantity and price).

Thanks.

Imendes

rbuser

Isildo,

This is an SQL and not an RB issue. The following is a possible solution
based on what you have told us:

SELECT invoice.StockCode, vendor.Code,
SUM(invoice.Quantity)
FROM invoice invoice
INNER JOIN vendor vendor ON
(vendor.StockCode = invoice.StockCode)
GROUP BY vendor.Code, invoice.StockCode
ORDER BY vendor.Code, SUM(invoice.Quantity) DESC

Change the table/field names and add any additional fields and you should be
complete.

--

Bob

rbuser

Well, thank you Bob. With that solution it works (some part). I added the
the total price (sum(invoice.Total)) and one variable to make some
calculations, and everything works fine. Now, the problem is that I want to
add two more fields. That fields will not appear in the report, but I need
them to show in the form where the user filters the fields. That form will
show all the fields (except the calculated) in the dataview. The fields are
the date and customer type (another table), so the user can filter the
report between dates, and by customer type. The date and customer code are
fields in the invoice's table, and the customer type is another table linked
by customer code. If I add this two fields in the data view, the reports
doesn't work anymore, because it tries to group by date and by customer
type, and then the data goes all wrong.
Is it possible to add these two fields in this report?

Thanks again for your support.

IMendes