Hi all,
Is there anyone know how to access subReport's item? For example, can i use:
Reports!subReport.controls.textbox1.value in main report to access
subreport's textbox value? I have tried that, but failed. :-(
Thanks,
LisaHello Lisa,
Sadly no, I dont think you can do that.
The only property that I recall when looking at the IL that is exposed on a
ReportItem appears to be the Value property and that's I think is typed as a
String.
So on a report itself you can have a TextBox say called "TextBox1" and that
might be bound to a value from the database, and then you could bind another
TextBox to the first one with this syntax
=ReportItems!TextBox1.Value.
This kind of trick we discuss on p378 and 379 of our book where we explain
that it is useful for when you want to get image content from a DataSet into
a Report Header or Footer.
Peter Blackburn
Author: Hitchhiker's Guide to SQL Server 2000 Reporting Services
www.sqlreportingservices.net
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:8BDED34A-EC44-41B0-AC89-4763086BD414@.microsoft.com...
> Hi all,
> Is there anyone know how to access subReport's item? For example, can i
> use:
> Reports!subReport.controls.textbox1.value in main report to access
> subreport's textbox value? I have tried that, but failed. :-(
> Thanks,
> Lisa
Showing posts with label report. Show all posts
Showing posts with label report. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Able to install Report Server component on non-domain web svr?
Hello all,
We are looking at installing Reporting Services at our company here. We wish
to install the Report Server component on an IIS 6 web server that is on our
DMZ network, and hence not in our domain (where the SQL Server resides that
we want to put the RS database.) While reading BOL's "Selecting Components
of Reporting Services to Install" chapter, it notes: "IMPORTANT: Report
server and the SQL Server instance hosting the report server database must
be in the same domain or trusted domain." In our case, they would not be. Is
this a deal-killer, or has someone successfully implemented this type of
architecture out there? Any comments would be greatly appreciated.
TIA,
Will DennisOne solution would be to put the Report Server almost anywhere on your
internal network, then access it via web services on your Internet-facing
server.
Look at the Report Server Control example, just modify the URL parameter.
Jeff
"Willard Dennis" <wdennis@.bayada.com> wrote in message
news:#CgzKzJWEHA.1380@.TK2MSFTNGP12.phx.gbl...
> Hello all,
> We are looking at installing Reporting Services at our company here. We
wish
> to install the Report Server component on an IIS 6 web server that is on
our
> DMZ network, and hence not in our domain (where the SQL Server resides
that
> we want to put the RS database.) While reading BOL's "Selecting Components
> of Reporting Services to Install" chapter, it notes: "IMPORTANT: Report
> server and the SQL Server instance hosting the report server database must
> be in the same domain or trusted domain." In our case, they would not be.
Is
> this a deal-killer, or has someone successfully implemented this type of
> architecture out there? Any comments would be greatly appreciated.
> TIA,
> Will Dennis
>
We are looking at installing Reporting Services at our company here. We wish
to install the Report Server component on an IIS 6 web server that is on our
DMZ network, and hence not in our domain (where the SQL Server resides that
we want to put the RS database.) While reading BOL's "Selecting Components
of Reporting Services to Install" chapter, it notes: "IMPORTANT: Report
server and the SQL Server instance hosting the report server database must
be in the same domain or trusted domain." In our case, they would not be. Is
this a deal-killer, or has someone successfully implemented this type of
architecture out there? Any comments would be greatly appreciated.
TIA,
Will DennisOne solution would be to put the Report Server almost anywhere on your
internal network, then access it via web services on your Internet-facing
server.
Look at the Report Server Control example, just modify the URL parameter.
Jeff
"Willard Dennis" <wdennis@.bayada.com> wrote in message
news:#CgzKzJWEHA.1380@.TK2MSFTNGP12.phx.gbl...
> Hello all,
> We are looking at installing Reporting Services at our company here. We
wish
> to install the Report Server component on an IIS 6 web server that is on
our
> DMZ network, and hence not in our domain (where the SQL Server resides
that
> we want to put the RS database.) While reading BOL's "Selecting Components
> of Reporting Services to Install" chapter, it notes: "IMPORTANT: Report
> server and the SQL Server instance hosting the report server database must
> be in the same domain or trusted domain." In our case, they would not be.
Is
> this a deal-killer, or has someone successfully implemented this type of
> architecture out there? Any comments would be greatly appreciated.
> TIA,
> Will Dennis
>
Ability to create dynamic subreports within report
I have the need to create a report that contains four subreports. Each of the four subreports can be any one of 20-30 different reports (layouts differ).
Is there a way to programatically change the name (or source) of the subreport at runtime? Would this end up creating a new rdl for each report?
Any help or ideas are greatly appreciated!!
RickGcan you put "all" your sub reports under the master report and hide your sub
reports.
then dynamically, simply show the targeted sub reports.
"RickG" <RickG@.discussions.microsoft.com> a écrit dans le message de
news:BAF79348-FB1D-45B0-861B-4A3BDA30DE58@.microsoft.com...
> I have the need to create a report that contains four subreports. Each of
the four subreports can be any one of 20-30 different reports (layouts
differ).
> Is there a way to programatically change the name (or source) of the
subreport at runtime? Would this end up creating a new rdl for each report?
> Any help or ideas are greatly appreciated!!
> RickGsql
Is there a way to programatically change the name (or source) of the subreport at runtime? Would this end up creating a new rdl for each report?
Any help or ideas are greatly appreciated!!
RickGcan you put "all" your sub reports under the master report and hide your sub
reports.
then dynamically, simply show the targeted sub reports.
"RickG" <RickG@.discussions.microsoft.com> a écrit dans le message de
news:BAF79348-FB1D-45B0-861B-4A3BDA30DE58@.microsoft.com...
> I have the need to create a report that contains four subreports. Each of
the four subreports can be any one of 20-30 different reports (layouts
differ).
> Is there a way to programatically change the name (or source) of the
subreport at runtime? Would this end up creating a new rdl for each report?
> Any help or ideas are greatly appreciated!!
> RickGsql
Sunday, March 25, 2012
A4/Landscape printing problem
Hi,
I got a report with page Width 11.6 and Height 8.2 and Body Width 11.1 and
all margins are 0.25.
I want to print it in A4 landscape format. Unfortunately if I set for
default printer HP Desk Jet printer it prints as A4/Landscape correctly, but
if I set for default printer any HP LaserJet printer it tries to print in
Letter/Landscape format and of course it prints empty pages after every page.
I use direct print, SQL2005 Reports
Is there a way to set the printer page Letter or A4? Or to set up all
reports to print at A4 not Letter format?
ThanksHi Ivailo,
it seems that standard settings to printers are to be configured via Control
Panel/Printers and Faxes/<your printer>/Printing Preferences
And this to be repeated for each and any printer.
Saludos, Henrique
I got a report with page Width 11.6 and Height 8.2 and Body Width 11.1 and
all margins are 0.25.
I want to print it in A4 landscape format. Unfortunately if I set for
default printer HP Desk Jet printer it prints as A4/Landscape correctly, but
if I set for default printer any HP LaserJet printer it tries to print in
Letter/Landscape format and of course it prints empty pages after every page.
I use direct print, SQL2005 Reports
Is there a way to set the printer page Letter or A4? Or to set up all
reports to print at A4 not Letter format?
ThanksHi Ivailo,
it seems that standard settings to printers are to be configured via Control
Panel/Printers and Faxes/<your printer>/Printing Preferences
And this to be repeated for each and any printer.
Saludos, Henrique
A4 printing
Hi,
I have problems with A4 printing from the RSClientPrint activex.
I defined the size of my report to 21cm x 29,7cm (the A4 format) but
when printing the printer is set on a "personalized paper" format
instead of "A4" format.
So the user need to specify the A4 format each time he need to print a
report.
When I set the format to 8,5in x 11.5in the format is correctly
detected by the printer as a "letter(8,5"x11")". So if it's possible to
make understand to the printer that the format is "letter" it could be
possible to make it understand the A4 format, but I don't see how.
For me it's the last thing I need to make Reporting Services a valid
end user reporting tool.
I try this on several printers with several reports and they all do the
same thing.
Thanks for your responses.I don't think there is really much you can do here. The ActiveX control
passes the report sizes to the printer and let's the printer driver
determine what to do with these values. For some reason the driver is not
matching the values up with the A4 paper size, but it does for 8.5 X 11.
Does it work for other paper sizes, like legal, or 11 x 17?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sébastien Guillot" <s.guillot@.gmail.com> wrote in message
news:1126536856.799335.33940@.z14g2000cwz.googlegroups.com...
> Hi,
> I have problems with A4 printing from the RSClientPrint activex.
> I defined the size of my report to 21cm x 29,7cm (the A4 format) but
> when printing the printer is set on a "personalized paper" format
> instead of "A4" format.
> So the user need to specify the A4 format each time he need to print a
> report.
> When I set the format to 8,5in x 11.5in the format is correctly
> detected by the printer as a "letter(8,5"x11")". So if it's possible to
> make understand to the printer that the format is "letter" it could be
> possible to make it understand the A4 format, but I don't see how.
> For me it's the last thing I need to make Reporting Services a valid
> end user reporting tool.
> I try this on several printers with several reports and they all do the
> same thing.
> Thanks for your responses.
>|||Thanks for your response.
I tried 8.5 x 11, it takes me Legal (8,5" x 14"),
11"x17" -> Personalized paper format
8.5"x14" -> Personalized paper format
21cm x 29.7cm -> Personalized paper format
210mm x 297mm -> Personalized paper format
As you said it depend on how the printer interpret the values.
This problem appears most of time on multifunctionnal laser printers
how can receive multiple paper formats. On inkjet printer with only A4
paper it works better.
But, I think it's possible to make understand to the printer the right
format. All the rest of applications like Word, Excel,... managed to do
this so there's no good reason for the activex to not managed to do
this.
I see while searching answers thru several forums and newsgroup that
this problem occurs to a number a person all over the world.
So, it could be usefull to have a clear answer on how we need to set
page heigth and width for the A4 format.
(Sorry for my poor english, I tried to be as clear as possible)|||Yes, it would have been possible to do paper size detection in the print
control code. We sampled several printers and each printer handled
different paper sizes correctly. We have however noted the response from
customers regarding this issue and will consider adding it in a future
release.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sébastien Guillot" <s.guillot@.gmail.com> wrote in message
news:1126594138.934122.74600@.f14g2000cwb.googlegroups.com...
> Thanks for your response.
> I tried 8.5 x 11, it takes me Legal (8,5" x 14"),
> 11"x17" -> Personalized paper format
> 8.5"x14" -> Personalized paper format
> 21cm x 29.7cm -> Personalized paper format
> 210mm x 297mm -> Personalized paper format
>
> As you said it depend on how the printer interpret the values.
> This problem appears most of time on multifunctionnal laser printers
> how can receive multiple paper formats. On inkjet printer with only A4
> paper it works better.
> But, I think it's possible to make understand to the printer the right
> format. All the rest of applications like Word, Excel,... managed to do
> this so there's no good reason for the activex to not managed to do
> this.
> I see while searching answers thru several forums and newsgroup that
> this problem occurs to a number a person all over the world.
> So, it could be usefull to have a clear answer on how we need to set
> page heigth and width for the A4 format.
> (Sorry for my poor english, I tried to be as clear as possible)
>
I have problems with A4 printing from the RSClientPrint activex.
I defined the size of my report to 21cm x 29,7cm (the A4 format) but
when printing the printer is set on a "personalized paper" format
instead of "A4" format.
So the user need to specify the A4 format each time he need to print a
report.
When I set the format to 8,5in x 11.5in the format is correctly
detected by the printer as a "letter(8,5"x11")". So if it's possible to
make understand to the printer that the format is "letter" it could be
possible to make it understand the A4 format, but I don't see how.
For me it's the last thing I need to make Reporting Services a valid
end user reporting tool.
I try this on several printers with several reports and they all do the
same thing.
Thanks for your responses.I don't think there is really much you can do here. The ActiveX control
passes the report sizes to the printer and let's the printer driver
determine what to do with these values. For some reason the driver is not
matching the values up with the A4 paper size, but it does for 8.5 X 11.
Does it work for other paper sizes, like legal, or 11 x 17?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sébastien Guillot" <s.guillot@.gmail.com> wrote in message
news:1126536856.799335.33940@.z14g2000cwz.googlegroups.com...
> Hi,
> I have problems with A4 printing from the RSClientPrint activex.
> I defined the size of my report to 21cm x 29,7cm (the A4 format) but
> when printing the printer is set on a "personalized paper" format
> instead of "A4" format.
> So the user need to specify the A4 format each time he need to print a
> report.
> When I set the format to 8,5in x 11.5in the format is correctly
> detected by the printer as a "letter(8,5"x11")". So if it's possible to
> make understand to the printer that the format is "letter" it could be
> possible to make it understand the A4 format, but I don't see how.
> For me it's the last thing I need to make Reporting Services a valid
> end user reporting tool.
> I try this on several printers with several reports and they all do the
> same thing.
> Thanks for your responses.
>|||Thanks for your response.
I tried 8.5 x 11, it takes me Legal (8,5" x 14"),
11"x17" -> Personalized paper format
8.5"x14" -> Personalized paper format
21cm x 29.7cm -> Personalized paper format
210mm x 297mm -> Personalized paper format
As you said it depend on how the printer interpret the values.
This problem appears most of time on multifunctionnal laser printers
how can receive multiple paper formats. On inkjet printer with only A4
paper it works better.
But, I think it's possible to make understand to the printer the right
format. All the rest of applications like Word, Excel,... managed to do
this so there's no good reason for the activex to not managed to do
this.
I see while searching answers thru several forums and newsgroup that
this problem occurs to a number a person all over the world.
So, it could be usefull to have a clear answer on how we need to set
page heigth and width for the A4 format.
(Sorry for my poor english, I tried to be as clear as possible)|||Yes, it would have been possible to do paper size detection in the print
control code. We sampled several printers and each printer handled
different paper sizes correctly. We have however noted the response from
customers regarding this issue and will consider adding it in a future
release.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sébastien Guillot" <s.guillot@.gmail.com> wrote in message
news:1126594138.934122.74600@.f14g2000cwb.googlegroups.com...
> Thanks for your response.
> I tried 8.5 x 11, it takes me Legal (8,5" x 14"),
> 11"x17" -> Personalized paper format
> 8.5"x14" -> Personalized paper format
> 21cm x 29.7cm -> Personalized paper format
> 210mm x 297mm -> Personalized paper format
>
> As you said it depend on how the printer interpret the values.
> This problem appears most of time on multifunctionnal laser printers
> how can receive multiple paper formats. On inkjet printer with only A4
> paper it works better.
> But, I think it's possible to make understand to the printer the right
> format. All the rest of applications like Word, Excel,... managed to do
> this so there's no good reason for the activex to not managed to do
> this.
> I see while searching answers thru several forums and newsgroup that
> this problem occurs to a number a person all over the world.
> So, it could be usefull to have a clear answer on how we need to set
> page heigth and width for the A4 format.
> (Sorry for my poor english, I tried to be as clear as possible)
>
A weired problem in exporting a drilldown report...
Hi,
I am having a report with a pie chart, each pie links(using Actions->Jump to report) to a child report showing the relevent data in tabular format.
Everything is working fine i am able to click on pie and drill down to relevent report. But when i try to export the child report (in excel/pdf...) instedd of getting the child report the parent report gets exported i.e the exported pdf/excel contains pie chart(parent report) instead of tabular report(child report).
Its not happening with other similar reports, and i am clueless regarding this...
Sometimes instead of exporting the wrong report report server throws
"Index was out of range. Must be non negative and less than the size of the collection. Parameter name: index"
This happens only for Excel and not for pdf.
|||HI,akjoshi:
I have found some tips about this problem. Hope it will lead you to the right direction.What seemed to finally do the trick was removing a couple of Line statements in the page footer in the RDL file. One had a height of 0 and a width of 10 inches, and the other had a width of 0 and a height of 0.375 inches. After that, I was able to resize the page and footer in Layout view, and the report is now rendering correctly.
So, if you're having this problem, take a good look at your RDL file. Chances are that VS screwed something up.
If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.
I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance
Thursday, March 22, 2012
A way to add .Net classes in a report?
I know that subject line might be a little confusing - -
I have a project with a .vb class, that, when accessing a web page, based on
the user's login, I grab their Employee ID. With that, I can conceivably
create a method to get a list of employees who report to that person
Is there a way to include a .vb class in a report project, and then, access
a particular method (in this case, getting the list of direct reports), in
the document map?Elmo,
I have done something similar. You should be able to create a reportviewer
in an aspx page and access your class in the aspx code...pass the subsequent
values to the report.
billN
--
Message posted via http://www.sqlmonster.com|||Will I be able to use the same report file (.rdl) that I designed in a
Report Server Project in BI?
I tried adding a .rdl file to a ReportViewer control one time, and it wasn't
recognized.
"wnichols via SQLMonster.com" <u3357@.uwe> wrote in message
news:7e587ee1771a4@.uwe...
> Elmo,
> I have done something similar. You should be able to create a
> reportviewer
> in an aspx page and access your class in the aspx code...pass the
> subsequent
> values to the report.
> billN
> --
> Message posted via http://www.sqlmonster.com
>
I have a project with a .vb class, that, when accessing a web page, based on
the user's login, I grab their Employee ID. With that, I can conceivably
create a method to get a list of employees who report to that person
Is there a way to include a .vb class in a report project, and then, access
a particular method (in this case, getting the list of direct reports), in
the document map?Elmo,
I have done something similar. You should be able to create a reportviewer
in an aspx page and access your class in the aspx code...pass the subsequent
values to the report.
billN
--
Message posted via http://www.sqlmonster.com|||Will I be able to use the same report file (.rdl) that I designed in a
Report Server Project in BI?
I tried adding a .rdl file to a ReportViewer control one time, and it wasn't
recognized.
"wnichols via SQLMonster.com" <u3357@.uwe> wrote in message
news:7e587ee1771a4@.uwe...
> Elmo,
> I have done something similar. You should be able to create a
> reportviewer
> in an aspx page and access your class in the aspx code...pass the
> subsequent
> values to the report.
> billN
> --
> Message posted via http://www.sqlmonster.com
>
A URL expression problem with a Jump to command
I have a report that uses the table control, the last column has id's for
dealcompanies in it(Fields!res_dealCompanyid.Value). When the user clicks on
any of the other Columns in a row I would like to be able to grab the id from
the last column in the row and insert it into the expression for the Jump to
url feature.
this url works fin
="javascript:void(window.open('http://sandbox:82/EE2/DealCompany.aspx?id={535d58cc-a1b3-da11-9864-001320020c86}','_blank'))"
this one gives me an Error on pag
="javascript:void(window.open('http://sandbox:82/EE2/DealCompany.aspx?id={'&Fields!res_dealCompanyid.Value&'}','_blank'))"
How can I find out what is wrong
MikeI got this to work, my problem was that the dealCompanyid value was not a
string so I had to do a Fields!res_dealcompanyid.Value.tostring() then it
worked fine.
Parameters!CRMServer.Value just holds a Server info
="javascript:void(window.open('http://" & Parameters!CRMServer.Value &
"/EE2/DealCompany.aspx?id=" & Fields!res_dealcompanyid.Value.tostring() & "',
'_blank'))"
"Hotwheels" wrote:
> I have a report that uses the table control, the last column has id's for
> dealcompanies in it(Fields!res_dealCompanyid.Value). When the user clicks on
> any of the other Columns in a row I would like to be able to grab the id from
> the last column in the row and insert it into the expression for the Jump to
> url feature.
> this url works fine
> ="javascript:void(window.open('http://sandbox:82/EE2/DealCompany.aspx?id={535d58cc-a1b3-da11-9864-001320020c86}','_blank'))"
> this one gives me an Error on page
> ="javascript:void(window.open('http://sandbox:82/EE2/DealCompany.aspx?id={'&Fields!res_dealCompanyid.Value&'}','_blank'))"
> How can I find out what is wrong
> Mike
>
dealcompanies in it(Fields!res_dealCompanyid.Value). When the user clicks on
any of the other Columns in a row I would like to be able to grab the id from
the last column in the row and insert it into the expression for the Jump to
url feature.
this url works fin
="javascript:void(window.open('http://sandbox:82/EE2/DealCompany.aspx?id={535d58cc-a1b3-da11-9864-001320020c86}','_blank'))"
this one gives me an Error on pag
="javascript:void(window.open('http://sandbox:82/EE2/DealCompany.aspx?id={'&Fields!res_dealCompanyid.Value&'}','_blank'))"
How can I find out what is wrong
MikeI got this to work, my problem was that the dealCompanyid value was not a
string so I had to do a Fields!res_dealcompanyid.Value.tostring() then it
worked fine.
Parameters!CRMServer.Value just holds a Server info
="javascript:void(window.open('http://" & Parameters!CRMServer.Value &
"/EE2/DealCompany.aspx?id=" & Fields!res_dealcompanyid.Value.tostring() & "',
'_blank'))"
"Hotwheels" wrote:
> I have a report that uses the table control, the last column has id's for
> dealcompanies in it(Fields!res_dealCompanyid.Value). When the user clicks on
> any of the other Columns in a row I would like to be able to grab the id from
> the last column in the row and insert it into the expression for the Jump to
> url feature.
> this url works fine
> ="javascript:void(window.open('http://sandbox:82/EE2/DealCompany.aspx?id={535d58cc-a1b3-da11-9864-001320020c86}','_blank'))"
> this one gives me an Error on page
> ="javascript:void(window.open('http://sandbox:82/EE2/DealCompany.aspx?id={'&Fields!res_dealCompanyid.Value&'}','_blank'))"
> How can I find out what is wrong
> Mike
>
Tuesday, March 20, 2012
A tricky query
I have to write a query to generate a report over some interesting
data. It's basically scheduling which days people are working. The
data looks like this:
Employee StartDate EndDate Roster
-- -- -- --
Bob 12-Jun-06 24-Jun-06 _*___**
Mary 12-Jun-06 24-Jun-06 *_*__*_
The trick is, the roster field contains a string with a _ or *
depending on wether the person is scheduled to work that day or not,
but the first character always starts on the sunday. The startdate and
enddate can be any day of the w
.
In the example above, the 12-jun is a monday, so monday corresponds to
the second character in the roster string, so Bob's working and Mary's
not. The roster string wraps around, so the first character of the
roster string actually corresponds with the enddate here! Now, this
roster string could be 7, 10, 14 days long. The startDate -> endDate
could be the length of the roster string or less (only show a subset of
the roster data).
So! I need to write a query to feed a report to format this into
something like:
Monday 12-Jun Tuesday 13-Jun Wednesday 14-Jun Thursday 15-Jun
Friday 16-Jun
-- --
-- --
--
Bob Mary
Bob
Mary
I could get the report out if I can write a query to get it to this:
Employee DateWorking
-- --
Bob 12-Jun
Bob 16-Jun
Mary 13-Jun
Mary 16-Jun
Any ideas?
Thanks!
DaveDave
Can I ask you , why not just doing such reports on the client side? T-SQL
is not good for such things
"Dave Newman" <ddangerous@.gmail.com> wrote in message
news:1150894805.464073.255220@.r2g2000cwb.googlegroups.com...
>I have to write a query to generate a report over some interesting
> data. It's basically scheduling which days people are working. The
> data looks like this:
>
> Employee StartDate EndDate Roster
> -- -- -- --
> Bob 12-Jun-06 24-Jun-06 _*___**
> Mary 12-Jun-06 24-Jun-06 *_*__*_
> The trick is, the roster field contains a string with a _ or *
> depending on wether the person is scheduled to work that day or not,
> but the first character always starts on the sunday. The startdate and
> enddate can be any day of the w.
> In the example above, the 12-jun is a monday, so monday corresponds to
> the second character in the roster string, so Bob's working and Mary's
> not. The roster string wraps around, so the first character of the
> roster string actually corresponds with the enddate here! Now, this
> roster string could be 7, 10, 14 days long. The startDate -> endDate
> could be the length of the roster string or less (only show a subset of
> the roster data).
> So! I need to write a query to feed a report to format this into
> something like:
> Monday 12-Jun Tuesday 13-Jun Wednesday 14-Jun Thursday 15-Jun
> Friday 16-Jun
> -- --
> -- --
> --
> Bob Mary
> Bob
> Mary
> I could get the report out if I can write a query to get it to this:
> Employee DateWorking
> -- --
> Bob 12-Jun
> Bob 16-Jun
> Mary 13-Jun
> Mary 16-Jun
>
> Any ideas?
> Thanks!
> Dave
>|||>> I have to write a query to generate a report over some interesting data.
<<
First of all, you are not using ISO-8601 format dates. You might want
to do that, since what you did post was ambigous as well as
non-standard, I hope you do not think that ORACLE is a standard.
Next, you might want to read a book on programming principles. We do
not do reports in the database in a tiered architecture. This is more
fundamental than SQL.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. I
would love to see the LOGICAL definition of that silly bar chart you
labeled "roster" in your narrative since it is pure display.
CREATE TABLE Roster
(employee_name VARCHAR(20) NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
PRIMARY KEY (employee_name, start_date));
This is usually done with a Calendar table:
SELECT R.employee_name, C.cal_date
FROM Calendar AS C
LEFT OUTER JOIN
Roster AS R
ON C.cal_date BETWEEN R.start_date AND R.end_date
WHERE C.cal_date BETWEEN @.my_start_date AND @.my_end_date;
Google for other uses of the Calendar and auxiliary tables.|||Dude, you don't do things by halves... <g>
I can't think of any way to do it without iterating over each employee...
step 1:
select employee, startDate, abs(datediff(day, startDate, endDate)) as
dateIterations, Roster
into #tmp1
from theTable
step 2:
-- loop the temp table
-- for each row
-- loop the roster string from 1 to dateIterations
-- if substring(roster, index, 1) = '*'
-- insert a row for employee, dateadd(day, index, startdate)
step 3:
join Thetable to #tmp1 by employee
It sounds like you need to denormalize the results so you could also start
by creating a date table from min(startDate) to max(endDate) first with all
the dates inbetween,
then outer join to that by date to #tmp1 and theTable
[Sidenote]
This is one of the reasons why normalization is a good thing. It's
extremely hard to pull peices-parts of data from a column that contains
multiple data points...
"Dave Newman" <ddangerous@.gmail.com> wrote in message
news:1150894805.464073.255220@.r2g2000cwb.googlegroups.com...
>I have to write a query to generate a report over some interesting
> data. It's basically scheduling which days people are working. The
> data looks like this:
>
> Employee StartDate EndDate Roster
> -- -- -- --
> Bob 12-Jun-06 24-Jun-06 _*___**
> Mary 12-Jun-06 24-Jun-06 *_*__*_
> The trick is, the roster field contains a string with a _ or *
> depending on wether the person is scheduled to work that day or not,
> but the first character always starts on the sunday. The startdate and
> enddate can be any day of the w.
> In the example above, the 12-jun is a monday, so monday corresponds to
> the second character in the roster string, so Bob's working and Mary's
> not. The roster string wraps around, so the first character of the
> roster string actually corresponds with the enddate here! Now, this
> roster string could be 7, 10, 14 days long. The startDate -> endDate
> could be the length of the roster string or less (only show a subset of
> the roster data).
> So! I need to write a query to feed a report to format this into
> something like:
> Monday 12-Jun Tuesday 13-Jun Wednesday 14-Jun Thursday 15-Jun
> Friday 16-Jun
> -- --
> -- --
> --
> Bob Mary
> Bob
> Mary
> I could get the report out if I can write a query to get it to this:
> Employee DateWorking
> -- --
> Bob 12-Jun
> Bob 16-Jun
> Mary 13-Jun
> Mary 16-Jun
>
> Any ideas?
> Thanks!
> Dave
>|||This data arrangement - I was going to say design, but that did not
seem applicable - is sheer madness, of course.
One bit of your description has me particularly
. Look at
these three bits of the description:
- "the first character always starts on the sunday"
- "The roster string wraps around, so the first character of the
roster string actually corresponds with the enddate here!"
- " this roster string could be 7, 10, 14 days long."
With a string of length 7 or 14 I can see how it can wrap around and
still start on Sunday. With a length of 10 I can not see how this is
possible.
A second point of confusion is the sample data. The date ranges give
are far longer than the strings . This makes no sense.
Anyway, I believe that once those issues have been resolved the
following will work. Note that I changed the date range from the test
data you provided so that it did not exceed the length of the string.
CREATE TABLE Numbers
(nbr int not null)
INSERT Numbers
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT
5 UNION
SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT
10 UNION
SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14
CREATE TABLE Madness
(Employee varchar(20) not null,
Startdate datetime not null,
EndDate datetime not null,
Roster varchar(14) not null)
INSERT Madness values('Bob', '12 Jun 2006', '18 Jun 2006', '_*___**')
INSERT Madness values('Mary', '12 Jun 2006', '18 Jun 2006', '*_*__*_')
GO
--A view that lines up the string with the StartDate
CREATE View Madness_V
AS
SELECT Employee, StartDate, EndDate,
SUBSTRING(RTRIM(Roster) + Roster,
Datepart(wday,StartDate),
DateDiff(day,StartDate,EndDate)) as Shifted
FROM Madness
GO
--Lets see what the view gives us
SELECT *
FROM Madness_V
--Now for the desired results
SELECT M.Employee,
DATEADD(Day,Nbr-1,StartDate) as WorkingDate
FROM Madness_V as M
JOIN Numbers as N
ON Nbr <= Datediff(day,M.StartDate,M.EndDate)
WHERE SUBSTRING(Shifted,Nbr,1) = '*'
order by 1, 2
Roy Harvey
Beacon Falls, CT
On 21 Jun 2006 06:00:05 -0700, "Dave Newman" <ddangerous@.gmail.com>
wrote:
>I have to write a query to generate a report over some interesting
>data. It's basically scheduling which days people are working. The
>data looks like this:
>
>Employee StartDate EndDate Roster
>-- -- -- --
>Bob 12-Jun-06 24-Jun-06 _*___**
>Mary 12-Jun-06 24-Jun-06 *_*__*_
>The trick is, the roster field contains a string with a _ or *
>depending on wether the person is scheduled to work that day or not,
>but the first character always starts on the sunday. The startdate and
>enddate can be any day of the w.
>In the example above, the 12-jun is a monday, so monday corresponds to
>the second character in the roster string, so Bob's working and Mary's
>not. The roster string wraps around, so the first character of the
>roster string actually corresponds with the enddate here! Now, this
>roster string could be 7, 10, 14 days long. The startDate -> endDate
>could be the length of the roster string or less (only show a subset of
>the roster data).
>So! I need to write a query to feed a report to format this into
>something like:
>Monday 12-Jun Tuesday 13-Jun Wednesday 14-Jun Thursday 15-Jun
>Friday 16-Jun
>-- --
>-- --
>--
>Bob Mary
> Bob
> Mary
>I could get the report out if I can write a query to get it to this:
>Employee DateWorking
>-- --
>Bob 12-Jun
>Bob 16-Jun
>Mary 13-Jun
>Mary 16-Jun
>
>Any ideas?
>Thanks!
>Dave|||I don't understand your definition of how a roster day maps to an actual
date so I can't calculate that, but you can use recursion to "unflatten"
roster string into a row for each '*' and go from there. I also don't know
what the primary key of your table is, but I assume that name+start date
will work for that.
Create table #sched
(
name NVARCHAR(20),
start DATETIME,
[end] DATETIME,
roster NVARCHAR(14)
)
--TRUNCATE TABLE #sched
INSERT INTO #sched VALUES (N'bob', '12-JUN-06', '24-JUN-06', N'_*--**')
INSERT INTO #sched VALUES (N'mary', '12-JUN-06', '24-JUN-06', N'*_*__*_')
WITH workingDays
AS
(
-- get the first roster day for every entry in table
SELECT name, CHARINDEX(N'*', roster) AS rosterDay, start, roster FROM #sched
WHERE CHARINDEX(N'*', roster, 0) > 0
UNION ALL
-- now get subsequent roster days
SELECT S.name, CHARINDEX(N'*', S.roster, W.rosterDay + 1) AS rosterDay,
S.start,
S.roster from #sched AS S
JOIN workingDays AS W ON W.name = S.name and W.start = S.start AND
CHARINDEX(N'*', S.roster, W.rosterDay + 1) > 0
)
SELECT name, rosterDay, roster, start,
DATEADD(day, rosterDay-1, start) as DateWorking
FROM workingDays
ORDER BY name, rosterDay
name rosterDay roster start Date
Working
-- -- -- -- --
--
bob 2 _*--** 2006-06-12 00:00:00.000 2006
-06-13
00:00:00.000
bob 6 _*--** 2006-06-12 00:00:00.000 2006
-06-17
00:00:00.000
bob 7 _*--** 2006-06-12 00:00:00.000 2006
-06-18
00:00:00.000
mary 1 *_*__*_ 2006-06-12 00:00:00.000 2006
-06-12
00:00:00.000
mary 3 *_*__*_ 2006-06-12 00:00:00.000 2006
-06-14
00:00:00.000
mary 6 *_*__*_ 2006-06-12 00:00:00.000 2006
-06-17
00:00:00.000
If you replace the DateWorking column with a UDF that takes as input a roste
rDay
and start and produces a working date I think you will have what you want.
Dan
> I have to write a query to generate a report over some interesting
> data. It's basically scheduling which days people are working. The
> data looks like this:
> Employee StartDate EndDate Roster
> -- -- -- --
> Bob 12-Jun-06 24-Jun-06 _*___**
> Mary 12-Jun-06 24-Jun-06 *_*__*_
> The trick is, the roster field contains a string with a _ or *
> depending on wether the person is scheduled to work that day or not,
> but the first character always starts on the sunday. The startdate
> and enddate can be any day of the w.
> In the example above, the 12-jun is a monday, so monday corresponds to
> the second character in the roster string, so Bob's working and Mary's
> not. The roster string wraps around, so the first character of the
> roster string actually corresponds with the enddate here! Now, this
> roster string could be 7, 10, 14 days long. The startDate -> endDate
> could be the length of the roster string or less (only show a subset
> of the roster data).
> So! I need to write a query to feed a report to format this into
> something like:
> Monday 12-Jun Tuesday 13-Jun Wednesday 14-Jun Thursday 15-Jun
> Friday 16-Jun
> -- --
> -- --
> --
> Bob Mary
> Bob
> Mary
> I could get the report out if I can write a query to get it to this:
> Employee DateWorking
> -- --
> Bob 12-Jun
> Bob 16-Jun
> Mary 13-Jun
> Mary 16-Jun
> Any ideas?
> Thanks!
> Dave
>|||Hi Dave,
It would have been great if you had given the ddl. Anyways, here is the
solution.
Let me know if this works..
The ddl and ur sample data first
create table t1 (Employee varchar(10), StartDate datetime,
EndDate datetime, Roster varchar(10))
insert into t1 values ('Bob', '12-Jun-06', '24-Jun-06',
'_*___**')
insert into t1 values ('Mary', '12-Jun-06', '24-Jun-06',
'*_*__*_')
And here is the query. You need a temp table so that I don't loop through
that roster or whatever
--I have assumed that the size of the roster is 30 characters. If yit can be
100 or 1000 increase it accordingly
select top 30 identity(int,0,1) as num into #temp from sysobjects
--query
-- This is to remove any ambiguity
set datefirst 7
select Employee, StartDate - datepart(dw,startdate) + num + 1
from t1, #temp
where
substring(Roster,num%len(roster) + 1,1) = '*'
and StartDate - datepart(dw,startdate) + num + 1 between startdate and endda
te
Let me know if this was what you wanted.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi There,
I think this may solve your problem
Try it and let me know if it helped
CREATE TABLE Madness
(Employee varchar(20) not null,
Startdate datetime not null,
EndDate datetime not null,
Roster varchar(14) not null)
INSERT Madness values('Bob', '13 Jun 2006', '18 Jun 2006', '_*___**')
INSERT Madness values('Mary', '13 Jun 2006', '18 Jun 2006', '*_*__*_')
GO
Select identity(int,1,1) myid into Numbers from sysobjects
Select Case When myid<pos Then startdate+myid-1
Else startdate+pos-datepart(dw,startdate)
end ,
Employee, Case when substring(roster,pos,1)='*' then 'Working' else
'Not' End
From
(
Select myid,
case when (datepart(dw,startdate)+myid-1)%datalength(roster)= 0 then
datalength(roster) else
(datepart(dw,startdate)+myid-1)%datalength(roster) end Pos,
Employee,
Startdate,
Enddate,
Roster
>From Madness,Numbers
where myid <= datalength(Roster)
) XY order by 2,1
With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
Omnibuzz wrote:
> Hi Dave,
> It would have been great if you had given the ddl. Anyways, here is the
> solution.
> Let me know if this works..
> The ddl and ur sample data first
>
> create table t1 (Employee varchar(10), StartDate datetime,
> EndDate datetime, Roster varchar(10))
> insert into t1 values ('Bob', '12-Jun-06', '24-Jun-06',
> '_*___**')
> insert into t1 values ('Mary', '12-Jun-06', '24-Jun-06',
> '*_*__*_')
>
> And here is the query. You need a temp table so that I don't loop through
> that roster or whatever
> --I have assumed that the size of the roster is 30 characters. If yit can
be
> 100 or 1000 increase it accordingly
> select top 30 identity(int,0,1) as num into #temp from sysobjects
>
> --query
> -- This is to remove any ambiguity
> set datefirst 7
> select Employee, StartDate - datepart(dw,startdate) + num + 1
> from t1, #temp
> where
> substring(Roster,num%len(roster) + 1,1) = '*'
> and StartDate - datepart(dw,startdate) + num + 1 between startdate and end
date
> Let me know if this was what you wanted.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/sql
data. It's basically scheduling which days people are working. The
data looks like this:
Employee StartDate EndDate Roster
-- -- -- --
Bob 12-Jun-06 24-Jun-06 _*___**
Mary 12-Jun-06 24-Jun-06 *_*__*_
The trick is, the roster field contains a string with a _ or *
depending on wether the person is scheduled to work that day or not,
but the first character always starts on the sunday. The startdate and
enddate can be any day of the w
.In the example above, the 12-jun is a monday, so monday corresponds to
the second character in the roster string, so Bob's working and Mary's
not. The roster string wraps around, so the first character of the
roster string actually corresponds with the enddate here! Now, this
roster string could be 7, 10, 14 days long. The startDate -> endDate
could be the length of the roster string or less (only show a subset of
the roster data).
So! I need to write a query to feed a report to format this into
something like:
Monday 12-Jun Tuesday 13-Jun Wednesday 14-Jun Thursday 15-Jun
Friday 16-Jun
-- --
-- --
--
Bob Mary
Bob
Mary
I could get the report out if I can write a query to get it to this:
Employee DateWorking
-- --
Bob 12-Jun
Bob 16-Jun
Mary 13-Jun
Mary 16-Jun
Any ideas?
Thanks!
DaveDave
Can I ask you , why not just doing such reports on the client side? T-SQL
is not good for such things
"Dave Newman" <ddangerous@.gmail.com> wrote in message
news:1150894805.464073.255220@.r2g2000cwb.googlegroups.com...
>I have to write a query to generate a report over some interesting
> data. It's basically scheduling which days people are working. The
> data looks like this:
>
> Employee StartDate EndDate Roster
> -- -- -- --
> Bob 12-Jun-06 24-Jun-06 _*___**
> Mary 12-Jun-06 24-Jun-06 *_*__*_
> The trick is, the roster field contains a string with a _ or *
> depending on wether the person is scheduled to work that day or not,
> but the first character always starts on the sunday. The startdate and
> enddate can be any day of the w
.> In the example above, the 12-jun is a monday, so monday corresponds to
> the second character in the roster string, so Bob's working and Mary's
> not. The roster string wraps around, so the first character of the
> roster string actually corresponds with the enddate here! Now, this
> roster string could be 7, 10, 14 days long. The startDate -> endDate
> could be the length of the roster string or less (only show a subset of
> the roster data).
> So! I need to write a query to feed a report to format this into
> something like:
> Monday 12-Jun Tuesday 13-Jun Wednesday 14-Jun Thursday 15-Jun
> Friday 16-Jun
> -- --
> -- --
> --
> Bob Mary
> Bob
> Mary
> I could get the report out if I can write a query to get it to this:
> Employee DateWorking
> -- --
> Bob 12-Jun
> Bob 16-Jun
> Mary 13-Jun
> Mary 16-Jun
>
> Any ideas?
> Thanks!
> Dave
>|||>> I have to write a query to generate a report over some interesting data.
<<
First of all, you are not using ISO-8601 format dates. You might want
to do that, since what you did post was ambigous as well as
non-standard, I hope you do not think that ORACLE is a standard.
Next, you might want to read a book on programming principles. We do
not do reports in the database in a tiered architecture. This is more
fundamental than SQL.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. I
would love to see the LOGICAL definition of that silly bar chart you
labeled "roster" in your narrative since it is pure display.
CREATE TABLE Roster
(employee_name VARCHAR(20) NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
PRIMARY KEY (employee_name, start_date));
This is usually done with a Calendar table:
SELECT R.employee_name, C.cal_date
FROM Calendar AS C
LEFT OUTER JOIN
Roster AS R
ON C.cal_date BETWEEN R.start_date AND R.end_date
WHERE C.cal_date BETWEEN @.my_start_date AND @.my_end_date;
Google for other uses of the Calendar and auxiliary tables.|||Dude, you don't do things by halves... <g>
I can't think of any way to do it without iterating over each employee...
step 1:
select employee, startDate, abs(datediff(day, startDate, endDate)) as
dateIterations, Roster
into #tmp1
from theTable
step 2:
-- loop the temp table
-- for each row
-- loop the roster string from 1 to dateIterations
-- if substring(roster, index, 1) = '*'
-- insert a row for employee, dateadd(day, index, startdate)
step 3:
join Thetable to #tmp1 by employee
It sounds like you need to denormalize the results so you could also start
by creating a date table from min(startDate) to max(endDate) first with all
the dates inbetween,
then outer join to that by date to #tmp1 and theTable
[Sidenote]
This is one of the reasons why normalization is a good thing. It's
extremely hard to pull peices-parts of data from a column that contains
multiple data points...
"Dave Newman" <ddangerous@.gmail.com> wrote in message
news:1150894805.464073.255220@.r2g2000cwb.googlegroups.com...
>I have to write a query to generate a report over some interesting
> data. It's basically scheduling which days people are working. The
> data looks like this:
>
> Employee StartDate EndDate Roster
> -- -- -- --
> Bob 12-Jun-06 24-Jun-06 _*___**
> Mary 12-Jun-06 24-Jun-06 *_*__*_
> The trick is, the roster field contains a string with a _ or *
> depending on wether the person is scheduled to work that day or not,
> but the first character always starts on the sunday. The startdate and
> enddate can be any day of the w
.> In the example above, the 12-jun is a monday, so monday corresponds to
> the second character in the roster string, so Bob's working and Mary's
> not. The roster string wraps around, so the first character of the
> roster string actually corresponds with the enddate here! Now, this
> roster string could be 7, 10, 14 days long. The startDate -> endDate
> could be the length of the roster string or less (only show a subset of
> the roster data).
> So! I need to write a query to feed a report to format this into
> something like:
> Monday 12-Jun Tuesday 13-Jun Wednesday 14-Jun Thursday 15-Jun
> Friday 16-Jun
> -- --
> -- --
> --
> Bob Mary
> Bob
> Mary
> I could get the report out if I can write a query to get it to this:
> Employee DateWorking
> -- --
> Bob 12-Jun
> Bob 16-Jun
> Mary 13-Jun
> Mary 16-Jun
>
> Any ideas?
> Thanks!
> Dave
>|||This data arrangement - I was going to say design, but that did not
seem applicable - is sheer madness, of course.
One bit of your description has me particularly
. Look atthese three bits of the description:
- "the first character always starts on the sunday"
- "The roster string wraps around, so the first character of the
roster string actually corresponds with the enddate here!"
- " this roster string could be 7, 10, 14 days long."
With a string of length 7 or 14 I can see how it can wrap around and
still start on Sunday. With a length of 10 I can not see how this is
possible.
A second point of confusion is the sample data. The date ranges give
are far longer than the strings . This makes no sense.
Anyway, I believe that once those issues have been resolved the
following will work. Note that I changed the date range from the test
data you provided so that it did not exceed the length of the string.
CREATE TABLE Numbers
(nbr int not null)
INSERT Numbers
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT
5 UNION
SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT
10 UNION
SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14
CREATE TABLE Madness
(Employee varchar(20) not null,
Startdate datetime not null,
EndDate datetime not null,
Roster varchar(14) not null)
INSERT Madness values('Bob', '12 Jun 2006', '18 Jun 2006', '_*___**')
INSERT Madness values('Mary', '12 Jun 2006', '18 Jun 2006', '*_*__*_')
GO
--A view that lines up the string with the StartDate
CREATE View Madness_V
AS
SELECT Employee, StartDate, EndDate,
SUBSTRING(RTRIM(Roster) + Roster,
Datepart(w
day,StartDate),DateDiff(day,StartDate,EndDate)) as Shifted
FROM Madness
GO
--Lets see what the view gives us
SELECT *
FROM Madness_V
--Now for the desired results
SELECT M.Employee,
DATEADD(Day,Nbr-1,StartDate) as WorkingDate
FROM Madness_V as M
JOIN Numbers as N
ON Nbr <= Datediff(day,M.StartDate,M.EndDate)
WHERE SUBSTRING(Shifted,Nbr,1) = '*'
order by 1, 2
Roy Harvey
Beacon Falls, CT
On 21 Jun 2006 06:00:05 -0700, "Dave Newman" <ddangerous@.gmail.com>
wrote:
>I have to write a query to generate a report over some interesting
>data. It's basically scheduling which days people are working. The
>data looks like this:
>
>Employee StartDate EndDate Roster
>-- -- -- --
>Bob 12-Jun-06 24-Jun-06 _*___**
>Mary 12-Jun-06 24-Jun-06 *_*__*_
>The trick is, the roster field contains a string with a _ or *
>depending on wether the person is scheduled to work that day or not,
>but the first character always starts on the sunday. The startdate and
>enddate can be any day of the w
.>In the example above, the 12-jun is a monday, so monday corresponds to
>the second character in the roster string, so Bob's working and Mary's
>not. The roster string wraps around, so the first character of the
>roster string actually corresponds with the enddate here! Now, this
>roster string could be 7, 10, 14 days long. The startDate -> endDate
>could be the length of the roster string or less (only show a subset of
>the roster data).
>So! I need to write a query to feed a report to format this into
>something like:
>Monday 12-Jun Tuesday 13-Jun Wednesday 14-Jun Thursday 15-Jun
>Friday 16-Jun
>-- --
>-- --
>--
>Bob Mary
> Bob
> Mary
>I could get the report out if I can write a query to get it to this:
>Employee DateWorking
>-- --
>Bob 12-Jun
>Bob 16-Jun
>Mary 13-Jun
>Mary 16-Jun
>
>Any ideas?
>Thanks!
>Dave|||I don't understand your definition of how a roster day maps to an actual
date so I can't calculate that, but you can use recursion to "unflatten"
roster string into a row for each '*' and go from there. I also don't know
what the primary key of your table is, but I assume that name+start date
will work for that.
Create table #sched
(
name NVARCHAR(20),
start DATETIME,
[end] DATETIME,
roster NVARCHAR(14)
)
--TRUNCATE TABLE #sched
INSERT INTO #sched VALUES (N'bob', '12-JUN-06', '24-JUN-06', N'_*--**')
INSERT INTO #sched VALUES (N'mary', '12-JUN-06', '24-JUN-06', N'*_*__*_')
WITH workingDays
AS
(
-- get the first roster day for every entry in table
SELECT name, CHARINDEX(N'*', roster) AS rosterDay, start, roster FROM #sched
WHERE CHARINDEX(N'*', roster, 0) > 0
UNION ALL
-- now get subsequent roster days
SELECT S.name, CHARINDEX(N'*', S.roster, W.rosterDay + 1) AS rosterDay,
S.start,
S.roster from #sched AS S
JOIN workingDays AS W ON W.name = S.name and W.start = S.start AND
CHARINDEX(N'*', S.roster, W.rosterDay + 1) > 0
)
SELECT name, rosterDay, roster, start,
DATEADD(day, rosterDay-1, start) as DateWorking
FROM workingDays
ORDER BY name, rosterDay
name rosterDay roster start Date
Working
-- -- -- -- --
--
bob 2 _*--** 2006-06-12 00:00:00.000 2006
-06-13
00:00:00.000
bob 6 _*--** 2006-06-12 00:00:00.000 2006
-06-17
00:00:00.000
bob 7 _*--** 2006-06-12 00:00:00.000 2006
-06-18
00:00:00.000
mary 1 *_*__*_ 2006-06-12 00:00:00.000 2006
-06-12
00:00:00.000
mary 3 *_*__*_ 2006-06-12 00:00:00.000 2006
-06-14
00:00:00.000
mary 6 *_*__*_ 2006-06-12 00:00:00.000 2006
-06-17
00:00:00.000
If you replace the DateWorking column with a UDF that takes as input a roste
rDay
and start and produces a working date I think you will have what you want.
Dan
> I have to write a query to generate a report over some interesting
> data. It's basically scheduling which days people are working. The
> data looks like this:
> Employee StartDate EndDate Roster
> -- -- -- --
> Bob 12-Jun-06 24-Jun-06 _*___**
> Mary 12-Jun-06 24-Jun-06 *_*__*_
> The trick is, the roster field contains a string with a _ or *
> depending on wether the person is scheduled to work that day or not,
> but the first character always starts on the sunday. The startdate
> and enddate can be any day of the w
.> In the example above, the 12-jun is a monday, so monday corresponds to
> the second character in the roster string, so Bob's working and Mary's
> not. The roster string wraps around, so the first character of the
> roster string actually corresponds with the enddate here! Now, this
> roster string could be 7, 10, 14 days long. The startDate -> endDate
> could be the length of the roster string or less (only show a subset
> of the roster data).
> So! I need to write a query to feed a report to format this into
> something like:
> Monday 12-Jun Tuesday 13-Jun Wednesday 14-Jun Thursday 15-Jun
> Friday 16-Jun
> -- --
> -- --
> --
> Bob Mary
> Bob
> Mary
> I could get the report out if I can write a query to get it to this:
> Employee DateWorking
> -- --
> Bob 12-Jun
> Bob 16-Jun
> Mary 13-Jun
> Mary 16-Jun
> Any ideas?
> Thanks!
> Dave
>|||Hi Dave,
It would have been great if you had given the ddl. Anyways, here is the
solution.
Let me know if this works..
The ddl and ur sample data first
create table t1 (Employee varchar(10), StartDate datetime,
EndDate datetime, Roster varchar(10))
insert into t1 values ('Bob', '12-Jun-06', '24-Jun-06',
'_*___**')
insert into t1 values ('Mary', '12-Jun-06', '24-Jun-06',
'*_*__*_')
And here is the query. You need a temp table so that I don't loop through
that roster or whatever
--I have assumed that the size of the roster is 30 characters. If yit can be
100 or 1000 increase it accordingly
select top 30 identity(int,0,1) as num into #temp from sysobjects
--query
-- This is to remove any ambiguity
set datefirst 7
select Employee, StartDate - datepart(dw,startdate) + num + 1
from t1, #temp
where
substring(Roster,num%len(roster) + 1,1) = '*'
and StartDate - datepart(dw,startdate) + num + 1 between startdate and endda
te
Let me know if this was what you wanted.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi There,
I think this may solve your problem
Try it and let me know if it helped
CREATE TABLE Madness
(Employee varchar(20) not null,
Startdate datetime not null,
EndDate datetime not null,
Roster varchar(14) not null)
INSERT Madness values('Bob', '13 Jun 2006', '18 Jun 2006', '_*___**')
INSERT Madness values('Mary', '13 Jun 2006', '18 Jun 2006', '*_*__*_')
GO
Select identity(int,1,1) myid into Numbers from sysobjects
Select Case When myid<pos Then startdate+myid-1
Else startdate+pos-datepart(dw,startdate)
end ,
Employee, Case when substring(roster,pos,1)='*' then 'Working' else
'Not' End
From
(
Select myid,
case when (datepart(dw,startdate)+myid-1)%datalength(roster)= 0 then
datalength(roster) else
(datepart(dw,startdate)+myid-1)%datalength(roster) end Pos,
Employee,
Startdate,
Enddate,
Roster
>From Madness,Numbers
where myid <= datalength(Roster)
) XY order by 2,1
With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
Omnibuzz wrote:
> Hi Dave,
> It would have been great if you had given the ddl. Anyways, here is the
> solution.
> Let me know if this works..
> The ddl and ur sample data first
>
> create table t1 (Employee varchar(10), StartDate datetime,
> EndDate datetime, Roster varchar(10))
> insert into t1 values ('Bob', '12-Jun-06', '24-Jun-06',
> '_*___**')
> insert into t1 values ('Mary', '12-Jun-06', '24-Jun-06',
> '*_*__*_')
>
> And here is the query. You need a temp table so that I don't loop through
> that roster or whatever
> --I have assumed that the size of the roster is 30 characters. If yit can
be
> 100 or 1000 increase it accordingly
> select top 30 identity(int,0,1) as num into #temp from sysobjects
>
> --query
> -- This is to remove any ambiguity
> set datefirst 7
> select Employee, StartDate - datepart(dw,startdate) + num + 1
> from t1, #temp
> where
> substring(Roster,num%len(roster) + 1,1) = '*'
> and StartDate - datepart(dw,startdate) + num + 1 between startdate and end
date
> Let me know if this was what you wanted.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/sql
A tricky code problem!
I have a report that looks like the following:
record record record null null record record
supress supress supress null record1 null supress
supress supress supress record2 null null supress
This is the best way to describe my data, its 3 rows of records and I want to show record1 and record2 on the top row. I have the other data beneath supressed. I am sure this maybe be possible using SQL code or using a loop or something but I have no idea where to start and have been trawling the web for answers or some code samples but have found none to date as it is quite a specific thing. Its tricky I know but could someone help me please?Hi,
I want to make a page break after section3 in crystal report(ver. 8) after displaying 5 records. I'm using .Dsr report. I am using the following code.
'*******************************
Set Report = New CrystalReport1
Report.Database.AddADOCommand db, cmd
For i = 1 To rs.Recordcount
Set txtObj = Report.Section2.AddTextObject(l, 150, vHight)
txtObj.Width = 300
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True
Set txtObj = Report.Section2.AddTextObject(rs!ITEM_DESC, 400, vHight)
txtObj.Width = 2000
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True
Set txtObj = Report.Section2.AddTextObject(rs!narmst_sItemName, 2800, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True
Set txtObj = Report.Section2.AddTextObject(rs!dlnsp_dItemRate, 4200, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True
Set txtObj = Report.Section2.AddTextObject(rs!dlnsp_dItemQty, 5700, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True
Set txtObj = Report.Section2.AddTextObject(rs!dlnsp_dItemRate * rs!dlnsp_dItemQty, 7100, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True
Set txtObj = Report.Section2.AddTextObject(rs!dlnsp_dItemRate * rs!dlnsp_dItemQty, 8500, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True
If IsNull(rs!VAT_AMT) Then
m = 0
Else
m = Val(rs!VAT_AMT)
End If
Set txtObj = Report.Section2.AddTextObject(m, 9600, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True
Set txtObj = Report.Section2.AddTextObject(Val(rs!dlnsp_dItemRate * rs!dlnsp_dItemQty + m), 11200, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True
If i > 5 Then
Report.Section3.NewPageAfter = True
End If
rs.MoveNext
vHight = vHight + 250
l = l + 1
Next i
'**************************
But it is not working. Please Help me.
with regards,
Arindam
record record record null null record record
supress supress supress null record1 null supress
supress supress supress record2 null null supress
This is the best way to describe my data, its 3 rows of records and I want to show record1 and record2 on the top row. I have the other data beneath supressed. I am sure this maybe be possible using SQL code or using a loop or something but I have no idea where to start and have been trawling the web for answers or some code samples but have found none to date as it is quite a specific thing. Its tricky I know but could someone help me please?Hi,
I want to make a page break after section3 in crystal report(ver. 8) after displaying 5 records. I'm using .Dsr report. I am using the following code.
'*******************************
Set Report = New CrystalReport1
Report.Database.AddADOCommand db, cmd
For i = 1 To rs.Recordcount
Set txtObj = Report.Section2.AddTextObject(l, 150, vHight)
txtObj.Width = 300
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True
Set txtObj = Report.Section2.AddTextObject(rs!ITEM_DESC, 400, vHight)
txtObj.Width = 2000
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True
Set txtObj = Report.Section2.AddTextObject(rs!narmst_sItemName, 2800, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True
Set txtObj = Report.Section2.AddTextObject(rs!dlnsp_dItemRate, 4200, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True
Set txtObj = Report.Section2.AddTextObject(rs!dlnsp_dItemQty, 5700, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True
Set txtObj = Report.Section2.AddTextObject(rs!dlnsp_dItemRate * rs!dlnsp_dItemQty, 7100, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True
Set txtObj = Report.Section2.AddTextObject(rs!dlnsp_dItemRate * rs!dlnsp_dItemQty, 8500, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True
If IsNull(rs!VAT_AMT) Then
m = 0
Else
m = Val(rs!VAT_AMT)
End If
Set txtObj = Report.Section2.AddTextObject(m, 9600, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True
Set txtObj = Report.Section2.AddTextObject(Val(rs!dlnsp_dItemRate * rs!dlnsp_dItemQty + m), 11200, vHight)
txtObj.Width = 1200
txtObj.HorAlignment = crLeftAlign
txtObj.Font.Bold = True
If i > 5 Then
Report.Section3.NewPageAfter = True
End If
rs.MoveNext
vHight = vHight + 250
l = l + 1
Next i
'**************************
But it is not working. Please Help me.
with regards,
Arindam
Monday, March 19, 2012
a text file data source
I would know how to create a report which has as data source a text file
someone can help me?
Hi Maaloul-
To retrieve data directly from a text file, you would need to implement your own custom data extension. A sample data extension can be found here:
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
-Jon
|||Another option is to create an ODBC connection to the text file. This has been written up several times.a text file data source
I would know how to create a report which has as data source a text file
someone can help me?
Hi Maaloul-
To retrieve data directly from a text file, you would need to implement your own custom data extension. A sample data extension can be found here:
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
-Jon
|||Another option is to create an ODBC connection to the text file. This has been written up several times.A text file data source
Hi all,
I have to write a report that uses a .txt file as a data-source and I
am not sure how to do that.
Anyone know where I can find out how to set this up? ODBC Connection?
The .txt file format is like this...(top 5 rows)
20040881 2004/12/22 01:30:12 NONIMPACT
20040883 2004/12/22 15:02:12 NONIMPACT
20040884 2004/12/22 17:52:12 NONIMPACT
20040885 2004/12/23 11:20:12 NONIMPACT
20040891 2004/12/23 15:10:12 NONIMPACT
and so one....
Any help would be great!!!!!!!!!!!!
Thanks, KerrieI've never tried to reach a flat text file, but I do not think that SRS
supports connecting directly to a flat file...(correct me if I am
wrong).
You may have to write your own data processing extension that can
connect to the files you want and extract the data therein. Hope this
helps!
Lance M.
Kerrie wrote:
> Hi all,
> I have to write a report that uses a .txt file as a data-source and I
> am not sure how to do that.
> Anyone know where I can find out how to set this up? ODBC Connection?
> The .txt file format is like this...(top 5 rows)
> 20040881 2004/12/22 01:30:12 NONIMPACT
> 20040883 2004/12/22 15:02:12 NONIMPACT
> 20040884 2004/12/22 17:52:12 NONIMPACT
> 20040885 2004/12/23 11:20:12 NONIMPACT
> 20040891 2004/12/23 15:10:12 NONIMPACT
> and so one....
> Any help would be great!!!!!!!!!!!!
> Thanks, Kerrie|||Create a linked data source of type CSV to the flat file and use it in
your report against the text file.
Lance M wrote:
> I've never tried to reach a flat text file, but I do not think that SRS
> supports connecting directly to a flat file...(correct me if I am
> wrong).
> You may have to write your own data processing extension that can
> connect to the files you want and extract the data therein. Hope this
> helps!
> Lance M.
> Kerrie wrote:
> > Hi all,
> > I have to write a report that uses a .txt file as a data-source and I
> > am not sure how to do that.
> >
> > Anyone know where I can find out how to set this up? ODBC Connection?
> >
> > The .txt file format is like this...(top 5 rows)
> > 20040881 2004/12/22 01:30:12 NONIMPACT
> > 20040883 2004/12/22 15:02:12 NONIMPACT
> > 20040884 2004/12/22 17:52:12 NONIMPACT
> > 20040885 2004/12/23 11:20:12 NONIMPACT
> > 20040891 2004/12/23 15:10:12 NONIMPACT
> >
> > and so one....
> >
> > Any help would be great!!!!!!!!!!!!
> >
> > Thanks, Kerrie
I have to write a report that uses a .txt file as a data-source and I
am not sure how to do that.
Anyone know where I can find out how to set this up? ODBC Connection?
The .txt file format is like this...(top 5 rows)
20040881 2004/12/22 01:30:12 NONIMPACT
20040883 2004/12/22 15:02:12 NONIMPACT
20040884 2004/12/22 17:52:12 NONIMPACT
20040885 2004/12/23 11:20:12 NONIMPACT
20040891 2004/12/23 15:10:12 NONIMPACT
and so one....
Any help would be great!!!!!!!!!!!!
Thanks, KerrieI've never tried to reach a flat text file, but I do not think that SRS
supports connecting directly to a flat file...(correct me if I am
wrong).
You may have to write your own data processing extension that can
connect to the files you want and extract the data therein. Hope this
helps!
Lance M.
Kerrie wrote:
> Hi all,
> I have to write a report that uses a .txt file as a data-source and I
> am not sure how to do that.
> Anyone know where I can find out how to set this up? ODBC Connection?
> The .txt file format is like this...(top 5 rows)
> 20040881 2004/12/22 01:30:12 NONIMPACT
> 20040883 2004/12/22 15:02:12 NONIMPACT
> 20040884 2004/12/22 17:52:12 NONIMPACT
> 20040885 2004/12/23 11:20:12 NONIMPACT
> 20040891 2004/12/23 15:10:12 NONIMPACT
> and so one....
> Any help would be great!!!!!!!!!!!!
> Thanks, Kerrie|||Create a linked data source of type CSV to the flat file and use it in
your report against the text file.
Lance M wrote:
> I've never tried to reach a flat text file, but I do not think that SRS
> supports connecting directly to a flat file...(correct me if I am
> wrong).
> You may have to write your own data processing extension that can
> connect to the files you want and extract the data therein. Hope this
> helps!
> Lance M.
> Kerrie wrote:
> > Hi all,
> > I have to write a report that uses a .txt file as a data-source and I
> > am not sure how to do that.
> >
> > Anyone know where I can find out how to set this up? ODBC Connection?
> >
> > The .txt file format is like this...(top 5 rows)
> > 20040881 2004/12/22 01:30:12 NONIMPACT
> > 20040883 2004/12/22 15:02:12 NONIMPACT
> > 20040884 2004/12/22 17:52:12 NONIMPACT
> > 20040885 2004/12/23 11:20:12 NONIMPACT
> > 20040891 2004/12/23 15:10:12 NONIMPACT
> >
> > and so one....
> >
> > Any help would be great!!!!!!!!!!!!
> >
> > Thanks, Kerrie
A Template for Reports
Hi,
Does anyone know how to create a template for reports that can be
linked to every report. Changes in the template would then be
automatically reflected in every report connected to the template.
This is a similar paradigm as a css style sheet. There is a
description of way to do this in "Hitchhiker's Guide to SQL Server 2000
Reporting Services", but it is rather cryptic and involves a lot of
complex hacks.
Thanks,
Woody
MCADA simplier approach may be to store your "template" information in a table.
Create a template rdl that pulls the information, such as a Page Header that
displays your corp. image lets say. If you need to ever change it, you just
need to overwrite the record in the table.
Obviously, everyone who designs reports would have to use the template. To
ensure that everyone, in fact, does use the same template report, you can
have them overwrite the default template with the new template. The following
is the path to the default template.
C:\Program Files\Microsoft SQL Server\80\Tools\Report
Designer\ProjectItems\ReportProject\Report.rdl
Hope this helps!
"lwoodyiii" wrote:
> Hi,
> Does anyone know how to create a template for reports that can be
> linked to every report. Changes in the template would then be
> automatically reflected in every report connected to the template.
> This is a similar paradigm as a css style sheet. There is a
> description of way to do this in "Hitchhiker's Guide to SQL Server 2000
> Reporting Services", but it is rather cryptic and involves a lot of
> complex hacks.
> Thanks,
> Woody
> MCAD
>|||Thanks for the help :)!!
Does anyone know how to create a template for reports that can be
linked to every report. Changes in the template would then be
automatically reflected in every report connected to the template.
This is a similar paradigm as a css style sheet. There is a
description of way to do this in "Hitchhiker's Guide to SQL Server 2000
Reporting Services", but it is rather cryptic and involves a lot of
complex hacks.
Thanks,
Woody
MCADA simplier approach may be to store your "template" information in a table.
Create a template rdl that pulls the information, such as a Page Header that
displays your corp. image lets say. If you need to ever change it, you just
need to overwrite the record in the table.
Obviously, everyone who designs reports would have to use the template. To
ensure that everyone, in fact, does use the same template report, you can
have them overwrite the default template with the new template. The following
is the path to the default template.
C:\Program Files\Microsoft SQL Server\80\Tools\Report
Designer\ProjectItems\ReportProject\Report.rdl
Hope this helps!
"lwoodyiii" wrote:
> Hi,
> Does anyone know how to create a template for reports that can be
> linked to every report. Changes in the template would then be
> automatically reflected in every report connected to the template.
> This is a similar paradigm as a css style sheet. There is a
> description of way to do this in "Hitchhiker's Guide to SQL Server 2000
> Reporting Services", but it is rather cryptic and involves a lot of
> complex hacks.
> Thanks,
> Woody
> MCAD
>|||Thanks for the help :)!!
a strange problem
I made a some reports. set their parameterss and passed them while invoking report. At report creation time I check the INTEGRATED SECURITY checkbox so it does not ask for password when pressing ther "invoke report" button. (expectds behaviour).
But when I executed the same exe on some other machine. having similar database as mine. by clikcing the invoke report button, a box comes up asking the following informtion.
Server name:
user :
password :
database :
how to solve it...Open the report and do verify database
But when I executed the same exe on some other machine. having similar database as mine. by clikcing the invoke report button, a box comes up asking the following informtion.
Server name:
user :
password :
database :
how to solve it...Open the report and do verify database
Sunday, March 11, 2012
a SQL string
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.
> > >> >> >> >> >> >>
> > >> >> >> >> >> >
> > >> >> >> >> >> >
> > >> >> >> >> >> >.
> > >> >> >> >> >> >
> > >> >> >> >> >
> > >> >> >> >> >
> > >> >> >> >> >.
> > >> >> >> >> >
> > >> >> >> >
> > >> >> >> >
> > >> >> >> >.
> > >> >> >> >
> > >> >> >
> > >> >> >
> > >> >> >.
> > >> >> >
> > >> >
> > >> >
> > >> >.
> > >> >
> > >
> > >
> > >.
> > >
>
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.
> > >> >> >> >> >> >>
> > >> >> >> >> >> >
> > >> >> >> >> >> >
> > >> >> >> >> >> >.
> > >> >> >> >> >> >
> > >> >> >> >> >
> > >> >> >> >> >
> > >> >> >> >> >.
> > >> >> >> >> >
> > >> >> >> >
> > >> >> >> >
> > >> >> >> >.
> > >> >> >> >
> > >> >> >
> > >> >> >
> > >> >> >.
> > >> >> >
> > >> >
> > >> >
> > >> >.
> > >> >
> > >
> > >
> > >.
> > >
>
Subscribe to:
Posts (Atom)