calcualtions failing in crosstab report

[kmuller]

I have a crosstab query with embedded calculations (cell overrode and calculated field drug in) in the % Last Pd Field that works fine when I have one data item on the left of the crosstab.  But the calculations break as soon as we add the second item to the left side of the crosstab. If I report on only 1, the variance calculations work, but as soon as the second is added, it breaks and all the variance calculations break. The report and the queries behind it are pretty complex, but it is odd it only breaks when I add in the second service line.

I figured out that the numbers we are seeing in the variance are a total for all the service lines shown. I took the query down to 1 calc with 2 service line and am trying to get it working.

I think the issue I am having with the crosstab variance totals are directly related to the union that I am using to form my main query. The variance seems to be taking into account the total for the entire report and not just for each service line.

Tried this calculation, but now instead to a variance for all service lines, I get the variance for the bottom service line.

(total([CY Gross Rev by Case]) for [REPORTING_SERVICE_LINE] - total([PY Gross Rev by Case]) for [REPORTING_SERVICE_LINE])/total([PY Gross Rev by Case]) for [REPORTING_SERVICE_LINE]

Any ideas on how to get the calc to work?

-k

[MFGF]

Just a quick thought:

Try putting the 'for' clauses of your totals within the parentheses for the total.

You calculation would look as follows:

(total([CY Gross Rev by Case] for [REPORTING_SERVICE_LINE]) - total([PY Gross Rev by Case] for [REPORTING_SERVICE_LINE]))/total([PY Gross Rev by Case] for [REPORTING_SERVICE_LINE])

May be what you need, or maybe your example was just a typo and you are doing this anyway...

Regards,

MF.

[hendrixski]

(total([CY Gross Rev by Case] for [REPORTING_SERVICE_LINE]) - total([PY Gross Rev by Case] for [REPORTING_SERVICE_LINE]))/total([PY Gross Rev by Case] for [REPORTING_SERVICE_LINE])

MF, maybe this is a good place to ask a question I've been dying for an answer to.  Would there be a way to write that code above with variables so that it's readable by humans?

for example something like:
  var TotalCYGross = total([CY.... )
  var TotalPYGross = total([PY.... )
  (TotalCYGross - TotalPYGross)/(TotalPYGross)

It's a simple question really, is there a way to instantiate variables in Cognos MDX, and how.