Post reply

Can i use parameter value as column name in dataset query and report table?

  • June 20, 2012 at 4:40 am

    #276586

    i want to know if (and how if possible) i can use the value of a parameter as the column name in the dataset query and ultimately display the table data of the column value in the report?

    for example my parameter on the report will have labels of Source & Customer with values of Source and Customer_Name. these values are column names in the table i want to retrieve data from.

    my query would be something like

    SELECT param.value, Company, Site

    FROM tablename

    and my report table would have param.value, company, site as its columns

    So, if selecting Source as a param it would be like executing Select Source, Company, Site and if selecting Customer it would be like executing Select Customer_name, Company, Site.

    And my report would then show rows of data of either Source, Company, Site or Customer Name, Company, Site.

    i've tried all sort of things including using expressions for the query eg ="select " & Fields(Parameters!Param.Value).Value & " , Company, Site from ifsinfo.iws_pipeline_ial" but not got anywhere.

    It's so simple to change the report table column heading using [@param.Label] i was hoping to do something similar with the actual data column selection.

    i havent found any examples of what i'm trying to do anywhere online or even people asking!

    I hope i'm clear in explaining what im trying to do.

    Any help or suggestions would be greatly appreciated.

  • June 20, 2012 at 8:36 am

    #1503487

    Below is an example using the AdventureworksDW2008 demo database where the productname language could be chosen. Given that the parameter @Source is a column name in the table and would have either EnglishProductName, FrenchProductname or SpanishProductName then...

    Data set query would be:

    declare @sql varchar(200)

    set @sql = 'select productkey, ' + @Source + ' as NameColumn

    from DimProduct'

    exec(@sql)

    Due to the declare statement the @sql is not considered as a potential parameter. You will need to have an alias for the extra column (NameColumn) as the report will fail if the all required column are not returned.

    Fitz

  • June 22, 2012 at 7:32 am

    #1504482

    thanks Fitz. i will try this out when i get a chance.

    as a quick fix i have added a dataset and report table for each set of data i need to see and then made the tables visible dependant on what is selected in the parameter.

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

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