Bursting on a Union query

[Cognos_World]

hi,

I have a burst table with almost 1000 entries. And I want to burst the report based on that. I have 2 lists on the report layout.

Using Master Detail relationship takes way too long for the report to complete. Hence, I first did a join on the Query 1 with Burst Query and Query 2 with Burst query, then used these 2 join queries in the union to get my final query to be used in the report. So far so good.

In the burst option, I used this final union query for burst group and burst recipient. And while validating, it gives the error "The union query cannot be nested." And there is no nesting in my report. The generated SQl though, has quite a few sub selects.

So, I tried other way round. I first did my union and then joined it with the burst query, and I used this query for bursting. I got the error "The queries for the set operation are incompatible"

Is there any other way to make bursting work, without using the master detail?

Thanks in advance!

[Lynn]

The second error about incompatible queries indicates that the columns in the two queries are not matching. You may have more columns in one query than the other or the datatypes for a particular column may be different between the two queries.

[Cognos_World]

Thanks Linn.
If I disable the burst option, the report validates fine. I also run the tabular data of the union query and it works fine.

[Lynn]

Not sure I follow you...

The union query seems OK because you can view the tabular data....

You then join union query to the query with your burst keys and set that as the burst query in burst options which produces the error?

Can you run the report for a small test set of data without the burst and see it works as expected? Can you see any generated SQL that might give a clue?

Sorry I'm coming up empty for you.

[Cognos_World]

Thanks for your response.

You then join union query to the query with your burst keys and set that as the burst query in burst options which produces the error?

Yes.

Tried running on a small data set, get the same error,"The queries for the set operation are incompatible"

[Lynn]

"QueryA" and "QueryB" get unioned to "UnionQuery" and you can see tabular data for this, right?

"BurstKeyQuery", presumably, also runs fine for tabular data, yes?

Lastly you have "FinalQuery" that is a join of "UnionQuery" and "BurstKeyQuery"...does this one run for tabular data? You are doing a join rather than a union of these two, right?

Is there any difference between having the burst options set vs. not set in terms of the error?

The only other thing I can think of is finding out if your FM model would support just including the burst keys from the burst key table directly in the base queries (QueryA and QueryB). Then you'd just union them and eliminate the last join.

Totally grasping at straws here...

[Cognos_World]

"QueryA" and "QueryB" get unioned to "UnionQuery" and you can see tabular data for this, right?
Yes

"BurstKeyQuery", presumably, also runs fine for tabular data, yes?
Yes

Lastly you have "FinalQuery" that is a join of "UnionQuery" and "BurstKeyQuery"...does this one run for tabular data? You are doing a join rather than a union of these two, right?
Yes

I got my error resolved. Had one data item mismatch in my union.
Because, I have my current year and last year data in the "FinalQuery", I am using a query reference to separate the CY and LY. I am then using these 2 referenced queries as my report list queries.

I have my burst option set on the "FinaQuery" so that the bursting should happen to both the referenced queries subsequently.

The report gives burst outputs, but, I am getting my CY data correctly and in the LY data container, the bursting is not working. It mixes the values on which the report was bursted.

I have the right burst groups and burst recipients set in the burst options and the joins are correct.
Somehow the second container is not identifying the burst.
Attaching the screenshot of the query structure.
As per the Query Structure, the burst option is set on "Union Burst Query"
Thanks!

[Lynn]

I thought the point of getting all the data in one query was so you could use only one layout container and avoid the master-detail. Can you get rid of the query references and use one list with grouping/sorting to present current year and last year?