Usage of free-hand SQL

building reports desktopintelligence
1

Hi,

I’m having problems in creating a report that mimics a sql script that I’m directly running against sql server 2000.

So my workaround was to us Free-Hand Sql. Problem is i’m trying to do a query from two different databases. B.O. would only let me put in one database connection.

Here’s my sample script:

select distinct a.id, a.date_start, t.xnumb
from transaction1 as t
inner join account as a ON t.acct_id = a.id
where a.date_start between ‘8/1/02’ and ‘9/1/02’
and a.acct_type = 2
and exists (select 1 from crm_prod_02…crm_stg_icon_hist as c
where c.orderid = t.xNumb and
t.transactiontypeid = 1 and
c.sitename like ‘Ar%’ and
c.perfdate is not null)
order by a.date_start

Transaction1 and account tables are on my default db and the crm_stg_icon_hist is on another database.

Thanks in advance.

Hernan

P.S. Hey Michael W., wish I had taken your advance reporting class :frowning:

srt6 (BOB member since 2003-09-17)

2

If your second database is “reachable” via your first connection, it should still work. In other words, when you define a connection, BusinessObjects get the data it needs to establish a communication with a server / database. (The terms vary from one vendor to another.) If you can connect to the second database using the same connection, using the database.owner.table.column structure instead of just owner.table.column or simply table.column, then you should be fine.

Have you tried it? Are you getting an error?

Dave

Dave Rathbun :us: (BOB member since 2002-06-06)

3

Using fully qualified object names did the trick.

Got another problem though. When I do a view the results that it displays on the window that pops up is correct, ie.

id start_date xNumb

10000 7/1/02 2100
10000 7/1/02 2000
10001 7/1/02 3000

but after I run it, the data on the report shows this:

id start_date xNumb

20000 7/1/02 4100
10001 7/1/02 3000

It looks like it summing the id and xnumb columns for id’s that are identical.

Thanks again.

Hernan

srt6 (BOB member since 2003-09-17)

4

Hi,

You probably have not checked “Avoid Duplicate Row Aggregation” in Table Format.

Judy

JMulders :us: (BOB member since 2002-06-20)

5

Any numbers that come in a free hand or personal data provider will default to a measure, so BO is aggregating them at the report level. You just need to click on the data cube and change those objects to dimensions if that is what they are supposed to be. I wouldn’t turn off the aggregate duplicate rows unless absolutly neccesary.

Scott Bowers :us: (BOB member since 2002-09-30)

6

I don’t see that option when I’m using FreeHand Sql. I do know what you are referring and have seen that option when using a Universe.

–Hernan

srt6 (BOB member since 2003-09-17)

7

Hmm, I just ran some free hand sql and I still had the option. Usually when you run on a Universe you cant change them because that is set at the Universe level.
So, if you click on Data from the formula bar, then click view data, then click on the definition tab. Then click on the field name, you should have the option to change from a measure to a dimension.

Scott Bowers :us: (BOB member since 2002-09-30)

8

Good to know about fields defaulting to a Measure. The fields that are aggregating are account numbers so their safe to convert to dimension. Changing them to Dimension fixed my problem.

Thanks all for the help and very quick responses…

:smiley:

Hernan

srt6 (BOB member since 2003-09-17)