locked
Table/View question RRS feed

  • Question

  • Can you use a entire table or a view as a Paramter dynamically?

    My view is

    select  name,place,city,state,ordernumber,locationid from vw_workorder

    what i want is to have a way where a user can select any of the columns from the view as a parameter

    Tuesday, August 16, 2011 4:44 PM

Answers

  • It'll be a bad idea to expose anything and everything from a table/view for the user to select. If you have specific requirements to implement something like that, create specific report parameters for the columns you'd like to be exposed to the user. And you can then use OR statements in your report query to make use of any of the input fields selected by the user to filter data on the report. Your view has 6 columns and having 6 report parameters will not be a bad-looking report design at all!

     

    Hope this helps.

    Cheers!!

    Muqadder.

     

    • Proposed as answer by Sharp Wang Thursday, August 18, 2011 7:23 AM
    • Marked as answer by Sharp Wang Wednesday, August 24, 2011 9:24 AM
    Tuesday, August 16, 2011 9:01 PM

All replies

  • It'll be a bad idea to expose anything and everything from a table/view for the user to select. If you have specific requirements to implement something like that, create specific report parameters for the columns you'd like to be exposed to the user. And you can then use OR statements in your report query to make use of any of the input fields selected by the user to filter data on the report. Your view has 6 columns and having 6 report parameters will not be a bad-looking report design at all!

     

    Hope this helps.

    Cheers!!

    Muqadder.

     

    • Proposed as answer by Sharp Wang Thursday, August 18, 2011 7:23 AM
    • Marked as answer by Sharp Wang Wednesday, August 24, 2011 9:24 AM
    Tuesday, August 16, 2011 9:01 PM
  • You can do so as well.....

    you can create a parameter let say ABC

    in the available value select Specific Values 

    and assign values for each field

     

    i.e. Label    Value

         Name                   N

         Place                     P

         City                       C

         State                     S

         Order Number       O

         Location ID           L

     

    Now Your  Query should be like as follows

    Select Name from vw_workorder

    where :ABC='N'

    and (:ABC!='P' and  :ABC!='C' and :ABC!='S' and :ABC!='O' and :ABC!='Ll)

    Union

    Select Place from vw_workorder 

    where :ABC='P'

    and (:ABC!='N' and  :ABC!='C' and :ABC!='S' and :ABC!='O' and :ABC!='Ll)

    ......

    ......

    so on

     

    This should work fine 

    Thanks

     

    Tuesday, August 16, 2011 9:15 PM
  • My question is "Parameter to What?"

    If you are exposing the rows in an app (web/win), and want the app to "drill" to some detail related to that row, if so, it would be best to also surface some sort of key with the other data in the view.  That way, the drill param will only be the key.

    Otherwise (within app paradigm), you can create a query string, or some sort of other passing mechanism.

    If you are talking about another SP within SQL, you can select from the view into a set of vars and use them as params.

    Finally if you are talking about filtering the view on any one or more of the fields, again this is primarily an app scenario where you would wire up the app to use one or more of the fields (I'm assuming surfaced from a table) in a query string or other mechanism to pass back to SQL

    I know this is a pretty open ended answer, but it would be better if you could further specify your question.

    Hope this helped

     

    Wednesday, August 17, 2011 3:23 PM