3 Replies Latest reply on Dec 29, 2014 11:34 AM by Jonathan Drummey

    Drilling down from a summary table to a detail table: Actions or Parameters?

    Rama Kunaparaju

      I have a report that is based on 3 tables in Vertica. Basically, I am trying to find the best way to show granular information available (like sample account numbers in 'detail' table) by clicking the summary statistic from the 'summary' table


      The 3 tables are


      1. File_info (has information about the jobs that I have identified by job_id and the record_count within each job_id)


         2. Summary (has the job_id and has the counts of all the tags)


         3. Detail (has the job_id, all the records within a given job_id identified by the rec_id and also the account numbers)


      For example, there are 100 rows in job_id '12345' . 9 of those rows are tagged for an invalid Identification code (IC_1 in summary), I want the user to see the count = 9 and also be able to drill down to that sample 10 account numbers.


      I can't do this using filter actions, because there is not linking fields between the summary and detail connections. I tried doing this with dynamic parameters (parameters in custom SQL query), but it is still not the best way to do it.


      select account_number from detail a left join file_info b on a.job_id=b.job_id

      where a.<Parameters.Tag name-1>=<Parameters.Tag-1> and b.activity_date=<Parameters.Activity/File Date>


      My tag name-1 paramter would take values like IC_tag and OD_tag and tag-1 parameter could take values 1,2,3,4


      Is there a better way to do this?