Getting quantities for each store 2

[Light777]

I need to get a quantity value for each store based on the ItemID, however, the client wants it within a column rather than grouping the ItemID and listing the stores with a quantity behind each, which is a lot simpler. Of course people want things the hard way...

i.e.

if {Item.StoreID} = "such-and-such" and {Item.ItemID} = {current}
then {Item.Quantity}

The problem I'm having is that I can't figure out how to get the current value of ItemID or even if this is the right logic, because ItemID should always equal the current ItemID. Is there anyway to do this by grouping ItemID and then running through the group for each store?

 

[pgtek]

hi
Create a running total and have a group by store id

cheers

pg

pgtek

 

[lupins46]

Personally I don't understand the distinction between 'within a column' and 'listing'.
Can you give an example of your input records and your required output from that data.

 

[lbass]

Try inserting a crosstab with ItemID as the column, store ID as the row, and quantity as the summary field.

-LB

 

[Light777]

Sorry, let me try to explain myself a little bit better...

the database:

Item StoreID Quantity
1 1 3
1 2 5
1 3 2
2 1 5
2 2 3
2 3 6
etc.

The easy way is to group by Item and put Item in the group heading. With StoreID and Quantity in the body.

The way the customer wants it though is...

Item Store1 Store2 Store3
Quantity Quantity Quantity

 

[BFAGG]

Create a cross tab with item as the row and store as the column. Use the quantity as the summary value. This will do the trick.

 

[Light777]

Thanks BFAGG,

That would have worked...

I failed to give more detail into the scope of the project. The report needs more than just those fields...

i.e.

Item Supplier Department Category Total Quantity Store1 Quantity Store2 Quantity Store3 Quantity Cost Price

All of these values are getting pulled from basically 2 different tables that are linked together.

I also need to set this up to be able to be imported into Microsoft Retail Management Solutions.

Please help, I don't really know that much about Crystal Reports. I thought it looked to be more like Access than it is.

 

[Madawc]

You could do a Mock-Crosstab, where you define a large number of running totals that sit in the report footer and give the appearance of Crosstab. If you have Crystal 8.5, you should have an example along with the model crosstabs.

Madawc Williams (East Anglia)

 

[lbass]

Group by Item and then place supplier, department, category in the group header. Then create three formulas and place them in the details section:

//{@Store1Qty}:
if {table.StoreID} = 1 then {table.itemqty}

//{@Store2Qty}:
if {table.StoreID} = 2 then {table.itemqty}

//{@Store3Qty}:
if {table.StoreID} = 3 then {table.itemqty}

Next right click on each formula and insert a summary (Sum) at the group level, and then suppress the details section. You can drag the summaries into the group header next to your other fields. For the total quantity, you just need to place {table.itemqty} in the details section and insert a sum on it. Not sure what you are looking for for cost and price.

-LB

 

[Light777]

Thank you lbass...

That was the logic I had at first. I was wondering why it didn't work...well, I had the tables linked wrong. oops.

Thank you everyone for your help on this situation.