Jump to URL - Base URL portion

I need to make the hyperlink from the report based on the current virtual direcrory.

If I do not havehttp://server/virtualdirectory/ in the expression, it will not make it hyperlink.

But if I hard code it, when I move to production I will need to change it.

I can make it a parameter and fill it in the load but I am looking for a code solution that will make it smart to know where the report is running from.

Any one?

Begin you hyperlink with ~ (tilde) character and then /. It will be replaced with Virtual directory name by ASP.net at runtime. for ex: in your case you can refer to http://server/virtualdirectory/test.aspx with ~/test.aspx.|||

I put in the navigation tab in jump to URL: the expresion

="~/OTDByFamilyPlant.aspx?FromDate=" & Parameters!FromDate.Value &"&ToDate=" & Parameters!ToDate.Value &"&FamilyID=" & Fields!Customer_Family_ID.Value

it did not even change the cursor to a hand.

Do I need to do something else?

|||Please note that you can't resolve this URL in browser navigation bar, as the URL starting with ~ will be resolved at the server end by ASP.NET. If you use any URL that starts with ~/ in for server controls in ASPX pages, it will be resolved at the server end as corresponding virtual directory.

Hope I am clear.|||

Not clear at all.

I was looking for a solution to show hyperlinks in a report based on the current virtual directory.

You suggested to use ~/ as a placeholder that will do the trick.

It did not!

What do I do in a report?

Is there a command solution?

Joining a query (inner/outer)

I have this current sql query which works great, but it only returns
the rows that exist in all tables.
I need to return all rows from po_cfg_Budget B and simply fill in 0 if
there are no totals.
Any help would be greatly appreciated.
select S.*,
b.Budget,
B.Description,
D.DeptID as DeptDescription,
(B.Budget - NotApproved - Approved) as AmountRemaining,
(B.Budget - Approved) as AmountApprovedRemaining
from BudgetStatus S,
(select * from po_cfg_budget) B,
(Select * from po_cfg_dept) D
where S.Years = B.BudgetYear
and S.Months = B.BudgetMonth
and S.DeptID = B.DeptID
and S.AccountID = B.AccountID
and S.DeptID = D.BudgetDeptID
order by B.DeptIDThis might work for you (passes syntax check but not actually
tested)...
select S.*,
b.Budget,
B.Description,
D.DeptID as DeptDescription,
(B.Budget - NotApproved - Approved) as AmountRemaining,
(B.Budget - Approved) as AmountApprovedRemaining
from BudgetStatus S
left outer join po_cfg_budget B on S.Years = B.BudgetYear
and S.Months = B.BudgetMonth
and S.DeptID = B.DeptID
and S.AccountID = B.AccountID
left outer join po_cfg_dept D on S.DeptID = D.BudgetDeptID
order by B.DeptID
If you have null dollar amounts you can wrap those in the ISNULL or
COALESCE function.
Bryce|||Not entirely sure what you are looking for but try this.
select S.*,
b.Budget,
B.Description,
D.DeptID as DeptDescription,
coalesce((B.Budget - NotApproved - Approved), 0) as AmountRemaining,
coalesce((B.Budget - Approved),0) as AmountApprovedRemaining
from po_cfg_budget B
left outer join BudgetStatus S
on S.Years = B.BudgetYear
and S.Months = B.BudgetMonth
and S.DeptID = B.DeptID
and S.AccountID = B.AccountID
left outer join po_cfg_dept D
on D.BudgetDeptID = S.DeptID
order by B.DeptID
The inline views that you were using were redundant.
"Dave" wrote:

> I have this current sql query which works great, but it only returns
> the rows that exist in all tables.
> I need to return all rows from po_cfg_Budget B and simply fill in 0 if
> there are no totals.
> Any help would be greatly appreciated.
> select S.*,
> b.Budget,
> B.Description,
> D.DeptID as DeptDescription,
> (B.Budget - NotApproved - Approved) as AmountRemaining,
> (B.Budget - Approved) as AmountApprovedRemaining
> from BudgetStatus S,
> (select * from po_cfg_budget) B,
> (Select * from po_cfg_dept) D
> where S.Years = B.BudgetYear
> and S.Months = B.BudgetMonth
> and S.DeptID = B.DeptID
> and S.AccountID = B.AccountID
> and S.DeptID = D.BudgetDeptID
> order by B.DeptID
>

join or subselect ??

Not sure if this is the right group to post this to but.

This is the current query that I have.

select tableA.id,tableB.artist,tableB.image,from tableA,tableB where
tableA.image = tableB.image AND tableB.price >0 AND tableB.price < 20
order by tableB.price DESC'

What I need is, for each row returned I need information from a third
and fourth table. tableC, and tableD.

tableC has information ( the tableA.id = tableC.eventId) that I need to
obtain tableC.accountId = tableD.accountId in order do select the
the binding information in tableD between a Vendor(name,address..etc..)
and tableB.image

Any help would be greatly appreciated.If I'm reading you correctly, you should be able to do this pretty easily.
In the SELECT statement, list all the fields from each table that you want
to see. In the FROM statement list the tables. In the WHERE statement list
all the parameters and relationships. So:

SELECT
tableA.fields
tableB.fields
tableC.fields
tableD.fields
FROM
TableA
TableB
TableC
TableD
WHERE
tableA.image = tableB.image AND
tablea.id = tableC.eventID AND
talbeC.accountID = tableD.accountID AND
tableB.price >0 AND
tableB.price < 20
ORDER by tableB.price DESC

"kjc" <ksitron@.elp.rr.com> wrote in message
news:OnIXc.52730$xi6.21027@.fe2.texas.rr.com...
> Not sure if this is the right group to post this to but.
> This is the current query that I have.
> select tableA.id,tableB.artist,tableB.image,from tableA,tableB where
> tableA.image = tableB.image AND tableB.price >0 AND tableB.price < 20
> order by tableB.price DESC'
> What I need is, for each row returned I need information from a third
> and fourth table. tableC, and tableD.
>
> tableC has information ( the tableA.id = tableC.eventId) that I need to
> obtain tableC.accountId = tableD.accountId in order do select the
> the binding information in tableD between a Vendor(name,address..etc..)
> and tableB.image
> Any help would be greatly appreciated.|||[Top posting is annoying and confusing. Rearranging ...]

"Big Time" <big-time-grizz@.remove-for-spam-hotmail.com> wrote in
news:cgo06l$10ga$1@.lettuce.bcit.ca:

> "kjc" <ksitron@.elp.rr.com> wrote in message
> news:OnIXc.52730$xi6.21027@.fe2.texas.rr.com...
>> Not sure if this is the right group to post this to but.
>>
>> This is the current query that I have.
>>
>> select tableA.id,tableB.artist,tableB.image,from tableA,tableB where
>> tableA.image = tableB.image AND tableB.price >0 AND tableB.price < 20
>> order by tableB.price DESC'
>>
>> What I need is, for each row returned I need information from a third
>> and fourth table. tableC, and tableD.
>>
>>
>> tableC has information ( the tableA.id = tableC.eventId) that I need
>> to obtain tableC.accountId = tableD.accountId in order do select the
>> the binding information in tableD between a
>> Vendor(name,address..etc..) and tableB.image
>>
>> Any help would be greatly appreciated.
>
> If I'm reading you correctly, you should be able to do this pretty
> easily. In the SELECT statement, list all the fields from each table
> that you want to see. In the FROM statement list the tables. In the
> WHERE statement list all the parameters and relationships. So:
> SELECT
> tableA.fields
> tableB.fields
> tableC.fields
> tableD.fields
> FROM
> TableA
> TableB
> TableC
> TableD
> WHERE
> tableA.image = tableB.image AND
> tablea.id = tableC.eventID AND
> talbeC.accountID = tableD.accountID AND
> tableB.price >0 AND
> tableB.price < 20
> ORDER by tableB.price DESC

This will indeed work fine, and the optimizer should have no problem
(given sufficient foreign key constraints and indexing) rewriting it to
run with maximum efficiency. However, future programmers may thank you
if you separate the join relationships from the filtering clauses:

SELECT
tableA.fields,
tableB.fields,
tableC.fields,
tableD.fields
FROM
tableA
INNER JOIN tableB on tableA.image = tableB.image
INNER JOIN tableC on tableA.id = tableC.eventID
INNER JOIN tableD on tableC.accountID = tableD.accountID
WHERE
tableB.price > 0 AND tableB.price < 20