Using SELECT in the FROM clause in Designer

[legend8]

In the Designer, is it possible to create a SELECT statement in the FROM clause? For example:

Select *
From tbl1 table1,
(Select a, count(*) ct1 from tbl2 group by a) table2
Where
.........

I appreciate if someone has a solution for this.
Thanks

 

[BobWard]

I dont think youd be able to do it without editing the sql in the report. You could create a database view though and add this to the universe

 

[legend8]

BobWard, thanks for the response.
However, do you know if I use a hand written SQL (i.e. not using the usiverse), will the 'report bursting process' still work?

 

[BobWard]

Broadcast Agent report bursting will not work would be my hunch. Interesting you mention repot bursting though. as you are probably aware Broadcast Agent does not do True report bursting in tht it refreshes the query once for each permition of the reports. This is a major issue for me as a want to produce reports for over 1000 customers on a daily basis.. Doing the maths this soon becomes unscaleable. I have just received some literature on a product called info-portal which claims to do 'True report bursting' from BusinessObjects, ie one databse refesh only. Have you heard anything of this product ?

 

[bmacman]

legend8,
It appears that you wish to do a derived table and unfortuantely that is not possible in the current BO configuration. I hoped that they would have changed that by now (we are currently using 5.1.2), but we have to get our queries to run by creating subselects, or doing what Bobward suggested and have a view created to give you desired results.... Hope this helps

B

 

[legend8]

Hi guys,
I guess create a view in the database is the only way to go since I need to "burst" the report to different offices.
I haven't heard of the product 'info-portal' before. BobWard, in your situation with the 1000 customers (maybe more), do you create the reports overnight or are they online reports (i.e. thru Web Intelligence or asp pages)?

 

[BobWard]

Well at the moment i use Broadcast agent to do many refreshes over night and save as html. This gives me many problems

1. Many refreshes (it takes too long)
2. No Security (any user can view other peoples html)

Webi is not a possible solution due to cost and low performance so ive been looking at info-portal. info-portal promises to give me true report bursting and secure access to personilsed information for my users. If your interested you should take a look at the web site for info-portal its something like

http://www.mahbs.co.uk/

 

[legend8]

Thanks for the info.