We have an historical transaction group db table that consists of multiple columns, (siteXXXgalstoday, siteXXXgalsyesterday, siteXXXgalsweek, etc.) for at least 75 sites. Is there a way to dynamically extract these statistics pertinent to one site into a table dataset from a sql query or do I need to manipulate the dataset? I looked for a way to do a select column dynamically from an sql query but was not successful.
Well, if your columns are named like that consistently for each site you can create a dynamic SQL query by sticking in the site. Something like:SELECT
{Root Container.SiteName}galstoday,
{Root Container.SiteName}galsyesterday,
...Where the {Root Container.SiteName} just points to a property that contains the site name. You can replace that with a dropdown list or whatever you need.
Works great! Exactly what we needed. Thanks