bind variables / parameter queries

Hi,

I'm writing an Access pass-through query against a SQL server backend and I need some advice on passing parameters. Currently I use vba to substitute the literal values for the parameters prior to passing the query to SQL Server. However, I am going through a loop thousands of times with different literals for these parameters which causes the server's cache to fill up. In Oracle, there is a way to use bind variables for the parameters so that only one copy of the query is cached.

Does anyone know how I can do this in SQL Server?

For instance, I have 20,000 employees and I'm pulling info by SS#:

Select * from EmpTable where SS_number = [SSN]

Is there a way I can pass this query to SQL Server and then pass the value of [SSN] as I loop through the dataset?

Thanks.write a stored procedure, and instead of calling the database engine 20,000 times, just call it once and pass it a list of 20,000 numbers

come to think of it, where would you get 20,000 numbers? sounds like you might want to look for a JOIN solution|||SQL Server actually goes you one better, in that its ODBC drivers will automagically parameterize a query for you (unless you get really creative in modifying the query).

As Rudy pointed out though, if you have more than 20 iterations from a given client, you really ought to be thinking about a JOIN based solution... Doing that kind of thing on that scale one row at a time is WAY too much work for me!

-PatP|||Thanks, guys. I'll get write access to the backend and write a stored proc.

I'll have to read up on how to pass values to the proc (I'm guessing it's like a function).

Thanks again.|||How about this?:

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open "DSN=PKRebate2001", "sa", ""
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cnn
.CommandText = "sp_UpdateCustomerUnique"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@.ImportMonth").Value = IM
.Execute
End With
Set cmd = Nothing
Set cnn = Nothing|||If you are looking to get automatic parameterization, that VBA code is conceptually good.

-PatP

Between date selection

Hi,

I have the following situation.

I want to create reports with a start date parameter and an end date parameter

In my query i use "where date between @.startdate and @.enddate"

i configure my parameters so i get a nice calendar when i generate the report.

the problem is when i select for example a date starting 1 april 2007 and enddate 10 april 2007,

the result in my report gives me only the data from 1 until 9 april and not until 10.

in my database the date is stored as a date time (10/04/2007 17:25:30).

Any suggestion how i can solve my problem?

Greetings

Vinnie

Hello Vinnie,

The problem is that your database stores the time with your date and when you select a date in the date picker, it doesn't have a tme, so anything after midnight gets excluded. Try this as your where clause:

where date >= @.startdate and date < dateadd(d, 1, @.enddate)

Hope this helps.

Jarret

|||

Hi Vinnie,

When I come across this I normally use a conversion in my query:

where convert(varchar(25),date,101) between @.startdate and @.enddate

The conversion eliminates the time from the database field and will pull back all rows with the date portion between the start and end dates.

Simone

|||

Do you have a date picker control on your reporting website, so that instead of the user having to manually input the date in the correct format, they are given a control to pick the date ranges?

(Like datetimepicker control in C#)

Please let me know as that is what I need to do.

|||

Yes I do. You need to change the data type of the parameter in the Report to datetime. Go to "Report" then "Report Parameters" Select the parameter from the list on the left and change the data type to DateTime. This should automatically give the date control.

Simone

Best way to use listbox selection in WHERE ... IN (...) clause?

I need to use the list of items from a multiselect listbox in a parameter of a query's IN clause.
Example:
I assemble the list from the listbox and set the paramter value so @.CityList = 'London','Paris','Rome' ... etc.
A sample SQL would be:
SELECT * FROM Customers
WHERE City IN (@.CityList)
However, the SQL will not work. It seems the whole string is put inanother set of single quotes by the compiler so it's treated as onestring literal instead of a list.
Is there a way around this?

The returned value should be like this
@.CityList = ('London','Paris','Rome')
in order to be working
regards

|||Try these links:

Arrays and Lists in SQL Server
Passing a list/array to SQL Server

|||Hi,
You can build a query string using something like below...
DECLARE @.SQL NVARCHAR(4000)
SELECT @.SQL ='SELECT * FROM Customers WHERE City IN (' + @.CityList + ')'
Then run the @.SQL query using
EXEC (@.SQL)

Eralper
http://www.kodyaz.com


|||

eralper wrote:

You can build a query string using something like below...

DECLARE @.SQL NVARCHAR(4000)
SELECT @.SQL ='SELECT * FROM Customers WHERE City IN (' + @.CityList + ')'

Then run the @.SQL query using

EXEC (@.SQL)

Eralper, in the link I provided (Arrays and Lists in SQL Server) that is identified as one of the solutions to stay away from. I do not recommend that method.

Best way to make a report multi-cultural

We wanted to add a language parameter to a report and allow the user to
output the report to multiple languages. We did this using three
techniques:
Static resource strings:
We created a custom assembly with resource files. We have one property and
one method. Each report has a textbox as it's first item that passes the
report parameter and sets the culture property. Each textbox then uses the
method and passes in a stringID. We configured the component to use an
instance in the report. I believe this will hurt scalability, to what
degree, uncertain, and will need to test. Looking into ways to set a
property on the report and use that instead so that the component method
could be static and shared across all report instances.
Strings from queries:
We used a Translation web service found on CodeProject to access BabelFish
and perform translations. We plan on using dual columns in the db, but for
current proof of concept this seemed like an easy approach. We may plan on
using this in the future for text fields input from the user where they do
not want to translate. We are not sure on this item yet. Either we add a
drop down and they can enter the text in multiple languages, we translate at
time of data input, we translate at time of report generation.
Strings from queries (incomplete):
We need to modify the source data to accomodate multiple languages, and add
support to our stored procedures for this. This will eliminate or greatly
reduce the dependency on BabelFish which will speed up the report generation
and scale.
I'd appreciate feedback, or what others have done to accomplish the same
feature.Rather than use a hidden textbox to set the property, used the following:
<Code>Protected Overrides Sub OnInit()
cust.SetLanguage(Report.Parameters!Language.Value)
End Sub</Code>
"Steve Munson" <smunson@.clearwire.net> wrote in message
news:eBlPycWFGHA.3984@.TK2MSFTNGP14.phx.gbl...
> We wanted to add a language parameter to a report and allow the user to
> output the report to multiple languages. We did this using three
> techniques:
> Static resource strings:
> We created a custom assembly with resource files. We have one property
> and one method. Each report has a textbox as it's first item that passes
> the report parameter and sets the culture property. Each textbox then
> uses the method and passes in a stringID. We configured the component to
> use an instance in the report. I believe this will hurt scalability, to
> what degree, uncertain, and will need to test. Looking into ways to set a
> property on the report and use that instead so that the component method
> could be static and shared across all report instances.
> Strings from queries:
> We used a Translation web service found on CodeProject to access BabelFish
> and perform translations. We plan on using dual columns in the db, but
> for current proof of concept this seemed like an easy approach. We may
> plan on using this in the future for text fields input from the user where
> they do not want to translate. We are not sure on this item yet. Either
> we add a drop down and they can enter the text in multiple languages, we
> translate at time of data input, we translate at time of report
> generation.
> Strings from queries (incomplete):
> We need to modify the source data to accomodate multiple languages, and
> add support to our stored procedures for this. This will eliminate or
> greatly reduce the dependency on BabelFish which will speed up the report
> generation and scale.
> I'd appreciate feedback, or what others have done to accomplish the same
> feature.
>|||I am not understanding how you did this exactly .. I am trying to get this
going too. I added custom code which is of course NOT the way to go. I am
trying to change over to a class. I am not following this that you wrote. I
have never set up resource files and I am very new to that. I have written
custom code for MRS and used that. But I need something that can be shared
across all report instances as you say and the method you suggest will work.
I just need the steps to create the resource files and class. Thanks.
"Steve Munson" wrote:
> Rather than use a hidden textbox to set the property, used the following:
> <Code>Protected Overrides Sub OnInit()
> cust.SetLanguage(Report.Parameters!Language.Value)
> End Sub</Code>
>
> "Steve Munson" <smunson@.clearwire.net> wrote in message
> news:eBlPycWFGHA.3984@.TK2MSFTNGP14.phx.gbl...
> > We wanted to add a language parameter to a report and allow the user to
> > output the report to multiple languages. We did this using three
> > techniques:
> >
> > Static resource strings:
> > We created a custom assembly with resource files. We have one property
> > and one method. Each report has a textbox as it's first item that passes
> > the report parameter and sets the culture property. Each textbox then
> > uses the method and passes in a stringID. We configured the component to
> > use an instance in the report. I believe this will hurt scalability, to
> > what degree, uncertain, and will need to test. Looking into ways to set a
> > property on the report and use that instead so that the component method
> > could be static and shared across all report instances.
> >
> > Strings from queries:
> > We used a Translation web service found on CodeProject to access BabelFish
> > and perform translations. We plan on using dual columns in the db, but
> > for current proof of concept this seemed like an easy approach. We may
> > plan on using this in the future for text fields input from the user where
> > they do not want to translate. We are not sure on this item yet. Either
> > we add a drop down and they can enter the text in multiple languages, we
> > translate at time of data input, we translate at time of report
> > generation.
> >
> > Strings from queries (incomplete):
> > We need to modify the source data to accomodate multiple languages, and
> > add support to our stored procedures for this. This will eliminate or
> > greatly reduce the dependency on BabelFish which will speed up the report
> > generation and scale.
> >
> > I'd appreciate feedback, or what others have done to accomplish the same
> > feature.
> >
>
>|||I have a set of power point slides and a sample project. In the sample I
add Spanish to one of the RS sample reports.
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:4A51D2B7-4FBF-4D32-8CD0-34C598DBA7DE@.microsoft.com...
>I am not understanding how you did this exactly .. I am trying to get this
> going too. I added custom code which is of course NOT the way to go. I
> am
> trying to change over to a class. I am not following this that you wrote.
> I
> have never set up resource files and I am very new to that. I have
> written
> custom code for MRS and used that. But I need something that can be
> shared
> across all report instances as you say and the method you suggest will
> work.
> I just need the steps to create the resource files and class. Thanks.
> "Steve Munson" wrote:
>> Rather than use a hidden textbox to set the property, used the following:
>> <Code>Protected Overrides Sub OnInit()
>> cust.SetLanguage(Report.Parameters!Language.Value)
>> End Sub</Code>
>>
>> "Steve Munson" <smunson@.clearwire.net> wrote in message
>> news:eBlPycWFGHA.3984@.TK2MSFTNGP14.phx.gbl...
>> > We wanted to add a language parameter to a report and allow the user to
>> > output the report to multiple languages. We did this using three
>> > techniques:
>> >
>> > Static resource strings:
>> > We created a custom assembly with resource files. We have one property
>> > and one method. Each report has a textbox as it's first item that
>> > passes
>> > the report parameter and sets the culture property. Each textbox then
>> > uses the method and passes in a stringID. We configured the component
>> > to
>> > use an instance in the report. I believe this will hurt scalability,
>> > to
>> > what degree, uncertain, and will need to test. Looking into ways to
>> > set a
>> > property on the report and use that instead so that the component
>> > method
>> > could be static and shared across all report instances.
>> >
>> > Strings from queries:
>> > We used a Translation web service found on CodeProject to access
>> > BabelFish
>> > and perform translations. We plan on using dual columns in the db, but
>> > for current proof of concept this seemed like an easy approach. We may
>> > plan on using this in the future for text fields input from the user
>> > where
>> > they do not want to translate. We are not sure on this item yet.
>> > Either
>> > we add a drop down and they can enter the text in multiple languages,
>> > we
>> > translate at time of data input, we translate at time of report
>> > generation.
>> >
>> > Strings from queries (incomplete):
>> > We need to modify the source data to accomodate multiple languages, and
>> > add support to our stored procedures for this. This will eliminate or
>> > greatly reduce the dependency on BabelFish which will speed up the
>> > report
>> > generation and scale.
>> >
>> > I'd appreciate feedback, or what others have done to accomplish the
>> > same
>> > feature.
>> >
>>|||The attachments for source code are too large (200 kb), I'll try and figure
out where I can post it.
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:4A51D2B7-4FBF-4D32-8CD0-34C598DBA7DE@.microsoft.com...
>I am not understanding how you did this exactly .. I am trying to get this
> going too. I added custom code which is of course NOT the way to go. I
> am
> trying to change over to a class. I am not following this that you wrote.
> I
> have never set up resource files and I am very new to that. I have
> written
> custom code for MRS and used that. But I need something that can be
> shared
> across all report instances as you say and the method you suggest will
> work.
> I just need the steps to create the resource files and class. Thanks.
> "Steve Munson" wrote:
>> Rather than use a hidden textbox to set the property, used the following:
>> <Code>Protected Overrides Sub OnInit()
>> cust.SetLanguage(Report.Parameters!Language.Value)
>> End Sub</Code>
>>
>> "Steve Munson" <smunson@.clearwire.net> wrote in message
>> news:eBlPycWFGHA.3984@.TK2MSFTNGP14.phx.gbl...
>> > We wanted to add a language parameter to a report and allow the user to
>> > output the report to multiple languages. We did this using three
>> > techniques:
>> >
>> > Static resource strings:
>> > We created a custom assembly with resource files. We have one property
>> > and one method. Each report has a textbox as it's first item that
>> > passes
>> > the report parameter and sets the culture property. Each textbox then
>> > uses the method and passes in a stringID. We configured the component
>> > to
>> > use an instance in the report. I believe this will hurt scalability,
>> > to
>> > what degree, uncertain, and will need to test. Looking into ways to
>> > set a
>> > property on the report and use that instead so that the component
>> > method
>> > could be static and shared across all report instances.
>> >
>> > Strings from queries:
>> > We used a Translation web service found on CodeProject to access
>> > BabelFish
>> > and perform translations. We plan on using dual columns in the db, but
>> > for current proof of concept this seemed like an easy approach. We may
>> > plan on using this in the future for text fields input from the user
>> > where
>> > they do not want to translate. We are not sure on this item yet.
>> > Either
>> > we add a drop down and they can enter the text in multiple languages,
>> > we
>> > translate at time of data input, we translate at time of report
>> > generation.
>> >
>> > Strings from queries (incomplete):
>> > We need to modify the source data to accomodate multiple languages, and
>> > add support to our stored procedures for this. This will eliminate or
>> > greatly reduce the dependency on BabelFish which will speed up the
>> > report
>> > generation and scale.
>> >
>> > I'd appreciate feedback, or what others have done to accomplish the
>> > same
>> > feature.
>> >
>>|||If you are able to post the example you have ... maybe is someone's blog ...
then please post the location. Thanks.
"Steve MunLeeuw" wrote:
> The attachments for source code are too large (200 kb), I'll try and figure
> out where I can post it.
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:4A51D2B7-4FBF-4D32-8CD0-34C598DBA7DE@.microsoft.com...
> >I am not understanding how you did this exactly .. I am trying to get this
> > going too. I added custom code which is of course NOT the way to go. I
> > am
> > trying to change over to a class. I am not following this that you wrote.
> > I
> > have never set up resource files and I am very new to that. I have
> > written
> > custom code for MRS and used that. But I need something that can be
> > shared
> > across all report instances as you say and the method you suggest will
> > work.
> > I just need the steps to create the resource files and class. Thanks.
> >
> > "Steve Munson" wrote:
> >
> >> Rather than use a hidden textbox to set the property, used the following:
> >> <Code>Protected Overrides Sub OnInit()
> >>
> >> cust.SetLanguage(Report.Parameters!Language.Value)
> >>
> >> End Sub</Code>
> >>
> >>
> >> "Steve Munson" <smunson@.clearwire.net> wrote in message
> >> news:eBlPycWFGHA.3984@.TK2MSFTNGP14.phx.gbl...
> >> > We wanted to add a language parameter to a report and allow the user to
> >> > output the report to multiple languages. We did this using three
> >> > techniques:
> >> >
> >> > Static resource strings:
> >> > We created a custom assembly with resource files. We have one property
> >> > and one method. Each report has a textbox as it's first item that
> >> > passes
> >> > the report parameter and sets the culture property. Each textbox then
> >> > uses the method and passes in a stringID. We configured the component
> >> > to
> >> > use an instance in the report. I believe this will hurt scalability,
> >> > to
> >> > what degree, uncertain, and will need to test. Looking into ways to
> >> > set a
> >> > property on the report and use that instead so that the component
> >> > method
> >> > could be static and shared across all report instances.
> >> >
> >> > Strings from queries:
> >> > We used a Translation web service found on CodeProject to access
> >> > BabelFish
> >> > and perform translations. We plan on using dual columns in the db, but
> >> > for current proof of concept this seemed like an easy approach. We may
> >> > plan on using this in the future for text fields input from the user
> >> > where
> >> > they do not want to translate. We are not sure on this item yet.
> >> > Either
> >> > we add a drop down and they can enter the text in multiple languages,
> >> > we
> >> > translate at time of data input, we translate at time of report
> >> > generation.
> >> >
> >> > Strings from queries (incomplete):
> >> > We need to modify the source data to accomodate multiple languages, and
> >> > add support to our stored procedures for this. This will eliminate or
> >> > greatly reduce the dependency on BabelFish which will speed up the
> >> > report
> >> > generation and scale.
> >> >
> >> > I'd appreciate feedback, or what others have done to accomplish the
> >> > same
> >> > feature.
> >> >
> >>
> >>
> >>
>
>|||Sorry for the delay, and thanks for the info on the other post with respect
to the custom assembly, preview tab.
http://smunson.jot.com/WikiHome/Reporting+Services
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:0E5DE435-1C2A-4AC8-88A7-6EB22EC20D2C@.microsoft.com...
> If you are able to post the example you have ... maybe is someone's blog
> ...
> then please post the location. Thanks.
> "Steve MunLeeuw" wrote:
>> The attachments for source code are too large (200 kb), I'll try and
>> figure
>> out where I can post it.
>> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> news:4A51D2B7-4FBF-4D32-8CD0-34C598DBA7DE@.microsoft.com...
>> >I am not understanding how you did this exactly .. I am trying to get
>> >this
>> > going too. I added custom code which is of course NOT the way to go.
>> > I
>> > am
>> > trying to change over to a class. I am not following this that you
>> > wrote.
>> > I
>> > have never set up resource files and I am very new to that. I have
>> > written
>> > custom code for MRS and used that. But I need something that can be
>> > shared
>> > across all report instances as you say and the method you suggest will
>> > work.
>> > I just need the steps to create the resource files and class. Thanks.
>> >
>> > "Steve Munson" wrote:
>> >
>> >> Rather than use a hidden textbox to set the property, used the
>> >> following:
>> >> <Code>Protected Overrides Sub OnInit()
>> >>
>> >> cust.SetLanguage(Report.Parameters!Language.Value)
>> >>
>> >> End Sub</Code>
>> >>
>> >>
>> >> "Steve Munson" <smunson@.clearwire.net> wrote in message
>> >> news:eBlPycWFGHA.3984@.TK2MSFTNGP14.phx.gbl...
>> >> > We wanted to add a language parameter to a report and allow the user
>> >> > to
>> >> > output the report to multiple languages. We did this using three
>> >> > techniques:
>> >> >
>> >> > Static resource strings:
>> >> > We created a custom assembly with resource files. We have one
>> >> > property
>> >> > and one method. Each report has a textbox as it's first item that
>> >> > passes
>> >> > the report parameter and sets the culture property. Each textbox
>> >> > then
>> >> > uses the method and passes in a stringID. We configured the
>> >> > component
>> >> > to
>> >> > use an instance in the report. I believe this will hurt
>> >> > scalability,
>> >> > to
>> >> > what degree, uncertain, and will need to test. Looking into ways to
>> >> > set a
>> >> > property on the report and use that instead so that the component
>> >> > method
>> >> > could be static and shared across all report instances.
>> >> >
>> >> > Strings from queries:
>> >> > We used a Translation web service found on CodeProject to access
>> >> > BabelFish
>> >> > and perform translations. We plan on using dual columns in the db,
>> >> > but
>> >> > for current proof of concept this seemed like an easy approach. We
>> >> > may
>> >> > plan on using this in the future for text fields input from the user
>> >> > where
>> >> > they do not want to translate. We are not sure on this item yet.
>> >> > Either
>> >> > we add a drop down and they can enter the text in multiple
>> >> > languages,
>> >> > we
>> >> > translate at time of data input, we translate at time of report
>> >> > generation.
>> >> >
>> >> > Strings from queries (incomplete):
>> >> > We need to modify the source data to accomodate multiple languages,
>> >> > and
>> >> > add support to our stored procedures for this. This will eliminate
>> >> > or
>> >> > greatly reduce the dependency on BabelFish which will speed up the
>> >> > report
>> >> > generation and scale.
>> >> >
>> >> > I'd appreciate feedback, or what others have done to accomplish the
>> >> > same
>> >> > feature.
>> >> >
>> >>
>> >>
>> >>
>>|||Thanks Steve for the link, however I tried to access it and get an error
Insufficient Privileges.
"Steve MunLeeuw" wrote:
> Sorry for the delay, and thanks for the info on the other post with respect
> to the custom assembly, preview tab.
> http://smunson.jot.com/WikiHome/Reporting+Services
>
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:0E5DE435-1C2A-4AC8-88A7-6EB22EC20D2C@.microsoft.com...
> > If you are able to post the example you have ... maybe is someone's blog
> > ...
> > then please post the location. Thanks.
> >
> > "Steve MunLeeuw" wrote:
> >
> >> The attachments for source code are too large (200 kb), I'll try and
> >> figure
> >> out where I can post it.
> >>
> >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> >> news:4A51D2B7-4FBF-4D32-8CD0-34C598DBA7DE@.microsoft.com...
> >> >I am not understanding how you did this exactly .. I am trying to get
> >> >this
> >> > going too. I added custom code which is of course NOT the way to go.
> >> > I
> >> > am
> >> > trying to change over to a class. I am not following this that you
> >> > wrote.
> >> > I
> >> > have never set up resource files and I am very new to that. I have
> >> > written
> >> > custom code for MRS and used that. But I need something that can be
> >> > shared
> >> > across all report instances as you say and the method you suggest will
> >> > work.
> >> > I just need the steps to create the resource files and class. Thanks.
> >> >
> >> > "Steve Munson" wrote:
> >> >
> >> >> Rather than use a hidden textbox to set the property, used the
> >> >> following:
> >> >> <Code>Protected Overrides Sub OnInit()
> >> >>
> >> >> cust.SetLanguage(Report.Parameters!Language.Value)
> >> >>
> >> >> End Sub</Code>
> >> >>
> >> >>
> >> >> "Steve Munson" <smunson@.clearwire.net> wrote in message
> >> >> news:eBlPycWFGHA.3984@.TK2MSFTNGP14.phx.gbl...
> >> >> > We wanted to add a language parameter to a report and allow the user
> >> >> > to
> >> >> > output the report to multiple languages. We did this using three
> >> >> > techniques:
> >> >> >
> >> >> > Static resource strings:
> >> >> > We created a custom assembly with resource files. We have one
> >> >> > property
> >> >> > and one method. Each report has a textbox as it's first item that
> >> >> > passes
> >> >> > the report parameter and sets the culture property. Each textbox
> >> >> > then
> >> >> > uses the method and passes in a stringID. We configured the
> >> >> > component
> >> >> > to
> >> >> > use an instance in the report. I believe this will hurt
> >> >> > scalability,
> >> >> > to
> >> >> > what degree, uncertain, and will need to test. Looking into ways to
> >> >> > set a
> >> >> > property on the report and use that instead so that the component
> >> >> > method
> >> >> > could be static and shared across all report instances.
> >> >> >
> >> >> > Strings from queries:
> >> >> > We used a Translation web service found on CodeProject to access
> >> >> > BabelFish
> >> >> > and perform translations. We plan on using dual columns in the db,
> >> >> > but
> >> >> > for current proof of concept this seemed like an easy approach. We
> >> >> > may
> >> >> > plan on using this in the future for text fields input from the user
> >> >> > where
> >> >> > they do not want to translate. We are not sure on this item yet.
> >> >> > Either
> >> >> > we add a drop down and they can enter the text in multiple
> >> >> > languages,
> >> >> > we
> >> >> > translate at time of data input, we translate at time of report
> >> >> > generation.
> >> >> >
> >> >> > Strings from queries (incomplete):
> >> >> > We need to modify the source data to accomodate multiple languages,
> >> >> > and
> >> >> > add support to our stored procedures for this. This will eliminate
> >> >> > or
> >> >> > greatly reduce the dependency on BabelFish which will speed up the
> >> >> > report
> >> >> > generation and scale.
> >> >> >
> >> >> > I'd appreciate feedback, or what others have done to accomplish the
> >> >> > same
> >> >> > feature.
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Can you try the following link, and if that doesn't work I can email them.
http://smunson.jot.com/Reporting+Services
The two files are at the bottom.
SSRS 05 multicultural parameterized reports.ppt
SSRS 05 multicultural parameterized reports.zip
Any feedback on the code or ppt is appreciated.
Steve MunLeeuw
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:C77162ED-AD12-4956-B458-31DA2EFA25B1@.microsoft.com...
> Thanks Steve for the link, however I tried to access it and get an error
> Insufficient Privileges.
>
> "Steve MunLeeuw" wrote:
>> Sorry for the delay, and thanks for the info on the other post with
>> respect
>> to the custom assembly, preview tab.
>> http://smunson.jot.com/WikiHome/Reporting+Services
>>
>> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> news:0E5DE435-1C2A-4AC8-88A7-6EB22EC20D2C@.microsoft.com...
>> > If you are able to post the example you have ... maybe is someone's
>> > blog
>> > ...
>> > then please post the location. Thanks.
>> >
>> > "Steve MunLeeuw" wrote:
>> >
>> >> The attachments for source code are too large (200 kb), I'll try and
>> >> figure
>> >> out where I can post it.
>> >>
>> >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> >> news:4A51D2B7-4FBF-4D32-8CD0-34C598DBA7DE@.microsoft.com...
>> >> >I am not understanding how you did this exactly .. I am trying to get
>> >> >this
>> >> > going too. I added custom code which is of course NOT the way to
>> >> > go.
>> >> > I
>> >> > am
>> >> > trying to change over to a class. I am not following this that you
>> >> > wrote.
>> >> > I
>> >> > have never set up resource files and I am very new to that. I
>> >> > have
>> >> > written
>> >> > custom code for MRS and used that. But I need something that can be
>> >> > shared
>> >> > across all report instances as you say and the method you suggest
>> >> > will
>> >> > work.
>> >> > I just need the steps to create the resource files and class.
>> >> > Thanks.
>> >> >
>> >> > "Steve Munson" wrote:
>> >> >
>> >> >> Rather than use a hidden textbox to set the property, used the
>> >> >> following:
>> >> >> <Code>Protected Overrides Sub OnInit()
>> >> >>
>> >> >> cust.SetLanguage(Report.Parameters!Language.Value)
>> >> >>
>> >> >> End Sub</Code>
>> >> >>
>> >> >>
>> >> >> "Steve Munson" <smunson@.clearwire.net> wrote in message
>> >> >> news:eBlPycWFGHA.3984@.TK2MSFTNGP14.phx.gbl...
>> >> >> > We wanted to add a language parameter to a report and allow the
>> >> >> > user
>> >> >> > to
>> >> >> > output the report to multiple languages. We did this using three
>> >> >> > techniques:
>> >> >> >
>> >> >> > Static resource strings:
>> >> >> > We created a custom assembly with resource files. We have one
>> >> >> > property
>> >> >> > and one method. Each report has a textbox as it's first item
>> >> >> > that
>> >> >> > passes
>> >> >> > the report parameter and sets the culture property. Each textbox
>> >> >> > then
>> >> >> > uses the method and passes in a stringID. We configured the
>> >> >> > component
>> >> >> > to
>> >> >> > use an instance in the report. I believe this will hurt
>> >> >> > scalability,
>> >> >> > to
>> >> >> > what degree, uncertain, and will need to test. Looking into ways
>> >> >> > to
>> >> >> > set a
>> >> >> > property on the report and use that instead so that the component
>> >> >> > method
>> >> >> > could be static and shared across all report instances.
>> >> >> >
>> >> >> > Strings from queries:
>> >> >> > We used a Translation web service found on CodeProject to access
>> >> >> > BabelFish
>> >> >> > and perform translations. We plan on using dual columns in the
>> >> >> > db,
>> >> >> > but
>> >> >> > for current proof of concept this seemed like an easy approach.
>> >> >> > We
>> >> >> > may
>> >> >> > plan on using this in the future for text fields input from the
>> >> >> > user
>> >> >> > where
>> >> >> > they do not want to translate. We are not sure on this item yet.
>> >> >> > Either
>> >> >> > we add a drop down and they can enter the text in multiple
>> >> >> > languages,
>> >> >> > we
>> >> >> > translate at time of data input, we translate at time of report
>> >> >> > generation.
>> >> >> >
>> >> >> > Strings from queries (incomplete):
>> >> >> > We need to modify the source data to accomodate multiple
>> >> >> > languages,
>> >> >> > and
>> >> >> > add support to our stored procedures for this. This will
>> >> >> > eliminate
>> >> >> > or
>> >> >> > greatly reduce the dependency on BabelFish which will speed up
>> >> >> > the
>> >> >> > report
>> >> >> > generation and scale.
>> >> >> >
>> >> >> > I'd appreciate feedback, or what others have done to accomplish
>> >> >> > the
>> >> >> > same
>> >> >> > feature.
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>