Auto-Refresh Report with new parameter each time

Question

Auto-Refresh Report with new parameter each time

ms-techie

SSChasing Mays

Points: 627
More actions
February 22, 2014 at 9:26 pm

#304622

Hi,
I have a requirement , wherein , the report shows different values , based upon the parameters selected . Now there are 6 parameters.
Is it possible to refresh the report ,periodically , so that is automatically takes the parameters one by one and displays the corresponding data on the Report for 10 seconds , for each parameter . I am usin VS 2008 (SQL 2008 R2)
This is basically going to be like a moving display . How can we acheive this
Please help
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47068 More actions · Answer 1

Not entirely clear what you want, but it sounds like you want to get the stored procedure you are calling to handle this rather than using parameters in the report...

e.g.

create proc GetMyDataController

as

declare @RunNumber int;

-- get and update the run number - which is just an incrementing value

update RuntimeStore

set @RunNumber = 1 + ( RunNumber % 6 )

, RunNumber = RunNumber + 1

where ReportName = 'MyReport';

-- get your data based on your RunNumber

exec GetMyData @RunNumber;

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

ms-techie SSChasing Mays Points: 627 More actions · Answer 2

Thanks for the procedure.

My basic need is that the Report , has to automatically get updated with different rows every 10 seconds.

Say suppose , I have set my Report to Auto-Refresh option every 10 seconds. In that case, will this stored procedure return a value exactly incremented by 1 each time , the auto-refresh happens . That is ...

1st time , this procedure is invoked from the Report via Auto-Refresh it should return 1

2nd time the same procedure is invoked from the Report via Auto-Refresh, it should return 2

3rd time the same procedure is invoked from the Report via Auto-Refresh, it should return 3

4th time the same procedure is invoked from the Report via Auto-Refresh, it should return 4

Finally the 5th time ,the same procedure is invoked from the Report via Auto-Refresh , it should return 1

So across each postback from the Report , is there a mechanism which will make the SQL Server remember the value it returned from the stored procedure , the previous time , so that it can get incremented by 1 , on every new call.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 3

That example uses a table called runtimestore to keep track of which number/variant of data you require. You could use any table on the server for this.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

ms-techie SSChasing Mays Points: 627 More actions · Answer 4

Thanks,

I wrote a stored procedure which always returns next row from the table and the proc does not take any parameters. So I think, if I call the stored procedure during Auto-Refresh, it should return different vales each time. I will give this a try.