Crosstab Custom Subtotal

[fgarav]

Hi everybody,
i have strange requirement ... on a crosstab ,how i can calculate a custom subtotal .
for istance my crosstab is like this
               month
type1      value
type2      value
type3      value
type4      value
type5      value
and so on...
my requirement say that i need to put a new subtotal that it's equal to type1+type2+type3
in way that my crosstab become like this

               month
type1      value
type2      value
type3      value
                xyztotal(sum type1+type2+type3)
type4      value
type5      value


how i can do this ?
regards
Fab

[Lynn]

Maybe you could try creating a data item to group on. For example:

Code Select Expand


case [TypeName]
when 'type1' then 'Custom Group'
when 'type2' then 'Custom Group'
when 'type3' then 'Custom Group'
else 'Other'
end


Include this in your crosstab with the types nested within it you'll get the subtotal you want. You would also get a sub-total for all the others, but maybe that could be conditionally hidden?

I know there are others here more adept with crosstabs, so maybe better advice is forthcoming.

[fgarav]

Hy Lynn can u explain better ?

[Lynn]

Assume your query has three data items:

[TypeName]
[Month]
[Value]

In your crosstab, you have [TypeName] as rows, [Month] as columns, and [Value] as the measure.

What I suggest is that you add a new data item to your query containing the expression in my earlier post. Let's pretend you call it [GroupName].

Once you create this, drag it into the crosstab to the left of the [TypeName] item so that [TypeName] is nested within [GroupName].

Code Select Expand


                         2011-01
Custom Group | type1          10
Custom Group | type2           2
Custom Group | type3          20
------------ | -----------------
Custom Group |                32
------------ | -----------------
Other        | type4          18
Other        | type5           4
------------ | -----------------
Other        |                22


[fgarav]

great help Lynn, but u think that is possible  on my query calculate a dataitem
total for currentmeasure within SET TYPE IN 1,3,,5,12 where the id is the pk of type referenced on the crosstab?

[Lynn]

Did you try that and not get it to work? I don't know offhand.

What I was trying to suggest was a way to have that total computed for you automatically by just defining the group.

[fgarav]

Hi Lynn, first of all thank's for the help, your suggest seems to be ok, just another thing related to the answer u give me .
I insert the calculated dataitem as u suggest , when i run teh report i  see two row about my aggregation , the first one contain the correct data , and the second one is the total minus the value i have....
Any suggestion is very very appreciate...
Fab

[leahmarie]

I am pretty sure you can just use a calculated member and add [type1]+[type2]+[type3].  Just put the calculated member wherever you need it to be and name it something unique.