Tessitura 'DISTINCT' Parameter filtration in the report dropdown

Hello all, 

This may be covered somewhere but I couldn't find it with searching here. 

If i built a view that has distinct posted_dt and posted_by, like this

select distinct posted_by, posted_dt
from T_BATCH a (nolock)
where posted_by is not null

I would like to have report parameters that return all posted_by between a given date range but DISTINCT.

I run this report for multiple dates and whenever i have multiple dates the users appear multiple times. If a single user posted multiple batches at different points in the date the posted_dt is different. This is all okay. 

However I want to build a report that looks like this - a report that pulls some details between a posting start and end date, but i gives the user the ability to restrict it by who posted the batches. It also only displays users who posted batches in the respective date range. 

The issue is I now get users appearing in my list multiple times. I can't get rid oft he date bit because I need the date bit to filter which users we see as part of the output, but I only want to see distinct users. 

Parents
  • I think you can accomplish this by looking at the basic T_METUSER table that has 1 row for each user, but then nesting a query to the T_BATCH table in your WHERE clause.

    Table = T_METUSER
    Display column = userid
    Data column = userid
    Where Clause = userid in (SELECT distinct posted_by FROM T_BATCH WHERE posted_dt between <<p1>> and <<p2>>)

    Haven't tested it out myself but I think this would work. I'm pretty certain nested queries work in the WHERE clause.

Reply
  • I think you can accomplish this by looking at the basic T_METUSER table that has 1 row for each user, but then nesting a query to the T_BATCH table in your WHERE clause.

    Table = T_METUSER
    Display column = userid
    Data column = userid
    Where Clause = userid in (SELECT distinct posted_by FROM T_BATCH WHERE posted_dt between <<p1>> and <<p2>>)

    Haven't tested it out myself but I think this would work. I'm pretty certain nested queries work in the WHERE clause.

Children
No Data