Dynamic Scripts

I've found that SSIS does not work well with running small dynamic scripts ...

For example, I have scripts that need be created via dynamic SQL. I want the dynamic SQL to be created using variables in SSIS.

The ways around this are to create small temporary stored procedures that I can create in the SSIS package at run time that have dynamic SQL in it - and execute it with the variables as my parameters.

Does anybody else do this, or have a better way of doing it?

I would be inclined to use:

package variables for my input values;|||

I have the same problem too; I tried 2 approaches to do it without success. Could someone please point out where I’m missing and how to do it?

Approach 1:

1, use an Execute SQL task the define an package variable

select last_modified_date as variable1

from table

2, use another Execute SQL task to define second package variable(variable2)

select * from sourceTable

where last_modified_date > ? --variable1

3, use variable2 in OLE db source in a data flow to get source data

Approach 2:

1, 1, use an Execute SQL task with dynimac sql to define an package variable

select ‘select * from SourceTable where last_modified_date > ‘’ + ‘’’ +last_modified_date + ‘’’ AS variable_SQL

from table

2, use the variable (variable_SQL) to get source data in OLE db source in a data flow

|||

Jessie,

Why is this not working for you? Do you get an error message?

-Jamie

|||I don't think he meant to say that it doesn't work- it's just not very elegant ... it's such a workaround and difficult to work with ...|||

This may work for you.

Create 2 string variables (SQL_DATE & SQL_QUERY)
Set SQL_DATE to 20050101
Change Expression property of SQL_QUERY to True
In Expression Value property, type in "Select * from mytable where datecol = " + @.[User::SQL_DATE]

Look at the resulting value. The query and the date should now be together.

You can apply this to many different scenarios. The biggest flaw, which will be fixed with SP1 is that there is no Expression designer for variables. Just use the one that is in one of the other tabs to test your expression.

cheers,

Andrew

|||I have just been creating stored procs and passing the parameters into them. This has been the easiest solution for me.

Dynamic Scripts

I've found that SSIS does not work well with running small dynamic scripts ...

For example, I have scripts that need be created via dynamic SQL. I want the dynamic SQL to be created using variables in SSIS.

The ways around this are to create small temporary stored procedures that I can create in the SSIS package at run time that have dynamic SQL in it - and execute it with the variables as my parameters.

Does anybody else do this, or have a better way of doing it?

I would be inclined to use:

package variables for my input values;|||

I have the same problem too; I tried 2 approaches to do it without success. Could someone please point out where I’m missing and how to do it?

Approach 1:

1, use an Execute SQL task the define an package variable

select last_modified_date as variable1

from table

2, use another Execute SQL task to define second package variable(variable2)

select * from sourceTable

where last_modified_date > ? --variable1

3, use variable2 in OLE db source in a data flow to get source data

Approach 2:

1, 1, use an Execute SQL task with dynimac sql to define an package variable

select ‘select * from SourceTable where last_modified_date > ‘’ + ‘’’ +last_modified_date + ‘’’ AS variable_SQL

from table

2, use the variable (variable_SQL) to get source data in OLE db source in a data flow

|||

Jessie,

Why is this not working for you? Do you get an error message?

-Jamie

|||I don't think he meant to say that it doesn't work- it's just not very elegant ... it's such a workaround and difficult to work with ...|||

This may work for you.

Create 2 string variables (SQL_DATE & SQL_QUERY)
Set SQL_DATE to 20050101
Change Expression property of SQL_QUERY to True
In Expression Value property, type in "Select * from mytable where datecol = " + @.[User::SQL_DATE]

Look at the resulting value. The query and the date should now be together.

You can apply this to many different scenarios. The biggest flaw, which will be fixed with SP1 is that there is no Expression designer for variables. Just use the one that is in one of the other tabs to test your expression.

cheers,

Andrew

|||I have just been creating stored procs and passing the parameters into them. This has been the easiest solution for me.

Dynamic Scripts

I've found that SSIS does not work well with running small dynamic scripts ...

For example, I have scripts that need be created via dynamic SQL. I want the dynamic SQL to be created using variables in SSIS.

The ways around this are to create small temporary stored procedures that I can create in the SSIS package at run time that have dynamic SQL in it - and execute it with the variables as my parameters.

Does anybody else do this, or have a better way of doing it?

I would be inclined to use:

package variables for my input values;|||

I have the same problem too; I tried 2 approaches to do it without success. Could someone please point out where I’m missing and how to do it?

Approach 1:

1, use an Execute SQL task the define an package variable

select last_modified_date as variable1

from table

2, use another Execute SQL task to define second package variable(variable2)

select * from sourceTable

where last_modified_date > ? --variable1

3, use variable2 in OLE db source in a data flow to get source data

Approach 2:

1, 1, use an Execute SQL task with dynimac sql to define an package variable

select ‘select * from SourceTable where last_modified_date > ‘’ + ‘’’ +last_modified_date + ‘’’ AS variable_SQL

from table

2, use the variable (variable_SQL) to get source data in OLE db source in a data flow

|||

Jessie,

Why is this not working for you? Do you get an error message?

-Jamie

|||I don't think he meant to say that it doesn't work- it's just not very elegant ... it's such a workaround and difficult to work with ...|||

This may work for you.

Create 2 string variables (SQL_DATE & SQL_QUERY)
Set SQL_DATE to 20050101
Change Expression property of SQL_QUERY to True
In Expression Value property, type in "Select * from mytable where datecol = " + @.[User::SQL_DATE]

Look at the resulting value. The query and the date should now be together.

You can apply this to many different scenarios. The biggest flaw, which will be fixed with SP1 is that there is no Expression designer for variables. Just use the one that is in one of the other tabs to test your expression.

cheers,

Andrew

|||I have just been creating stored procs and passing the parameters into them. This has been the easiest solution for me.sql

Dynamic Query View?

This summary is not available. Please click here to view the post.

dynamic queries

Hello All,
I are just starting to use reporting server (2003) and I have hit a
problem. The reports I have created so far have had 3 or 4 sets of possible
parameters, and I have created a different report based on each set. i.e.
agedTrialBalanceByCustomerNumber, agedTrialBalanceByAcccountNumber,
agedTrialBalanceBySeries, and agedTrialBalanceByCustomerNumberAndSeries.
The problem now is the next report has far, far too many parameter to
possibly create a report for each permutation. I can't figure out how to
dynamically create the where clause based on which parameters the report
gets passed. Just to clarify, the entire where clause needs to be dynamic,
not just the value.
I need something like this
SELECT blah
FROM table
WHERE IIF(Params!series <> null, "series=Params!series", "")
but this doesn't work.
any help is greatly appreciated,
CraigThe best option for this is to make use of Dynamic queries where you
build your queries dynamically inside the stored proc. You can pass the
superset of all parameters to the SP and check inside the SP. Refer
example below
Inputs to SP;
in_Name
in_Age
in_Sex
in_AcctNum
var str = 'Select * from ACCT_MASTER where'
if (in_name != null)
str = str + 'Name = ' + in_Name
etc.. etc...
finally execute the sp as
exec(str)
This is one of the way of satisfying your requirement|||YOu can also do
="SELECT blah FROM table "
& IIF(Parameters!Series.Value is nothing, ""," Where series='" &
Paramters!Series.Value & "'")
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.
"Craig L" wrote:
> Hello All,
> I are just starting to use reporting server (2003) and I have hit a
> problem. The reports I have created so far have had 3 or 4 sets of possible
> parameters, and I have created a different report based on each set. i.e.
> agedTrialBalanceByCustomerNumber, agedTrialBalanceByAcccountNumber,
> agedTrialBalanceBySeries, and agedTrialBalanceByCustomerNumberAndSeries.
> The problem now is the next report has far, far too many parameter to
> possibly create a report for each permutation. I can't figure out how to
> dynamically create the where clause based on which parameters the report
> gets passed. Just to clarify, the entire where clause needs to be dynamic,
> not just the value.
> I need something like this
> SELECT blah
> FROM table
> WHERE IIF(Params!series <> null, "series=Params!series", "")
> but this doesn't work.
> any help is greatly appreciated,
> Craig
>
>

Dynamic Parameter

I have created the following two parameters:
DateRange Parameter with available values:
1. label = today, value = 1
2. label = yesterday, value = 2
StartDate with Default Value (Non Queried) = =
iif(Parameters!PMDateRange.Value = "1",
now(),
iif(Parameters!PMDateRange.Value = "2",
Today.AddDays(-1),
nothing))
The idea is that the user selects a daterange parameter and that the
startdate parameter is populated accordingly, the user must still have the
ability to manually override the start date if desired.
When I run the report I get the following error message.
he value expression for the report parameter â'PMStartDateâ' contains an
error: [BC30654] 'Return' statement in a Function or a Get must return a
value.The way I have implemented this is by creating another dataset like
(although against oracle)
select sysdate-1 as Yesterday, sysdate as Today from dual
and use this dataset in the default section of the parameter as 'Queried'..
the user can change the dates if needed..
Hope this helps..
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:D74138F5-D842-4AB8-82E9-5CCB4315AA90@.microsoft.com...
> I have created the following two parameters:
> DateRange Parameter with available values:
> 1. label = today, value = 1
> 2. label = yesterday, value = 2
> StartDate with Default Value (Non Queried) = => iif(Parameters!PMDateRange.Value = "1",
> now(),
> iif(Parameters!PMDateRange.Value = "2",
> Today.AddDays(-1),
> nothing))
> The idea is that the user selects a daterange parameter and that the
> startdate parameter is populated accordingly, the user must still have the
> ability to manually override the start date if desired.
> When I run the report I get the following error message.
> he value expression for the report parameter 'PMStartDate' contains an
> error: [BC30654] 'Return' statement in a Function or a Get must return a
> value.|||Thanks RK but its not quite what I am looking for. Does anyone have any other
ideas?
PLEASE!!!
"RK Balaji" wrote:
> The way I have implemented this is by creating another dataset like
> (although against oracle)
> select sysdate-1 as Yesterday, sysdate as Today from dual
> and use this dataset in the default section of the parameter as 'Queried'..
> the user can change the dates if needed..
> Hope this helps..
> "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> news:D74138F5-D842-4AB8-82E9-5CCB4315AA90@.microsoft.com...
> > I have created the following two parameters:
> >
> > DateRange Parameter with available values:
> > 1. label = today, value = 1
> > 2. label = yesterday, value = 2
> >
> > StartDate with Default Value (Non Queried) = => >
> > iif(Parameters!PMDateRange.Value = "1",
> > now(),
> > iif(Parameters!PMDateRange.Value = "2",
> > Today.AddDays(-1),
> > nothing))
> >
> > The idea is that the user selects a daterange parameter and that the
> > startdate parameter is populated accordingly, the user must still have the
> > ability to manually override the start date if desired.
> >
> > When I run the report I get the following error message.
> >
> > he value expression for the report parameter 'PMStartDate' contains an
> > error: [BC30654] 'Return' statement in a Function or a Get must return a
> > value.
>
>

Dynamic Members Security

Hi All,
I have a working solution with user-defined security. When user connects to a cube, a list with allowed members is created(retrieved from table) and returned as set to AS. Everything works fine.

Problem is that members are retrieved only at connection time. When I change my table with allowed members later, I would like to force AS to re-read this information again. Of course I can restart AS service or do full process of cube.

Does anybody have an idea?

Thanks,
Radim

Have you tried just reprocessing the dimension affected? What are the specifics of the solution?

|||That's very correct. Processing is possible solution but since I have many concurent users I didn't want to disrupt them. But yesterday I played with it more and I discovered that it's only necessary to do Process index. That's enough to force re-read of allowed members.

Thanks for contribution.

Radim

Dynamic MDX Deployement in Reporting Services

I have created a Reporting Services report that uses MDX to retrieve data
from a cube. The MDX statements must use a parameter so they are placed into
text strings. The report will run with the Dynamic MDX in Visual Studio but
when I deploy it to the Web Server it locks up.
I believe that the dynamic MDX is causing the problem. The reports that
connect to the Cube that do not use parameters run fine on the web.
Any advice would be appreciated.
Thanks.I found this to be very helpful with parameterized MD
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
"AndyT" wrote:
> I have created a Reporting Services report that uses MDX to retrieve data
> from a cube. The MDX statements must use a parameter so they are placed into
> text strings. The report will run with the Dynamic MDX in Visual Studio but
> when I deploy it to the Web Server it locks up.
> I believe that the dynamic MDX is causing the problem. The reports that
> connect to the Cube that do not use parameters run fine on the web.
> Any advice would be appreciated.
> Thanks.

Dynamic Images with ASPX extensions in Reports

I want to create a report that shows dynamically created images from a web
site. It looks like it will work OK, I can put the graphic in and a link
http://localhost/image.aspx?123 but the image doiesnot show in the reports.
I am guessing there is a miss match between the extension and the MIMEType
thats stuffing it up. Can anybody confirm this, before I rush into building
a HTTPRequestHandler.
Message posted via http://www.sqlmonster.com
I think you meant to post this in the reporting services newsgroup.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tom Robson via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:acbdec17de10456a86dee6a044719188@.SQLMonster.c om...
> I want to create a report that shows dynamically created images from a web
> site. It looks like it will work OK, I can put the graphic in and a link
> http://localhost/image.aspx?123 but the image doiesnot show in the
reports.
> I am guessing there is a miss match between the extension and the MIMEType
> thats stuffing it up. Can anybody confirm this, before I rush into
building
> a HTTPRequestHandler.
> --
> Message posted via http://www.sqlmonster.com
|||Tom
I am facing the same problem! Did you ever find a way to show an image
using a url such as http://localhost/image.aspx?123
Any help would be great as I am starting to tear my hair out!!!
Richard
Message posted via http://www.sqlmonster.com

Dynamic Hyperlink

I've created a simple report that lists out Invoice numbers and Invoice
Titles. When a user clicks the Title, I want the user to be sent to a page
that gives details for that Title.
The syntax needs to be like:
http://servername/details.aspx?ID=123
The ID parameter needs to dynamically change to match the Invoice number for
that Title. The closest I've gotten is:
http://servername/details.aspx?ID=Fields!Invoice.Value
Which (of course) throws an error in the browser when you click the link.
Any ideas how I can do this? Thanks for your help.
MikeFirst, there are two ways to do this. Jump to Report and Jump to URL. Jump
to Report is the easiest. Click on the field, properties,advanced,
navigation. Pick Jump to Report and then set the parameter mapping for it.
Now, if you are going to a non-report page (which is what it seems you want
to do) then you need to use the jump to URL. This is an expression so you
would put in this:
="http://servername/details.aspx?ID=" & Fields!Invoice.Value
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Wisconsin Mike" <WisconsinMike@.discussions.microsoft.com> wrote in message
news:42BEF1D5-46D3-47C8-ABC3-77297BE21890@.microsoft.com...
> I've created a simple report that lists out Invoice numbers and Invoice
> Titles. When a user clicks the Title, I want the user to be sent to a
page
> that gives details for that Title.
> The syntax needs to be like:
> http://servername/details.aspx?ID=123
> The ID parameter needs to dynamically change to match the Invoice number
for
> that Title. The closest I've gotten is:
> http://servername/details.aspx?ID=Fields!Invoice.Value
> Which (of course) throws an error in the browser when you click the link.
> Any ideas how I can do this? Thanks for your help.
> Mike|||Thanks, Bruce! I was missing the equals sign at the beginning of my
expression and the quotes around the url. Works great.
"Bruce L-C [MVP]" wrote:
> First, there are two ways to do this. Jump to Report and Jump to URL. Jump
> to Report is the easiest. Click on the field, properties,advanced,
> navigation. Pick Jump to Report and then set the parameter mapping for it.
> Now, if you are going to a non-report page (which is what it seems you want
> to do) then you need to use the jump to URL. This is an expression so you
> would put in this:
> ="http://servername/details.aspx?ID=" & Fields!Invoice.Value
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Wisconsin Mike" <WisconsinMike@.discussions.microsoft.com> wrote in message
> news:42BEF1D5-46D3-47C8-ABC3-77297BE21890@.microsoft.com...
> > I've created a simple report that lists out Invoice numbers and Invoice
> > Titles. When a user clicks the Title, I want the user to be sent to a
> page
> > that gives details for that Title.
> >
> > The syntax needs to be like:
> >
> > http://servername/details.aspx?ID=123
> >
> > The ID parameter needs to dynamically change to match the Invoice number
> for
> > that Title. The closest I've gotten is:
> >
> > http://servername/details.aspx?ID=Fields!Invoice.Value
> >
> > Which (of course) throws an error in the browser when you click the link.
> > Any ideas how I can do this? Thanks for your help.
> >
> > Mike
>
>

Dynamic generation of table name

I have a database that has some of its tables created at run-time, i.e., a table name is computed by a program and then the table is created, and the data - written to it. My program will have no trouble finding those tables but the end users will want to run queries using a DBMS client of their choice. They may simply want us to write them some predefined queries that they can execute in MSQuery to build Excel spreadsheets, or something like that.
My question is: is it possible to put in the FROM clause of a SELECT statement anything other than a string describing the name of a table in the database, and more specifically, a sub-query? Let's say I have a table with a known name that contains two columns, and I want to use the concatenation of the contents of those two columns to build the names of some tables, which will then be used in the FROM clause of another SELECT statement. Is that possible and how can I do that? Thanks in advance!
KamenFor end-users: NO, for Developers you can use dynamic SQL. :shocked:
Maybe for the end-users you could create a synonym for these tables, for example: every time you create a new table, you could drop old synonym and create new pointing to new table. ;)|||Bummer... That's what I thought, looking at the SQL documentation. I guess they can always use some "programmable" client, such as MS Access. Thanks, anyway.
Kamen

Dynamic Font In Crystal Report

Hi!
I have created a report and now i want that when user takes the printout, he can select the font style and size ...........Is it possible and if yes then pls let me know..........
ThanksI dont think it is possible
You need to design the report with the font client wants

Dynamic filenames from scheduled reports

We created a sales history report for a client that has one prompt, Sales
Region, and when they schedule the report all the filenames of the Excel
attachments are the same. They are all the report name.
They would like each file to have a name like
"SalesRegion-SelectedRegion.xls". And possibly the dynamic month generated
in the report from a user defined function.
Is there anyway to include the prompted Sales Region in the filename?
On a related issue I have noticed people discussing doing Data Driven
subscriptions, but even though the report data source credentials are saved
in the report server it is not an option for them.
Any help would be greatly appreciated.Without using Data Driven subscription, the fileshare delivery extension
does not have any means to accomplish this.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"IST" <IST@.discussions.microsoft.com> wrote in message
news:5A358241-7057-4E4C-B5B3-F7E5C32D3A2C@.microsoft.com...
> We created a sales history report for a client that has one prompt, Sales
> Region, and when they schedule the report all the filenames of the Excel
> attachments are the same. They are all the report name.
> They would like each file to have a name like
> "SalesRegion-SelectedRegion.xls". And possibly the dynamic month
> generated
> in the report from a user defined function.
> Is there anyway to include the prompted Sales Region in the filename?
> On a related issue I have noticed people discussing doing Data Driven
> subscriptions, but even though the report data source credentials are
> saved
> in the report server it is not an option for them.
> Any help would be greatly appreciated.|||These reports are scheduled to be delivered as Excel attachments. Please
elaborate on your response to this.
"Daniel Reib [MSFT]" wrote:
> Without using Data Driven subscription, the fileshare delivery extension
> does not have any means to accomplish this.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "IST" <IST@.discussions.microsoft.com> wrote in message
> news:5A358241-7057-4E4C-B5B3-F7E5C32D3A2C@.microsoft.com...
> > We created a sales history report for a client that has one prompt, Sales
> > Region, and when they schedule the report all the filenames of the Excel
> > attachments are the same. They are all the report name.
> >
> > They would like each file to have a name like
> > "SalesRegion-SelectedRegion.xls". And possibly the dynamic month
> > generated
> > in the report from a user defined function.
> >
> > Is there anyway to include the prompted Sales Region in the filename?
> >
> > On a related issue I have noticed people discussing doing Data Driven
> > subscriptions, but even though the report data source credentials are
> > saved
> > in the report server it is not an option for them.
> >
> > Any help would be greatly appreciated.
>
>|||When a regular subscription is created the name of the file must be passed
in. The Fileshare extension provides no means for which to add information
dynamically to the name of the generated file. If you used a data driven
subscription you could create the name in the SQL query and have that passed
in as the file name. If the query is smart enough it could generate the
filename with the desired parameter embedded. However, you said DD
subscriptions were not an option, and the Fileshare extension does not have
the ability to do this.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"IST" <IST@.discussions.microsoft.com> wrote in message
news:AB59B9E4-9395-4D37-BD0A-43C02CCFAD1B@.microsoft.com...
> These reports are scheduled to be delivered as Excel attachments. Please
> elaborate on your response to this.
>
> "Daniel Reib [MSFT]" wrote:
>> Without using Data Driven subscription, the fileshare delivery extension
>> does not have any means to accomplish this.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "IST" <IST@.discussions.microsoft.com> wrote in message
>> news:5A358241-7057-4E4C-B5B3-F7E5C32D3A2C@.microsoft.com...
>> > We created a sales history report for a client that has one prompt,
>> > Sales
>> > Region, and when they schedule the report all the filenames of the
>> > Excel
>> > attachments are the same. They are all the report name.
>> >
>> > They would like each file to have a name like
>> > "SalesRegion-SelectedRegion.xls". And possibly the dynamic month
>> > generated
>> > in the report from a user defined function.
>> >
>> > Is there anyway to include the prompted Sales Region in the filename?
>> >
>> > On a related issue I have noticed people discussing doing Data Driven
>> > subscriptions, but even though the report data source credentials are
>> > saved
>> > in the report server it is not an option for them.
>> >
>> > Any help would be greatly appreciated.
>>|||Thanks for your assistance Daniel. So there is no way to override the
"report name" as the attachment name in a standard scheduled report
regardless of the render type.
Outside of the fact that you should be able to select the attachment name in
the report manager just like you do the subject when scheduling a report.
i.e. "@.ReportName was executed at @.ExecutionTime"
The sales manager at our client location refuses to open each Excel
spreadsheet attachement in his email to find the sales region and then select
"Save As" with this name for each of their 77 sales regions every month.
I can't blame them. This is supposed to be automated.
Do you have any suggestions to make this work as an automated solution?
If this has to be done via Data Driven Subscriptions, do you have any
information about why a report that has stored credential would not have the
option of data driven subscription? If that works, could you please give me
an example to use for giving these managers their reports with Region/Month
filenames?
I have been using RS since the first public beta. It is an amazing product.
We are just struggling with a few of these detail issues.
Thank you so much for your time.
"Daniel Reib [MSFT]" wrote:
> When a regular subscription is created the name of the file must be passed
> in. The Fileshare extension provides no means for which to add information
> dynamically to the name of the generated file. If you used a data driven
> subscription you could create the name in the SQL query and have that passed
> in as the file name. If the query is smart enough it could generate the
> filename with the desired parameter embedded. However, you said DD
> subscriptions were not an option, and the Fileshare extension does not have
> the ability to do this.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "IST" <IST@.discussions.microsoft.com> wrote in message
> news:AB59B9E4-9395-4D37-BD0A-43C02CCFAD1B@.microsoft.com...
> > These reports are scheduled to be delivered as Excel attachments. Please
> > elaborate on your response to this.
> >
> >
> > "Daniel Reib [MSFT]" wrote:
> >
> >> Without using Data Driven subscription, the fileshare delivery extension
> >> does not have any means to accomplish this.
> >>
> >> --
> >> -Daniel
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "IST" <IST@.discussions.microsoft.com> wrote in message
> >> news:5A358241-7057-4E4C-B5B3-F7E5C32D3A2C@.microsoft.com...
> >> > We created a sales history report for a client that has one prompt,
> >> > Sales
> >> > Region, and when they schedule the report all the filenames of the
> >> > Excel
> >> > attachments are the same. They are all the report name.
> >> >
> >> > They would like each file to have a name like
> >> > "SalesRegion-SelectedRegion.xls". And possibly the dynamic month
> >> > generated
> >> > in the report from a user defined function.
> >> >
> >> > Is there anyway to include the prompted Sales Region in the filename?
> >> >
> >> > On a related issue I have noticed people discussing doing Data Driven
> >> > subscriptions, but even though the report data source credentials are
> >> > saved
> >> > in the report server it is not an option for them.
> >> >
> >> > Any help would be greatly appreciated.
> >>
> >>
> >>
>
>|||Data driven subscriptions are not supported in the standard edition of RS.
Is it possible that he has that?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"IST" <IST@.discussions.microsoft.com> wrote in message
news:956BB75C-B82C-46E6-AA5E-91AEDDD7B0CB@.microsoft.com...
> Thanks for your assistance Daniel. So there is no way to override the
> "report name" as the attachment name in a standard scheduled report
> regardless of the render type.
> Outside of the fact that you should be able to select the attachment name
> in
> the report manager just like you do the subject when scheduling a report.
> i.e. "@.ReportName was executed at @.ExecutionTime"
> The sales manager at our client location refuses to open each Excel
> spreadsheet attachement in his email to find the sales region and then
> select
> "Save As" with this name for each of their 77 sales regions every month.
> I can't blame them. This is supposed to be automated.
> Do you have any suggestions to make this work as an automated solution?
> If this has to be done via Data Driven Subscriptions, do you have any
> information about why a report that has stored credential would not have
> the
> option of data driven subscription? If that works, could you please give
> me
> an example to use for giving these managers their reports with
> Region/Month
> filenames?
> I have been using RS since the first public beta. It is an amazing
> product.
> We are just struggling with a few of these detail issues.
> Thank you so much for your time.
>
> "Daniel Reib [MSFT]" wrote:
>> When a regular subscription is created the name of the file must be
>> passed
>> in. The Fileshare extension provides no means for which to add
>> information
>> dynamically to the name of the generated file. If you used a data driven
>> subscription you could create the name in the SQL query and have that
>> passed
>> in as the file name. If the query is smart enough it could generate the
>> filename with the desired parameter embedded. However, you said DD
>> subscriptions were not an option, and the Fileshare extension does not
>> have
>> the ability to do this.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "IST" <IST@.discussions.microsoft.com> wrote in message
>> news:AB59B9E4-9395-4D37-BD0A-43C02CCFAD1B@.microsoft.com...
>> > These reports are scheduled to be delivered as Excel attachments.
>> > Please
>> > elaborate on your response to this.
>> >
>> >
>> > "Daniel Reib [MSFT]" wrote:
>> >
>> >> Without using Data Driven subscription, the fileshare delivery
>> >> extension
>> >> does not have any means to accomplish this.
>> >>
>> >> --
>> >> -Daniel
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "IST" <IST@.discussions.microsoft.com> wrote in message
>> >> news:5A358241-7057-4E4C-B5B3-F7E5C32D3A2C@.microsoft.com...
>> >> > We created a sales history report for a client that has one prompt,
>> >> > Sales
>> >> > Region, and when they schedule the report all the filenames of the
>> >> > Excel
>> >> > attachments are the same. They are all the report name.
>> >> >
>> >> > They would like each file to have a name like
>> >> > "SalesRegion-SelectedRegion.xls". And possibly the dynamic month
>> >> > generated
>> >> > in the report from a user defined function.
>> >> >
>> >> > Is there anyway to include the prompted Sales Region in the
>> >> > filename?
>> >> >
>> >> > On a related issue I have noticed people discussing doing Data
>> >> > Driven
>> >> > subscriptions, but even though the report data source credentials
>> >> > are
>> >> > saved
>> >> > in the report server it is not an option for them.
>> >> >
>> >> > Any help would be greatly appreciated.
>> >>
>> >>
>> >>
>>|||Hello Daniel, That is exactly it. They have the standard version of RS.
Do we have any options using either Email or Fileshare to get a Excel
filename other than the default report name? i.e. Instead of all 77 email
having the same attachment filename "Sales Region.xls" it could be even the a
combination of the report name and report parameter "@.SALESREGION" resulting
in "Sales Region - North East.xls".
Thanks.
"Daniel Reib [MSFT]" wrote:
> Data driven subscriptions are not supported in the standard edition of RS.
> Is it possible that he has that?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "IST" <IST@.discussions.microsoft.com> wrote in message
> news:956BB75C-B82C-46E6-AA5E-91AEDDD7B0CB@.microsoft.com...
> > Thanks for your assistance Daniel. So there is no way to override the
> > "report name" as the attachment name in a standard scheduled report
> > regardless of the render type.
> >
> > Outside of the fact that you should be able to select the attachment name
> > in
> > the report manager just like you do the subject when scheduling a report.
> > i.e. "@.ReportName was executed at @.ExecutionTime"
> >
> > The sales manager at our client location refuses to open each Excel
> > spreadsheet attachement in his email to find the sales region and then
> > select
> > "Save As" with this name for each of their 77 sales regions every month.
> >
> > I can't blame them. This is supposed to be automated.
> >
> > Do you have any suggestions to make this work as an automated solution?
> >
> > If this has to be done via Data Driven Subscriptions, do you have any
> > information about why a report that has stored credential would not have
> > the
> > option of data driven subscription? If that works, could you please give
> > me
> > an example to use for giving these managers their reports with
> > Region/Month
> > filenames?
> >
> > I have been using RS since the first public beta. It is an amazing
> > product.
> > We are just struggling with a few of these detail issues.
> >
> > Thank you so much for your time.
> >
> >
> > "Daniel Reib [MSFT]" wrote:
> >
> >> When a regular subscription is created the name of the file must be
> >> passed
> >> in. The Fileshare extension provides no means for which to add
> >> information
> >> dynamically to the name of the generated file. If you used a data driven
> >> subscription you could create the name in the SQL query and have that
> >> passed
> >> in as the file name. If the query is smart enough it could generate the
> >> filename with the desired parameter embedded. However, you said DD
> >> subscriptions were not an option, and the Fileshare extension does not
> >> have
> >> the ability to do this.
> >>
> >> --
> >> -Daniel
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "IST" <IST@.discussions.microsoft.com> wrote in message
> >> news:AB59B9E4-9395-4D37-BD0A-43C02CCFAD1B@.microsoft.com...
> >> > These reports are scheduled to be delivered as Excel attachments.
> >> > Please
> >> > elaborate on your response to this.
> >> >
> >> >
> >> > "Daniel Reib [MSFT]" wrote:
> >> >
> >> >> Without using Data Driven subscription, the fileshare delivery
> >> >> extension
> >> >> does not have any means to accomplish this.
> >> >>
> >> >> --
> >> >> -Daniel
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >>
> >> >>
> >> >> "IST" <IST@.discussions.microsoft.com> wrote in message
> >> >> news:5A358241-7057-4E4C-B5B3-F7E5C32D3A2C@.microsoft.com...
> >> >> > We created a sales history report for a client that has one prompt,
> >> >> > Sales
> >> >> > Region, and when they schedule the report all the filenames of the
> >> >> > Excel
> >> >> > attachments are the same. They are all the report name.
> >> >> >
> >> >> > They would like each file to have a name like
> >> >> > "SalesRegion-SelectedRegion.xls". And possibly the dynamic month
> >> >> > generated
> >> >> > in the report from a user defined function.
> >> >> >
> >> >> > Is there anyway to include the prompted Sales Region in the
> >> >> > filename?
> >> >> >
> >> >> > On a related issue I have noticed people discussing doing Data
> >> >> > Driven
> >> >> > subscriptions, but even though the report data source credentials
> >> >> > are
> >> >> > saved
> >> >> > in the report server it is not an option for them.
> >> >> >
> >> >> > Any help would be greatly appreciated.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Maybe I am a little confused here. Do you have 77 subscriptions? How are
you generating each file?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"IST" <IST@.discussions.microsoft.com> wrote in message
news:B5F42661-0335-4D38-85D1-DCCD47882BC0@.microsoft.com...
> Hello Daniel, That is exactly it. They have the standard version of RS.
> Do we have any options using either Email or Fileshare to get a Excel
> filename other than the default report name? i.e. Instead of all 77 email
> having the same attachment filename "Sales Region.xls" it could be even
> the a
> combination of the report name and report parameter "@.SALESREGION"
> resulting
> in "Sales Region - North East.xls".
> Thanks.
> "Daniel Reib [MSFT]" wrote:
>> Data driven subscriptions are not supported in the standard edition of
>> RS.
>> Is it possible that he has that?
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "IST" <IST@.discussions.microsoft.com> wrote in message
>> news:956BB75C-B82C-46E6-AA5E-91AEDDD7B0CB@.microsoft.com...
>> > Thanks for your assistance Daniel. So there is no way to override the
>> > "report name" as the attachment name in a standard scheduled report
>> > regardless of the render type.
>> >
>> > Outside of the fact that you should be able to select the attachment
>> > name
>> > in
>> > the report manager just like you do the subject when scheduling a
>> > report.
>> > i.e. "@.ReportName was executed at @.ExecutionTime"
>> >
>> > The sales manager at our client location refuses to open each Excel
>> > spreadsheet attachement in his email to find the sales region and then
>> > select
>> > "Save As" with this name for each of their 77 sales regions every
>> > month.
>> >
>> > I can't blame them. This is supposed to be automated.
>> >
>> > Do you have any suggestions to make this work as an automated solution?
>> >
>> > If this has to be done via Data Driven Subscriptions, do you have any
>> > information about why a report that has stored credential would not
>> > have
>> > the
>> > option of data driven subscription? If that works, could you please
>> > give
>> > me
>> > an example to use for giving these managers their reports with
>> > Region/Month
>> > filenames?
>> >
>> > I have been using RS since the first public beta. It is an amazing
>> > product.
>> > We are just struggling with a few of these detail issues.
>> >
>> > Thank you so much for your time.
>> >
>> >
>> > "Daniel Reib [MSFT]" wrote:
>> >
>> >> When a regular subscription is created the name of the file must be
>> >> passed
>> >> in. The Fileshare extension provides no means for which to add
>> >> information
>> >> dynamically to the name of the generated file. If you used a data
>> >> driven
>> >> subscription you could create the name in the SQL query and have that
>> >> passed
>> >> in as the file name. If the query is smart enough it could generate
>> >> the
>> >> filename with the desired parameter embedded. However, you said DD
>> >> subscriptions were not an option, and the Fileshare extension does not
>> >> have
>> >> the ability to do this.
>> >>
>> >> --
>> >> -Daniel
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "IST" <IST@.discussions.microsoft.com> wrote in message
>> >> news:AB59B9E4-9395-4D37-BD0A-43C02CCFAD1B@.microsoft.com...
>> >> > These reports are scheduled to be delivered as Excel attachments.
>> >> > Please
>> >> > elaborate on your response to this.
>> >> >
>> >> >
>> >> > "Daniel Reib [MSFT]" wrote:
>> >> >
>> >> >> Without using Data Driven subscription, the fileshare delivery
>> >> >> extension
>> >> >> does not have any means to accomplish this.
>> >> >>
>> >> >> --
>> >> >> -Daniel
>> >> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> >> rights.
>> >> >>
>> >> >>
>> >> >> "IST" <IST@.discussions.microsoft.com> wrote in message
>> >> >> news:5A358241-7057-4E4C-B5B3-F7E5C32D3A2C@.microsoft.com...
>> >> >> > We created a sales history report for a client that has one
>> >> >> > prompt,
>> >> >> > Sales
>> >> >> > Region, and when they schedule the report all the filenames of
>> >> >> > the
>> >> >> > Excel
>> >> >> > attachments are the same. They are all the report name.
>> >> >> >
>> >> >> > They would like each file to have a name like
>> >> >> > "SalesRegion-SelectedRegion.xls". And possibly the dynamic month
>> >> >> > generated
>> >> >> > in the report from a user defined function.
>> >> >> >
>> >> >> > Is there anyway to include the prompted Sales Region in the
>> >> >> > filename?
>> >> >> >
>> >> >> > On a related issue I have noticed people discussing doing Data
>> >> >> > Driven
>> >> >> > subscriptions, but even though the report data source credentials
>> >> >> > are
>> >> >> > saved
>> >> >> > in the report server it is not an option for them.
>> >> >> >
>> >> >> > Any help would be greatly appreciated.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||For the purposes of this question there are two reports involved. One for
Sales Regions and another for Product Regions. Each report has a parameter
for its respective region.
For Sales Region (14 regions) and Product Regions (63 regions) each have a
subscription setup to send as an email.
They originally created 77 reports covering each of the regions and a
subscription to each. Then management decided to make a few modifications to
the report format. Then we were faced with modifying 77 reports anytime
changes were needed. Each report had a unique name so that was the name of
the Excel Attachment.
We created the two reports with parameters so when changes were needed to
the format there were only two reports that needed to be modified. But now
that each region comes from the same report the Excel attachment name is the
same for all reports.
They simply want to setup the subscription to email each region with an
attachment name descriptive of the report. i.e. Sales Region - Southwest.xls
These reports use dynamic dates so the report is always based on the
previous complete month. It would be even better if the email attachment
would have the name "Sales Region - Southwest - 1204.xls" adding the month
year to the attachment name.
Do you have any idea how we could accomplish this? Would it even be
possible to set the email Excel attachment name if we create a custom
application? Is attachment name something that is exposed with the Reporting
Services API?
Thanks.
"Daniel Reib [MSFT]" wrote:
> Maybe I am a little confused here. Do you have 77 subscriptions? How are
> you generating each file?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "IST" <IST@.discussions.microsoft.com> wrote in message
> news:B5F42661-0335-4D38-85D1-DCCD47882BC0@.microsoft.com...
> > Hello Daniel, That is exactly it. They have the standard version of RS.
> >
> > Do we have any options using either Email or Fileshare to get a Excel
> > filename other than the default report name? i.e. Instead of all 77 email
> > having the same attachment filename "Sales Region.xls" it could be even
> > the a
> > combination of the report name and report parameter "@.SALESREGION"
> > resulting
> > in "Sales Region - North East.xls".
> >
> > Thanks.
> >
> > "Daniel Reib [MSFT]" wrote:
> >
> >> Data driven subscriptions are not supported in the standard edition of
> >> RS.
> >> Is it possible that he has that?
> >>
> >> --
> >> -Daniel
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "IST" <IST@.discussions.microsoft.com> wrote in message
> >> news:956BB75C-B82C-46E6-AA5E-91AEDDD7B0CB@.microsoft.com...
> >> > Thanks for your assistance Daniel. So there is no way to override the
> >> > "report name" as the attachment name in a standard scheduled report
> >> > regardless of the render type.
> >> >
> >> > Outside of the fact that you should be able to select the attachment
> >> > name
> >> > in
> >> > the report manager just like you do the subject when scheduling a
> >> > report.
> >> > i.e. "@.ReportName was executed at @.ExecutionTime"
> >> >
> >> > The sales manager at our client location refuses to open each Excel
> >> > spreadsheet attachement in his email to find the sales region and then
> >> > select
> >> > "Save As" with this name for each of their 77 sales regions every
> >> > month.
> >> >
> >> > I can't blame them. This is supposed to be automated.
> >> >
> >> > Do you have any suggestions to make this work as an automated solution?
> >> >
> >> > If this has to be done via Data Driven Subscriptions, do you have any
> >> > information about why a report that has stored credential would not
> >> > have
> >> > the
> >> > option of data driven subscription? If that works, could you please
> >> > give
> >> > me
> >> > an example to use for giving these managers their reports with
> >> > Region/Month
> >> > filenames?
> >> >
> >> > I have been using RS since the first public beta. It is an amazing
> >> > product.
> >> > We are just struggling with a few of these detail issues.
> >> >
> >> > Thank you so much for your time.
> >> >
> >> >
> >> > "Daniel Reib [MSFT]" wrote:
> >> >
> >> >> When a regular subscription is created the name of the file must be
> >> >> passed
> >> >> in. The Fileshare extension provides no means for which to add
> >> >> information
> >> >> dynamically to the name of the generated file. If you used a data
> >> >> driven
> >> >> subscription you could create the name in the SQL query and have that
> >> >> passed
> >> >> in as the file name. If the query is smart enough it could generate
> >> >> the
> >> >> filename with the desired parameter embedded. However, you said DD
> >> >> subscriptions were not an option, and the Fileshare extension does not
> >> >> have
> >> >> the ability to do this.
> >> >>
> >> >> --
> >> >> -Daniel
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >>
> >> >>
> >> >> "IST" <IST@.discussions.microsoft.com> wrote in message
> >> >> news:AB59B9E4-9395-4D37-BD0A-43C02CCFAD1B@.microsoft.com...
> >> >> > These reports are scheduled to be delivered as Excel attachments.
> >> >> > Please
> >> >> > elaborate on your response to this.
> >> >> >
> >> >> >
> >> >> > "Daniel Reib [MSFT]" wrote:
> >> >> >
> >> >> >> Without using Data Driven subscription, the fileshare delivery
> >> >> >> extension
> >> >> >> does not have any means to accomplish this.
> >> >> >>
> >> >> >> --
> >> >> >> -Daniel
> >> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> >> rights.
> >> >> >>
> >> >> >>
> >> >> >> "IST" <IST@.discussions.microsoft.com> wrote in message
> >> >> >> news:5A358241-7057-4E4C-B5B3-F7E5C32D3A2C@.microsoft.com...
> >> >> >> > We created a sales history report for a client that has one
> >> >> >> > prompt,
> >> >> >> > Sales
> >> >> >> > Region, and when they schedule the report all the filenames of
> >> >> >> > the
> >> >> >> > Excel
> >> >> >> > attachments are the same. They are all the report name.
> >> >> >> >
> >> >> >> > They would like each file to have a name like
> >> >> >> > "SalesRegion-SelectedRegion.xls". And possibly the dynamic month
> >> >> >> > generated
> >> >> >> > in the report from a user defined function.
> >> >> >> >
> >> >> >> > Is there anyway to include the prompted Sales Region in the
> >> >> >> > filename?
> >> >> >> >
> >> >> >> > On a related issue I have noticed people discussing doing Data
> >> >> >> > Driven
> >> >> >> > subscriptions, but even though the report data source credentials
> >> >> >> > are
> >> >> >> > saved
> >> >> >> > in the report server it is not an option for them.
> >> >> >> >
> >> >> >> > Any help would be greatly appreciated.
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Ok, I have a better idea what is going on now. Unfortunately I don't have
good news. The FileShare Delivery Extension does not have anyway to get
report parameters into the name of the file. It can't do this because the
Delivery API do not give it this information, so you would not be able to
even write your own extension to do this. The only option you have with
Standard is to create the 77 subscriptions, giving each subscription a
unique filename. (You could create 77 linked reports and have each report
have a single subscription. This would make managing them a little easier
since the descriptions of the subscriptions are not helpful)
The best option would be to use DataDriven subscriptions, but that would
require you purchasing the Enterprise Edition.
I hope that helps.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"IST" <IST@.discussions.microsoft.com> wrote in message
news:BE830564-D7BC-4373-90AF-3A55B75E66E4@.microsoft.com...
> For the purposes of this question there are two reports involved. One for
> Sales Regions and another for Product Regions. Each report has a
> parameter
> for its respective region.
> For Sales Region (14 regions) and Product Regions (63 regions) each have a
> subscription setup to send as an email.
> They originally created 77 reports covering each of the regions and a
> subscription to each. Then management decided to make a few modifications
> to
> the report format. Then we were faced with modifying 77 reports anytime
> changes were needed. Each report had a unique name so that was the name
> of
> the Excel Attachment.
> We created the two reports with parameters so when changes were needed to
> the format there were only two reports that needed to be modified. But
> now
> that each region comes from the same report the Excel attachment name is
> the
> same for all reports.
> They simply want to setup the subscription to email each region with an
> attachment name descriptive of the report. i.e. Sales Region -
> Southwest.xls
> These reports use dynamic dates so the report is always based on the
> previous complete month. It would be even better if the email attachment
> would have the name "Sales Region - Southwest - 1204.xls" adding the month
> year to the attachment name.
> Do you have any idea how we could accomplish this? Would it even be
> possible to set the email Excel attachment name if we create a custom
> application? Is attachment name something that is exposed with the
> Reporting
> Services API?
> Thanks.
>
> "Daniel Reib [MSFT]" wrote:
>> Maybe I am a little confused here. Do you have 77 subscriptions? How
>> are
>> you generating each file?
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "IST" <IST@.discussions.microsoft.com> wrote in message
>> news:B5F42661-0335-4D38-85D1-DCCD47882BC0@.microsoft.com...
>> > Hello Daniel, That is exactly it. They have the standard version of
>> > RS.
>> >
>> > Do we have any options using either Email or Fileshare to get a Excel
>> > filename other than the default report name? i.e. Instead of all 77
>> > email
>> > having the same attachment filename "Sales Region.xls" it could be even
>> > the a
>> > combination of the report name and report parameter "@.SALESREGION"
>> > resulting
>> > in "Sales Region - North East.xls".
>> >
>> > Thanks.
>> >
>> > "Daniel Reib [MSFT]" wrote:
>> >
>> >> Data driven subscriptions are not supported in the standard edition of
>> >> RS.
>> >> Is it possible that he has that?
>> >>
>> >> --
>> >> -Daniel
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "IST" <IST@.discussions.microsoft.com> wrote in message
>> >> news:956BB75C-B82C-46E6-AA5E-91AEDDD7B0CB@.microsoft.com...
>> >> > Thanks for your assistance Daniel. So there is no way to override
>> >> > the
>> >> > "report name" as the attachment name in a standard scheduled report
>> >> > regardless of the render type.
>> >> >
>> >> > Outside of the fact that you should be able to select the attachment
>> >> > name
>> >> > in
>> >> > the report manager just like you do the subject when scheduling a
>> >> > report.
>> >> > i.e. "@.ReportName was executed at @.ExecutionTime"
>> >> >
>> >> > The sales manager at our client location refuses to open each Excel
>> >> > spreadsheet attachement in his email to find the sales region and
>> >> > then
>> >> > select
>> >> > "Save As" with this name for each of their 77 sales regions every
>> >> > month.
>> >> >
>> >> > I can't blame them. This is supposed to be automated.
>> >> >
>> >> > Do you have any suggestions to make this work as an automated
>> >> > solution?
>> >> >
>> >> > If this has to be done via Data Driven Subscriptions, do you have
>> >> > any
>> >> > information about why a report that has stored credential would not
>> >> > have
>> >> > the
>> >> > option of data driven subscription? If that works, could you please
>> >> > give
>> >> > me
>> >> > an example to use for giving these managers their reports with
>> >> > Region/Month
>> >> > filenames?
>> >> >
>> >> > I have been using RS since the first public beta. It is an amazing
>> >> > product.
>> >> > We are just struggling with a few of these detail issues.
>> >> >
>> >> > Thank you so much for your time.
>> >> >
>> >> >
>> >> > "Daniel Reib [MSFT]" wrote:
>> >> >
>> >> >> When a regular subscription is created the name of the file must be
>> >> >> passed
>> >> >> in. The Fileshare extension provides no means for which to add
>> >> >> information
>> >> >> dynamically to the name of the generated file. If you used a data
>> >> >> driven
>> >> >> subscription you could create the name in the SQL query and have
>> >> >> that
>> >> >> passed
>> >> >> in as the file name. If the query is smart enough it could
>> >> >> generate
>> >> >> the
>> >> >> filename with the desired parameter embedded. However, you said DD
>> >> >> subscriptions were not an option, and the Fileshare extension does
>> >> >> not
>> >> >> have
>> >> >> the ability to do this.
>> >> >>
>> >> >> --
>> >> >> -Daniel
>> >> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> >> rights.
>> >> >>
>> >> >>
>> >> >> "IST" <IST@.discussions.microsoft.com> wrote in message
>> >> >> news:AB59B9E4-9395-4D37-BD0A-43C02CCFAD1B@.microsoft.com...
>> >> >> > These reports are scheduled to be delivered as Excel attachments.
>> >> >> > Please
>> >> >> > elaborate on your response to this.
>> >> >> >
>> >> >> >
>> >> >> > "Daniel Reib [MSFT]" wrote:
>> >> >> >
>> >> >> >> Without using Data Driven subscription, the fileshare delivery
>> >> >> >> extension
>> >> >> >> does not have any means to accomplish this.
>> >> >> >>
>> >> >> >> --
>> >> >> >> -Daniel
>> >> >> >> This posting is provided "AS IS" with no warranties, and confers
>> >> >> >> no
>> >> >> >> rights.
>> >> >> >>
>> >> >> >>
>> >> >> >> "IST" <IST@.discussions.microsoft.com> wrote in message
>> >> >> >> news:5A358241-7057-4E4C-B5B3-F7E5C32D3A2C@.microsoft.com...
>> >> >> >> > We created a sales history report for a client that has one
>> >> >> >> > prompt,
>> >> >> >> > Sales
>> >> >> >> > Region, and when they schedule the report all the filenames of
>> >> >> >> > the
>> >> >> >> > Excel
>> >> >> >> > attachments are the same. They are all the report name.
>> >> >> >> >
>> >> >> >> > They would like each file to have a name like
>> >> >> >> > "SalesRegion-SelectedRegion.xls". And possibly the dynamic
>> >> >> >> > month
>> >> >> >> > generated
>> >> >> >> > in the report from a user defined function.
>> >> >> >> >
>> >> >> >> > Is there anyway to include the prompted Sales Region in the
>> >> >> >> > filename?
>> >> >> >> >
>> >> >> >> > On a related issue I have noticed people discussing doing Data
>> >> >> >> > Driven
>> >> >> >> > subscriptions, but even though the report data source
>> >> >> >> > credentials
>> >> >> >> > are
>> >> >> >> > saved
>> >> >> >> > in the report server it is not an option for them.
>> >> >> >> >
>> >> >> >> > Any help would be greatly appreciated.
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||I appreciate your assistance. Thanks.
"Daniel Reib [MSFT]" wrote:
> Ok, I have a better idea what is going on now. Unfortunately I don't have
> good news. The FileShare Delivery Extension does not have anyway to get
> report parameters into the name of the file. It can't do this because the
> Delivery API do not give it this information, so you would not be able to
> even write your own extension to do this. The only option you have with
> Standard is to create the 77 subscriptions, giving each subscription a
> unique filename. (You could create 77 linked reports and have each report
> have a single subscription. This would make managing them a little easier
> since the descriptions of the subscriptions are not helpful)
> The best option would be to use DataDriven subscriptions, but that would
> require you purchasing the Enterprise Edition.
> I hope that helps.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "IST" <IST@.discussions.microsoft.com> wrote in message
> news:BE830564-D7BC-4373-90AF-3A55B75E66E4@.microsoft.com...
> > For the purposes of this question there are two reports involved. One for
> > Sales Regions and another for Product Regions. Each report has a
> > parameter
> > for its respective region.
> >
> > For Sales Region (14 regions) and Product Regions (63 regions) each have a
> > subscription setup to send as an email.
> >
> > They originally created 77 reports covering each of the regions and a
> > subscription to each. Then management decided to make a few modifications
> > to
> > the report format. Then we were faced with modifying 77 reports anytime
> > changes were needed. Each report had a unique name so that was the name
> > of
> > the Excel Attachment.
> >
> > We created the two reports with parameters so when changes were needed to
> > the format there were only two reports that needed to be modified. But
> > now
> > that each region comes from the same report the Excel attachment name is
> > the
> > same for all reports.
> >
> > They simply want to setup the subscription to email each region with an
> > attachment name descriptive of the report. i.e. Sales Region -
> > Southwest.xls
> > These reports use dynamic dates so the report is always based on the
> > previous complete month. It would be even better if the email attachment
> > would have the name "Sales Region - Southwest - 1204.xls" adding the month
> > year to the attachment name.
> >
> > Do you have any idea how we could accomplish this? Would it even be
> > possible to set the email Excel attachment name if we create a custom
> > application? Is attachment name something that is exposed with the
> > Reporting
> > Services API?
> >
> > Thanks.
> >
> >
> > "Daniel Reib [MSFT]" wrote:
> >
> >> Maybe I am a little confused here. Do you have 77 subscriptions? How
> >> are
> >> you generating each file?
> >>
> >> --
> >> -Daniel
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "IST" <IST@.discussions.microsoft.com> wrote in message
> >> news:B5F42661-0335-4D38-85D1-DCCD47882BC0@.microsoft.com...
> >> > Hello Daniel, That is exactly it. They have the standard version of
> >> > RS.
> >> >
> >> > Do we have any options using either Email or Fileshare to get a Excel
> >> > filename other than the default report name? i.e. Instead of all 77
> >> > email
> >> > having the same attachment filename "Sales Region.xls" it could be even
> >> > the a
> >> > combination of the report name and report parameter "@.SALESREGION"
> >> > resulting
> >> > in "Sales Region - North East.xls".
> >> >
> >> > Thanks.
> >> >
> >> > "Daniel Reib [MSFT]" wrote:
> >> >
> >> >> Data driven subscriptions are not supported in the standard edition of
> >> >> RS.
> >> >> Is it possible that he has that?
> >> >>
> >> >> --
> >> >> -Daniel
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >>
> >> >>
> >> >> "IST" <IST@.discussions.microsoft.com> wrote in message
> >> >> news:956BB75C-B82C-46E6-AA5E-91AEDDD7B0CB@.microsoft.com...
> >> >> > Thanks for your assistance Daniel. So there is no way to override
> >> >> > the
> >> >> > "report name" as the attachment name in a standard scheduled report
> >> >> > regardless of the render type.
> >> >> >
> >> >> > Outside of the fact that you should be able to select the attachment
> >> >> > name
> >> >> > in
> >> >> > the report manager just like you do the subject when scheduling a
> >> >> > report.
> >> >> > i.e. "@.ReportName was executed at @.ExecutionTime"
> >> >> >
> >> >> > The sales manager at our client location refuses to open each Excel
> >> >> > spreadsheet attachement in his email to find the sales region and
> >> >> > then
> >> >> > select
> >> >> > "Save As" with this name for each of their 77 sales regions every
> >> >> > month.
> >> >> >
> >> >> > I can't blame them. This is supposed to be automated.
> >> >> >
> >> >> > Do you have any suggestions to make this work as an automated
> >> >> > solution?
> >> >> >
> >> >> > If this has to be done via Data Driven Subscriptions, do you have
> >> >> > any
> >> >> > information about why a report that has stored credential would not
> >> >> > have
> >> >> > the
> >> >> > option of data driven subscription? If that works, could you please
> >> >> > give
> >> >> > me
> >> >> > an example to use for giving these managers their reports with
> >> >> > Region/Month
> >> >> > filenames?
> >> >> >
> >> >> > I have been using RS since the first public beta. It is an amazing
> >> >> > product.
> >> >> > We are just struggling with a few of these detail issues.
> >> >> >
> >> >> > Thank you so much for your time.
> >> >> >
> >> >> >
> >> >> > "Daniel Reib [MSFT]" wrote:
> >> >> >
> >> >> >> When a regular subscription is created the name of the file must be
> >> >> >> passed
> >> >> >> in. The Fileshare extension provides no means for which to add
> >> >> >> information
> >> >> >> dynamically to the name of the generated file. If you used a data
> >> >> >> driven
> >> >> >> subscription you could create the name in the SQL query and have
> >> >> >> that
> >> >> >> passed
> >> >> >> in as the file name. If the query is smart enough it could
> >> >> >> generate
> >> >> >> the
> >> >> >> filename with the desired parameter embedded. However, you said DD
> >> >> >> subscriptions were not an option, and the Fileshare extension does
> >> >> >> not
> >> >> >> have
> >> >> >> the ability to do this.
> >> >> >>
> >> >> >> --
> >> >> >> -Daniel
> >> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> >> rights.
> >> >> >>
> >> >> >>
> >> >> >> "IST" <IST@.discussions.microsoft.com> wrote in message
> >> >> >> news:AB59B9E4-9395-4D37-BD0A-43C02CCFAD1B@.microsoft.com...
> >> >> >> > These reports are scheduled to be delivered as Excel attachments.
> >> >> >> > Please
> >> >> >> > elaborate on your response to this.
> >> >> >> >
> >> >> >> >
> >> >> >> > "Daniel Reib [MSFT]" wrote:
> >> >> >> >
> >> >> >> >> Without using Data Driven subscription, the fileshare delivery
> >> >> >> >> extension
> >> >> >> >> does not have any means to accomplish this.
> >> >> >> >>
> >> >> >> >> --
> >> >> >> >> -Daniel
> >> >> >> >> This posting is provided "AS IS" with no warranties, and confers
> >> >> >> >> no
> >> >> >> >> rights.
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> "IST" <IST@.discussions.microsoft.com> wrote in message
> >> >> >> >> news:5A358241-7057-4E4C-B5B3-F7E5C32D3A2C@.microsoft.com...
> >> >> >> >> > We created a sales history report for a client that has one
> >> >> >> >> > prompt,
> >> >> >> >> > Sales
> >> >> >> >> > Region, and when they schedule the report all the filenames of
> >> >> >> >> > the
> >> >> >> >> > Excel
> >> >> >> >> > attachments are the same. They are all the report name.
> >> >> >> >> >
> >> >> >> >> > They would like each file to have a name like
> >> >> >> >> > "SalesRegion-SelectedRegion.xls". And possibly the dynamic
> >> >> >> >> > month
> >> >> >> >> > generated
> >> >> >> >> > in the report from a user defined function.
> >> >> >> >> >
> >> >> >> >> > Is there anyway to include the prompted Sales Region in the
> >> >> >> >> > filename?
> >> >> >> >> >
> >> >> >> >> > On a related issue I have noticed people discussing doing Data
> >> >> >> >> > Driven
> >> >> >> >> > subscriptions, but even though the report data source
> >> >> >> >> > credentials
> >> >> >> >> > are
> >> >> >> >> > saved
> >> >> >> >> > in the report server it is not an option for them.
> >> >> >> >> >
> >> >> >> >> > Any help would be greatly appreciated.
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

dynamic excel destination depend on dataflow data

I created a data flow with complaicated SQL. There is "type" field in the output column.

I would like to created excel files for each "type" value

E.g. If there is 3 "type" values (A, B, C), I would like to create 3 excel files to store type A, type B, and type C data respectively.

Since the number of possibe value of "type" field is various, how can I create the xls destination dynamic and move the correct type to the corresponding excel file?

The conditional split has fixed conditions, it is not suitable for by dynamic number of value

For Loop condition is not a good choice because I need to run the complicated SQL for many time.

Thanks.

Put all data in one place, but with the additional type column. Then drive a loop to extract each type of data into a file. This assumes that each types file has the same format, and would require two data flows, the latter of which would be inside the loop.

I would store the list of unique types in advance, perhaps by multicasting the preparatory flow just prior to writing the full file, and then getting distinct type values, use the Sort component.

Your second data flow would just be source, conditional split acting as a filter then the destination. The split filter would be based on typecolumn == @.typevariable. @.typevariable would be set from the loop enumerator, it is type value for that iteration. I would also use that variable in an expression on the destination connection to give a type specific filename.

Dynamic database in Web Service

I have a question about changing the database source dynamically in a Web Service. I have created a Web Service for a Crystal Report using VS .NET 2003. I use Crystal Reports Web Viewer to display the report in an ASP.NET page. My database is an Access database and I have a copy of the report source database for each unique user to the Web page. I read the post on changing the database dynamically but it seems to be changed on the Crystal Reports viewer and not in the Crystal report which would be done in the Web Service. I have tried setting debug stops in the code behind module in the Web Service but the debugger does not seem to reach the breakpoints. Can you offer any advice on modifying the database connections dynamically in the Web Service?I have a similar problem. I can set the viewer db properties when i declare a web service but i cant use report engine object model. Can you tell me how you have accomplished that|||I found it fairly easy to bind to a single database. I merely opened the Crystal Report in Visual Studio .NET. In the Report Design view, I right click on the Database Fields in the Field Explorer and Select "Set Location". I defined an ODBC connection to the database and bind the report to that. Turning the report into a Web Services was easy as well. I added the report to a project that I was using to define Web Services. Then right click on the project and select Publish the report as a Web Service. Visual Studio does all the rest. Unforunately all of this is a design/build time and I haven't figured out how to change the database during runtime.

Hope this helps you.|||thanks for the reply. However that is not my problem. I want to be able to export these reports as well. Exporting code only works from report engine object model and you cant use it from client application if your reports are published as web service. That is my complication. I can set parameters, db changes at runtime when i do web services. Still thanks for the reply|||I'm sorry. I guess I did not understand the question about the the report engine object model. I also am not sure what you are referring to on exporting the reports. I haven't done anything beyond the defaults that you get when Visual Studio creates the Web Service except to connect it to the Crystal Reports Web Viewer in the ASPX pages. What did you mean when you said you can set parameters, db changes at runtime through web services? My main question is how to specify a different database file(Access database) at runtime.|||This is how u do it with web services and a strored procedure as your data source. If you use tables as data source. loop through all the tables and set the propetries of the tables(uid,pwd,dbname, servername)
Hope this helps.
Exporting questions is i have to export the reports in crystal to excel, pdf, word format.That can rbe done using viewer object model and i have to do it in web services but i dont know how to modify the engine in the web service code. I have the same problem you have. U cant debug any code in web services

Dim mytablelogoninfos As New CrystalDecisions.Shared.TableLogOnInfos
Dim mytablelogoninfo As New CrystalDecisions.Shared.TableLogOnInfo
Dim myconnectioninfo As New CrystalDecisions.Shared.ConnectionInfo

With myconnectioninfo
.UserID = "uid"
.ServerName = "servername"
.Password = "pwd"
.DatabaseName = "dbname"
End With

mytablelogoninfo.TableName = "cspAppointmentsReport;1"
With mytablelogoninfo.ConnectionInfo
.UserID = "uid"
.Password = "pwd"
End With
mytablelogoninfo.ConnectionInfo = myconnectioninfo
mytablelogoninfos.Add(mytablelogoninfo)
CrystalReportViewer1.LogOnInfo = mytablelogoninfos
CrystalReportViewer1.ReportSource = New localhost.AppointmentSubReportService|||Now I understand what you mean by exporting and no I haven't tried anything like that yet. Sorry I don't think I can help you.

Thanks for the sample code. I will see if I can make it work for what I'm trying to do. I appreciate the help.

Good luck solving your problem.

Dynamic data set parameters during report run time

I need to pass the out put from a stored procedure as an input parameter to another stored procedure. I created a data set for each stored procedure. Fron the second data set parameter tab, I added a parameter to refer to the field from the first data set.

I get the error Fiels can not be used in query parameter expressions.

I tried two simple queries instead of stored procedures with first query feeding the second query. I get the same error. Is there any other other way to accomplish this?

Thank You.

Hello,

Are you trying to have a report parameter that is populated from one stored procedure and the data in the report is populated from another stored procedure?

Jarret

Dynamic columns

Hi,
Can a column be created dynamically in a table...
For example the user selects start date and enddate and i want to create
columns from startdate to enddate...
Thanks,
Chandra.Chandra,
The simple answer for tables is "No".
What you should be using is a matrix, the columns are dynamic on this.
Chris
CCP wrote:
> Hi,
> Can a column be created dynamically in a table...
> For example the user selects start date and enddate and i want to
> create columns from startdate to enddate...
> Thanks,
> Chandra.

dynamic (xml text fields) for update sproc

hi folks
anyone have a link or sproc for doing (simple table) updates where the field
list for the update is dynamically created from the inbound XMLtext (could
either validate against an XMLSchema or sysColumns)?
for instance (this is out of Pubs):
CREATE PROCEDURE dbo.sp_update_employee
@.xmltext ntext
AS
-- sample
-- '<update><Employee emp_id="GHT50241M" lname="Thomas II"
fname="Larry"/></update>'
DECLARE @.hDoc int
exec sp_xml_preparedocument @.hDoc OUTPUT,@.xmltext
UPDATE Employee
SET
Employee.fname = XMLEmployee.fname,
Employee.lname = XMLEmployee.lname
FROM OPENXML(@.hDoc, 'update/Employee')
WITH Employee XMLEmployee
WHERE Employee.emp_id = XMLEmployee.emp_id
EXEC sp_xml_removedocument @.hDoc
--SELECT * from Employee WHERE emp_id='GHT50241M' FOR XML AUTO, elements
GO
I'd like to have the updating happen only to those elements/fields existing
in the XML. (In a time crunch, or I'd start writing my own.)
Rob
A couple of options:
Check out SqlXml updategrams, it will do this for you automatically.
Probably the easiest way to do with OpenXml is shred the document into edge
table format and then build up the sql yourself.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad