i am asp.net developer. currently i am working on proficy historian which is very new to me. i need to develop ssrs report in which user will provide month as varchar(2) and year as varchar(4) after that when user click on view report button then ssrs report should display result as follows. (suppose if user pass month as 12 and year as 2013)
Date TagName Average
12/01/13 Tag01 45.23
12/01/13 Tag02 89.23
12/02/13 Tag01 2.363
12/02/13 Tag02 45.23
.
.
.
12/31/13 Tag01 55.24
12/31/13 Tag02 95.24
for that i designed a query
create table #tempval
(
[timestamp] varchar(30),
tagname varchar(300),
tagval decimal(18,5)
)
declare @month varchar(2)
declare @year varchar(4)
set @month='08'
set @year='2014'
declare @startdate varchar(30)
declare @enddate varchar(30)
set @startdate=@month+'/01/'+@year
if (convert(int,@month)>= MONTH(GETDATE()) and convert(int,@year)>=YEAR(GETDATE()))
begin
set @enddate=convert(varchar,(select GETDATE()))
end
else
begin
set @enddate=convert(varchar,DATEADD(day,-1,DATEADD(month,1,convert(datetime,@startdate))))
end
declare @query varchar(1000)
declare @starttime varchar(30)
declare @endtime varchar(30)
set @starttime= convert(varchar,datepart(month,dateadd(hour,22,dateadd(day,-1,convert(datetime,@startdate)))))+'/'+convert(varchar,datepart(DAY,dateadd(hour,22,dateadd(day,-1,convert(datetime,@startdate)))))+'/'+convert(varchar,datepart(YEAR,dateadd(hour,22,dateadd(day,-1,convert(datetime,@startdate)))))+' '+convert(varchar,datepart(HOUR,dateadd(hour,22,dateadd(day,-1,convert(datetime,@startdate)))))+':'+convert(varchar,datepart(MINUTE,dateadd(hour,22,dateadd(day,-1,convert(datetime,@startdate)))))+':'+convert(varchar,datepart(SECOND,dateadd(hour,22,dateadd(day,-1,convert(datetime,@startdate)))))
set @endtime=convert(varchar,datepart(month,dateadd(hour,22,convert(datetime,@startdate))))+'/'+convert(varchar,datepart(DAY,dateadd(hour,22,convert(datetime,@startdate))))+'/'+convert(varchar,datepart(YEAR,dateadd(hour,22,convert(datetime,@startdate))))+' '+convert(varchar,datepart(HOUR,dateadd(hour,22,convert(datetime,@startdate))))+':'+convert(varchar,datepart(MINUTE,dateadd(hour,22,convert(datetime,@startdate))))+':'+convert(varchar,datepart(SECOND,dateadd(hour,22,convert(datetime,@startdate))))
set @query='select * from openquery(muri,''set StartTime='''''+@starttime+''''',EndTime='''''+@endtime+''''' select * from ihrawdata where tagname=MURISERVER.MURI.DCS.ASSETS.87A.87A_FI_2101.DACA.PV and samplingmode=rawbytime'')'
exec (@query)
but it display blank columns plz help