Thursday, March 22, 2012
Changing the color of the series label
would like to make one data point stand out based on a parameter that is
passed in. I have already set up the color schemes for the series to do this
inside the chart but would like the label to make that distinction as well.
For example I would like to set the series label to red for one data point
while all of the other labels would be black. Is this possible?
ThanksIf you have at least RS 2000 SP1 installed, you can edit the data point
label properties (font, color, etc.) and use expressions to determine the
color based on certain conditions.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Todd" <Todd@.discussions.microsoft.com> wrote in message
news:EAEF7051-1909-4CD8-93D8-F6842C2887B9@.microsoft.com...
>I am able to change the colors of the series inside the graph, however I
> would like to make one data point stand out based on a parameter that is
> passed in. I have already set up the color schemes for the series to do
> this
> inside the chart but would like the label to make that distinction as
> well.
> For example I would like to set the series label to red for one data point
> while all of the other labels would be black. Is this possible?
> Thanks|||I am running with SP2 and have attempted to put in the code to make this
happen but it renders all of the labels with the color not just the one that
I am looking for. I have tested the snippet of code elsewhere and it works
fine. It seems as if the chart rendering looks at the first attribute passed
for the series labels and stops looking at the attributes for the rest of the
data points.
"Robert Bruckner [MSFT]" wrote:
> If you have at least RS 2000 SP1 installed, you can edit the data point
> label properties (font, color, etc.) and use expressions to determine the
> color based on certain conditions.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Todd" <Todd@.discussions.microsoft.com> wrote in message
> news:EAEF7051-1909-4CD8-93D8-F6842C2887B9@.microsoft.com...
> >I am able to change the colors of the series inside the graph, however I
> > would like to make one data point stand out based on a parameter that is
> > passed in. I have already set up the color schemes for the series to do
> > this
> > inside the chart but would like the label to make that distinction as
> > well.
> >
> > For example I would like to set the series label to red for one data point
> > while all of the other labels would be black. Is this possible?
> >
> > Thanks
>
>|||Does the chart have a series grouping?
If yes, then assuming you have a series grouping called "ProductCategory",
you have to use a style color expression with an aggregate function:
= iif(First(Fields!Abc.Value, "ProductCategory") > 10, "Red", Nothing)
The important part is the aggregate scope which has to be identical to the
chart series grouping name. Just using the First aggregate without the scope
will give you incorrect results, because the aggregate will be just scoped
for every chart datapoint (and therefore null if you don't have any
datapoints for a particular series group / category group combination).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Todd" <Todd@.discussions.microsoft.com> wrote in message
news:3048F968-1AA1-474D-9EEA-4C985F5DC1DA@.microsoft.com...
>I am running with SP2 and have attempted to put in the code to make this
> happen but it renders all of the labels with the color not just the one
> that
> I am looking for. I have tested the snippet of code elsewhere and it
> works
> fine. It seems as if the chart rendering looks at the first attribute
> passed
> for the series labels and stops looking at the attributes for the rest of
> the
> data points.
> "Robert Bruckner [MSFT]" wrote:
>> If you have at least RS 2000 SP1 installed, you can edit the data point
>> label properties (font, color, etc.) and use expressions to determine the
>> color based on certain conditions.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Todd" <Todd@.discussions.microsoft.com> wrote in message
>> news:EAEF7051-1909-4CD8-93D8-F6842C2887B9@.microsoft.com...
>> >I am able to change the colors of the series inside the graph, however I
>> > would like to make one data point stand out based on a parameter that
>> > is
>> > passed in. I have already set up the color schemes for the series to
>> > do
>> > this
>> > inside the chart but would like the label to make that distinction as
>> > well.
>> >
>> > For example I would like to set the series label to red for one data
>> > point
>> > while all of the other labels would be black. Is this possible?
>> >
>> > Thanks
>>|||No, The chart does not have series grouping. I also wonder if I have
communicated this effectively. I am talking about the labels on the x-axis.
I am only concerned with the series labels not data point labels. Does this
help to clarify?
"Robert Bruckner [MSFT]" wrote:
> Does the chart have a series grouping?
> If yes, then assuming you have a series grouping called "ProductCategory",
> you have to use a style color expression with an aggregate function:
> = iif(First(Fields!Abc.Value, "ProductCategory") > 10, "Red", Nothing)
> The important part is the aggregate scope which has to be identical to the
> chart series grouping name. Just using the First aggregate without the scope
> will give you incorrect results, because the aggregate will be just scoped
> for every chart datapoint (and therefore null if you don't have any
> datapoints for a particular series group / category group combination).
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Todd" <Todd@.discussions.microsoft.com> wrote in message
> news:3048F968-1AA1-474D-9EEA-4C985F5DC1DA@.microsoft.com...
> >I am running with SP2 and have attempted to put in the code to make this
> > happen but it renders all of the labels with the color not just the one
> > that
> > I am looking for. I have tested the snippet of code elsewhere and it
> > works
> > fine. It seems as if the chart rendering looks at the first attribute
> > passed
> > for the series labels and stops looking at the attributes for the rest of
> > the
> > data points.
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> >> If you have at least RS 2000 SP1 installed, you can edit the data point
> >> label properties (font, color, etc.) and use expressions to determine the
> >> color based on certain conditions.
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Todd" <Todd@.discussions.microsoft.com> wrote in message
> >> news:EAEF7051-1909-4CD8-93D8-F6842C2887B9@.microsoft.com...
> >> >I am able to change the colors of the series inside the graph, however I
> >> > would like to make one data point stand out based on a parameter that
> >> > is
> >> > passed in. I have already set up the color schemes for the series to
> >> > do
> >> > this
> >> > inside the chart but would like the label to make that distinction as
> >> > well.
> >> >
> >> > For example I would like to set the series label to red for one data
> >> > point
> >> > while all of the other labels would be black. Is this possible?
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>|||Sorry, the individual x-axis labels share the same color and font settings.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Todd" <Todd@.discussions.microsoft.com> wrote in message
news:7391723B-1CAB-4990-A5A7-E41A41DAF65E@.microsoft.com...
> No, The chart does not have series grouping. I also wonder if I have
> communicated this effectively. I am talking about the labels on the
> x-axis.
> I am only concerned with the series labels not data point labels. Does
> this
> help to clarify?
> "Robert Bruckner [MSFT]" wrote:
>> Does the chart have a series grouping?
>> If yes, then assuming you have a series grouping called
>> "ProductCategory",
>> you have to use a style color expression with an aggregate function:
>> = iif(First(Fields!Abc.Value, "ProductCategory") > 10, "Red", Nothing)
>> The important part is the aggregate scope which has to be identical to
>> the
>> chart series grouping name. Just using the First aggregate without the
>> scope
>> will give you incorrect results, because the aggregate will be just
>> scoped
>> for every chart datapoint (and therefore null if you don't have any
>> datapoints for a particular series group / category group combination).
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Todd" <Todd@.discussions.microsoft.com> wrote in message
>> news:3048F968-1AA1-474D-9EEA-4C985F5DC1DA@.microsoft.com...
>> >I am running with SP2 and have attempted to put in the code to make this
>> > happen but it renders all of the labels with the color not just the one
>> > that
>> > I am looking for. I have tested the snippet of code elsewhere and it
>> > works
>> > fine. It seems as if the chart rendering looks at the first attribute
>> > passed
>> > for the series labels and stops looking at the attributes for the rest
>> > of
>> > the
>> > data points.
>> >
>> > "Robert Bruckner [MSFT]" wrote:
>> >
>> >> If you have at least RS 2000 SP1 installed, you can edit the data
>> >> point
>> >> label properties (font, color, etc.) and use expressions to determine
>> >> the
>> >> color based on certain conditions.
>> >>
>> >> -- Robert
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "Todd" <Todd@.discussions.microsoft.com> wrote in message
>> >> news:EAEF7051-1909-4CD8-93D8-F6842C2887B9@.microsoft.com...
>> >> >I am able to change the colors of the series inside the graph,
>> >> >however I
>> >> > would like to make one data point stand out based on a parameter
>> >> > that
>> >> > is
>> >> > passed in. I have already set up the color schemes for the series
>> >> > to
>> >> > do
>> >> > this
>> >> > inside the chart but would like the label to make that distinction
>> >> > as
>> >> > well.
>> >> >
>> >> > For example I would like to set the series label to red for one data
>> >> > point
>> >> > while all of the other labels would be black. Is this possible?
>> >> >
>> >> > Thanks
>> >>
>> >>
>> >>
>>sql
changing textbox expression based on column value
playing around with Reporting Svcs for a few days.
I have a report that's rendered from a stored procedure (no prob there
- works quite nicely). One of the grouping variables, Type, has values
such as "SS_F", "SS_P" and the like.
Naturally what I'd like to do is assign meaningful labels to these
values. Since there are only a few of them and they're static for the
report - that is, the same 4 values (well, up to 4 .. perhaps only 2 or
3 in any given run) would be returned on any run of the stored proc.
Where do I do that' Or do I need to make the assignments in code? If
so, how do I get there? And before suggesting that I do them in the
sp, I can't - it's not mine to touch :)
Thanks so much for any help!
-Steve LordYou would put in a nested iif statement. RS 2005 books online didn't have an
index listing for that but you could search on the phrase conditional
formatting
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Steve L" <slord@.circlesofcare.org> wrote in message
news:1141419699.794514.9160@.e56g2000cwe.googlegroups.com...
> Please forgive what I'm sure is a novice question but I've only been
> playing around with Reporting Svcs for a few days.
> I have a report that's rendered from a stored procedure (no prob there
> - works quite nicely). One of the grouping variables, Type, has values
> such as "SS_F", "SS_P" and the like.
> Naturally what I'd like to do is assign meaningful labels to these
> values. Since there are only a few of them and they're static for the
> report - that is, the same 4 values (well, up to 4 .. perhaps only 2 or
> 3 in any given run) would be returned on any run of the stored proc.
> Where do I do that' Or do I need to make the assignments in code? If
> so, how do I get there? And before suggesting that I do them in the
> sp, I can't - it's not mine to touch :)
> Thanks so much for any help!
> -Steve Lord
>|||Here's what I had for changing the image used in a report:
=Switch(Fields!PDPType.Value="Bug", "http://localhost/images/bug.gif",
Fields!PDPType.Value="Customization Request",
"http://localhost/images/customization.gif", Fields!PDPType.Value="New
Feature/Enhancement", "http://localhost/images/enhancement.gif",
Fields!PDPType.Value="Script Request", "http://localhost/images/script.gif",
Fields!PDPType.Value="Support Request", http://localhost/images/support.GIF)
Should work for a text field too, I think. Sorry if I'm not understanding
your original question, though, or if you run into another snag. I'm rather
a newbie at the RS stuff myself.
Clint
"Steve L" <slord@.circlesofcare.org> wrote in message
news:1141419699.794514.9160@.e56g2000cwe.googlegroups.com...
> Please forgive what I'm sure is a novice question but I've only been
> playing around with Reporting Svcs for a few days.
> I have a report that's rendered from a stored procedure (no prob there
> - works quite nicely). One of the grouping variables, Type, has values
> such as "SS_F", "SS_P" and the like.
> Naturally what I'd like to do is assign meaningful labels to these
> values. Since there are only a few of them and they're static for the
> report - that is, the same 4 values (well, up to 4 .. perhaps only 2 or
> 3 in any given run) would be returned on any run of the stored proc.
> Where do I do that' Or do I need to make the assignments in code? If
> so, how do I get there? And before suggesting that I do them in the
> sp, I can't - it's not mine to touch :)
> Thanks so much for any help!
> -Steve Lord
>
Changing text appearance on a MouseOver event.
Hi,
Is it possible in SSRS to change the appearance of a Hyperlink based on a MouseOver event? For example can I show the text as say, underlined, only when the Mouse pointer hovers over it.. and normal otherwise?
http://technet.microsoft.com/en-us/library/ms345247(SQL.90).aspxWhat's the point of a hyperlink if it is not underlined? Bad practice. Jakob Neilson wouldn't like it. I'm telling.
Tuesday, March 20, 2012
changing SQL syntax
I am using a java component which generates SELECT statements based on
different critieria. The SELECT statements generated are using the old SQL
syntax. For example it will generate :
SELECT e.employee_id, er.location_code
FROM employee e, employee_record er
WHERE e.employee_id = er.employee_id
What I would like is to get this SELECT statement as :
SELECT e.employee_id, er.location_code
FROM employee e INNER JOIN employee_record er ON (e.employee_id =
er.employee_id)
Now in Enterprise Manager if i create a new view and type in the old SQL
syntax, and see the result, it automatically changes it to new SQL syntax. I
would like to call that functionality from my code after I have received the
SELECT statement from the java component. Is it possible?
Thanks
Rizwan>> I would like to call that functionality from my code after I have
I guess no. It might be an internal parsing/tokenizing routine within the EM
interface & it seems unreasonable to expect some external application have
access to such internal routines.
Anith|||1) Why are you generating SQL instead of calling stored procedures?
2) Why do you care what the genrerated coee looks like anyway? They
run the same!
Thursday, March 8, 2012
Changing Query behavior based on local vs. remote context?
I'm not sure if this is the right ng for this question, but here goes...
I just installed a product that uses SQL Express. When I run queries
against the installed instance I get different behavior depending on if the
query is run from Management Studio locally or from SSMS running on a remote
machine. In both cases the user is a domain admin and the query is
identical. The local query returns 1000+ rows, the remote query returns
ZERO. If the query returns less than say 100 rows, there is no difference
between remote and local.
Has anyone else seen this or use use/implemented it themselves?
I'm curious; how this behavior is defined/implemented? Is it database or
instance specific? It seems to be instance-wide in my case but I'm not sure
yet. I've been digging around in SSMS but haven't found any settings or
properties that seem to apply to this, so any sugestions on where to look
would be appreciated.
Thanks!
KeithMy guess would be that there is a problem on the remote machine. Have you
tried a 2nd remote query?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Keith" <keith@.alh.com> wrote in message
news:%23Ej27KC4HHA.1824@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I'm not sure if this is the right ng for this question, but here goes...
> I just installed a product that uses SQL Express. When I run queries
> against the installed instance I get different behavior depending on if
> the query is run from Management Studio locally or from SSMS running on a
> remote machine. In both cases the user is a domain admin and the query is
> identical. The local query returns 1000+ rows, the remote query returns
> ZERO. If the query returns less than say 100 rows, there is no difference
> between remote and local.
> Has anyone else seen this or use use/implemented it themselves?
> I'm curious; how this behavior is defined/implemented? Is it database or
> instance specific? It seems to be instance-wide in my case but I'm not
> sure yet. I've been digging around in SSMS but haven't found any
> settings or properties that seem to apply to this, so any sugestions on
> where to look would be appreciated.
> Thanks!
> Keith
>|||Yes...a dozen or so actually. And I've tried them on different remote
machines as well with SSMS and SSMSe, in addition to using queries from the
query window and "Open Table/View" from the Object Explorer.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OTMOdYC4HHA.2312@.TK2MSFTNGP06.phx.gbl...
> My guess would be that there is a problem on the remote machine. Have you
> tried a 2nd remote query?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Keith" <keith@.alh.com> wrote in message
> news:%23Ej27KC4HHA.1824@.TK2MSFTNGP04.phx.gbl...
>|||Sounds like larger network packets are getting dropped. Some bridges that
do not do packet splitting can cause this.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Keith" <keith@.alh.com> wrote in message
news:%23Ej27KC4HHA.1824@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I'm not sure if this is the right ng for this question, but here goes...
> I just installed a product that uses SQL Express. When I run queries
> against the installed instance I get different behavior depending on if
> the query is run from Management Studio locally or from SSMS running on a
> remote machine. In both cases the user is a domain admin and the query is
> identical. The local query returns 1000+ rows, the remote query returns
> ZERO. If the query returns less than say 100 rows, there is no difference
> between remote and local.
> Has anyone else seen this or use use/implemented it themselves?
> I'm curious; how this behavior is defined/implemented? Is it database or
> instance specific? It seems to be instance-wide in my case but I'm not
> sure yet. I've been digging around in SSMS but haven't found any
> settings or properties that seem to apply to this, so any sugestions on
> where to look would be appreciated.
> Thanks!
> Keith
>|||OK then I would have to agree with Geoff in that it sounds like an issue
with the servers communications. If you can run this fine locally and all
other machines have issues it kind of narrows it down to that machine
Check the event logs and see if there are errors on that server.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Keith" <keith@.alh.com> wrote in message
news:e8xPg6C4HHA.5724@.TK2MSFTNGP05.phx.gbl...
> Yes...a dozen or so actually. And I've tried them on different remote
> machines as well with SSMS and SSMSe, in addition to using queries from
> the query window and "Open Table/View" from the Object Explorer.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OTMOdYC4HHA.2312@.TK2MSFTNGP06.phx.gbl...
>|||Geoff N. Hiten (SQLCraftsman@.gmail.com) writes:
> Sounds like larger network packets are getting dropped. Some bridges that
> do not do packet splitting can cause this.
Keith's description reminds me of a very weird error that a formed DSL
provider of mine had. I was mainly reading my after a holiday in SSH2
connection to a Unix account. And that worked fine. But then I got the
idea to look at some web site, but I could not access it. Tried another.
Did not work. A third one. Eventually I tried running lynx from the Unix
account. And that hung too! But I could open a new connection and read my
mail.
Finally I came around to put a very short text file on my web site, and
sure enough, this page did turn up in the browser. So I concluded they
had an error where split packets got lost, but as long as the packets
were small, things worked.
(The reason this provider is a former provider, is simply because I moved
to a new flat, and they could not deliver DSL there.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:erXXwEE4HHA.5980@.TK2MSFTNGP04.phx.gbl...
> Sounds like larger network packets are getting dropped. Some bridges that
> do not do packet splitting can cause this.
I should add that this behavior does not happen running queries against any
other SQL Express, SQL 2005, SQL 2000, or MSDE instance - named or
otherwise, physical or virtual machine - anywhere on my network.
Only this particular instance behaves this way.|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OeY8weE4HHA.3916@.TK2MSFTNGP02.phx.gbl...
> OK then I would have to agree with Geoff in that it sounds like an issue
> with the servers communications. If you can run this fine locally and all
> other machines have issues it kind of narrows it down to that machine
Unless you install the product on another machine and get the same behavior,
which I have.
> Check the event logs and see if there are errors on that server.
The Event Viewer does not show any errors, nor does the error log in the
instance's data directory.
Considering that this behavior is present along with the installation of
this particular product, and that other named SQL and SQL Express instances
on the same machine do not demonstrate this behavior - even when returning
result sets orders of magnitude larger and on the same physical network -
I'm inclined to believe that this behavior is not machine specific but
rather localized to the named instance installed by this particular product.
Meaning, some configurable property of SQL Server.
I'm totally willing to believe that this is a bug in the product - a
misconfiguration of some kind - but it sounds like most folks don't think
that this type of (mis)configuration is even possible.
Oh, did I mention that this is a Microsoft product's install of SQL Express
;-)
k|||> Unless you install the product on another machine and get the same
> behavior, which I have.
> Considering that this behavior is present along with the installation of
> this particular product, and that other named SQL and SQL Express
> instances on the same machine do not demonstrate this behavior - even when
> returning result sets orders of magnitude larger and on the same physical
> network - I'm inclined to believe that this behavior is not machine
> specific but rather localized to the named instance installed by this
> particular product. Meaning, some configurable property of SQL Server.
Tidbits like these are always good to know. The answers we gave were based
on the info given. I would ping the makers of the product then and see if
they can provide a clue. Other than some ANSI settings at the connection or
database object level that may return different results I know of no
settings or configuration that would produce this behavior. Things like ANSI
PADDING, ANSI NULLS etc can affect how SQL Server treats the data for joins
and the Where clause which can produce different result sets. But none of
that is based on the size of the result set. I would check to see if the
connection settings are the same for both the local and remote SSMS. But
like I said that doesn't explain all the symptoms you mention.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Keith" <keith@.alh.com> wrote in message
news:O%23h0HtF4HHA.484@.TK2MSFTNGP06.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OeY8weE4HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Unless you install the product on another machine and get the same
> behavior, which I have.
>
> The Event Viewer does not show any errors, nor does the error log in the
> instance's data directory.
> Considering that this behavior is present along with the installation of
> this particular product, and that other named SQL and SQL Express
> instances on the same machine do not demonstrate this behavior - even when
> returning result sets orders of magnitude larger and on the same physical
> network - I'm inclined to believe that this behavior is not machine
> specific but rather localized to the named instance installed by this
> particular product. Meaning, some configurable property of SQL Server.
> I'm totally willing to believe that this is a bug in the product - a
> misconfiguration of some kind - but it sounds like most folks don't think
> that this type of (mis)configuration is even possible.
> Oh, did I mention that this is a Microsoft product's install of SQL
> Express ;-)
> k
>
>|||I discovered this when I was a network admin/dba many years ago. We had a
business network and a process control network that had to be splittable for
political reasons. We had an ethernet bridge set up that we could unplug to
isolate the systems. I was setting up Replication (SQL 6.0/6.5, I think)
between two servers. The both had FDDI interfaces , but there was the
ethernet bridge between them. They negotiated large frame sizes since they
were both on FDDI, not considering that the equipment in the middle couldn't
pass that big a packet. Whatever genius designed the netotiation protocol
didn't bother to test and see if an actual large packet could make it
through. Drove me buggy troubleshooting it. Test queries worked. Ping
never failed. Drives mapped. Then replication would fail to sync. I
eventually found the issue and limited the packet size on the
publisher/distributor and never had another problem.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns998F55155F70Yazorman@.127.0.0.1...
> Geoff N. Hiten (SQLCraftsman@.gmail.com) writes:
> Keith's description reminds me of a very weird error that a formed DSL
> provider of mine had. I was mainly reading my after a holiday in SSH2
> connection to a Unix account. And that worked fine. But then I got the
> idea to look at some web site, but I could not access it. Tried another.
> Did not work. A third one. Eventually I tried running lynx from the Unix
> account. And that hung too! But I could open a new connection and read my
> mail.
> Finally I came around to put a very short text file on my web site, and
> sure enough, this page did turn up in the browser. So I concluded they
> had an error where split packets got lost, but as long as the packets
> were small, things worked.
>
> (The reason this provider is a former provider, is simply because I moved
> to a new flat, and they could not deliver DSL there.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
Wednesday, March 7, 2012
Changing Parameters Dynamically
How do I change paramters dynamically based on user selection. For
example if I let the user select country in a drop down, and based on
the country, I need to load the states parameter, and based on the
states, the counties parameter etc...Is such a dynamic layout possible
using reporting services and VS.NET? If so how? Any links or code
samples would be really helpful.
Thanks,
ChrisYes you can do this. Reference the data set in the report parameter. In the
code snippet below, I have 2 parameters. The first report Parameter is
passed into the query parameter. The resulting data set is then use in the
second parameter and the report filters the data set based on that
parameter. Play with the .rdl a bit and you will get it to work.
<ReportParameters>
<ReportParameter Name="FirstName">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>Brad</Value>
</Values>
</DefaultValue>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>Brad</Value>
</ParameterValue>
<ParameterValue>
<Value>Jon</Value>
</ParameterValue>
<ParameterValue>
<Value>Steve</Value>
</ParameterValue>
</ParameterValues>
</ValidValues>
<Prompt>FirstName</Prompt>
</ReportParameter>
<ReportParameter Name="LastName">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>Syputa</Value>
</Values>
</DefaultValue>
<Prompt>LastName</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>Northwind</DataSetName>
<ValueField>EmployeeLastName</ValueField> --Field(column) in the
dataset.
<LabelField>EmployeeLastName</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
</ReportParameters>
--
| From: chrispragash@.hotmail.com (CPragash)
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| Subject: Changing Parameters Dynamically
| Date: 14 Sep 2004 10:57:39 -0700
| Organization: http://groups.google.com
| Lines: 11
| Message-ID: <79c9faae.0409140957.43a028f6@.posting.google.com>
| NNTP-Posting-Host: 167.7.17.3
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1095184659 14011 127.0.0.1 (14 Sep 2004
17:57:39 GMT)
| X-Complaints-To: groups-abuse@.google.com
| NNTP-Posting-Date: Tue, 14 Sep 2004 17:57:39 +0000 (UTC)
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
e.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!postnews2.google.com!no
t-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.reportingsvcs:29153
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hello All,
|
| How do I change paramters dynamically based on user selection. For
| example if I let the user select country in a drop down, and based on
| the country, I need to load the states parameter, and based on the
| states, the counties parameter etc...Is such a dynamic layout possible
| using reporting services and VS.NET? If so how? Any links or code
| samples would be really helpful.
|
| Thanks,
| Chris
|
Changing PageHeight dynamically
My requirement is to have my report display on one 11 by 8.5 page. Is there a way to programmatically change the PageHeight report property based on the number of rows returned in a table? i.e., if the rowcount for table x >= 10, change PageHeight to 9.5.
Any ideas?
Thanks.
Brad
Is there anyway to achieve this?|||Not really but you can use the next size or change the orientation of your report but before you do that run test copies through either preview or Reportveiwer depending of your choice for display and printing. Here is what I know there is an interactive height setting which you can use to reduce the size so maybe you can use it to increase it. Try the link below for details.
http://blogs.msdn.com/bwelcker/archive/2005/08/19/Alien-Lanes-_2800_Logical-and-Physical-Pagination-Rules_2900_.aspx
Saturday, February 25, 2012
Changing Ordering of a Dimension Attribute
Thanks,
ChrisChange the order by from "Name" to "Key". No idea what "Attribute Name" and "Attribute Key" do however.|||If you choose attribute name, you can define the order by another field.
I have in each dimension an order field and I have configured each dimension to sort by this field. By default I fill in this field with name. But if a user request another order I can change values in this field without changing dimension structure.
Friday, February 24, 2012
Changing join order based on parameters
I keep coming across a performance problem in SQL that I feel must have
an elegant solution, I just can't find it.
The problem is quite obvious:
I have a query that performs a search based on some search parameters
which are given default values in the query of null.
I have a heirachical structure of tables which might be used in the
filtering
e.g.
SELECT *
FROM TableA
INNER JOIN TableB on FKa = FKb
INNER JOIN TableC on FKb = FKc
INNER JOIN TableD on FKc = FKd
I filter the result by adding to these join filters something like
SELECT *
FROM TableA
INNER JOIN TableB on FKa = FKb and (@.filterCol1 is null OR TableB.col1
= @.filterCol1 )
etc...
The problem comes when there are a few hundred thousand rows in each
table, and my search wants to filter on a parameter which affects
TableD. It appears from the execution plan (and indeed only seems
logical) that SQL has to join the entirity of tables A,B and C on their
foreign keys, before joining to D in order to filter. This of course
makes the query take ages.
I've got ideas of how I could get around this, but they are at best
"clunky".
for example:
1) I could have several different "selects" each joining in a
different order based on the parameters that are set.
2) I could build up a "filter" temporary table based on the parameters
passed, then only do the select once, starting with this filter table
first
3) I could come up with a nice elegant solution which currently eludes
me.
Any suggestions?
WillWill
> SELECT *
> FROM TableA
> INNER JOIN TableB on FKa = FKb and (@.filterCol1 is null OR TableB.col1
> = @.filterCol1 )
SELECT *
FROM TableA
INNER JOIN TableB on FKa = FKb OR TableB.col1 =
COALESCE(@.filterCol1,TableB.col1 )
WHERE @.filterCol1 IS NULL
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1143715517.502232.295550@.z34g2000cwc.googlegroups.com...
> Hi,
> I keep coming across a performance problem in SQL that I feel must have
> an elegant solution, I just can't find it.
> The problem is quite obvious:
> I have a query that performs a search based on some search parameters
> which are given default values in the query of null.
> I have a heirachical structure of tables which might be used in the
> filtering
> e.g.
> SELECT *
> FROM TableA
> INNER JOIN TableB on FKa = FKb
> INNER JOIN TableC on FKb = FKc
> INNER JOIN TableD on FKc = FKd
> I filter the result by adding to these join filters something like
> SELECT *
> FROM TableA
> INNER JOIN TableB on FKa = FKb and (@.filterCol1 is null OR TableB.col1
> = @.filterCol1 )
> etc...
> The problem comes when there are a few hundred thousand rows in each
> table, and my search wants to filter on a parameter which affects
> TableD. It appears from the execution plan (and indeed only seems
> logical) that SQL has to join the entirity of tables A,B and C on their
> foreign keys, before joining to D in order to filter. This of course
> makes the query take ages.
> I've got ideas of how I could get around this, but they are at best
> "clunky".
> for example:
> 1) I could have several different "selects" each joining in a
> different order based on the parameters that are set.
> 2) I could build up a "filter" temporary table based on the parameters
> passed, then only do the select once, starting with this filter table
> first
> 3) I could come up with a nice elegant solution which currently eludes
> me.
> Any suggestions?
> Will
>|||Uri,
Thanks for replying.
I must admit I'm having trouble fully understanding what the aim of the
change is. As I see it if I pass something not null as @.filterCol1 then
I won't get any results back. This isn't what I wanted. Essentially the
point of the @.filtercol1 was to indicate that I'm using optional
parameters to filter it, so sometimes I want to filter on @.filterCol1,
sometimes the user might not select any filter for that, in which case
I'll want to filter on another variable @.filterCol2 (sorry I should
have put a fuller example in, I was abreviating it and only using one
variable). This behaviour in itself isn't causing me any problems. My
issue is that if the user only selects a parameter which filters on
tableD, I need this to be used to optimise my query. e.g.:
let's say the user selects @.filterCol1 = null and @.filterCol2 = 27
SELECT *
FROM TableA
INNER JOIN TableB on FKa = FKb and (@.filterCol1 is null or @.filterCol1
= TableB.Col1)
INNER JOIN TableC on FKb = FKc
INNER JOIN TableD on FKc = FKd and (@.filterCol2 is null or @.filterCol2
= TableD.Col2)
I need my sql query to realise that TableD is going to be the bounding
result set, evaluate this one first, then use that to restrict the
scope of the joins on the other 3 tables. However if the user were to
put a value into @.filterCol1 and null for @.filterCol2 then I would need
it to evaluate tableB first as this will be the smallest result set.
I don't think there's going to be a perfect answer, but this problem
must be encountered a lot.
Will|||Will
Well if I understood you cannot dictate to optimizer what order of joins to
be chosen. Yes, there are some JOIN hints but not in this case I think. Have
you considered ( I don't know your business requirements) to separe this
SELECT statement i mean JOIN two tables/three tables based on
parameteters that you've got?
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1143720216.439397.65770@.t31g2000cwb.googlegroups.com...
> Uri,
> Thanks for replying.
> I must admit I'm having trouble fully understanding what the aim of the
> change is. As I see it if I pass something not null as @.filterCol1 then
> I won't get any results back. This isn't what I wanted. Essentially the
> point of the @.filtercol1 was to indicate that I'm using optional
> parameters to filter it, so sometimes I want to filter on @.filterCol1,
> sometimes the user might not select any filter for that, in which case
> I'll want to filter on another variable @.filterCol2 (sorry I should
> have put a fuller example in, I was abreviating it and only using one
> variable). This behaviour in itself isn't causing me any problems. My
> issue is that if the user only selects a parameter which filters on
> tableD, I need this to be used to optimise my query. e.g.:
> let's say the user selects @.filterCol1 = null and @.filterCol2 = 27
> SELECT *
> FROM TableA
> INNER JOIN TableB on FKa = FKb and (@.filterCol1 is null or @.filterCol1
> = TableB.Col1)
> INNER JOIN TableC on FKb = FKc
> INNER JOIN TableD on FKc = FKd and (@.filterCol2 is null or @.filterCol2
> = TableD.Col2)
> I need my sql query to realise that TableD is going to be the bounding
> result set, evaluate this one first, then use that to restrict the
> scope of the joins on the other 3 tables. However if the user were to
> put a value into @.filterCol1 and null for @.filterCol2 then I would need
> it to evaluate tableB first as this will be the smallest result set.
> I don't think there's going to be a perfect answer, but this problem
> must be encountered a lot.
> Will
>|||Uri,
Yes, splitting the select out into different ones dependent on
parameters seems to be the only way to go (but unfortunately not an
option as I won't be able to justify a potentially breaking change such
as that for performance increases). I was hoping that there would be
some nice sql trick that allows you to always hit the smaller tables
first.
Thanks for your help
Will|||If not specified otherwise (through the use of join hints) the Query
Optimizer will always choose the best order according to indexes, statistics
,
etc.
ML
http://milambda.blogspot.com/|||I know that the optimiser will try, and that I can override with join
hints, but in this case the optimiser can't help because the logic of
the join forces it to be evaluated in that order. What I was hoping for
was some kind of new way of laying out the query such that I could
"enter" my data structure from different directions depending on which
one has parameters defined to filter it. I suspect it's not possible,
but doesn't anyone else find this a problem? is there some better way
to either lay out the tables or to have filter procs with multiple
optional parameters?
Cheers
Will|||One way to filter the rows before the join is to use inline table functions,
that accept filtering values as parameters, I'm not sure, however, if this
will actually improve the performance. It's an option to consider and test.
ML
http://milambda.blogspot.com/|||"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1143723585.520821.232940@.e56g2000cwe.googlegroups.com...
>I know that the optimiser will try, and that I can override with join
> hints, but in this case the optimiser can't help because the logic of
> the join forces it to be evaluated in that order. What I was hoping for
> was some kind of new way of laying out the query such that I could
> "enter" my data structure from different directions depending on which
> one has parameters defined to filter it. I suspect it's not possible,
> but doesn't anyone else find this a problem? is there some better way
> to either lay out the tables or to have filter procs with multiple
> optional parameters?
>
The problem is that SQL Server will pick one query plan and reuse it for all
parameter sets. If this is in a stored procedure, you could try to mark it
WITH RECOMPILE, or use dynamic SQL.
David|||You have hit one of the problems with SQL Server. It was built for a
"departmetn level" database on a small machine. It keeps one exectuion
plan for each stored procedure and uses that plan when the proc is
invoked. They added parameter sniffing, but that can actually hurt.
Better products, meant for enterprise level applications and VLDB hold
multiple plans (Is DB2 keeping 16 or 32 now? I do not remember). Thus
they know that when the query looks like this (I will use the @. for
parameters even tho that is not Standard SQL):
SELECT x.y.x
FROM Foobar AS F1
WHERE F1.a = COALESCE (@.p1, a)
AND F1.b = COALESCE (@.p2, b)
AND WHERE F1.c = COALESCE (@.p3, c)
SQL needs to generate 8 execution plans, one for each combination of
NULLs. The best one will be picked at run time when we know (@.p1, @.p2,
@.p3) and twher the NULLs are that will turn a search condition into a
constant TRUE, FALSE or UNKNOWN.
This is your solution #1 -- fake a good optimizer and RDBMS engine in
T-SQL by hand. You can do it with IF-THEN-ELSE control flow in T-SQL.
It is a XXXXX to maintain, but easy to write with a cut and paste in a
text editor. It can run much faster than what you have now, however.
Sunday, February 19, 2012
Changing Header based on subreport
How can I change the contents of a container reports page header based on
the subreport that is being rendered?
Thanks,
JohnNever mind. Did workaround by putting each subreport in separate table with
separate table header and page break after table checked. originally had
each subreport in separate header lines in a single table.
Thanks,
John
"John" wrote:
> Hi,
> How can I change the contents of a container reports page header based on
> the subreport that is being rendered?
> Thanks,
> John
Changing global variable based on day of the week
I'm new to SSIS so please be gentle...
I'm creating a package that needs to go to an FTP site (FTP Task), download a file, unzip it and then process a series of table loads for the 12 text files that will be unzipped. My problem is that the zip file is a date (yyyymmdd.zip) which is normally the previous day of execution EXCEPT on Mondays when it would be the previous Friday's date. My thought is that IF (magic question) I could determine the day of the week in the SSIS package, I know that Tuesday-Friday is just a formatting exercise of getdate()-1 and Monday would be getdate()-3 but I can't seem to find a way (function?) that will allow me to determine the day of the week?
Thanks in advance!
Is that the only file on the site? If so it is easier to just download it using a wildcard *.zip. Then use a for each loop to process each file in the folder which will pick up the filename for you.
Even if it's not it's probably better to check all files on the site and process those which you haven't processed before.
What happens on bank holidays.
|||Thanks for replying. Unfortunately it is not the only file (they keep them for archival purposes so there are hundreds). Is there an easy way to track which files I've processed (flag/switch) or are you suggesting a directory comparison or storing file names in a table somehow (not sure how to compare?)?
If there is no file, it just emails a failure notice and the process would be run manually as it is now (not the most efficient way but we can get to bells and whistles later).
You can disregard this question. I actually found the solution in a posting from "killerless" on 8/30/2006. He/she actually did pretty much the exact expression I needed to calculate the day. I love forums! :-)
|||I would use a ForEachLoop conatiner with an expression in FileSpec property. The FileSpec property overrides the 'files' entry in the conatiner GUI. The expression should have the logic to get the right file name based on the system or execution date. I think sothing like this should work:
Datepart("dw", @.[User::CurrentDate] ) == 2 ?
(DT_WSTR,4)DATEPART("YYYY", @.[User::CurrentDate]) + RIGHT("0" + (DT_WSTR,2)DATEPART("MM", @.[User::CurrentDate]),2) + RIGHT("0" + (DT_WSTR,20) DAY(DATEADD("dd", -3, @.[User::CurrentDate])),2) + ".zip"
:
(DT_WSTR,4)DATEPART("YYYY", @.[User::CurrentDate]) + RIGHT("0" + (DT_WSTR,2)DATEPART("MM", @.[User::CurrentDate]),2) + RIGHT("0" + (DT_WSTR,20) DAY(DATEADD("dd", -1, @.[User::CurrentDate])),2) + ".zip"
It says someting like: if its friday; then substarct 3 days otherwise substract 1.
Put all the logic inside of the container. Let me know if you want the sample package.
|||It looks like my answer got too lateTuesday, February 14, 2012
Changing default parameters cause postback\refresh
I think it has been discussed previously that having default parameter values based on expressions, e.g. a default parameter value of =Split("Bug",",") in multiple parameters will cause a postbacl whenever a user selects different values from the list.
Is this by design? Its a bit of an annoying thing. The refresh\postback doesnt happen if you have basic defaults like ="All" but only when an expression of some sort is used in more than 1 parameter. Does RS think they are linked or something, why does it need to psotback\refresh?
Anyone know of a solution to this?|||Yes, this behavior is by design. Reporting Services allows you to have cascading parameters, where the value from one parameter is used to help populate the values of another parameter. For example, if you have a report with Param1 and Param2, RS will assume Param2 depends on Param1 if:
Param2 is populated from a query that uses certain types of expressions for query text, query parameters, calculated fields, filters, etc.|||I have a similar problem with cascading parameters in reporting services. If the user selects an option from from a cascading parameter dropdown, it seems like the postback/refresh clears the report, even if the user hasn't pressed the View Report button yet. They want to be able to view the current report while selecting new lookup values. All my param lookups are populated thru sql queries. Is there any way to disable the refresh of the report (or keep its current viewstate) from the lookups? We don't want to have to access/display the params outside of the reports (we are accessing/displaying them thru the url), the whole reason for trying reporting services was to simplify the reporting process. Any suggestions? Thanx|||
Hi John,
I have a similar problem in that I am trying to achieve cascading parameters where default value for parameter 2 is dependent on selection for parameter 1. The problem though is that when they choose parameter 1 initially it does what I want. But if they change parameter 1, parameter 2 does not change along with it.
So in other words, parameter 2 is not refreshing. I have tried many possible solutions for this including writing stored procedures, function, custom code, and iif statements to correct this but none of these have worked. Do you have any ideas for how I can fix this?
Thanks!,
|||John or any Microsoft representative that reads this post, are there any plans for implementing an AJAX-based solution to the postback problem for cascading parameters? It does make for a poor user experience to have the report refresh upon a change in parameter value.
Changing default parameters cause postback\refresh
I think it has been discussed previously that having default parameter values based on expressions, e.g. a default parameter value of =Split("Bug",",") in multiple parameters will cause a postbacl whenever a user selects different values from the list.
Is this by design? Its a bit of an annoying thing. The refresh\postback doesnt happen if you have basic defaults like ="All" but only when an expression of some sort is used in more than 1 parameter. Does RS think they are linked or something, why does it need to psotback\refresh?
Anyone know of a solution to this?|||Yes, this behavior is by design. Reporting Services allows you to have cascading parameters, where the value from one parameter is used to help populate the values of another parameter. For example, if you have a report with Param1 and Param2, RS will assume Param2 depends on Param1 if:
Param2 is populated from a query that uses certain types of expressions for query text, query parameters, calculated fields, filters, etc.|||I have a similar problem with cascading parameters in reporting services. If the user selects an option from from a cascading parameter dropdown, it seems like the postback/refresh clears the report, even if the user hasn't pressed the View Report button yet. They want to be able to view the current report while selecting new lookup values. All my param lookups are populated thru sql queries. Is there any way to disable the refresh of the report (or keep its current viewstate) from the lookups? We don't want to have to access/display the params outside of the reports (we are accessing/displaying them thru the url), the whole reason for trying reporting services was to simplify the reporting process. Any suggestions? Thanx|||
Hi John,
I have a similar problem in that I am trying to achieve cascading parameters where default value for parameter 2 is dependent on selection for parameter 1. The problem though is that when they choose parameter 1 initially it does what I want. But if they change parameter 1, parameter 2 does not change along with it.
So in other words, parameter 2 is not refreshing. I have tried many possible solutions for this including writing stored procedures, function, custom code, and iif statements to correct this but none of these have worked. Do you have any ideas for how I can fix this?
Thanks!,
|||John or any Microsoft representative that reads this post, are there any plans for implementing an AJAX-based solution to the postback problem for cascading parameters? It does make for a poor user experience to have the report refresh upon a change in parameter value.
Changing default parameters cause postback\refresh
I think it has been discussed previously that having default parameter values based on expressions, e.g. a default parameter value of =Split("Bug",",") in multiple parameters will cause a postbacl whenever a user selects different values from the list.
Is this by design? Its a bit of an annoying thing. The refresh\postback doesnt happen if you have basic defaults like ="All" but only when an expression of some sort is used in more than 1 parameter. Does RS think they are linked or something, why does it need to psotback\refresh?
Anyone know of a solution to this?|||Yes, this behavior is by design. Reporting Services allows you to have cascading parameters, where the value from one parameter is used to help populate the values of another parameter. For example, if you have a report with Param1 and Param2, RS will assume Param2 depends on Param1 if:
Param2 is populated from a query that uses certain types of expressions for query text, query parameters, calculated fields, filters, etc.|||I have a similar problem with cascading parameters in reporting services. If the user selects an option from from a cascading parameter dropdown, it seems like the postback/refresh clears the report, even if the user hasn't pressed the View Report button yet. They want to be able to view the current report while selecting new lookup values. All my param lookups are populated thru sql queries. Is there any way to disable the refresh of the report (or keep its current viewstate) from the lookups? We don't want to have to access/display the params outside of the reports (we are accessing/displaying them thru the url), the whole reason for trying reporting services was to simplify the reporting process. Any suggestions? Thanx|||
Hi John,
I have a similar problem in that I am trying to achieve cascading parameters where default value for parameter 2 is dependent on selection for parameter 1. The problem though is that when they choose parameter 1 initially it does what I want. But if they change parameter 1, parameter 2 does not change along with it.
So in other words, parameter 2 is not refreshing. I have tried many possible solutions for this including writing stored procedures, function, custom code, and iif statements to correct this but none of these have worked. Do you have any ideas for how I can fix this?
Thanks!,
|||John or any Microsoft representative that reads this post, are there any plans for implementing an AJAX-based solution to the postback problem for cascading parameters? It does make for a poor user experience to have the report refresh upon a change in parameter value.Changing default parameters cause postback\refresh
I think it has been discussed previously that having default parameter values based on expressions, e.g. a default parameter value of =Split("Bug",",") in multiple parameters will cause a postbacl whenever a user selects different values from the list.
Is this by design? Its a bit of an annoying thing. The refresh\postback doesnt happen if you have basic defaults like ="All" but only when an expression of some sort is used in more than 1 parameter. Does RS think they are linked or something, why does it need to psotback\refresh?
Anyone know of a solution to this?|||Yes, this behavior is by design. Reporting Services allows you to have cascading parameters, where the value from one parameter is used to help populate the values of another parameter. For example, if you have a report with Param1 and Param2, RS will assume Param2 depends on Param1 if:
Param2 is populated from a query that uses certain types of expressions for query text, query parameters, calculated fields, filters, etc.|||I have a similar problem with cascading parameters in reporting services. If the user selects an option from from a cascading parameter dropdown, it seems like the postback/refresh clears the report, even if the user hasn't pressed the View Report button yet. They want to be able to view the current report while selecting new lookup values. All my param lookups are populated thru sql queries. Is there any way to disable the refresh of the report (or keep its current viewstate) from the lookups? We don't want to have to access/display the params outside of the reports (we are accessing/displaying them thru the url), the whole reason for trying reporting services was to simplify the reporting process. Any suggestions? Thanx|||
Hi John,
I have a similar problem in that I am trying to achieve cascading parameters where default value for parameter 2 is dependent on selection for parameter 1. The problem though is that when they choose parameter 1 initially it does what I want. But if they change parameter 1, parameter 2 does not change along with it.
So in other words, parameter 2 is not refreshing. I have tried many possible solutions for this including writing stored procedures, function, custom code, and iif statements to correct this but none of these have worked. Do you have any ideas for how I can fix this?
Thanks!,
|||John or any Microsoft representative that reads this post, are there any plans for implementing an AJAX-based solution to the postback problem for cascading parameters? It does make for a poor user experience to have the report refresh upon a change in parameter value.
Sunday, February 12, 2012
Changing database connect to dynamic then the report fails.
Hi all. I've got a report in RS2005 where I am trying to be able to change the database it runs against based on a parameter. I started by making the report and having this as my connect string in the dataset:
="data source=MyServer; initial catalog=DataYear2005"
I was able to connect and preview the report with no problems. I checked the forum and found out that to make it dynamic, I should be able to just add a parameter to the above definition. I set up the parameter as a string with a default value of 2005 and then changed the connect string to be:
="data source=MyServer; initial catalog=DataYear"+Trim(Parameters!sSession.Value)
When I preview this, I get the error: "Error durring processing of the ConnectString expression of datasource MyDatasource."
I'm working in the VS.Net 2005 environment that gets installed with the SQL Server 2005 tools and running against a SQL Server 2000 server. Any help would be apprieciated.
Suggestion: change the connection string back so that the report is working again. Then add a textbox in the report that uses the same expression as your planned dynamic connection string expression. Make sure the expression works in that report textbox. I think there may be type conversion problems with the expression or the name of the report parameter is not correct (case-sensitive!). You may also want to try:
="data source=MyServer; initial catalog=DataYear" & Trim(CStr(Parameters!sSession.Value))
-- Robert
|||Robert,
I tried your ideas and it is still not working. I set up the text box with the expression and I also made one with the connect string without the parameter. The data in both text boxes was identical. I then added the CStr function with no improvements. I also verified that the case was correct and it was. Any other ideas?
Thanks.
|||Do you have any parameters that are query based and rely on this connection string?
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.