Crosstab with multiple groups

[SuperTime]

Crystal 9
SQL DB

My crosstab in the report footer currently looks like:

Prod1 Prod2 Prod3 etc...
AVDH 2 0 1
WarehouseError 2 0 1
WrongSize 2 0 1
AVDH 0 4 0
WarehouseError 0 4 0
WrongType 0 4 0
AVDH 1 0 0
WarehouseError 1 0 0
Missing 1 0 0
AVDH 1 1 1
ProductionError 1 1 1
WrongColor 1 1 1
AVDH 0 0 2
ProductionError 0 0 2
WrongSize 0 0 2
AVDH 1 1 0
ProductionError 1 1 0
Missing 1 1 0
CBW 1 1 0
ProductionError 1 1 0
Missing 1 1 0

But I want it to be grouped together as follows alongwith subtotals:

Prod1 Prod2 Prod3 etc...
AVDH
WarehouseError
WrongSize 2 0 1
WrongType 0 4 0
Missing 1 0 0
Subtotals 3 4 1
ProductionError
WrongColor 1 1 1
WrongSize 0 0 2
Missing 1 1 0
Subtotals 2 2 3
CBW
ProductionError
Missing 1 1 0
Subtotals 1 1 0

etc...

Please advice

 

[CoSpringsGuy]

If you want to use crosstabs then group your report by the database field that produces AVDH and CBW. Then place your crosstab in the footer of that group section.

I think you could do it without the crosstab though and make it look nicer .... might need to see a sample of your data though?

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[PatriciaObreja]

I see that you have 3 groupings for rows, and you need only the first 2 of them.

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[lbass]

In the crosstab expert, what fields do you have entered as rows (in order)? If any of these are formulas, please show the contents of each.

-LB

 

[SuperTime]

I can not do it without the cross tab because the Prod1 Prod2 Prod3 etc. is not a static list. there can be 1 product or upto 20 products.
Also the data above is the sample data.
I did create a group on the field that produces AVDH and CBW and placed the crosstab in the footer of that group, that didnt change anything.

 

[CoSpringsGuy]

I may not completely understand your desires then. I tested a similar model from my database using that method and it worked.

Since there is a limit (20) to the number of products I still see a way to do it without a crosstable.

It looks like LBass can probably help on this one though using crosstables.....

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

Please answer my questions.

-LB

 

[SuperTime]

Have fields named SubReason, MainReason and Description in that order as the rows of the crosstab.

The following fields carry the following values.
SubReason = WrongSize,WrongType,Missing
MainReason = WarehouseError, ProductionError
Description = AVDH, CBW

The Summary is on the Qty field

And the column is on the Product field

 

[lbass]

In the crosstab expert, the rows should be in this order:

Description = AVDH, CBW
MainReason = WarehouseError, ProductionError
SubReason = WrongSize,WrongType,Missing

-LB

 

[SuperTime]

Getting close but its in the opp. direction, looks like this now:

Prod1 Prod2 Prod3 etc...
WrongSize 2 0 1
WrongType 0 4 0
Missing 1 0 0
WarehouseError
WrongColor 1 1 1
WrongSize 0 0 2
Missing 1 1 0
ProductionError
AVDH
Missing 1 1 0
ProductionError
CBW

Also need to add SubTotals for each MainReason.

Please advice.

 

[CoSpringsGuy]

then group the report using the same fileds in the same order?
then place the crosstab in Group #3 header or footer?
then hide all other sections?
then you could take out the grid lines if you wanted and move the totals (which would be subtotals) to the bottom instead of the top?


I just did this and it works but the biggest problem I see with that scenario is column headings. They appear for each sub sub group

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

Please go to the customize style tab and uncheck "indent row labels".

-LB

 

[CrysRptDev]

Yeah, do as lbass had mentioned.
Group this in order in Crosstab Expert for rows:
Description = AVDH, CBW
MainReason = WarehouseError, ProductionError
SubReason = WrongSize,WrongType,Missing

Make sure in Crosstab Expert > Style ...should have Original style
then in same Crosstab Expert >Customize Style ....check on Indent Row Label

It works for me. I think the reason you got it backward because you might use different style for crosstab. For example, you might use Basic-blue, Basic-Gold, etc..put it back to original and do as mention above.

 

[lbass]

The indent on the row label should be UNchecked.

-LB

 

[CoSpringsGuy]

LB,

what you have explained takes care of the crosstab structure and order but more will have to be done to get the subtotals that the post is looking for right? Im confusing myself potentially...

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[CrysRptDev]

Hi lbass,

How is your days going? Since this question is similar to owner's post, so I just post it here instead and this question is for you. Same as SuperTime's crosstab example, in addition, I want to add in Crosstab Expert colum under each Product, I also want to display "Count" and "%". For example, under Prod1, there are 2 sub-columns showing count and %, Prod2 has the same thing count and %...etc. Is it better to write that in crosstab formula or write queries in my database? if it is better to write formula, how to write it so that it will count all 3 groups and percentage according to each rows? and also if the percentage for SubReason (in SuperTime's sample, third group) is less than 5% then don't display in report or crosstab, only display data that greater than 5%.

Thanks a lot,

P.S Anyone have any ideas, please advice!

 

[CrysRptDev]

LB,

How come i uncheck on Indent Row Labels, it does not display nicely like SuperTime's sample? If I uncheck it, it add extra box around each group like original style. It does not look like drill down like SuperTime's sample!?

 

[SuperTime]

That was it... works great now!!!!

Thanks a lot to everyone for helping on this one.

 

[CrysRptDev]

It addtion to my first reply here. It only works if I have "Column Total on Top" check box and uncheck "Row Total on Left" box

 

[SuperTime]

Hi CrysRptDev, I didnt see your post ealier... yes I too get that block of space for each row in the crosstab. I think thats the way its supposed to be and we will have to live with it if we go for the Original Style of the crosstab. Unless if there are some other suggestions....