Help Reuse One report with new Tables

[Former Member]

I have to make several hundred reports.  They all are similar but use different tables.  They all contain formulas that are the same just different data, for example

Well30/Pump1/Runtime:Maximum

Then on the next report it will be

Well8/Pump1/Runtime:Maximum

I'm trying to save myself the trouble and make one fancy parametrized report that when I open it I select the tables I want, well 8 runtime, starts, and it fills the report and I save it with those tables.  Run it again select well 30 table, runtime, starts, fills the report and save it with those tables.

I'm trying not to spend an entire week manually replacing tables and rewriting equations.  I have watched all the videos on parameters and I can't figure it out.  I found this suggestion online:

"Actually, you can do this if you use a command as your datasource. Open a blank report->select "add command" at the top of the table list, and then create a string parameter within the command (on the right). Then set up the command like this:

Select {?table}.`field1`, {?table}.`field2`

from {?table}

Or even more simply (if you are only using one table):

Select `field1`, `field2`

from {?table}

The punctuation depends upon your datasource."

But I can't get that to work either.  It tells me it can't find the table name.  Plus even once it added the table name then how can I tell it to put this value here and this value here, especially in a formula.  It doesn't help that my table names are super long.  Is there someway to refer to them shorthand,  I cant change my ODBC formatting so they are stuck long for now.  Just take a look at image and you will see what I mean by long names and how I need to just swap out the tables for new ones, keep the formulas somehow, and make bunch of reports.

I'm a bit desperate at this point I tried to contact chat support today but they didn't seem to understand what I wanted to how to explain to do it. 

Any help would greatly appreciated!!!!

Thanks

Robert

[JWiseman]

hi Robert,

the easiest way to determine the syntax for the command is to first create a report off of tables only. add a couple of tables and link them in the database expert and then add some fields to the report and preview the report.

then go back to the database menu and choose Show SQL Query. copy this syntax. now create a new report and add a command this time, copying in the sql that you copied. you can now use this syntax as  base syntax. any punctuation etc. required by your db will be in this syntax.

in your particular case you may wish to look at the syntax of {?table}...it will most likely require quotes around it. e.g.

SELECT `{?table}`.`Customer Name` AS TEXTFIELD1

FROM   `{?table}`

note that the command must return an identical field set in each case or you will get errors. i.e. the number of fields must be the same and the types must be the same and the field names must be the same. for the latter concern, you can use dummy names like in the 'textfield1' example above.

-jamie