Changing the parameter of a Snapshot

I understand that a history can be created in Reporting Services. Is
it true that when you click on the "New Snapshot" button, you get a
report using the default parameter? If so, then the "history" feature
is pretty much useless as you can only generate one version of the
report. What we want is to be able to take snapshots of a report at a
give point in time with the desired parameters.
Thanks for any input here.Yes, snapshots in the history are taken with default values of parameters.
If you need to store snapshot with different parameter values, you need to
set different defaults and then take snapshot. Also, when you render that
snapshot, you can change values of parameters that are not used in query.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"Eugene" <primer200@.yahoo.com> wrote in message
news:6290d6d2.0407281559.735524cb@.posting.google.com...
> I understand that a history can be created in Reporting Services. Is
> it true that when you click on the "New Snapshot" button, you get a
> report using the default parameter? If so, then the "history" feature
> is pretty much useless as you can only generate one version of the
> report. What we want is to be able to take snapshots of a report at a
> give point in time with the desired parameters.
> Thanks for any input here.|||Dmitry,
From a user point of view, I changed the parameter and I want to take
a snapshot of what I did. That would be a fair enough request,
wouldn't it? The administrator can't give a user Content Manager right
for him to change the default. May be this can be included in
enhencement for the next release?
Regards
Eugene|||We consider this as a request, however, it is not as straightforward as it
seems.
1. Snapshots were not intended to be a replacement for a good Data
Warehouse. Every missing feature about snapshots (and even snapshots
themselves) can be "worked around" by setting a data warehouse that can
reconstruct result of a query for any given time.
2. History is bound to a report, not to a user. Items in history don't carry
any individual security. Anyone who have access to a report would be able to
render from history. Therefore, personal preferences of a anyone, creating
snapshot would affect all users.
3. When allowing defaults only we identify snapshots by creation time. If we
accept parameters, we would have to introduce other means to identify and
manage snapshots. It would be possible and reasonable to take multiple
snapshots at the same time.
4. In many cases, users can create a linked report, provide different
default values for parameters and take snapshot of that linked report.
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"Eugene" <primer200@.yahoo.com> wrote in message
news:6290d6d2.0408012040.b062b04@.posting.google.com...
> Dmitry,
> From a user point of view, I changed the parameter and I want to take
> a snapshot of what I did. That would be a fair enough request,
> wouldn't it? The administrator can't give a user Content Manager right
> for him to change the default. May be this can be included in
> enhencement for the next release?
> Regards
> Eugene

Changing the OPEN OBJECTS variable

SQL 2000 error in event log... 2 events as follows:
1. OPEN OBJECTS parameter may be set to low
2. Run sp_configure to change parameter.
How can I see what it is set to now? How can I change this value?
TIAThis is documented in Books on line... - search for 'Setting Configuration
Options'
It is fairly unusual ( in my experience) to need to change this in sql
2000...
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corp (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"_M_" <here@.gone.com> wrote in message
news:ejZi8SWbDHA.2668@.TK2MSFTNGP09.phx.gbl...
> SQL 2000 error in event log... 2 events as follows:
> 1. OPEN OBJECTS parameter may be set to low
> 2. Run sp_configure to change parameter.
> How can I see what it is set to now? How can I change this value?
> TIA
>|||(1) exec sp_configure
(2) exec sp_configure 'open objects', <new value>
You may also have to RECONFIGURE WITH OVERRIDE after running step 2.
-- Keith
"_M_" <here@.gone.com> wrote in message =news:ejZi8SWbDHA.2668@.TK2MSFTNGP09.phx.gbl...
> SQL 2000 error in event log... 2 events as follows:
> > 1. OPEN OBJECTS parameter may be set to low
> 2. Run sp_configure to change parameter.
> > How can I see what it is set to now? How can I change this value?
> > TIA
> >|||Ran the "exec sp_configure"
Check the value for 'open objects'
Was set to 1000
Ran a query in QA as follows...
Exec sp_configure 'show advanced options', 1
Go
RECONFIGURE WITH OVERRIDE
Go
Exec sp_configure 'open objects', 0
Go
RECONFIGURE WITH OVERRIDE
Go
Re-Ran the "exec sp_configure"
Value is now shown as 0, WHICH IS DYNAMIC, CORRECT '
The thing that bothers me is that it said to run RECONFIGURE 2-3 times in
the output in QA... Did it actually change it, or do I have to run
RECONFIGURE WITH OVERRIDE by itself ?
TIA
"Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote in
message news:#JRyCeXbDHA.2412@.TK2MSFTNGP10.phx.gbl...
(1)
exec sp_configure
(2)
exec sp_configure 'open objects', <new value>
You may also have to RECONFIGURE WITH OVERRIDE after running step 2.
--
Keith
"_M_" <here@.gone.com> wrote in message
news:ejZi8SWbDHA.2668@.TK2MSFTNGP09.phx.gbl...
> SQL 2000 error in event log... 2 events as follows:
> 1. OPEN OBJECTS parameter may be set to low
> 2. Run sp_configure to change parameter.
> How can I see what it is set to now? How can I change this value?
> TIA
>|||It prints it event if you run it, as long as you "know" you ran it you can
ignore the extra messages.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"_M_" <here@.gone.com> wrote in message
news:uH1No6YbDHA.2672@.tk2msftngp13.phx.gbl...
Ran the "exec sp_configure"
Check the value for 'open objects'
Was set to 1000
Ran a query in QA as follows...
Exec sp_configure 'show advanced options', 1
Go
RECONFIGURE WITH OVERRIDE
Go
Exec sp_configure 'open objects', 0
Go
RECONFIGURE WITH OVERRIDE
Go
Re-Ran the "exec sp_configure"
Value is now shown as 0, WHICH IS DYNAMIC, CORRECT '
The thing that bothers me is that it said to run RECONFIGURE 2-3 times in
the output in QA... Did it actually change it, or do I have to run
RECONFIGURE WITH OVERRIDE by itself ?
TIA
"Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote in
message news:#JRyCeXbDHA.2412@.TK2MSFTNGP10.phx.gbl...
(1)
exec sp_configure
(2)
exec sp_configure 'open objects', <new value>
You may also have to RECONFIGURE WITH OVERRIDE after running step 2.
--
Keith
"_M_" <here@.gone.com> wrote in message
news:ejZi8SWbDHA.2668@.TK2MSFTNGP09.phx.gbl...
> SQL 2000 error in event log... 2 events as follows:
> 1. OPEN OBJECTS parameter may be set to low
> 2. Run sp_configure to change parameter.
> How can I see what it is set to now? How can I change this value?
> TIA
>

changing the label of the boolean parameter

I'm creating a boolean parameter in Report Designer and set .
Then on Preview Page I see to radio buttons, the first one is labeled
"true" and the other one is "false".? Can I set the labels I want to this
radio buttons?It is not possible to manipulate the boolean true/false labels.
Andy Potter|||Thanks Potter, And what about radio button or checkbox there is a way to put
it in the reporting server as afilter?
"Potter" wrote:
> It is not possible to manipulate the boolean true/false labels.
> Andy Potter
>|||When using the built in parameter selection, the "control type" of the
parameter (which I assume you mean by filter) is determined by the
datatype.
If you need more flexibility, you have to look at creating your own
parameter selection screen and calling the report via URL access or the
web service.
Andy Potter

Changing the color of the series label

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?
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 Row Height Question

I have a report of Assets where I let the user decide if they want to group
on the Asset category or not. If they choose a Group from the Parameter
list, the report puts the Asset Category in the Group Header. If they choose
not to group, then it puts nothing in the Group Header. However, this leaves
a blank spot that doesn't look right. How can get rid of the Group Header if
the user selects '<None>' from the parameter list?
The first field in my Group Header looks like this:
=IIF(Parameters!GroupOn.Value = "Asset Category",
Fields!AssetCategory.Value, "")
--
Thank You!Hi,
Go to the parameters, in the visibility , click on the epression and type
the expression and it will hide accordingly.
OR right click the group,click edit group,click visibility and do the
expression there as well.
cheers
"Shane Eckel" wrote:
> I have a report of Assets where I let the user decide if they want to group
> on the Asset category or not. If they choose a Group from the Parameter
> list, the report puts the Asset Category in the Group Header. If they choose
> not to group, then it puts nothing in the Group Header. However, this leaves
> a blank spot that doesn't look right. How can get rid of the Group Header if
> the user selects '<None>' from the parameter list?
> The first field in my Group Header looks like this:
> =IIF(Parameters!GroupOn.Value = "Asset Category",
> Fields!AssetCategory.Value, "")
> --
> Thank You!|||Nice, I'll try this right now. Thanks for taking the time to reply to my
inquiry.
Take care,
Shane
"Bismi" wrote:
> Hi,
> Go to the parameters, in the visibility , click on the epression and type
> the expression and it will hide accordingly.
> OR right click the group,click edit group,click visibility and do the
> expression there as well.
> cheers
> "Shane Eckel" wrote:
> > I have a report of Assets where I let the user decide if they want to group
> > on the Asset category or not. If they choose a Group from the Parameter
> > list, the report puts the Asset Category in the Group Header. If they choose
> > not to group, then it puts nothing in the Group Header. However, this leaves
> > a blank spot that doesn't look right. How can get rid of the Group Header if
> > the user selects '<None>' from the parameter list?
> >
> > The first field in my Group Header looks like this:
> > =IIF(Parameters!GroupOn.Value = "Asset Category",
> > Fields!AssetCategory.Value, "")
> > --
> > Thank You!|||Thanks for posting this answer, but it isn't behaving correctly.
If I put the expression on the Edit Group level, the entire group hides.
If I put the expression on the textbox, the text within the field hides, but
the row does not shrink.
Any other ideas?
--
Thank You!
"Bismi" wrote:
> Hi,
> Go to the parameters, in the visibility , click on the epression and type
> the expression and it will hide accordingly.
> OR right click the group,click edit group,click visibility and do the
> expression there as well.
> cheers
> "Shane Eckel" wrote:
> > I have a report of Assets where I let the user decide if they want to group
> > on the Asset category or not. If they choose a Group from the Parameter
> > list, the report puts the Asset Category in the Group Header. If they choose
> > not to group, then it puts nothing in the Group Header. However, this leaves
> > a blank spot that doesn't look right. How can get rid of the Group Header if
> > the user selects '<None>' from the parameter list?
> >
> > The first field in my Group Header looks like this:
> > =IIF(Parameters!GroupOn.Value = "Asset Category",
> > Fields!AssetCategory.Value, "")
> > --
> > Thank You!

Changing parameter prompt depending on language

Hi,
is it possible to change the prompt for a parameter depending on the browser
language ?
Kind regardsNo, not at this time. You can use a linked report to customize the labels
per language.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"obeckers" <obeckers@.discussions.microsoft.com> wrote in message
news:655D999C-AA4A-46DC-8424-C20942F24230@.microsoft.com...
> Hi,
> is it possible to change the prompt for a parameter depending on the
> browser
> language ?
> Kind regards
>

Changing Parameter in SQLDataSource for Multiple DropDowns

I have one page, one connection, and three dropdowns.
The connection looks like (as an example):

<asp:SqlDataSource ID="DropDownConn" runat="server" ConnectionString="<%$ ConnectionStrings:aousConnectionString %>"
SelectCommand="SELECT [Value], [Text] FROM [DropDown] WHERE (([Group] = @.Group) AND ([Viewable] = @.Viewable))">
<SelectParameters>
<asp:Parameter Name="Group" Type="String" />
<asp:Parameter DefaultValue="True" Name="Viewable" Type="Boolean" />
</SelectParameters>
</asp:SqlDataSource>

The DropDowns Look like this:

<asp:DropDownList ID="DropDown1" runat="server"></asp:DropDownList>
<asp:DropDownList ID="DropDown2" runat="server"></asp:DropDownList>
<asp:DropDownList ID="DropDown3" runat="server"></asp:DropDownList
The C# Code I am trying is like this:
DropDownConn.SelectParameters["Group"].Equals("DropDown1");
DropDownConn.SelectParameters["Viewable"].Equals(true);
DropDown1.DataSourceID = "DropDownConn";
DropDown1.DataTextField = "Text";
DropDown1.DataValueField = "Value";
DropDown1.DataBind();

As an example. I can not get it done so that I don't have to create 3 dataconnections. Any help, PLEASE?

It should be done this way..

DropDownConn.SelectParameters["Group"].DefaultValue = "some group value"; // change it accordingly on what data should be displayed in the dropdownlists
DropDownConn.SelectParameters["Viewable"].DefaultValue = "True";

DropDown1.DataSource = DropDownConn;
DropDown1.DataTextField = "Text";
DropDown1.DataValueField = "Value";
DropDown1.DataBind();

// for second

DropDownConn.SelectParameters["Group"].DefaultValue = "some group value for second dropdown"; // change it accordingly on what data should be displayed in the dropdownlists
DropDownConn.SelectParameters["Viewable"].DefaultValue = "True";

DropDown2.DataSource = DropDownConn;
DropDown2.DataTextField = "Text";
DropDown2.DataValueField = "Value";
DropDown2.DataBind();

-----

You can also, refactor the code to set the datasource for the dropdownlist.

Thanks

-Mark post(s) as "Answer" that helped you

|||

Thank you for the answer, and that method does work. Is that really the way we are suppose to do it. The default value is such a poor naming scheme and Microsoft had done such a good job with most other things. Thanks for the the help.

Changing Parameter Dropdown Size

I have parameters that are too long to fit into the dropdown box which then
requires horizontal scrolling. How can i change the width of the dropdown
controls?
TIA
DeanOn Jun 25, 9:35 am, "Dean" <deanl...@.hotmail.com.nospam> wrote:
> I have parameters that are too long to fit into the dropdown box which then
> requires horizontal scrolling. How can i change the width of the dropdown
> controls?
> TIA
> Dean
Currently, there is no way to control the width of the drop-down
boxes. Possibly, the next version of SSRS (V2008) will have this
functionality, since this topic has come up a few times in this group.
Sorry that I can not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

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.

Changing DB connection from SqlServer to Oracle

Hi

I am trying to switch between oracle and sqlserver databases to read the source data. I have used a parameter file to specify the connection parameters. For SQLserver the connection looks like in the file as below:

<?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading><DTSConfigurationFileInfo GeneratedBy="ab\vsh" GeneratedFromPackageName="Package" GeneratedFromPackageID="{8A304BF7-5325-4079-9D92-2B9BBF8793AA}" GeneratedDate="1/23/2007 4:46:08 PM"/></DTSConfigurationHeading><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=dl;Initial Catalog=PM_DW;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[Description]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[InitialCatalog]" ValueType="String"><ConfiguredValue>PM_DW</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[Name]" ValueType="String"><ConfiguredValue>dl.PM_DW</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[Password]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[ProtectionLevel]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[RetainSameConnection]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[ServerName]" ValueType="String"><ConfiguredValue>dl</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[UserName]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration></DTSConfiguration>

the pacakge runs fine. But when i change the connection to Oracle, it gives me error.

The config file looks like when i put oracle connection is as follows:

<?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading><DTSConfigurationFileInfo GeneratedBy="I2\vshrivas" GeneratedFromPackageName="Package" GeneratedFromPackageID="{8A304BF7-5325-4079-9D92-2B9BBF8793AA}" GeneratedDate="1/23/2007 4:46:08 PM"/></DTSConfigurationHeading><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=pm62;User ID=pcm_62;Provider=OraOLEDB.Oracle.1;Persist Security Info=True;</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[Description]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[InitialCatalog]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[Name]" ValueType="String"><ConfiguredValue>dl.PM_DW</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[Password]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[ProtectionLevel]" ValueType="Int32"><ConfiguredValue>1</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[RetainSameConnection]" ValueType="Boolean"><ConfiguredValue>0</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[ServerName]" ValueType="String"><ConfiguredValue>i2pm62</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[dl.PM_DW].Properties[UserName]" ValueType="String"><ConfiguredValue>pcm_62</ConfiguredValue></Configuration></DTSConfiguration>

The error which i get when running from oracle source is this:

Information: 0x40016041 at Package: The package is attempting to configure from the XML file "C:\oraTOsql-DataTX\Integration Services Project1\Integration Services Project1\Integration Services Project1\testConfig.dtsConfig".
SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at Package, Connection manager "dl.PM_DW": An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Error: 0xC020801C at Data Flow Task, OLE DB Source [1]: The AcquireConnection method call to the connection manager "dl.PM_DW" failed with error code 0xC0202009.
Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS package "Package.dtsx" finished: Failure.
The program '[7264] Package.dtsx: DTS' has exited with code 0 (0x0).

Please let me know if you guys have any suggestion in what i am doing wrong.

Thanks,

Vipul

First of all, I highly recommend that you only store the ConnectionString property in the configuration file rather than the constituent parts.

Please could you make that change and see if you get the same problem. If you do, post the before & after contents of the new config file up here - it'll be much easier to read than what you have posted above.

-Jamie

|||

Jamie:

Thanks for the advice. Using only connection string in the configuration file works.

Thanks,

Vipul

Changing database within a stored proc

Is this possible in a stored procedure to receive a database name in parameter to be able to apply the code within that stored procedure to all table in the database?

I need to go trough all of the database of the server trough the database list of the master and gather data from all the table and i was wondering if there was a way for me to do a generic code for each database, the only thing that i would need to change would be the database it look into at the moment.

Would something like this work? :

*Code*

*a loop in the database list*

My_stored_proc current_database_name

My stored proc:

Use current_database_name

*Code that work with this database*

Unfortunately, that is not just a simple task.

Yes, you can create a loop through the databases from sys.databases (SQL 2005) or sysdatabases (SQL 2000).

However, you cannot simply use an 'Object' name as you would use a variable or parameter. So the statement [ USE @.Variable ] does not work. You could create an entire SQL statement as a variable, and then execute that statement using dynamic SQL -but that too has it issues.

First you have to 'build' the entire SQL statement; that's not too difficult. something like (peusdocode):

Code Snippet

DECLARE
@.dbName nvarchar(200),
@.SQLStatement nvarchar(1000)
{loop here}
SET @.dbName = name in sys.databases {selected from loop position}
SET @.SQLStatement = 'Use ' + @.dbname
EXECUTE( @.SQLStatement )

Now at this point, there is a problem, because the EXECUTE (or sp_executesql) statement operates in a separate thread, and any results are not directly accessible to the rest of your code. The next line of your code would NOT use any database other then the one you started out with because as soon as the EXECUTE statement completes, control is returned back to the original thread -which has no idea what happened when the EXECUTE statement ran.

There are methods to get around that behavior, but the most common ones require extensive knowledge of how EXECUTE() or sp_executesql operates.

I recommend that you first review this article, and then come back for assistance if you wish to continue down this path.

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

The key element that I hope you get here is, yes, someone can create the functionality. But will you be able to understand and maintain the code, or will you be 'up a creek' if something changes? ... Your best option if you have to do this, is to learn and understand how to use and what is happening with dynamic SQL as it is being used. Without that understanding, you may continually be frustrated.

|||

Thanks for the text, it was very interesting.

I found out that using cursor will be much more usefull for going trough all the table of the databases.

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.