|
Восстановление в SSRS 2008 Значений пар-ров выбранных при предыдущем запуске (СДЕЛАНО!)
#38042547
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
В MSRS 2005 решал подобную задачу с помощью следующей ХП
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
create proc [dbo].[spGetLastReportingParameters](@reportname nvarchar(4000),@username nvarchar(4000))
as
set nocount on
declare @lastexecution datetime
declare @reportid uniqueidentifier
declare @params varchar(8000)
declare @xml xml
select top 1 @reportid=itemid,@xml=parameter from [rpt_catalog] where [name]=@reportname
select @lastexecution=max(timestart) from rpt_executionlog where reportid=@reportid and username=@username and parameters is not null and datalength(parameters)<8000 --order by timestart desc
set @params=((select top 1 parameters from rpt_executionlog where reportid=@reportid and username=@username and timestart=@lastexecution and parameters is not null and datalength(parameters)<8000) )
if object_id('tempdb..#t1') is not null drop table #t1
select left(id,patindex('%=%',id)-1) as paramname,right(id,len(id)-patindex('%=%',id)) as paramvalue into #t1 from dbo.getvarcharTableFromList(@params,'&')
if object_id('tempdb..#t2') is not null drop table #t2
select * into #t2 from (
select
t1.*
from
#t1 t1
inner join (select paramname from #t1 group by paramname having count(*)=1) t2 on t1.paramname=t2.paramname
union all
select
#t1.paramname,
(select paramvalue + ',' as 'data()' from #t1 t1 where t1.paramname=#t1.paramname for xml path(''))
from
#t1
inner join (select paramname from #t1 group by paramname having count(*)>1) t2 on #t1.paramname=t2.paramname
group by #t1.paramname) t
update #t2
set paramvalue=null
where patindex('%:isnull',paramname)<>0
update #t2
set paramname=left(paramname,patindex('%:isnull',paramname)-1)
where patindex('%:isnull',paramname)<>0
if object_id('tempdb..#t3') is not null drop table #t3
select
t.rows.value('Name[1]','varchar(800)') as paramname,
t.rows.value('Type[1]','varchar(800)') as paramtype
into #t3
from
@xml.nodes('/Parameters/Parameter') as t(rows)
declare @paramname varchar(8000)
declare @paramvalue varchar(8000)
declare @paramtype varchar(8000)
declare @sql varchar(8000)
set @sql=''
declare c cursor for select t2.paramname,t2.paramvalue,t3.paramtype from #t2 t2 inner join #t3 t3 on t2.paramname=t3.paramname
open c fetch next from c into @paramname,@paramvalue,@paramtype
while @@fetch_status=0
begin
if len(@sql)>0
begin
set @sql=@sql + ','
end
else
begin
set @sql='select '
end
set @sql=
@sql
+ 'cast(' + case when @paramvalue is null then 'NULL' else ''''+ @paramvalue + '''' end
+ ' as '
+ case @paramtype
when 'String' then 'varchar(8000)'
when 'DateTime' then 'datetime'
when 'Integer' then 'int'
when 'Boolean' then 'bit'
when 'Float' then 'money'
end
+ ') as [' + @paramname + ']'
fetch next from c into @paramname,@paramvalue,@paramtype
end
close c
deallocate c
exec(@sql)
go
|
|
|
