Data Driven Subscription Help

Hello all,

I am trying to schedule an MDX report to run once a week and it only works if I pass one parameter per field.It would fail every time I try to pass multiple parameters.Do you know if there is way to pass multiple MDX parameters?

The parameter field is set as multi-value field.

This worked –

'[Dim Travel Product].[Dim Travel Product].&[67]'

This failed –

'[Dim Travel Product].[Dim Travel Product].&[67], [Dim Travel Product].[Dim Travel Product].&[70], [Dim Travel Product].[Dim Travel Product].&[69]'

This failed -

'[Dim Travel Product].[Dim Travel Product].&[67]&[69]&[70]'

Could you post the MDX statement into which the parameters are being passed?

Thanks,
Bryan

|||

The parameter is passed to @.TravelProduct:

SET [Dim Travel Product Set] AS StrToSet(@.TravelProduct)

MEMBER [Dim Travel Product].[Dim Travel Product].[Travel Product Subset] AS 'Aggregate([Dim Travel Product Set])'

Thanks in advance!

|||It also works if i were to run this manually and select multiple parameters. The problem is I don't know the proper syntax of what's being passed when there are multiple selections.

|||

Enclose the list of members inside { } when passing multiple members.

|||

None of the below syntax works:

'{[Dim Travel Product].[Dim Travel Product].&[4]&[5]&[7]}'

'{[Dim Travel Product].[Dim Travel Product].&[4], [Dim Travel Product].[Dim Travel Product].&[5], [Dim Travel Product].[Dim Travel Product].&[7]}'

This is the method that I am using to pass the parameter:

So this is still working:

SELECT CONVERT(char(10),DateAdd(dd, -1 - (DatePart(dw, getdate()) - 2), getdate()),101) as Sunday,
'[Dim Travel Product].[Dim Travel Product].&[4]' as TravProd,
'[Dim Car Vendor].[Car Vendor Desc].[All]' as Vendor

This does not work:

SELECT CONVERT(char(10),DateAdd(dd, -1 - (DatePart(dw, getdate()) - 2), getdate()),101) as Sunday,
'{[Dim Travel Product].[Dim Travel Product].&[4], [Dim Travel Product].[Dim Travel Product].&[5], [Dim Travel Product].[Dim Travel Product].&[7]}' as TravProd,
'[Dim Car Vendor].[Car Vendor Desc].[All]' as Vendor

This does not work:

SELECT CONVERT(char(10),DateAdd(dd, -1 - (DatePart(dw, getdate()) - 2), getdate()),101) as Sunday,
'{[Dim Travel Product].[Dim Travel Product].&[4]&[5]&[7]}' as TravProd,
'[Dim Car Vendor].[Car Vendor Desc].[All]' as Vendor

|||

Take a look at this. It works against Adventure Works:

Code Snippet

select

[Date].[Date].[July 1, 2003] on 0,

strtoset("{[Product].[Category].[Bikes],[Product].[Category].[Clothing]}") on 1

from [Adventure Works]

B.|||

What I am trying to do is pass the @.TravelProduct from a Data Driven Subscription in Reporting Services and schedule it to run once a week. The select statement that I built contains all my filters, but when I add more than one filter for a particular field, it errors.

Thanks!

|||

What my code illustrates is how the MDX must be constructed to support multiple values from a parameter. You need to use STRTOSET, you need to wrap that comma delimited list of set members in curly braces, and that set string needs to be wrapped in double-quotes. If you've got all that in place, you can use a parameter in SSRS supply the set of members. The trick is getting your MDX in order to be able to work with the value from the parameter.

B.

|||

Thanks! I'll try that later today and report back.

|||

I tried what Bryan did above but it is still not working. ;(

sql

data access from sql 2005

i am using asp.net with sql 2005.

here i am having lakhs of records .

when we retrive the records it became very slow. it takes long time to retrive the datas from sql.

i used index,stored procedure,but no use. Any one know how to access the datas in fast manner.

First question would be have you tried executing the stored procedure directly from SQL Query Analyser / Management Studio? This is to determine whether it is a database issue or application issue?

If this is a database issue then you will have to look into appropriate column indexing, minimising table joins, optimise the query execution plan

If it points to an application issue, then make sure you are disposing connection objects, use appropriate command objects like readers and adapters, only fetch data you need, consider caching if the data is static in nature

Data access by creating functions

a number of time I have come accross developers using functions like

GetProduct()
GetProductTitle()
GetProductCategory()

to get the value of the data instead of just using queries / stored procedures

I have not understood why

can you please point to some good forum messages / blogposts / articles on this ?

Whats your take on this ?Dealing with functions that give direct results is easier programmatically than dealing with OUTPUT parameters in stored procedures.|||The concept of user defined functions is a great way to centralize code and they can come in very handy.

But in my experience they have one major drawback: performance.

When a user defined function needs to be performed on over a thousend rows (arbitrary boundery) the performance seems to decrease dramaticly. My guess it is because SQL Server has to switch from set-processing to row-by-row prossing.

AmitGeorge: look in the BOL under CREATE FUNCTION. That doesn't say anything about performance though.

Lex|||The use of UDFs will not cause the engine to stop using set-based processing. It's more likely due to the fact that UDF's tend to complex application-specific algorithms, otherwise they would be included in the standard stock of functions, right? And of course, there is no guarantee that the code in the UDF is GOOD code. MS hasn't yet come up with an error message that goes "You want me to run that? You can't be serious. Click OK to continue, or click Jeez What Was I Thinking to cancel."|||Hi Blindman,

A few months back I had to tune a stp which did a select which returned about 50 columns and about 25000 rows. Of these 50 columns 10 called a udf which only did the following:
CREATE FUNCTION convdate (@.dtMyDate DATETIME)
RETURNS VARCHAR(8)
AS
BEGIN
RETURN CONVERT(VARCHAR(8), @.dtMyDate, 112)
END
After replacing the functions calls with the CONVERT-statement in the select the procedure was about 10 times as fast!

If SQL is still using set-based processing I can't explain why! Got any ideas?

Lex|||i was not talking about using UDFs

I am talking about functions in the client language like VB, PHP, Asp, Java etc|||After replacing the functions calls with the CONVERT-statement in the select the procedure was about 10 times as fast!

If SQL is still using set-based processing I can't explain why! Got any ideas?Sorry. I just don't get the same results. I used these objects:set nocount on
go

CREATE FUNCTION dbo.convdate (@.dtMyDate DATETIME)
RETURNS VARCHAR(8)
AS
BEGIN
RETURN CONVERT(VARCHAR(8), @.dtMyDate, 112)
END

GO

CREATE TABLE TestDates (DateID int NOT NULL IDENTITY (1, 1) PRIMARY KEY CLUSTERED, DateValue datetime NOT NULL)

Insert into TestDates (DateValue)
select dateadd(Hour, Ones + Tens + Hundreds + Thousands + TenThousands, '2000-01-01')
from (select 0 as Ones
Union select 1
Union select 2
Union select 3
Union select 4
Union select 5
Union select 6
Union select 7
Union select 8
Union select 9) Ones,
(select 0 as Tens
Union select 10
Union select 20
Union select 30
Union select 40
Union select 50
Union select 60
Union select 70
Union select 80
Union select 90) Tens,
(select 0 as Hundreds
Union select 100
Union select 200
Union select 300
Union select 400
Union select 500
Union select 600
Union select 700
Union select 800
Union select 900) Hundreds,
(select 0 as Thousands
Union select 1000
Union select 2000
Union select 3000
Union select 4000
Union select 5000
Union select 6000
Union select 7000
Union select 8000
Union select 9000) Thousands,
(select 0 as TenThousands
Union select 10000
Union select 20000
Union select 30000
Union select 40000
Union select 50000
Union select 60000
Union select 70000
Union select 80000
Union select 90000) TenThousands
order by Ones + Tens + Hundreds + Thousands + TenThousands

Running these statements, I get virtually no difference in execution times:declare @.StartTime datetime
set @.StartTime = getdate()

-- select CONVERT(VARCHAR(8), DateValue, 112)
-- from TestDates

select dbo.convdate(DateValue)
from TestDates

select datediff(ms, @.StartTime, GetDate())
Regardless, you can check the execution plans for each statement and see that they are identical.|||i was not talking about using UDFs

I am talking about functions in the client language like VB, PHP, Asp, Java etc

you got the wrong forum then.|||MS hasn't yet come up with an error message that goes "You want me to run that? You can't be serious. Click OK to continue, or click Jeez What Was I Thinking to cancel."

it appears 2005 does this by making sucky code suck more after the "upgrade".

Data access by creating functions

A number of time I have come accross developers using functions like

GetProduct()
GetProductTitle()
GetProductCategory()

to get the value of the data instead of just using queries / stored procedures

Not UDFs but functions in VB and in DLLs

I have not understood why

can you please point to some good forum messages / blogposts / articles on this ?

Whats your take on this ?The main reason for this is to separate your code into layers. When you create this function, then you could have different database implementations inside of the function, like data from SQL Server or Oracle, but to outside world, that uses this function, it is still the same value. This design simplifies switching between databases and does not affect the code that calls these functions

data "movement" the culprit?

sql2k sp3
I have a report query that sometimes runs in 3 seconds, other times I kill
it after 10 minutes. When its taking a long time, there is no no blocking, no
extremely high cpu/ memory usage, no huge amount of activity. Something I
just noticed though is that someone put the Clustered Index on a date filed
even though this is an OLTP DB. Is there any possibility that when its taking
a long time to return results, whats happening is that data was recently
inserted that would cause the data in the table to move because of where the
clustering is? That SQL is searching for a moving target based on the data
range of the SP, which is in the middle of being rearranged because of it's
clustering setup? If this were the case, would it cause actual blocking(dont
forget Im not seeing any.)? I know I'm grasping here, but dont know what else
could cause this.
TIA, ChrisR
It's unlikely that is the cause. A page split if it occurs should only take
ms not minutes. And you would see blocking. My guess is you have a bad query
plan and disk bottlenecks. But the only way to be sure is to monitor the
server from all aspects to see what is the bottleneck. What about wait's
during that time?
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
> sql2k sp3
> I have a report query that sometimes runs in 3 seconds, other times I kill
> it after 10 minutes. When its taking a long time, there is no no blocking,
> no
> extremely high cpu/ memory usage, no huge amount of activity. Something I
> just noticed though is that someone put the Clustered Index on a date
> filed
> even though this is an OLTP DB. Is there any possibility that when its
> taking
> a long time to return results, whats happening is that data was recently
> inserted that would cause the data in the table to move because of where
> the
> clustering is? That SQL is searching for a moving target based on the data
> range of the SP, which is in the middle of being rearranged because of
> it's
> clustering setup? If this were the case, would it cause actual
> blocking(dont
> forget Im not seeing any.)? I know I'm grasping here, but dont know what
> else
> could cause this.
> TIA, ChrisR
|||I havent looked, but will. Does this number represent how long it take SQL to
satisfy a query? Im doing my testing in Query Analyzer so I can already tell
that.
"Andrew J. Kelly" wrote:

> It's unlikely that is the cause. A page split if it occurs should only take
> ms not minutes. And you would see blocking. My guess is you have a bad query
> plan and disk bottlenecks. But the only way to be sure is to monitor the
> server from all aspects to see what is the bottleneck. What about wait's
> during that time?
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
>
>
|||Waits tell you allot about what sql server is waiting on most. See here for
more details:
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:90871384-25AD-485B-BB51-B623B8671647@.microsoft.com...[vbcol=seagreen]
>I havent looked, but will. Does this number represent how long it take SQL
>to
> satisfy a query? Im doing my testing in Query Analyzer so I can already
> tell
> that.
> "Andrew J. Kelly" wrote:

data "movement" the culprit?

sql2k sp3
I have a report query that sometimes runs in 3 seconds, other times I kill
it after 10 minutes. When its taking a long time, there is no no blocking, no
extremely high cpu/ memory usage, no huge amount of activity. Something I
just noticed though is that someone put the Clustered Index on a date filed
even though this is an OLTP DB. Is there any possibility that when its taking
a long time to return results, whats happening is that data was recently
inserted that would cause the data in the table to move because of where the
clustering is? That SQL is searching for a moving target based on the data
range of the SP, which is in the middle of being rearranged because of it's
clustering setup? If this were the case, would it cause actual blocking(dont
forget Im not seeing any.)? I know I'm grasping here, but dont know what else
could cause this.
TIA, ChrisRIt's unlikely that is the cause. A page split if it occurs should only take
ms not minutes. And you would see blocking. My guess is you have a bad query
plan and disk bottlenecks. But the only way to be sure is to monitor the
server from all aspects to see what is the bottleneck. What about wait's
during that time?
--
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
> sql2k sp3
> I have a report query that sometimes runs in 3 seconds, other times I kill
> it after 10 minutes. When its taking a long time, there is no no blocking,
> no
> extremely high cpu/ memory usage, no huge amount of activity. Something I
> just noticed though is that someone put the Clustered Index on a date
> filed
> even though this is an OLTP DB. Is there any possibility that when its
> taking
> a long time to return results, whats happening is that data was recently
> inserted that would cause the data in the table to move because of where
> the
> clustering is? That SQL is searching for a moving target based on the data
> range of the SP, which is in the middle of being rearranged because of
> it's
> clustering setup? If this were the case, would it cause actual
> blocking(dont
> forget Im not seeing any.)? I know I'm grasping here, but dont know what
> else
> could cause this.
> TIA, ChrisR|||I havent looked, but will. Does this number represent how long it take SQL to
satisfy a query? Im doing my testing in Query Analyzer so I can already tell
that.
"Andrew J. Kelly" wrote:
> It's unlikely that is the cause. A page split if it occurs should only take
> ms not minutes. And you would see blocking. My guess is you have a bad query
> plan and disk bottlenecks. But the only way to be sure is to monitor the
> server from all aspects to see what is the bottleneck. What about wait's
> during that time?
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
> > sql2k sp3
> >
> > I have a report query that sometimes runs in 3 seconds, other times I kill
> > it after 10 minutes. When its taking a long time, there is no no blocking,
> > no
> > extremely high cpu/ memory usage, no huge amount of activity. Something I
> > just noticed though is that someone put the Clustered Index on a date
> > filed
> > even though this is an OLTP DB. Is there any possibility that when its
> > taking
> > a long time to return results, whats happening is that data was recently
> > inserted that would cause the data in the table to move because of where
> > the
> > clustering is? That SQL is searching for a moving target based on the data
> > range of the SP, which is in the middle of being rearranged because of
> > it's
> > clustering setup? If this were the case, would it cause actual
> > blocking(dont
> > forget Im not seeing any.)? I know I'm grasping here, but dont know what
> > else
> > could cause this.
> >
> > TIA, ChrisR
>
>|||Waits tell you allot about what sql server is waiting on most. See here for
more details:
http://sqldev.net/misc/WaitTypes.htm Wait Types
--
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:90871384-25AD-485B-BB51-B623B8671647@.microsoft.com...
>I havent looked, but will. Does this number represent how long it take SQL
>to
> satisfy a query? Im doing my testing in Query Analyzer so I can already
> tell
> that.
> "Andrew J. Kelly" wrote:
>> It's unlikely that is the cause. A page split if it occurs should only
>> take
>> ms not minutes. And you would see blocking. My guess is you have a bad
>> query
>> plan and disk bottlenecks. But the only way to be sure is to monitor the
>> server from all aspects to see what is the bottleneck. What about wait's
>> during that time?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
>> > sql2k sp3
>> >
>> > I have a report query that sometimes runs in 3 seconds, other times I
>> > kill
>> > it after 10 minutes. When its taking a long time, there is no no
>> > blocking,
>> > no
>> > extremely high cpu/ memory usage, no huge amount of activity. Something
>> > I
>> > just noticed though is that someone put the Clustered Index on a date
>> > filed
>> > even though this is an OLTP DB. Is there any possibility that when its
>> > taking
>> > a long time to return results, whats happening is that data was
>> > recently
>> > inserted that would cause the data in the table to move because of
>> > where
>> > the
>> > clustering is? That SQL is searching for a moving target based on the
>> > data
>> > range of the SP, which is in the middle of being rearranged because of
>> > it's
>> > clustering setup? If this were the case, would it cause actual
>> > blocking(dont
>> > forget Im not seeing any.)? I know I'm grasping here, but dont know
>> > what
>> > else
>> > could cause this.
>> >
>> > TIA, ChrisR
>>

data "movement" the culprit?

sql2k sp3
I have a report query that sometimes runs in 3 seconds, other times I kill
it after 10 minutes. When its taking a long time, there is no no blocking, n
o
extremely high cpu/ memory usage, no huge amount of activity. Something I
just noticed though is that someone put the Clustered Index on a date filed
even though this is an OLTP DB. Is there any possibility that when its takin
g
a long time to return results, whats happening is that data was recently
inserted that would cause the data in the table to move because of where the
clustering is? That SQL is searching for a moving target based on the data
range of the SP, which is in the middle of being rearranged because of it's
clustering setup? If this were the case, would it cause actual blocking(dont
forget Im not seeing any.)? I know I'm grasping here, but dont know what els
e
could cause this.
TIA, ChrisRIt's unlikely that is the cause. A page split if it occurs should only take
ms not minutes. And you would see blocking. My guess is you have a bad query
plan and disk bottlenecks. But the only way to be sure is to monitor the
server from all aspects to see what is the bottleneck. What about wait's
during that time?
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
> sql2k sp3
> I have a report query that sometimes runs in 3 seconds, other times I kill
> it after 10 minutes. When its taking a long time, there is no no blocking,
> no
> extremely high cpu/ memory usage, no huge amount of activity. Something I
> just noticed though is that someone put the Clustered Index on a date
> filed
> even though this is an OLTP DB. Is there any possibility that when its
> taking
> a long time to return results, whats happening is that data was recently
> inserted that would cause the data in the table to move because of where
> the
> clustering is? That SQL is searching for a moving target based on the data
> range of the SP, which is in the middle of being rearranged because of
> it's
> clustering setup? If this were the case, would it cause actual
> blocking(dont
> forget Im not seeing any.)? I know I'm grasping here, but dont know what
> else
> could cause this.
> TIA, ChrisR|||I havent looked, but will. Does this number represent how long it take SQL t
o
satisfy a query? Im doing my testing in Query Analyzer so I can already tell
that.
"Andrew J. Kelly" wrote:

> It's unlikely that is the cause. A page split if it occurs should only tak
e
> ms not minutes. And you would see blocking. My guess is you have a bad que
ry
> plan and disk bottlenecks. But the only way to be sure is to monitor the
> server from all aspects to see what is the bottleneck. What about wait's
> during that time?
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:D4E53ADF-5F2E-4F46-A32E-A94985132F56@.microsoft.com...
>
>|||Waits tell you allot about what sql server is waiting on most. See here for
more details:
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:90871384-25AD-485B-BB51-B623B8671647@.microsoft.com...[vbcol=seagreen]
>I havent looked, but will. Does this number represent how long it take SQL
>to
> satisfy a query? Im doing my testing in Query Analyzer so I can already
> tell
> that.
> "Andrew J. Kelly" wrote:
>

Dash boards with Reporting Services

Hi All,

I have been working on Reporting Services for quite some time but not able to find a way to create dash boards.

I hereby request you to tell me a way to do that.

An early reply from you help solve my problem.

Thanks

SPC

On which platform ? Plain HTML / ASP.NET / Sharepoint ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Moved to Reporting Services.

Damn, I failed my 70-229 again

Okay I am starting to think that 70-229 is not for me. I failed the thing again for the 2nd time. Boy sometimes I wonder why I went to college and passed SQL Server, but now I can not pass it through these MS exams. I think I will take it one last time, after that I am taking the Oracle root.Always treat failure as a step for success.

Attempts may fail ... but never fail to attempt...

Good luck.|||They are overrated...you'll find some of the poorest dba's who have passed it...

Just keep taking it, and you're bound to pass...

Plus the questions sometimes are based on situations you would never find yourself in...

You should never attack a situation in certain ways..

Check out this thread...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32178&SearchTerms=70-229|||Originally posted by Satya
Always treat failure as a step for success.

Attempts may fail ... but never fail to attempt...

Good luck.

Thanks for the wise words

Damn! Can not have it to work!

I am a noob toSQL server. I need it to create a database that should work along with my program. Firts time I have installed Microsoft SQL Server 2005 with all components (because I was not shure which components may appear necessary later). It does not work. I could create a database in SQL Server management Studio, but as soon as I tried to add a new table to the database an error message appeared: "Object reference not set to an instance of an object. (SQLEditors)". All the same happened in Visual Studio (they call it Business development studio or something like that). As I tried to connect a database an error message displayed: "can not make connection.... Object reference not set to an instance of an object". I formatted my hard drive, reinstalled windows (Windows Server 2003 R2), Installed SQL Server (this time, only database services an workstation components), downloaded and installed SP2. and it is all the same, damn thing does not work. Whatever i do - trying to add a new table or new view to existing database - I get the same damn ansver. And there is not an error number, why?
I would appreciate any advise.

pls refer this,

http://www.mcse.ms/message2008943.html they have solved reinstalling the workstation components

http://fabdata.wordpress.com/2007/03/24/object-reference-not-set-to-an-instance-of-an-object-sqleditors/ also this one

|||Deepak Rangarajan,
Unfortunately, that didn't work for me. I uninstalled workstation components, even nativeclient. restarted an installed them again. All the same - can not cerate table, can not create view, can not connection to databese in VS.

Cutting out the sentence

dear all,
I need do constantly queries against a remote server:
select * from [xxx.xx.xxx.xx].db.owner.table
I would like to avoid put every time the IP between brackets
Thanks a lot,Try using "Client Network Utility" to create an alias.
AMB
"Enric" wrote:

> dear all,
> I need do constantly queries against a remote server:
> select * from [xxx.xx.xxx.xx].db.owner.table
> I would like to avoid put every time the IP between brackets
> Thanks a lot,
>

CustomReportItem Element in an RDL

I have 3 related questions, thanks ahead of time for any help...
1 - Does anyone know of any documentation and/or examples on how to use the
CustomReportItem Element in an RDL doc? MS has a PDF available about the
RDL specification, RDLDEC03.PDF, but it is not very clear on how to use it.
2 - Is there a way to write your own custom Report Item that could be added
to the toolbox?
3- Specifically, when a report is rendered as HTML, we want to add a
checkbox and a bit of JavaScript to allow users to select/deselect report
records. Is there another way besides 1 or 2?
Thanks again!#1, #2:
The currently available RDL spec describes the CustomReportItem element as
defined in RS 2000. The specification is getting changed/extended in RS
2005. We are planning on making a comprehensive sample for implementing a
design time control (report designer) and a runtime control (report server)
available when RS 2005 gets released.
#3:
This is only possible if you write your own custom rendering extension (on
top of RS 2000 or RS 2005). You cannot do your own Javascript just through a
CustomReportItem processing/runtime control.
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Scott LaFave" <slafave@.trendmls.com> wrote in message
news:eDKv2%23$TFHA.2812@.tk2msftngp13.phx.gbl...
>I have 3 related questions, thanks ahead of time for any help...
> 1 - Does anyone know of any documentation and/or examples on how to use
> the CustomReportItem Element in an RDL doc? MS has a PDF available about
> the RDL specification, RDLDEC03.PDF, but it is not very clear on how to
> use it.
> 2 - Is there a way to write your own custom Report Item that could be
> added to the toolbox?
>
> 3- Specifically, when a report is rendered as HTML, we want to add a
> checkbox and a bit of JavaScript to allow users to select/deselect report
> records. Is there another way besides 1 or 2?
> Thanks again!
>|||hi,
About #2, is there a step by step RS 2000 example code ?
Thanks.
> #1, #2:
> The currently available RDL spec describes the CustomReportItem element as
> defined in RS 2000. The specification is getting changed/extended in RS
> 2005. We are planning on making a comprehensive sample for implementing a
> design time control (report designer) and a runtime control (report
server)
> available when RS 2005 gets released.
> #3:
> This is only possible if you write your own custom rendering extension (on
> top of RS 2000 or RS 2005). You cannot do your own Javascript just through
a
> CustomReportItem processing/runtime control.
>
> --
> Robert M. Bruckner
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Scott LaFave" <slafave@.trendmls.com> wrote in message
> news:eDKv2%23$TFHA.2812@.tk2msftngp13.phx.gbl...
> >I have 3 related questions, thanks ahead of time for any help...
> >
> > 1 - Does anyone know of any documentation and/or examples on how to use
> > the CustomReportItem Element in an RDL doc? MS has a PDF available
about
> > the RDL specification, RDLDEC03.PDF, but it is not very clear on how to
> > use it.
> >
> > 2 - Is there a way to write your own custom Report Item that could be
> > added to the toolbox?
> >
> >
> > 3- Specifically, when a report is rendered as HTML, we want to add a
> > checkbox and a bit of JavaScript to allow users to select/deselect
report
> > records. Is there another way besides 1 or 2?
> >
> > Thanks again!
> >
> >
>

Customizing the report scheduling...

one of the requirements is that if the report takes a long time to run the user can start report processing and later
when that report is processed user can see that in his 'My reports' section,

What will be the best way to do this, my main concerns are -

1 How can I use scheduling to accomplish this.

2 How to save the report, as snappshot or what...

3. How can I find in my Web app. that report is ready or not and show a link to it.

There isn't an easy way to know how long a report will take to run if this is what you after. Other than that:

1. Sure but scheduled reports have limitations. Since they run in an unattended mode, they cannot use User!UserID and all parameters must have defaults.

2. I would see if subscribed delivery works for your users. In fact, your users can create their own subscriptions if they have the necessary rights.

3. Subscribed reports can be delivered via e-mail. You don't need to do anything. The user will automatically receive the report via e-mail.

|||

Hi Teo,

Thanks for the reply,

1 What if we are not using the User!userId in report and all other parameters are passed while creating the schedule?

one more thing is while creating the schedule using web service (CreateSchedule) we can send a link of the report in email(without attaching the report);to what that link points to and cant we use that with ReportViewer control to show report to user.

2. I think after the schedule runs and finish processing report report service would be updating some table to show that this schedule is completed or not.

3. Just got an idea that, can i save the report in an ftp location through schedule and pick the report from there to show to user?

hope that makes sense....

|||

1. The parameters needs to be set when creating the subscription not schedule. A schedule can be shared among subscriptions. As I mentioned, if you decide to use subcribed delivery, it will be the end user who will set the parameters. When the user clicks on the link, the report will be open in the ASP.NET ReportViewer control. In the case of a custom application, we address a similar requirement by having a custom job controller which would generate the report on the server as a snapshot and send the history ID to the application once the report is ready.

2. Again, there is a difference between a schedule and subscription (if this is what you would use). That said, you can use GetScheduleProperties API to get the last time the schedule is run.

3. A subscribed report can be delivered to a network share. Start creating a subscription and you will see that you have a choice between e-mail and network share delivery.

|||

Teo Lachev wrote:

When the user clicks on the link, the report will be open in the ASP.NET ReportViewer control.

Did'nt got which link you are talking about...one sent in email? how come it will be opened in reportviewer?

Teo Lachev wrote:

In the case of a custom application, we address a similar requirement by having a custom job controller which would generate the report on the server as a snapshot and send the history ID to the application once the report is ready.

I think thats what I am looking for, can you provide some links for this or some sample app.

And by using CreateSubscription API i can set the schedule also or will need something else for that?

|||

Did'nt got which link you are talking about...one sent in email? how come it will be opened in reportviewer?

When you set up an e-mail subscription you can configure it to send the report link instead of the entire report. The report will open up in the ASP.NET report viewer. Try it out http://localhost/reportserver?/AdventureWorks%20Sample%20Reports/Company%20Sales&rs:Command=Render

I think thats what I am looking for, can you provide some links for this or some sample app.

In our case, we don't use subscriptions. Instead, the job service generates the report as a snapshot

if (parameters != null) // set report parameters {

managementProxy.SetReportParameters(reportPath, parameters);

}

// Create the report snapshot so the user can browse the report

managementProxy.UpdateReportExecutionSnapshot(reportPath);

// Check if the report is configured to keep snapshots in history

bool keepExecutionShapshots = false;

bool result = managementProxy.GetReportHistoryOptions(reportPath, out keepExecutionShapshots, out schedule);

if (keepExecutionShapshots)

{

// history is automatically created, get the list of history runs

ReportHistorySnapshot[] history = managementProxy.ListReportHistory(reportPath);

Array.Sort(history, CompareReportHistoryByDate); // need to sorty by date since history runs may not be chronologically sorted

historyID = history[history.Length - 1].HistoryID; //grab the last history run

}

else

{

// explicitly create history snapshot

historyID = managementProxy.CreateReportHistorySnapshot(reportPath, out warnings);

}

Customizing the connection string to the server where sp will be created?

When you create a SQL server project, it asks you for the connection string to the database. Can I change this connection string at run time?. By the way it is included in the project properties at desing time.I don't really understand the question - the connection string you mention is the string which tells VS where the assembly and procs etc will be deployed, when you do Deployment - at wich stage would you like to change the string? When your code is invoked eventually, the connection string is not used.

Niels