free-hand sql with a subquery built using query panel

building reports desktopintelligence
1

Is there a way to write free hand sql but have a subquery be modifiable via query panel so that users can filter within the subquery. Can this be done via @script or VBA or can this be done at all?

nrusse :us: (BOB member since 2006-06-27)

2

Your question is slightly confusing. :?

You can also build a subquery in the query panel, you can filter out the rows by providing a prompt. It can be modifiable as well.

But FreeHand Sql is entirely different thing, you have to write the SQL code in the editor provided. If the user knows sql, how to filter what he/she wants, then he/she can do it too in FH sql editor. But it is risky, because of security reasons, they can also see the data which they are restricted at BO Universe level, if there are no restrictions for them maintained at the Database Level.

So I would suggest build the sub query in the query panel, Do not use FH sql for various reasons.

Hope I answered your question to some extent…! 8)

BO_Chief :us: (BOB member since 2004-06-06)

3

I am confused too. Maybe you could give us a more precise example of what you are trying to do?

jac :australia: (BOB member since 2005-05-10)

4

The problem is that the user does not know sql so we are creating some pretty complex sql. The users are wanting this as a “canned” report so they can run it over and over agian with just changing the variables they are prompted for along with the subquery which is a list of customers stored in an oracle table. But they want to be able to filter that list as well with any value that is available in the universe. Being that they do not know sql they need to have BO build the sql for them via query panel.

Thanks for the call out on security with free hand sql I was concerned that was the case.

To add a little more detail they are going to have many users that belong to several user groups and there will be about 20 universes. They want these “canned” reports to be able to be used in all these different universes. If we use query pannel to create these reports it is specifically associated with that universe. After doing the math on the number of individual reports they want it is up to over 800 reports that would need to be created, formated and maintained (way to much overhead if they request a change). With groups of 20 reports being exactly the same except for the universe it is associated with. Unless someone knows of a way to copy and move a report to another universe with out haveing to recreate from scratch.

Thanks

nrusse :us: (BOB member since 2006-06-27)

5

This is easy to do…

  1. Select Data>View Data
  2. Click on the definition tab
  3. Click on the ‘…’ button next to the current universe name
  4. Select the new universe that you want to use.

A few words of caution…

  1. When you change from one universe to another, the report will map to objects according to their name, If it can’t find a match, then you will get an error message advising you of the problem objects.
  2. Object names are case sensitive.
  3. Make sure that all of your objects in a universe have unique names, otherwise the matching may not be correct.

Why are you having 20 variants of the same universe, there may be other ways of approaching the problem?

anorak :uk: (BOB member since 2002-09-13)

6

nrusse wrote:

I didn’t get the above… :nonod:

If a customer table has 100 records, These 100 Customer ids, or Name are available for the user when refreshes a report with a prompt.

The user can select one customer, more than 1, or all 100 customers at once buy customizing the LOV.

Is this what you are looking for ?

Additionally why do you need so many universes, It will lead to high maintenance overhead in future. If you are worried about security for different groups, it can be easily taken care by ‘row-level security’ feature in Supervisor tool. Making 800 reports(huge amount) in total that too duplicates… just with a different Universe each time is not good. :roll_eyes:

I would suggest you to consider alternatives, try to do in an efficient way.

Good Luck

BO_Chief :us: (BOB member since 2004-06-06)

7

I think you are a hung jury. I was going to suggest a predefined condition with the complex sql built into it, but it sounds like the users want to be able to change that so much that you can’t predict what they want and built the smarts into it.

I think the solution might have to be education! Teach them how to build their own subqueries. Create heaps of easy to follow documentation to help them, and perhaps a suite of template reports.

jac :australia: (BOB member since 2005-05-10)