Crosstab calculated column

[CoginAustin]

I want to create a cross tab that has the following:

   a   b   c  z
w 1   2  2  1
x  1   2  2  1
y  2   4  2  2

Column z would be Column.B - Column.A.  A calc needs to be done to get the correct value since the rows contain the correct value. I thought adding a tabular set with 1 model containing values for a,b,c columns and another tab model with the new column.z unioned would work but it doesn't, or, I am doing it wrong.

Any help is appreciated.

[BIsrik]

did u try just creating a calculation column in the query and use that in cross tab.

srik

[bdybldr]

Austin,
You have to have the same number of data items and same data types in both tabular models of the tabular set.  You can define the A, B, and C data items as 0 in the "right" tab model and Z as 0 in the "left" tab model.

But, as Srik suggested, I would just create a calculation in the query if possible.  Can you provide specifics?

[JoeBass]

I'm not sure why your tab set isn't working but, what about replacing it with a tabular model?

Query
  Tab Model 1 (Add a filter(s) that joins a common field from Tab Model 1A to Tab Model 1B) Create your calc at this level.
    Tab Model 1A  (Add whatever filter you need)
    Tab Model 1B  (Add whatever filter you need)

While we're on the subject, can someone explain when a tabular set is most useful?  I haven't found a need for them yet. 

[BIsrik]

i had that use in creating discontinous cross tab...i was supposed create a cross tab report in which problem type and problem method needs to be displayed in one dimension and time period as another dimension and measure the number of problems.

Srik

[bdybldr]

Joe,
A tabular set works just like a union query...it allows you to combine the results of disimilar queries into one query.

I use them all the time.Ã,  For example,Ã,  If you want to display budget vs. actual, you can use the following:

Budget:

Select Month, Dept, SalesÃ,  Where sales_type = 'A'

UNION

Select Month, Dept, SalesÃ,  Where sales_type = 'B'

Not the best example but it is very helpful when conbining disimilar queries.

It's also used when you want to display the data over different periodsÃ,  such as Month, YTD (you can define these in your filter:

Select Month, Dept, Sales AS MonthSales, 0 AS YTDSales Where trxDate = current month

UNION

Select Month, Dept, 0 AS MonthSales, Sales AS YTDSales where trxDate = current year

Again, not the best example but I hope you get the general idea.

[JoeBass]

Thanks bdy, I understand how it works, but I'm not sure when they are more appropriate than a tabular model.  Tabular models are more flexible in that you do not have to have the same columns in each of the children as you do in a tab set.  I'm guessing that tab sets are more efficient?

[bdybldr]

Well, as an example, if you wanted to display the different time categories for sales as in the second example above.  In a tab model, you would have to use IF...THEN...ELSE constructs for each of the columns.  Whereas, a tab set allows you to define filters on each column without using IF...THEN...ELSE constructs to define your query items.

[JoeBass]

We're not on the same page bldr.  Here is a quick example of a child tabular model type report that combines results from different date ranges on one line.  Hope this helps.

[bdybldr]

Ok, I see what you're saying now.  I thought you were talking about a tabular model, not a child tabular model.  You right.  They do seem to function in the same way.  It's not necessarily true that the columns have to be the same in a tabular set just the number of columns and the datatype.

I imagine it's a question of efficiency, ie. does a union query run faster than a subquery?  That would be a good test.

[bdybldr]

Hey Joe, Just for fun I created the same report twice, one with a tabular set, the other with a child tabular model.  I saw absolutely no difference in efficiency.  Of course, this was just a non-scientific test.  So to answer your question, I don't know what the difference is.

[JoeBass]

Cool.  Our dataset isn't big enough for much of a stress test.