Script Execution of Report with parameters

I need to script the execution of reports with input parameter values and with the report output going to PDF in a local file location on the server. I can't use traditional Report Server scheduling because I have to loop through a list of clients and execute the report for each client.

I appreciate any help you can offer.

Hello,

You should take a look at Data-Driven Subscriptions (if you have Enterprise edition) , it will be able to handle what you're trying to do. You can set the render output, parameters, etc.

Jarret

|||

Thanks for the feedback Jarret. We have only had the Standard edition, but our new server will have the Enterprise edition for me to try Data-Driven Subscriptions in the next couple of weeks.

I setup a test server with the Demo of SQL 2005 Enterprise and here are my results for anyone else having similar goals.

We have about 30-40 reports all of which are used by 40-50 clients. The way I accomplish this is by using dynamic Data Source connections in the reports which can connect to all 40-50 client databases. It works fine for on-demand reporting where I pass in the connection information based on the client calling the report. By using these dynamic Data Sources, it saves me from having to maintain between 1200 and 2000 reports and/or linked reports, one for each combination of report and client. We simply don't have the staff to maintain that many reports.

By following the steps in the BOL and using Credentials stored securely in the report server, you can create a New Data Driven Subscription... and report against multiple databases from one schedule by passing in the connection information from the Subscription database.

Script

Hi Folks,
I need a script that it'll changed varchar field to nvarchar a table which i
pass it's name via parameter.
I generated it but it'll raise exception when a field has foreign key etc...
I don't want to spend more time to this work. Do you have a script like
this? Or program?
If you have it could you share with me?
Thanks.
Hi
If you try and change a column's data type and the column is referenced by a
foreign key, or is part of a primary key will give you an error. You will
need to drop these before trying to change the columns datatype. The easiest
way would be to script them, drop them and re-create them afterwards. To drop
them see http://www.mssqlserver.com/scripts/drop_keys.sql
John
"Erencan SADIRODLU" wrote:

> Hi Folks,
> I need a script that it'll changed varchar field to nvarchar a table which i
> pass it's name via parameter.
> I generated it but it'll raise exception when a field has foreign key etc...
> I don't want to spend more time to this work. Do you have a script like
> this? Or program?
> If you have it could you share with me?
> Thanks.
>
>
|||Thanks John, but i want to foreign keys add again after change data type.
This script just drop foreign keys.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...[vbcol=seagreen]
> Hi
> If you try and change a column's data type and the column is referenced by
> a
> foreign key, or is part of a primary key will give you an error. You will
> need to drop these before trying to change the columns datatype. The
> easiest
> way would be to script them, drop them and re-create them afterwards. To
> drop
> them see http://www.mssqlserver.com/scripts/drop_keys.sql
> John
> "Erencan SADIRODLU" wrote:
|||Hi
But you could script them first, which is what I indicated in the response.
There are several ways to do this, as this is a one off job use Enterprise
Manager. If you take a backup before making any changes and restore the
database under a different name, you could use a tool such as Red Gate's SQL
Compare to check the differences (in fact you could use this to re-apply the
missing FKs and PKs).
John
"Erencan SADIRODLU" wrote:

> Thanks John, but i want to foreign keys add again after change data type.
> This script just drop foreign keys.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...
>
>

Script

Hi Folks,
I need a script that it'll changed varchar field to nvarchar a table which i
pass it's name via parameter.
I generated it but it'll raise exception when a field has foreign key etc...
I don't want to spend more time to this work. Do you have a script like
this? Or program?
If you have it could you share with me?
Thanks.Hi
If you try and change a column's data type and the column is referenced by a
foreign key, or is part of a primary key will give you an error. You will
need to drop these before trying to change the columns datatype. The easiest
way would be to script them, drop them and re-create them afterwards. To drop
them see http://www.mssqlserver.com/scripts/drop_keys.sql
John
"Erencan SAÃ?IROÃ?LU" wrote:
> Hi Folks,
> I need a script that it'll changed varchar field to nvarchar a table which i
> pass it's name via parameter.
> I generated it but it'll raise exception when a field has foreign key etc...
> I don't want to spend more time to this work. Do you have a script like
> this? Or program?
> If you have it could you share with me?
> Thanks.
>
>|||Thanks John, but i want to foreign keys add again after change data type.
This script just drop foreign keys. :(
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...
> Hi
> If you try and change a column's data type and the column is referenced by
> a
> foreign key, or is part of a primary key will give you an error. You will
> need to drop these before trying to change the columns datatype. The
> easiest
> way would be to script them, drop them and re-create them afterwards. To
> drop
> them see http://www.mssqlserver.com/scripts/drop_keys.sql
> John
> "Erencan SADIRODLU" wrote:
>> Hi Folks,
>> I need a script that it'll changed varchar field to nvarchar a table
>> which i
>> pass it's name via parameter.
>> I generated it but it'll raise exception when a field has foreign key
>> etc...
>> I don't want to spend more time to this work. Do you have a script like
>> this? Or program?
>> If you have it could you share with me?
>> Thanks.
>>|||Hi
But you could script them first, which is what I indicated in the response.
There are several ways to do this, as this is a one off job use Enterprise
Manager. If you take a backup before making any changes and restore the
database under a different name, you could use a tool such as Red Gate's SQL
Compare to check the differences (in fact you could use this to re-apply the
missing FKs and PKs).
John
"Erencan SAÃ?IROÃ?LU" wrote:
> Thanks John, but i want to foreign keys add again after change data type.
> This script just drop foreign keys. :(
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...
> > Hi
> >
> > If you try and change a column's data type and the column is referenced by
> > a
> > foreign key, or is part of a primary key will give you an error. You will
> > need to drop these before trying to change the columns datatype. The
> > easiest
> > way would be to script them, drop them and re-create them afterwards. To
> > drop
> > them see http://www.mssqlserver.com/scripts/drop_keys.sql
> >
> > John
> >
> > "Erencan SADIRODLU" wrote:
> >
> >> Hi Folks,
> >> I need a script that it'll changed varchar field to nvarchar a table
> >> which i
> >> pass it's name via parameter.
> >> I generated it but it'll raise exception when a field has foreign key
> >> etc...
> >> I don't want to spend more time to this work. Do you have a script like
> >> this? Or program?
> >>
> >> If you have it could you share with me?
> >> Thanks.
> >>
> >>
> >>
>
>

Script

Hi Folks,
I need a script that it'll changed varchar field to nvarchar a table which i
pass it's name via parameter.
I generated it but it'll raise exception when a field has foreign key etc...
I don't want to spend more time to this work. Do you have a script like
this? Or program?
If you have it could you share with me?
Thanks.Hi
If you try and change a column's data type and the column is referenced by a
foreign key, or is part of a primary key will give you an error. You will
need to drop these before trying to change the columns datatype. The easies
t
way would be to script them, drop them and re-create them afterwards. To dro
p
them see http://www.mssqlserver.com/scripts/drop_keys.sql
John
"Erencan SADIRODLU" wrote:

> Hi Folks,
> I need a script that it'll changed varchar field to nvarchar a table which
i
> pass it's name via parameter.
> I generated it but it'll raise exception when a field has foreign key etc.
.
> I don't want to spend more time to this work. Do you have a script like
> this? Or program?
> If you have it could you share with me?
> Thanks.
>
>|||Thanks John, but i want to foreign keys add again after change data type.
This script just drop foreign keys.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...[vbcol=seagreen]
> Hi
> If you try and change a column's data type and the column is referenced by
> a
> foreign key, or is part of a primary key will give you an error. You will
> need to drop these before trying to change the columns datatype. The
> easiest
> way would be to script them, drop them and re-create them afterwards. To
> drop
> them see http://www.mssqlserver.com/scripts/drop_keys.sql
> John
> "Erencan SADIRODLU" wrote:
>|||Hi
But you could script them first, which is what I indicated in the response.
There are several ways to do this, as this is a one off job use Enterprise
Manager. If you take a backup before making any changes and restore the
database under a different name, you could use a tool such as Red Gate's SQL
Compare to check the differences (in fact you could use this to re-apply the
missing FKs and PKs).
John
"Erencan SADIRODLU" wrote:

> Thanks John, but i want to foreign keys add again after change data type.
> This script just drop foreign keys.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...
>
>

Script

Hi Folks,
I need a script that it'll changed varchar field to nvarchar a table which i
pass it's name via parameter.
I generated it but it'll raise exception when a field has foreign key etc...
I don't want to spend more time to this work. Do you have a script like
this? Or program?
If you have it could you share with me?
Thanks.
Hi
If you try and change a column's data type and the column is referenced by a
foreign key, or is part of a primary key will give you an error. You will
need to drop these before trying to change the columns datatype. The easiest
way would be to script them, drop them and re-create them afterwards. To drop
them see http://www.mssqlserver.com/scripts/drop_keys.sql
John
"Erencan SADIRODLU" wrote:

> Hi Folks,
> I need a script that it'll changed varchar field to nvarchar a table which i
> pass it's name via parameter.
> I generated it but it'll raise exception when a field has foreign key etc...
> I don't want to spend more time to this work. Do you have a script like
> this? Or program?
> If you have it could you share with me?
> Thanks.
>
>
|||Thanks John, but i want to foreign keys add again after change data type.
This script just drop foreign keys.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...[vbcol=seagreen]
> Hi
> If you try and change a column's data type and the column is referenced by
> a
> foreign key, or is part of a primary key will give you an error. You will
> need to drop these before trying to change the columns datatype. The
> easiest
> way would be to script them, drop them and re-create them afterwards. To
> drop
> them see http://www.mssqlserver.com/scripts/drop_keys.sql
> John
> "Erencan SADIRODLU" wrote:
|||Hi
But you could script them first, which is what I indicated in the response.
There are several ways to do this, as this is a one off job use Enterprise
Manager. If you take a backup before making any changes and restore the
database under a different name, you could use a tool such as Red Gate's SQL
Compare to check the differences (in fact you could use this to re-apply the
missing FKs and PKs).
John
"Erencan SADIRODLU" wrote:

> Thanks John, but i want to foreign keys add again after change data type.
> This script just drop foreign keys.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...
>
>

Script

Hi Folks,
I need a script that it'll changed varchar field to nvarchar a table which i
pass it's name via parameter.
I generated it but it'll raise exception when a field has foreign key etc...
I don't want to spend more time to this work. Do you have a script like
this? Or program?
If you have it could you share with me?
Thanks.
Hi
If you try and change a column's data type and the column is referenced by a
foreign key, or is part of a primary key will give you an error. You will
need to drop these before trying to change the columns datatype. The easiest
way would be to script them, drop them and re-create them afterwards. To drop
them see http://www.mssqlserver.com/scripts/drop_keys.sql
John
"Erencan SADIRODLU" wrote:

> Hi Folks,
> I need a script that it'll changed varchar field to nvarchar a table which i
> pass it's name via parameter.
> I generated it but it'll raise exception when a field has foreign key etc...
> I don't want to spend more time to this work. Do you have a script like
> this? Or program?
> If you have it could you share with me?
> Thanks.
>
>
|||Thanks John, but i want to foreign keys add again after change data type.
This script just drop foreign keys.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...[vbcol=seagreen]
> Hi
> If you try and change a column's data type and the column is referenced by
> a
> foreign key, or is part of a primary key will give you an error. You will
> need to drop these before trying to change the columns datatype. The
> easiest
> way would be to script them, drop them and re-create them afterwards. To
> drop
> them see http://www.mssqlserver.com/scripts/drop_keys.sql
> John
> "Erencan SADIRODLU" wrote:
|||Hi
But you could script them first, which is what I indicated in the response.
There are several ways to do this, as this is a one off job use Enterprise
Manager. If you take a backup before making any changes and restore the
database under a different name, you could use a tool such as Red Gate's SQL
Compare to check the differences (in fact you could use this to re-apply the
missing FKs and PKs).
John
"Erencan SADIRODLU" wrote:

> Thanks John, but i want to foreign keys add again after change data type.
> This script just drop foreign keys.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:55E6E65D-A653-4620-A2D3-C4BEB4A224AC@.microsoft.com...
>
>

scopeing problem in an aggregate function

I have a matrix with two column groups. I need to be able have the user
select from my boolean parameter and change the scope on my aggregate
function. I already have my boolean parameter setup. the two groups are
called matrix1_Year and matrix1_Hub. i used the following expression in my
textbox
=Count(Fields!DeliveryNumber.Value)/Count(Fields!DeliveryNumber.Value,IIF(Parameters!Scope1.Value,"matrix1_Year","matrix1_hub"))
when i preview the report it gives me the following error
The Value expression for the textbox â'textbox5â' has a scope parameter that
is not valid for an aggregate function. The scope parameter must be set to a
string constant that is equal to either the name of a containing group, the
name of a containing data region, or the name of a data set.
If i take the IIF statement out and put in either of the group names, the
report works and i get the values i would expect, but i need to be able to do
this by passing in a parameter, i don't want to create two report just for
this one problem.Have you tried adding the IIF to the outside... ie
=IIf (Parameters@.Scope1.Value,Big Expression1, Big Expression2)
I do not know if this will help, but it is possible that the problem is
related to WHEN the expression is evaluated..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"gacar101" wrote:
> I have a matrix with two column groups. I need to be able have the user
> select from my boolean parameter and change the scope on my aggregate
> function. I already have my boolean parameter setup. the two groups are
> called matrix1_Year and matrix1_Hub. i used the following expression in my
> textbox
> =Count(Fields!DeliveryNumber.Value)/Count(Fields!DeliveryNumber.Value,IIF(Parameters!Scope1.Value,"matrix1_Year","matrix1_hub"))
> when i preview the report it gives me the following error
> The Value expression for the textbox â'textbox5â' has a scope parameter that
> is not valid for an aggregate function. The scope parameter must be set to a
> string constant that is equal to either the name of a containing group, the
> name of a containing data region, or the name of a data set.
> If i take the IIF statement out and put in either of the group names, the
> report works and i get the values i would expect, but i need to be able to do
> this by passing in a parameter, i don't want to create two report just for
> this one problem.

Scope parameter

Hi
I have a report which includes a table as follows
table 1
table1_Group1
table1_Group2
In the footer of Group 1, I have a formula
=Sum(Fields!INVOICED.Value,"table1_Group2")
When I try and run the report, I keep getting the error
"The value expression for the textbox â'textbox21â' has a scope parameter that
is not valid for an aggregate function. The scope parameter must be set to a
string constant that is equal to either the name of a containing group, the
name of a containing data region, or the name of a data set."
As far as I can tell, table1_Group2 is a containing group?
The only thing that seems to work is putting the dataset name (or leaving it
out completely). The table name or group get this error.
Am I being stupid?
MacI think the problem is that table1_Group2 does not contain table1_Group1.
So a group can refer to something outside itself. But why not use this...?
=Sum(Fields!INVOICED.Value,"table1_Group1")
The sums should be the same, right? The multiple instances of table1_Group2
should correspond to the instance of table1_Group1 you're displaying.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Mac" <Mac@.discussions.microsoft.com> wrote in message
news:23899F92-A929-4407-854D-07B31AB99422@.microsoft.com...
> Hi
> I have a report which includes a table as follows
> table 1
> table1_Group1
> table1_Group2
> In the footer of Group 1, I have a formula
> =Sum(Fields!INVOICED.Value,"table1_Group2")
> When I try and run the report, I keep getting the error
> "The value expression for the textbox 'textbox21' has a scope parameter
> that
> is not valid for an aggregate function. The scope parameter must be set
> to a
> string constant that is equal to either the name of a containing group,
> the
> name of a containing data region, or the name of a data set."
> As far as I can tell, table1_Group2 is a containing group?
> The only thing that seems to work is putting the dataset name (or leaving
> it
> out completely). The table name or group get this error.
> Am I being stupid?
> Mac|||Ok that works although what I am trying to is this
I have a dataset which returns the amount invoiced by client
What I want the report to show is the breakdown of the top 20 clients and
then summarise the rest into a single line e.g.
Client 1 100,000
Client 2 90,000
.
.
.
Total top 20 190,000
Other Clients 60,000
Total Income 250,000
As group 2 holds the top 20 clients by using a filter, I wanted (hoping) to
sum the clients in that group only.
Using scope 1, I get the total for the entire dataset
"Jeff A. Stucker" wrote:
> I think the problem is that table1_Group2 does not contain table1_Group1.
> So a group can refer to something outside itself. But why not use this...?
> =Sum(Fields!INVOICED.Value,"table1_Group1")
> The sums should be the same, right? The multiple instances of table1_Group2
> should correspond to the instance of table1_Group1 you're displaying.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Mac" <Mac@.discussions.microsoft.com> wrote in message
> news:23899F92-A929-4407-854D-07B31AB99422@.microsoft.com...
> > Hi
> >
> > I have a report which includes a table as follows
> >
> > table 1
> > table1_Group1
> > table1_Group2
> >
> > In the footer of Group 1, I have a formula
> >
> > =Sum(Fields!INVOICED.Value,"table1_Group2")
> >
> > When I try and run the report, I keep getting the error
> >
> > "The value expression for the textbox 'textbox21' has a scope parameter
> > that
> > is not valid for an aggregate function. The scope parameter must be set
> > to a
> > string constant that is equal to either the name of a containing group,
> > the
> > name of a containing data region, or the name of a data set."
> >
> > As far as I can tell, table1_Group2 is a containing group?
> >
> > The only thing that seems to work is putting the dataset name (or leaving
> > it
> > out completely). The table name or group get this error.
> >
> > Am I being stupid?
> >
> > Mac
>
>|||Try playing with RunningValue in a formula or maybe a code block. Coupled
with the row count, you may be able to sum them up that way.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Mac" <Mac@.discussions.microsoft.com> wrote in message
news:CF9ED55F-C444-4342-B58F-E1863BF28BE3@.microsoft.com...
> Ok that works although what I am trying to is this
> I have a dataset which returns the amount invoiced by client
> What I want the report to show is the breakdown of the top 20 clients and
> then summarise the rest into a single line e.g.
> Client 1 100,000
> Client 2 90,000
> .
> .
> .
> Total top 20 190,000
> Other Clients 60,000
> Total Income 250,000
> As group 2 holds the top 20 clients by using a filter, I wanted (hoping)
> to
> sum the clients in that group only.
> Using scope 1, I get the total for the entire dataset
>
> "Jeff A. Stucker" wrote:
>> I think the problem is that table1_Group2 does not contain table1_Group1.
>> So a group can refer to something outside itself. But why not use
>> this...?
>> =Sum(Fields!INVOICED.Value,"table1_Group1")
>> The sums should be the same, right? The multiple instances of
>> table1_Group2
>> should correspond to the instance of table1_Group1 you're displaying.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "Mac" <Mac@.discussions.microsoft.com> wrote in message
>> news:23899F92-A929-4407-854D-07B31AB99422@.microsoft.com...
>> > Hi
>> >
>> > I have a report which includes a table as follows
>> >
>> > table 1
>> > table1_Group1
>> > table1_Group2
>> >
>> > In the footer of Group 1, I have a formula
>> >
>> > =Sum(Fields!INVOICED.Value,"table1_Group2")
>> >
>> > When I try and run the report, I keep getting the error
>> >
>> > "The value expression for the textbox 'textbox21' has a scope parameter
>> > that
>> > is not valid for an aggregate function. The scope parameter must be
>> > set
>> > to a
>> > string constant that is equal to either the name of a containing group,
>> > the
>> > name of a containing data region, or the name of a data set."
>> >
>> > As far as I can tell, table1_Group2 is a containing group?
>> >
>> > The only thing that seems to work is putting the dataset name (or
>> > leaving
>> > it
>> > out completely). The table name or group get this error.
>> >
>> > Am I being stupid?
>> >
>> > Mac
>>