Multiple queries/Merged dimension dispalying data in report

[Former Member]

Hi All,

I am using BOXI 3.1, In my report i have 2 queries both are using the same universe. First query will return data for current date and second query return for previous date. This is the only diffrence between 2 query apart from this it has same number of objects and filters.

I am doing merge dimension on basis of Transaction number, so the report need to display only those transaction number which is present today and yesterday.

Now i need to display the data in report such that 1st row will dispaly data for current date and second row will display data for previous date so user can compare the data for same transaction number.

Now when i drag transaction number and measure objects from query1 and same from measure from query2 below (i.e. second row) it display data perfactly but when i drag dimension objects whose values are diffrent for both days the report displays 4 rows instead of 2 rows. It multiplied the rows.(If dismension value is same then its working fine).

Can anyone please tell me how to display the data for 2 days one below other with dimension values are diffrent.

Thanks for the help.

[Former Member]

Hi Nimesh,

This result is fairly obvious. Bobj will make all unique combinations for Dimensions you take.

However there are ways to enforce Combinations.

Kuldeep is right "Mergerd dimensions act as Inner Joins" however you can enforce Left outer Join/Right Outer join in a single block bu using [Query1].[Transaction_Number] or, Right outer join by replacing [Transaction_Number] with

[Query2].[Transaction_Number]

Try the combination of

[Transaction_Number]

[Query1].[Transaction_Number]

[Query2].[Transaction_Number]

1st Alternative: You can use Concatenation function with carriage return to view two measure objects in a single row.

e.g. =[Measure1]+ " "+[Measure2]

use Ctrl+Enter to add newline so that measure2 shows up in next line. now you need no two rows. Highten the rows only.

2nd Alternative: Try using single query. Copy both queries and Goto Edit Query and change

Select ...your query1....

Union

Select ....your query2...

(**This might again give you four rows for unmatched dimension )

3rd Alternative: Try removing TRAILING BLANKS if Dimensions are same and still repeating rows. Use Ltrim() and rtrim() or, put formula =if( [Query1].[Transaction_Number]=[Query2].[Transaction_Number];[Query1].[Transaction_Number];[Query2].[Transaction_Number]) etc.

(** This could be little irrelevent)

Post your experiences.

Thx & rgds,

Prabhat 09657720647

[former_member207342]

well merge dimension works similar to inner join.

you can make one dimension for previous date using relativedate.

like.

date2 : RelativeDate(date1; 1)

and use this dimension for merging. It will give correct result.

--Kuldeep