I am using a join of two queries to build two crosstab reports. In one of the crosstab reports I want to add a query calculation that calculates a percentage of crosstab1 total for a row/crosstab2 total for a row. Cannot get this to work. Can this be done and if so how? format of crosstabs is below:
Month of the year Total Month of the year Total %
Business Unit Count of SARS Filed Count of Errors on SARS Filed
Based on what you say, I believe you need to join the two queries on "Month of the year" so Cognos can actually determine the column dimension to divide on.
Yes, I did join the two queries on file date and business unit. I am trying to divide total sar errors by total sars filed. Both of these totals are in the joined query in the main or parent portion of the query. The query calculation is [Total(month_errors)]/[Total(month)]. The error I am getting is
PCA-ERR-0062
The "cn" node is not expected to be a "value
I think i see your problem. In the combined query bound to the crosstab, create a DATA ITEM for the divided totals, not a query calculation. Query calculations belong outside of the queries I believe. Then add that data item to the crosstab.
This issue still exists almost 5 years later in Report Studio v 10.2.1.
Fortunately I was able to find information about it here on Cognoise.
I am disappointed in the product design.
Cross tab technology has been around for a while.
Ended up redsigning a single query to take the place of the two queries I wanted to combine to power the crosstab.
See you never know who you may help even years after you post your questions.