Post reply

Using SQL for multiple pulldowns

  • August 12, 2019 at 6:52 pm

    #3670815

    So, I need to be able to use SQL to provide options in a series of pulldowns related to an employee's space/location. They are:

    Pulldown #1: Building

    Pulldown #2: Floor

    Pullown #3: Room Number

     

    The user would select an option in each pulldown but do them in sequential order (Building pulldown first, then Floor pulldown, then Room Number pulldown). There are two key tables, FMA1 & FMB1 that contain the data needed for the 3 pulldowns:

    Table FMA1 with columns BLDGCODE , BLDGDESC:

     

    Table FMB1 with columns BLDGCODE , FLOORCD, ROOMNM:

     

    So, pulldown #1, the Building pulldown would be pretty easy, as they would literally just select their Building from the full list of available buildings from the FMA1 table. We would display both BLDGCODE & BLDGDESC from the FMA1 table, as some users are familiar with the alphabetical name of the building (A, B, C) but others know it more by the address (101 Main Street Building, etc.). We would ORDER by BLDGCODE. So, I expect it would just be a simple SELECT statement:

    SELECT BLDGCODE, BLDGDESC

    FROM FMA1

    ORDER BY BLDGCODE;

     

    Pulldown #2, the Floor Code pulldown, is where the curveball for me begins, as the options here would be dictated based on what Building they selected in Pulldown #1. If they selected BLDGCODE "A" in Pulldown #1, then they would have FLOORCD "01" and "02" as options here. If they selected BLDGCODE "B" in Pulldown #1, then they would have FLOORCD "01" , "02"  & "03" as options here. And, if they selected BLDGCODE "C" in Pulldown #1, then they would have FLOORCD "01" , "02" , "03" & "04" as options here. We would ORDER BY FLOORCD and only need to display the FLOORCD in the pulldown. Table FMB1 would be used here. How would you build this one?

     

    Pulldown #3, the Room Number pulldown, is another curveball, as you have to take into account the BLDGCODE that was selected in Pulldown #1 & the FLOORCD that was selected in Pulldown #2. For example, if they selected BLDGCODE "A" in Pulldown #1 and FLOORCODE "02" in Pulldown #2, then the only options that should be available here are ROOMNM "A2001" & "A2002". Only the ROOMNM would be visible in this pulldown and we would ORDER BY ROOMNM. Table FMB1 would be used here. How would you build this one?

     

    Sorry, I imagine this is probably an easy one to solve but I am in the very early stages of slogging through SQL and don't know how to building a SQL code that takes into account the user selections in the prior pulldown(s).

     

    • This topic was modified 5 years, 4 months ago by  mm92280.

  • August 12, 2019 at 8:47 pm

    #3670830

    If you are new to SQL, then read about DISTINCT and WHERE parts of SELECT. Happy learning!

    Have Fun!
    Ronzo

  • August 12, 2019 at 9:48 pm

    #3670847

    I assume that you are using SSRS, since you reference pulldowns.  Check out the following link on how to Add Cascading Parameters to a Report.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply