Problems displaying default value of first cascading parameters.

I have 5 cascading parameters. They all have default values. I would like the default value of each of these to be selected when the report opens.

It currently works correctly when I preview the report, however when I deploy the report to the server it does not. In the deployed report, the default value of the first parameter is not selected. However, when I select a value for this one, on postback the rest of these parameters get set to their default value. They're all configured the same. I'm confused as to why the first one doesn't default to its default value while the others do.

Got any ideas?

Here's how I have it configured:

Allow null value CHECKED

Allow blank value CHECKED

The rest are unchecked.

Available values: From query

Default Values: Non-queried with a value supplied that exists in the dataset.

This issue got resolved when I deleted all of my reports from the server and then redeployed them. Funny how that works...sql

Problems displaying default value of first cascading parameters.

I have 5 cascading parameters. They all have default values. I would like the default value of each of these to be selected when the report opens.

It currently works correctly when I preview the report, however when I deploy the report to the server it does not. In the deployed report, the default value of the first parameter is not selected. However, when I select a value for this one, on postback the rest of these parameters get set to their default value. They're all configured the same. I'm confused as to why the first one doesn't default to its default value while the others do.

Got any ideas?

Here's how I have it configured:

Allow null value CHECKED

Allow blank value CHECKED

The rest are unchecked.

Available values: From query

Default Values: Non-queried with a value supplied that exists in the dataset.

This issue got resolved when I deleted all of my reports from the server and then redeployed them. Funny how that works...

Problems converting varchar to smallmoney

Hi

My ticket engine stores values in varchar. The sql db-field that
corresponds was created as smallmoney.

The below statement works for conversion of "leavedays" if the given
value is entered without any decimal places (E.G. 4)

As soon as a user enters a value that includes decimal places (E.G.
4.5) the conversion will not work. In this case the value 4.5 is
rounded to 5.

What do i have to do to convert the value as it is entered by the user?

Thanks in advance

t.

Statement:

INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
{1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
{4}),{5}, getdate()

DDL for concerned database:

CREATE TABLE [dbo].[leavereq] (
[mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[startdate] datetime NULL,
[enddate] datetime NULL,
[leavedays] smallmoney NULL,
[remainingdays] smallmoney NULL,
[approvedon] datetime NULL,
[approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GO

I am little confused. In your INSERT INTO statement, you are converting {3} and {4} to numeric, while the datatype for these two columns are smallmoney. Why don't you use smallmoney directly in the INSERT statement?

The reason it rounds up for you is that the default scale (max number of decimal digits) of numeric data type is 0. It would work fine if you use numeric(20, 2) for example. See "decimal and numeric (Transact-SQL)" in SQL Server Book Online for details.

|||

thx

seems to work now using smallmoney directly or numeric (20,2)

Problem: Using Custom Code to provide default values for parameter

Hi, I have reports with parameters that use custom code, and when you hide
the parameters in the report manager the reports no longer work.
I thought it was a parameter dependancy problem, but have done the following
test to prove its not:
I have just created a report, with one parameter called prmUserID that gets
its value from my dll. The default non-queried value for this parameter is
set as follows:
=libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True , False,
False )
The report displays a table, and the table references a dataset that uses
the parameter value. This is the string for the dataset:
="Select * from mhgroup.docusers where userid='" &
Parameters!prmUserID.Value & "'"
The report works fine when I deploy it to the report
manager. The parameter shows the value returned from my
dll correctly.
However, I changed the parameter properties of my report in report manager
and took the tick out of the prompt user checkbox. The report no longer
works - I'm pretty sure that my prmUserID is just parsing an empty string now
to the dataset. Maybe once you've hidden a parameter the code from my dll is
no longer called?
I also know its not a problem with my dll as I did the same test above but
used custom code directly in the report (ie added to the code tab of the
report properties dialog). This produced the same result - deployed it,
worked fine, hide the parameter and the code no longer seems to run.
I would be grateful if someone at MS could try and replicate this and let me
know if I'm doing something wrong or if its a bug.
My Firm is VERY keen to move forward with Reporting Services, the printing
issue (which I know is being addressed in SP2) and this parameter problem is
the only thing holding us back at the moment.
Thanks
JeanineHave you installed SP1?... I think that hidden parameters are NOT settable
unless you have SP1 installed...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
news:E953EF89-AD1F-408D-9729-AC99C109D784@.microsoft.com...
> Hi, I have reports with parameters that use custom code, and when you hide
> the parameters in the report manager the reports no longer work.
> I thought it was a parameter dependancy problem, but have done the
> following
> test to prove its not:
> I have just created a report, with one parameter called prmUserID that
> gets
> its value from my dll. The default non-queried value for this parameter
> is
> set as follows:
> =libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True ,
> False,
> False )
> The report displays a table, and the table references a dataset that uses
> the parameter value. This is the string for the dataset:
> ="Select * from mhgroup.docusers where userid='" &
> Parameters!prmUserID.Value & "'"
> The report works fine when I deploy it to the report
> manager. The parameter shows the value returned from my
> dll correctly.
> However, I changed the parameter properties of my report in report manager
> and took the tick out of the prompt user checkbox. The report no longer
> works - I'm pretty sure that my prmUserID is just parsing an empty string
> now
> to the dataset. Maybe once you've hidden a parameter the code from my dll
> is
> no longer called?
> I also know its not a problem with my dll as I did the same test above but
> used custom code directly in the report (ie added to the code tab of the
> report properties dialog). This produced the same result - deployed it,
> worked fine, hide the parameter and the code no longer seems to run.
> I would be grateful if someone at MS could try and replicate this and let
> me
> know if I'm doing something wrong or if its a bug.
> My Firm is VERY keen to move forward with Reporting Services, the printing
> issue (which I know is being addressed in SP2) and this parameter problem
> is
> the only thing holding us back at the moment.
> Thanks
> Jeanine|||Thanks for you reply, yes I do have SP1 installed.
"Wayne Snyder" wrote:
> Have you installed SP1?... I think that hidden parameters are NOT settable
> unless you have SP1 installed...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
> news:E953EF89-AD1F-408D-9729-AC99C109D784@.microsoft.com...
> > Hi, I have reports with parameters that use custom code, and when you hide
> > the parameters in the report manager the reports no longer work.
> >
> > I thought it was a parameter dependancy problem, but have done the
> > following
> > test to prove its not:
> >
> > I have just created a report, with one parameter called prmUserID that
> > gets
> > its value from my dll. The default non-queried value for this parameter
> > is
> > set as follows:
> >
> > =libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True ,
> > False,
> > False )
> >
> > The report displays a table, and the table references a dataset that uses
> > the parameter value. This is the string for the dataset:
> >
> > ="Select * from mhgroup.docusers where userid='" &
> > Parameters!prmUserID.Value & "'"
> >
> > The report works fine when I deploy it to the report
> > manager. The parameter shows the value returned from my
> > dll correctly.
> >
> > However, I changed the parameter properties of my report in report manager
> > and took the tick out of the prompt user checkbox. The report no longer
> > works - I'm pretty sure that my prmUserID is just parsing an empty string
> > now
> > to the dataset. Maybe once you've hidden a parameter the code from my dll
> > is
> > no longer called?
> >
> > I also know its not a problem with my dll as I did the same test above but
> > used custom code directly in the report (ie added to the code tab of the
> > report properties dialog). This produced the same result - deployed it,
> > worked fine, hide the parameter and the code no longer seems to run.
> >
> > I would be grateful if someone at MS could try and replicate this and let
> > me
> > know if I'm doing something wrong or if its a bug.
> >
> > My Firm is VERY keen to move forward with Reporting Services, the printing
> > issue (which I know is being addressed in SP2) and this parameter problem
> > is
> > the only thing holding us back at the moment.
> >
> > Thanks
> > Jeanine
>
>|||You need to change the report parameter property. Set the PROMPT string to
"" and the prompt value to TRUE.
It will not display the prompt but at least it will be updatable.
=-Chris
"Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
news:E953EF89-AD1F-408D-9729-AC99C109D784@.microsoft.com...
> Hi, I have reports with parameters that use custom code, and when you hide
> the parameters in the report manager the reports no longer work.
> I thought it was a parameter dependancy problem, but have done the
> following
> test to prove its not:
> I have just created a report, with one parameter called prmUserID that
> gets
> its value from my dll. The default non-queried value for this parameter
> is
> set as follows:
> =libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True ,
> False,
> False )
> The report displays a table, and the table references a dataset that uses
> the parameter value. This is the string for the dataset:
> ="Select * from mhgroup.docusers where userid='" &
> Parameters!prmUserID.Value & "'"
> The report works fine when I deploy it to the report
> manager. The parameter shows the value returned from my
> dll correctly.
> However, I changed the parameter properties of my report in report manager
> and took the tick out of the prompt user checkbox. The report no longer
> works - I'm pretty sure that my prmUserID is just parsing an empty string
> now
> to the dataset. Maybe once you've hidden a parameter the code from my dll
> is
> no longer called?
> I also know its not a problem with my dll as I did the same test above but
> used custom code directly in the report (ie added to the code tab of the
> report properties dialog). This produced the same result - deployed it,
> worked fine, hide the parameter and the code no longer seems to run.
> I would be grateful if someone at MS could try and replicate this and let
> me
> know if I'm doing something wrong or if its a bug.
> My Firm is VERY keen to move forward with Reporting Services, the printing
> issue (which I know is being addressed in SP2) and this parameter problem
> is
> the only thing holding us back at the moment.
> Thanks
> Jeanine|||Thanks for that. I tried that and it worked - as long as you don't then go
and edit the report parameters in report manager.
What I would really like to do is have linked reports, one with a boolean
parameter set to true and one with a boolean parameter set to false.
So as soon as I use the report manager to alter the report parameter
properties (even though I've done you've said below), the reports don't work
properly again.
I still think this is a bug of some kind, and am hoping MS come up with a
fix for this.
Jeanine
"Christopher Conner" wrote:
> You need to change the report parameter property. Set the PROMPT string to
> "" and the prompt value to TRUE.
> It will not display the prompt but at least it will be updatable.
> =-Chris
>
> "Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
> news:E953EF89-AD1F-408D-9729-AC99C109D784@.microsoft.com...
> > Hi, I have reports with parameters that use custom code, and when you hide
> > the parameters in the report manager the reports no longer work.
> >
> > I thought it was a parameter dependancy problem, but have done the
> > following
> > test to prove its not:
> >
> > I have just created a report, with one parameter called prmUserID that
> > gets
> > its value from my dll. The default non-queried value for this parameter
> > is
> > set as follows:
> >
> > =libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True ,
> > False,
> > False )
> >
> > The report displays a table, and the table references a dataset that uses
> > the parameter value. This is the string for the dataset:
> >
> > ="Select * from mhgroup.docusers where userid='" &
> > Parameters!prmUserID.Value & "'"
> >
> > The report works fine when I deploy it to the report
> > manager. The parameter shows the value returned from my
> > dll correctly.
> >
> > However, I changed the parameter properties of my report in report manager
> > and took the tick out of the prompt user checkbox. The report no longer
> > works - I'm pretty sure that my prmUserID is just parsing an empty string
> > now
> > to the dataset. Maybe once you've hidden a parameter the code from my dll
> > is
> > no longer called?
> >
> > I also know its not a problem with my dll as I did the same test above but
> > used custom code directly in the report (ie added to the code tab of the
> > report properties dialog). This produced the same result - deployed it,
> > worked fine, hide the parameter and the code no longer seems to run.
> >
> > I would be grateful if someone at MS could try and replicate this and let
> > me
> > know if I'm doing something wrong or if its a bug.
> >
> > My Firm is VERY keen to move forward with Reporting Services, the printing
> > issue (which I know is being addressed in SP2) and this parameter problem
> > is
> > the only thing holding us back at the moment.
> >
> > Thanks
> > Jeanine
>
>|||Well first off - users shouldn't have rights to modify the report
parameters.
Second, I set those properties in the report designer - not off the report
manager. It would be a pain to update those reports everytime I did a
deployment.
What happens when you do this inside your report designer with your linked
report?
I'll see if I can replicate your issue here, but otherwise I haven't run
into any problems - albeit just a few quirks like the solution I just gave
you to not show parameters but yet allow them to be updatable.
=-Chris
"Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
news:038B93E0-E09F-487E-877F-FC1F2DCDB0E9@.microsoft.com...
> Thanks for that. I tried that and it worked - as long as you don't then
> go
> and edit the report parameters in report manager.
> What I would really like to do is have linked reports, one with a boolean
> parameter set to true and one with a boolean parameter set to false.
> So as soon as I use the report manager to alter the report parameter
> properties (even though I've done you've said below), the reports don't
> work
> properly again.
> I still think this is a bug of some kind, and am hoping MS come up with a
> fix for this.
> Jeanine
>
> "Christopher Conner" wrote:
>> You need to change the report parameter property. Set the PROMPT string
>> to
>> "" and the prompt value to TRUE.
>> It will not display the prompt but at least it will be updatable.
>> =-Chris
>>
>> "Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
>> news:E953EF89-AD1F-408D-9729-AC99C109D784@.microsoft.com...
>> > Hi, I have reports with parameters that use custom code, and when you
>> > hide
>> > the parameters in the report manager the reports no longer work.
>> >
>> > I thought it was a parameter dependancy problem, but have done the
>> > following
>> > test to prove its not:
>> >
>> > I have just created a report, with one parameter called prmUserID that
>> > gets
>> > its value from my dll. The default non-queried value for this
>> > parameter
>> > is
>> > set as follows:
>> >
>> > =libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True ,
>> > False,
>> > False )
>> >
>> > The report displays a table, and the table references a dataset that
>> > uses
>> > the parameter value. This is the string for the dataset:
>> >
>> > ="Select * from mhgroup.docusers where userid='" &
>> > Parameters!prmUserID.Value & "'"
>> >
>> > The report works fine when I deploy it to the report
>> > manager. The parameter shows the value returned from my
>> > dll correctly.
>> >
>> > However, I changed the parameter properties of my report in report
>> > manager
>> > and took the tick out of the prompt user checkbox. The report no
>> > longer
>> > works - I'm pretty sure that my prmUserID is just parsing an empty
>> > string
>> > now
>> > to the dataset. Maybe once you've hidden a parameter the code from my
>> > dll
>> > is
>> > no longer called?
>> >
>> > I also know its not a problem with my dll as I did the same test above
>> > but
>> > used custom code directly in the report (ie added to the code tab of
>> > the
>> > report properties dialog). This produced the same result - deployed
>> > it,
>> > worked fine, hide the parameter and the code no longer seems to run.
>> >
>> > I would be grateful if someone at MS could try and replicate this and
>> > let
>> > me
>> > know if I'm doing something wrong or if its a bug.
>> >
>> > My Firm is VERY keen to move forward with Reporting Services, the
>> > printing
>> > issue (which I know is being addressed in SP2) and this parameter
>> > problem
>> > is
>> > the only thing holding us back at the moment.
>> >
>> > Thanks
>> > Jeanine
>>|||Hi,
1) Users will not be modifying report parameters settings. They will
however be able to use the prms that I set as being visible.
2) What I'm trying to achieve is to have 1 main report and create from this,
several linked reports. By linked reports, I'm referring to the ability to
create a linked report from another report in Report manager. These linked
reports would have different parameter settings. This way I can use the
parameter information to tailor 1 main report, dependant upon the parameter
values. This saves me from having to create multiple reports in the report
designer, that contain basically the same information.
I tried your suggestion, with one of my production reports that has quite a
few parameters, and it worked fine. However to achieve 2) I will need to
create from this a linked report, change the parameter settings - which will
involve things such as changing a boolean prm from true to false etc.
If it is indeed a problem it is very easy to replicate, just create a report
with a parameter and set the default value of the parameter to a string
returned from a function in the custom code of the report. Use this
parameter in the datasource of the report, eg, ="Select * from
mhgroup.docusers where userid='" &
Parameters!prmUserID.Value & "'"
Deploy the report, use the report manager to hide the parameter, and the
report should then no longer display the correct information (because the
default value of the parameter was not populated with the custom code value).
Thanks very much.
"Christopher Conner" wrote:
> Well first off - users shouldn't have rights to modify the report
> parameters.
> Second, I set those properties in the report designer - not off the report
> manager. It would be a pain to update those reports everytime I did a
> deployment.
> What happens when you do this inside your report designer with your linked
> report?
> I'll see if I can replicate your issue here, but otherwise I haven't run
> into any problems - albeit just a few quirks like the solution I just gave
> you to not show parameters but yet allow them to be updatable.
> =-Chris
>
> "Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
> news:038B93E0-E09F-487E-877F-FC1F2DCDB0E9@.microsoft.com...
> > Thanks for that. I tried that and it worked - as long as you don't then
> > go
> > and edit the report parameters in report manager.
> >
> > What I would really like to do is have linked reports, one with a boolean
> > parameter set to true and one with a boolean parameter set to false.
> >
> > So as soon as I use the report manager to alter the report parameter
> > properties (even though I've done you've said below), the reports don't
> > work
> > properly again.
> >
> > I still think this is a bug of some kind, and am hoping MS come up with a
> > fix for this.
> >
> > Jeanine
> >
> >
> > "Christopher Conner" wrote:
> >
> >> You need to change the report parameter property. Set the PROMPT string
> >> to
> >> "" and the prompt value to TRUE.
> >>
> >> It will not display the prompt but at least it will be updatable.
> >>
> >> =-Chris
> >>
> >>
> >>
> >> "Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
> >> news:E953EF89-AD1F-408D-9729-AC99C109D784@.microsoft.com...
> >> > Hi, I have reports with parameters that use custom code, and when you
> >> > hide
> >> > the parameters in the report manager the reports no longer work.
> >> >
> >> > I thought it was a parameter dependancy problem, but have done the
> >> > following
> >> > test to prove its not:
> >> >
> >> > I have just created a report, with one parameter called prmUserID that
> >> > gets
> >> > its value from my dll. The default non-queried value for this
> >> > parameter
> >> > is
> >> > set as follows:
> >> >
> >> > =libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True ,
> >> > False,
> >> > False )
> >> >
> >> > The report displays a table, and the table references a dataset that
> >> > uses
> >> > the parameter value. This is the string for the dataset:
> >> >
> >> > ="Select * from mhgroup.docusers where userid='" &
> >> > Parameters!prmUserID.Value & "'"
> >> >
> >> > The report works fine when I deploy it to the report
> >> > manager. The parameter shows the value returned from my
> >> > dll correctly.
> >> >
> >> > However, I changed the parameter properties of my report in report
> >> > manager
> >> > and took the tick out of the prompt user checkbox. The report no
> >> > longer
> >> > works - I'm pretty sure that my prmUserID is just parsing an empty
> >> > string
> >> > now
> >> > to the dataset. Maybe once you've hidden a parameter the code from my
> >> > dll
> >> > is
> >> > no longer called?
> >> >
> >> > I also know its not a problem with my dll as I did the same test above
> >> > but
> >> > used custom code directly in the report (ie added to the code tab of
> >> > the
> >> > report properties dialog). This produced the same result - deployed
> >> > it,
> >> > worked fine, hide the parameter and the code no longer seems to run.
> >> >
> >> > I would be grateful if someone at MS could try and replicate this and
> >> > let
> >> > me
> >> > know if I'm doing something wrong or if its a bug.
> >> >
> >> > My Firm is VERY keen to move forward with Reporting Services, the
> >> > printing
> >> > issue (which I know is being addressed in SP2) and this parameter
> >> > problem
> >> > is
> >> > the only thing holding us back at the moment.
> >> >
> >> > Thanks
> >> > Jeanine
> >>
> >>
> >>
>
>|||i myself are having a similar problem
i believe its a BUG in report manager,
that changing any of the options for parameters in report manager
(which is what is needed to be done to make a writable hidden
parameter) causes default calculations (even query based) to not work
for parameters that are not pull down boxes.|||Thanks for that. Its nice to know I'm not the only one experiencing this
problem. I'm hoping somone from MS may be able to shed some light on this as
well.
"klumsy@.xtra.co.nz" wrote:
> i myself are having a similar problem
> i believe its a BUG in report manager,
> that changing any of the options for parameters in report manager
> (which is what is needed to be done to make a writable hidden
> parameter) causes default calculations (even query based) to not work
> for parameters that are not pull down boxes.
>|||Please check this related thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=5d83b939-71ab-47fa-9f85-9d15d90dacb9
Also check my recent posting in that same thread for a possible workaround
on RS 2000 (specifying hidden parameters through report designer).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
news:F1B09944-8648-49FD-95AE-66004ECD212D@.microsoft.com...
> Thanks for that. Its nice to know I'm not the only one experiencing this
> problem. I'm hoping somone from MS may be able to shed some light on this
> as
> well.
>
> "klumsy@.xtra.co.nz" wrote:
>> i myself are having a similar problem
>> i believe its a BUG in report manager,
>> that changing any of the options for parameters in report manager
>> (which is what is needed to be done to make a writable hidden
>> parameter) causes default calculations (even query based) to not work
>> for parameters that are not pull down boxes.
>>|||Thanks Robert. I'll look forward to the release of Yukon.
"Robert Bruckner [MSFT]" wrote:
> Please check this related thread:
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=5d83b939-71ab-47fa-9f85-9d15d90dacb9
> Also check my recent posting in that same thread for a possible workaround
> on RS 2000 (specifying hidden parameters through report designer).
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
> news:F1B09944-8648-49FD-95AE-66004ECD212D@.microsoft.com...
> > Thanks for that. Its nice to know I'm not the only one experiencing this
> > problem. I'm hoping somone from MS may be able to shed some light on this
> > as
> > well.
> >
> >
> > "klumsy@.xtra.co.nz" wrote:
> >
> >> i myself are having a similar problem
> >> i believe its a BUG in report manager,
> >> that changing any of the options for parameters in report manager
> >> (which is what is needed to be done to make a writable hidden
> >> parameter) causes default calculations (even query based) to not work
> >> for parameters that are not pull down boxes.
> >>
> >>
>
>

PROBLEM: Report parameters can not be set. Default values always take priority.

I am trying to create a report with parameters which are prompted by the user and are initially populated with default values. However, whenever I edit the parameters in Report Viewer and click 'View Report', the parameter values revert to their default value. The user-specified parameters never "stick".

Please help!

The xml looks like this:

<ReportParameters>

<ReportParameter Name="ConnectString">

<DataType>String</DataType>

<Prompt>ConnectString</Prompt>

<Hidden>true</Hidden>

</ReportParameter>

<ReportParameter Name="FromDate">

<DataType>DateTime</DataType>

<DefaultValue>

<Values>

<Value>2006-1-1</Value>

</Values>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<Prompt>From:</Prompt>

</ReportParameter>

<ReportParameter Name="ToDate">

<DataType>DateTime</DataType>

<DefaultValue>

<Values>

<Value>2006-12-31</Value>

</Values>

</DefaultValue>

<AllowBlank>true</AllowBlank>

<Prompt>To:</Prompt>

</ReportParameter>

</ReportParameters>

One more observation: It works within Report Manager. The problem is when the report is hosted in my application. It seems there might be something in my code that is causing the user-specified parameters to be ignored.

I tried eliminating SetParameters call for the one hidden parameter, and I made sure I was setting ShowParameterPrompts attribute once (in case changing value would reset parameters). The problem still exists.

|||One possible cause is that you are setting the report path or report server url after the LoadViewState event on the postback. If you do this, the report viewer control will see this as a change in the report definition and reload the report from the server, ignoring any input from the postback because it applies to the "old report". Try setting the report path/server url during OnInit, or only when Page.IsPostBack is false.|||

Thank you Brian! That worked!

I verified that either setting ServerReport properties in OnInit(), or after checking Page.IsPostBack is false in Page_Load(), will fix this problem. I also had to call SetParameters within these same constraints.

On to new problems...

Problem: Parameter with available values from query

=========================================

dataSource: dsABC (ODBC)

dataSet: dsetABC

=========================================

I have a parameterized report and one of parameter with available values from

query.

The query for the parameter is from another dataset (dsCompany) in order to allow

users choosing the company values. In the Designer platform, it normally provide a

drop-down list including expected company values. But when i upload to reporting

server (http://xxxx/reports) and the following error messages will appear

Reporting processing error (rsProcessingAborted)

...............Cannot connect to DataSource dsABC. (rsErrorOpeningConnection)

...........................................................................

Besides, i can run the report normally if i remove the task of "available values from

query".

How can i solve this problem? Thank you for any opinion.

Is dsCompany using a different datasource? If so, make sure that is deployed to your Report Server also.|||

guyinkalamazoo3 wrote:

Is dsCompany using a different datasource? If so, make sure that is deployed to your Report Server

also.

In fact, the dataset of dsetABC & dsCompany are also used a dataSource "dsABC",

now just the "available values" task make error

in the report. Why? (the report run normally if the "available values" task is removed in

Report Server)

|||

Anyone can give me some suggestions. thank you!

Problem with values returned by Calculated Member

Dear Friends,

I have a problem with CM:

Example

Row Field1 Field2 Field3 Total

1 31662 1 14,75 467014,5

2 250000 1 6,94 1735000

Total for each row= Field1*Field3 / Field2

The value I want for Calculated Member is Total1 + Total 2, 2202014,5, but the value is wrong... The calculated member gives me the value from: SUM Field1* SUM Field3 / SUM Field2, 3054624,39

The MDX:

[Measures].[Open Pos]*[Measures].[New Price]/[Measures].[Fx Rate Rep Cur]

And all the measures are with AggregateFunction as SUM...

Where is the problem? How can I calculate each row in spite of the sum?

Thanks!!

More help to you:

29-12-2006 BANCO PASTOR SA 31662 1 14,75 467.014,50 29-12-2006 GALP ENERGIA SGPS SA B 250000 1 6,94 1.735.000,00

For the date 29.12.2006 I want the calculated member with the value 2202014,5

And currently I'having the value 3054624,39.

|||Is the required calculation Sum(Field 1 * Field 3) / Sum (Field 2)? If so, try adding a calculated column to the DSV that does the (Field 1 * Field 3) calculation. Add this as a new measure, with Sum as the aggregation function. Then your calculated measure will be (New Measure) / (Field 2).|||

for the date 29-12-2006 I need to calculate each line of fact Table...

Example:

Date Field1 Field2 Field3 EachRow 29-12-2006 100 1 5 500 29-12-2006 200 1 4 800 29-12-2006 300 1 2 600 Correct 1900

As I did, in Calculated Member, the value returned is not 1900, but is 2200!! :-(

29-12-2006 600 3 11 2200

Understood?

Thanks!

|||

Then just add the entire calculation ( (Field 1 * Field 3) / Field 2 ) as a calculated column in your DSV. Create a new measure on that column, and set the AggregationFunction to SUM.

Anytime you need something calculated at the fact level, it's usually best to either add it to fact table directly, or include it in the DSV.

|||But field1 is a named calculation, and I cant use this value to create a new named calculation... :-(|||You should be able to replicate the calculation for Field1 in your new calculated column.|||

How can I do it?

When I am creating the second named calculation, in the expression textbox I wrote: [Field1]+1

and returned me the error message: Invalid column named 'Field1'

Field1 is a named calculation... :-(

|||Copy the expression from Field1 into Field2, and add the additional calculations that need to be performed.|||

The problem is that the calculations in named calculation Field 1 is not so simple...

yeh.. but I understood that is no possible call a NC from another NC... thanks!

Problem with values returned by Calculated Member

Dear Friends,

I have a problem with CM:

Example

Row Field1 Field2 Field3 Total

1 31662 1 14,75 467014,5

2 250000 1 6,94 1735000

Total for each row= Field1*Field3 / Field2

The value I want for Calculated Member is Total1 + Total 2, 2202014,5, but the value is wrong... The calculated member gives me the value from: SUM Field1* SUM Field3 / SUM Field2, 3054624,39

The MDX:

[Measures].[Open Pos]*[Measures].[New Price]/[Measures].[Fx Rate Rep Cur]

And all the measures are with AggregateFunction as SUM...

Where is the problem? How can I calculate each row in spite of the sum?

Thanks!!

More help to you:

29-12-2006

BANCO PASTOR SA

31662

1

14,75

467.014,50

29-12-2006

GALP ENERGIA SGPS SA B

250000

1

6,94

1.735.000,00

For the date 29.12.2006 I want the calculated member with the value 2202014,5

And currently I'having the value 3054624,39.

|||Is the required calculation Sum(Field 1 * Field 3) / Sum (Field 2)? If so, try adding a calculated column to the DSV that does the (Field 1 * Field 3) calculation. Add this as a new measure, with Sum as the aggregation function. Then your calculated measure will be (New Measure) / (Field 2).|||

for the date 29-12-2006 I need to calculate each line of fact Table...

Example:

Date

Field1

Field2

Field3

EachRow

29-12-2006

100

1

5

500

29-12-2006

200

1

4

800

29-12-2006

300

1

2

600

Correct

1900

As I did, in Calculated Member, the value returned is not 1900, but is 2200!! :-(

29-12-2006

600

3

11

2200

Understood?

Thanks!

|||

Then just add the entire calculation ( (Field 1 * Field 3) / Field 2 ) as a calculated column in your DSV. Create a new measure on that column, and set the AggregationFunction to SUM.

Anytime you need something calculated at the fact level, it's usually best to either add it to fact table directly, or include it in the DSV.

|||But field1 is a named calculation, and I cant use this value to create a new named calculation... :-(|||You should be able to replicate the calculation for Field1 in your new calculated column.|||

How can I do it?

When I am creating the second named calculation, in the expression textbox I wrote: [Field1]+1

and returned me the error message: Invalid column named 'Field1'

Field1 is a named calculation... :-(

|||Copy the expression from Field1 into Field2, and add the additional calculations that need to be performed.|||

The problem is that the calculations in named calculation Field 1 is not so simple...

yeh.. but I understood that is no possible call a NC from another NC... thanks!

Problem with using 'OutPut Clause' in SQL SEERVER 2005

Hi everyBody,

I Tried to use "OutPut" clause in insert statment as fallows:

insert into test

OUTPUT Inserted.Name,Inserted.LastName

values ('John','kransky')

The Error:

Line 2: Incorrect syntax near 'OUTPUT'.

but I face with this error,I checked MSDN and the other sources, it seems everything is ok. But when I run it ,I face with that error message.I would be appriciate if someone help me on it.

OUTPUT values MUST be 'captured' in some fashion. Typically, the OUTPUT values are captured in a @.Temp table.

For Example, this might work for you:

Code Snippet

DECLARE @.NewRows table

( [Name] varchar(20),

LastName varchar(20)

);

INSERT INTO Test

OUTPUT Inserted.Name, Inserted.LastName

INTO @.NewRows

VALUES ( 'John', 'kransky' );

Then you can use the data in the @.NewRows table however you wish.

Problem with using ''OutPut Clause'' in SQL SEERVER 2005

Hi everyBody,

I Tried to use "OutPut" clause in insert statment as fallows:

insert into test

OUTPUT Inserted.Name,Inserted.LastName

values ('John','kransky')

The Error:

Line 2: Incorrect syntax near 'OUTPUT'.

but I face with this error,I checked MSDN and the other sources, it seems everything is ok. But when I run it ,I face with that error message.I would be appriciate if someone help me on it.

OUTPUT values MUST be 'captured' in some fashion. Typically, the OUTPUT values are captured in a @.Temp table.

For Example, this might work for you:

Code Snippet

DECLARE @.NewRows table

( [Name] varchar(20),

LastName varchar(20)

);

INSERT INTO Test

OUTPUT Inserted.Name, Inserted.LastName

INTO @.NewRows

VALUES ( 'John', 'kransky' );

Then you can use the data in the @.NewRows table however you wish.

Problem with update when updating all rows of a table through dataset and saving back to d

Hi,

I have an application where I'm filling a dataset with values from a table. This table has no primary key. Then I iterate through each row of the dataset and I compute the value of one of the columns and then update that value in the dataset row. The problem I'm having is that when the database gets updated by the SqlDataAdapter.Update() method, the same value shows up under that column for all rows. I think my Update Command is not correct since I'm not specifying a where clause and hence it is using just the value lastly computed in the dataset to update the entire database. But I do not know how to specify a where clause for an update statement when I'm actually updating every row in the dataset. Basically I do not have an update parameter since all rows are meant to be updated. Any suggestions?

SqlCommand snUpdate = conn.CreateCommand();

snUpdate.CommandType =CommandType.Text;

snUpdate.CommandText ="Update TestTable set shipdate = @.shipdate";

snUpdate.Parameters.Add("@.shipdate",SqlDbType.Char, 10,"shipdate");

string jdate ="";

for (int i = 0; i < ds.Tables[0].Rows.Count - 1; i++)

{

jdate = ds.Tables[0].Rows[i]["shipdate"].ToString();

ds.Tables[0].Rows[i]["shipdate"] = convertToNormalDate(jdate);

}

da.Update(ds,"Table1");

conn.Close();

-Thanks

a quick once over and i have a few questions--what all in your TestTable? Also in all lines of code you refer to your table as Tables[0] you should always stick to one way. Your right about your Update, so when we see what's in your TestTable we'll be able to help you out a little bit more.

|||

Thanks for your response. My table contains 2 fields SKU numbers and Ship Dates. I changed my update statement as follows and it worked.

snUpdate.CommandText ="Update TestTable set shipdate = @.shipdate where skunum=@.skunum";

snUpdate.Parameters.Add("@.shipdate",SqlDbType.Char, 10,"shipdate");

snUpdate.Parameters.Add("@.skunum",SqlDbType.Int, 4,"skunum");

snUpdate.Parameters["@.skunum"].SourceVersion =DataRowVersion.Original;

-Thanks

|||So is skunum a unique value? If not, then it'll update all rows with that skunum. If it is, then why isn't that your primary key?|||The skunum is not unique. The table does have some duplicated data which I don't have to worry about if they get updated with the same value. This is just an old table whose data will be transformed into a different table and used in an app after my updates are done. Thanks.