Cross tab grouping question

[1994mkiv]

I am thinking about making this report in a cross-tab. So each row will be part # and each column will be order_qty. What I want is to group the order_qty in a way that it will count all the part ordered on a same price and make column and put price in summarize field. So the report will look like this

2 6 avg
part # 2095.68 2054.59 2064.86

 

[dgillz]

The problem with doing that as a crosstab is the the columns (and rows) of a crosstab are database or formula fields, not sums of database or formula fields.

So I do not believe this is possible.

If you figure it out please post the solution.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[lbass]

What version of CR are you using?

-LB

 

[1994mkiv]

I am using 8.5

 

[lbass]

I can't test this for your specific situation, but try creating a SQL expression {%sumqty}:

(
select sum(A.`OrderQty`)
from table A
where
A.`PartNum` = table.`PartNum` and
A.`UnitCost` = table.`UnitCost`
)

The punctuation is specific to your datasource/connectivity. You should replace "table" with your table name.

Then use {%sumqty} as your column field in the crosstab, add partnum as the row, and use average of unitcost as your summary field.

-LB

 

[1994mkiv]

Never used the SQL expression before, When I create a new SQL expression and even just save an empty screen it's giving me ODBC error, is there something I need to define in CR before start using SQL expressions.

 

[lbass]

What error message do you get? Please post what you created in the SQL expression area. For a guide to how you should punctuate, go to database->show SQL query.

-LB

 

[1994mkiv]

The only thing I wrote was
select table."UnitCost" from table
and I verified from database->show SQL query about the punctuation

The error I am getting is
ODBC error: [DataDirecrt-Technologies][ODBC PROGRESS driver][PROGRESS]Syntax error(7587)

 

[lbass]

Well, that wouldn't work, since a SQL expression can return only one value (per group). Also, you must follow the syntax I gave you, enclosing the entire thing in parens, using an alias table name "A", etc. What are your actual table and field names?

-LB

 

[1994mkiv]

The names are PODetail.PartNum, PODetail.OrderQty, PODetail.UnitCost and POHeader.OrderDate
Thanks for looking lbass

 

[lbass]

I'm not sure how you are using the OrderDate in the report--is it in the record selection formula, or just displayed in the detail section? If it isn't in the select statement, then the SQL expression should look something like this:

(
select sum(A."OrderQty")
from PODetail A
where
A."PartNum" = PODetail."PartNum" and
A."UnitCost" = PODetail."UnitCost"
)

You can copy this into the SQL expression, but you might need to replace the quotation marks, as the one's used within the editor can be different from the one's used in a Word document (or possibly here (not sure).

-LB