There are a number of ways you can accomplish what you are trying to do.
You haven’t provided enough information for anyone to clearly give you the ‘best’ option, but some things to consider.
If your users are on the low end of the tech usability spectrum, and you do not or cannot give them access to create thier own WEBI reports, you can acomplish this all via a Universe set of prompt objects.
The basic idea, is you create a prompt in the universe, that reads a table (or view or derived table) with a list of valid columns… Prompt the user to select one of those values, and based on that selection return a specific object.
For example… if you had a table you create called “ColumnTable” with values of “Sales $” and “Units”. (You can use a derived table for this rather easily inside the universe)
Then you would create an object with the syntax similar to:
Select:
Case When @PROMPT('Select Column1:','A','ColumnTable\ColumnField',single,constrained)) = "Sales $" THEN @Select(<Objects>\<salesobject>)
ELSE
Case When @PROMPT('Select Column1:','A','ColumnTable\ColumnField',single,constrained)) = "Units" THEN @Select(<Objects>\<unitsobject>)
Etc....
END
Then when you build your WEBI/Crystal report, you would use Column 1 in both the query filter and data objects… showing that data object on the report somewhere… it will automatically pick the proper data from the database based on the user prompt.
Do this for all 10 columns, and you have your dynamic report.
Now, while that works, that is a lot of work for your developers to create and debug.
The easier solution is if your users are somewhat tech savy and you can give them access to create thier own reports, you’d be MUCH better served training them on how to use WEBI to create thier own reports from the universe you provide (It can even be a scaled back universe just for this report if needed). The gains you get here will trickle to other projects as well and releive some of the reporting needs off IT (though it might cause other issues , so it’s a balancing act sometimes).
I think you were looking for more of a solution like option 1… but option 2 is a much better long term solution as others have suggested.
JPetlev (BOB member since 2006-11-01)