Jump to Report Issue

I have a "Jump to Report" issue. I have created a report that runs itself
with new parameters when a cell is clicked on. It works great except that
when it is run on the web, the "Jump to Report" has no access to the
parameters. I need to be able to have the parameters visible so the users
can change out their values at will.
I can not find any settings to turn on or off the parameters in the Jump-to
screens.You have to use Jump to URL. Here is an example of an expression that I use
for this. Note the global variable that puts in the server name of where the
report is running from.
=Globals!ReportServerUrl & "?/Inventory/Similar Loads&Manifest=" &
First(Fields!manifstdocno.Value, "LoadID") &"&WasteIDNum=" &
First(Fields!wasteidnum.Value, "LoadID")
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message
news:F9EDC25A-F1E3-48A5-9359-D6C4DEF27D4F@.microsoft.com...
> I have a "Jump to Report" issue. I have created a report that runs itself
> with new parameters when a cell is clicked on. It works great except that
> when it is run on the web, the "Jump to Report" has no access to the
> parameters. I need to be able to have the parameters visible so the users
> can change out their values at will.
> I can not find any settings to turn on or off the parameters in the
Jump-to
> screens.

Joining table to itself

In myTable I have
RequiredAmount
RequiredDate
GrantedAmount
GrantedDate
I wish to obtain one view showing the sum per year.
For the required part it would be:
SELECT YEAR(RequiredDate) AS myYear, SUM(RequiredAmount) AS reqAmount
FROM myTable
GROUP BY YEAR(RequiredDate)
And for the Granted part:
SELECT YEAR(GrantedDate) AS myYear, SUM(GrantedAmount) AS grantAmount
FROM myTable
GROUP BY YEAR(GrantedDate)
Now, what I need is a presentation with three columns:
myYear, reqAmount, grantAmount
I'm fooling around with the table joined to itself, but can't seem to
get it right...maybe wrong approach?
Regards /SnedkerRead about Cross-tab reports in sql server help file
Madhivanan
Morten Snedker wrote:
> In myTable I have
> RequiredAmount
> RequiredDate
> GrantedAmount
> GrantedDate
> I wish to obtain one view showing the sum per year.
> For the required part it would be:
> SELECT YEAR(RequiredDate) AS myYear, SUM(RequiredAmount) AS reqAmount
> FROM myTable
> GROUP BY YEAR(RequiredDate)
>
> And for the Granted part:
> SELECT YEAR(GrantedDate) AS myYear, SUM(GrantedAmount) AS grantAmount
> FROM myTable
> GROUP BY YEAR(GrantedDate)
>
> Now, what I need is a presentation with three columns:
> myYear, reqAmount, grantAmount
> I'm fooling around with the table joined to itself, but can't seem to
> get it right...maybe wrong approach?
>
> Regards /Snedker|||use a full outer join (self)..
something like this..
untested..
SELECT COALESCE(A.myYear,B.myYear), COALESCE(A.reqAmount,0),
COALESCE(B.grantAmount,0)
FROM
(SELECT YEAR(RequiredDate) AS myYear, SUM(RequiredAmount) AS reqAmount
FROM myTable
GROUP BY YEAR(RequiredDate)
) A FULL OUTER JOIN
(SELECT YEAR(GrantedDate) AS myYear, SUM(GrantedAmount) AS grantAmount
FROM myTable
GROUP BY YEAR(GrantedDate)) B
ON A.myYear= B.myYear
Hope this helps.
-Omni|||Can you see if this works for you
SELECT
YEAR(tDate) as TransYear,
SUM(CASE WHEN tType = 'R' then tAmt else 0 end) as SumReq,
SUM(CASE WHEN tType = 'G' then tAmt else 0 end) as GraReq
FROM
(
select 'R' as tType,reqAmount as tAmt,ReqDate as tDate FROM Mytable
UNION ALL
select 'G' as tType,GrantAmount as tAmt,GrantDate as tDate FROM Mytable
) x
GROUP BY YEAR(tDate)
- Sha Anand
"Morten Snedker" wrote:

> In myTable I have
> RequiredAmount
> RequiredDate
> GrantedAmount
> GrantedDate
> I wish to obtain one view showing the sum per year.
> For the required part it would be:
> SELECT YEAR(RequiredDate) AS myYear, SUM(RequiredAmount) AS reqAmount
> FROM myTable
> GROUP BY YEAR(RequiredDate)
>
> And for the Granted part:
> SELECT YEAR(GrantedDate) AS myYear, SUM(GrantedAmount) AS grantAmount
> FROM myTable
> GROUP BY YEAR(GrantedDate)
>
> Now, what I need is a presentation with three columns:
> myYear, reqAmount, grantAmount
> I'm fooling around with the table joined to itself, but can't seem to
> get it right...maybe wrong approach?
>
> Regards /Snedker
>|||Hi,
Nice solution.. But Why do you need a case? Can't it be something simple
like this.
select TransYear, sum(GrantAmount) SumGrant, sum(reqAmount) SumReq
from
(select 0 as GrantAmount,reqAmount ,year(ReqDate) as TransYear FROM Mytable
UNION ALL
select GrantAmount,0 as reqAmount, year(GrantDate) as TransYear FROM
Mytable) x
group by TransYear
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||If your a consultant you know your clients couldn't give
a whit about how you provide a solution just as long as you
give them one.Perhaps we can help.Check out RAC for easy
solutions to all kinds of data manipulation problems including
crosstabs on sql server.
www.rac4sql.net

joining one table with itself

Hello all

Let's say I have 1 table "contract" containing the following data:

id year sales
45 2005 100
45 2004 95
89 2005 250
89 2004 275
12 2005 42

I want to make a table with one unique row for each id and then a column for
2004 sales and 2005 sales, like this:

select a.id, a.sales, b.sales
from contract a, contract b
where a.contract=b.contract(+)
and a.year=2005
and b.year=2004

The rows for id 45 and 89 are shown perfectly. But id 12 is not shown at all
because it doesn't have a record for 2004!! I don't know why 'cause I
outerjoined the tables.

It works perfectly when I have two distinct tables for each year (for
instance contract_2005 and contract_2004). So the problem seems to be in the
fact I like to join one table with itself.

Someone has a solution for this?

thanks!

MaartenThe problem is a logical one; by specifing that you want rows returned
from your result set where a.year =2005 and b.year=2004, you've limited
your return to rows that match BOTH criteria. Essentially, you've
eliminated the NULLS from your outer join.

To get around this, you need to use subqueries; I would also move to a
newer JOIN syntax (it's easier to read):

SELECT a.id, a.sales, b.sales
FROM (SELECT id, sales
FROM contract
WHERE year = 2005) a
LEFT JOIN (SELECT id, sales
FROM contract
WHERE year = 2004) b
ON a.id=b.id

Untested.

HTH,
Stu|||Hi Stu

It works, thanks a lot!

regards,
Maarten

Joining an aggregate of a table to itself

I am trying to figure out how to do a simple aggregate of a table and then join it to itself to get the remaining fields. I have a few tables that list information on daily entries that includes job_id, location_id, desc, date, beginDate, endDate, manager. I want to list every entry in the table, but for each job_id and location_id combination, I'd like to get the very first entry for beginDate and the very latest entry for endDate for the group and then list all the remaining information within each record.

My first attempt to do this is:

Select M.job_id, M.location_id, M.desc, M.date, Y.beginDate, Y.endDate, M.manager
from myTable M
inner join

(select job_id, location_id, min(beginDate), max(endDate)

from myTable

group by job_id, location_id) as Y

on M.job_id = Y.job_id, M.location_id = Y.location_id

This seems inefficient and I'm not sure it's the best way to get what I want. Any suggestions?

Thanks!!!

I'm not completely sure what to aim at; it will help if you will give some sample data and the desired output. You might be looking for something like one of these:

declare @.myTable table
( rid integer,
job_id integer,
location_id integer,
[desc] varchar(10),
date datetime,
manager varchar(10)
)

insert into @.myTable
select 1, 1, 1, 'First Job', '4/20/7', 'Flintstone' union all
select 2, 1, 1, 'First Job', '4/21/7', 'Slate' union all
select 3, 1, 1, 'First Job', '4/22/7', 'Flintstone' union all
select 4, 2, 2, 'Future Job', '4/20/7', 'Jetson' union all
select 5, 2, 2, 'Future Job', '4/21/7', 'Spacely' union all
select 6, 2, 2, 'Future Job', '4/22/7', 'Spacely'

Select M.job_id,
M.location_id,
M.[desc],
M.date,
Y.beginDate,
Y.endDate,
M.manager
from @.myTable M
inner join
( select job_id,
location_id,
min(Date) as beginDate,
max(Date) as endDate
from @.myTable
group by job_id, location_id
) Y
on M.job_id = Y.job_id
and M.location_id = Y.location_id

/*
job_id location_id desc date beginDate endDate manager
- -- - -
1 1 First Job 2007-04-20 00:00:00.000 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Flintstone
1 1 First Job 2007-04-21 00:00:00.000 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Slate
1 1 First Job 2007-04-22 00:00:00.000 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Flintstone
2 2 Future Job 2007-04-20 00:00:00.000 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Jetson
2 2 Future Job 2007-04-21 00:00:00.000 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Spacely
2 2 Future Job 2007-04-22 00:00:00.000 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Spacely
*/

declare @.myTable2 table
( rid integer,
job_id integer,
location_id integer,
[desc] varchar(10),
date datetime,
manager varchar(10)
)

insert into @.myTable2
select 1, 1, 1, 'First Job', '4/20/7', 'Slate' union all
select 2, 1, 1, 'First Job', '4/21/7', 'Slate' union all
select 3, 1, 1, 'First Job', '4/22/7', 'Slate' union all
select 4, 2, 2, 'Future Job', '4/20/7', 'Spacely' union all
select 5, 2, 2, 'Future Job', '4/21/7', 'Spacely' union all
select 6, 2, 2, 'Future Job', '4/22/7', 'Spacely'
select job_id,
location_id,
[desc],
min(date) as beginDate,
max(date) as endDate,
Manager
from @.myTable2
group by job_id,
location_id,
[desc],
Manager
order by job_id,
location_id

/*
job_id location_id desc beginDate endDate manager
- -- - -
1 1 First Job 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Slate
2 2 Future Job 2007-04-20 00:00:00.000 2007-04-22 00:00:00.000 Spacely
*/

Join tables help

Could somebody please explain to me how do we join a table onto itself as that is what I was advised to do but I can't quite get where I want to go.

What I want to do is list values from a table,but those values can be just a quote (what would cost if they decided to go for that option) or those values can represent what was spent and invoiced, what is confusing me is that all of that gets saved in the same table and in same columns, so what was quoted for example for AirFares and what was spent gets saved in the same record but when it is "quoted amount" then ID = 1 but when it is invoiced ID = -1 and that is how we know what was quoted and what was invoiced.

But I need to split that one field into two columns one showing AirFareQuoted and one AirFareInvoiced and i have no idea how to achieve this.

I hope this makes sense and somebody can help meHi, can you post some sample data with expected outcome?

Madhivanan|||select t1.option
, t1.AirFares as AirFareQuoted
, t2.AirFares as AirFareInvoiced
from yourtable as t1
left outer
join yourtable as t2
on t1.option = t2.option
and t2.ID = -1
where t1.ID = 1|||I think he'd want a FULL OUTER JOIN on this in case data is not present for one of the two options:

select coalesce(t1.option, t2.option) as option,
t1.AirFares as AirFareQuoted
t2.AirFares as AirFareInvoiced
from yourtable as t1
full outer join yourtable as t2
on t1.option = t2.option
where nullif(t1.ID, 1) = 1
and nullif(t2.ID, -1) = -1|||blindman, he didn't say so, but my business logic is that you can't create an invoice unless there was a quote, but you can have a quote without it leading to an invoice|||Don't talk to me about LOGIC, man! We're dealing with BUSINESS!

You ivory tower Canadians with your high-falutin "educations" and your "logic" are just out of touch with the way things are done down here in the Red States! We don't got no truck with that elitist stuff no more!|||blindman, he didn't say so, but my business logic is that you can't create an invoice unless there was a quote, but you can have a quote without it leading to an invoiceA retainer fee is a common occurance where there is an invoice without a quote or even any service/time/labor.

-PatP|||Don't talk to me about LOGIC, man! We're dealing with BUSINESS!

You ivory tower Canadians with your high-falutin "educations" and your "logic" are just out of touch with the way things are done down here in the Red States! We don't got no truck with that elitist stuff no more!You are starting to frighten me... Have you thought about a career in politics?

-PatP|||A retainer fee is a common occurance where there is an invoice without a quote or even any service/time/labor.whare kin ah git me one a them?

that sounds lahk jest the ticket fer my consultin bidness

i wanna be retained!!|||I got me one o' them retainers fer my overbite back when'st I was in high-school. Derned uncomfertable.|||You guys have just about lost your collective minds today...

Did someone spike the watercooler?

Post the DDL for the table...sounds like a normalization problem to me...|||Thanks guys for your replys, I will use this and see what I get.

I was going to post the view I am trying to create but there is too much to explain.|||...but there is too much to explain.i know that feeling|||I just love Lindman's obsession with coalesce, especially when there is no 3rd value...I guess trying to be cute is a very strong feeling...Trying to compensate for something? :D|||I just love Lindman's obsession with coalesce, especially when there is no 3rd value...I guess trying to be cute is a very strong feeling...Trying to compensate for something? :DYeah, but I'm rather fond of standard, portable ways like Coalesce() to do things too... I hate having to rewrite code every time I switch database engines again.

-PatP|||exactamundo

i shudder every time i see someone use NVL and ISNULL

it's not an obsession, rdjabarov, it's just smart coding

why, what do you use instead of coalesce?

:)|||I don't get all lit up about IsNull, Nvl, and other vendor specific extensions to SQL. Often those are the things that force the standards committee to act on a much needed construct for the standard instead of debating it until half past doomsday. If a vendor specific extension is the only practical way from where I'm at to where I want to be, I'll gladly use the extension, but if there's a standard way to get there, I'll prefer it almost every time!

-PatP|||Well, maybe it's because COALESCE is really misleading

BOL:

COALESCE
Returns the first nonnull expression among its arguments.

The actual Def

Definition: [v] mix together different elements; "The colors blend well"; "fuse the clutter of detail into a rich narrative"--A. Schlesinger
[v] fuse or cause to grow together

In DB2 it's COALESCE and VALUE(Col1,'Argument')

Oracle is NVL as well...

OK...I take it back...NVL, VALUE, ISNULL are totally separate functions..COALESCE Can accept MANY Values and the first one that is NOT NULL, or the absence of anything, wins.

So they do seem to be different...

babble..babble..bable...

lack of sleep..sorry|||It's true, I am obsessed with COALESCE. I dream about it every night, and it coalesce(haunts, infests) my every waking thought. I feel the coalesce(compulsion, desire, need) to use it coalesce(constantly, continually, incessantly).

WHY COALESCE()?!!! WHY! WHY!!!

Somebody please coalesce(help, aid, assist) me!

You crack me up rdjabarov. But happy nullif(christmas, hannukah) to you anyway. :)|||happy nullif(christmas, hannukah) to you anywaymy nomination for quote of the year

nice one, blindman

:)|||My apologies to all the Kwanzaa and Festivus celebrators out there, but NULLIF could only take two values... :)|||actually, you should really leave NULL as the last option, for those of us without any religious or societal holiday adherence|||Unnecessary. If both parameters are null, NULLIF() returns null...|||... what is confusing me is that all of that gets saved in the same table and in same columns, so what was quoted for example for AirFares and what was spent gets saved in the same record but when it is "quoted amount" then ID = 1 but when it is invoiced ID = -1 and that is how we know what was quoted and what was invoiced.
I hope this makes sense and somebody can help me

Maybe its much less confusing to you if you decide not to work with the table itself, but with two views separating your quotes and spents?! In practise:

CREATE VIEW Quotes AS SELECT * FROM <YourTable> WHERE ID = 1;
CREATE VIEW Spents AS SELECT * FROM <YourTable> WHERE ID = -1;

If there is a way to relate quotes to spends, you can join these two views using this relation in any (INNER, OUTER, FULL OUTER) way you want.

I hope this helps.