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
Maybe you could try creating a data item to group on. For example:
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.
Hy Lynn can u explain better ?
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].
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
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?
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.
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
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.