Hello,
I'm trying to change the color of a chart region based on a report parameter
and I'm not having any luck. Can any one tell me where I change this? I see
all of the available color palattes, but I don't want to use those. The
regions need to be a specific color based on the report parameter option that
was choosen by the user.
ThanksYou will need RS 2000 with SP1 or later. In that case, you can control the
color of data points based on an expression. Please check the "Chart Styles"
section under 4.1.3 in the SP1 Readme:
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_chart_enhancements
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"dataGirl" <dataGirl@.discussions.microsoft.com> wrote in message
news:324B9D30-B779-4402-9E68-9FC8FE0941FD@.microsoft.com...
> Hello,
> I'm trying to change the color of a chart region based on a report
> parameter
> and I'm not having any luck. Can any one tell me where I change this? I
> see
> all of the available color palattes, but I don't want to use those. The
> regions need to be a specific color based on the report parameter option
> that
> was choosen by the user.
> Thankssql
Showing posts with label parameter. Show all posts
Showing posts with label parameter. Show all posts
Tuesday, March 27, 2012
Changing color of the report parameter viewer tool bar
Hey peeps,
I am wondering to know if there is a way to change the color of the SQL
Report parameter strip when the report is rendered. I tried changing a few
classes in the Reportingservice.css file but those changes reflect only in
the outer region(the container of the report, not the frame). When the report
is rendered, a new css file named "8.00.743.00HtmlViewer.css" is generated
dynamically which is holding control to the class files used by different
items in that tool bar. How would I get access to this newly generated css
file or howelse could I change the color of the tool bar that contains items
like "Find", "Export" buttons. Awaiting your suggestion.
Thanks
BalajiThis is a multi-part message in MIME format.
--=_NextPart_000_007D_01C534BB.3A051EF0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
RS SP1 has additional url access parameter Stylesheet that specifies a =style sheet to be applied to the HTML viewer..
You need to make your own file and save it to 'C:\Program =Files\Microsoft SQL Server\MSSQL\Reporting =Services\ReportServer\styles\custom.css'
Here is example:
http://servername/reportserver?/SampleReports/Report1&rs:Command=3DRender=
&rc:Stylesheet=3DCustom
The following style makes parameters row blue:
.MenuBarBkGnd
{
background-color: blue
}
PS:
New in SP2. The <HTMLViewerStyleSheet> property has been added to the =Reporting Services configuration file so that you can specify a new =default style sheet for your HTML viewer.
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message =news:C57EAA6A-1B94-4DF4-9F13-01E3CD433401@.microsoft.com...
> Hey peeps,
> > I am wondering to know if there is a way to change the color of the =SQL > Report parameter strip when the report is rendered. I tried changing a =few > classes in the Reportingservice.css file but those changes reflect =only in > the outer region(the container of the report, not the frame). When the =report > is rendered, a new css file named "8.00.743.00HtmlViewer.css" is =generated > dynamically which is holding control to the class files used by =different > items in that tool bar. How would I get access to this newly generated =css > file or howelse could I change the color of the tool bar that contains =items > like "Find", "Export" buttons. Awaiting your suggestion.
> > Thanks
> Balaji
--=_NextPart_000_007D_01C534BB.3A051EF0
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
RS SP1 has additional url access =parameter Stylesheet that specifies a style sheet to be applied to the HTML viewer..
You need to make your own file and save =it to 'C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\styles\custom.css'
Here is example:
http://servername/reportserver?/SampleReports/Report1&rs:Com=mand=3DRender&rc:Stylesheet=3DCustom
The following style makes parameters =row blue:
.MenuBarBkGnd{ background-color: blue}
PS:
New in SP2. The property has =been added to the Reporting Services configuration file so that you can =specify a new default style sheet for your HTML viewer.
"Balaji" wrote in message news:C57EAA6A-1B94-4DF4-9F13-01E3CD433401@.microsoft.com...> =Hey peeps,> > I am wondering to know if there is a way to =change the color of the SQL > Report parameter strip when the report is =rendered. I tried changing a few > classes in the Reportingservice.css file =but those changes reflect only in > the outer region(the container of the =report, not the frame). When the report > is rendered, a new css file =named "8.00.743.00HtmlViewer.css" is generated > dynamically which is =holding control to the class files used by different > items in that tool =bar. How would I get access to this newly generated css > file or =howelse could I change the color of the tool bar that contains items > =like "Find", "Export" buttons. Awaiting your =suggestion.> > Thanks> Balaji
--=_NextPart_000_007D_01C534BB.3A051EF0--
I am wondering to know if there is a way to change the color of the SQL
Report parameter strip when the report is rendered. I tried changing a few
classes in the Reportingservice.css file but those changes reflect only in
the outer region(the container of the report, not the frame). When the report
is rendered, a new css file named "8.00.743.00HtmlViewer.css" is generated
dynamically which is holding control to the class files used by different
items in that tool bar. How would I get access to this newly generated css
file or howelse could I change the color of the tool bar that contains items
like "Find", "Export" buttons. Awaiting your suggestion.
Thanks
BalajiThis is a multi-part message in MIME format.
--=_NextPart_000_007D_01C534BB.3A051EF0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
RS SP1 has additional url access parameter Stylesheet that specifies a =style sheet to be applied to the HTML viewer..
You need to make your own file and save it to 'C:\Program =Files\Microsoft SQL Server\MSSQL\Reporting =Services\ReportServer\styles\custom.css'
Here is example:
http://servername/reportserver?/SampleReports/Report1&rs:Command=3DRender=
&rc:Stylesheet=3DCustom
The following style makes parameters row blue:
.MenuBarBkGnd
{
background-color: blue
}
PS:
New in SP2. The <HTMLViewerStyleSheet> property has been added to the =Reporting Services configuration file so that you can specify a new =default style sheet for your HTML viewer.
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message =news:C57EAA6A-1B94-4DF4-9F13-01E3CD433401@.microsoft.com...
> Hey peeps,
> > I am wondering to know if there is a way to change the color of the =SQL > Report parameter strip when the report is rendered. I tried changing a =few > classes in the Reportingservice.css file but those changes reflect =only in > the outer region(the container of the report, not the frame). When the =report > is rendered, a new css file named "8.00.743.00HtmlViewer.css" is =generated > dynamically which is holding control to the class files used by =different > items in that tool bar. How would I get access to this newly generated =css > file or howelse could I change the color of the tool bar that contains =items > like "Find", "Export" buttons. Awaiting your suggestion.
> > Thanks
> Balaji
--=_NextPart_000_007D_01C534BB.3A051EF0
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
RS SP1 has additional url access =parameter Stylesheet that specifies a style sheet to be applied to the HTML viewer..
You need to make your own file and save =it to 'C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\styles\custom.css'
Here is example:
http://servername/reportserver?/SampleReports/Report1&rs:Com=mand=3DRender&rc:Stylesheet=3DCustom
The following style makes parameters =row blue:
.MenuBarBkGnd{ background-color: blue}
PS:
New in SP2. The property has =been added to the Reporting Services configuration file so that you can =specify a new default style sheet for your HTML viewer.
"Balaji"
--=_NextPart_000_007D_01C534BB.3A051EF0--
Sunday, March 25, 2012
Changing char length in stored procedures
Hello all, I'm using SQL Server 2000 and have about 250 stored procedures that use an EMPLID parameter or variable of type varchar with a length of 4. I need to change the length to 10 instead and would like to do so without having to open every sp for editing. Is there a way to do this through SQL Server 2000? Does anyone have a script to do this? Any help would be appreciated.I'd use SQL Enterprise Mangler to script all of the stored procedures, then a text editor to make the changes. It should be a simple replace operation, possibly using a regexp if you aren't careful about how you format your declarations.
-PatP
-PatP
Changing BLL parameter
I have inherited a web site from another developer. I am new to .net programming. Anyway I have had to add a parameter to an sql procedure and I thought i had made the change in the business logic layer correctly. I am using gridview to display and export to an excel spreadsheet. After my changes my page lets me select my parameters, but now my page come up blank, no error messages the only thing on the page are the save and export buttons.
Please help what have i missed.
Steve
You should provide more details, probably give some code excerpts for others to make suggestions.sqlSunday, March 11, 2012
Changed a parameter to multi-value and now it doesn't work
I have a report with multiple parameters that the user can specifiy before
running the report. One of them is a field that the user can type in a value
for. It's defined as a string and allows a blank value in the event the user
does not want to enter anything. It works fine when not multi-value. But
when I change it to multi-value, the report brings back nothing when the user
does not enter any values.
The related dataset code for this field (tracking_number) is
...
WHERE (D.name IN (@.name) OR ('-1' IN (@.name)))
AND (D.tracking_number IN (@.tracking_number) OR '' IN (@.tracking_number))
AND (D.promo_code IN (@.promo_code) or '-1' IN (@.promo_code))
...
Any help would be appreciated.
StephanieRemove the multi-value property. that is not really meant for a text field,
only for instances where one might choose from multiple values of a drop down
list.
"Stephanie" wrote:
> I have a report with multiple parameters that the user can specifiy before
> running the report. One of them is a field that the user can type in a value
> for. It's defined as a string and allows a blank value in the event the user
> does not want to enter anything. It works fine when not multi-value. But
> when I change it to multi-value, the report brings back nothing when the user
> does not enter any values.
> The related dataset code for this field (tracking_number) is
> ...
> WHERE (D.name IN (@.name) OR ('-1' IN (@.name)))
> AND (D.tracking_number IN (@.tracking_number) OR '' IN (@.tracking_number))
> AND (D.promo_code IN (@.promo_code) or '-1' IN (@.promo_code))
> ...
> Any help would be appreciated.
> Stephanie|||When I do that, I cannot add multple values for the field.
I've tried:
x,y
x y
'x','y'
Suggestions?
"Carl Henthorn" wrote:
> Remove the multi-value property. that is not really meant for a text field,
> only for instances where one might choose from multiple values of a drop down
> list.
> "Stephanie" wrote:
> > I have a report with multiple parameters that the user can specifiy before
> > running the report. One of them is a field that the user can type in a value
> > for. It's defined as a string and allows a blank value in the event the user
> > does not want to enter anything. It works fine when not multi-value. But
> > when I change it to multi-value, the report brings back nothing when the user
> > does not enter any values.
> >
> > The related dataset code for this field (tracking_number) is
> > ...
> > WHERE (D.name IN (@.name) OR ('-1' IN (@.name)))
> > AND (D.tracking_number IN (@.tracking_number) OR '' IN (@.tracking_number))
> > AND (D.promo_code IN (@.promo_code) or '-1' IN (@.promo_code))
> > ...
> >
> > Any help would be appreciated.
> >
> > Stephanie|||Everything that a person enters into that field will be considered one
string. you have to parse out the string in the sproc to break it up into its
component pieces.
Without seeing what you are doing, I dont understand why you "cant add
multiple values for the field." tha field just wraps a single quote around
whatever is there and passes it in as the parameter.
in my test, I put in 1,2,3,4,5 into my text box. what was passed in to my
sproc was '1,2,3,4,5' <note the addition of the single quotes>. If this is
not happening for you, you may want to make things easier on you and just
have a multi-valued dropdown list. At least that way you are not hostage to
your users spelling ability.
"Stephanie" wrote:
> When I do that, I cannot add multple values for the field.
> I've tried:
> x,y
> x y
> 'x','y'
> Suggestions?
> "Carl Henthorn" wrote:
> > Remove the multi-value property. that is not really meant for a text field,
> > only for instances where one might choose from multiple values of a drop down
> > list.
> >
> > "Stephanie" wrote:
> >
> > > I have a report with multiple parameters that the user can specifiy before
> > > running the report. One of them is a field that the user can type in a value
> > > for. It's defined as a string and allows a blank value in the event the user
> > > does not want to enter anything. It works fine when not multi-value. But
> > > when I change it to multi-value, the report brings back nothing when the user
> > > does not enter any values.
> > >
> > > The related dataset code for this field (tracking_number) is
> > > ...
> > > WHERE (D.name IN (@.name) OR ('-1' IN (@.name)))
> > > AND (D.tracking_number IN (@.tracking_number) OR '' IN (@.tracking_number))
> > > AND (D.promo_code IN (@.promo_code) or '-1' IN (@.promo_code))
> > > ...
> > >
> > > Any help would be appreciated.
> > >
> > > Stephanie|||The problem is that this is a string, not an integer. So a single quote at
the beginning and the end is not helpful. The user does not what a drop-down
list because the number of values that would be there would be very large.
They want to type in something like: CIM070524001,CIM070522002. They want to
be able to type in one or multiple values.
Can you test with a string and let me know how that goes? I just can't get
it to work.
"Carl Henthorn" wrote:
> Everything that a person enters into that field will be considered one
> string. you have to parse out the string in the sproc to break it up into its
> component pieces.
> Without seeing what you are doing, I dont understand why you "cant add
> multiple values for the field." tha field just wraps a single quote around
> whatever is there and passes it in as the parameter.
> in my test, I put in 1,2,3,4,5 into my text box. what was passed in to my
> sproc was '1,2,3,4,5' <note the addition of the single quotes>. If this is
> not happening for you, you may want to make things easier on you and just
> have a multi-valued dropdown list. At least that way you are not hostage to
> your users spelling ability.
> "Stephanie" wrote:
> > When I do that, I cannot add multple values for the field.
> >
> > I've tried:
> >
> > x,y
> > x y
> > 'x','y'
> >
> > Suggestions?
> >
> > "Carl Henthorn" wrote:
> >
> > > Remove the multi-value property. that is not really meant for a text field,
> > > only for instances where one might choose from multiple values of a drop down
> > > list.
> > >
> > > "Stephanie" wrote:
> > >
> > > > I have a report with multiple parameters that the user can specifiy before
> > > > running the report. One of them is a field that the user can type in a value
> > > > for. It's defined as a string and allows a blank value in the event the user
> > > > does not want to enter anything. It works fine when not multi-value. But
> > > > when I change it to multi-value, the report brings back nothing when the user
> > > > does not enter any values.
> > > >
> > > > The related dataset code for this field (tracking_number) is
> > > > ...
> > > > WHERE (D.name IN (@.name) OR ('-1' IN (@.name)))
> > > > AND (D.tracking_number IN (@.tracking_number) OR '' IN (@.tracking_number))
> > > > AND (D.promo_code IN (@.promo_code) or '-1' IN (@.promo_code))
> > > > ...
> > > >
> > > > Any help would be appreciated.
> > > >
> > > > Stephanie
running the report. One of them is a field that the user can type in a value
for. It's defined as a string and allows a blank value in the event the user
does not want to enter anything. It works fine when not multi-value. But
when I change it to multi-value, the report brings back nothing when the user
does not enter any values.
The related dataset code for this field (tracking_number) is
...
WHERE (D.name IN (@.name) OR ('-1' IN (@.name)))
AND (D.tracking_number IN (@.tracking_number) OR '' IN (@.tracking_number))
AND (D.promo_code IN (@.promo_code) or '-1' IN (@.promo_code))
...
Any help would be appreciated.
StephanieRemove the multi-value property. that is not really meant for a text field,
only for instances where one might choose from multiple values of a drop down
list.
"Stephanie" wrote:
> I have a report with multiple parameters that the user can specifiy before
> running the report. One of them is a field that the user can type in a value
> for. It's defined as a string and allows a blank value in the event the user
> does not want to enter anything. It works fine when not multi-value. But
> when I change it to multi-value, the report brings back nothing when the user
> does not enter any values.
> The related dataset code for this field (tracking_number) is
> ...
> WHERE (D.name IN (@.name) OR ('-1' IN (@.name)))
> AND (D.tracking_number IN (@.tracking_number) OR '' IN (@.tracking_number))
> AND (D.promo_code IN (@.promo_code) or '-1' IN (@.promo_code))
> ...
> Any help would be appreciated.
> Stephanie|||When I do that, I cannot add multple values for the field.
I've tried:
x,y
x y
'x','y'
Suggestions?
"Carl Henthorn" wrote:
> Remove the multi-value property. that is not really meant for a text field,
> only for instances where one might choose from multiple values of a drop down
> list.
> "Stephanie" wrote:
> > I have a report with multiple parameters that the user can specifiy before
> > running the report. One of them is a field that the user can type in a value
> > for. It's defined as a string and allows a blank value in the event the user
> > does not want to enter anything. It works fine when not multi-value. But
> > when I change it to multi-value, the report brings back nothing when the user
> > does not enter any values.
> >
> > The related dataset code for this field (tracking_number) is
> > ...
> > WHERE (D.name IN (@.name) OR ('-1' IN (@.name)))
> > AND (D.tracking_number IN (@.tracking_number) OR '' IN (@.tracking_number))
> > AND (D.promo_code IN (@.promo_code) or '-1' IN (@.promo_code))
> > ...
> >
> > Any help would be appreciated.
> >
> > Stephanie|||Everything that a person enters into that field will be considered one
string. you have to parse out the string in the sproc to break it up into its
component pieces.
Without seeing what you are doing, I dont understand why you "cant add
multiple values for the field." tha field just wraps a single quote around
whatever is there and passes it in as the parameter.
in my test, I put in 1,2,3,4,5 into my text box. what was passed in to my
sproc was '1,2,3,4,5' <note the addition of the single quotes>. If this is
not happening for you, you may want to make things easier on you and just
have a multi-valued dropdown list. At least that way you are not hostage to
your users spelling ability.
"Stephanie" wrote:
> When I do that, I cannot add multple values for the field.
> I've tried:
> x,y
> x y
> 'x','y'
> Suggestions?
> "Carl Henthorn" wrote:
> > Remove the multi-value property. that is not really meant for a text field,
> > only for instances where one might choose from multiple values of a drop down
> > list.
> >
> > "Stephanie" wrote:
> >
> > > I have a report with multiple parameters that the user can specifiy before
> > > running the report. One of them is a field that the user can type in a value
> > > for. It's defined as a string and allows a blank value in the event the user
> > > does not want to enter anything. It works fine when not multi-value. But
> > > when I change it to multi-value, the report brings back nothing when the user
> > > does not enter any values.
> > >
> > > The related dataset code for this field (tracking_number) is
> > > ...
> > > WHERE (D.name IN (@.name) OR ('-1' IN (@.name)))
> > > AND (D.tracking_number IN (@.tracking_number) OR '' IN (@.tracking_number))
> > > AND (D.promo_code IN (@.promo_code) or '-1' IN (@.promo_code))
> > > ...
> > >
> > > Any help would be appreciated.
> > >
> > > Stephanie|||The problem is that this is a string, not an integer. So a single quote at
the beginning and the end is not helpful. The user does not what a drop-down
list because the number of values that would be there would be very large.
They want to type in something like: CIM070524001,CIM070522002. They want to
be able to type in one or multiple values.
Can you test with a string and let me know how that goes? I just can't get
it to work.
"Carl Henthorn" wrote:
> Everything that a person enters into that field will be considered one
> string. you have to parse out the string in the sproc to break it up into its
> component pieces.
> Without seeing what you are doing, I dont understand why you "cant add
> multiple values for the field." tha field just wraps a single quote around
> whatever is there and passes it in as the parameter.
> in my test, I put in 1,2,3,4,5 into my text box. what was passed in to my
> sproc was '1,2,3,4,5' <note the addition of the single quotes>. If this is
> not happening for you, you may want to make things easier on you and just
> have a multi-valued dropdown list. At least that way you are not hostage to
> your users spelling ability.
> "Stephanie" wrote:
> > When I do that, I cannot add multple values for the field.
> >
> > I've tried:
> >
> > x,y
> > x y
> > 'x','y'
> >
> > Suggestions?
> >
> > "Carl Henthorn" wrote:
> >
> > > Remove the multi-value property. that is not really meant for a text field,
> > > only for instances where one might choose from multiple values of a drop down
> > > list.
> > >
> > > "Stephanie" wrote:
> > >
> > > > I have a report with multiple parameters that the user can specifiy before
> > > > running the report. One of them is a field that the user can type in a value
> > > > for. It's defined as a string and allows a blank value in the event the user
> > > > does not want to enter anything. It works fine when not multi-value. But
> > > > when I change it to multi-value, the report brings back nothing when the user
> > > > does not enter any values.
> > > >
> > > > The related dataset code for this field (tracking_number) is
> > > > ...
> > > > WHERE (D.name IN (@.name) OR ('-1' IN (@.name)))
> > > > AND (D.tracking_number IN (@.tracking_number) OR '' IN (@.tracking_number))
> > > > AND (D.promo_code IN (@.promo_code) or '-1' IN (@.promo_code))
> > > > ...
> > > >
> > > > Any help would be appreciated.
> > > >
> > > > Stephanie
Thursday, March 8, 2012
Change TSQl statement using case
Hello all, Thanks in advance to contributions.
I would like to be able to manipulate myTSQL in a stored procedure, based
on a value in a parameter, using the case statement.
I want to avoid Begin..End constructs
My code is such...
select field1,field2 etc etc
from Order o
inner join tablex oa on o.order_id = oa.order_id
-- etc etc p to 8 joins
where
-- some predicate
-- then the bit I want to case
case @.MyParameter
when 'IS_MANAGER' then (and o.reason = 'T1' or o.reason = 'T2')
when 'DE_MANAGER' then (and o.reason = 'T3')
end
So, what I am trying to do is change the overall selection predicate based
on the
value of the @.MyParameter. I am trying to avoid wrapping the whole code
block up into seperate Begin..End constructs if possible as it will make the
SP very big and ugly.
Amy I trying to break SLQ rules here?
Cheers
Scotchy
eg.Scotchy wrote:
> So, what I am trying to do is change the overall selection predicate
> based on the
> value of the @.MyParameter. I am trying to avoid wrapping the whole
> code block up into seperate Begin..End constructs if possible as it
> will make the SP very big and ugly.
> Amy I trying to break SLQ rules here?
That is indeed not possible. You have the same problem as a couple of
threads above, check "Stored procedure using dynamic WHERE statement"
for the offered solutions.
HTH,
Stijn Verrept.|||yep, I was clutching a straws and hoping :-)
cheers
"Stijn Verrept" wrote:
> Scotchy wrote:
>
> That is indeed not possible. You have the same problem as a couple of
> threads above, check "Stored procedure using dynamic WHERE statement"
> for the offered solutions.
> --
> HTH,
> Stijn Verrept.
>|||declare @.parm int
declare @.sql nvarchar(400)
set @.parm = 1
set @.sql = 'select * from table1 where Type IS NOT NULL and '
if ( @.parm = 1 )
set @.sql = @.sql + 'type = ''t1'''
else if ( @.parm = 2 )
set @.sql = @.sql + 'type = ''t2'''
else
print 'error'
exec (@.sql)
William Stacey [MVP]
"Scotchy" <Scotchy@.discussions.microsoft.com> wrote in message
news:5CB3CA78-0973-4ED8-B308-7B3028CBB8F2@.microsoft.com...
> Hello all, Thanks in advance to contributions.
> I would like to be able to manipulate myTSQL in a stored procedure, based
> on a value in a parameter, using the case statement.
> I want to avoid Begin..End constructs
> My code is such...
> select field1,field2 etc etc
> from Order o
> inner join tablex oa on o.order_id = oa.order_id
> -- etc etc p to 8 joins
> where
> -- some predicate
> -- then the bit I want to case
> case @.MyParameter
> when 'IS_MANAGER' then (and o.reason = 'T1' or o.reason = 'T2')
> when 'DE_MANAGER' then (and o.reason = 'T3')
> end
> So, what I am trying to do is change the overall selection predicate based
> on the
> value of the @.MyParameter. I am trying to avoid wrapping the whole code
> block up into seperate Begin..End constructs if possible as it will make
> the
> SP very big and ugly.
> Amy I trying to break SLQ rules here?
> Cheers
> Scotchy
>
>
>
> eg.|||-- Couple of things: do you need to CASE? Can't you do something like this
?
USE Northwind
GO
DECLARE @.EmployeeID INT
SET @.EmployeeID = 2
-- Normal query
SELECT *
FROM orders
WHERE EmployeeID = @.EmployeeID
-- You can use CASE in queries. Think of them like a function which returns
a value, so you have to put it on one side of an equals:
SELECT *
FROM orders
WHERE CustomerID =
CASE @.EmployeeID
WHEN 1 THEN 'ERNSH'
WHEN 2 THEN 'BLONP'
ELSE ''
END
-- Or multiple criteria. You could even nest your case statements.
SELECT *
FROM orders
WHERE CustomerID =
CASE
WHEN @.EmployeeID = 1 AND OrderDate < '19970101' THEN 'ERNSH'
WHEN @.EmployeeID = 2 AND OrderDate < '19970101' THEN 'BLONP'
ELSE ''
END
-- Let me know how you get on.
--
--
-- Damien
"Scotchy" wrote:
> Hello all, Thanks in advance to contributions.
> I would like to be able to manipulate myTSQL in a stored procedure, based
> on a value in a parameter, using the case statement.
> I want to avoid Begin..End constructs
> My code is such...
> select field1,field2 etc etc
> from Order o
> inner join tablex oa on o.order_id = oa.order_id
> -- etc etc p to 8 joins
> where
> -- some predicate
> -- then the bit I want to case
> case @.MyParameter
> when 'IS_MANAGER' then (and o.reason = 'T1' or o.reason = 'T2')
> when 'DE_MANAGER' then (and o.reason = 'T3')
> end
> So, what I am trying to do is change the overall selection predicate based
> on the
> value of the @.MyParameter. I am trying to avoid wrapping the whole code
> block up into seperate Begin..End constructs if possible as it will make t
he
> SP very big and ugly.
> Amy I trying to break SLQ rules here?
> Cheers
> Scotchy
>
>
>
> eg.|||On Tue, 20 Dec 2005 18:07:01 -0800, Scotchy wrote:
>Hello all, Thanks in advance to contributions.
>I would like to be able to manipulate myTSQL in a stored procedure, based
>on a value in a parameter, using the case statement.
>I want to avoid Begin..End constructs
>My code is such...
>select field1,field2 etc etc
>from Order o
>inner join tablex oa on o.order_id = oa.order_id
>-- etc etc p to 8 joins
>where
> -- some predicate
> -- then the bit I want to case
> case @.MyParameter
> when 'IS_MANAGER' then (and o.reason = 'T1' or o.reason = 'T2')
> when 'DE_MANAGER' then (and o.reason = 'T3')
> end
>So, what I am trying to do is change the overall selection predicate based
>on the
>value of the @.MyParameter. I am trying to avoid wrapping the whole code
>block up into seperate Begin..End constructs if possible as it will make th
e
>SP very big and ugly.
>Amy I trying to break SLQ rules here?
Hi Scotchy,
CASE is an expression, not a statement. Check the examples in Books
Online to get a feeling for what you can accomplish with CASE. And never
think that CASE in SQL Server is similar to CASE in languages such as C.
For your situation, a combination of OR and AND works better than a
CASE:
WHERE ...
AND ( (@.MyParameter = 'IS_MANAGER' AND o.reason IN ('T1', 'T2') )
OR (@.MyParameter = 'DE_MANAGER' AND o.reason = 'T3' ))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
I would like to be able to manipulate myTSQL in a stored procedure, based
on a value in a parameter, using the case statement.
I want to avoid Begin..End constructs
My code is such...
select field1,field2 etc etc
from Order o
inner join tablex oa on o.order_id = oa.order_id
-- etc etc p to 8 joins
where
-- some predicate
-- then the bit I want to case
case @.MyParameter
when 'IS_MANAGER' then (and o.reason = 'T1' or o.reason = 'T2')
when 'DE_MANAGER' then (and o.reason = 'T3')
end
So, what I am trying to do is change the overall selection predicate based
on the
value of the @.MyParameter. I am trying to avoid wrapping the whole code
block up into seperate Begin..End constructs if possible as it will make the
SP very big and ugly.
Amy I trying to break SLQ rules here?
Cheers
Scotchy
eg.Scotchy wrote:
> So, what I am trying to do is change the overall selection predicate
> based on the
> value of the @.MyParameter. I am trying to avoid wrapping the whole
> code block up into seperate Begin..End constructs if possible as it
> will make the SP very big and ugly.
> Amy I trying to break SLQ rules here?
That is indeed not possible. You have the same problem as a couple of
threads above, check "Stored procedure using dynamic WHERE statement"
for the offered solutions.
HTH,
Stijn Verrept.|||yep, I was clutching a straws and hoping :-)
cheers
"Stijn Verrept" wrote:
> Scotchy wrote:
>
> That is indeed not possible. You have the same problem as a couple of
> threads above, check "Stored procedure using dynamic WHERE statement"
> for the offered solutions.
> --
> HTH,
> Stijn Verrept.
>|||declare @.parm int
declare @.sql nvarchar(400)
set @.parm = 1
set @.sql = 'select * from table1 where Type IS NOT NULL and '
if ( @.parm = 1 )
set @.sql = @.sql + 'type = ''t1'''
else if ( @.parm = 2 )
set @.sql = @.sql + 'type = ''t2'''
else
print 'error'
exec (@.sql)
William Stacey [MVP]
"Scotchy" <Scotchy@.discussions.microsoft.com> wrote in message
news:5CB3CA78-0973-4ED8-B308-7B3028CBB8F2@.microsoft.com...
> Hello all, Thanks in advance to contributions.
> I would like to be able to manipulate myTSQL in a stored procedure, based
> on a value in a parameter, using the case statement.
> I want to avoid Begin..End constructs
> My code is such...
> select field1,field2 etc etc
> from Order o
> inner join tablex oa on o.order_id = oa.order_id
> -- etc etc p to 8 joins
> where
> -- some predicate
> -- then the bit I want to case
> case @.MyParameter
> when 'IS_MANAGER' then (and o.reason = 'T1' or o.reason = 'T2')
> when 'DE_MANAGER' then (and o.reason = 'T3')
> end
> So, what I am trying to do is change the overall selection predicate based
> on the
> value of the @.MyParameter. I am trying to avoid wrapping the whole code
> block up into seperate Begin..End constructs if possible as it will make
> the
> SP very big and ugly.
> Amy I trying to break SLQ rules here?
> Cheers
> Scotchy
>
>
>
> eg.|||-- Couple of things: do you need to CASE? Can't you do something like this
?
USE Northwind
GO
DECLARE @.EmployeeID INT
SET @.EmployeeID = 2
-- Normal query
SELECT *
FROM orders
WHERE EmployeeID = @.EmployeeID
-- You can use CASE in queries. Think of them like a function which returns
a value, so you have to put it on one side of an equals:
SELECT *
FROM orders
WHERE CustomerID =
CASE @.EmployeeID
WHEN 1 THEN 'ERNSH'
WHEN 2 THEN 'BLONP'
ELSE ''
END
-- Or multiple criteria. You could even nest your case statements.
SELECT *
FROM orders
WHERE CustomerID =
CASE
WHEN @.EmployeeID = 1 AND OrderDate < '19970101' THEN 'ERNSH'
WHEN @.EmployeeID = 2 AND OrderDate < '19970101' THEN 'BLONP'
ELSE ''
END
-- Let me know how you get on.
--
--
-- Damien
"Scotchy" wrote:
> Hello all, Thanks in advance to contributions.
> I would like to be able to manipulate myTSQL in a stored procedure, based
> on a value in a parameter, using the case statement.
> I want to avoid Begin..End constructs
> My code is such...
> select field1,field2 etc etc
> from Order o
> inner join tablex oa on o.order_id = oa.order_id
> -- etc etc p to 8 joins
> where
> -- some predicate
> -- then the bit I want to case
> case @.MyParameter
> when 'IS_MANAGER' then (and o.reason = 'T1' or o.reason = 'T2')
> when 'DE_MANAGER' then (and o.reason = 'T3')
> end
> So, what I am trying to do is change the overall selection predicate based
> on the
> value of the @.MyParameter. I am trying to avoid wrapping the whole code
> block up into seperate Begin..End constructs if possible as it will make t
he
> SP very big and ugly.
> Amy I trying to break SLQ rules here?
> Cheers
> Scotchy
>
>
>
> eg.|||On Tue, 20 Dec 2005 18:07:01 -0800, Scotchy wrote:
>Hello all, Thanks in advance to contributions.
>I would like to be able to manipulate myTSQL in a stored procedure, based
>on a value in a parameter, using the case statement.
>I want to avoid Begin..End constructs
>My code is such...
>select field1,field2 etc etc
>from Order o
>inner join tablex oa on o.order_id = oa.order_id
>-- etc etc p to 8 joins
>where
> -- some predicate
> -- then the bit I want to case
> case @.MyParameter
> when 'IS_MANAGER' then (and o.reason = 'T1' or o.reason = 'T2')
> when 'DE_MANAGER' then (and o.reason = 'T3')
> end
>So, what I am trying to do is change the overall selection predicate based
>on the
>value of the @.MyParameter. I am trying to avoid wrapping the whole code
>block up into seperate Begin..End constructs if possible as it will make th
e
>SP very big and ugly.
>Amy I trying to break SLQ rules here?
Hi Scotchy,
CASE is an expression, not a statement. Check the examples in Books
Online to get a feeling for what you can accomplish with CASE. And never
think that CASE in SQL Server is similar to CASE in languages such as C.
For your situation, a combination of OR and AND works better than a
CASE:
WHERE ...
AND ( (@.MyParameter = 'IS_MANAGER' AND o.reason IN ('T1', 'T2') )
OR (@.MyParameter = 'DE_MANAGER' AND o.reason = 'T3' ))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Change the size of drop down parameter box
Im trying to make it so my drop down box on the parameter for my report
automatically fits or at least I can specify the size. Im using ssrs 2005
and there is a sroll bar on on both sides. I would like to increase the size
so I dont have these but dont know how to. PLEASE HELP!!!!On Jun 28, 12:04 pm, OWeston <OWes...@.discussions.microsoft.com>
wrote:
> Im trying to make it so my drop down box on the parameter for my report
> automatically fits or at least I can specify the size. Im using ssrs 2005
> and there is a sroll bar on on both sides. I would like to increase the size
> so I dont have these but dont know how to. PLEASE HELP!!!!
This is a pretty regular request on this news group. Currently, this
type of property cannot be controlled; but, hopefully it will be
available in the near future (perhaps w/SSRS 2008). Sorry that I could
not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||Is there a fix/update for this yet?
"EMartinez" wrote:
> On Jun 28, 12:04 pm, OWeston <OWes...@.discussions.microsoft.com>
> wrote:
> > Im trying to make it so my drop down box on the parameter for my report
> > automatically fits or at least I can specify the size. Im using ssrs 2005
> > and there is a sroll bar on on both sides. I would like to increase the size
> > so I dont have these but dont know how to. PLEASE HELP!!!!
>
> This is a pretty regular request on this news group. Currently, this
> type of property cannot be controlled; but, hopefully it will be
> available in the near future (perhaps w/SSRS 2008). Sorry that I could
> not be of greater assistance.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Jul 10, 1:14 pm, JMD <J...@.discussions.microsoft.com> wrote:
> Is there a fix/update for this yet?
> "EMartinez" wrote:
> > On Jun 28, 12:04 pm, OWeston <OWes...@.discussions.microsoft.com>
> > wrote:
> > > Im trying to make it so my drop down box on the parameter for my report
> > > automatically fits or at least I can specify the size. Im using ssrs 2005
> > > and there is a sroll bar on on both sides. I would like to increase the size
> > > so I dont have these but dont know how to. PLEASE HELP!!!!
> > This is a pretty regular request on this news group. Currently, this
> > type of property cannot be controlled; but, hopefully it will be
> > available in the near future (perhaps w/SSRS 2008). Sorry that I could
> > not be of greater assistance.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
I've checked Microsoft.com and haven't found anything available yet.
Regards,
Enrique Martinez
Sr. Software Consultant
automatically fits or at least I can specify the size. Im using ssrs 2005
and there is a sroll bar on on both sides. I would like to increase the size
so I dont have these but dont know how to. PLEASE HELP!!!!On Jun 28, 12:04 pm, OWeston <OWes...@.discussions.microsoft.com>
wrote:
> Im trying to make it so my drop down box on the parameter for my report
> automatically fits or at least I can specify the size. Im using ssrs 2005
> and there is a sroll bar on on both sides. I would like to increase the size
> so I dont have these but dont know how to. PLEASE HELP!!!!
This is a pretty regular request on this news group. Currently, this
type of property cannot be controlled; but, hopefully it will be
available in the near future (perhaps w/SSRS 2008). Sorry that I could
not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||Is there a fix/update for this yet?
"EMartinez" wrote:
> On Jun 28, 12:04 pm, OWeston <OWes...@.discussions.microsoft.com>
> wrote:
> > Im trying to make it so my drop down box on the parameter for my report
> > automatically fits or at least I can specify the size. Im using ssrs 2005
> > and there is a sroll bar on on both sides. I would like to increase the size
> > so I dont have these but dont know how to. PLEASE HELP!!!!
>
> This is a pretty regular request on this news group. Currently, this
> type of property cannot be controlled; but, hopefully it will be
> available in the near future (perhaps w/SSRS 2008). Sorry that I could
> not be of greater assistance.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Jul 10, 1:14 pm, JMD <J...@.discussions.microsoft.com> wrote:
> Is there a fix/update for this yet?
> "EMartinez" wrote:
> > On Jun 28, 12:04 pm, OWeston <OWes...@.discussions.microsoft.com>
> > wrote:
> > > Im trying to make it so my drop down box on the parameter for my report
> > > automatically fits or at least I can specify the size. Im using ssrs 2005
> > > and there is a sroll bar on on both sides. I would like to increase the size
> > > so I dont have these but dont know how to. PLEASE HELP!!!!
> > This is a pretty regular request on this news group. Currently, this
> > type of property cannot be controlled; but, hopefully it will be
> > available in the near future (perhaps w/SSRS 2008). Sorry that I could
> > not be of greater assistance.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
I've checked Microsoft.com and haven't found anything available yet.
Regards,
Enrique Martinez
Sr. Software Consultant
Wednesday, March 7, 2012
Change the Parameter in an query
I have a report "Clearance Summary Report:
Each page summarized the total sales for one agency.
Everything works great except for one total "Adjustments:"
An agency can have one order but have 50 adjustments to that order.
That is why the Adjustments can not be included in the "dsReport" query.
For that reason the Adjustments number is calculate with its own query
(dsAdjustments). but gets two parameters ClearanceID and MagazineID.
NOW the problem.
The help file says to create two parameters. ClearanceID and MagazineID.
Declear them internal.
Link them to the fields: ClearanceID and MagazineID.
Then the dsAdjustments query can refreance the paramters ClearanceID and
MagazineID.
When the report is run the dsAjustments query gets it value from the
parameters ClearanceID and MagazineID and then returns a number. That is how
I get the adjusments.
This works if I lookup only one Agency. But if there are two or more
agencies in the report the parameters ClearanceID and MagazineID are only
fill in at the begining of the report. All the other Agencies reports ty to
use the first agency adjustmets numbers.
Question: How do I get the parameters ClearanceID and MagzineID to update
for every new Agency''
Thank you
Scott BurkeRead up on subreports. Subreports are exactly for this sort of this.
Master-detail type reports pretty much must be done using subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Burke" <ScottBurke@.discussions.microsoft.com> wrote in message
news:A7930198-0781-4DB7-A25E-9EDFBE434C6A@.microsoft.com...
>I have a report "Clearance Summary Report:
> Each page summarized the total sales for one agency.
> Everything works great except for one total "Adjustments:"
> An agency can have one order but have 50 adjustments to that order.
> That is why the Adjustments can not be included in the "dsReport" query.
> For that reason the Adjustments number is calculate with its own query
> (dsAdjustments). but gets two parameters ClearanceID and MagazineID.
> NOW the problem.
> The help file says to create two parameters. ClearanceID and MagazineID.
> Declear them internal.
> Link them to the fields: ClearanceID and MagazineID.
> Then the dsAdjustments query can refreance the paramters ClearanceID and
> MagazineID.
> When the report is run the dsAjustments query gets it value from the
> parameters ClearanceID and MagazineID and then returns a number. That is
> how
> I get the adjusments.
> This works if I lookup only one Agency. But if there are two or more
> agencies in the report the parameters ClearanceID and MagazineID are only
> fill in at the begining of the report. All the other Agencies reports ty
> to
> use the first agency adjustmets numbers.
> Question: How do I get the parameters ClearanceID and MagzineID to update
> for every new Agency''
> Thank you
> Scott Burke
>|||Hi Bruce, This report org used a subreport to produce the totals. HOWEVER;
For some reason unknow to everyone I talked to the Report server tends to
lose/ can't find the subreport!!!!!
The last time it happed I had to redeploy the main report. That did not
work this time.
We only had the report server for four months! I dont look forward to
redeploying the CommisionSummaryReport every two months!
my solution is to replace the subreport. Besides running faster the only
problem I am having is getting the right values to the dsAdjustment query.
My only problem is getting the right ClearanceID and MagazineID value to the
query.
Is it possible to load a value into the parameters?
I am trying this in the textbox:
=Parameters!MagazineID.Value = Fields!MagazineID.Value
=(Fields!Adjustment.Value, "dsAdjustment")
The problem here is that "=Parameters!MagazineID.Value =Fields!MagazineID.Value" is being treaded like a comparason. The textbox
displays "True" or "False".
Any Ideals?
Scott Burke
"Bruce L-C [MVP]" wrote:
> Read up on subreports. Subreports are exactly for this sort of this.
> Master-detail type reports pretty much must be done using subreports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott Burke" <ScottBurke@.discussions.microsoft.com> wrote in message
> news:A7930198-0781-4DB7-A25E-9EDFBE434C6A@.microsoft.com...
> >I have a report "Clearance Summary Report:
> > Each page summarized the total sales for one agency.
> >
> > Everything works great except for one total "Adjustments:"
> >
> > An agency can have one order but have 50 adjustments to that order.
> > That is why the Adjustments can not be included in the "dsReport" query.
> >
> > For that reason the Adjustments number is calculate with its own query
> > (dsAdjustments). but gets two parameters ClearanceID and MagazineID.
> >
> > NOW the problem.
> > The help file says to create two parameters. ClearanceID and MagazineID.
> > Declear them internal.
> > Link them to the fields: ClearanceID and MagazineID.
> > Then the dsAdjustments query can refreance the paramters ClearanceID and
> > MagazineID.
> >
> > When the report is run the dsAjustments query gets it value from the
> > parameters ClearanceID and MagazineID and then returns a number. That is
> > how
> > I get the adjusments.
> >
> > This works if I lookup only one Agency. But if there are two or more
> > agencies in the report the parameters ClearanceID and MagazineID are only
> > fill in at the begining of the report. All the other Agencies reports ty
> > to
> > use the first agency adjustmets numbers.
> >
> > Question: How do I get the parameters ClearanceID and MagzineID to update
> > for every new Agency''
> >
> > Thank you
> > Scott Burke
> >
>
>|||Don't know why you are losing subreports. I have lots and lots and lots of
subreports. Not only have I never seen this but I have not seen any posting
about losing subreports. Makes me wonder what is unusual about your
environment.
Read up on drill-down (not drill through). Basically you have a single query
that contains all your information. The master information is repeated for
every row. Then you design your report to drill-down (click on a + sign to
see more) OR if you don't want to use drill down UI then you just hide the
master information when it is in the above row.
What you are trying to do will not work.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Burke" <ScottBurke@.discussions.microsoft.com> wrote in message
news:D302FB3A-8351-4C5E-AD1E-0011FCFC6634@.microsoft.com...
> Hi Bruce, This report org used a subreport to produce the totals.
> HOWEVER;
> For some reason unknow to everyone I talked to the Report server tends to
> lose/ can't find the subreport!!!!!
> The last time it happed I had to redeploy the main report. That did not
> work this time.
> We only had the report server for four months! I dont look forward to
> redeploying the CommisionSummaryReport every two months!
> my solution is to replace the subreport. Besides running faster the only
> problem I am having is getting the right values to the dsAdjustment query.
> My only problem is getting the right ClearanceID and MagazineID value to
> the
> query.
> Is it possible to load a value into the parameters?
> I am trying this in the textbox:
> =Parameters!MagazineID.Value = Fields!MagazineID.Value
> =(Fields!Adjustment.Value, "dsAdjustment")
>
> The problem here is that "=Parameters!MagazineID.Value => Fields!MagazineID.Value" is being treaded like a comparason. The textbox
> displays "True" or "False".
> Any Ideals?
> Scott Burke
>
>
> "Bruce L-C [MVP]" wrote:
>> Read up on subreports. Subreports are exactly for this sort of this.
>> Master-detail type reports pretty much must be done using subreports.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Scott Burke" <ScottBurke@.discussions.microsoft.com> wrote in message
>> news:A7930198-0781-4DB7-A25E-9EDFBE434C6A@.microsoft.com...
>> >I have a report "Clearance Summary Report:
>> > Each page summarized the total sales for one agency.
>> >
>> > Everything works great except for one total "Adjustments:"
>> >
>> > An agency can have one order but have 50 adjustments to that order.
>> > That is why the Adjustments can not be included in the "dsReport"
>> > query.
>> >
>> > For that reason the Adjustments number is calculate with its own query
>> > (dsAdjustments). but gets two parameters ClearanceID and MagazineID.
>> >
>> > NOW the problem.
>> > The help file says to create two parameters. ClearanceID and
>> > MagazineID.
>> > Declear them internal.
>> > Link them to the fields: ClearanceID and MagazineID.
>> > Then the dsAdjustments query can refreance the paramters ClearanceID
>> > and
>> > MagazineID.
>> >
>> > When the report is run the dsAjustments query gets it value from the
>> > parameters ClearanceID and MagazineID and then returns a number. That
>> > is
>> > how
>> > I get the adjusments.
>> >
>> > This works if I lookup only one Agency. But if there are two or more
>> > agencies in the report the parameters ClearanceID and MagazineID are
>> > only
>> > fill in at the begining of the report. All the other Agencies reports
>> > ty
>> > to
>> > use the first agency adjustmets numbers.
>> >
>> > Question: How do I get the parameters ClearanceID and MagzineID to
>> > update
>> > for every new Agency''
>> >
>> > Thank you
>> > Scott Burke
>> >
>>
Each page summarized the total sales for one agency.
Everything works great except for one total "Adjustments:"
An agency can have one order but have 50 adjustments to that order.
That is why the Adjustments can not be included in the "dsReport" query.
For that reason the Adjustments number is calculate with its own query
(dsAdjustments). but gets two parameters ClearanceID and MagazineID.
NOW the problem.
The help file says to create two parameters. ClearanceID and MagazineID.
Declear them internal.
Link them to the fields: ClearanceID and MagazineID.
Then the dsAdjustments query can refreance the paramters ClearanceID and
MagazineID.
When the report is run the dsAjustments query gets it value from the
parameters ClearanceID and MagazineID and then returns a number. That is how
I get the adjusments.
This works if I lookup only one Agency. But if there are two or more
agencies in the report the parameters ClearanceID and MagazineID are only
fill in at the begining of the report. All the other Agencies reports ty to
use the first agency adjustmets numbers.
Question: How do I get the parameters ClearanceID and MagzineID to update
for every new Agency''
Thank you
Scott BurkeRead up on subreports. Subreports are exactly for this sort of this.
Master-detail type reports pretty much must be done using subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Burke" <ScottBurke@.discussions.microsoft.com> wrote in message
news:A7930198-0781-4DB7-A25E-9EDFBE434C6A@.microsoft.com...
>I have a report "Clearance Summary Report:
> Each page summarized the total sales for one agency.
> Everything works great except for one total "Adjustments:"
> An agency can have one order but have 50 adjustments to that order.
> That is why the Adjustments can not be included in the "dsReport" query.
> For that reason the Adjustments number is calculate with its own query
> (dsAdjustments). but gets two parameters ClearanceID and MagazineID.
> NOW the problem.
> The help file says to create two parameters. ClearanceID and MagazineID.
> Declear them internal.
> Link them to the fields: ClearanceID and MagazineID.
> Then the dsAdjustments query can refreance the paramters ClearanceID and
> MagazineID.
> When the report is run the dsAjustments query gets it value from the
> parameters ClearanceID and MagazineID and then returns a number. That is
> how
> I get the adjusments.
> This works if I lookup only one Agency. But if there are two or more
> agencies in the report the parameters ClearanceID and MagazineID are only
> fill in at the begining of the report. All the other Agencies reports ty
> to
> use the first agency adjustmets numbers.
> Question: How do I get the parameters ClearanceID and MagzineID to update
> for every new Agency''
> Thank you
> Scott Burke
>|||Hi Bruce, This report org used a subreport to produce the totals. HOWEVER;
For some reason unknow to everyone I talked to the Report server tends to
lose/ can't find the subreport!!!!!
The last time it happed I had to redeploy the main report. That did not
work this time.
We only had the report server for four months! I dont look forward to
redeploying the CommisionSummaryReport every two months!
my solution is to replace the subreport. Besides running faster the only
problem I am having is getting the right values to the dsAdjustment query.
My only problem is getting the right ClearanceID and MagazineID value to the
query.
Is it possible to load a value into the parameters?
I am trying this in the textbox:
=Parameters!MagazineID.Value = Fields!MagazineID.Value
=(Fields!Adjustment.Value, "dsAdjustment")
The problem here is that "=Parameters!MagazineID.Value =Fields!MagazineID.Value" is being treaded like a comparason. The textbox
displays "True" or "False".
Any Ideals?
Scott Burke
"Bruce L-C [MVP]" wrote:
> Read up on subreports. Subreports are exactly for this sort of this.
> Master-detail type reports pretty much must be done using subreports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Scott Burke" <ScottBurke@.discussions.microsoft.com> wrote in message
> news:A7930198-0781-4DB7-A25E-9EDFBE434C6A@.microsoft.com...
> >I have a report "Clearance Summary Report:
> > Each page summarized the total sales for one agency.
> >
> > Everything works great except for one total "Adjustments:"
> >
> > An agency can have one order but have 50 adjustments to that order.
> > That is why the Adjustments can not be included in the "dsReport" query.
> >
> > For that reason the Adjustments number is calculate with its own query
> > (dsAdjustments). but gets two parameters ClearanceID and MagazineID.
> >
> > NOW the problem.
> > The help file says to create two parameters. ClearanceID and MagazineID.
> > Declear them internal.
> > Link them to the fields: ClearanceID and MagazineID.
> > Then the dsAdjustments query can refreance the paramters ClearanceID and
> > MagazineID.
> >
> > When the report is run the dsAjustments query gets it value from the
> > parameters ClearanceID and MagazineID and then returns a number. That is
> > how
> > I get the adjusments.
> >
> > This works if I lookup only one Agency. But if there are two or more
> > agencies in the report the parameters ClearanceID and MagazineID are only
> > fill in at the begining of the report. All the other Agencies reports ty
> > to
> > use the first agency adjustmets numbers.
> >
> > Question: How do I get the parameters ClearanceID and MagzineID to update
> > for every new Agency''
> >
> > Thank you
> > Scott Burke
> >
>
>|||Don't know why you are losing subreports. I have lots and lots and lots of
subreports. Not only have I never seen this but I have not seen any posting
about losing subreports. Makes me wonder what is unusual about your
environment.
Read up on drill-down (not drill through). Basically you have a single query
that contains all your information. The master information is repeated for
every row. Then you design your report to drill-down (click on a + sign to
see more) OR if you don't want to use drill down UI then you just hide the
master information when it is in the above row.
What you are trying to do will not work.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott Burke" <ScottBurke@.discussions.microsoft.com> wrote in message
news:D302FB3A-8351-4C5E-AD1E-0011FCFC6634@.microsoft.com...
> Hi Bruce, This report org used a subreport to produce the totals.
> HOWEVER;
> For some reason unknow to everyone I talked to the Report server tends to
> lose/ can't find the subreport!!!!!
> The last time it happed I had to redeploy the main report. That did not
> work this time.
> We only had the report server for four months! I dont look forward to
> redeploying the CommisionSummaryReport every two months!
> my solution is to replace the subreport. Besides running faster the only
> problem I am having is getting the right values to the dsAdjustment query.
> My only problem is getting the right ClearanceID and MagazineID value to
> the
> query.
> Is it possible to load a value into the parameters?
> I am trying this in the textbox:
> =Parameters!MagazineID.Value = Fields!MagazineID.Value
> =(Fields!Adjustment.Value, "dsAdjustment")
>
> The problem here is that "=Parameters!MagazineID.Value => Fields!MagazineID.Value" is being treaded like a comparason. The textbox
> displays "True" or "False".
> Any Ideals?
> Scott Burke
>
>
> "Bruce L-C [MVP]" wrote:
>> Read up on subreports. Subreports are exactly for this sort of this.
>> Master-detail type reports pretty much must be done using subreports.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Scott Burke" <ScottBurke@.discussions.microsoft.com> wrote in message
>> news:A7930198-0781-4DB7-A25E-9EDFBE434C6A@.microsoft.com...
>> >I have a report "Clearance Summary Report:
>> > Each page summarized the total sales for one agency.
>> >
>> > Everything works great except for one total "Adjustments:"
>> >
>> > An agency can have one order but have 50 adjustments to that order.
>> > That is why the Adjustments can not be included in the "dsReport"
>> > query.
>> >
>> > For that reason the Adjustments number is calculate with its own query
>> > (dsAdjustments). but gets two parameters ClearanceID and MagazineID.
>> >
>> > NOW the problem.
>> > The help file says to create two parameters. ClearanceID and
>> > MagazineID.
>> > Declear them internal.
>> > Link them to the fields: ClearanceID and MagazineID.
>> > Then the dsAdjustments query can refreance the paramters ClearanceID
>> > and
>> > MagazineID.
>> >
>> > When the report is run the dsAjustments query gets it value from the
>> > parameters ClearanceID and MagazineID and then returns a number. That
>> > is
>> > how
>> > I get the adjusments.
>> >
>> > This works if I lookup only one Agency. But if there are two or more
>> > agencies in the report the parameters ClearanceID and MagazineID are
>> > only
>> > fill in at the begining of the report. All the other Agencies reports
>> > ty
>> > to
>> > use the first agency adjustmets numbers.
>> >
>> > Question: How do I get the parameters ClearanceID and MagzineID to
>> > update
>> > for every new Agency''
>> >
>> > Thank you
>> > Scott Burke
>> >
>>
Change the Column Heading on select statement
I have a problem I don't know if there's a way to solve it.
I have a store procedure with 2 parameter the first parameter is the booking ID which I used in the where statement the other one is the type "D" for Driver and "H" for helper actually they are on the same table I'm asking is it possible to change the column heading in the select below as you can see the "AS [DRIVER NAME]" what if the type passed is type "H" means is it possible to change it to "[HELPER NAME]" on the same select statement.
SELECT Booking_ID AS [BOOKING ID], UPPER(Lastname_VC)+', '+Firstname_VC+' '+Middlename_VC AS [DRIVER NAME] FROM Boo_DrvHlp_T INNER JOIN Boo_TripHist_T
ON Boo_DrvHlp_T.Employee_ID=Boo_TripHist_T.Employee_I D AND Boo_TripHist_T.Type=@.Type WHERE Booking_ID=@.BookingID
thanks.RE: I have a problem I don't know if there's a way to solve it.
I have a store procedure with 2 parameter the first parameter is the booking ID which I used in the where statement the other one is the type "D" for Driver and "H" for helper actually they are on the same table I'm asking is it possible to change the column heading in the select below as you can see the "AS [DRIVER NAME]" what if the type passed is type "H" means is it possible to change it to "[HELPER NAME]" on the same select statement.
SELECT Booking_ID AS [BOOKING ID], UPPER(Lastname_VC)+', '+Firstname_VC+' '+Middlename_VC AS [DRIVER NAME] FROM Boo_DrvHlp_T INNER JOIN Boo_TripHist_T
ON Boo_DrvHlp_T.Employee_ID=Boo_TripHist_T.Employee_I D AND Boo_TripHist_T.Type=@.Type WHERE Booking_ID=@.BookingID thanks.
Q1 [Is it possible to have a result set heading e.g. , 'DRIVER NAME', conditionally appear as something else e.g. , 'HELPER NAME', depending on a passed parameter being either 'D' or 'H'?]
A1 Yes. Assign the passed parameter to a variable in the stored procedure; use a Case statement to evaluate and appropriatly use either 'DRIVER NAME' or 'HELPER NAME' in your result header column.
the logic [psudocode] amounts to something like:
Case @.vPassedParameter = 'H'
Then @.vColumnHeader = 'HELPER NAME'
Case @.vPassedParameter = 'D'
Then @.vColumnHeader = 'DRIVER NAME'
I have a store procedure with 2 parameter the first parameter is the booking ID which I used in the where statement the other one is the type "D" for Driver and "H" for helper actually they are on the same table I'm asking is it possible to change the column heading in the select below as you can see the "AS [DRIVER NAME]" what if the type passed is type "H" means is it possible to change it to "[HELPER NAME]" on the same select statement.
SELECT Booking_ID AS [BOOKING ID], UPPER(Lastname_VC)+', '+Firstname_VC+' '+Middlename_VC AS [DRIVER NAME] FROM Boo_DrvHlp_T INNER JOIN Boo_TripHist_T
ON Boo_DrvHlp_T.Employee_ID=Boo_TripHist_T.Employee_I D AND Boo_TripHist_T.Type=@.Type WHERE Booking_ID=@.BookingID
thanks.RE: I have a problem I don't know if there's a way to solve it.
I have a store procedure with 2 parameter the first parameter is the booking ID which I used in the where statement the other one is the type "D" for Driver and "H" for helper actually they are on the same table I'm asking is it possible to change the column heading in the select below as you can see the "AS [DRIVER NAME]" what if the type passed is type "H" means is it possible to change it to "[HELPER NAME]" on the same select statement.
SELECT Booking_ID AS [BOOKING ID], UPPER(Lastname_VC)+', '+Firstname_VC+' '+Middlename_VC AS [DRIVER NAME] FROM Boo_DrvHlp_T INNER JOIN Boo_TripHist_T
ON Boo_DrvHlp_T.Employee_ID=Boo_TripHist_T.Employee_I D AND Boo_TripHist_T.Type=@.Type WHERE Booking_ID=@.BookingID thanks.
Q1 [Is it possible to have a result set heading e.g. , 'DRIVER NAME', conditionally appear as something else e.g. , 'HELPER NAME', depending on a passed parameter being either 'D' or 'H'?]
A1 Yes. Assign the passed parameter to a variable in the stored procedure; use a Case statement to evaluate and appropriatly use either 'DRIVER NAME' or 'HELPER NAME' in your result header column.
the logic [psudocode] amounts to something like:
Case @.vPassedParameter = 'H'
Then @.vColumnHeader = 'HELPER NAME'
Case @.vPassedParameter = 'D'
Then @.vColumnHeader = 'DRIVER NAME'
Friday, February 24, 2012
Change Shared Data Source at runtime based on a parameter
I have 11 databases identical databases except that they have different usename and passwords. I want to setup 11 Shared Data Sources and set the report to choose a different Shared Data Source based off of a parameter. Is this possiable
Changing shared data sources dynamically is currently not supported.
The closest you can get through expressions in the report is to use a non-shared data source with an expression-based connection string. This option and other options are discussed in more detail in this relating thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=16395&SiteID=1
-- Robert
Sunday, February 19, 2012
Change request: Parameter mapping
In some places in the SSIS enviroment, you can use parameters in queries, for examples in the query part of the OLE DB Command component.
You do this by specifying placeholders using question marks. The question marks result in parameters on the Column Mappings tab of the Advanced Editor named Param_0, Param_1, etc. When adding question marks to the query later on, new parameters are created named Param_<x>, where ‘x’ is the next higher index number, no matter where the question mark appears in the query. If, for example, your initial query contains two parameters and later on you add a third one that appears as the first parameter in the query, it results in a new Param_3 on the Column Mappings tab.
This is all a bit confusing and not very wanted from a maintenance point of view. I know this might be a limitation caused by using OLE DB connections; we tried to do something with ADO.NET connections, but until now, not very succesful. Therefore we think this is something to put on a wish list for a next SSIS version. It would be great to have the opportunity to give parameters a meaningful name, in stead of the question marks in the queries and the generated Param_<x> names in other places.
Hans Geurtsen
MoreInfo BV
You should raise this request at http://lab.msdn.microsoft.com/productfeedback/default.aspx (and let us know because I will vote for it) and also on the "SSIS Requests" thread at the top of this forum.
-Jamie
Thursday, February 16, 2012
Change Report Criteria by Username
Hi,
I have a question about passing user information into a report. Right now I have a travel report that accepts trip ID as a parameter. I have multiple users who will access this report through active directory and I think using tripID is too constricting for them. Ideally, I want them to pick their name from a list and pick the travel date(s) that they want to print out.
How can I make the report read in their username from Active Directory and pass this along to the report so that it'll automatically apply a filter the report. Then from a pull-down list, pick the travel date(s) that they're interested. Is this even possible?
Thanks,
Curtis111
Hi Curtis,
You might try using the =User!UserID.Value in the report, pass that as a query parameter, and apply filtering in your database query. This would allow users to see only values based on their userid, and they would not have to select any sort of parameters.
A more secure approach would be to apply filtering in the underlying database based on the users windows credentials. You would set the report data source connection to use windows credentials and then configure the database as noted here:
http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx
Thanks, Jon
|||Thanks, Jon. Sounds like the first approach is the simpliest. I'll take easy any day.
Curtis
|||Can someone provide more detail on how to accomplish the first approach? I am very new to Reporting Services, thanks!
Change Report Criteria by Username
Hi,
I have a question about passing user information into a report. Right now I have a travel report that accepts trip ID as a parameter. I have multiple users who will access this report through active directory and I think using tripID is too constricting for them. Ideally, I want them to pick their name from a list and pick the travel date(s) that they want to print out.
How can I make the report read in their username from Active Directory and pass this along to the report so that it'll automatically apply a filter the report. Then from a pull-down list, pick the travel date(s) that they're interested. Is this even possible?
Thanks,
Curtis111
Hi Curtis,
You might try using the =User!UserID.Value in the report, pass that as a query parameter, and apply filtering in your database query. This would allow users to see only values based on their userid, and they would not have to select any sort of parameters.
A more secure approach would be to apply filtering in the underlying database based on the users windows credentials. You would set the report data source connection to use windows credentials and then configure the database as noted here:
http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx
Thanks, Jon
|||Thanks, Jon. Sounds like the first approach is the simpliest. I'll take easy any day.
Curtis
|||Can someone provide more detail on how to accomplish the first approach? I am very new to Reporting Services, thanks!
Tuesday, February 14, 2012
change parameter prompt for boolean
How can I have a boolean parameter show yes and no instead of true or false?
TIA
deanOn Aug 24, 11:43 am, "Dean" <deanl...@.hotmail.com.nospam> wrote:
> How can I have a boolean parameter show yes and no instead of true or false?
> TIA
> dean
You should create a parameter and give it string values of 'yes' and
'no.' As far as I know, changing the boolean values directly is not
possible. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
TIA
deanOn Aug 24, 11:43 am, "Dean" <deanl...@.hotmail.com.nospam> wrote:
> How can I have a boolean parameter show yes and no instead of true or false?
> TIA
> dean
You should create a parameter and give it string values of 'yes' and
'no.' As far as I know, changing the boolean values directly is not
possible. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
change Parameter order
Hello,
I'd like to change the order in which Parameters are displayed. When I go to the menu Report - Report Parameters and change the order with the arrows displayed - I'll get the following error msg.:The value expression for the report parameter contains an error:The expression contains a non existing parameter in the report parameter collection.
Any ideas to change the order? Edit the XML File?
Thanks!You're doing it the right way. It sounds like you have dependent parametrs in which case they have to appear in order of dependency.|||Thats right they depent on each other but this was automatically. How can I turn that on or off? And if I turn that off - will my procedure to switch the position of the Parameter fields work?
Thanks!|||
Parameters become dependent when you use parameter A in the query that populates paramter B. This usually happens in the WHERE clause e.g.
SELECT *
FROM param_b_table
where some_column = @.param_a
Removing the WHERE clause should remove the dependency between the parameter and and allow you to reorder them.
But are you sure that removing the dependency is really what you want to do for the sake of ordering?
Obviously you won't be able to put the WHERE clause back in and expect to retain the ordering.
|||I'm not using any SQL Query. I use MS Reporting Services - Design Mode and click my Measures and Dimensions together.|||right so you're querying an Analysis Services 2005 cube using MDX. Fine so in the query design mode for your parameter query, remove the dependency to the other parameters.|||Here is my query. How can I switch the dependencies off. After doing this it is possible to change the order and switching the dependencies back on again?SELECT NON EMPTY { } ON COLUMNS, NON EMPTY { ([Organisation].[Business Unit Description].[Business Unit Description].ALLMEMBERS * [Organisation].[Room Local Code].[Room Local Code].ALLMEMBERS * [Organisation].[Business Area Description].[Business Area Description].ALLMEMBERS * [Time].[Hour Description].[Hour Description].ALLMEMBERS * [Date].[Date].[Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@.OrganisationMainUnitDescription, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.OrganisationRoomLocalCode, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DateDate, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.OrganisationBusinessAreaDescription, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Organisation].[Room Type Description].&[Recovery room] } ) ON COLUMNS FROM [Clinical Performance]))))) WHERE ( [Organisation].[Room Type Description].&[Recovery room], IIF( STRTOSET(@.OrganisationMainUnitDescription, CONSTRAINED).Count = 1, STRTOSET(@.OrganisationMainUnitDescription, CONSTRAINED), [Organisation].[Main Unit Description].currentmember ) ) CELL PROPERTIES VALUE|||
I'll refer you to my previous answer in which I stated that you can't expect to put the WHERE clause back in and expect to retain the order. This is equivalent to putting back the dependency, you can'y do it and expect to retain ordering.
Once again, parameters with dependency must appear in order of dependency otherwise you'll get an error.
FYI, the dependencies in this query are highlighted. However, this looks like the main dataset query rather than a parameter dataset query and if so this is not the one you want to remove the dependencies from. Paramter order will need to be enforced if the parameters are dependet on each other, so it's the queries that feed the parameters you want to be looking at and not the main dataset query.
SELECT
NON EMPTY { } ON COLUMNS,
NON EMPTY { ([Organisation].[Business Unit Description].[Business Unit Description].ALLMEMBERS * [Organisation].[Room Local Code].[Room Local Code].ALLMEMBERS * [Organisation].[Business Area Description].[Business Area Description].ALLMEMBERS * [Time].[Hour Description].[Hour Description].ALLMEMBERS * [Date].[Date].[Date].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM (
SELECT ( STRTOSET(@.OrganisationMainUnitDescription, CONSTRAINED) ) ON COLUMNS
FROM (
SELECT ( STRTOSET(@.OrganisationRoomLocalCode, CONSTRAINED) ) ON COLUMNS
FROM (
SELECT ( STRTOSET(@.DateDate, CONSTRAINED) ) ON COLUMNS
FROM (
SELECT ( STRTOSET(@.OrganisationBusinessAreaDescription, CONSTRAINED) ) ON COLUMNS
FROM (
SELECT ( { [Organisation].[Room Type Description].&[Recovery room] } ) ON COLUMNS
FROM [Clinical Performance])))))
WHERE ( [Organisation].[Room Type Description].&[Recovery room], IIF( STRTOSET(@.OrganisationMainUnitDescription, CONSTRAINED).Count = 1, STRTOSET(@.OrganisationMainUnitDescription, CONSTRAINED), [Organisation].[Main Unit Description].currentmember ) )
CELL PROPERTIES VALUE
Change Parameter Entry View
Is there a way I can change the look of the field where you enter the parameters. I was wondering if it would be possible to change the display fonts and background colors. Thanks
No. The Parameter pane is not configurable.
Your request is not the first and is hopefully something that can be made available in the future.
|||I think you can change some things, like the fonts, and even the size of the single select parameter box.
See: http://msdn2.microsoft.com/en-us/library/ms345247(SQL.90).aspx
Subscribe to:
Posts (Atom)