Grouping Formula Fields

[MrArmstrong]

Hello all , I am running Crystal Reports 11, I have a report that is focusing on counting all of our Purchase Orders.
I had to count and see which parts are ordered the most.I was successful in doing a count on the PO.Partnumber field , which gave me a count of how many times each part number was ordered.I have that number showing in the group footer.
The next step is to count how many 1 time orders, 2 time orders, etc.I have also added a formula field which shows in the details with the same count thats in the Group Footer.

How can I group on this Summary field or Formula field?

 

[IanWaterman]

Its not clear what you are trying to achieve. Let us have a rough layout of dataset and how you want to see it on report.

Ian

 

[MrArmstrong]

Part Number Description qty
114HS122-1 VALVE, LINEAR DIRECT 1
occurences 1
114R3119-3 SPACER 1
114R3119-3 SPACER 1
114R3119-3 SPACER 1
114R3119-3 SPACER 1
occurences 4
114S4909-50 PLATE 1
occurences 1
114SS660-1 MOTOR, HYD. 1
occurences 1
115785-01 CONNECTOR, PLUG ELEC 1
115785-01 CONNECTOR, PLUG ELEC 1
115785-01 CONNECTOR, PLUG ELEC 1
occurences 3
1-300-142-02 HOSE, ASSY 1
occurences 1
131A006-11-23 DP 50 SOFTWARE (VDD-09BR) 1
occurences 1
131A006-F09BP DP 49 SOFTWARE (VDD-09BP) 1
131A006-F09BP DP 49 SOFTWARE (VDD-09BP) 1
occurences 2
131A093-97-11 IAP 169 SOFTWARE (VDD-09GR) 1
occurences 1
131A093-F09GR IAP 168 SOFTWARE (VDD-09GR) 1
occurences 1
144A456-11-16 RTU 10 SOFTWARE (VDD-09L) 1
occurences 1
145A7004-1 TUBE ASSY 1
145A7004-1 TUBE ASSY 1
occurences 2

1 Occurences ?
2 Occurences ?
3 Occurences ?
4 Occurences ?
5 Occurences ?
6 Occurences ?
7 Occurences ?
8 Occurences ?
9 Occurences ?
10 Occurences ?

This is a sample, as you can see I used a summary to get how many times a part number occurs. Now I need to know how to get them group by occurences, i.e. how many parts ordered only 1 time, 2 times, etc.See the bottom. As you can see there were 8 occurences that only had 1 order.How do sum on this.

 

[lbass]

Use a command as a datasource where you set it up like this:

Select table.`field1`, table.`field2,
(select count(A.`partno`) from table A where
A.`partno` = table.`partno`) as occurrences
From table

Select all of your fields and use the command as your entire datasource. Then you can insert a group on {command.occurrences}. The exact syntax/punctuation is specific to your datasource.

-LB

 

[MrArmstrong]

Sorry for the delay but I have been trying for 2 days to figure out what you are telling me to do but I have never used the Command or parameter part of CR XI.
I have created a formula of the count that gives me the number of occurences in the group footer.That is better than a summary field.

Now if I could just figure out how to group on this formula field that would be helpful. Everytime I go to the group expert the formula field is not in the list of database fields.

 

[lbass]

To do what you want to do, you need to create a command to use as your datasource instead of individual tables. Start a new report->blank report->your datasource->add command and enter the command there.

In your current report, you could go to database->show SQL query and copy the SQL query there and then paste it into the command area of a new report, and then just add in the

(select count(A.`partno`) from table A where
A.`partno` = table.`partno`) as occurrences

...adapting it to fit your syntax/punctuation.

-LB

 

[MrArmstrong]

I am feeling real Crystal DUMB right not, I can't get this one. Everytime I try to type it in it says failed to retrieve data.
Maybe this will help.

All my data comes from these three tables:

PO_HDR
PO_LINE
STOCK

Fields
131A006-11-23 DP 50 SOFTWARE (VDD-09BR) 1

BREAKDOWN Example:

PO_LINE.PARTNUMBER
131A006-11-23

PO_LINE.DESCRIPTION
DP 50 SOFTWARE (VDD-09BR)

PO.LINE.QTY
1

PO_LINE table would be the one that has all the data for the partnumbers and occurences.This is what I had that did not work:
(select count(PO_LINE.'PARTNUMBER') from PO_LINE where PO.LINE.'PARTNUMBER' = PO.LINE.'PARTNUMBER') as occurrences

 

[CoSpringsGuy]

Your problem is probably generated from your specific data structure. Each data structure has variations of SQL code. LBass has given me a few SQL command structures to try and they have always been spot on with what I was trying to do. The problem was that I had to interpret them back to my data structures SQL format. Not sure what yours is but it is usually pretty easy to find. For example, I query a lot of reports out of Intersystems Cache database. An easy search on thier website for 'SQL reference' pointed me in the right direction.

Good Luck!

_____________________________________
Crystal Reports XI Developer Version

 

[lbass]

It should read:

(select count(A.`PARTNUMBER`) from PO_LINE A where A.`PARTNUMBER` = PO.LINE.`PARTNUMBER`) as occurrences

Note that these are not single quotes either--however, that may be irrelevant to your particular database.

Please copy and paste the SQL query from your original report and I will try to adapt it for you. Also identify the datasource you are using.

-LB

 

[MrArmstrong]

Here is my SQL Query:


SELECT "PO_HDR"."DOC_NO", "PO_LINE"."PARTNUMBER", "PO_LINE"."ADDED_DTE",
"PO_LINE"."UNITP", "PO_LINE"."QORDER", "PO_LINE"."SUBTOTAL",
"PO_LINE"."STATUS", "PO_HDR"."CUST_REF3", "PO_HDR"."CUST_REF4",
"PO_HDR"."DOC_TYPE", "STOCK"."STK_TYPE", "STOCK"."CATEGORY",
"PO_HDR"."DOC_STATUS"
FROM "GDB_01_BOEING3"."dbo"."STOCK" "STOCK" RIGHT OUTER JOIN
("GDB_01_BOEING3"."dbo"."PO_HDR" "PO_HDR" FULL OUTER JOIN
"GDB_01_BOEING3"."dbo"."PO_LINE" "PO_LINE" ON
"PO_HDR"."DOC_NO"="PO_LINE"."DOC_NO") ON
"STOCK"."PARTNUMBER"="PO_LINE"."PARTNUMBER"
WHERE "PO_HDR"."CUST_REF3" LIKE '0%' AND "PO_HDR"."DOC_STATUS"<>11 AND
"STOCK"."STK_TYPE"='STK'
ORDER BY "PO_LINE"."PARTNUMBER", "PO_HDR"."DOC_NO"


My data source is called PENTAGON.My database is GDB_01_BOEING3.

 

[lbass]

Try this:

SELECT (Select count(A."PARTNUMBER") from "PO_LINE" A
where A."PARTNUMBER" = "PO_LINE"."PARTNUMBER") "Occurrences",
"PO_HDR"."DOC_NO", "PO_LINE"."PARTNUMBER", "PO_LINE"."ADDED_DTE",
"PO_LINE"."UNITP", "PO_LINE"."QORDER", "PO_LINE"."SUBTOTAL",
"PO_LINE"."STATUS", "PO_HDR"."CUST_REF3", "PO_HDR"."CUST_REF4",
"PO_HDR"."DOC_TYPE", "STOCK"."STK_TYPE", "STOCK"."CATEGORY",
"PO_HDR"."DOC_STATUS"
FROM "GDB_01_BOEING3"."dbo"."STOCK" "STOCK" RIGHT OUTER JOIN
("GDB_01_BOEING3"."dbo"."PO_HDR" "PO_HDR" FULL OUTER JOIN
"GDB_01_BOEING3"."dbo"."PO_LINE" "PO_LINE" ON
"PO_HDR"."DOC_NO"="PO_LINE"."DOC_NO") ON
"STOCK"."PARTNUMBER"="PO_LINE"."PARTNUMBER"
WHERE "PO_HDR"."CUST_REF3" LIKE '0%' AND "PO_HDR"."DOC_STATUS"<>11 AND
"STOCK"."STK_TYPE"='STK'
ORDER BY "PO_LINE"."PARTNUMBER", "PO_HDR"."DOC_NO"

-LB