I have a crosstab that contains:
COUNT_DISTINCT_OF_CONTACTS | SUM_OF_RESPONSES | RESPONSETYPE - 100 | RESPONSETYPE - 200
SEGMENT_1 10 200 5 10
My table behind this looks like that:
SEGMENT | SUBSEGMENT | RESPONSE | RESPONSETYPE
1 A200 1 100
1 A200 1 200
1 A201 1 100
.
.
.
The problem I have is this. The data gets grouped by SEGMENT and with my COUNT_DISTINCT_OF_CONTACTS I can get the distinct count of (SUBSEGMENT). The RESPONSETYPE is across the columns and I can get for each RESPONSETYPE a count of RESPONSE. The SUM_OF_RESPONSES unfortunately is double-counted because it is added twice for each RESPONSETYPE like in the example above for A200. I almost need two queries here one that gives me the SUM OF RESPONSES for each SEGMENT and one that gives me the Count of RESPONSES for each RESPONSETYPE.
Any idea how to achieve multiple queries in crosstab?
You did not mention if this is a relational or dimensional source. You posted a different question recently that indicated a dimensional source for content similar to what you're asking about here, although you don't seem sure. Your new post identifies a tabular representation of data which suggests a relational source.
Take a look at Q. 5 on the sticky post about reporting FAQs (http://www.cognoise.com/index.php/topic,27563.0.html) so you can be sure what type of source you are dealing with.
relational source although I don't understand why on earth we need dimensional.