historical lookup query

I'm having a dickins of a time with a particular query and am hoping
someone here can help me.
Using the following example;
declare @.SearchDate datetime
set @.SearchDate = '30 Nov 2005'
declare @.t1 table (t1id int, t1desc varchar(10))
insert into @.t1 (t1id, t1desc) values (1, 'Ed')
insert into @.t1 (t1id, t1desc) values (2, 'Bill')
insert into @.t1 (t1id, t1desc) values (3, 'Bob')
insert into @.t1 (t1id, t1desc) values (4, 'Fred')
insert into @.t1 (t1id, t1desc) values (5, 'John')
declare @.t1history table (t1id int, t1desc varchar(10), created
datetime)
insert into @.t1history (t1id, t1desc, created) values (1, 'James', '01
Jan 2005')
insert into @.t1history (t1id, t1desc, created) values (1, 'Frank', '05
Jan 2005')
insert into @.t1history (t1id, t1desc, created) values (1, 'Henry', '10
May 2005')
insert into @.t1history (t1id, t1desc, created) values (1, 'Joe', '28
Nov 2005')
insert into @.t1history (t1id, t1desc, created) values (4, 'Toby', '21
Oct 2005')
insert into @.t1history (t1id, t1desc, created) values (4, 'Brian', '25
Oct 2005')
insert into @.t1history (t1id, t1desc, created) values (4, 'Horace', '28
Nov 2005')
insert into @.t1history (t1id, t1desc, created) values (5, 'Ben', '21
Oct 2005')
declare @.lookup table (val varchar(10))
insert into @.lookup (val) values ('Ben')
insert into @.lookup (val) values ('Frank')
insert into @.lookup (val) values ('Bill')
--example query
select *,
(select top 1 t1desc from @.t1history as Table1History where
Table1History.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
created desc) as t1deschistory
from @.t1 as Table1
I want to filter the results returned from @.t1 against those contained
in @.lookup
I also need to be able to filter the results based on what the value
for t1desc could have been in the past using @.t1history and @.SearchDate
For example, with the date of '30 Nov 2005' I would expect the
following;
t1id t1desc t1deschistory
----
2 'Bill' null
5 'John' 'Ben'
Changing the date to '15 Oct 2005' I would expect;
t1id t1desc t1deschistory
----
2 'Bill' null
and changing it again to '15 Jan 2005' I would expect;
t1id t1desc t1deschistory
----
1 'Ed' 'Frank'
2 'Bill' null
What I basically want to do is this;
select *,
(select top 1 t1desc from @.t1history as Table1History where
Table1History.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
created desc) as t1deschistory
from @.t1 as Table1
where t1desc in (select val from @.lookup) or t1deschistory in (select
val from @.lookup)
This gives the following error as expected;
Server: Msg 207, Level 16, State 3, Line 28
Invalid column name 't1deschistory'.
Moving the sub-query into a join doesn't work either;
select *
from @.t1 as Table1
left join (select top 1 * from @.t1history as t1history where
t1history.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
created desc) Table1History on Table1.t1id = Table1History.t1Id
where Table1.t1desc in (select val from @.lookup) or
Table1History.t1desc in (select val from @.lookup)
This gives the following error;
Server: Msg 107, Level 16, State 2, Line 28
The column prefix 'Table1' does not match with a table name or alias
name used in the query.
Can anyone help?
Many thanks in advance,
Edone way: make it a derived table before applying the where e.g.
select * from (
select *,
(select top 1 t1desc
from @.t1history as Table1History
where Table1History.t1id = Table1.t1Id
and created <= @.SearchDate +1
order by created desc) as t1deschistory
from @.t1 as Table1
) x
where t1desc in (select val from @.lookup) or t1deschistory in (select
val from @.lookup)
ThievingScouser wrote:
> I'm having a dickins of a time with a particular query and am hoping
> someone here can help me.
> Using the following example;
> declare @.SearchDate datetime
> set @.SearchDate = '30 Nov 2005'
> declare @.t1 table (t1id int, t1desc varchar(10))
> insert into @.t1 (t1id, t1desc) values (1, 'Ed')
> insert into @.t1 (t1id, t1desc) values (2, 'Bill')
> insert into @.t1 (t1id, t1desc) values (3, 'Bob')
> insert into @.t1 (t1id, t1desc) values (4, 'Fred')
> insert into @.t1 (t1id, t1desc) values (5, 'John')
> declare @.t1history table (t1id int, t1desc varchar(10), created
> datetime)
> insert into @.t1history (t1id, t1desc, created) values (1, 'James', '01
> Jan 2005')
> insert into @.t1history (t1id, t1desc, created) values (1, 'Frank', '05
> Jan 2005')
> insert into @.t1history (t1id, t1desc, created) values (1, 'Henry', '10
> May 2005')
> insert into @.t1history (t1id, t1desc, created) values (1, 'Joe', '28
> Nov 2005')
> insert into @.t1history (t1id, t1desc, created) values (4, 'Toby', '21
> Oct 2005')
> insert into @.t1history (t1id, t1desc, created) values (4, 'Brian', '25
> Oct 2005')
> insert into @.t1history (t1id, t1desc, created) values (4, 'Horace', '28
> Nov 2005')
> insert into @.t1history (t1id, t1desc, created) values (5, 'Ben', '21
> Oct 2005')
> declare @.lookup table (val varchar(10))
> insert into @.lookup (val) values ('Ben')
> insert into @.lookup (val) values ('Frank')
> insert into @.lookup (val) values ('Bill')
> --example query
> select *,
> (select top 1 t1desc from @.t1history as Table1History where
> Table1History.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
> created desc) as t1deschistory
> from @.t1 as Table1
>
> I want to filter the results returned from @.t1 against those contained
> in @.lookup
> I also need to be able to filter the results based on what the value
> for t1desc could have been in the past using @.t1history and @.SearchDate
> For example, with the date of '30 Nov 2005' I would expect the
> following;
> t1id t1desc t1deschistory
> ----
> 2 'Bill' null
> 5 'John' 'Ben'
> Changing the date to '15 Oct 2005' I would expect;
> t1id t1desc t1deschistory
> ----
> 2 'Bill' null
> and changing it again to '15 Jan 2005' I would expect;
> t1id t1desc t1deschistory
> ----
> 1 'Ed' 'Frank'
> 2 'Bill' null
>
> What I basically want to do is this;
> select *,
> (select top 1 t1desc from @.t1history as Table1History where
> Table1History.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
> created desc) as t1deschistory
> from @.t1 as Table1
> where t1desc in (select val from @.lookup) or t1deschistory in (select
> val from @.lookup)
> This gives the following error as expected;
> Server: Msg 207, Level 16, State 3, Line 28
> Invalid column name 't1deschistory'.
> Moving the sub-query into a join doesn't work either;
> select *
> from @.t1 as Table1
> left join (select top 1 * from @.t1history as t1history where
> t1history.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
> created desc) Table1History on Table1.t1id = Table1History.t1Id
> where Table1.t1desc in (select val from @.lookup) or
> Table1History.t1desc in (select val from @.lookup)
> This gives the following error;
> Server: Msg 107, Level 16, State 2, Line 28
> The column prefix 'Table1' does not match with a table name or alias
> name used in the query.
>
> Can anyone help?
> Many thanks in advance,
> Ed
>

Hiding parts of the report

Hello All,
I have to generate a report based on parameters. And each part of the
report depends based a particular set of parameters.
Is there a way to hide the parts of the report that i dont need to show
?
Thankx
BofoDid you look into the Visibility.Hidden property of reportitems? You can use
expressions based on parameter values to statically or dynamically hide
certain areas of a report.
More information can be found here:
*
http://msdn.microsoft.com/library/en-us/RShowto/htm/hrs_designer_v1_3kq5.asp
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_dc_v1_0rc7.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Win2003InstallIssues" <bofobofo@.yahoo.com> wrote in message
news:1107047048.476551.144590@.c13g2000cwb.googlegroups.com...
> Hello All,
> I have to generate a report based on parameters. And each part of the
> report depends based a particular set of parameters.
> Is there a way to hide the parts of the report that i dont need to show
> ?
>
> Thankx
> Bofo
>|||Hello Robert,
I am partially successful in using the Hiding/hidden properties.
Here is the issue.
I have 7 diff parameters to my report, 2 main groups say A and B to gen
Areport and Breport
if the user enters any of the fields of Group A, the ReportB should not
appear and vice-versa.
Now I tried using the parameter.value of the Group B to hide Breport.
But to no avail. I can still see the Textbox label.
I can hide the table cos I created dummy fields in Select and use them
as the Labels for the table columns.
But what I need is something that will hide the entire part of the
report.
Any Ideas on how to go about it ?
Thx|||Finally got it.
This is the expr.
=iif((Parameters!param1.Value + Parameters!param2.Value +
Parameters!param3.Value +
Parameters!4.Value)="", TRUE, FALSE)
Rgds
Bofo

Hiding elements for print and export

Is it possible to suppress the output of certain report elements when the report is printed or exported? I want a particular image element that has a hyperlink to be visible when the report is viewed within RS's ReportViewer, but don't want it to show up on the printed or exported file. I figure, if this is even possible, the most obvious methods would be either:

1. Put the element in the page header or footer, then set up the page header or footer to not show up on the printed or exported report.

2. Put the element anywhere on the report, and set it to hidden when the report is printed or exported.

Are either of these possible? If not, is there another way to accomplish this?

Thanks,
DonThere is no way to do this. You can however write your own rendering extension in which you have full control over how to render the report.