Impromptu-Using alias columns and outer joins

[ShellsG]

We use Cognos Impromptu to report on an application that is highly customizable by its users. I’m finding that to be a great challenge – there are many data elements that I can’t define in the catalog because they are defined by the user. Right now we only use one catalog for every user and we don’t allow the users to edit the catalog or create their own catalog elements.

For example, users would like to be able to report the time that a button was clicked in our application. However, the names of the buttons in our application can be customized by the user. If I were to write this report as a SQL query, I’d do something like the query below (The custom names of the buttons are WITHTRIAGE and WITHNURSE)

SELECT c.mpi_id,
c.pt_enct_no,
c.entered_dt,
wt.pt_status_etm WITH_TRG_DT,
wt.pt_status_cd WITH_TRG_CD,
wn.pt_status_etm WITH_NRS_DT,
wn.pt_status_cd WITH_NRS_CD
FROM ptcurstatus c LEFT OUTER JOIN ptstatushist as wt
on c.mpi_id = wt.mpi_id
and c.pt_enct_no = wt.pt_enct_no
and wt.pt_status_cd = 'WITHTRIAGE'
LEFT OUTER JOIN ptstatushist as wn
on c.mpi_id = wn.mpi_id
and c.pt_enct_no = wn.pt_enct_no
and wn.pt_status_cd = 'WITHNURSE'

I have considered creating alias columns in my catalog (pt_status_etm as Status Button Time 1, Status Button Time 2), but then I run into a problem with outer joins. If a user building a report builds a filter like “Status Button Code 1 = ‘WITHTRIAGE’”, this will step on the outer join.

Any ideas?

Thanks - Shelley