SQL Object In Report Studio

[craigalaniz]

Hi All

Here is my issue. Basically I have records with a account_id and a visit_id and visit_id2. visit_id2 links records together, so for example

Record 1   100, 4, 20

Record 2   200, 20, NULL

In my report I am getting record 1, but I need to use a SQL Object to link to get record 2 to get record 2 account_id.

How do I join them together using a WHERE statement? Can I send record 1 visit_id2 to sql object to get the record 2 account number.

SELECT a.account_id

FROM "prdAccountDim" a

WHERE @visit_id2 = a.visit_id             


I hope I am not being too confusing.

Thanks

Craig

[MFGF]

Hi,

The ideal place to do this is in the framework - add a second query subject pointing to the same table and add a relationship between them. Adding SQL into a report is a nasty end-of-the-world-so-desperate-last-gasp choice to be avoided if at all possible.

If changing the model is not an option, bring in another query containing the same items and add a join between the queries, feeding into a third query. Base your report off this third query.

Cheers!

MF.