Group Issue

[codrutza]

Hi

I have purchases by suppliers and I have to group by sum purchases:

--all the suppliers that have sum purchases >50000
--all the suppliers that have sum purchases <50000 >15000
--the suppliers that have sum purchases <15000 >1000

I sort on sum purchases descending but I can't remember how to do the group.

Can you help?

 

[LaurieHamlin]

Which version of Crystal are you using? In the Menu under Insert should be Group.

 

[CrystalizeCanada]

Let say that you have the following records:

Supp PurchaseValue
ABC 570
ABC 1000
DEF 22000
DEF 10000

And the results you want to see are

0 to 15000 (This is Group 1)
ABC 1570 (This is Group 2)
ABC 570 (This is the detail which can be hidden)
ABC 1000 (This is the detail which can be hidden)
15000 to 50000
DEF 32000
DEF 22000
DEF 10000

If the above data set is correct then you have to find out the SUM of purchases for each Supplier so you can group on that field.

SQL Expressions:
I would use a SQL Expression for to capture the SUM of PurchaseValue per Supplier for each record.
SQL Expressions are in the Field Explorer below Formula fields.
Once you have the SUM of purchases you can then Insert a Group on the SQL Expression and use Specified Order instead of Ascending Order to create the numeric intervals.
Example SQL Expression:
(SELECT SUM(PurchaseValue) FROM YourTable YT WHERE YT.SuppCode = TableFromMainReport.SuppCode)

This may be a challenge if you are not comfortable with SQL so if that is the case then you may want to explore another approach.


Gordon BOCP
Crystalize

 

[codrutza]

Thank you for replies.

Laurie:
I'm using version 2013

Gordon:
I had to am use a SQL command already -PurchasesCmdJob, for retrieving the data in the report. If I use the second, how do write in the selection
where YT.SuppCode = PurchasesCmdJob.SuppCode?

Is it possible?

I can't use a command only because I get in the report other detail data as well.

 

[CrystalizeCanada]

SQL Expressions are not available in the Field Explorer if you use a command in the database expert.
The SQL Expressions heading will not appear below the Formula Field heading as it normally does when basing a report on tables.

Since you are using a command one option is to modify the command to incorporate the SQL needed to grab the Supplier Purchase Totals.

As a very simple example

If this is my command:
SELECT this, that
FROM TA

I could moidfy the FROM line to the following:
FROM TA [highlight #FCE94F]INNER JOIN (SELECT SuppCode, SUM(PurchaseValue) AS SupplierTotal FROM YourTable Group BY SuppCode) X ON X.SuppCode = TA.SuppCode)[/highlight]

I would then have access to the SupplierTotal field in the report.



Gordon BOCP
Crystalize