I’m using Hyperion Interactive Reporting Studio version 9.3.1 to write my first report.
The query that I am trying to produce is in the form
SELECT A, B FROM CW_STATEMNT_BEN_FEAT
UNION
SELECT C,D FROM Table2
…where the data type of A and B matches respectively to the data types of column C and D.
My steps involved creating a query, CW_STATEMNT_BEN_FEAT for the first select statement. Then, I clicked the menu “Query => Append Query which resulted in another Query tab being added to the Query section. From drop down I changed the default selecton from “UNION” to “UNION ALL” to get duplicate value combination returned.
Once on the new “Query” tab (my 2nd query) was added, I was surprised to see that the app reproduced the query on the first tab (CW_STATEMNT_BEN_FEAT) on the second tab (Query). My initial reaction was that the app was trying to be helpful by assuming that my 2nd query would be similar. However, after designing the second SQL to draw from TableB I was surprised to find that the changes that I made on the inserted SQL tab (Query) were mirrored on the first. Essentially, I could not find a way to design a query in the second tab (Query) without also simultaneously changing the query on the original tab.
Why is this happening? How can I create the SQL above that uses a UNION ALL?
Separate issue: I noticed that the below image is not displaying in Google Chrome for some reason but it shows when I use IE 9.
The answer is that you drop table A AND B on the first tab without a join between them but on the first tab, to only drag the columns of Table A as the Requested fields. Similarly, on tab B, drag the columns from Table B to the Request columns.
Add this to the list of reasons why I say BRIO is unintuitive, a long list after only 3 days of experience with it.