Merging cells in cross tab Union query in Cognos analaytics

[kvca11]

Hi,

I have created the Union and i have done the dummy for few columns in first query and few columns in second query.

I need to add few columns from first query and few columns from second query

After that i have added one of the column from  the union query and its getting merged means getting 2 values for the same column.

Please find the attached screen shot for the same.

Please provide your thoughts

Thanks,
kvca11

[oscarca]

Cant you just put the two columns in the same data item e.g. [Data item 1] + '-' + [Data item 2] ? or concatenate them in Framework manager ?
or can you draw up what you are trying to accomplish ?

[kvca11]

Hi Osacra,

Thanks for the advice.

We need to show the date column from first query and date column from the second query.

Need to show both the column dates from the final report

for that i have made the 0 in first query and second query i have made the same 0

thanks

[oscarca]

Example:
Step 1: Create 3 queries. In Query 1 create a data item with year and filter year = 2018, in Query 2 create a data item with year from datasource and filter year = 2019.
Step 2 Create a join on the third Query and Place Query 1 and Query 2 in the join. Create a relation with cardinality 1..1 on both sides with the operator "<>"
Step 3 add the two data items in Query 3 and then Place them in the crosstab.

Another example is:
Query1 data items "Order method", "quantity" and filter Year =2018
Query2 data items "Order method", "quantity" and filter Year =2019

Query3 create join between Query1 and Query2 on Order method setting the cardinality 1..1 on both sides and operator "="
Placing the data items in a crosstab will give you 2018's quantity and 2019's quantity in separate columns/rows.

[kvca11]

Hi Oscarca,

Thanks for your prompt response.

here is the requirement.

We have a 2 prompts where we can select values. and also some other prompts are also there(year and noofyeasr data like that)

For ex: prompt1:data1,data2 and data3
Prompt2:Data1,data2,data3

In the report layout we need to show the data for both the queries.
  few measures are also there and data columns are also there and we need to show both.

query1:total quantity,total exp,start date,end date and few other columns
query2:total quantity,total exp,start date,end date and few other columns

and in final query we need to show columns and measures from both the queries aswell need to show the variance

this is the requirement

Thanks,
ca11