using parameters to tap into different data tables 1

[robreports]

I have created parameters before for record selection. Is it possible to create user-defined parameters from a pick list of different tablenames (static or dynamic)?I have to create a comparative report comparing table values for the same fields every three months. A new table is being created every three months containing summary numerical data.Someday we will have several dozen tables which a user might want to compare any two by typing into a tablename picklist.Seems stupid and amateurish to have to add a new table to a report every three months.What is the more polished/professional way? Create a view and add the new table to the view every three months?Thank you.

 

[Turkbear]

Hi,
Using a view that is updated as needed is by far, IMHO, the best approach to this requirement.(The more you can do within the database, the better, usually)

If the structure of the view is not changed then the added data will appear with no action needed by the user .





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

It is possible to set up a parameter within a command that is then used in the command for the table name, e.g.,

Select {?table}.`Customer ID`, {?table}.`Order Amount`
from {?table}

The command would then be used as your sole datasource for the report.

-LB

 

[robreports]

LBass, that sample code in your reply would be created under "SQL Expression Field" or "Formula Field" in Crystal?
All I have ever done is create formulas and then drag them into report where I wanted them. If I create one long,complex SQL query string and then give it a name and save it.....how do I format my report?Just drag the SQL expression formula anywhere into report and let go of mouse?I will need to experiment with this but I do understand the SQL statement logic.Sounds like it should work but I have formatting concerns. Can I add a second,duplicate "SELECT" SQL statement using "and" for the second table parameter?(this report will compare two tables).Thanks again Lbass.

 

[lbass]

This method makes use of a command, NOT a SQL expression (which can only return one value). In the database expert, after selecting your datasource, you will see "Add command" above the list of tables. You would select this and then create two string parameters in the area on the right: {?table1} and {?table2}. Then add them into the command. If both tables have the same field, you might want set this up as a union all query, like this:

select 'Table1' as whichtable, {?table1}.`field1`, {?table1}.`field2`
from {?table1}
union all
select 'Table2' as whichtable, {?table2}.`field1`, {?table2}.`field2`
from {?table2}

This would "merge" the corresponding field from each table into one field which you could then compare using {command.whichtable}.

This approach assumes that the command is used as the entire datasource for the report.

-LB