Hello,
I have the following SQL given by report designer in the
SQL pane. I know I have MYSET defined however this SQL
string does not bring anything, if I remove mySet filed it
is working fine.
SELECT myTable.*
FROM myTable
WHERE (DateTimeEntered BETWEEN @.StartDate AND
@.EndDate) AND (mySet = N'"
&System.Environment.GetEnvironmentVariable("MYSET") &"')
What is problem?
Thanks,
Jim.This has to be a query expression. Try:
="SELECT myTable.* FROM myTable WHERE (DateTimeEntered BETWEEN @.StartDate
AND @.EndDate) AND (mySet = N'" &
System.Environment.GetEnvironmentVariable("MYSET") & "')"
You'll need to first type in the static SQL query (in order to populate the
fields list in fields window), run it, and then replace it with the above
query expression.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JIM.H." <anonymous@.discussions.microsoft.com> wrote in message
news:2c50601c46934$5bd8b080$a401280a@.phx.gbl...
> Hello,
> I have the following SQL given by report designer in the
> SQL pane. I know I have MYSET defined however this SQL
> string does not bring anything, if I remove mySet filed it
> is working fine.
> SELECT myTable.*
> FROM myTable
> WHERE (DateTimeEntered BETWEEN @.StartDate AND
> @.EndDate) AND (mySet = N'"
> &System.Environment.GetEnvironmentVariable("MYSET") &"')
> What is problem?
> Thanks,
> Jim.
>|||Ravi, you need to tell me what you mean by query
expression. I am completely new and did not get any
training on this system yet. I run my query without mySet
part, it runs fine, and I copy-paste your string to SQL
pane and I get unable to parse query text message. Can you
give me a link that explains this case.
>--Original Message--
>This has to be a query expression. Try:
>="SELECT myTable.* FROM myTable WHERE (DateTimeEntered
BETWEEN @.StartDate
>AND @.EndDate) AND (mySet = N'" &
>System.Environment.GetEnvironmentVariable("MYSET") & "')"
>You'll need to first type in the static SQL query (in
order to populate the
>fields list in fields window), run it, and then replace
it with the above
>query expression.
>--
>Ravi Mumulla (Microsoft)
>SQL Server Reporting Services
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
message
>news:2c50601c46934$5bd8b080$a401280a@.phx.gbl...
>> Hello,
>> I have the following SQL given by report designer in the
>> SQL pane. I know I have MYSET defined however this SQL
>> string does not bring anything, if I remove mySet filed
it
>> is working fine.
>> SELECT myTable.*
>> FROM myTable
>> WHERE (DateTimeEntered BETWEEN @.StartDate AND
>> @.EndDate) AND (mySet = N'"
>> &System.Environment.GetEnvironmentVariable("MYSET") &"')
>> What is problem?
>> Thanks,
>> Jim.
>
>.
>|||Jim-
Here are the detailed steps:
Step 1. Create the relevant dataset in report designer.
Step 2. In the report designer Data tab, switch to Generic Query Designer
(this is the fourth button from the left after the Dataset dropdown - just
press this button if it isn't in the pressed state.)
Step 3. Type in the following query text: SELECT myTable.* FROM myTable
WHERE (DateTimeEntered BETWEEN @.StartDate AND @.EndDate)
Step 4. Run the query (enter relevant values for @.StartDate AND @.EndDate
when prompted).
Step 5. Switch to report designer Layout tab. This should populate the
fields windows with relevant fields, i.e., you will see the list of fields
in the fields window.
Step 6. Go back to report designer Data tab. Switch to Generic Query
Designer if you're not already there.
Step 7. Type in the following query text (which is a query expression):
="SELECT myTable.* FROM myTable WHERE (DateTimeEntered BETWEEN @.StartDate
AND @.EndDate) AND (mySet = N'" &
System.Environment.GetEnvironmentVariable("MYSET") & "')"
The Run button in the query designer should now be disabled. You will not be
able to execute the query.
Step 8. Switch to report designer Layout tab.
Step 9. You're now ready to use the fields in any of the data-bound controls
(table, matrix, list, chart).
Please let me know if you'd like further clarification.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JIM.H." <anonymous@.discussions.microsoft.com> wrote in message
news:2c25101c46945$74999a60$a501280a@.phx.gbl...
> Ravi, you need to tell me what you mean by query
> expression. I am completely new and did not get any
> training on this system yet. I run my query without mySet
> part, it runs fine, and I copy-paste your string to SQL
> pane and I get unable to parse query text message. Can you
> give me a link that explains this case.
> >--Original Message--
> >This has to be a query expression. Try:
> >
> >="SELECT myTable.* FROM myTable WHERE (DateTimeEntered
> BETWEEN @.StartDate
> >AND @.EndDate) AND (mySet = N'" &
> >System.Environment.GetEnvironmentVariable("MYSET") & "')"
> >
> >You'll need to first type in the static SQL query (in
> order to populate the
> >fields list in fields window), run it, and then replace
> it with the above
> >query expression.
> >
> >--
> >Ravi Mumulla (Microsoft)
> >SQL Server Reporting Services
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2c50601c46934$5bd8b080$a401280a@.phx.gbl...
> >> Hello,
> >> I have the following SQL given by report designer in the
> >> SQL pane. I know I have MYSET defined however this SQL
> >> string does not bring anything, if I remove mySet filed
> it
> >> is working fine.
> >>
> >> SELECT myTable.*
> >> FROM myTable
> >> WHERE (DateTimeEntered BETWEEN @.StartDate AND
> >> @.EndDate) AND (mySet = N'"
> >> &System.Environment.GetEnvironmentVariable("MYSET") &"')
> >>
> >> What is problem?
> >> Thanks,
> >> Jim.
> >>
> >
> >
> >.
> >|||Thanks Ravi. That was my problem I did not know that there
is a generic query designer. I followed your steps run my
report with Debug->Start and got the following.
. An error has occurred during report processing.
(rsProcessingAborted) Get Online Help
o Cannot set the command text for data
set 'MySQLDB'. (rsErrorSettingCommandText) Get Online Help
Error during processing of the CommandText
expression of dataset 'MySQLDB'.
(rsQueryCommandTextProcessingError) Get Online Hel
The report is running if I do not have mySet constraint.
Do you have any suggestions?
>--Original Message--
>Jim-
>Here are the detailed steps:
>Step 1. Create the relevant dataset in report designer.
>Step 2. In the report designer Data tab, switch to
Generic Query Designer
>(this is the fourth button from the left after the
Dataset dropdown - just
>press this button if it isn't in the pressed state.)
>Step 3. Type in the following query text: SELECT
myTable.* FROM myTable
>WHERE (DateTimeEntered BETWEEN @.StartDate AND @.EndDate)
>Step 4. Run the query (enter relevant values for
@.StartDate AND @.EndDate
>when prompted).
>Step 5. Switch to report designer Layout tab. This should
populate the
>fields windows with relevant fields, i.e., you will see
the list of fields
>in the fields window.
>Step 6. Go back to report designer Data tab. Switch to
Generic Query
>Designer if you're not already there.
>Step 7. Type in the following query text (which is a
query expression):
>="SELECT myTable.* FROM myTable WHERE (DateTimeEntered
BETWEEN @.StartDate
>AND @.EndDate) AND (mySet = N'" &
>System.Environment.GetEnvironmentVariable("MYSET") & "')"
>The Run button in the query designer should now be
disabled. You will not be
>able to execute the query.
>Step 8. Switch to report designer Layout tab.
>Step 9. You're now ready to use the fields in any of the
data-bound controls
>(table, matrix, list, chart).
>Please let me know if you'd like further clarification.
>--
>Ravi Mumulla (Microsoft)
>SQL Server Reporting Services
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
message
>news:2c25101c46945$74999a60$a501280a@.phx.gbl...
>> Ravi, you need to tell me what you mean by query
>> expression. I am completely new and did not get any
>> training on this system yet. I run my query without
mySet
>> part, it runs fine, and I copy-paste your string to SQL
>> pane and I get unable to parse query text message. Can
you
>> give me a link that explains this case.
>> >--Original Message--
>> >This has to be a query expression. Try:
>> >
>> >="SELECT myTable.* FROM myTable WHERE (DateTimeEntered
>> BETWEEN @.StartDate
>> >AND @.EndDate) AND (mySet = N'" &
>> >System.Environment.GetEnvironmentVariable("MYSET")
& "')"
>> >
>> >You'll need to first type in the static SQL query (in
>> order to populate the
>> >fields list in fields window), run it, and then replace
>> it with the above
>> >query expression.
>> >
>> >--
>> >Ravi Mumulla (Microsoft)
>> >SQL Server Reporting Services
>> >
>> >This posting is provided "AS IS" with no warranties,
and
>> confers no rights.
>> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:2c50601c46934$5bd8b080$a401280a@.phx.gbl...
>> >> Hello,
>> >> I have the following SQL given by report designer in
the
>> >> SQL pane. I know I have MYSET defined however this
SQL
>> >> string does not bring anything, if I remove mySet
filed
>> it
>> >> is working fine.
>> >>
>> >> SELECT myTable.*
>> >> FROM myTable
>> >> WHERE (DateTimeEntered BETWEEN @.StartDate AND
>> >> @.EndDate) AND (mySet = N'"
>> >> &System.Environment.GetEnvironmentVariable("MYSET")
&"')
>> >>
>> >> What is problem?
>> >> Thanks,
>> >> Jim.
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||What error do you see when you include it in the query expression?
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JIM.H." <anonymous@.discussions.microsoft.com> wrote in message
news:2cc6b01c46999$51fa2fc0$a501280a@.phx.gbl...
> Thanks Ravi. That was my problem I did not know that there
> is a generic query designer. I followed your steps run my
> report with Debug->Start and got the following.
> . An error has occurred during report processing.
> (rsProcessingAborted) Get Online Help
> o Cannot set the command text for data
> set 'MySQLDB'. (rsErrorSettingCommandText) Get Online Help
> Error during processing of the CommandText
> expression of dataset 'MySQLDB'.
> (rsQueryCommandTextProcessingError) Get Online Hel
> The report is running if I do not have mySet constraint.
> Do you have any suggestions?
> >--Original Message--
> >Jim-
> >Here are the detailed steps:
> >
> >Step 1. Create the relevant dataset in report designer.
> >Step 2. In the report designer Data tab, switch to
> Generic Query Designer
> >(this is the fourth button from the left after the
> Dataset dropdown - just
> >press this button if it isn't in the pressed state.)
> >Step 3. Type in the following query text: SELECT
> myTable.* FROM myTable
> >WHERE (DateTimeEntered BETWEEN @.StartDate AND @.EndDate)
> >Step 4. Run the query (enter relevant values for
> @.StartDate AND @.EndDate
> >when prompted).
> >Step 5. Switch to report designer Layout tab. This should
> populate the
> >fields windows with relevant fields, i.e., you will see
> the list of fields
> >in the fields window.
> >Step 6. Go back to report designer Data tab. Switch to
> Generic Query
> >Designer if you're not already there.
> >Step 7. Type in the following query text (which is a
> query expression):
> >="SELECT myTable.* FROM myTable WHERE (DateTimeEntered
> BETWEEN @.StartDate
> >AND @.EndDate) AND (mySet = N'" &
> >System.Environment.GetEnvironmentVariable("MYSET") & "')"
> >The Run button in the query designer should now be
> disabled. You will not be
> >able to execute the query.
> >Step 8. Switch to report designer Layout tab.
> >Step 9. You're now ready to use the fields in any of the
> data-bound controls
> >(table, matrix, list, chart).
> >
> >Please let me know if you'd like further clarification.
> >
> >--
> >Ravi Mumulla (Microsoft)
> >SQL Server Reporting Services
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2c25101c46945$74999a60$a501280a@.phx.gbl...
> >> Ravi, you need to tell me what you mean by query
> >> expression. I am completely new and did not get any
> >> training on this system yet. I run my query without
> mySet
> >> part, it runs fine, and I copy-paste your string to SQL
> >> pane and I get unable to parse query text message. Can
> you
> >> give me a link that explains this case.
> >>
> >> >--Original Message--
> >> >This has to be a query expression. Try:
> >> >
> >> >="SELECT myTable.* FROM myTable WHERE (DateTimeEntered
> >> BETWEEN @.StartDate
> >> >AND @.EndDate) AND (mySet = N'" &
> >> >System.Environment.GetEnvironmentVariable("MYSET")
> & "')"
> >> >
> >> >You'll need to first type in the static SQL query (in
> >> order to populate the
> >> >fields list in fields window), run it, and then replace
> >> it with the above
> >> >query expression.
> >> >
> >> >--
> >> >Ravi Mumulla (Microsoft)
> >> >SQL Server Reporting Services
> >> >
> >> >This posting is provided "AS IS" with no warranties,
> and
> >> confers no rights.
> >> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:2c50601c46934$5bd8b080$a401280a@.phx.gbl...
> >> >> Hello,
> >> >> I have the following SQL given by report designer in
> the
> >> >> SQL pane. I know I have MYSET defined however this
> SQL
> >> >> string does not bring anything, if I remove mySet
> filed
> >> it
> >> >> is working fine.
> >> >>
> >> >> SELECT myTable.*
> >> >> FROM myTable
> >> >> WHERE (DateTimeEntered BETWEEN @.StartDate AND
> >> >> @.EndDate) AND (mySet = N'"
> >> >> &System.Environment.GetEnvironmentVariable("MYSET")
> &"')
> >> >>
> >> >> What is problem?
> >> >> Thanks,
> >> >> Jim.
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Ravi,
Thansk for the reply. The error is listed below in the
text, I get it in the internet explorer, I do not see any
other error around. I have
=System.Environment.GetEnvironmentVariable("MYSET") in one
of the TextBox in the header section and it is giving
error in the internet explorer, but in design view in the
report designer I get MYSET value correct. I migth have
some setting problem with he internet explorer.
Thanks,
Jim.
>--Original Message--
>What error do you see when you include it in the query
expression?
>--
>Ravi Mumulla (Microsoft)
>SQL Server Reporting Services
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
message
>news:2cc6b01c46999$51fa2fc0$a501280a@.phx.gbl...
>> Thanks Ravi. That was my problem I did not know that
there
>> is a generic query designer. I followed your steps run
my
>> report with Debug->Start and got the following.
>> . An error has occurred during report processing.
>> (rsProcessingAborted) Get Online Help
>> o Cannot set the command text for data
>> set 'MySQLDB'. (rsErrorSettingCommandText) Get Online
Help
>> Error during processing of the CommandText
>> expression of dataset 'MySQLDB'.
>> (rsQueryCommandTextProcessingError) Get Online Hel
>> The report is running if I do not have mySet constraint.
>> Do you have any suggestions?
>> >--Original Message--
>> >Jim-
>> >Here are the detailed steps:
>> >
>> >Step 1. Create the relevant dataset in report designer.
>> >Step 2. In the report designer Data tab, switch to
>> Generic Query Designer
>> >(this is the fourth button from the left after the
>> Dataset dropdown - just
>> >press this button if it isn't in the pressed state.)
>> >Step 3. Type in the following query text: SELECT
>> myTable.* FROM myTable
>> >WHERE (DateTimeEntered BETWEEN @.StartDate AND @.EndDate)
>> >Step 4. Run the query (enter relevant values for
>> @.StartDate AND @.EndDate
>> >when prompted).
>> >Step 5. Switch to report designer Layout tab. This
should
>> populate the
>> >fields windows with relevant fields, i.e., you will see
>> the list of fields
>> >in the fields window.
>> >Step 6. Go back to report designer Data tab. Switch to
>> Generic Query
>> >Designer if you're not already there.
>> >Step 7. Type in the following query text (which is a
>> query expression):
>> >="SELECT myTable.* FROM myTable WHERE (DateTimeEntered
>> BETWEEN @.StartDate
>> >AND @.EndDate) AND (mySet = N'" &
>> >System.Environment.GetEnvironmentVariable("MYSET")
& "')"
>> >The Run button in the query designer should now be
>> disabled. You will not be
>> >able to execute the query.
>> >Step 8. Switch to report designer Layout tab.
>> >Step 9. You're now ready to use the fields in any of
the
>> data-bound controls
>> >(table, matrix, list, chart).
>> >
>> >Please let me know if you'd like further clarification.
>> >
>> >--
>> >Ravi Mumulla (Microsoft)
>> >SQL Server Reporting Services
>> >
>> >This posting is provided "AS IS" with no warranties,
and
>> confers no rights.
>> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:2c25101c46945$74999a60$a501280a@.phx.gbl...
>> >> Ravi, you need to tell me what you mean by query
>> >> expression. I am completely new and did not get any
>> >> training on this system yet. I run my query without
>> mySet
>> >> part, it runs fine, and I copy-paste your string to
SQL
>> >> pane and I get unable to parse query text message.
Can
>> you
>> >> give me a link that explains this case.
>> >>
>> >> >--Original Message--
>> >> >This has to be a query expression. Try:
>> >> >
>> >> >="SELECT myTable.* FROM myTable WHERE
(DateTimeEntered
>> >> BETWEEN @.StartDate
>> >> >AND @.EndDate) AND (mySet = N'" &
>> >> >System.Environment.GetEnvironmentVariable("MYSET")
>> & "')"
>> >> >
>> >> >You'll need to first type in the static SQL query
(in
>> >> order to populate the
>> >> >fields list in fields window), run it, and then
replace
>> >> it with the above
>> >> >query expression.
>> >> >
>> >> >--
>> >> >Ravi Mumulla (Microsoft)
>> >> >SQL Server Reporting Services
>> >> >
>> >> >This posting is provided "AS IS" with no warranties,
>> and
>> >> confers no rights.
>> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
wrote in
>> >> message
>> >> >news:2c50601c46934$5bd8b080$a401280a@.phx.gbl...
>> >> >> Hello,
>> >> >> I have the following SQL given by report designer
in
>> the
>> >> >> SQL pane. I know I have MYSET defined however this
>> SQL
>> >> >> string does not bring anything, if I remove mySet
>> filed
>> >> it
>> >> >> is working fine.
>> >> >>
>> >> >> SELECT myTable.*
>> >> >> FROM myTable
>> >> >> WHERE (DateTimeEntered BETWEEN @.StartDate AND
>> >> >> @.EndDate) AND (mySet = N'"
>> >> >> &System.Environment.GetEnvironmentVariable
("MYSET")
>> &"')
>> >> >>
>> >> >> What is problem?
>> >> >> Thanks,
>> >> >> Jim.
>> >> >>
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||This is because the Expression Host is assigned "Execution" permission set,
so when it runs from Report Manager, it will not have access to the
envrionment variables. If you change the permission set for expression host
to "FullTrust" in %PROGRAMFILES%\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\rssrvpolicy.config, it will allow you to read the
environment variables. However, giving the expression host "FullTrust"
privileges is a potential security risk and not recommended. See
"Expressions Used in Reports" topic in
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_extend_install_8j8x.asp
for details.
That said, you'd have to use some other method to figure out the current
user. Did you already consider using global collections, specifically
User!UserID, to get the current user name? Refer to
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
for details.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JIM.H." <anonymous@.discussions.microsoft.com> wrote in message
news:2cb5301c469ae$75bc7ed0$a401280a@.phx.gbl...
> Ravi,
> Thansk for the reply. The error is listed below in the
> text, I get it in the internet explorer, I do not see any
> other error around. I have
> =System.Environment.GetEnvironmentVariable("MYSET") in one
> of the TextBox in the header section and it is giving
> error in the internet explorer, but in design view in the
> report designer I get MYSET value correct. I migth have
> some setting problem with he internet explorer.
> Thanks,
> Jim.
>
> >--Original Message--
> >What error do you see when you include it in the query
> expression?
> >
> >--
> >Ravi Mumulla (Microsoft)
> >SQL Server Reporting Services
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2cc6b01c46999$51fa2fc0$a501280a@.phx.gbl...
> >> Thanks Ravi. That was my problem I did not know that
> there
> >> is a generic query designer. I followed your steps run
> my
> >> report with Debug->Start and got the following.
> >>
> >> . An error has occurred during report processing.
> >> (rsProcessingAborted) Get Online Help
> >> o Cannot set the command text for data
> >> set 'MySQLDB'. (rsErrorSettingCommandText) Get Online
> Help
> >> Error during processing of the CommandText
> >> expression of dataset 'MySQLDB'.
> >> (rsQueryCommandTextProcessingError) Get Online Hel
> >>
> >> The report is running if I do not have mySet constraint.
> >> Do you have any suggestions?
> >>
> >> >--Original Message--
> >> >Jim-
> >> >Here are the detailed steps:
> >> >
> >> >Step 1. Create the relevant dataset in report designer.
> >> >Step 2. In the report designer Data tab, switch to
> >> Generic Query Designer
> >> >(this is the fourth button from the left after the
> >> Dataset dropdown - just
> >> >press this button if it isn't in the pressed state.)
> >> >Step 3. Type in the following query text: SELECT
> >> myTable.* FROM myTable
> >> >WHERE (DateTimeEntered BETWEEN @.StartDate AND @.EndDate)
> >> >Step 4. Run the query (enter relevant values for
> >> @.StartDate AND @.EndDate
> >> >when prompted).
> >> >Step 5. Switch to report designer Layout tab. This
> should
> >> populate the
> >> >fields windows with relevant fields, i.e., you will see
> >> the list of fields
> >> >in the fields window.
> >> >Step 6. Go back to report designer Data tab. Switch to
> >> Generic Query
> >> >Designer if you're not already there.
> >> >Step 7. Type in the following query text (which is a
> >> query expression):
> >> >="SELECT myTable.* FROM myTable WHERE (DateTimeEntered
> >> BETWEEN @.StartDate
> >> >AND @.EndDate) AND (mySet = N'" &
> >> >System.Environment.GetEnvironmentVariable("MYSET")
> & "')"
> >> >The Run button in the query designer should now be
> >> disabled. You will not be
> >> >able to execute the query.
> >> >Step 8. Switch to report designer Layout tab.
> >> >Step 9. You're now ready to use the fields in any of
> the
> >> data-bound controls
> >> >(table, matrix, list, chart).
> >> >
> >> >Please let me know if you'd like further clarification.
> >> >
> >> >--
> >> >Ravi Mumulla (Microsoft)
> >> >SQL Server Reporting Services
> >> >
> >> >This posting is provided "AS IS" with no warranties,
> and
> >> confers no rights.
> >> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:2c25101c46945$74999a60$a501280a@.phx.gbl...
> >> >> Ravi, you need to tell me what you mean by query
> >> >> expression. I am completely new and did not get any
> >> >> training on this system yet. I run my query without
> >> mySet
> >> >> part, it runs fine, and I copy-paste your string to
> SQL
> >> >> pane and I get unable to parse query text message.
> Can
> >> you
> >> >> give me a link that explains this case.
> >> >>
> >> >> >--Original Message--
> >> >> >This has to be a query expression. Try:
> >> >> >
> >> >> >="SELECT myTable.* FROM myTable WHERE
> (DateTimeEntered
> >> >> BETWEEN @.StartDate
> >> >> >AND @.EndDate) AND (mySet = N'" &
> >> >> >System.Environment.GetEnvironmentVariable("MYSET")
> >> & "')"
> >> >> >
> >> >> >You'll need to first type in the static SQL query
> (in
> >> >> order to populate the
> >> >> >fields list in fields window), run it, and then
> replace
> >> >> it with the above
> >> >> >query expression.
> >> >> >
> >> >> >--
> >> >> >Ravi Mumulla (Microsoft)
> >> >> >SQL Server Reporting Services
> >> >> >
> >> >> >This posting is provided "AS IS" with no warranties,
> >> and
> >> >> confers no rights.
> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
> wrote in
> >> >> message
> >> >> >news:2c50601c46934$5bd8b080$a401280a@.phx.gbl...
> >> >> >> Hello,
> >> >> >> I have the following SQL given by report designer
> in
> >> the
> >> >> >> SQL pane. I know I have MYSET defined however this
> >> SQL
> >> >> >> string does not bring anything, if I remove mySet
> >> filed
> >> >> it
> >> >> >> is working fine.
> >> >> >>
> >> >> >> SELECT myTable.*
> >> >> >> FROM myTable
> >> >> >> WHERE (DateTimeEntered BETWEEN @.StartDate AND
> >> >> >> @.EndDate) AND (mySet = N'"
> >> >> >> &System.Environment.GetEnvironmentVariable
> ("MYSET")
> >> &"')
> >> >> >>
> >> >> >> What is problem?
> >> >> >> Thanks,
> >> >> >> Jim.
> >> >> >>
> >> >> >
> >> >> >
> >> >> >.
> >> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Thanks Ravi,
I set PermissionSetName="FullTrust" for
Name="Report_Expressions_Default_Permissions"
This time I do not see anything, not even #err. I still
see my environment variable in Preview screen.
>--Original Message--
>This is because the Expression Host is
assigned "Execution" permission set,
>so when it runs from Report Manager, it will not have
access to the
>envrionment variables. If you change the permission set
for expression host
>to "FullTrust" in %PROGRAMFILES%\Microsoft SQL
Server\MSSQL\Reporting
>Services\ReportServer\rssrvpolicy.config, it will allow
you to read the
>environment variables. However, giving the expression
host "FullTrust"
>privileges is a potential security risk and not
recommended. See
>"Expressions Used in Reports" topic in
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-
us/RSPROG/htm/rsp_prog_extend_install_8j8x.asp
>for details.
>That said, you'd have to use some other method to figure
out the current
>user. Did you already consider using global collections,
specifically
>User!UserID, to get the current user name? Refer to
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-
us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
>for details.
>--
>Ravi Mumulla (Microsoft)
>SQL Server Reporting Services
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
message
>news:2cb5301c469ae$75bc7ed0$a401280a@.phx.gbl...
>> Ravi,
>> Thansk for the reply. The error is listed below in the
>> text, I get it in the internet explorer, I do not see
any
>> other error around. I have
>> =System.Environment.GetEnvironmentVariable("MYSET") in
one
>> of the TextBox in the header section and it is giving
>> error in the internet explorer, but in design view in
the
>> report designer I get MYSET value correct. I migth have
>> some setting problem with he internet explorer.
>> Thanks,
>> Jim.
>>
>> >--Original Message--
>> >What error do you see when you include it in the query
>> expression?
>> >
>> >--
>> >Ravi Mumulla (Microsoft)
>> >SQL Server Reporting Services
>> >
>> >This posting is provided "AS IS" with no warranties,
and
>> confers no rights.
>> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:2cc6b01c46999$51fa2fc0$a501280a@.phx.gbl...
>> >> Thanks Ravi. That was my problem I did not know that
>> there
>> >> is a generic query designer. I followed your steps
run
>> my
>> >> report with Debug->Start and got the following.
>> >>
>> >> . An error has occurred during report processing.
>> >> (rsProcessingAborted) Get Online Help
>> >> o Cannot set the command text for data
>> >> set 'MySQLDB'. (rsErrorSettingCommandText) Get Online
>> Help
>> >> Error during processing of the CommandText
>> >> expression of dataset 'MySQLDB'.
>> >> (rsQueryCommandTextProcessingError) Get Online Hel
>> >>
>> >> The report is running if I do not have mySet
constraint.
>> >> Do you have any suggestions?
>> >>
>> >> >--Original Message--
>> >> >Jim-
>> >> >Here are the detailed steps:
>> >> >
>> >> >Step 1. Create the relevant dataset in report
designer.
>> >> >Step 2. In the report designer Data tab, switch to
>> >> Generic Query Designer
>> >> >(this is the fourth button from the left after the
>> >> Dataset dropdown - just
>> >> >press this button if it isn't in the pressed state.)
>> >> >Step 3. Type in the following query text: SELECT
>> >> myTable.* FROM myTable
>> >> >WHERE (DateTimeEntered BETWEEN @.StartDate AND
@.EndDate)
>> >> >Step 4. Run the query (enter relevant values for
>> >> @.StartDate AND @.EndDate
>> >> >when prompted).
>> >> >Step 5. Switch to report designer Layout tab. This
>> should
>> >> populate the
>> >> >fields windows with relevant fields, i.e., you will
see
>> >> the list of fields
>> >> >in the fields window.
>> >> >Step 6. Go back to report designer Data tab. Switch
to
>> >> Generic Query
>> >> >Designer if you're not already there.
>> >> >Step 7. Type in the following query text (which is a
>> >> query expression):
>> >> >="SELECT myTable.* FROM myTable WHERE
(DateTimeEntered
>> >> BETWEEN @.StartDate
>> >> >AND @.EndDate) AND (mySet = N'" &
>> >> >System.Environment.GetEnvironmentVariable("MYSET")
>> & "')"
>> >> >The Run button in the query designer should now be
>> >> disabled. You will not be
>> >> >able to execute the query.
>> >> >Step 8. Switch to report designer Layout tab.
>> >> >Step 9. You're now ready to use the fields in any of
>> the
>> >> data-bound controls
>> >> >(table, matrix, list, chart).
>> >> >
>> >> >Please let me know if you'd like further
clarification.
>> >> >
>> >> >--
>> >> >Ravi Mumulla (Microsoft)
>> >> >SQL Server Reporting Services
>> >> >
>> >> >This posting is provided "AS IS" with no warranties,
>> and
>> >> confers no rights.
>> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
wrote in
>> >> message
>> >> >news:2c25101c46945$74999a60$a501280a@.phx.gbl...
>> >> >> Ravi, you need to tell me what you mean by query
>> >> >> expression. I am completely new and did not get
any
>> >> >> training on this system yet. I run my query
without
>> >> mySet
>> >> >> part, it runs fine, and I copy-paste your string
to
>> SQL
>> >> >> pane and I get unable to parse query text message.
>> Can
>> >> you
>> >> >> give me a link that explains this case.
>> >> >>
>> >> >> >--Original Message--
>> >> >> >This has to be a query expression. Try:
>> >> >> >
>> >> >> >="SELECT myTable.* FROM myTable WHERE
>> (DateTimeEntered
>> >> >> BETWEEN @.StartDate
>> >> >> >AND @.EndDate) AND (mySet = N'" &
>> >> >> >System.Environment.GetEnvironmentVariable
("MYSET")
>> >> & "')"
>> >> >> >
>> >> >> >You'll need to first type in the static SQL query
>> (in
>> >> >> order to populate the
>> >> >> >fields list in fields window), run it, and then
>> replace
>> >> >> it with the above
>> >> >> >query expression.
>> >> >> >
>> >> >> >--
>> >> >> >Ravi Mumulla (Microsoft)
>> >> >> >SQL Server Reporting Services
>> >> >> >
>> >> >> >This posting is provided "AS IS" with no
warranties,
>> >> and
>> >> >> confers no rights.
>> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
>> wrote in
>> >> >> message
>> >> >> >news:2c50601c46934$5bd8b080$a401280a@.phx.gbl...
>> >> >> >> Hello,
>> >> >> >> I have the following SQL given by report
designer
>> in
>> >> the
>> >> >> >> SQL pane. I know I have MYSET defined however
this
>> >> SQL
>> >> >> >> string does not bring anything, if I remove
mySet
>> >> filed
>> >> >> it
>> >> >> >> is working fine.
>> >> >> >>
>> >> >> >> SELECT myTable.*
>> >> >> >> FROM myTable
>> >> >> >> WHERE (DateTimeEntered BETWEEN @.StartDate
AND
>> >> >> >> @.EndDate) AND (mySet = N'"
>> >> >> >> &System.Environment.GetEnvironmentVariable
>> ("MYSET")
>> >> &"')
>> >> >> >>
>> >> >> >> What is problem?
>> >> >> >> Thanks,
>> >> >> >> Jim.
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >> >.
>> >> >> >
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||I tried it on both Win2k and Win2k3 and it seems to be working with RS 2000
SP1 installed. Can you try it with a new report or on another machine and
let me know?
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JIM.H." <anonymous@.discussions.microsoft.com> wrote in message
news:2ceb701c46a83$7b33d130$a601280a@.phx.gbl...
> Thanks Ravi,
> I set PermissionSetName="FullTrust" for
> Name="Report_Expressions_Default_Permissions"
> This time I do not see anything, not even #err. I still
> see my environment variable in Preview screen.
>
> >--Original Message--
> >This is because the Expression Host is
> assigned "Execution" permission set,
> >so when it runs from Report Manager, it will not have
> access to the
> >envrionment variables. If you change the permission set
> for expression host
> >to "FullTrust" in %PROGRAMFILES%\Microsoft SQL
> Server\MSSQL\Reporting
> >Services\ReportServer\rssrvpolicy.config, it will allow
> you to read the
> >environment variables. However, giving the expression
> host "FullTrust"
> >privileges is a potential security risk and not
> recommended. See
> >"Expressions Used in Reports" topic in
> >http://msdn.microsoft.com/library/default.asp?
> url=/library/en-
> us/RSPROG/htm/rsp_prog_extend_install_8j8x.asp
> >for details.
> >
> >That said, you'd have to use some other method to figure
> out the current
> >user. Did you already consider using global collections,
> specifically
> >User!UserID, to get the current user name? Refer to
> >http://msdn.microsoft.com/library/default.asp?
> url=/library/en-
> us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
> >for details.
> >
> >--
> >Ravi Mumulla (Microsoft)
> >SQL Server Reporting Services
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2cb5301c469ae$75bc7ed0$a401280a@.phx.gbl...
> >> Ravi,
> >> Thansk for the reply. The error is listed below in the
> >> text, I get it in the internet explorer, I do not see
> any
> >> other error around. I have
> >> =System.Environment.GetEnvironmentVariable("MYSET") in
> one
> >> of the TextBox in the header section and it is giving
> >> error in the internet explorer, but in design view in
> the
> >> report designer I get MYSET value correct. I migth have
> >> some setting problem with he internet explorer.
> >> Thanks,
> >> Jim.
> >>
> >>
> >> >--Original Message--
> >> >What error do you see when you include it in the query
> >> expression?
> >> >
> >> >--
> >> >Ravi Mumulla (Microsoft)
> >> >SQL Server Reporting Services
> >> >
> >> >This posting is provided "AS IS" with no warranties,
> and
> >> confers no rights.
> >> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:2cc6b01c46999$51fa2fc0$a501280a@.phx.gbl...
> >> >> Thanks Ravi. That was my problem I did not know that
> >> there
> >> >> is a generic query designer. I followed your steps
> run
> >> my
> >> >> report with Debug->Start and got the following.
> >> >>
> >> >> . An error has occurred during report processing.
> >> >> (rsProcessingAborted) Get Online Help
> >> >> o Cannot set the command text for data
> >> >> set 'MySQLDB'. (rsErrorSettingCommandText) Get Online
> >> Help
> >> >> Error during processing of the CommandText
> >> >> expression of dataset 'MySQLDB'.
> >> >> (rsQueryCommandTextProcessingError) Get Online Hel
> >> >>
> >> >> The report is running if I do not have mySet
> constraint.
> >> >> Do you have any suggestions?
> >> >>
> >> >> >--Original Message--
> >> >> >Jim-
> >> >> >Here are the detailed steps:
> >> >> >
> >> >> >Step 1. Create the relevant dataset in report
> designer.
> >> >> >Step 2. In the report designer Data tab, switch to
> >> >> Generic Query Designer
> >> >> >(this is the fourth button from the left after the
> >> >> Dataset dropdown - just
> >> >> >press this button if it isn't in the pressed state.)
> >> >> >Step 3. Type in the following query text: SELECT
> >> >> myTable.* FROM myTable
> >> >> >WHERE (DateTimeEntered BETWEEN @.StartDate AND
> @.EndDate)
> >> >> >Step 4. Run the query (enter relevant values for
> >> >> @.StartDate AND @.EndDate
> >> >> >when prompted).
> >> >> >Step 5. Switch to report designer Layout tab. This
> >> should
> >> >> populate the
> >> >> >fields windows with relevant fields, i.e., you will
> see
> >> >> the list of fields
> >> >> >in the fields window.
> >> >> >Step 6. Go back to report designer Data tab. Switch
> to
> >> >> Generic Query
> >> >> >Designer if you're not already there.
> >> >> >Step 7. Type in the following query text (which is a
> >> >> query expression):
> >> >> >="SELECT myTable.* FROM myTable WHERE
> (DateTimeEntered
> >> >> BETWEEN @.StartDate
> >> >> >AND @.EndDate) AND (mySet = N'" &
> >> >> >System.Environment.GetEnvironmentVariable("MYSET")
> >> & "')"
> >> >> >The Run button in the query designer should now be
> >> >> disabled. You will not be
> >> >> >able to execute the query.
> >> >> >Step 8. Switch to report designer Layout tab.
> >> >> >Step 9. You're now ready to use the fields in any of
> >> the
> >> >> data-bound controls
> >> >> >(table, matrix, list, chart).
> >> >> >
> >> >> >Please let me know if you'd like further
> clarification.
> >> >> >
> >> >> >--
> >> >> >Ravi Mumulla (Microsoft)
> >> >> >SQL Server Reporting Services
> >> >> >
> >> >> >This posting is provided "AS IS" with no warranties,
> >> and
> >> >> confers no rights.
> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
> wrote in
> >> >> message
> >> >> >news:2c25101c46945$74999a60$a501280a@.phx.gbl...
> >> >> >> Ravi, you need to tell me what you mean by query
> >> >> >> expression. I am completely new and did not get
> any
> >> >> >> training on this system yet. I run my query
> without
> >> >> mySet
> >> >> >> part, it runs fine, and I copy-paste your string
> to
> >> SQL
> >> >> >> pane and I get unable to parse query text message.
> >> Can
> >> >> you
> >> >> >> give me a link that explains this case.
> >> >> >>
> >> >> >> >--Original Message--
> >> >> >> >This has to be a query expression. Try:
> >> >> >> >
> >> >> >> >="SELECT myTable.* FROM myTable WHERE
> >> (DateTimeEntered
> >> >> >> BETWEEN @.StartDate
> >> >> >> >AND @.EndDate) AND (mySet = N'" &
> >> >> >> >System.Environment.GetEnvironmentVariable
> ("MYSET")
> >> >> & "')"
> >> >> >> >
> >> >> >> >You'll need to first type in the static SQL query
> >> (in
> >> >> >> order to populate the
> >> >> >> >fields list in fields window), run it, and then
> >> replace
> >> >> >> it with the above
> >> >> >> >query expression.
> >> >> >> >
> >> >> >> >--
> >> >> >> >Ravi Mumulla (Microsoft)
> >> >> >> >SQL Server Reporting Services
> >> >> >> >
> >> >> >> >This posting is provided "AS IS" with no
> warranties,
> >> >> and
> >> >> >> confers no rights.
> >> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
> >> wrote in
> >> >> >> message
> >> >> >> >news:2c50601c46934$5bd8b080$a401280a@.phx.gbl...
> >> >> >> >> Hello,
> >> >> >> >> I have the following SQL given by report
> designer
> >> in
> >> >> the
> >> >> >> >> SQL pane. I know I have MYSET defined however
> this
> >> >> SQL
> >> >> >> >> string does not bring anything, if I remove
> mySet
> >> >> filed
> >> >> >> it
> >> >> >> >> is working fine.
> >> >> >> >>
> >> >> >> >> SELECT myTable.*
> >> >> >> >> FROM myTable
> >> >> >> >> WHERE (DateTimeEntered BETWEEN @.StartDate
> AND
> >> >> >> >> @.EndDate) AND (mySet = N'"
> >> >> >> >> &System.Environment.GetEnvironmentVariable
> >> ("MYSET")
> >> >> &"')
> >> >> >> >>
> >> >> >> >> What is problem?
> >> >> >> >> Thanks,
> >> >> >> >> Jim.
> >> >> >> >>
> >> >> >> >
> >> >> >> >
> >> >> >> >.
> >> >> >> >
> >> >> >
> >> >> >
> >> >> >.
> >> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Ravi,
We are using Win2003 (I have only one machine that has SQL
and RS installed), I installed SP1 for RS2000 and tried
it, it did not work. I also tried =User!UserID to see if
it is comping up in IE, that works fine. I could not get
environment variable displayed in IE.
Thanks,
Jim.
>--Original Message--
>I tried it on both Win2k and Win2k3 and it seems to be
working with RS 2000
>SP1 installed. Can you try it with a new report or on
another machine and
>let me know?
>--
>Ravi Mumulla (Microsoft)
>SQL Server Reporting Services
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
message
>news:2ceb701c46a83$7b33d130$a601280a@.phx.gbl...
>> Thanks Ravi,
>> I set PermissionSetName="FullTrust" for
>> Name="Report_Expressions_Default_Permissions"
>> This time I do not see anything, not even #err. I still
>> see my environment variable in Preview screen.
>>
>> >--Original Message--
>> >This is because the Expression Host is
>> assigned "Execution" permission set,
>> >so when it runs from Report Manager, it will not have
>> access to the
>> >envrionment variables. If you change the permission set
>> for expression host
>> >to "FullTrust" in %PROGRAMFILES%\Microsoft SQL
>> Server\MSSQL\Reporting
>> >Services\ReportServer\rssrvpolicy.config, it will allow
>> you to read the
>> >environment variables. However, giving the expression
>> host "FullTrust"
>> >privileges is a potential security risk and not
>> recommended. See
>> >"Expressions Used in Reports" topic in
>> >http://msdn.microsoft.com/library/default.asp?
>> url=/library/en-
>> us/RSPROG/htm/rsp_prog_extend_install_8j8x.asp
>> >for details.
>> >
>> >That said, you'd have to use some other method to
figure
>> out the current
>> >user. Did you already consider using global
collections,
>> specifically
>> >User!UserID, to get the current user name? Refer to
>> >http://msdn.microsoft.com/library/default.asp?
>> url=/library/en-
>> us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
>> >for details.
>> >
>> >--
>> >Ravi Mumulla (Microsoft)
>> >SQL Server Reporting Services
>> >
>> >This posting is provided "AS IS" with no warranties,
and
>> confers no rights.
>> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:2cb5301c469ae$75bc7ed0$a401280a@.phx.gbl...
>> >> Ravi,
>> >> Thansk for the reply. The error is listed below in
the
>> >> text, I get it in the internet explorer, I do not see
>> any
>> >> other error around. I have
>> >> =System.Environment.GetEnvironmentVariable("MYSET")
in
>> one
>> >> of the TextBox in the header section and it is giving
>> >> error in the internet explorer, but in design view in
>> the
>> >> report designer I get MYSET value correct. I migth
have
>> >> some setting problem with he internet explorer.
>> >> Thanks,
>> >> Jim.
>> >>
>> >>
>> >> >--Original Message--
>> >> >What error do you see when you include it in the
query
>> >> expression?
>> >> >
>> >> >--
>> >> >Ravi Mumulla (Microsoft)
>> >> >SQL Server Reporting Services
>> >> >
>> >> >This posting is provided "AS IS" with no warranties,
>> and
>> >> confers no rights.
>> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
wrote in
>> >> message
>> >> >news:2cc6b01c46999$51fa2fc0$a501280a@.phx.gbl...
>> >> >> Thanks Ravi. That was my problem I did not know
that
>> >> there
>> >> >> is a generic query designer. I followed your steps
>> run
>> >> my
>> >> >> report with Debug->Start and got the following.
>> >> >>
>> >> >> . An error has occurred during report processing.
>> >> >> (rsProcessingAborted) Get Online Help
>> >> >> o Cannot set the command text for data
>> >> >> set 'MySQLDB'. (rsErrorSettingCommandText) Get
Online
>> >> Help
>> >> >> Error during processing of the CommandText
>> >> >> expression of dataset 'MySQLDB'.
>> >> >> (rsQueryCommandTextProcessingError) Get Online Hel
>> >> >>
>> >> >> The report is running if I do not have mySet
>> constraint.
>> >> >> Do you have any suggestions?
>> >> >>
>> >> >> >--Original Message--
>> >> >> >Jim-
>> >> >> >Here are the detailed steps:
>> >> >> >
>> >> >> >Step 1. Create the relevant dataset in report
>> designer.
>> >> >> >Step 2. In the report designer Data tab, switch
to
>> >> >> Generic Query Designer
>> >> >> >(this is the fourth button from the left after
the
>> >> >> Dataset dropdown - just
>> >> >> >press this button if it isn't in the pressed
state.)
>> >> >> >Step 3. Type in the following query text: SELECT
>> >> >> myTable.* FROM myTable
>> >> >> >WHERE (DateTimeEntered BETWEEN @.StartDate AND
>> @.EndDate)
>> >> >> >Step 4. Run the query (enter relevant values for
>> >> >> @.StartDate AND @.EndDate
>> >> >> >when prompted).
>> >> >> >Step 5. Switch to report designer Layout tab.
This
>> >> should
>> >> >> populate the
>> >> >> >fields windows with relevant fields, i.e., you
will
>> see
>> >> >> the list of fields
>> >> >> >in the fields window.
>> >> >> >Step 6. Go back to report designer Data tab.
Switch
>> to
>> >> >> Generic Query
>> >> >> >Designer if you're not already there.
>> >> >> >Step 7. Type in the following query text (which
is a
>> >> >> query expression):
>> >> >> >="SELECT myTable.* FROM myTable WHERE
>> (DateTimeEntered
>> >> >> BETWEEN @.StartDate
>> >> >> >AND @.EndDate) AND (mySet = N'" &
>> >> >> >System.Environment.GetEnvironmentVariable
("MYSET")
>> >> & "')"
>> >> >> >The Run button in the query designer should now
be
>> >> >> disabled. You will not be
>> >> >> >able to execute the query.
>> >> >> >Step 8. Switch to report designer Layout tab.
>> >> >> >Step 9. You're now ready to use the fields in
any of
>> >> the
>> >> >> data-bound controls
>> >> >> >(table, matrix, list, chart).
>> >> >> >
>> >> >> >Please let me know if you'd like further
>> clarification.
>> >> >> >
>> >> >> >--
>> >> >> >Ravi Mumulla (Microsoft)
>> >> >> >SQL Server Reporting Services
>> >> >> >
>> >> >> >This posting is provided "AS IS" with no
warranties,
>> >> and
>> >> >> confers no rights.
>> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
>> wrote in
>> >> >> message
>> >> >> >news:2c25101c46945$74999a60$a501280a@.phx.gbl...
>> >> >> >> Ravi, you need to tell me what you mean by
query
>> >> >> >> expression. I am completely new and did not get
>> any
>> >> >> >> training on this system yet. I run my query
>> without
>> >> >> mySet
>> >> >> >> part, it runs fine, and I copy-paste your
string
>> to
>> >> SQL
>> >> >> >> pane and I get unable to parse query text
message.
>> >> Can
>> >> >> you
>> >> >> >> give me a link that explains this case.
>> >> >> >>
>> >> >> >> >--Original Message--
>> >> >> >> >This has to be a query expression. Try:
>> >> >> >> >
>> >> >> >> >="SELECT myTable.* FROM myTable WHERE
>> >> (DateTimeEntered
>> >> >> >> BETWEEN @.StartDate
>> >> >> >> >AND @.EndDate) AND (mySet = N'" &
>> >> >> >> >System.Environment.GetEnvironmentVariable
>> ("MYSET")
>> >> >> & "')"
>> >> >> >> >
>> >> >> >> >You'll need to first type in the static SQL
query
>> >> (in
>> >> >> >> order to populate the
>> >> >> >> >fields list in fields window), run it, and
then
>> >> replace
>> >> >> >> it with the above
>> >> >> >> >query expression.
>> >> >> >> >
>> >> >> >> >--
>> >> >> >> >Ravi Mumulla (Microsoft)
>> >> >> >> >SQL Server Reporting Services
>> >> >> >> >
>> >> >> >> >This posting is provided "AS IS" with no
>> warranties,
>> >> >> and
>> >> >> >> confers no rights.
>> >> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
>> >> wrote in
>> >> >> >> message
>> >> >> >> >news:2c50601c46934$5bd8b080
$a401280a@.phx.gbl...
>> >> >> >> >> Hello,
>> >> >> >> >> I have the following SQL given by report
>> designer
>> >> in
>> >> >> the
>> >> >> >> >> SQL pane. I know I have MYSET defined
however
>> this
>> >> >> SQL
>> >> >> >> >> string does not bring anything, if I remove
>> mySet
>> >> >> filed
>> >> >> >> it
>> >> >> >> >> is working fine.
>> >> >> >> >>
>> >> >> >> >> SELECT myTable.*
>> >> >> >> >> FROM myTable
>> >> >> >> >> WHERE (DateTimeEntered BETWEEN
@.StartDate
>> AND
>> >> >> >> >> @.EndDate) AND (mySet = N'"
>> >> >> >> >> &System.Environment.GetEnvironmentVariable
>> >> ("MYSET")
>> >> >> &"')
>> >> >> >> >>
>> >> >> >> >> What is problem?
>> >> >> >> >> Thanks,
>> >> >> >> >> Jim.
>> >> >> >> >>
>> >> >> >> >
>> >> >> >> >
>> >> >> >> >.
>> >> >> >> >
>> >> >> >
>> >> >> >
>> >> >> >.
>> >> >> >
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||If =User!UserID works for you, that would be the recommended approach.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JIM.H." <anonymous@.discussions.microsoft.com> wrote in message
news:2dbc101c46aa1$8c871b40$a401280a@.phx.gbl...
> Ravi,
> We are using Win2003 (I have only one machine that has SQL
> and RS installed), I installed SP1 for RS2000 and tried
> it, it did not work. I also tried =User!UserID to see if
> it is comping up in IE, that works fine. I could not get
> environment variable displayed in IE.
> Thanks,
> Jim.
> >--Original Message--
> >I tried it on both Win2k and Win2k3 and it seems to be
> working with RS 2000
> >SP1 installed. Can you try it with a new report or on
> another machine and
> >let me know?
> >
> >--
> >Ravi Mumulla (Microsoft)
> >SQL Server Reporting Services
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2ceb701c46a83$7b33d130$a601280a@.phx.gbl...
> >> Thanks Ravi,
> >> I set PermissionSetName="FullTrust" for
> >> Name="Report_Expressions_Default_Permissions"
> >> This time I do not see anything, not even #err. I still
> >> see my environment variable in Preview screen.
> >>
> >>
> >> >--Original Message--
> >> >This is because the Expression Host is
> >> assigned "Execution" permission set,
> >> >so when it runs from Report Manager, it will not have
> >> access to the
> >> >envrionment variables. If you change the permission set
> >> for expression host
> >> >to "FullTrust" in %PROGRAMFILES%\Microsoft SQL
> >> Server\MSSQL\Reporting
> >> >Services\ReportServer\rssrvpolicy.config, it will allow
> >> you to read the
> >> >environment variables. However, giving the expression
> >> host "FullTrust"
> >> >privileges is a potential security risk and not
> >> recommended. See
> >> >"Expressions Used in Reports" topic in
> >> >http://msdn.microsoft.com/library/default.asp?
> >> url=/library/en-
> >> us/RSPROG/htm/rsp_prog_extend_install_8j8x.asp
> >> >for details.
> >> >
> >> >That said, you'd have to use some other method to
> figure
> >> out the current
> >> >user. Did you already consider using global
> collections,
> >> specifically
> >> >User!UserID, to get the current user name? Refer to
> >> >http://msdn.microsoft.com/library/default.asp?
> >> url=/library/en-
> >> us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
> >> >for details.
> >> >
> >> >--
> >> >Ravi Mumulla (Microsoft)
> >> >SQL Server Reporting Services
> >> >
> >> >This posting is provided "AS IS" with no warranties,
> and
> >> confers no rights.
> >> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:2cb5301c469ae$75bc7ed0$a401280a@.phx.gbl...
> >> >> Ravi,
> >> >> Thansk for the reply. The error is listed below in
> the
> >> >> text, I get it in the internet explorer, I do not see
> >> any
> >> >> other error around. I have
> >> >> =System.Environment.GetEnvironmentVariable("MYSET")
> in
> >> one
> >> >> of the TextBox in the header section and it is giving
> >> >> error in the internet explorer, but in design view in
> >> the
> >> >> report designer I get MYSET value correct. I migth
> have
> >> >> some setting problem with he internet explorer.
> >> >> Thanks,
> >> >> Jim.
> >> >>
> >> >>
> >> >> >--Original Message--
> >> >> >What error do you see when you include it in the
> query
> >> >> expression?
> >> >> >
> >> >> >--
> >> >> >Ravi Mumulla (Microsoft)
> >> >> >SQL Server Reporting Services
> >> >> >
> >> >> >This posting is provided "AS IS" with no warranties,
> >> and
> >> >> confers no rights.
> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
> wrote in
> >> >> message
> >> >> >news:2cc6b01c46999$51fa2fc0$a501280a@.phx.gbl...
> >> >> >> Thanks Ravi. That was my problem I did not know
> that
> >> >> there
> >> >> >> is a generic query designer. I followed your steps
> >> run
> >> >> my
> >> >> >> report with Debug->Start and got the following.
> >> >> >>
> >> >> >> . An error has occurred during report processing.
> >> >> >> (rsProcessingAborted) Get Online Help
> >> >> >> o Cannot set the command text for data
> >> >> >> set 'MySQLDB'. (rsErrorSettingCommandText) Get
> Online
> >> >> Help
> >> >> >> Error during processing of the CommandText
> >> >> >> expression of dataset 'MySQLDB'.
> >> >> >> (rsQueryCommandTextProcessingError) Get Online Hel
> >> >> >>
> >> >> >> The report is running if I do not have mySet
> >> constraint.
> >> >> >> Do you have any suggestions?
> >> >> >>
> >> >> >> >--Original Message--
> >> >> >> >Jim-
> >> >> >> >Here are the detailed steps:
> >> >> >> >
> >> >> >> >Step 1. Create the relevant dataset in report
> >> designer.
> >> >> >> >Step 2. In the report designer Data tab, switch
> to
> >> >> >> Generic Query Designer
> >> >> >> >(this is the fourth button from the left after
> the
> >> >> >> Dataset dropdown - just
> >> >> >> >press this button if it isn't in the pressed
> state.)
> >> >> >> >Step 3. Type in the following query text: SELECT
> >> >> >> myTable.* FROM myTable
> >> >> >> >WHERE (DateTimeEntered BETWEEN @.StartDate AND
> >> @.EndDate)
> >> >> >> >Step 4. Run the query (enter relevant values for
> >> >> >> @.StartDate AND @.EndDate
> >> >> >> >when prompted).
> >> >> >> >Step 5. Switch to report designer Layout tab.
> This
> >> >> should
> >> >> >> populate the
> >> >> >> >fields windows with relevant fields, i.e., you
> will
> >> see
> >> >> >> the list of fields
> >> >> >> >in the fields window.
> >> >> >> >Step 6. Go back to report designer Data tab.
> Switch
> >> to
> >> >> >> Generic Query
> >> >> >> >Designer if you're not already there.
> >> >> >> >Step 7. Type in the following query text (which
> is a
> >> >> >> query expression):
> >> >> >> >="SELECT myTable.* FROM myTable WHERE
> >> (DateTimeEntered
> >> >> >> BETWEEN @.StartDate
> >> >> >> >AND @.EndDate) AND (mySet = N'" &
> >> >> >> >System.Environment.GetEnvironmentVariable
> ("MYSET")
> >> >> & "')"
> >> >> >> >The Run button in the query designer should now
> be
> >> >> >> disabled. You will not be
> >> >> >> >able to execute the query.
> >> >> >> >Step 8. Switch to report designer Layout tab.
> >> >> >> >Step 9. You're now ready to use the fields in
> any of
> >> >> the
> >> >> >> data-bound controls
> >> >> >> >(table, matrix, list, chart).
> >> >> >> >
> >> >> >> >Please let me know if you'd like further
> >> clarification.
> >> >> >> >
> >> >> >> >--
> >> >> >> >Ravi Mumulla (Microsoft)
> >> >> >> >SQL Server Reporting Services
> >> >> >> >
> >> >> >> >This posting is provided "AS IS" with no
> warranties,
> >> >> and
> >> >> >> confers no rights.
> >> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
> >> wrote in
> >> >> >> message
> >> >> >> >news:2c25101c46945$74999a60$a501280a@.phx.gbl...
> >> >> >> >> Ravi, you need to tell me what you mean by
> query
> >> >> >> >> expression. I am completely new and did not get
> >> any
> >> >> >> >> training on this system yet. I run my query
> >> without
> >> >> >> mySet
> >> >> >> >> part, it runs fine, and I copy-paste your
> string
> >> to
> >> >> SQL
> >> >> >> >> pane and I get unable to parse query text
> message.
> >> >> Can
> >> >> >> you
> >> >> >> >> give me a link that explains this case.
> >> >> >> >>
> >> >> >> >> >--Original Message--
> >> >> >> >> >This has to be a query expression. Try:
> >> >> >> >> >
> >> >> >> >> >="SELECT myTable.* FROM myTable WHERE
> >> >> (DateTimeEntered
> >> >> >> >> BETWEEN @.StartDate
> >> >> >> >> >AND @.EndDate) AND (mySet = N'" &
> >> >> >> >> >System.Environment.GetEnvironmentVariable
> >> ("MYSET")
> >> >> >> & "')"
> >> >> >> >> >
> >> >> >> >> >You'll need to first type in the static SQL
> query
> >> >> (in
> >> >> >> >> order to populate the
> >> >> >> >> >fields list in fields window), run it, and
> then
> >> >> replace
> >> >> >> >> it with the above
> >> >> >> >> >query expression.
> >> >> >> >> >
> >> >> >> >> >--
> >> >> >> >> >Ravi Mumulla (Microsoft)
> >> >> >> >> >SQL Server Reporting Services
> >> >> >> >> >
> >> >> >> >> >This posting is provided "AS IS" with no
> >> warranties,
> >> >> >> and
> >> >> >> >> confers no rights.
> >> >> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
> >> >> wrote in
> >> >> >> >> message
> >> >> >> >> >news:2c50601c46934$5bd8b080
> $a401280a@.phx.gbl...
> >> >> >> >> >> Hello,
> >> >> >> >> >> I have the following SQL given by report
> >> designer
> >> >> in
> >> >> >> the
> >> >> >> >> >> SQL pane. I know I have MYSET defined
> however
> >> this
> >> >> >> SQL
> >> >> >> >> >> string does not bring anything, if I remove
> >> mySet
> >> >> >> filed
> >> >> >> >> it
> >> >> >> >> >> is working fine.
> >> >> >> >> >>
> >> >> >> >> >> SELECT myTable.*
> >> >> >> >> >> FROM myTable
> >> >> >> >> >> WHERE (DateTimeEntered BETWEEN
> @.StartDate
> >> AND
> >> >> >> >> >> @.EndDate) AND (mySet = N'"
> >> >> >> >> >> &System.Environment.GetEnvironmentVariable
> >> >> ("MYSET")
> >> >> >> &"')
> >> >> >> >> >>
> >> >> >> >> >> What is problem?
> >> >> >> >> >> Thanks,
> >> >> >> >> >> Jim.
> >> >> >> >> >>
> >> >> >> >> >
> >> >> >> >> >
> >> >> >> >> >.
> >> >> >> >> >
> >> >> >> >
> >> >> >> >
> >> >> >> >.
> >> >> >> >
> >> >> >
> >> >> >
> >> >> >.
> >> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||I actaully need =User!UserGroupNames, if availabe, not
UserID. I could not get Active directory connection
working, Group name is actaully an environment variable,
that is why I was trying to read it, but if I could list
of group names in another way that would still work for me.
>--Original Message--
>If =User!UserID works for you, that would be the
recommended approach.
>--
>Ravi Mumulla (Microsoft)
>SQL Server Reporting Services
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
message
>news:2dbc101c46aa1$8c871b40$a401280a@.phx.gbl...
>> Ravi,
>> We are using Win2003 (I have only one machine that has
SQL
>> and RS installed), I installed SP1 for RS2000 and tried
>> it, it did not work. I also tried =User!UserID to see if
>> it is comping up in IE, that works fine. I could not get
>> environment variable displayed in IE.
>> Thanks,
>> Jim.
>> >--Original Message--
>> >I tried it on both Win2k and Win2k3 and it seems to be
>> working with RS 2000
>> >SP1 installed. Can you try it with a new report or on
>> another machine and
>> >let me know?
>> >
>> >--
>> >Ravi Mumulla (Microsoft)
>> >SQL Server Reporting Services
>> >
>> >This posting is provided "AS IS" with no warranties,
and
>> confers no rights.
>> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:2ceb701c46a83$7b33d130$a601280a@.phx.gbl...
>> >> Thanks Ravi,
>> >> I set PermissionSetName="FullTrust" for
>> >> Name="Report_Expressions_Default_Permissions"
>> >> This time I do not see anything, not even #err. I
still
>> >> see my environment variable in Preview screen.
>> >>
>> >>
>> >> >--Original Message--
>> >> >This is because the Expression Host is
>> >> assigned "Execution" permission set,
>> >> >so when it runs from Report Manager, it will not
have
>> >> access to the
>> >> >envrionment variables. If you change the permission
set
>> >> for expression host
>> >> >to "FullTrust" in %PROGRAMFILES%\Microsoft SQL
>> >> Server\MSSQL\Reporting
>> >> >Services\ReportServer\rssrvpolicy.config, it will
allow
>> >> you to read the
>> >> >environment variables. However, giving the
expression
>> >> host "FullTrust"
>> >> >privileges is a potential security risk and not
>> >> recommended. See
>> >> >"Expressions Used in Reports" topic in
>> >> >http://msdn.microsoft.com/library/default.asp?
>> >> url=/library/en-
>> >> us/RSPROG/htm/rsp_prog_extend_install_8j8x.asp
>> >> >for details.
>> >> >
>> >> >That said, you'd have to use some other method to
>> figure
>> >> out the current
>> >> >user. Did you already consider using global
>> collections,
>> >> specifically
>> >> >User!UserID, to get the current user name? Refer to
>> >> >http://msdn.microsoft.com/library/default.asp?
>> >> url=/library/en-
>> >> us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
>> >> >for details.
>> >> >
>> >> >--
>> >> >Ravi Mumulla (Microsoft)
>> >> >SQL Server Reporting Services
>> >> >
>> >> >This posting is provided "AS IS" with no warranties,
>> and
>> >> confers no rights.
>> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
wrote in
>> >> message
>> >> >news:2cb5301c469ae$75bc7ed0$a401280a@.phx.gbl...
>> >> >> Ravi,
>> >> >> Thansk for the reply. The error is listed below in
>> the
>> >> >> text, I get it in the internet explorer, I do not
see
>> >> any
>> >> >> other error around. I have
>> >> >> =System.Environment.GetEnvironmentVariable
("MYSET")
>> in
>> >> one
>> >> >> of the TextBox in the header section and it is
giving
>> >> >> error in the internet explorer, but in design
view in
>> >> the
>> >> >> report designer I get MYSET value correct. I migth
>> have
>> >> >> some setting problem with he internet explorer.
>> >> >> Thanks,
>> >> >> Jim.
>> >> >>
>> >> >>
>> >> >> >--Original Message--
>> >> >> >What error do you see when you include it in the
>> query
>> >> >> expression?
>> >> >> >
>> >> >> >--
>> >> >> >Ravi Mumulla (Microsoft)
>> >> >> >SQL Server Reporting Services
>> >> >> >
>> >> >> >This posting is provided "AS IS" with no
warranties,
>> >> and
>> >> >> confers no rights.
>> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
>> wrote in
>> >> >> message
>> >> >> >news:2cc6b01c46999$51fa2fc0$a501280a@.phx.gbl...
>> >> >> >> Thanks Ravi. That was my problem I did not know
>> that
>> >> >> there
>> >> >> >> is a generic query designer. I followed your
steps
>> >> run
>> >> >> my
>> >> >> >> report with Debug->Start and got the following.
>> >> >> >>
>> >> >> >> . An error has occurred during report
processing.
>> >> >> >> (rsProcessingAborted) Get Online Help
>> >> >> >> o Cannot set the command text for data
>> >> >> >> set 'MySQLDB'. (rsErrorSettingCommandText) Get
>> Online
>> >> >> Help
>> >> >> >> Error during processing of the CommandText
>> >> >> >> expression of dataset 'MySQLDB'.
>> >> >> >> (rsQueryCommandTextProcessingError) Get Online
Hel
>> >> >> >>
>> >> >> >> The report is running if I do not have mySet
>> >> constraint.
>> >> >> >> Do you have any suggestions?
>> >> >> >>
>> >> >> >> >--Original Message--
>> >> >> >> >Jim-
>> >> >> >> >Here are the detailed steps:
>> >> >> >> >
>> >> >> >> >Step 1. Create the relevant dataset in report
>> >> designer.
>> >> >> >> >Step 2. In the report designer Data tab,
switch
>> to
>> >> >> >> Generic Query Designer
>> >> >> >> >(this is the fourth button from the left after
>> the
>> >> >> >> Dataset dropdown - just
>> >> >> >> >press this button if it isn't in the pressed
>> state.)
>> >> >> >> >Step 3. Type in the following query text:
SELECT
>> >> >> >> myTable.* FROM myTable
>> >> >> >> >WHERE (DateTimeEntered BETWEEN @.StartDate AND
>> >> @.EndDate)
>> >> >> >> >Step 4. Run the query (enter relevant values
for
>> >> >> >> @.StartDate AND @.EndDate
>> >> >> >> >when prompted).
>> >> >> >> >Step 5. Switch to report designer Layout tab.
>> This
>> >> >> should
>> >> >> >> populate the
>> >> >> >> >fields windows with relevant fields, i.e., you
>> will
>> >> see
>> >> >> >> the list of fields
>> >> >> >> >in the fields window.
>> >> >> >> >Step 6. Go back to report designer Data tab.
>> Switch
>> >> to
>> >> >> >> Generic Query
>> >> >> >> >Designer if you're not already there.
>> >> >> >> >Step 7. Type in the following query text
(which
>> is a
>> >> >> >> query expression):
>> >> >> >> >="SELECT myTable.* FROM myTable WHERE
>> >> (DateTimeEntered
>> >> >> >> BETWEEN @.StartDate
>> >> >> >> >AND @.EndDate) AND (mySet = N'" &
>> >> >> >> >System.Environment.GetEnvironmentVariable
>> ("MYSET")
>> >> >> & "')"
>> >> >> >> >The Run button in the query designer should
now
>> be
>> >> >> >> disabled. You will not be
>> >> >> >> >able to execute the query.
>> >> >> >> >Step 8. Switch to report designer Layout tab.
>> >> >> >> >Step 9. You're now ready to use the fields in
>> any of
>> >> >> the
>> >> >> >> data-bound controls
>> >> >> >> >(table, matrix, list, chart).
>> >> >> >> >
>> >> >> >> >Please let me know if you'd like further
>> >> clarification.
>> >> >> >> >
>> >> >> >> >--
>> >> >> >> >Ravi Mumulla (Microsoft)
>> >> >> >> >SQL Server Reporting Services
>> >> >> >> >
>> >> >> >> >This posting is provided "AS IS" with no
>> warranties,
>> >> >> and
>> >> >> >> confers no rights.
>> >> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
>> >> wrote in
>> >> >> >> message
>> >> >> >> >news:2c25101c46945$74999a60
$a501280a@.phx.gbl...
>> >> >> >> >> Ravi, you need to tell me what you mean by
>> query
>> >> >> >> >> expression. I am completely new and did not
get
>> >> any
>> >> >> >> >> training on this system yet. I run my query
>> >> without
>> >> >> >> mySet
>> >> >> >> >> part, it runs fine, and I copy-paste your
>> string
>> >> to
>> >> >> SQL
>> >> >> >> >> pane and I get unable to parse query text
>> message.
>> >> >> Can
>> >> >> >> you
>> >> >> >> >> give me a link that explains this case.
>> >> >> >> >>
>> >> >> >> >> >--Original Message--
>> >> >> >> >> >This has to be a query expression. Try:
>> >> >> >> >> >
>> >> >> >> >> >="SELECT myTable.* FROM myTable WHERE
>> >> >> (DateTimeEntered
>> >> >> >> >> BETWEEN @.StartDate
>> >> >> >> >> >AND @.EndDate) AND (mySet = N'" &
>> >> >> >> >> >System.Environment.GetEnvironmentVariable
>> >> ("MYSET")
>> >> >> >> & "')"
>> >> >> >> >> >
>> >> >> >> >> >You'll need to first type in the static SQL
>> query
>> >> >> (in
>> >> >> >> >> order to populate the
>> >> >> >> >> >fields list in fields window), run it, and
>> then
>> >> >> replace
>> >> >> >> >> it with the above
>> >> >> >> >> >query expression.
>> >> >> >> >> >
>> >> >> >> >> >--
>> >> >> >> >> >Ravi Mumulla (Microsoft)
>> >> >> >> >> >SQL Server Reporting Services
>> >> >> >> >> >
>> >> >> >> >> >This posting is provided "AS IS" with no
>> >> warranties,
>> >> >> >> and
>> >> >> >> >> confers no rights.
>> >> >> >> >> >"JIM.H."
<anonymous@.discussions.microsoft.com>
>> >> >> wrote in
>> >> >> >> >> message
>> >> >> >> >> >news:2c50601c46934$5bd8b080
>> $a401280a@.phx.gbl...
>> >> >> >> >> >> Hello,
>> >> >> >> >> >> I have the following SQL given by report
>> >> designer
>> >> >> in
>> >> >> >> the
>> >> >> >> >> >> SQL pane. I know I have MYSET defined
>> however
>> >> this
>> >> >> >> SQL
>> >> >> >> >> >> string does not bring anything, if I
remove
>> >> mySet
>> >> >> >> filed
>> >> >> >> >> it
>> >> >> >> >> >> is working fine.
>> >> >> >> >> >>
>> >> >> >> >> >> SELECT myTable.*
>> >> >> >> >> >> FROM myTable
>> >> >> >> >> >> WHERE (DateTimeEntered BETWEEN
>> @.StartDate
>> >> AND
>> >> >> >> >> >> @.EndDate) AND (mySet = N'"
>> >> >> >> >> >>
&System.Environment.GetEnvironmentVariable
>> >> >> ("MYSET")
>> >> >> >> &"')
>> >> >> >> >> >>
>> >> >> >> >> >> What is problem?
>> >> >> >> >> >> Thanks,
>> >> >> >> >> >> Jim.
>> >> >> >> >> >>
>> >> >> >> >> >
>> >> >> >> >> >
>> >> >> >> >> >.
>> >> >> >> >> >
>> >> >> >> >
>> >> >> >> >
>> >> >> >> >.
>> >> >> >> >
>> >> >> >
>> >> >> >
>> >> >> >.
>> >> >> >
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||There's no =User!UserGroupNames collection. You can use LDAP provider with a
SQL query simlar to the following to get group names:
select name from 'LDAP://DC=DomainShortName,DC=com' where
objectCategory='group'
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JIM.H." <anonymous@.discussions.microsoft.com> wrote in message
news:2e69d01c46acf$d7501220$a301280a@.phx.gbl...
> I actaully need =User!UserGroupNames, if availabe, not
> UserID. I could not get Active directory connection
> working, Group name is actaully an environment variable,
> that is why I was trying to read it, but if I could list
> of group names in another way that would still work for me.
> >--Original Message--
> >If =User!UserID works for you, that would be the
> recommended approach.
> >
> >--
> >Ravi Mumulla (Microsoft)
> >SQL Server Reporting Services
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2dbc101c46aa1$8c871b40$a401280a@.phx.gbl...
> >> Ravi,
> >> We are using Win2003 (I have only one machine that has
> SQL
> >> and RS installed), I installed SP1 for RS2000 and tried
> >> it, it did not work. I also tried =User!UserID to see if
> >> it is comping up in IE, that works fine. I could not get
> >> environment variable displayed in IE.
> >> Thanks,
> >> Jim.
> >>
> >> >--Original Message--
> >> >I tried it on both Win2k and Win2k3 and it seems to be
> >> working with RS 2000
> >> >SP1 installed. Can you try it with a new report or on
> >> another machine and
> >> >let me know?
> >> >
> >> >--
> >> >Ravi Mumulla (Microsoft)
> >> >SQL Server Reporting Services
> >> >
> >> >This posting is provided "AS IS" with no warranties,
> and
> >> confers no rights.
> >> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:2ceb701c46a83$7b33d130$a601280a@.phx.gbl...
> >> >> Thanks Ravi,
> >> >> I set PermissionSetName="FullTrust" for
> >> >> Name="Report_Expressions_Default_Permissions"
> >> >> This time I do not see anything, not even #err. I
> still
> >> >> see my environment variable in Preview screen.
> >> >>
> >> >>
> >> >> >--Original Message--
> >> >> >This is because the Expression Host is
> >> >> assigned "Execution" permission set,
> >> >> >so when it runs from Report Manager, it will not
> have
> >> >> access to the
> >> >> >envrionment variables. If you change the permission
> set
> >> >> for expression host
> >> >> >to "FullTrust" in %PROGRAMFILES%\Microsoft SQL
> >> >> Server\MSSQL\Reporting
> >> >> >Services\ReportServer\rssrvpolicy.config, it will
> allow
> >> >> you to read the
> >> >> >environment variables. However, giving the
> expression
> >> >> host "FullTrust"
> >> >> >privileges is a potential security risk and not
> >> >> recommended. See
> >> >> >"Expressions Used in Reports" topic in
> >> >> >http://msdn.microsoft.com/library/default.asp?
> >> >> url=/library/en-
> >> >> us/RSPROG/htm/rsp_prog_extend_install_8j8x.asp
> >> >> >for details.
> >> >> >
> >> >> >That said, you'd have to use some other method to
> >> figure
> >> >> out the current
> >> >> >user. Did you already consider using global
> >> collections,
> >> >> specifically
> >> >> >User!UserID, to get the current user name? Refer to
> >> >> >http://msdn.microsoft.com/library/default.asp?
> >> >> url=/library/en-
> >> >> us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
> >> >> >for details.
> >> >> >
> >> >> >--
> >> >> >Ravi Mumulla (Microsoft)
> >> >> >SQL Server Reporting Services
> >> >> >
> >> >> >This posting is provided "AS IS" with no warranties,
> >> and
> >> >> confers no rights.
> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
> wrote in
> >> >> message
> >> >> >news:2cb5301c469ae$75bc7ed0$a401280a@.phx.gbl...
> >> >> >> Ravi,
> >> >> >> Thansk for the reply. The error is listed below in
> >> the
> >> >> >> text, I get it in the internet explorer, I do not
> see
> >> >> any
> >> >> >> other error around. I have
> >> >> >> =System.Environment.GetEnvironmentVariable
> ("MYSET")
> >> in
> >> >> one
> >> >> >> of the TextBox in the header section and it is
> giving
> >> >> >> error in the internet explorer, but in design
> view in
> >> >> the
> >> >> >> report designer I get MYSET value correct. I migth
> >> have
> >> >> >> some setting problem with he internet explorer.
> >> >> >> Thanks,
> >> >> >> Jim.
> >> >> >>
> >> >> >>
> >> >> >> >--Original Message--
> >> >> >> >What error do you see when you include it in the
> >> query
> >> >> >> expression?
> >> >> >> >
> >> >> >> >--
> >> >> >> >Ravi Mumulla (Microsoft)
> >> >> >> >SQL Server Reporting Services
> >> >> >> >
> >> >> >> >This posting is provided "AS IS" with no
> warranties,
> >> >> and
> >> >> >> confers no rights.
> >> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
> >> wrote in
> >> >> >> message
> >> >> >> >news:2cc6b01c46999$51fa2fc0$a501280a@.phx.gbl...
> >> >> >> >> Thanks Ravi. That was my problem I did not know
> >> that
> >> >> >> there
> >> >> >> >> is a generic query designer. I followed your
> steps
> >> >> run
> >> >> >> my
> >> >> >> >> report with Debug->Start and got the following.
> >> >> >> >>
> >> >> >> >> . An error has occurred during report
> processing.
> >> >> >> >> (rsProcessingAborted) Get Online Help
> >> >> >> >> o Cannot set the command text for data
> >> >> >> >> set 'MySQLDB'. (rsErrorSettingCommandText) Get
> >> Online
> >> >> >> Help
> >> >> >> >> Error during processing of the CommandText
> >> >> >> >> expression of dataset 'MySQLDB'.
> >> >> >> >> (rsQueryCommandTextProcessingError) Get Online
> Hel
> >> >> >> >>
> >> >> >> >> The report is running if I do not have mySet
> >> >> constraint.
> >> >> >> >> Do you have any suggestions?
> >> >> >> >>
> >> >> >> >> >--Original Message--
> >> >> >> >> >Jim-
> >> >> >> >> >Here are the detailed steps:
> >> >> >> >> >
> >> >> >> >> >Step 1. Create the relevant dataset in report
> >> >> designer.
> >> >> >> >> >Step 2. In the report designer Data tab,
> switch
> >> to
> >> >> >> >> Generic Query Designer
> >> >> >> >> >(this is the fourth button from the left after
> >> the
> >> >> >> >> Dataset dropdown - just
> >> >> >> >> >press this button if it isn't in the pressed
> >> state.)
> >> >> >> >> >Step 3. Type in the following query text:
> SELECT
> >> >> >> >> myTable.* FROM myTable
> >> >> >> >> >WHERE (DateTimeEntered BETWEEN @.StartDate AND
> >> >> @.EndDate)
> >> >> >> >> >Step 4. Run the query (enter relevant values
> for
> >> >> >> >> @.StartDate AND @.EndDate
> >> >> >> >> >when prompted).
> >> >> >> >> >Step 5. Switch to report designer Layout tab.
> >> This
> >> >> >> should
> >> >> >> >> populate the
> >> >> >> >> >fields windows with relevant fields, i.e., you
> >> will
> >> >> see
> >> >> >> >> the list of fields
> >> >> >> >> >in the fields window.
> >> >> >> >> >Step 6. Go back to report designer Data tab.
> >> Switch
> >> >> to
> >> >> >> >> Generic Query
> >> >> >> >> >Designer if you're not already there.
> >> >> >> >> >Step 7. Type in the following query text
> (which
> >> is a
> >> >> >> >> query expression):
> >> >> >> >> >="SELECT myTable.* FROM myTable WHERE
> >> >> (DateTimeEntered
> >> >> >> >> BETWEEN @.StartDate
> >> >> >> >> >AND @.EndDate) AND (mySet = N'" &
> >> >> >> >> >System.Environment.GetEnvironmentVariable
> >> ("MYSET")
> >> >> >> & "')"
> >> >> >> >> >The Run button in the query designer should
> now
> >> be
> >> >> >> >> disabled. You will not be
> >> >> >> >> >able to execute the query.
> >> >> >> >> >Step 8. Switch to report designer Layout tab.
> >> >> >> >> >Step 9. You're now ready to use the fields in
> >> any of
> >> >> >> the
> >> >> >> >> data-bound controls
> >> >> >> >> >(table, matrix, list, chart).
> >> >> >> >> >
> >> >> >> >> >Please let me know if you'd like further
> >> >> clarification.
> >> >> >> >> >
> >> >> >> >> >--
> >> >> >> >> >Ravi Mumulla (Microsoft)
> >> >> >> >> >SQL Server Reporting Services
> >> >> >> >> >
> >> >> >> >> >This posting is provided "AS IS" with no
> >> warranties,
> >> >> >> and
> >> >> >> >> confers no rights.
> >> >> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
> >> >> wrote in
> >> >> >> >> message
> >> >> >> >> >news:2c25101c46945$74999a60
> $a501280a@.phx.gbl...
> >> >> >> >> >> Ravi, you need to tell me what you mean by
> >> query
> >> >> >> >> >> expression. I am completely new and did not
> get
> >> >> any
> >> >> >> >> >> training on this system yet. I run my query
> >> >> without
> >> >> >> >> mySet
> >> >> >> >> >> part, it runs fine, and I copy-paste your
> >> string
> >> >> to
> >> >> >> SQL
> >> >> >> >> >> pane and I get unable to parse query text
> >> message.
> >> >> >> Can
> >> >> >> >> you
> >> >> >> >> >> give me a link that explains this case.
> >> >> >> >> >>
> >> >> >> >> >> >--Original Message--
> >> >> >> >> >> >This has to be a query expression. Try:
> >> >> >> >> >> >
> >> >> >> >> >> >="SELECT myTable.* FROM myTable WHERE
> >> >> >> (DateTimeEntered
> >> >> >> >> >> BETWEEN @.StartDate
> >> >> >> >> >> >AND @.EndDate) AND (mySet = N'" &
> >> >> >> >> >> >System.Environment.GetEnvironmentVariable
> >> >> ("MYSET")
> >> >> >> >> & "')"
> >> >> >> >> >> >
> >> >> >> >> >> >You'll need to first type in the static SQL
> >> query
> >> >> >> (in
> >> >> >> >> >> order to populate the
> >> >> >> >> >> >fields list in fields window), run it, and
> >> then
> >> >> >> replace
> >> >> >> >> >> it with the above
> >> >> >> >> >> >query expression.
> >> >> >> >> >> >
> >> >> >> >> >> >--
> >> >> >> >> >> >Ravi Mumulla (Microsoft)
> >> >> >> >> >> >SQL Server Reporting Services
> >> >> >> >> >> >
> >> >> >> >> >> >This posting is provided "AS IS" with no
> >> >> warranties,
> >> >> >> >> and
> >> >> >> >> >> confers no rights.
> >> >> >> >> >> >"JIM.H."
> <anonymous@.discussions.microsoft.com>
> >> >> >> wrote in
> >> >> >> >> >> message
> >> >> >> >> >> >news:2c50601c46934$5bd8b080
> >> $a401280a@.phx.gbl...
> >> >> >> >> >> >> Hello,
> >> >> >> >> >> >> I have the following SQL given by report
> >> >> designer
> >> >> >> in
> >> >> >> >> the
> >> >> >> >> >> >> SQL pane. I know I have MYSET defined
> >> however
> >> >> this
> >> >> >> >> SQL
> >> >> >> >> >> >> string does not bring anything, if I
> remove
> >> >> mySet
> >> >> >> >> filed
> >> >> >> >> >> it
> >> >> >> >> >> >> is working fine.
> >> >> >> >> >> >>
> >> >> >> >> >> >> SELECT myTable.*
> >> >> >> >> >> >> FROM myTable
> >> >> >> >> >> >> WHERE (DateTimeEntered BETWEEN
> >> @.StartDate
> >> >> AND
> >> >> >> >> >> >> @.EndDate) AND (mySet = N'"
> >> >> >> >> >> >>
> &System.Environment.GetEnvironmentVariable
> >> >> >> ("MYSET")
> >> >> >> >> &"')
> >> >> >> >> >> >>
> >> >> >> >> >> >> What is problem?
> >> >> >> >> >> >> Thanks,
> >> >> >> >> >> >> Jim.
> >> >> >> >> >> >>
> >> >> >> >> >> >
> >> >> >> >> >> >
> >> >> >> >> >> >.
> >> >> >> >> >> >
> >> >> >> >> >
> >> >> >> >> >
> >> >> >> >> >.
> >> >> >> >> >
> >> >> >> >
> >> >> >> >
> >> >> >> >.
> >> >> >> >
> >> >> >
> >> >> >
> >> >> >.
> >> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||OK, I figured out a hack for you. Try this:
1. Pull up report designer.
2. Create a dataset pointing to the master database (or any other database)
on the server on which you want to read the environment variable.
3. Paste the following query in data view: master..xp_cmdshell 'echo
%MYSET%'
4. Run the query and verify that it is returning the expected value. The
column header will show up as "Output"
5. Go ot the fields window and create a database field (Fields Window ->
Right-click -> Add... button).
6. Type in "output" for Name and "output" for database field.
7. Go to layout view and drop a textbox on the design surface.
8. Type in the following expression in the textbox: =First(Fields!Output.Value, "master")
9. Preview the report. It should show you the value of %MYSET% env.
variable.
10. Deploy the report and view it in report manager. It should show you the
value of %MYSET% env. variable.
This does not require you to give the expression host FullTrust permisson
set.
Please let me know if this works for you.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:%23Fd7pPtaEHA.3508@.TK2MSFTNGP09.phx.gbl...
> There's no =User!UserGroupNames collection. You can use LDAP provider with
a
> SQL query simlar to the following to get group names:
> select name from 'LDAP://DC=DomainShortName,DC=com' where
> objectCategory='group'
>
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "JIM.H." <anonymous@.discussions.microsoft.com> wrote in message
> news:2e69d01c46acf$d7501220$a301280a@.phx.gbl...
> >
> > I actaully need =User!UserGroupNames, if availabe, not
> > UserID. I could not get Active directory connection
> > working, Group name is actaully an environment variable,
> > that is why I was trying to read it, but if I could list
> > of group names in another way that would still work for me.
> >
> > >--Original Message--
> > >If =User!UserID works for you, that would be the
> > recommended approach.
> > >
> > >--
> > >Ravi Mumulla (Microsoft)
> > >SQL Server Reporting Services
> > >
> > >This posting is provided "AS IS" with no warranties, and
> > confers no rights.
> > >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> > message
> > >news:2dbc101c46aa1$8c871b40$a401280a@.phx.gbl...
> > >> Ravi,
> > >> We are using Win2003 (I have only one machine that has
> > SQL
> > >> and RS installed), I installed SP1 for RS2000 and tried
> > >> it, it did not work. I also tried =User!UserID to see if
> > >> it is comping up in IE, that works fine. I could not get
> > >> environment variable displayed in IE.
> > >> Thanks,
> > >> Jim.
> > >>
> > >> >--Original Message--
> > >> >I tried it on both Win2k and Win2k3 and it seems to be
> > >> working with RS 2000
> > >> >SP1 installed. Can you try it with a new report or on
> > >> another machine and
> > >> >let me know?
> > >> >
> > >> >--
> > >> >Ravi Mumulla (Microsoft)
> > >> >SQL Server Reporting Services
> > >> >
> > >> >This posting is provided "AS IS" with no warranties,
> > and
> > >> confers no rights.
> > >> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> > >> message
> > >> >news:2ceb701c46a83$7b33d130$a601280a@.phx.gbl...
> > >> >> Thanks Ravi,
> > >> >> I set PermissionSetName="FullTrust" for
> > >> >> Name="Report_Expressions_Default_Permissions"
> > >> >> This time I do not see anything, not even #err. I
> > still
> > >> >> see my environment variable in Preview screen.
> > >> >>
> > >> >>
> > >> >> >--Original Message--
> > >> >> >This is because the Expression Host is
> > >> >> assigned "Execution" permission set,
> > >> >> >so when it runs from Report Manager, it will not
> > have
> > >> >> access to the
> > >> >> >envrionment variables. If you change the permission
> > set
> > >> >> for expression host
> > >> >> >to "FullTrust" in %PROGRAMFILES%\Microsoft SQL
> > >> >> Server\MSSQL\Reporting
> > >> >> >Services\ReportServer\rssrvpolicy.config, it will
> > allow
> > >> >> you to read the
> > >> >> >environment variables. However, giving the
> > expression
> > >> >> host "FullTrust"
> > >> >> >privileges is a potential security risk and not
> > >> >> recommended. See
> > >> >> >"Expressions Used in Reports" topic in
> > >> >> >http://msdn.microsoft.com/library/default.asp?
> > >> >> url=/library/en-
> > >> >> us/RSPROG/htm/rsp_prog_extend_install_8j8x.asp
> > >> >> >for details.
> > >> >> >
> > >> >> >That said, you'd have to use some other method to
> > >> figure
> > >> >> out the current
> > >> >> >user. Did you already consider using global
> > >> collections,
> > >> >> specifically
> > >> >> >User!UserID, to get the current user name? Refer to
> > >> >> >http://msdn.microsoft.com/library/default.asp?
> > >> >> url=/library/en-
> > >> >> us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
> > >> >> >for details.
> > >> >> >
> > >> >> >--
> > >> >> >Ravi Mumulla (Microsoft)
> > >> >> >SQL Server Reporting Services
> > >> >> >
> > >> >> >This posting is provided "AS IS" with no warranties,
> > >> and
> > >> >> confers no rights.
> > >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
> > wrote in
> > >> >> message
> > >> >> >news:2cb5301c469ae$75bc7ed0$a401280a@.phx.gbl...
> > >> >> >> Ravi,
> > >> >> >> Thansk for the reply. The error is listed below in
> > >> the
> > >> >> >> text, I get it in the internet explorer, I do not
> > see
> > >> >> any
> > >> >> >> other error around. I have
> > >> >> >> =System.Environment.GetEnvironmentVariable
> > ("MYSET")
> > >> in
> > >> >> one
> > >> >> >> of the TextBox in the header section and it is
> > giving
> > >> >> >> error in the internet explorer, but in design
> > view in
> > >> >> the
> > >> >> >> report designer I get MYSET value correct. I migth
> > >> have
> > >> >> >> some setting problem with he internet explorer.
> > >> >> >> Thanks,
> > >> >> >> Jim.
> > >> >> >>
> > >> >> >>
> > >> >> >> >--Original Message--
> > >> >> >> >What error do you see when you include it in the
> > >> query
> > >> >> >> expression?
> > >> >> >> >
> > >> >> >> >--
> > >> >> >> >Ravi Mumulla (Microsoft)
> > >> >> >> >SQL Server Reporting Services
> > >> >> >> >
> > >> >> >> >This posting is provided "AS IS" with no
> > warranties,
> > >> >> and
> > >> >> >> confers no rights.
> > >> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
> > >> wrote in
> > >> >> >> message
> > >> >> >> >news:2cc6b01c46999$51fa2fc0$a501280a@.phx.gbl...
> > >> >> >> >> Thanks Ravi. That was my problem I did not know
> > >> that
> > >> >> >> there
> > >> >> >> >> is a generic query designer. I followed your
> > steps
> > >> >> run
> > >> >> >> my
> > >> >> >> >> report with Debug->Start and got the following.
> > >> >> >> >>
> > >> >> >> >> . An error has occurred during report
> > processing.
> > >> >> >> >> (rsProcessingAborted) Get Online Help
> > >> >> >> >> o Cannot set the command text for data
> > >> >> >> >> set 'MySQLDB'. (rsErrorSettingCommandText) Get
> > >> Online
> > >> >> >> Help
> > >> >> >> >> Error during processing of the CommandText
> > >> >> >> >> expression of dataset 'MySQLDB'.
> > >> >> >> >> (rsQueryCommandTextProcessingError) Get Online
> > Hel
> > >> >> >> >>
> > >> >> >> >> The report is running if I do not have mySet
> > >> >> constraint.
> > >> >> >> >> Do you have any suggestions?
> > >> >> >> >>
> > >> >> >> >> >--Original Message--
> > >> >> >> >> >Jim-
> > >> >> >> >> >Here are the detailed steps:
> > >> >> >> >> >
> > >> >> >> >> >Step 1. Create the relevant dataset in report
> > >> >> designer.
> > >> >> >> >> >Step 2. In the report designer Data tab,
> > switch
> > >> to
> > >> >> >> >> Generic Query Designer
> > >> >> >> >> >(this is the fourth button from the left after
> > >> the
> > >> >> >> >> Dataset dropdown - just
> > >> >> >> >> >press this button if it isn't in the pressed
> > >> state.)
> > >> >> >> >> >Step 3. Type in the following query text:
> > SELECT
> > >> >> >> >> myTable.* FROM myTable
> > >> >> >> >> >WHERE (DateTimeEntered BETWEEN @.StartDate AND
> > >> >> @.EndDate)
> > >> >> >> >> >Step 4. Run the query (enter relevant values
> > for
> > >> >> >> >> @.StartDate AND @.EndDate
> > >> >> >> >> >when prompted).
> > >> >> >> >> >Step 5. Switch to report designer Layout tab.
> > >> This
> > >> >> >> should
> > >> >> >> >> populate the
> > >> >> >> >> >fields windows with relevant fields, i.e., you
> > >> will
> > >> >> see
> > >> >> >> >> the list of fields
> > >> >> >> >> >in the fields window.
> > >> >> >> >> >Step 6. Go back to report designer Data tab.
> > >> Switch
> > >> >> to
> > >> >> >> >> Generic Query
> > >> >> >> >> >Designer if you're not already there.
> > >> >> >> >> >Step 7. Type in the following query text
> > (which
> > >> is a
> > >> >> >> >> query expression):
> > >> >> >> >> >="SELECT myTable.* FROM myTable WHERE
> > >> >> (DateTimeEntered
> > >> >> >> >> BETWEEN @.StartDate
> > >> >> >> >> >AND @.EndDate) AND (mySet = N'" &
> > >> >> >> >> >System.Environment.GetEnvironmentVariable
> > >> ("MYSET")
> > >> >> >> & "')"
> > >> >> >> >> >The Run button in the query designer should
> > now
> > >> be
> > >> >> >> >> disabled. You will not be
> > >> >> >> >> >able to execute the query.
> > >> >> >> >> >Step 8. Switch to report designer Layout tab.
> > >> >> >> >> >Step 9. You're now ready to use the fields in
> > >> any of
> > >> >> >> the
> > >> >> >> >> data-bound controls
> > >> >> >> >> >(table, matrix, list, chart).
> > >> >> >> >> >
> > >> >> >> >> >Please let me know if you'd like further
> > >> >> clarification.
> > >> >> >> >> >
> > >> >> >> >> >--
> > >> >> >> >> >Ravi Mumulla (Microsoft)
> > >> >> >> >> >SQL Server Reporting Services
> > >> >> >> >> >
> > >> >> >> >> >This posting is provided "AS IS" with no
> > >> warranties,
> > >> >> >> and
> > >> >> >> >> confers no rights.
> > >> >> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
> > >> >> wrote in
> > >> >> >> >> message
> > >> >> >> >> >news:2c25101c46945$74999a60
> > $a501280a@.phx.gbl...
> > >> >> >> >> >> Ravi, you need to tell me what you mean by
> > >> query
> > >> >> >> >> >> expression. I am completely new and did not
> > get
> > >> >> any
> > >> >> >> >> >> training on this system yet. I run my query
> > >> >> without
> > >> >> >> >> mySet
> > >> >> >> >> >> part, it runs fine, and I copy-paste your
> > >> string
> > >> >> to
> > >> >> >> SQL
> > >> >> >> >> >> pane and I get unable to parse query text
> > >> message.
> > >> >> >> Can
> > >> >> >> >> you
> > >> >> >> >> >> give me a link that explains this case.
> > >> >> >> >> >>
> > >> >> >> >> >> >--Original Message--
> > >> >> >> >> >> >This has to be a query expression. Try:
> > >> >> >> >> >> >
> > >> >> >> >> >> >="SELECT myTable.* FROM myTable WHERE
> > >> >> >> (DateTimeEntered
> > >> >> >> >> >> BETWEEN @.StartDate
> > >> >> >> >> >> >AND @.EndDate) AND (mySet = N'" &
> > >> >> >> >> >> >System.Environment.GetEnvironmentVariable
> > >> >> ("MYSET")
> > >> >> >> >> & "')"
> > >> >> >> >> >> >
> > >> >> >> >> >> >You'll need to first type in the static SQL
> > >> query
> > >> >> >> (in
> > >> >> >> >> >> order to populate the
> > >> >> >> >> >> >fields list in fields window), run it, and
> > >> then
> > >> >> >> replace
> > >> >> >> >> >> it with the above
> > >> >> >> >> >> >query expression.
> > >> >> >> >> >> >
> > >> >> >> >> >> >--
> > >> >> >> >> >> >Ravi Mumulla (Microsoft)
> > >> >> >> >> >> >SQL Server Reporting Services
> > >> >> >> >> >> >
> > >> >> >> >> >> >This posting is provided "AS IS" with no
> > >> >> warranties,
> > >> >> >> >> and
> > >> >> >> >> >> confers no rights.
> > >> >> >> >> >> >"JIM.H."
> > <anonymous@.discussions.microsoft.com>
> > >> >> >> wrote in
> > >> >> >> >> >> message
> > >> >> >> >> >> >news:2c50601c46934$5bd8b080
> > >> $a401280a@.phx.gbl...
> > >> >> >> >> >> >> Hello,
> > >> >> >> >> >> >> I have the following SQL given by report
> > >> >> designer
> > >> >> >> in
> > >> >> >> >> the
> > >> >> >> >> >> >> SQL pane. I know I have MYSET defined
> > >> however
> > >> >> this
> > >> >> >> >> SQL
> > >> >> >> >> >> >> string does not bring anything, if I
> > remove
> > >> >> mySet
> > >> >> >> >> filed
> > >> >> >> >> >> it
> > >> >> >> >> >> >> is working fine.
> > >> >> >> >> >> >>
> > >> >> >> >> >> >> SELECT myTable.*
> > >> >> >> >> >> >> FROM myTable
> > >> >> >> >> >> >> WHERE (DateTimeEntered BETWEEN
> > >> @.StartDate
> > >> >> AND
> > >> >> >> >> >> >> @.EndDate) AND (mySet = N'"
> > >> >> >> >> >> >>
> > &System.Environment.GetEnvironmentVariable
> > >> >> >> ("MYSET")
> > >> >> >> >> &"')
> > >> >> >> >> >> >>
> > >> >> >> >> >> >> What is problem?
> > >> >> >> >> >> >> Thanks,
> > >> >> >> >> >> >> Jim.
> > >> >> >> >> >> >>
> > >> >> >> >> >> >
> > >> >> >> >> >> >
> > >> >> >> >> >> >.
> > >> >> >> >> >> >
> > >> >> >> >> >
> > >> >> >> >> >
> > >> >> >> >> >.
> > >> >> >> >> >
> > >> >> >> >
> > >> >> >> >
> > >> >> >> >.
> > >> >> >> >
> > >> >> >
> > >> >> >
> > >> >> >.
> > >> >> >
> > >> >
> > >> >
> > >> >.
> > >> >
> > >
> > >
> > >.
> > >
>
No comments:
Post a Comment