Cross tab help

[omacron]

Hi

I am need of some help with a cross tab that I am trying to do. I am trying to create a report that needs one of the options in the cross tab not be summarized. Here is an example of the data that is coming to the report:

Item Number Size Qty Sold qty Received Qty on hand
1234 AA 1 0 4
1234 AA 0 2 4
1234 AA 2 0 4
1234 BB 5 0 7

Now Qty on hand refers to how many there is at the store. Each sale and each receive is it's own record and this can not be helped. So this is what Cross Tab is giving me:

AA BB total
1234 (sold) 3 5 8
(received) 2 0 2
(On hand) 12 7 19

I am skiping the button total just for ease of reading in a post. As you can see this isn't a true calculation for the on hand. this is what I need:

AA BB total
1234 (sold) 3 5 8
(received) 2 0 2
(On hand) 4 7 11


Now I can change the sum to nth most freq but this will screw up the total side like this:

AA BB total
1234 (sold) 3 5 8
(received) 2 0 2
(On hand) 4 7 4


I am at a real lose. I read the FAQ about making a manual cross tab but the problem is that my colums will always vary number.

Thanks

 

[lbass]

What version of CR are you using? Do you have the option of using SQL expressions?

-LB

 

[omacron]

I am running 8.5 and I have never used SQL expressions, although willing to try.

 

[WarrenRoss]

You can use a running total for this.

Create:

Group 1 on item number
Group 2 on size

Create a running total which does the following:

Maximum of Qty on Hand
Evaluate on change of field "size"
Reset on change of group "item number"

Pop the running total in group footer 2. Create your cross tab and use the running total as your summary field for "on hand".

 

[lbass]

Warren,

I don't believe that running totals were available in crosstabs in 8.5.

Omacron,

I am assuming that the qty on hand is the same for all records per item number/size. My thought was that you could create two SQL expressions:

[{%maxqty}:]
(select max(AKA.`Qty on Hand`) from Table AKA where
AKA.`Item Number` = Table.`Item Number` and
AKA.`Size` = Table.`Size`)

[{%countsize}:]
(select count(AKA.`Size`) from Table AKA where
AKA.`Item Number` = Table.`Item Number` and
AKA.`Size` = Table.`Size`)

In each of these expressions, you would replace "Qty on Hand", "Item Number", and "Size" with your exact field names, and replace "Table" with your Table name. Leave "AKA" as is, since it is an alias table name. Depending upon your record selection criteria, these might need to be adapted.

Then you would go to the formula editor and create a formula {@qtyperrecord}:

{%maxqty}/{%countsize}

Then use sum of {@qtyperrecord} as your summary field for quantity on hand in the crosstab. This should give you correct cell and total values.

-LB

 

[WarrenRoss]

lbass,

Ah, right you are. It's all a blur, really.

Neat solution with the SQL expressions.

 

[omacron]

I am sorry but I am at a lose on how to make the SQL expression work. Here is my direct code:

(select max(AKA.`curonh`) from AKA where
AKA.`level1` = IPINVDTL."LEVEL1"
and AKA.`level2` = IPINVDTL."LEVEL2"
and AKA.`level3` = IPINVDTL."LEVEL3"
and AKA.`level4` = IPINVDTL."LEVEL4"
and AKA.`loctn` = IPINVDTL."LOCTN")

It gives me two error:

No rowset was returned for this table, query, or procedure

then

Error in compiling SQL Expression : Syntax error here

once you click ok, it places the cursor right in front of max. I am sorry for the simple question but I have never used SQL expression and can't seem to find an example to help me.

 

[lbass]

You left out your table name in the from clause:

(select max(AKA.`curonh`) from IPINVDTL AKA where
AKA.`level1` = IPINVDTL."LEVEL1"
and AKA.`level2` = IPINVDTL."LEVEL2"
and AKA.`level3` = IPINVDTL."LEVEL3"
and AKA.`level4` = IPINVDTL."LEVEL4"
and AKA.`loctn` = IPINVDTL."LOCTN")

I don't understand how these level and location fields relate to your original thread. What happened to the item number and size fields? These must be incorporated for the SQL to work.

Please also note that the location of a cursor when using a SQL expression usually does not indicate where the error is occuring. Also, the syntax/punctuation for a SQL expression varies depending upon the datasource, so you might have to play around with that.

-LB