summing totals dynamically in colums and rows..

[Former Member]

hi all,

I have a Orocle Database table with the following struture.

ShopNumber ShopName ShopItem NumberSold Expired Total initial stock

00609001....' K1................ Fanta........ 176............... 4................... 500

00609001....' K1................ Coke........ 156............... 2...................500

00609001....' K1................ Sprite........ 134............... 1................... 500

00609004....' K2................ Fanta........ 45............... 4................... 500

00609004....' K2................ Coke........ 56............... 5................... 500

00609004....' K2................ Sprite........ 78............... 5................... 500

I would like to present the data in Crystal reports in the following format..

ShopNumber initial stock Fanta(F) Coke (C) Sprite (S) expired(e) Total(Fcs+e)

00609001..... 1500............. 176..........156..........134............7................473

00609004..... 1500............. 45............56...........78............14................193

inital stock is a total of all the drinks in a particular shop.the expired amount is a total of all the expired drinks in a particular shop.

The types of drinks can increase or reduce, its dynamic. the same applies to the shops.. we can have more shops. i have tried to use a cross tab, but the cross tab is rigid as one can not add a field which is not directly related to a drink type(eg expired amounts which are totals)..

I would appreciate if i could be helped. thanks in advance..

[Former Member · Accepted solution]

create a group by shopnumber

then by shopitem

anytime this report is run and there are new items it will auto group them

create your formulas subtracting out your Fanta(F) Coke (C) Sprite (S) expired(e) Total(Fcs+e)

place them in the shop item group

[Former Member · Accepted solution]

Thank you for your response..

i tried your solution by grouping, but i get the following structure on the crystal report..

609001* .......Initial stock...Sold*

Fanta ........... 500............... 176

Coke ............ 500............... 156

Sprite........... 500............... 134

609004

Fanta ........... 500............... 45

Coke ............ 500............... 56

Sprite........... 500............... 78

i needed the struture which i note below.....

ShopNumber initial stock Fanta(F) Coke (C) Sprite (S) expired(e) Total(Fcs+e)

00609001..... 1500............. 176..........156..........134............7................473

00609004..... 1500............. 45............56...........78............14................193

Edited by: chulu123 on Jun 13, 2011 10:37 AM

[Former Member · Accepted solution]

Provided you know columns you can do a manual cross tab

Group by shop number then create 4 formula

@Cokes

If namefield = 'coke' then valuefield else 0

Repeat for fanta and sprite

@expired

If otherfield = 'rxpired' then valuefield else 0

Add these to details and suppress detail section, add sum summaries for these formula to shop group footer

Then create formula, and also add to Shop Group footer

@total

Sum(@coke, shopID) +Sum(@fanta, shopID) + Sum(@sprite, shopID) - Sum(@expired, shopID)

Ian

[Former Member · Accepted solution]

Thank you Ian for your answer.. It works.. the only issue left out is that of the report being dynamic.. with your implementation, every time we add a new drink type ..lets say Mirinda, we have to modify the report object. it could be best if the report could dynamically adjust to new drink additions or indeed subtractions. Any workaround?

[Former Member · Accepted solution]

You might be able to use a standard cross tab.

Create a formula for the columns, not sure how your data is structured, but some how you will need to differentiate between stock and expired, and combine all expired into a single column.

It might be better to do all this in a SQL command and then report off that.

Ian

[Former Member · Accepted solution]

Thank you for your suggestions. They work.. But i needed the report to be Dynamic. So i had to redesign the database table to suit the report structure. i designed the report to have 20 text fields(Which i think is the maximum number of products).then i made a counter of the number of products so that i can allow only the products in the datasouece and the rest of the place holder fields are conditionaly suppressed. the text field width also is conditionally adjusted according to the number of products for the report to be presenatble..

Thanx again.