Help if possible

I have very limited skill in Access, but am working on a project for work. I have a database file that includes fields like item number and ticket number. There are currently identical records for both of these fields. I'm trying to create a query that dislplays a specific item only if it has the same ticket number as any other item.

I'm not sure if I explained this the best, but any help would be greatly appreciated.select t1.itemnumber
, t1.ticketnumber
from yourtable as t1
inner
join yourtable as t2
on t1.ticketnumber
= t2.ticketnumber
and t1.itemnumber
<> t2.itemnumber|||I tried that code and it didn't work right, but maybe it's because I forgot to explain something in full detail. I'm looking to display the ticket number in which an itemnum 8999 was sold, but another itemnum was sold on that same ticket.|||select t2.itemnumber
, t1.ticketnumber
from yourtable as t1
inner
join yourtable as t2
on t1.ticketnumber
= t2.ticketnumber
and t1.itemnumber
<> t2.itemnumber
where t1.itemnumber = 8999sql

help how to create a row progressive..

Hi to everybody, I'd like to know how to extract a progressive number in a
select.
Example..

Select * from employers

Name Surname Position Age Hire Date Progressive
Mario Rossi Consultant 25 26/07/2003 1 (this
field doeas not exists in table)
fredrick Bauman Secretary 30 15/06/1999 2
Liana Orfei Director 45 15/01/1970 3
...................................... .....................
...........n

Thanks to every bodyHere's an example from the Pubs database:

SELECT
(SELECT COUNT(*)
FROM Authors
WHERE au_id <= A.au_id)
,*
FROM Authors AS A

--
David Portas
SQL Server MVP
--|||If you can afford to use an intermediate temp table:

select IDENTITY(int,1,1) as ProgressiveNumber into #Employers
select * from #Employers

MSSQL requires a table (even a temp table) on which to hang the identity
state info.

" Pecos Bill" <pecos.bill@.yahoo.it> wrote in message
news:6KlCc.15581$c_1.447390@.twister1.libero.it...
> Hi to everybody, I'd like to know how to extract a progressive number in a
> select.
> Example..
> Select * from employers
> Name Surname Position Age Hire Date Progressive
> Mario Rossi Consultant 25 26/07/2003 1 (this
> field doeas not exists in table)
> fredrick Bauman Secretary 30 15/06/1999 2
> Liana Orfei Director 45 15/01/1970 3|||> SELECT
> (SELECT COUNT(*)
> FROM Authors
> WHERE au_id <= A.au_id)
> ,*
> FROM Authors AS A

thanks...I'll try soon.|||"Mischa Sandberg" <mischa_sandberg@.telus.net> ha scritto nel messaggio
news:72pCc.6022$E84.827@.edtnps89...
> If you can afford to use an intermediate temp table:
> select IDENTITY(int,1,1) as ProgressiveNumber into #Employers
> select * from #Employers
> MSSQL requires a table (even a temp table) on which to hang the identity
> state info.

thanks...I'll try soon.
alessandro|||>> I'd like to know how to extract a progressive number in a select.
<<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access
or ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. In SQL and tiered architectures, things that deal with a
PHYSICAL ordering are done in the front end, never in the database.

Help for Sql Query

I am writing one procedure and in that i am giving three inputs ie account number and from date and to date, and i will give any one inupt ie account number or (from and to dates).

So in the procedure i want to select records from table and in where condition depending on input ie account number or from and to dates, so i am not able to write query,

I will write the query here what i know, please corrct the query bcoz i am getting error

Select
*
From Mf_Tran_Reg
Where
mft_fundcd='ABN' and mft_purred='P'
if @.Folio = '' --if account number is null
begin
and mft_procdate between @.Fdate and @.tdate
end
else
begin
and mft_accno= @.Folio
enddo the conditional branching earlier,

if @.Folio = '' --if account number is null

select * from tbl where param1 and param2 and param3

else

select * from tbl where param2 and param3

Quote:

Originally Posted by majidsql

I am writing one procedure and in that i am giving three inputs ie account number and from date and to date, and i will give any one inupt ie account number or (from and to dates).

So in the procedure i want to select records from table and in where condition depending on input ie account number or from and to dates, so i am not able to write query,

I will write the query here what i know, please corrct the query bcoz i am getting error

Select
*
From Mf_Tran_Reg
Where
mft_fundcd='ABN' and mft_purred='P'
if @.Folio = '' --if account number is null
begin
and mft_procdate between @.Fdate and @.tdate
end
else
begin
and mft_accno= @.Folio
end

Help for INTRO function

Hello.
I need to create a new table from a query result, then I've written this SQL
command:
"SELECT Name, Number FROM Friends INTRO Result"
BUT
SQL give me an error!!
Please Help Me
Bye
CrisI assume you mean INTO not INTRO
SELECT Name, Number INTO result FROM Friends
http://sqlservercode.blogspot.com/|||Cristian wrote:
> Hello.
> I need to create a new table from a query result, then I've written this S
QL
> command:
> "SELECT Name, Number FROM Friends INTRO Result"
> BUT
> SQL give me an error!!
> Please Help Me
> Bye
> Cris
It's INTO not INTRO.
However, the resulting table won't have any keys or constraints.
Although SELECT INTO is handy as a quick and dirty way to create
tables, it may be better to do something like:
CREATE TABLE Result (name VARCHAR(50) NOT NULL, number INTEGER NOT
NULL, PRIMARY KEY ...)
INSERT INTO Result (name, number)
SELECT name, number
FROM Friends ;
David Portas
SQL Server MVP
--|||Also,
If you need an autonumber in the destination table and need the data ordered
you must create the table first.
Good code:-
Create table myTable ( id int identity(1,1) not null, name varchar(50) not
null )
INSERT INTO myTable ( name ) SELECT name FROM friends ORDER BY name
if you use "select identity(int,1,1) AS id , name into MyTable from ..."
the order is not guaranteed and so it is advised against.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1134382059.521678.242420@.g44g2000cwa.googlegroups.com...
> Cristian wrote:
SQL
> It's INTO not INTRO.
> However, the resulting table won't have any keys or constraints.
> Although SELECT INTO is handy as a quick and dirty way to create
> tables, it may be better to do something like:
> CREATE TABLE Result (name VARCHAR(50) NOT NULL, number INTEGER NOT
> NULL, PRIMARY KEY ...)
> INSERT INTO Result (name, number)
> SELECT name, number
> FROM Friends ;
> --
> David Portas
> SQL Server MVP
> --
>

Help - Standard vs. Data-Driven Subscriptions

Hello,
We have a number of reports, all of which have from and to dates/times as
parameters. This allows us to display data on the report only for a certain
time period (the parameter values are passed to the stored procedure to
return the subset of data).

Anyway, we also need to schedule these reports to run automatically at
specified times. So, we're setting up subscriptions for these reports. The
issue that we're confused about is whether to use standard or data-driven
subscriptions.

Why is this important? Well, the from and to dates/times (parameter values)
will need to change depending on the scheduled run time, and will need to
change every time the report is executed on the schedule. For example, say
we have a report that needs to run every day at 6:00 am. This report needs
to display data for the last 24 hours (therefore, the from and to
dates/times are know--from = day before at 6:00 am and to = current day at
6:00 am). But, since it's running every day, the from and to dates/times
change every time the report schedule runs. I hope this makes sense.

So, is it possible to use a standard subscription to accomplish our goal?
Or, do we have to use some sort of data-driven subscription? Either way,
how would we set this up?

Thanks.
Either would probably work for you, but I'd probably use a data-driven subscription. The biggest reason why a data-driven would be better for you is the relative ease you can modify your parameter values each time you run the report. Essentially, all you'd have to do is modify the values of a couple columns of data in a particular row inside SQL Server.

You could build an expression to subtract / add X hours from the current date/time and use that as the basis for a parameter's default value, and you would be OK with this technique as long as the time you execute the report was always the same...you would be dead if you ran the report at 6a one day, and 9a the next, however. Using this technique would let you go with a standard subscription

|||Thanks for the info! However, I'm still a little unsure about the data-driven subscription route.

In reading Books Online, the documentation says that a data-driven subscription provides a way to deliver reports to a list of subscribers that is determined at run time or to support the wide distribution of a report with a fluctuating list of subscribers. This is not what we need. We don't have a list of subscribers. We only have one subscriber (I guess).

The documentation also notes that to set up a data-driven subscription you need to supply a command that gets subscriber data and that the query should produce one row for each subscriber. We don't even have a user table in our database to query in setting up the data-driven subscription.

All we need is a way to schedule reports to run at any frequency (i.e., daily, weekly, monthly, etc.) in which the report start and end dates/times are determined at run-time based on current date/time and passed as parameter values to the report for generation.

So, you also mentioned building an expression to subtract/add hours, what does this mean? And, how could we do that?

Or, do you have any other suggestions?

Thanks again.
|||

Data driven subscriptions (in my opinion, anyway) are much more flexible than standard subscriptions because you store the parameter values that you want to use inside a SQL table. When you want to change any of this information (or add a new subscriber), it's simply a matter of modifying the row in question or adding a new one. Data driven subscriptions DO take more effort to set up (creating a sql table to host the subscriber info and writing a simple query to return this info to the wizard) , but they pay off down the road because they are easy to care for and feed. Changing stuff around in a standard subscription requires a lot more clicking around in the UI and will take you more time.

You can use a data driven subscription regardless of whether you have one or many subscribers.

That being said, any parameter can have a default value, and that default value can be set via code (an expression) versus using a real, hardcoded value. So you could write an expression which gets the current date (using the Date() or Today() function) and then add or subtract a particular amount of time using the DataAdd() function. Set your defaults up like this and create a standard subscription, and you'll have a "sliding window" of time based on the current date/time. Again, you're at the mercy at the point in time the subscription is executed, etc. etc.

Hope this clears things up for you.

|||Thanks again for the info!

I think all this subscription stuff is starting to make a little sense to me. However, now I have another question.

Let's say that we go with the data-driven subscription scenario, and we create a table to hold subscriber info and all that. Can a data-drive subscription be created using the web service and our own UI instead of Report Manager? We are creating our own ASP.NET (C#) front-end for our Reporting Services implementation (we can't/don't want to use Report Manager) and we'll need to code a solution that allows end-users to create these data-driven subscriptions. Is this possible?

Thanks!
|||

Yes, you can do this -- no problem at all. Report Manager isn't magic in any form, and uses the same (documented) web service calls that you can.

|||

Hi,

In such case, if you are using a Stored Procedure (SP) to generate a Report. You can even add one extra input varchar parameter to SP to take ReportDateRange - e.g. Today, Y'day, CurrentWeek, etc. And then let that SP use this parameter to calculate the current date range and use it to generate report.

This will not only avoid complexity in subscription but also make your report SP more flexible and pluggable.

Thanks,
Mahesh

|||

Really, it doesn't matter. You could create a data-driven subscription with a query that gets the right date/time for the start and end parameters.

You could also put this logic in the default value expression for your parameter. Essentially, when you run the report, you can use an expression to obtain the current execution time of the report. From this value you can use standard .Net date/time functions to build the appropriate values.

Using this approach has the advantage that any user can run the report interactively or subscribe to it and get the same behavior.

Typically, people use two parameters for each query parameter to make this work well - first they create a parameter with valid values like 'Today', 'Yesterday', 'Last week', etc. Then they create a second parameter that is set to no-prompt. This parameter has a default value expression that is based on the value of the first parameter. This way you decouple the actual date used for the report from the value supplied by the user. Thus subscriptions will continue to work whether or not the user actually specifies 'use default' or a specific value for the one parameter they do see.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I am trying to create a Data-Driven Subscription to query off a value. I have the value in my stored procedure like:

Procedure dbo.sp_ReportMaster @.myid int=null

When I run the report in the Reporting Services, it prompts for myid, which is fine. I then deployed the report to the Reports Manager and tried to create a Data-Driven Subscription. I put in the pertinent information, then it says, "The 'ReportMaster' has no parameters," even though I have the parameter in my stored procedure and I put myid as a parameter in my report in Reporting Services.

What am I missing here?

Thanks, Iris

|||

Data-driven subscriptions using stored procs are not supported well, though you can make them work if you spend a little time playing with code.

The issue is the Report manager and management studio UI don't handle the case of a stored proc.

You can work around this by calling the createdatadrivensubcription method in code and passing in the prameters explicitly. This isn't entirely easy, but it can be made to work.

Sorry, I don't have an example handy. Take a look for the method definition in MSDN and then you can write a VB.Net script that runs in rs.exe to automate it.

-Lukasz

|||

I have a report that uses a stored procedure that pulls all my data, then I saw where I can create a linked report from my orginial report that will change the parameters. When I tried to use that, I got an error message, 'An interal error occurred on the report server.' I searched for this error when using linked reports, and it said to go to http://support.microsoft.com/kb/918222 and to install the hotfix.

The instructions said something about enabling SMO and SQL-DMO extended stored procedures. It didn't give much instructions but to search the SQL Server 2005 Books online. Then gave a note about making sure a default is set to 1 which mine is.

I installed the packages in order like it said, and still got the error.

Thanks, Iris

Help - Standard vs. Data-Driven Subscriptions

Hello,
We have a number of reports, all of which have from and to dates/times as
parameters. This allows us to display data on the report only for a certain
time period (the parameter values are passed to the stored procedure to
return the subset of data).

Anyway, we also need to schedule these reports to run automatically at
specified times. So, we're setting up subscriptions for these reports. The
issue that we're confused about is whether to use standard or data-driven
subscriptions.

Why is this important? Well, the from and to dates/times (parameter values)
will need to change depending on the scheduled run time, and will need to
change every time the report is executed on the schedule. For example, say
we have a report that needs to run every day at 6:00 am. This report needs
to display data for the last 24 hours (therefore, the from and to
dates/times are know--from = day before at 6:00 am and to = current day at
6:00 am). But, since it's running every day, the from and to dates/times
change every time the report schedule runs. I hope this makes sense.

So, is it possible to use a standard subscription to accomplish our goal?
Or, do we have to use some sort of data-driven subscription? Either way,
how would we set this up?

Thanks.
Either would probably work for you, but I'd probably use a data-driven subscription. The biggest reason why a data-driven would be better for you is the relative ease you can modify your parameter values each time you run the report. Essentially, all you'd have to do is modify the values of a couple columns of data in a particular row inside SQL Server.

You could build an expression to subtract / add X hours from the current date/time and use that as the basis for a parameter's default value, and you would be OK with this technique as long as the time you execute the report was always the same...you would be dead if you ran the report at 6a one day, and 9a the next, however. Using this technique would let you go with a standard subscription

|||Thanks for the info! However, I'm still a little unsure about the data-driven subscription route.

In reading Books Online, the documentation says that a data-driven subscription provides a way to deliver reports to a list of subscribers that is determined at run time or to support the wide distribution of a report with a fluctuating list of subscribers. This is not what we need. We don't have a list of subscribers. We only have one subscriber (I guess).

The documentation also notes that to set up a data-driven subscription you need to supply a command that gets subscriber data and that the query should produce one row for each subscriber. We don't even have a user table in our database to query in setting up the data-driven subscription.

All we need is a way to schedule reports to run at any frequency (i.e., daily, weekly, monthly, etc.) in which the report start and end dates/times are determined at run-time based on current date/time and passed as parameter values to the report for generation.

So, you also mentioned building an expression to subtract/add hours, what does this mean? And, how could we do that?

Or, do you have any other suggestions?

Thanks again.
|||

Data driven subscriptions (in my opinion, anyway) are much more flexible than standard subscriptions because you store the parameter values that you want to use inside a SQL table. When you want to change any of this information (or add a new subscriber), it's simply a matter of modifying the row in question or adding a new one. Data driven subscriptions DO take more effort to set up (creating a sql table to host the subscriber info and writing a simple query to return this info to the wizard) , but they pay off down the road because they are easy to care for and feed. Changing stuff around in a standard subscription requires a lot more clicking around in the UI and will take you more time.

You can use a data driven subscription regardless of whether you have one or many subscribers.

That being said, any parameter can have a default value, and that default value can be set via code (an expression) versus using a real, hardcoded value. So you could write an expression which gets the current date (using the Date() or Today() function) and then add or subtract a particular amount of time using the DataAdd() function. Set your defaults up like this and create a standard subscription, and you'll have a "sliding window" of time based on the current date/time. Again, you're at the mercy at the point in time the subscription is executed, etc. etc.

Hope this clears things up for you.

|||Thanks again for the info!

I think all this subscription stuff is starting to make a little sense to me. However, now I have another question.

Let's say that we go with the data-driven subscription scenario, and we create a table to hold subscriber info and all that. Can a data-drive subscription be created using the web service and our own UI instead of Report Manager? We are creating our own ASP.NET (C#) front-end for our Reporting Services implementation (we can't/don't want to use Report Manager) and we'll need to code a solution that allows end-users to create these data-driven subscriptions. Is this possible?

Thanks!
|||

Yes, you can do this -- no problem at all. Report Manager isn't magic in any form, and uses the same (documented) web service calls that you can.

|||

Hi,

In such case, if you are using a Stored Procedure (SP) to generate a Report. You can even add one extra input varchar parameter to SP to take ReportDateRange - e.g. Today, Y'day, CurrentWeek, etc. And then let that SP use this parameter to calculate the current date range and use it to generate report.

This will not only avoid complexity in subscription but also make your report SP more flexible and pluggable.

Thanks,
Mahesh

|||

Really, it doesn't matter. You could create a data-driven subscription with a query that gets the right date/time for the start and end parameters.

You could also put this logic in the default value expression for your parameter. Essentially, when you run the report, you can use an expression to obtain the current execution time of the report. From this value you can use standard .Net date/time functions to build the appropriate values.

Using this approach has the advantage that any user can run the report interactively or subscribe to it and get the same behavior.

Typically, people use two parameters for each query parameter to make this work well - first they create a parameter with valid values like 'Today', 'Yesterday', 'Last week', etc. Then they create a second parameter that is set to no-prompt. This parameter has a default value expression that is based on the value of the first parameter. This way you decouple the actual date used for the report from the value supplied by the user. Thus subscriptions will continue to work whether or not the user actually specifies 'use default' or a specific value for the one parameter they do see.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I am trying to create a Data-Driven Subscription to query off a value. I have the value in my stored procedure like:

Procedure dbo.sp_ReportMaster @.myid int=null

When I run the report in the Reporting Services, it prompts for myid, which is fine. I then deployed the report to the Reports Manager and tried to create a Data-Driven Subscription. I put in the pertinent information, then it says, "The 'ReportMaster' has no parameters," even though I have the parameter in my stored procedure and I put myid as a parameter in my report in Reporting Services.

What am I missing here?

Thanks, Iris

|||

Data-driven subscriptions using stored procs are not supported well, though you can make them work if you spend a little time playing with code.

The issue is the Report manager and management studio UI don't handle the case of a stored proc.

You can work around this by calling the createdatadrivensubcription method in code and passing in the prameters explicitly. This isn't entirely easy, but it can be made to work.

Sorry, I don't have an example handy. Take a look for the method definition in MSDN and then you can write a VB.Net script that runs in rs.exe to automate it.

-Lukasz

|||

I have a report that uses a stored procedure that pulls all my data, then I saw where I can create a linked report from my orginial report that will change the parameters. When I tried to use that, I got an error message, 'An interal error occurred on the report server.' I searched for this error when using linked reports, and it said to go to http://support.microsoft.com/kb/918222 and to install the hotfix.

The instructions said something about enabling SMO and SQL-DMO extended stored procedures. It didn't give much instructions but to search the SQL Server 2005 Books online. Then gave a note about making sure a default is set to 1 which mine is.

I installed the packages in order like it said, and still got the error.

Thanks, Iris

Help - Standard vs. Data-Driven Subscriptions

Hello,
We have a number of reports, all of which have from and to dates/times as
parameters. This allows us to display data on the report only for a certain
time period (the parameter values are passed to the stored procedure to
return the subset of data).

Anyway, we also need to schedule these reports to run automatically at
specified times. So, we're setting up subscriptions for these reports. The
issue that we're confused about is whether to use standard or data-driven
subscriptions.

Why is this important? Well, the from and to dates/times (parameter values)
will need to change depending on the scheduled run time, and will need to
change every time the report is executed on the schedule. For example, say
we have a report that needs to run every day at 6:00 am. This report needs
to display data for the last 24 hours (therefore, the from and to
dates/times are know--from = day before at 6:00 am and to = current day at
6:00 am). But, since it's running every day, the from and to dates/times
change every time the report schedule runs. I hope this makes sense.

So, is it possible to use a standard subscription to accomplish our goal?
Or, do we have to use some sort of data-driven subscription? Either way,
how would we set this up?

Thanks.
Either would probably work for you, but I'd probably use a data-driven subscription. The biggest reason why a data-driven would be better for you is the relative ease you can modify your parameter values each time you run the report. Essentially, all you'd have to do is modify the values of a couple columns of data in a particular row inside SQL Server.

You could build an expression to subtract / add X hours from the current date/time and use that as the basis for a parameter's default value, and you would be OK with this technique as long as the time you execute the report was always the same...you would be dead if you ran the report at 6a one day, and 9a the next, however. Using this technique would let you go with a standard subscription

|||Thanks for the info! However, I'm still a little unsure about the data-driven subscription route.

In reading Books Online, the documentation says that a data-driven subscription provides a way to deliver reports to a list of subscribers that is determined at run time or to support the wide distribution of a report with a fluctuating list of subscribers. This is not what we need. We don't have a list of subscribers. We only have one subscriber (I guess).

The documentation also notes that to set up a data-driven subscription you need to supply a command that gets subscriber data and that the query should produce one row for each subscriber. We don't even have a user table in our database to query in setting up the data-driven subscription.

All we need is a way to schedule reports to run at any frequency (i.e., daily, weekly, monthly, etc.) in which the report start and end dates/times are determined at run-time based on current date/time and passed as parameter values to the report for generation.

So, you also mentioned building an expression to subtract/add hours, what does this mean? And, how could we do that?

Or, do you have any other suggestions?

Thanks again.
|||

Data driven subscriptions (in my opinion, anyway) are much more flexible than standard subscriptions because you store the parameter values that you want to use inside a SQL table. When you want to change any of this information (or add a new subscriber), it's simply a matter of modifying the row in question or adding a new one. Data driven subscriptions DO take more effort to set up (creating a sql table to host the subscriber info and writing a simple query to return this info to the wizard) , but they pay off down the road because they are easy to care for and feed. Changing stuff around in a standard subscription requires a lot more clicking around in the UI and will take you more time.

You can use a data driven subscription regardless of whether you have one or many subscribers.

That being said, any parameter can have a default value, and that default value can be set via code (an expression) versus using a real, hardcoded value. So you could write an expression which gets the current date (using the Date() or Today() function) and then add or subtract a particular amount of time using the DataAdd() function. Set your defaults up like this and create a standard subscription, and you'll have a "sliding window" of time based on the current date/time. Again, you're at the mercy at the point in time the subscription is executed, etc. etc.

Hope this clears things up for you.

|||Thanks again for the info!

I think all this subscription stuff is starting to make a little sense to me. However, now I have another question.

Let's say that we go with the data-driven subscription scenario, and we create a table to hold subscriber info and all that. Can a data-drive subscription be created using the web service and our own UI instead of Report Manager? We are creating our own ASP.NET (C#) front-end for our Reporting Services implementation (we can't/don't want to use Report Manager) and we'll need to code a solution that allows end-users to create these data-driven subscriptions. Is this possible?

Thanks!
|||

Yes, you can do this -- no problem at all. Report Manager isn't magic in any form, and uses the same (documented) web service calls that you can.

|||

Hi,

In such case, if you are using a Stored Procedure (SP) to generate a Report. You can even add one extra input varchar parameter to SP to take ReportDateRange - e.g. Today, Y'day, CurrentWeek, etc. And then let that SP use this parameter to calculate the current date range and use it to generate report.

This will not only avoid complexity in subscription but also make your report SP more flexible and pluggable.

Thanks,
Mahesh

|||

Really, it doesn't matter. You could create a data-driven subscription with a query that gets the right date/time for the start and end parameters.

You could also put this logic in the default value expression for your parameter. Essentially, when you run the report, you can use an expression to obtain the current execution time of the report. From this value you can use standard .Net date/time functions to build the appropriate values.

Using this approach has the advantage that any user can run the report interactively or subscribe to it and get the same behavior.

Typically, people use two parameters for each query parameter to make this work well - first they create a parameter with valid values like 'Today', 'Yesterday', 'Last week', etc. Then they create a second parameter that is set to no-prompt. This parameter has a default value expression that is based on the value of the first parameter. This way you decouple the actual date used for the report from the value supplied by the user. Thus subscriptions will continue to work whether or not the user actually specifies 'use default' or a specific value for the one parameter they do see.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I am trying to create a Data-Driven Subscription to query off a value. I have the value in my stored procedure like:

Procedure dbo.sp_ReportMaster @.myid int=null

When I run the report in the Reporting Services, it prompts for myid, which is fine. I then deployed the report to the Reports Manager and tried to create a Data-Driven Subscription. I put in the pertinent information, then it says, "The 'ReportMaster' has no parameters," even though I have the parameter in my stored procedure and I put myid as a parameter in my report in Reporting Services.

What am I missing here?

Thanks, Iris

|||

Data-driven subscriptions using stored procs are not supported well, though you can make them work if you spend a little time playing with code.

The issue is the Report manager and management studio UI don't handle the case of a stored proc.

You can work around this by calling the createdatadrivensubcription method in code and passing in the prameters explicitly. This isn't entirely easy, but it can be made to work.

Sorry, I don't have an example handy. Take a look for the method definition in MSDN and then you can write a VB.Net script that runs in rs.exe to automate it.

-Lukasz

|||

I have a report that uses a stored procedure that pulls all my data, then I saw where I can create a linked report from my orginial report that will change the parameters. When I tried to use that, I got an error message, 'An interal error occurred on the report server.' I searched for this error when using linked reports, and it said to go to http://support.microsoft.com/kb/918222 and to install the hotfix.

The instructions said something about enabling SMO and SQL-DMO extended stored procedures. It didn't give much instructions but to search the SQL Server 2005 Books online. Then gave a note about making sure a default is set to 1 which mine is.

I installed the packages in order like it said, and still got the error.

Thanks, Iris

Help - Standard vs. Data-Driven Subscriptions

Hello,
We have a number of reports, all of which have from and to dates/times as
parameters. This allows us to display data on the report only for a certain
time period (the parameter values are passed to the stored procedure to
return the subset of data).

Anyway, we also need to schedule these reports to run automatically at
specified times. So, we're setting up subscriptions for these reports. The
issue that we're confused about is whether to use standard or data-driven
subscriptions.

Why is this important? Well, the from and to dates/times (parameter values)
will need to change depending on the scheduled run time, and will need to
change every time the report is executed on the schedule. For example, say
we have a report that needs to run every day at 6:00 am. This report needs
to display data for the last 24 hours (therefore, the from and to
dates/times are know--from = day before at 6:00 am and to = current day at
6:00 am). But, since it's running every day, the from and to dates/times
change every time the report schedule runs. I hope this makes sense.

So, is it possible to use a standard subscription to accomplish our goal?
Or, do we have to use some sort of data-driven subscription? Either way,
how would we set this up?

Thanks.Either would probably work for you, but I'd probably use a data-driven subscription. The biggest reason why a data-driven would be better for you is the relative ease you can modify your parameter values each time you run the report. Essentially, all you'd have to do is modify the values of a couple columns of data in a particular row inside SQL Server.

You could build an expression to subtract / add X hours from the current date/time and use that as the basis for a parameter's default value, and you would be OK with this technique as long as the time you execute the report was always the same...you would be dead if you ran the report at 6a one day, and 9a the next, however. Using this technique would let you go with a standard subscription|||Thanks for the info! However, I'm still a little unsure about the data-driven subscription route.

In reading Books Online, the documentation says that a data-driven subscription provides a way to deliver reports to a list of subscribers that is determined at run time or to support the wide distribution of a report with a fluctuating list of subscribers. This is not what we need. We don't have a list of subscribers. We only have one subscriber (I guess).

The documentation also notes that to set up a data-driven subscription you need to supply a command that gets subscriber data and that the query should produce one row for each subscriber. We don't even have a user table in our database to query in setting up the data-driven subscription.

All we need is a way to schedule reports to run at any frequency (i.e., daily, weekly, monthly, etc.) in which the report start and end dates/times are determined at run-time based on current date/time and passed as parameter values to the report for generation.

So, you also mentioned building an expression to subtract/add hours, what does this mean? And, how could we do that?

Or, do you have any other suggestions?

Thanks again.|||

Data driven subscriptions (in my opinion, anyway) are much more flexible than standard subscriptions because you store the parameter values that you want to use inside a SQL table. When you want to change any of this information (or add a new subscriber), it's simply a matter of modifying the row in question or adding a new one. Data driven subscriptions DO take more effort to set up (creating a sql table to host the subscriber info and writing a simple query to return this info to the wizard) , but they pay off down the road because they are easy to care for and feed. Changing stuff around in a standard subscription requires a lot more clicking around in the UI and will take you more time.

You can use a data driven subscription regardless of whether you have one or many subscribers.

That being said, any parameter can have a default value, and that default value can be set via code (an expression) versus using a real, hardcoded value. So you could write an expression which gets the current date (using the Date() or Today() function) and then add or subtract a particular amount of time using the DataAdd() function. Set your defaults up like this and create a standard subscription, and you'll have a "sliding window" of time based on the current date/time. Again, you're at the mercy at the point in time the subscription is executed, etc. etc.

Hope this clears things up for you.

|||Thanks again for the info!

I think all this subscription stuff is starting to make a little sense to me. However, now I have another question.

Let's say that we go with the data-driven subscription scenario, and we create a table to hold subscriber info and all that. Can a data-drive subscription be created using the web service and our own UI instead of Report Manager? We are creating our own ASP.NET (C#) front-end for our Reporting Services implementation (we can't/don't want to use Report Manager) and we'll need to code a solution that allows end-users to create these data-driven subscriptions. Is this possible?

Thanks!|||

Yes, you can do this -- no problem at all. Report Manager isn't magic in any form, and uses the same (documented) web service calls that you can.

|||

Hi,

In such case, if you are using a Stored Procedure (SP) to generate a Report. You can even add one extra input varchar parameter to SP to take ReportDateRange - e.g. Today, Y'day, CurrentWeek, etc. And then let that SP use this parameter to calculate the current date range and use it to generate report.

This will not only avoid complexity in subscription but also make your report SP more flexible and pluggable.

Thanks,
Mahesh

|||

Really, it doesn't matter. You could create a data-driven subscription with a query that gets the right date/time for the start and end parameters.

You could also put this logic in the default value expression for your parameter. Essentially, when you run the report, you can use an expression to obtain the current execution time of the report. From this value you can use standard .Net date/time functions to build the appropriate values.

Using this approach has the advantage that any user can run the report interactively or subscribe to it and get the same behavior.

Typically, people use two parameters for each query parameter to make this work well - first they create a parameter with valid values like 'Today', 'Yesterday', 'Last week', etc. Then they create a second parameter that is set to no-prompt. This parameter has a default value expression that is based on the value of the first parameter. This way you decouple the actual date used for the report from the value supplied by the user. Thus subscriptions will continue to work whether or not the user actually specifies 'use default' or a specific value for the one parameter they do see.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I am trying to create a Data-Driven Subscription to query off a value. I have the value in my stored procedure like:

Procedure dbo.sp_ReportMaster @.myid int=null

When I run the report in the Reporting Services, it prompts for myid, which is fine. I then deployed the report to the Reports Manager and tried to create a Data-Driven Subscription. I put in the pertinent information, then it says, "The 'ReportMaster' has no parameters," even though I have the parameter in my stored procedure and I put myid as a parameter in my report in Reporting Services.

What am I missing here?

Thanks, Iris

|||

Data-driven subscriptions using stored procs are not supported well, though you can make them work if you spend a little time playing with code.

The issue is the Report manager and management studio UI don't handle the case of a stored proc.

You can work around this by calling the createdatadrivensubcription method in code and passing in the prameters explicitly. This isn't entirely easy, but it can be made to work.

Sorry, I don't have an example handy. Take a look for the method definition in MSDN and then you can write a VB.Net script that runs in rs.exe to automate it.

-Lukasz

|||

I have a report that uses a stored procedure that pulls all my data, then I saw where I can create a linked report from my orginial report that will change the parameters. When I tried to use that, I got an error message, 'An interal error occurred on the report server.' I searched for this error when using linked reports, and it said to go to http://support.microsoft.com/kb/918222 and to install the hotfix.

The instructions said something about enabling SMO and SQL-DMO extended stored procedures. It didn't give much instructions but to search the SQL Server 2005 Books online. Then gave a note about making sure a default is set to 1 which mine is.

I installed the packages in order like it said, and still got the error.

Thanks, Iris

Help

I changed it a bit.. can anyone tell me how i can get rid
of the "number 25" but keep what i am trying to do(cause
some tables have more columns and some have less than 25):
declare @.i varchar(10)
set @.i = '1'
while @.i < 25
begin
select distinct o.name from syscomments c join
sysobjects o on o.id = c.id
where o.type IN ('TR','TF','P','X','V','FN')
and
c.text like '%order by ' + @.i + '%'
set @.i = @.i+1
end

>--Original Message--
>Can anyone help me,
>I am writting a script where i get to find out all the
>store procedure, triggers,... that has the
command "..from
>table_name order by 2,1.." for example...
>Here is what i got as a base idea... hope you understand
>what i am trying to get at:
>declare @.i int
>declare @.tbnames varchar (150)
>set @.i = 1
>declare test cursor for
> select [name] from sysobjects where xtype = 'U'
>open test
>fetch next from test into @.tbnames
>if @.@.fetch_status = 1
>Begin
> if @.i < 9
> begin
> select o.name from syscomments c join sysobjects o
>on o.id = c.id
> where o.type IN ('TR','TF','P','X','V','FN')
> and
> c.text like '%from ' + @.tbnames + 'order by' + @.i
>+ '%'
> set @.i = @.i + 1
> end
>
>fetch next from test into @.tbnames
>End
>close test
>deallocate test
>Can anyone give me a hand with this please
>.
>if you are looking for anything that has "order by " and a digit why not
take it out of the loop and try
c.text like '%order by [0-9]%'
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"" <anonymous@.discussions.microsoft.com> wrote in message
news:14fc301c41585$6bb3b6b0$a401280a@.phx
.gbl...
> I changed it a bit.. can anyone tell me how i can get rid
> of the "number 25" but keep what i am trying to do(cause
> some tables have more columns and some have less than 25):
> declare @.i varchar(10)
> set @.i = '1'
> while @.i < 25
> begin
> select distinct o.name from syscomments c join
> sysobjects o on o.id = c.id
> where o.type IN ('TR','TF','P','X','V','FN')
> and
> c.text like '%order by ' + @.i + '%'
> set @.i = @.i+1
> end
>
>
> command "..from