Totaling different groups together

[crystaldev1]

Hello, I'm using CR 11 and Sql Serve 2008. I have several groups and just wanted to see how I can group the totals as desired. Here is the example layout:

Group1: Active
Group2: Import Parts
Group3:
CatA $1
CatB $2
...
CatV $8
Group2: Domestic Parts
Group3:
CatA $3
CatB $4
...
CatZ $6


Combined Parts

CatA $4
CatB $6
....
CatV $8
Catz $6
Group1: Inactive
..
..
------------------------------------------------

So my question is how can I combine Group2 together but still show totals for each categories in Group3 as shown above (Combined Parts). I'm sure I can use variables or subreports. I'm just trying to figure out the best option. Note that number of Group#3 items can change for each Group#2 (Ex, Group#3a has A through V while Group#3b has A through Z. Thanks.

 

[lbass]

Insert a crosstab in the Group #1 footer that uses your category field as the row field (whatever the name of the field is you are grouping on for group #3), and then add sum of amount as your summary field. This should work unless you have row inflation (duplicate results). If it doesn't work, please show the content of your Group#3 summary field if it is a formula.

-LB

 

[crystaldev1]

Thanks LB. I think the crosstab would work. But the only problem is the columns and running totals in crosstab. I need to create about 4 columns which all of them would have running totals as the row total for each based on certain fields of that record to total up. I also have few columns such as ColumnA - ColumnC, ColumnB + Column D.

 

[crystaldev1]

Also, is there a way to have the columns in crosstab show up regardless if there's any record that matches the column? I have 7 columns and need every column to be aligned with the page header columns. Thanks.

 

[lbass]

Why are you using running totals? Do you have row inflation?

It sounds like you have multiple summaries you want to show for each category. This can be done in a crosstab, but it might be that in the case a subreport would be easier. You could save the current report as a subreport that is linked on the group #1 field, and then place it in the group footer. Remove the Group #2, leaving the category group. Then you use either running totals or summaries of conditional formulas for the columns, and create formulas for the more complex summaries.

-LB

 

[crystaldev1]

You are right. Subreport would be easier but I am trying to avoid using subreport since this report contains 3 other subreports. I am trying to do it in a crosstab. The crosstab is in the group footer as you suggested which is grouping it correctly. I just have to figure out how I can subtract columns. I have two questions:

1. For correct alignment of the columns, I need to have at least 1 record that belongs to each column which is not the case. So I need to somehow insert static columns.

2. How can I subtract columns for a column. Ex, ColumnC = ColumnB - ColumnA.

Thanks.

 

[crystaldev1]

I would like to elaborate on question #2 above.

I have selected a field "Type" for my "Columns" in crosstab. But in addition, I would like to show the difference as a column too. Ex,

TypeA TypeB TypeC TypeA-TypeC TypeB-TypeC

Is this possible in crosstab?

 

[lbass]

So you have category as the row and type as the column? Are the types always the same per group? What is the maximum number of types? You can subtract columns, but you need to create the columns as summary fields, using conditional formulas. I don't want to explain this unless there are a reasonable number of column instances and comparisons.

-LB