Page Numbers in Excel: values

Showing posts with label values. Show all posts

Friday, March 30, 2012

Parameter Prompt List & Filter selection

I have created a parameter for a report that displays a drop-down list of
values to pick from. The parameter values are defined as being "loaded from
a query." The problen is that I need to have the option of blanks as a valid
value in this parameter so that the report can be generated for "all" values
too. Note: The query is the result of a dataset which executes an MDX
statement .
Part II of my problem is that I want to be able to filtermy results to say
either "part number = parameter value", or part number <> " ". I can't seem
to conditionally set the filter. Perhaps I am using the wrong syntax. Note:
I have tried placing the filter on both the dataset and the matrix object.
Any help would be greatly appreciated!
PBOn Mar 8, 12:37 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I have created a parameter for a report that displays a drop-down list of
> values to pick from. The parameter values are defined as being "loaded from
> a query." The problen is that I need to have the option of blanks as a valid
> value in this parameter so that the report can be generated for "all" values
> too. Note: The query is the result of a dataset which executes an MDX
> statement .
> Part II of my problem is that I want to be able to filtermy results to say
> either "part number = parameter value", or part number <> " ". I can't seem
> to conditionally set the filter. Perhaps I am using the wrong syntax. Note:
> I have tried placing the filter on both the dataset and the matrix object.
> Any help would be greatly appreciated!
> PB
If you install Service Pack 2, you will have 'Select All' as an
available option for multi-select parameters. For the most part,
filters can only do and-ing, not or-ing. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Deveoper

Wednesday, March 28, 2012

Parameter not refreshing default data selection

Here is the senerio:

Parameter 1: Company - multi-value

Parameter 2: Employee - multi-value

Available values: Query - Dataset: EmpList

Default values: Query - Dataset: EmpList

Parameter 2 is dependant on Parameter 1.

When a Company is selected, it does update the list of employees in parameter 2. But what it isn't doing is updating my default values.

I select Company A. It populates the Employee list with all of Company A's employees and marks them all as selected (check in the checkbox) and the "Select All" checkbox is also checked.

I then decide I want to include Company B. It populates the Employee list with all of Company B's employees but it does NOT select the employee's from Company B. It is like it ignored the default values query.

I would also think that if "Select All" is checked all new values coming in would be checked.

Is there a way around this problem?

I need to know if this is a bug or if there is something I can do to fix this.

parameter modification within mdx

Is it possible to modify a parameter value within a dataset using mdx?

lets say the param values of @.bucket = 6

how would you go about writing @.bucket + 1

so @.bucket now would equal 7

is this possible?

Your options are:

1. Call an SSAS stored procedure from the generated MDX statement, e.g.

SELECT NON EMPTY { [Measures].[Profit] } ON COLUMNS, NON EMPTY { ([Product].[Product].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(MyAssemblyName.MyStoredProcedureName(@.ProductProduct), CONSTRAINED) ) ON COLUMNS FROM [RPM]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

2. If you want to use an SSRS code-behind function, you need to use an expression-based query, e.g.:

="SELECT NON EMPTY { [Measures].[Profit] } ON COLUMNS, NON EMPTY { ([Product].[Product].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(" & Code.SomeProcedure(Parameters!Product.Value & "), CONSTRAINED) ) ON COLUMNS FROM [RPM]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"

Parameter isn't refreshing

Hi.
I have a datasource that depend on parameter A.
Parameter A get his values from query and have a defualt value.
Parameter B get his default value from query that depend on parameter
A.
Now, whan I run the report, parameter A get a value and then parameter
B get his value and the
datasource run OK.
But the problem is that when I'm changing the A value (from the value
list) - The datasource run fine but Parameter B stay with the old value
with out any change.
Does any one know how to solve it.
I'll be happy to give more explanation if it isn't clear enough.
Thanks.Problem solved.
I set the parameter to "Internal".
thanks.
nicknack =D7=9B=D7=AA=D7=91:
> Hi.
> I have a datasource that depend on parameter A.
> Parameter A get his values from query and have a defualt value.
> Parameter B get his default value from query that depend on parameter
> A.
> Now, whan I run the report, parameter A get a value and then parameter
> B get his value and the
> datasource run OK.
> But the problem is that when I'm changing the A value (from the value
> list) - The datasource run fine but Parameter B stay with the old value
> with out any change.
> Does any one know how to solve it.
> I'll be happy to give more explanation if it isn't clear enough.
> Thanks.

Parameter isn't refreshing

Hi.
I have a datasource that depend on parameter A.
Parameter A get his values from query and have a defualt value.
Parameter B get his default value from query that depend on parameter A.

Now, whan I run the report, parameter A get a value and then parameter B get his value and the
datasource run OK.

But the problem is that when I'm changing the A value (from the value list) - The datasource run fine but Parameter B stay with the old value with out any change.

Does any one know how to solve it.
I'll be happy to give more explanation if it isn't clear enough.

Thanks.

I think we need more clarification.

How do the parameters get passed to the report? I don't understand why you seem to think that Parameter B should be updated when you change Parameter A.

Parameter B may seem to depend on Parameter A logically speaking, but as far as I know you can't set up parameters to be dependent on one another.

|||

Actually,

Parameter can be dependent on another, they are considered cascading parameters.

On your problem Roy, I was thinking that your report maybe caching and not refreshing for that reason. You can start a profiler on your SQL instance and you should see Parameter B SQL fire after selecting Parameter "A" to verify the cascading is actually working.

I hope this helps.

Ham

|||I stand corrected. Thanks Ham.|||Hi, thanks for your reply.

I found a sulotion in another forum.
I just needed to set the parameter as "Internal" and then he is refreshing according to the first parameter.

Thanks to both of you for answring.

Roy.

Monday, March 26, 2012

Parameter dropdown width

How do I adjust the reporting parameter dropdown width? the dropdown is
populated from a dataset and the width of the values exceed the width of the
drop down
thanks
--
Message posted via http://www.sqlmonster.comI am also looking for the similar solution. Please respond ,if it is possible
to adjust the drop down parameter list width. Would it be possible after SP2.
I hope somebody from Reporting Services team would help us here.
Regards
Vijay
"Angie via SQLMonster.com" wrote:
> How do I adjust the reporting parameter dropdown width? the dropdown is
> populated from a dataset and the width of the values exceed the width of the
> drop down
> thanks
>
> --
> Message posted via http://www.sqlmonster.com
>|||There is no way to do this that I know of. That sort of fine tuning of the
Report Manager UI is not possible. I don't know about RS 2005 but I for RS
2000 SP2 this is not possible. You would need to create your own web page
and then integrate via either URL or webservices.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Vijay Tripathi" <VijayTripathi@.discussions.microsoft.com> wrote in message
news:49AAE0A4-A32E-41CC-A821-F81BBEDDD501@.microsoft.com...
>I am also looking for the similar solution. Please respond ,if it is
>possible
> to adjust the drop down parameter list width. Would it be possible after
> SP2.
> I hope somebody from Reporting Services team would help us here.
> Regards
> Vijay
> "Angie via SQLMonster.com" wrote:
>> How do I adjust the reporting parameter dropdown width? the dropdown is
>> populated from a dataset and the width of the values exceed the width of
>> the
>> drop down
>> thanks
>>
>> --
>> Message posted via http://www.sqlmonster.com

Parameter Display in Report: Display "Select All" rather than all selections

Dear Anyone,

We have reports in RS that includes a display of selected parameter values in the page footer of the report. When "Select All" is selected in the parameter, all parameter values are displayed in our page footer. We would like to rather display the words "Select All" rather than displaying all of the available selections when select all is selected. Is there a way to do this?

Thanks,
Joseph

If you know the number of parameters in the list, you could compare that number with the count of the parameter collection.
Example:

Set up a text box, and in the expression put:

=iif(Parameters!yourParameter.Count = yourCount, "Select All", Join(Parameters!yourParameter,", "))

That is what I have done. There could be a simpler way, I just haven't stumbled upon it yet! Wink

BobP

|||If only theres a way to determine how many rows or records are returned by the dataset in the parameter.

Parameter defaults not working

Hi,

i have some parameter dropdowns in a report, and i have default values for those parameters, but when viewing the report through IE (just as a customer would), the defaults for the dropdowns are not working - in multivalue dropdowns nothing is selected, and in single select dropdowns the value "<Select Value>" is showing.

The ParameterValue entries of the parameters consist of MDX style values, so they would look like this:

ParameterCaption ParameterValue

-- --

1 January 2006 [Sales Date].[Date Description].&[2123]

and the rdl looks like this (the first one is single select, the second one multi):

<ReportParameter Name="FromSalesDateDescription">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>[Sales Date].[Date Description].&[2332]</Value>
</Values>
</DefaultValue>
<Prompt>From Date</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>FromSalesDateDescription</DataSetName>
<ValueField>ParameterValue</ValueField>
<LabelField>ParameterCaption</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>

<ReportParameter Name="Saleserson">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>[Saleserson].[Saleserson].[All]</Value>
</Values>
</DefaultValue>
<Prompt>Saleserson</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>SalesersonDataset</DataSetName>
<ValueField>ParameterValue</ValueField>
<LabelField>ParameterCaption</LabelField>
</DataSetReference>
</ValidValues>
<MultiValue>true</MultiValue>
</ReportParameter>

I will mention now that the MDX values used in the defaults are correct. Can anyone give me an idea about this?

Thanks,

sluggy

I have a similar problem. Did anyone ever get a hint on this?

TIA!

|||

Don't know if I understand you correctly but I have something like this:

MyDateDataSet:

select GetDate() ID, 'Today' Description
union
select GetDate()-1, 'Yesterday'
union
select GetDate()-30, '30 days ago'
order by ID desc

I added parameter to my report: MyDateParam

Available values:

*from query

*Data set: MyDateDataSet

*Value field: ID

*Label field: Description

Default value:

*from query

*value field: ID

In RDL it looks like this:

<DataType>String</DataType>

<DataSetName>MyDateDataSet</DataSetName>

</DataSetReference>

</DefaultValue>

<Prompt>MyDateParam</Prompt>

<DataSetName>MyDateDataSet</DataSetName>

<LabelField>Description</LabelField>

</DataSetReference>

</ValidValues>

</ReportParameter>

Either in designer and www it works fine. Default value is the first item in the query (“Today”).

Maciej

sql

Parameter defaults not working

Hi,

The ParameterValue entries of the parameters consist of MDX style values, so they would look like this:

ParameterCaption ParameterValue

-- --

1 January 2006 [Sales Date].[Date Description].&[2123]

and the rdl looks like this (the first one is single select, the second one multi):

I will mention now that the MDX values used in the defaults are correct. Can anyone give me an idea about this?

Thanks,

sluggy

I have a similar problem. Did anyone ever get a hint on this?

TIA!

|||

Don't know if I understand you correctly but I have something like this:

MyDateDataSet:

select GetDate() ID, 'Today' Description
union
select GetDate()-1, 'Yesterday'
union
select GetDate()-30, '30 days ago'
order by ID desc

I added parameter to my report: MyDateParam

Available values:

*from query

*Data set: MyDateDataSet

*Value field: ID

*Label field: Description

Default value:

*from query

*value field: ID

In RDL it looks like this:

<DataType>String</DataType>

<DataSetName>MyDateDataSet</DataSetName>

</DataSetReference>

</DefaultValue>

<Prompt>MyDateParam</Prompt>

<DataSetName>MyDateDataSet</DataSetName>

<LabelField>Description</LabelField>

</DataSetReference>

</ValidValues>

</ReportParameter>

Either in designer and www it works fine. Default value is the first item in the query (“Today”).

Maciej

parameter collection

greetings -
I am trying to set up a custom parameters page in an ASP .NET application -
I am in the process of choosing between keeping the values (information I
need to list the parameters for the reports, as well as the available, valid
and default values for any given report) in a database or access all I need
through reporting services (web service).
Does anyone have any advice for setting up a Web App to run rs reports - can
you get all you need from the rs web services to create a custom parameter
page, or is it better to retrieve the values you need from a database?
I can dynamically build the custom parm page using values from a database
(dynamically list the parms for the report, fire off the sp to return valid
values for the parm and set the default value) - I would like to get this
going using the web services but am stuck at the point of trying to fill the
valid parm values - I do have the name of the stored procedure to run to
retrieve the valid values for the parm, but can't seem to figure out how to
either a)get the name of the sp to run, or b)just do a databind some how to
my dynamic control (what ever that may be - a textbox, a list box, a drop
down list, etc.)
Any ideas - ?
ThanksI have done exactly this, but the code is at work and it is a (very) long
weekend.
You can certainly use the web services to retrieve everything - the trick to
getting the values you have set up from datasets that populate the possible
values etc is to that when you run the GetReportParameters method, make sure
you set the ForRendering option to true - then everything you need comes
back.
Have a go and I'll post my code on Tuesday
--
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"Myles" <Myles@.discussions.microsoft.com> wrote in message
news:44942D91-7D8D-4514-91BE-E0FD9E9750FA@.microsoft.com...
> greetings -
> I am trying to set up a custom parameters page in an ASP .NET
> application -
> I am in the process of choosing between keeping the values (information I
> need to list the parameters for the reports, as well as the available,
> valid
> and default values for any given report) in a database or access all I
> need
> through reporting services (web service).
> Does anyone have any advice for setting up a Web App to run rs reports -
> can
> you get all you need from the rs web services to create a custom parameter
> page, or is it better to retrieve the values you need from a database?
> I can dynamically build the custom parm page using values from a database
> (dynamically list the parms for the report, fire off the sp to return
> valid
> values for the parm and set the default value) - I would like to get this
> going using the web services but am stuck at the point of trying to fill
> the
> valid parm values - I do have the name of the stored procedure to run to
> retrieve the valid values for the parm, but can't seem to figure out how
> to
> either a)get the name of the sp to run, or b)just do a databind some how
> to
> my dynamic control (what ever that may be - a textbox, a list box, a drop
> down list, etc.)
> Any ideas - ?
>
> Thanks
>
>|||Thank you for the reply Mary - I may need a little help yet,
I went back and read my question and I was so involved I think I mis-stated
my question! Do the web services expose the names of the stored procedures
used to fill the valid values for the parameters - from your response it
sounds as if you just use the GetReportParameters method and that returns
everything (all of the datasets for all of the parameters) - if that's the
case how do you separate or sort through it all...HELP!
It was a very long weekend - and I still didn't get anything done!
Thanks,
"Mary Bray [MVP]" wrote:
> I have done exactly this, but the code is at work and it is a (very) long
> weekend.
> You can certainly use the web services to retrieve everything - the trick to
> getting the values you have set up from datasets that populate the possible
> values etc is to that when you run the GetReportParameters method, make sure
> you set the ForRendering option to true - then everything you need comes
> back.
> Have a go and I'll post my code on Tuesday
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "Myles" <Myles@.discussions.microsoft.com> wrote in message
> news:44942D91-7D8D-4514-91BE-E0FD9E9750FA@.microsoft.com...
> > greetings -
> >
> > I am trying to set up a custom parameters page in an ASP .NET
> > application -
> > I am in the process of choosing between keeping the values (information I
> > need to list the parameters for the reports, as well as the available,
> > valid
> > and default values for any given report) in a database or access all I
> > need
> > through reporting services (web service).
> >
> > Does anyone have any advice for setting up a Web App to run rs reports -
> > can
> > you get all you need from the rs web services to create a custom parameter
> > page, or is it better to retrieve the values you need from a database?
> >
> > I can dynamically build the custom parm page using values from a database
> > (dynamically list the parms for the report, fire off the sp to return
> > valid
> > values for the parm and set the default value) - I would like to get this
> > going using the web services but am stuck at the point of trying to fill
> > the
> > valid parm values - I do have the name of the stored procedure to run to
> > retrieve the valid values for the parm, but can't seem to figure out how
> > to
> > either a)get the name of the sp to run, or b)just do a databind some how
> > to
> > my dynamic control (what ever that may be - a textbox, a list box, a drop
> > down list, etc.)
> >
> > Any ideas - ?
> >
> >
> > Thanks
> >
> >
> >
> >
>
>|||OK - now I'm at work so here are some code snippets that may help (in C#):
They are used to build drop down lists in a web page with the parameter
values. This way RS takes care of the security for the parameter data. To
find out the names of the stored procs you need to get the DataSetDefinition
object and query it. I haven't yet worked out how to get it back though...
sorry. I think i tried this first and gave up, so used the parameters
collection to build the lookup data.
ReportingService rs=new ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
bool forRendering = true;
string historyID = null;
ParameterValue[] values = null;
DataSourceCredentials[] credentials = null;
ReportParameter[] parameters = null;
parameters = rs.GetReportParameters(ReportPath, historyID, forRendering,
values, credentials);
if (parameters != null)
{
foreach (ReportParameter rp in parameters)
if((rp.ValidValues!=null)||(rp.ValidValuesQueryBased))
{
DropDownList lst=new DropDownList();
foreach(ValidValue vv in rp.ValidValues)
{
ListItem item=new ListItem();
item.Text=vv.Label;
item.Value=vv.Value;
lst.Items.Add(item);
}
"Myles" wrote:
> Thank you for the reply Mary - I may need a little help yet,
> I went back and read my question and I was so involved I think I mis-stated
> my question! Do the web services expose the names of the stored procedures
> used to fill the valid values for the parameters - from your response it
> sounds as if you just use the GetReportParameters method and that returns
> everything (all of the datasets for all of the parameters) - if that's the
> case how do you separate or sort through it all...HELP!
> It was a very long weekend - and I still didn't get anything done!
>
> Thanks,
>
> "Mary Bray [MVP]" wrote:
> > I have done exactly this, but the code is at work and it is a (very) long
> > weekend.
> > You can certainly use the web services to retrieve everything - the trick to
> > getting the values you have set up from datasets that populate the possible
> > values etc is to that when you run the GetReportParameters method, make sure
> > you set the ForRendering option to true - then everything you need comes
> > back.
> >
> > Have a go and I'll post my code on Tuesday
> > --
> >
> > Mary Bray [SQL Server MVP]
> > Please reply only to newsgroups
> >
> > "Myles" <Myles@.discussions.microsoft.com> wrote in message
> > news:44942D91-7D8D-4514-91BE-E0FD9E9750FA@.microsoft.com...
> > > greetings -
> > >
> > > I am trying to set up a custom parameters page in an ASP .NET
> > > application -
> > > I am in the process of choosing between keeping the values (information I
> > > need to list the parameters for the reports, as well as the available,
> > > valid
> > > and default values for any given report) in a database or access all I
> > > need
> > > through reporting services (web service).
> > >
> > > Does anyone have any advice for setting up a Web App to run rs reports -
> > > can
> > > you get all you need from the rs web services to create a custom parameter
> > > page, or is it better to retrieve the values you need from a database?
> > >
> > > I can dynamically build the custom parm page using values from a database
> > > (dynamically list the parms for the report, fire off the sp to return
> > > valid
> > > values for the parm and set the default value) - I would like to get this
> > > going using the web services but am stuck at the point of trying to fill
> > > the
> > > valid parm values - I do have the name of the stored procedure to run to
> > > retrieve the valid values for the parm, but can't seem to figure out how
> > > to
> > > either a)get the name of the sp to run, or b)just do a databind some how
> > > to
> > > my dynamic control (what ever that may be - a textbox, a list box, a drop
> > > down list, etc.)
> > >
> > > Any ideas - ?
> > >
> > >
> > > Thanks
> > >
> > >
> > >
> > >
> >
> >
> >|||beautiful - thank you very much Mary - this will be a tremendous help. If I
figure out how to actually get the sp names I will post back ~
Hope you had a great weekend!
Thanks,
Pete
"Mary Bray [SQL Server MVP]" wrote:
> OK - now I'm at work so here are some code snippets that may help (in C#):
> They are used to build drop down lists in a web page with the parameter
> values. This way RS takes care of the security for the parameter data. To
> find out the names of the stored procs you need to get the DataSetDefinition
> object and query it. I haven't yet worked out how to get it back though...
> sorry. I think i tried this first and gave up, so used the parameters
> collection to build the lookup data.
> ReportingService rs=new ReportingService();
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
> bool forRendering = true;
> string historyID = null;
> ParameterValue[] values = null;
> DataSourceCredentials[] credentials = null;
> ReportParameter[] parameters = null;
> parameters = rs.GetReportParameters(ReportPath, historyID, forRendering,
> values, credentials);
> if (parameters != null)
> {
> foreach (ReportParameter rp in parameters)
> if((rp.ValidValues!=null)||(rp.ValidValuesQueryBased))
> {
> DropDownList lst=new DropDownList();
> foreach(ValidValue vv in rp.ValidValues)
> {
> ListItem item=new ListItem();
> item.Text=vv.Label;
> item.Value=vv.Value;
> lst.Items.Add(item);
> }
>
>
> "Myles" wrote:
> > Thank you for the reply Mary - I may need a little help yet,
> >
> > I went back and read my question and I was so involved I think I mis-stated
> > my question! Do the web services expose the names of the stored procedures
> > used to fill the valid values for the parameters - from your response it
> > sounds as if you just use the GetReportParameters method and that returns
> > everything (all of the datasets for all of the parameters) - if that's the
> > case how do you separate or sort through it all...HELP!
> >
> > It was a very long weekend - and I still didn't get anything done!
> >
> >
> > Thanks,
> >
> >
> >
> > "Mary Bray [MVP]" wrote:
> >
> > > I have done exactly this, but the code is at work and it is a (very) long
> > > weekend.
> > > You can certainly use the web services to retrieve everything - the trick to
> > > getting the values you have set up from datasets that populate the possible
> > > values etc is to that when you run the GetReportParameters method, make sure
> > > you set the ForRendering option to true - then everything you need comes
> > > back.
> > >
> > > Have a go and I'll post my code on Tuesday
> > > --
> > >
> > > Mary Bray [SQL Server MVP]
> > > Please reply only to newsgroups
> > >
> > > "Myles" <Myles@.discussions.microsoft.com> wrote in message
> > > news:44942D91-7D8D-4514-91BE-E0FD9E9750FA@.microsoft.com...
> > > > greetings -
> > > >
> > > > I am trying to set up a custom parameters page in an ASP .NET
> > > > application -
> > > > I am in the process of choosing between keeping the values (information I
> > > > need to list the parameters for the reports, as well as the available,
> > > > valid
> > > > and default values for any given report) in a database or access all I
> > > > need
> > > > through reporting services (web service).
> > > >
> > > > Does anyone have any advice for setting up a Web App to run rs reports -
> > > > can
> > > > you get all you need from the rs web services to create a custom parameter
> > > > page, or is it better to retrieve the values you need from a database?
> > > >
> > > > I can dynamically build the custom parm page using values from a database
> > > > (dynamically list the parms for the report, fire off the sp to return
> > > > valid
> > > > values for the parm and set the default value) - I would like to get this
> > > > going using the web services but am stuck at the point of trying to fill
> > > > the
> > > > valid parm values - I do have the name of the stored procedure to run to
> > > > retrieve the valid values for the parm, but can't seem to figure out how
> > > > to
> > > > either a)get the name of the sp to run, or b)just do a databind some how
> > > > to
> > > > my dynamic control (what ever that may be - a textbox, a list box, a drop
> > > > down list, etc.)
> > > >
> > > > Any ideas - ?
> > > >
> > > >
> > > > Thanks
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >

Friday, March 23, 2012

Parameter Available Values

If I have a paramter that has query based available values, how do I get it to use a connection string specified in another paramter to do the query?

The scenario is that I have one set of reports that pull from a number of databases that have identical schemas and each serve a different customer. i need to be able to somehow populate the valid values with the right data from the appropriate database at runtime.

Nevermind, I found it.

I just had to populate an array of ParameterValues with just the connection string and then do a call to GetReportParameters, that made sure that the other parameters had the data that they needed.

Parameter All Values and Values

I am new at this parameter thing. I have a paramater that uses a drop down
list and can select multiple values. How can I let the user choose all of
the values without having to click on all the values individually. And also
How can I get the parameter to import the values into the dropdown list
automatically instead of me having to type them all in?
ThanksThe easiest way I have found to solve your problem is to create a new dataset
to select the values for the parameter. If you create a union for the value
(ALL), and order by the values, (ALL) will be at the top of the list - for
example (SELECT DISTINCT(CUSTNMBR)
, RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
LEN(CUSTNMBR))) AS CUSTNAME
FROM RM00101 (NOLOCK)
UNION
SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
ORDER BY CUSTNAME ASC) . In the report parameters, select available values
from query, and chose your new dataset and the appropriate label and value.
If you want, you can set the nonqueried default to (ALL). In the main
dataset, use a where clause to set the field value equal to the parameter, OR
the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
OR
@.CUSTOMER IS NULL).
I hope this helps.
"Benw" wrote:
> I am new at this parameter thing. I have a paramater that uses a drop down
> list and can select multiple values. How can I let the user choose all of
> the values without having to click on all the values individually. And also
> How can I get the parameter to import the values into the dropdown list
> automatically instead of me having to type them all in?
> Thanks|||Yes that helped ALOT!!! Here is my query for the parameter. I still need to
add the ALL function.
SELECT Empid
FROM smServFault
WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
GROUP BY Empid
ORDER BY Empid
"Joe" wrote:
> The easiest way I have found to solve your problem is to create a new dataset
> to select the values for the parameter. If you create a union for the value
> (ALL), and order by the values, (ALL) will be at the top of the list - for
> example (SELECT DISTINCT(CUSTNMBR)
> , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> LEN(CUSTNMBR))) AS CUSTNAME
> FROM RM00101 (NOLOCK)
> UNION
> SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> from query, and chose your new dataset and the appropriate label and value.
> If you want, you can set the nonqueried default to (ALL). In the main
> dataset, use a where clause to set the field value equal to the parameter, OR
> the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> OR
> @.CUSTOMER IS NULL).
> I hope this helps.
> "Benw" wrote:
> > I am new at this parameter thing. I have a paramater that uses a drop down
> > list and can select multiple values. How can I let the user choose all of
> > the values without having to click on all the values individually. And also
> > How can I get the parameter to import the values into the dropdown list
> > automatically instead of me having to type them all in?
> >
> > Thanks|||Try this. I don't think you need the "GROUP BY", and I normally use
DISTINCT, but I don't know your situation.
SELECT Empid AS EMPLOYEE
FROM smServFault
WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
UNION
SELECT '(ALL)' AS EMPLOYEE
GROUP BY EMPLOYEE
ORDER BY EMPLOYEE
"Benw" wrote:
> Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> add the ALL function.
> SELECT Empid
> FROM smServFault
> WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> GROUP BY Empid
> ORDER BY Empid
>
> "Joe" wrote:
> > The easiest way I have found to solve your problem is to create a new dataset
> > to select the values for the parameter. If you create a union for the value
> > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > example (SELECT DISTINCT(CUSTNMBR)
> > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > LEN(CUSTNMBR))) AS CUSTNAME
> > FROM RM00101 (NOLOCK)
> > UNION
> > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > from query, and chose your new dataset and the appropriate label and value.
> > If you want, you can set the nonqueried default to (ALL). In the main
> > dataset, use a where clause to set the field value equal to the parameter, OR
> > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > OR
> > @.CUSTOMER IS NULL).
> >
> > I hope this helps.
> > "Benw" wrote:
> >
> > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > list and can select multiple values. How can I let the user choose all of
> > > the values without having to click on all the values individually. And also
> > > How can I get the parameter to import the values into the dropdown list
> > > automatically instead of me having to type them all in?
> > >
> > > Thanks|||That worked awesome. Thank you so much for the tips. I have one more
question and I will leave you alone. LOL. I want the user to be able to
choose a blank parameter from the EMPLOYEE. It works fine in the report
writer but on the report server it tells me to enter a value. What should I
do?
"Joe" wrote:
> Try this. I don't think you need the "GROUP BY", and I normally use
> DISTINCT, but I don't know your situation.
> SELECT Empid AS EMPLOYEE
> FROM smServFault
> WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> UNION
> SELECT '(ALL)' AS EMPLOYEE
> GROUP BY EMPLOYEE
> ORDER BY EMPLOYEE
>
>
> "Benw" wrote:
> > Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> > add the ALL function.
> >
> > SELECT Empid
> > FROM smServFault
> > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > GROUP BY Empid
> > ORDER BY Empid
> >
> >
> > "Joe" wrote:
> >
> > > The easiest way I have found to solve your problem is to create a new dataset
> > > to select the values for the parameter. If you create a union for the value
> > > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > > example (SELECT DISTINCT(CUSTNMBR)
> > > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > > LEN(CUSTNMBR))) AS CUSTNAME
> > > FROM RM00101 (NOLOCK)
> > > UNION
> > > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > > from query, and chose your new dataset and the appropriate label and value.
> > > If you want, you can set the nonqueried default to (ALL). In the main
> > > dataset, use a where clause to set the field value equal to the parameter, OR
> > > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > > OR
> > > @.CUSTOMER IS NULL).
> > >
> > > I hope this helps.
> > > "Benw" wrote:
> > >
> > > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > > list and can select multiple values. How can I let the user choose all of
> > > > the values without having to click on all the values individually. And also
> > > > How can I get the parameter to import the values into the dropdown list
> > > > automatically instead of me having to type them all in?
> > > >
> > > > Thanks|||I'm afraid I'll have to defer that one to someone else. What results would
you expect to get?
"Benw" wrote:
> That worked awesome. Thank you so much for the tips. I have one more
> question and I will leave you alone. LOL. I want the user to be able to
> choose a blank parameter from the EMPLOYEE. It works fine in the report
> writer but on the report server it tells me to enter a value. What should I
> do?
> "Joe" wrote:
> > Try this. I don't think you need the "GROUP BY", and I normally use
> > DISTINCT, but I don't know your situation.
> >
> > SELECT Empid AS EMPLOYEE
> > FROM smServFault
> > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > UNION
> > SELECT '(ALL)' AS EMPLOYEE
> > GROUP BY EMPLOYEE
> > ORDER BY EMPLOYEE
> >
> >
> >
> >
> > "Benw" wrote:
> >
> > > Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> > > add the ALL function.
> > >
> > > SELECT Empid
> > > FROM smServFault
> > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > GROUP BY Empid
> > > ORDER BY Empid
> > >
> > >
> > > "Joe" wrote:
> > >
> > > > The easiest way I have found to solve your problem is to create a new dataset
> > > > to select the values for the parameter. If you create a union for the value
> > > > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > > > example (SELECT DISTINCT(CUSTNMBR)
> > > > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > > > LEN(CUSTNMBR))) AS CUSTNAME
> > > > FROM RM00101 (NOLOCK)
> > > > UNION
> > > > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > > > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > > > from query, and chose your new dataset and the appropriate label and value.
> > > > If you want, you can set the nonqueried default to (ALL). In the main
> > > > dataset, use a where clause to set the field value equal to the parameter, OR
> > > > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > > > OR
> > > > @.CUSTOMER IS NULL).
> > > >
> > > > I hope this helps.
> > > > "Benw" wrote:
> > > >
> > > > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > > > list and can select multiple values. How can I let the user choose all of
> > > > > the values without having to click on all the values individually. And also
> > > > > How can I get the parameter to import the values into the dropdown list
> > > > > automatically instead of me having to type them all in?
> > > > >
> > > > > Thanks|||I want to ba able to choose a blank value so I can get all rows with EMPLOYEE
that is blank. I want to choose blank and get a rows that the EMPLOYYEE is
blank. Like I said its weird, it works in in the report writer but not on
the report server.
"Joe" wrote:
> I'm afraid I'll have to defer that one to someone else. What results would
> you expect to get?
> "Benw" wrote:
> > That worked awesome. Thank you so much for the tips. I have one more
> > question and I will leave you alone. LOL. I want the user to be able to
> > choose a blank parameter from the EMPLOYEE. It works fine in the report
> > writer but on the report server it tells me to enter a value. What should I
> > do?
> >
> > "Joe" wrote:
> >
> > > Try this. I don't think you need the "GROUP BY", and I normally use
> > > DISTINCT, but I don't know your situation.
> > >
> > > SELECT Empid AS EMPLOYEE
> > > FROM smServFault
> > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > UNION
> > > SELECT '(ALL)' AS EMPLOYEE
> > > GROUP BY EMPLOYEE
> > > ORDER BY EMPLOYEE
> > >
> > >
> > >
> > >
> > > "Benw" wrote:
> > >
> > > > Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> > > > add the ALL function.
> > > >
> > > > SELECT Empid
> > > > FROM smServFault
> > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > GROUP BY Empid
> > > > ORDER BY Empid
> > > >
> > > >
> > > > "Joe" wrote:
> > > >
> > > > > The easiest way I have found to solve your problem is to create a new dataset
> > > > > to select the values for the parameter. If you create a union for the value
> > > > > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > > > > example (SELECT DISTINCT(CUSTNMBR)
> > > > > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > > > > LEN(CUSTNMBR))) AS CUSTNAME
> > > > > FROM RM00101 (NOLOCK)
> > > > > UNION
> > > > > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > > > > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > > > > from query, and chose your new dataset and the appropriate label and value.
> > > > > If you want, you can set the nonqueried default to (ALL). In the main
> > > > > dataset, use a where clause to set the field value equal to the parameter, OR
> > > > > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > > > > OR
> > > > > @.CUSTOMER IS NULL).
> > > > >
> > > > > I hope this helps.
> > > > > "Benw" wrote:
> > > > >
> > > > > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > > > > list and can select multiple values. How can I let the user choose all of
> > > > > > the values without having to click on all the values individually. And also
> > > > > > How can I get the parameter to import the values into the dropdown list
> > > > > > automatically instead of me having to type them all in?
> > > > > >
> > > > > > Thanks|||I just noticed something with the "ALL". It wont pull anything. I have to
select the individual Employee for it to pull anything. The ALL wont work.
Thanks, you have been a huge help.
"Joe" wrote:
> I'm afraid I'll have to defer that one to someone else. What results would
> you expect to get?
> "Benw" wrote:
> > That worked awesome. Thank you so much for the tips. I have one more
> > question and I will leave you alone. LOL. I want the user to be able to
> > choose a blank parameter from the EMPLOYEE. It works fine in the report
> > writer but on the report server it tells me to enter a value. What should I
> > do?
> >
> > "Joe" wrote:
> >
> > > Try this. I don't think you need the "GROUP BY", and I normally use
> > > DISTINCT, but I don't know your situation.
> > >
> > > SELECT Empid AS EMPLOYEE
> > > FROM smServFault
> > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > UNION
> > > SELECT '(ALL)' AS EMPLOYEE
> > > GROUP BY EMPLOYEE
> > > ORDER BY EMPLOYEE
> > >
> > >
> > >
> > >
> > > "Benw" wrote:
> > >
> > > > Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> > > > add the ALL function.
> > > >
> > > > SELECT Empid
> > > > FROM smServFault
> > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > GROUP BY Empid
> > > > ORDER BY Empid
> > > >
> > > >
> > > > "Joe" wrote:
> > > >
> > > > > The easiest way I have found to solve your problem is to create a new dataset
> > > > > to select the values for the parameter. If you create a union for the value
> > > > > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > > > > example (SELECT DISTINCT(CUSTNMBR)
> > > > > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > > > > LEN(CUSTNMBR))) AS CUSTNAME
> > > > > FROM RM00101 (NOLOCK)
> > > > > UNION
> > > > > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > > > > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > > > > from query, and chose your new dataset and the appropriate label and value.
> > > > > If you want, you can set the nonqueried default to (ALL). In the main
> > > > > dataset, use a where clause to set the field value equal to the parameter, OR
> > > > > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > > > > OR
> > > > > @.CUSTOMER IS NULL).
> > > > >
> > > > > I hope this helps.
> > > > > "Benw" wrote:
> > > > >
> > > > > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > > > > list and can select multiple values. How can I let the user choose all of
> > > > > > the values without having to click on all the values individually. And also
> > > > > > How can I get the parameter to import the values into the dropdown list
> > > > > > automatically instead of me having to type them all in?
> > > > > >
> > > > > > Thanks|||It should. Did you add the @.employee=null to your SQL statement?
"Benw" wrote:
> I just noticed something with the "ALL". It wont pull anything. I have to
> select the individual Employee for it to pull anything. The ALL wont work.
> Thanks, you have been a huge help.
> "Joe" wrote:
> > I'm afraid I'll have to defer that one to someone else. What results would
> > you expect to get?
> >
> > "Benw" wrote:
> >
> > > That worked awesome. Thank you so much for the tips. I have one more
> > > question and I will leave you alone. LOL. I want the user to be able to
> > > choose a blank parameter from the EMPLOYEE. It works fine in the report
> > > writer but on the report server it tells me to enter a value. What should I
> > > do?
> > >
> > > "Joe" wrote:
> > >
> > > > Try this. I don't think you need the "GROUP BY", and I normally use
> > > > DISTINCT, but I don't know your situation.
> > > >
> > > > SELECT Empid AS EMPLOYEE
> > > > FROM smServFault
> > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > UNION
> > > > SELECT '(ALL)' AS EMPLOYEE
> > > > GROUP BY EMPLOYEE
> > > > ORDER BY EMPLOYEE
> > > >
> > > >
> > > >
> > > >
> > > > "Benw" wrote:
> > > >
> > > > > Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> > > > > add the ALL function.
> > > > >
> > > > > SELECT Empid
> > > > > FROM smServFault
> > > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > > GROUP BY Empid
> > > > > ORDER BY Empid
> > > > >
> > > > >
> > > > > "Joe" wrote:
> > > > >
> > > > > > The easiest way I have found to solve your problem is to create a new dataset
> > > > > > to select the values for the parameter. If you create a union for the value
> > > > > > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > > > > > example (SELECT DISTINCT(CUSTNMBR)
> > > > > > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > > > > > LEN(CUSTNMBR))) AS CUSTNAME
> > > > > > FROM RM00101 (NOLOCK)
> > > > > > UNION
> > > > > > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > > > > > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > > > > > from query, and chose your new dataset and the appropriate label and value.
> > > > > > If you want, you can set the nonqueried default to (ALL). In the main
> > > > > > dataset, use a where clause to set the field value equal to the parameter, OR
> > > > > > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > > > > > OR
> > > > > > @.CUSTOMER IS NULL).
> > > > > >
> > > > > > I hope this helps.
> > > > > > "Benw" wrote:
> > > > > >
> > > > > > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > > > > > list and can select multiple values. How can I let the user choose all of
> > > > > > > the values without having to click on all the values individually. And also
> > > > > > > How can I get the parameter to import the values into the dropdown list
> > > > > > > automatically instead of me having to type them all in?
> > > > > > >
> > > > > > > Thanks|||I am so lost now. Here is my EmpId Parameter
SELECT Empid AS EMPLOYEE
FROM smServFault
WHERE (TaskStatus <> 'C')
UNION
SELECT '(ALL)' AS EMPLOYEE
ORDER BY EMPLOYEE
And here is my main query
SELECT smServCall.ShiptoId, smServFault.FaultCodeId, smServFault.Empid,
smServFault.TaskStatus, smServFault.StartDate, smServFault.WarrStart,
smServFault.Notes, smServFault.CauseID,
smServFault.ResolutionID, smServCall.ServiceCallCompleted
FROM smServCall INNER JOIN
smServFault ON smServCall.ServiceCallID =smServFault.ServiceCallId
WHERE (smServFault.TaskStatus <> 'C') AND
(smServCall.ServiceCallCompleted = 0) AND (smServFault.Empid IN (@.vendor))
AND
(smServFault.StartDate = @.date)
ORDER BY smServFault.StartDate
"Joe" wrote:
> It should. Did you add the @.employee=null to your SQL statement?
> "Benw" wrote:
> > I just noticed something with the "ALL". It wont pull anything. I have to
> > select the individual Employee for it to pull anything. The ALL wont work.
> > Thanks, you have been a huge help.
> >
> > "Joe" wrote:
> >
> > > I'm afraid I'll have to defer that one to someone else. What results would
> > > you expect to get?
> > >
> > > "Benw" wrote:
> > >
> > > > That worked awesome. Thank you so much for the tips. I have one more
> > > > question and I will leave you alone. LOL. I want the user to be able to
> > > > choose a blank parameter from the EMPLOYEE. It works fine in the report
> > > > writer but on the report server it tells me to enter a value. What should I
> > > > do?
> > > >
> > > > "Joe" wrote:
> > > >
> > > > > Try this. I don't think you need the "GROUP BY", and I normally use
> > > > > DISTINCT, but I don't know your situation.
> > > > >
> > > > > SELECT Empid AS EMPLOYEE
> > > > > FROM smServFault
> > > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > > UNION
> > > > > SELECT '(ALL)' AS EMPLOYEE
> > > > > GROUP BY EMPLOYEE
> > > > > ORDER BY EMPLOYEE
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Benw" wrote:
> > > > >
> > > > > > Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> > > > > > add the ALL function.
> > > > > >
> > > > > > SELECT Empid
> > > > > > FROM smServFault
> > > > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > > > GROUP BY Empid
> > > > > > ORDER BY Empid
> > > > > >
> > > > > >
> > > > > > "Joe" wrote:
> > > > > >
> > > > > > > The easiest way I have found to solve your problem is to create a new dataset
> > > > > > > to select the values for the parameter. If you create a union for the value
> > > > > > > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > > > > > > example (SELECT DISTINCT(CUSTNMBR)
> > > > > > > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > > > > > > LEN(CUSTNMBR))) AS CUSTNAME
> > > > > > > FROM RM00101 (NOLOCK)
> > > > > > > UNION
> > > > > > > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > > > > > > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > > > > > > from query, and chose your new dataset and the appropriate label and value.
> > > > > > > If you want, you can set the nonqueried default to (ALL). In the main
> > > > > > > dataset, use a where clause to set the field value equal to the parameter, OR
> > > > > > > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > > > > > > OR
> > > > > > > @.CUSTOMER IS NULL).
> > > > > > >
> > > > > > > I hope this helps.
> > > > > > > "Benw" wrote:
> > > > > > >
> > > > > > > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > > > > > > list and can select multiple values. How can I let the user choose all of
> > > > > > > > the values without having to click on all the values individually. And also
> > > > > > > > How can I get the parameter to import the values into the dropdown list
> > > > > > > > automatically instead of me having to type them all in?
> > > > > > > >
> > > > > > > > Thanks|||Try this.
(smServFault.Empid = @.vendor OR @.vendor IS NULL)
"Benw" wrote:
> I am so lost now. Here is my EmpId Parameter
> SELECT Empid AS EMPLOYEE
> FROM smServFault
> WHERE (TaskStatus <> 'C')
> UNION
> SELECT '(ALL)' AS EMPLOYEE
> ORDER BY EMPLOYEE
> And here is my main query
> SELECT smServCall.ShiptoId, smServFault.FaultCodeId, smServFault.Empid,
> smServFault.TaskStatus, smServFault.StartDate, smServFault.WarrStart,
> smServFault.Notes, smServFault.CauseID,
> smServFault.ResolutionID, smServCall.ServiceCallCompleted
> FROM smServCall INNER JOIN
> smServFault ON smServCall.ServiceCallID => smServFault.ServiceCallId
> WHERE (smServFault.TaskStatus <> 'C') AND
> (smServCall.ServiceCallCompleted = 0) AND (smServFault.Empid IN (@.vendor))
> AND
> (smServFault.StartDate = @.date)
> ORDER BY smServFault.StartDate
> "Joe" wrote:
> > It should. Did you add the @.employee=null to your SQL statement?
> >
> > "Benw" wrote:
> >
> > > I just noticed something with the "ALL". It wont pull anything. I have to
> > > select the individual Employee for it to pull anything. The ALL wont work.
> > > Thanks, you have been a huge help.
> > >
> > > "Joe" wrote:
> > >
> > > > I'm afraid I'll have to defer that one to someone else. What results would
> > > > you expect to get?
> > > >
> > > > "Benw" wrote:
> > > >
> > > > > That worked awesome. Thank you so much for the tips. I have one more
> > > > > question and I will leave you alone. LOL. I want the user to be able to
> > > > > choose a blank parameter from the EMPLOYEE. It works fine in the report
> > > > > writer but on the report server it tells me to enter a value. What should I
> > > > > do?
> > > > >
> > > > > "Joe" wrote:
> > > > >
> > > > > > Try this. I don't think you need the "GROUP BY", and I normally use
> > > > > > DISTINCT, but I don't know your situation.
> > > > > >
> > > > > > SELECT Empid AS EMPLOYEE
> > > > > > FROM smServFault
> > > > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > > > UNION
> > > > > > SELECT '(ALL)' AS EMPLOYEE
> > > > > > GROUP BY EMPLOYEE
> > > > > > ORDER BY EMPLOYEE
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Benw" wrote:
> > > > > >
> > > > > > > Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> > > > > > > add the ALL function.
> > > > > > >
> > > > > > > SELECT Empid
> > > > > > > FROM smServFault
> > > > > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > > > > GROUP BY Empid
> > > > > > > ORDER BY Empid
> > > > > > >
> > > > > > >
> > > > > > > "Joe" wrote:
> > > > > > >
> > > > > > > > The easiest way I have found to solve your problem is to create a new dataset
> > > > > > > > to select the values for the parameter. If you create a union for the value
> > > > > > > > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > > > > > > > example (SELECT DISTINCT(CUSTNMBR)
> > > > > > > > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > > > > > > > LEN(CUSTNMBR))) AS CUSTNAME
> > > > > > > > FROM RM00101 (NOLOCK)
> > > > > > > > UNION
> > > > > > > > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > > > > > > > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > > > > > > > from query, and chose your new dataset and the appropriate label and value.
> > > > > > > > If you want, you can set the nonqueried default to (ALL). In the main
> > > > > > > > dataset, use a where clause to set the field value equal to the parameter, OR
> > > > > > > > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > > > > > > > OR
> > > > > > > > @.CUSTOMER IS NULL).
> > > > > > > >
> > > > > > > > I hope this helps.
> > > > > > > > "Benw" wrote:
> > > > > > > >
> > > > > > > > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > > > > > > > list and can select multiple values. How can I let the user choose all of
> > > > > > > > > the values without having to click on all the values individually. And also
> > > > > > > > > How can I get the parameter to import the values into the dropdown list
> > > > > > > > > automatically instead of me having to type them all in?
> > > > > > > > >
> > > > > > > > > Thanks|||is this for the ALL not working. Because it still wont.
"Joe" wrote:
> Try this.
> (smServFault.Empid = @.vendor OR @.vendor IS NULL)
> "Benw" wrote:
> > I am so lost now. Here is my EmpId Parameter
> >
> > SELECT Empid AS EMPLOYEE
> > FROM smServFault
> > WHERE (TaskStatus <> 'C')
> > UNION
> > SELECT '(ALL)' AS EMPLOYEE
> > ORDER BY EMPLOYEE
> >
> > And here is my main query
> >
> > SELECT smServCall.ShiptoId, smServFault.FaultCodeId, smServFault.Empid,
> > smServFault.TaskStatus, smServFault.StartDate, smServFault.WarrStart,
> > smServFault.Notes, smServFault.CauseID,
> > smServFault.ResolutionID, smServCall.ServiceCallCompleted
> > FROM smServCall INNER JOIN
> > smServFault ON smServCall.ServiceCallID => > smServFault.ServiceCallId
> > WHERE (smServFault.TaskStatus <> 'C') AND
> > (smServCall.ServiceCallCompleted = 0) AND (smServFault.Empid IN (@.vendor))
> > AND
> > (smServFault.StartDate = @.date)
> > ORDER BY smServFault.StartDate
> >
> > "Joe" wrote:
> >
> > > It should. Did you add the @.employee=null to your SQL statement?
> > >
> > > "Benw" wrote:
> > >
> > > > I just noticed something with the "ALL". It wont pull anything. I have to
> > > > select the individual Employee for it to pull anything. The ALL wont work.
> > > > Thanks, you have been a huge help.
> > > >
> > > > "Joe" wrote:
> > > >
> > > > > I'm afraid I'll have to defer that one to someone else. What results would
> > > > > you expect to get?
> > > > >
> > > > > "Benw" wrote:
> > > > >
> > > > > > That worked awesome. Thank you so much for the tips. I have one more
> > > > > > question and I will leave you alone. LOL. I want the user to be able to
> > > > > > choose a blank parameter from the EMPLOYEE. It works fine in the report
> > > > > > writer but on the report server it tells me to enter a value. What should I
> > > > > > do?
> > > > > >
> > > > > > "Joe" wrote:
> > > > > >
> > > > > > > Try this. I don't think you need the "GROUP BY", and I normally use
> > > > > > > DISTINCT, but I don't know your situation.
> > > > > > >
> > > > > > > SELECT Empid AS EMPLOYEE
> > > > > > > FROM smServFault
> > > > > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > > > > UNION
> > > > > > > SELECT '(ALL)' AS EMPLOYEE
> > > > > > > GROUP BY EMPLOYEE
> > > > > > > ORDER BY EMPLOYEE
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Benw" wrote:
> > > > > > >
> > > > > > > > Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> > > > > > > > add the ALL function.
> > > > > > > >
> > > > > > > > SELECT Empid
> > > > > > > > FROM smServFault
> > > > > > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > > > > > GROUP BY Empid
> > > > > > > > ORDER BY Empid
> > > > > > > >
> > > > > > > >
> > > > > > > > "Joe" wrote:
> > > > > > > >
> > > > > > > > > The easiest way I have found to solve your problem is to create a new dataset
> > > > > > > > > to select the values for the parameter. If you create a union for the value
> > > > > > > > > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > > > > > > > > example (SELECT DISTINCT(CUSTNMBR)
> > > > > > > > > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > > > > > > > > LEN(CUSTNMBR))) AS CUSTNAME
> > > > > > > > > FROM RM00101 (NOLOCK)
> > > > > > > > > UNION
> > > > > > > > > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > > > > > > > > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > > > > > > > > from query, and chose your new dataset and the appropriate label and value.
> > > > > > > > > If you want, you can set the nonqueried default to (ALL). In the main
> > > > > > > > > dataset, use a where clause to set the field value equal to the parameter, OR
> > > > > > > > > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > > > > > > > > OR
> > > > > > > > > @.CUSTOMER IS NULL).
> > > > > > > > >
> > > > > > > > > I hope this helps.
> > > > > > > > > "Benw" wrote:
> > > > > > > > >
> > > > > > > > > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > > > > > > > > list and can select multiple values. How can I let the user choose all of
> > > > > > > > > > the values without having to click on all the values individually. And also
> > > > > > > > > > How can I get the parameter to import the values into the dropdown list
> > > > > > > > > > automatically instead of me having to type them all in?
> > > > > > > > > >
> > > > > > > > > > Thanks|||maybe I am missing something here but in RS2005 if you define the parameters
as a multi-value parameter <Select All> is an option isnt it?
"Joe" wrote:
> The easiest way I have found to solve your problem is to create a new dataset
> to select the values for the parameter. If you create a union for the value
> (ALL), and order by the values, (ALL) will be at the top of the list - for
> example (SELECT DISTINCT(CUSTNMBR)
> , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> LEN(CUSTNMBR))) AS CUSTNAME
> FROM RM00101 (NOLOCK)
> UNION
> SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> from query, and chose your new dataset and the appropriate label and value.
> If you want, you can set the nonqueried default to (ALL). In the main
> dataset, use a where clause to set the field value equal to the parameter, OR
> the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> OR
> @.CUSTOMER IS NULL).
> I hope this helps.
> "Benw" wrote:
> > I am new at this parameter thing. I have a paramater that uses a drop down
> > list and can select multiple values. How can I let the user choose all of
> > the values without having to click on all the values individually. And also
> > How can I get the parameter to import the values into the dropdown list
> > automatically instead of me having to type them all in?
> >
> > Thanks|||Yes, your are right, but when I put it on the report server for others to
view, the <Select All> function disappears. I actually figured out what I
was doing wrong.
I fixed it by saying
WHERE field = @.vendor or @.vendor = 'All'
"MJT" wrote:
> maybe I am missing something here but in RS2005 if you define the parameters
> as a multi-value parameter <Select All> is an option isnt it?
> "Joe" wrote:
> > The easiest way I have found to solve your problem is to create a new dataset
> > to select the values for the parameter. If you create a union for the value
> > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > example (SELECT DISTINCT(CUSTNMBR)
> > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > LEN(CUSTNMBR))) AS CUSTNAME
> > FROM RM00101 (NOLOCK)
> > UNION
> > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > from query, and chose your new dataset and the appropriate label and value.
> > If you want, you can set the nonqueried default to (ALL). In the main
> > dataset, use a where clause to set the field value equal to the parameter, OR
> > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > OR
> > @.CUSTOMER IS NULL).
> >
> > I hope this helps.
> > "Benw" wrote:
> >
> > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > list and can select multiple values. How can I let the user choose all of
> > > the values without having to click on all the values individually. And also
> > > How can I get the parameter to import the values into the dropdown list
> > > automatically instead of me having to type them all in?
> > >
> > > Thanks

Parameter - Set default values - Some fields missing

Please help. I'm using Crytal Reports v.9. I would like to create a parameter and want to set default values. The table I'm using contains 150 fields, however, the Browse Field drop down only shows 22 fields. Is there a way to fix this so that it'll show all 150 fields?

Thanks a lot to all who respond!

- SCham -What is the database you are using?
Why do you want to set default value to 150 columns?

paramater values inside IN clause

I have a problem in a stored proc where I pass more than one value to
the parameters inside a IN clause: it doesn=B4t retrieve any data. But
if there's only one value I get results.
here's the code:
CREATE PROCEDURE spGetInvoiceData
@.strClient varchar(10),
@.strBrand varchar(10),
@.strService varchar(10),
@.dtBeguinDate datetime,
@.dtEndDate datetime,
@.strCamp varchar(10) =3D NULL
AS
select request.orgunit as unit, campaign.name as campaign, service.name
as service, entity.name as destinationEntity, requestitem.request,
request.efectivedate, item.itemcode, item.name as item, brand.name as
brand, requestitem.amount,
item.weigth, facturationtype.name as facturacao,
facturationstep.value, facturationstep.lowerbound,
facturationstep.upperbound, facturationstep.addvalue
into #ttemp
from request, requestitem, item, brand, service, entity,
facturationtype, facturationstep, campaign
where requestitem.request in (select code from request where state=3D1
and (requeststate=3D'expd' or requeststate=3D'done' or
requeststate=3D'closed' or requeststate=3D'atrib')
and client like @.strClient and efectivedate >=3D @.dtBeguinDate and
efectivedate <=3D @.dtEndDate) and requestitem.state=3D1 and
(facturationtype.code =3D request.invoicetype
and request.destinationentity =3D entity.code
and request.service =3D service.code
and request.code=3Drequestitem.request
and item.code =3D requestitem.item
and requestitem.brand =3D brand.code
and facturationtype.code =3D facturationstep.facturationtypecode
and request.campaign=3Dcampaign.code)
and item.state=3D1 and requestitem.state=3D1 and request.state=3D1 and
service.state=3D1 and brand.state=3D1 and campaign.state=3D1
and entity.state=3D1 and facturationtype.state=3D1 and
request.toinvoice=3D1 and facturationstep.state =3D 1
and service.code in (@.strService) and brand.code in (@.strBrand)
and (request.campaign =3D @.strCamp or @.strCamp IS NULL)
order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
select *,
(select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
t1.request =3D t2.request and t1.brand =3D t2.brand) as pesomarca,
(select distinct sum(weigth * amount) from #ttemp t1 where t1.request
=3D t2.request) as pesopedido
from #ttemp t2
drop table #ttemp
GO
Can anyone help?Which parameter are you using to pass in multiple values? The parameters for
the stored procedure don't seem quite 'large' enough to hold many values in
a comma delimited string.
You are passing them in as a comma delimited string?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
I have a problem in a stored proc where I pass more than one value to
the parameters inside a IN clause: it doesnt retrieve any data. But
if there's only one value I get results.
here's the code:
CREATE PROCEDURE spGetInvoiceData
@.strClient varchar(10),
@.strBrand varchar(10),
@.strService varchar(10),
@.dtBeguinDate datetime,
@.dtEndDate datetime,
@.strCamp varchar(10) = NULL
AS
select request.orgunit as unit, campaign.name as campaign, service.name
as service, entity.name as destinationEntity, requestitem.request,
request.efectivedate, item.itemcode, item.name as item, brand.name as
brand, requestitem.amount,
item.weigth, facturationtype.name as facturacao,
facturationstep.value, facturationstep.lowerbound,
facturationstep.upperbound, facturationstep.addvalue
into #ttemp
from request, requestitem, item, brand, service, entity,
facturationtype, facturationstep, campaign
where requestitem.request in (select code from request where state=1
and (requeststate='expd' or requeststate='done' or
requeststate='closed' or requeststate='atrib')
and client like @.strClient and efectivedate >= @.dtBeguinDate and
efectivedate <= @.dtEndDate) and requestitem.state=1 and
(facturationtype.code = request.invoicetype
and request.destinationentity = entity.code
and request.service = service.code
and request.code=requestitem.request
and item.code = requestitem.item
and requestitem.brand = brand.code
and facturationtype.code = facturationstep.facturationtypecode
and request.campaign=campaign.code)
and item.state=1 and requestitem.state=1 and request.state=1 and
service.state=1 and brand.state=1 and campaign.state=1
and entity.state=1 and facturationtype.state=1 and
request.toinvoice=1 and facturationstep.state = 1
and service.code in (@.strService) and brand.code in (@.strBrand)
and (request.campaign = @.strCamp or @.strCamp IS NULL)
order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
select *,
(select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
t1.request = t2.request and t1.brand = t2.brand) as pesomarca,
(select distinct sum(weigth * amount) from #ttemp t1 where t1.request
= t2.request) as pesopedido
from #ttemp t2
drop table #ttemp
GO
Can anyone help?|||For instance if pass these values:
@.strService ('111,'222') which is in the subquery won't get results
but if @.strService ('111') i get results
Arnie Rowland escreveu:
> Which parameter are you using to pass in multiple values? The parameters =
for
> the stored procedure don't seem quite 'large' enough to hold many values =
in
> a comma delimited string.
> You are passing them in as a comma delimited string?
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
> I have a problem in a stored proc where I pass more than one value to
> the parameters inside a IN clause: it doesn=B4t retrieve any data. But
> if there's only one value I get results.
> here's the code:
>
> CREATE PROCEDURE spGetInvoiceData
> @.strClient varchar(10),
> @.strBrand varchar(10),
> @.strService varchar(10),
> @.dtBeguinDate datetime,
> @.dtEndDate datetime,
> @.strCamp varchar(10) =3D NULL
> AS
> select request.orgunit as unit, campaign.name as campaign, service.name
> as service, entity.name as destinationEntity, requestitem.request,
> request.efectivedate, item.itemcode, item.name as item, brand.name as
> brand, requestitem.amount,
> item.weigth, facturationtype.name as facturacao,
> facturationstep.value, facturationstep.lowerbound,
> facturationstep.upperbound, facturationstep.addvalue
> into #ttemp
> from request, requestitem, item, brand, service, entity,
> facturationtype, facturationstep, campaign
> where requestitem.request in (select code from request where state=3D1
> and (requeststate=3D'expd' or requeststate=3D'done' or
> requeststate=3D'closed' or requeststate=3D'atrib')
> and client like @.strClient and efectivedate >=3D @.dtBeguinDate and
> efectivedate <=3D @.dtEndDate) and requestitem.state=3D1 and
> (facturationtype.code =3D request.invoicetype
> and request.destinationentity =3D entity.code
> and request.service =3D service.code
> and request.code=3Drequestitem.request
> and item.code =3D requestitem.item
> and requestitem.brand =3D brand.code
> and facturationtype.code =3D facturationstep.facturationtypecode
> and request.campaign=3Dcampaign.code)
> and item.state=3D1 and requestitem.state=3D1 and request.state=3D1 and
> service.state=3D1 and brand.state=3D1 and campaign.state=3D1
> and entity.state=3D1 and facturationtype.state=3D1 and
> request.toinvoice=3D1 and facturationstep.state =3D 1
> and service.code in (@.strService) and brand.code in (@.strBrand)
> and (request.campaign =3D @.strCamp or @.strCamp IS NULL)
> order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
> select *,
> (select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
> t1.request =3D t2.request and t1.brand =3D t2.brand) as pesomarca,
> (select distinct sum(weigth * amount) from #ttemp t1 where t1.request
> =3D t2.request) as pesopedido
> from #ttemp t2
>=20
> drop table #ttemp
> GO
>=20
>=20
>=20
> Can anyone help?|||For @.strService, the comma delimited string should have single quotes around
each element. Your example [@.strService ('111,'222')] is incorrect.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151081861.177541.90980@.g10g2000cwb.googlegroups.com...
For instance if pass these values:
@.strService ('111,'222') which is in the subquery won't get results
but if @.strService ('111') i get results
Arnie Rowland escreveu:
> Which parameter are you using to pass in multiple values? The parameters
> for
> the stored procedure don't seem quite 'large' enough to hold many values
> in
> a comma delimited string.
> You are passing them in as a comma delimited string?
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
> I have a problem in a stored proc where I pass more than one value to
> the parameters inside a IN clause: it doesnt retrieve any data. But
> if there's only one value I get results.
> here's the code:
>
> CREATE PROCEDURE spGetInvoiceData
> @.strClient varchar(10),
> @.strBrand varchar(10),
> @.strService varchar(10),
> @.dtBeguinDate datetime,
> @.dtEndDate datetime,
> @.strCamp varchar(10) = NULL
> AS
> select request.orgunit as unit, campaign.name as campaign, service.name
> as service, entity.name as destinationEntity, requestitem.request,
> request.efectivedate, item.itemcode, item.name as item, brand.name as
> brand, requestitem.amount,
> item.weigth, facturationtype.name as facturacao,
> facturationstep.value, facturationstep.lowerbound,
> facturationstep.upperbound, facturationstep.addvalue
> into #ttemp
> from request, requestitem, item, brand, service, entity,
> facturationtype, facturationstep, campaign
> where requestitem.request in (select code from request where state=1
> and (requeststate='expd' or requeststate='done' or
> requeststate='closed' or requeststate='atrib')
> and client like @.strClient and efectivedate >= @.dtBeguinDate and
> efectivedate <= @.dtEndDate) and requestitem.state=1 and
> (facturationtype.code = request.invoicetype
> and request.destinationentity = entity.code
> and request.service = service.code
> and request.code=requestitem.request
> and item.code = requestitem.item
> and requestitem.brand = brand.code
> and facturationtype.code = facturationstep.facturationtypecode
> and request.campaign=campaign.code)
> and item.state=1 and requestitem.state=1 and request.state=1 and
> service.state=1 and brand.state=1 and campaign.state=1
> and entity.state=1 and facturationtype.state=1 and
> request.toinvoice=1 and facturationstep.state = 1
> and service.code in (@.strService) and brand.code in (@.strBrand)
> and (request.campaign = @.strCamp or @.strCamp IS NULL)
> order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
> select *,
> (select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
> t1.request = t2.request and t1.brand = t2.brand) as pesomarca,
> (select distinct sum(weigth * amount) from #ttemp t1 where t1.request
> = t2.request) as pesopedido
> from #ttemp t2
> drop table #ttemp
> GO
>
> Can anyone help?|||I ment to write @.strService ('111,222') and not
@.strService ('111,'222').
And I don't think the problem is the single quotes around each element
because my query string in VB.net is:
strQuery =3D "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
"', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
& endDate & "'"
Arnie Rowland escreveu:
> For @.strService, the comma delimited string should have single quotes aro=
und
> each element. Your example [@.strService ('111,'222')] is incorrect.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151081861.177541.90980@.g10g2000cwb.googlegroups.com...
> For instance if pass these values:
> @.strService ('111,'222') which is in the subquery won't get results
> but if @.strService ('111') i get results
>
> Arnie Rowland escreveu:|||Table DDL would help in diagnosis.
If service.code is a numeric datatype then, @.strService ('111,222') would be
correct. If service.code is a character datatype then each comma separated
element needs quotes around it.
Please verify the datatype for service.code.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151083291.840423.149320@.r2g2000cwb.googlegroups.com...
I ment to write @.strService ('111,222') and not
@.strService ('111,'222').
And I don't think the problem is the single quotes around each element
because my query string in VB.net is:
strQuery = "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
"', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
& endDate & "'"
Arnie Rowland escreveu:
> For @.strService, the comma delimited string should have single quotes
> around
> each element. Your example [@.strService ('111,'222')] is incorrect.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151081861.177541.90980@.g10g2000cwb.googlegroups.com...
> For instance if pass these values:
> @.strService ('111,'222') which is in the subquery won't get results
> but if @.strService ('111') i get results
>
> Arnie Rowland escreveu:|||Sorry, but I' ve supllied wrong information:
@.strService('dsmt','merch')
and
@.strBrand ('111,222')
Arnie Rowland escreveu:
> Table DDL would help in diagnosis.
> If service.code is a numeric datatype then, @.strService ('111,222') would=
be
> correct. If service.code is a character datatype then each comma separated
> element needs quotes around it.
> Please verify the datatype for service.code.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151083291.840423.149320@.r2g2000cwb.googlegroups.com...
> I ment to write @.strService ('111,222') and not
> @.strService ('111,'222').
> And I don't think the problem is the single quotes around each element
> because my query string in VB.net is:
>
> strQuery =3D "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
> "', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
> & endDate & "'"
>
>
> Arnie Rowland escreveu:
ers
ues
me
=3D1|||IN() list in SQL cannot accept a parameter. Either for scalar values use the
equality operator ( = ) or for multiple values use an appropriate technique
detailed at:
http://www.sommarskog.se/arrays-in-sql.html
Anith|||The way I see it, @.strBrand has a length problem.
It's defined as a varchar(10), and with @.strService('dsmt','merch'), you are
trying to push 14 characters into it.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151085462.464011.201720@.c74g2000cwc.googlegroups.com...
Sorry, but I' ve supllied wrong information:
@.strService('dsmt','merch')
and
@.strBrand ('111,222')
Arnie Rowland escreveu:
> Table DDL would help in diagnosis.
> If service.code is a numeric datatype then, @.strService ('111,222') would
> be
> correct. If service.code is a character datatype then each comma separated
> element needs quotes around it.
> Please verify the datatype for service.code.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Pedro" <p_costa@.sapo.pt> wrote in message
> news:1151083291.840423.149320@.r2g2000cwb.googlegroups.com...
> I ment to write @.strService ('111,222') and not
> @.strService ('111,'222').
> And I don't think the problem is the single quotes around each element
> because my query string in VB.net is:
>
> strQuery = "EXECUTE " & database & ".dbo.spGetInvoiceData '" & client &
> "', '" & brandCode & "', '" & serviceCode & "', '" & startDate & "', '"
> & endDate & "'"
>
>
> Arnie Rowland escreveu:|||Your problem is, quite simply, that you're using IN wrong. This is what
you're telling SQL Server when you pass it a @.strService value of
('dsmt,merch'):
@.strService IN ('dsmt,merch')
What you're trying to achieve is this:
@.strService IN ('dsmt', 'merch')
The way to do this is to break up your comma-delimited string into a temp
table or table variable and join/subquery on it. Here's one method:
http://groups.google.com/group/micr...de=source&hl=en
Here's more methods: http://www.sommarskog.se/arrays-in-sql.html
"Pedro" <p_costa@.sapo.pt> wrote in message
news:1151080483.044837.122720@.y41g2000cwy.googlegroups.com...
I have a problem in a stored proc where I pass more than one value to
the parameters inside a IN clause: it doesnt retrieve any data. But
if there's only one value I get results.
here's the code:
CREATE PROCEDURE spGetInvoiceData
@.strClient varchar(10),
@.strBrand varchar(10),
@.strService varchar(10),
@.dtBeguinDate datetime,
@.dtEndDate datetime,
@.strCamp varchar(10) = NULL
AS
select request.orgunit as unit, campaign.name as campaign, service.name
as service, entity.name as destinationEntity, requestitem.request,
request.efectivedate, item.itemcode, item.name as item, brand.name as
brand, requestitem.amount,
item.weigth, facturationtype.name as facturacao,
facturationstep.value, facturationstep.lowerbound,
facturationstep.upperbound, facturationstep.addvalue
into #ttemp
from request, requestitem, item, brand, service, entity,
facturationtype, facturationstep, campaign
where requestitem.request in (select code from request where state=1
and (requeststate='expd' or requeststate='done' or
requeststate='closed' or requeststate='atrib')
and client like @.strClient and efectivedate >= @.dtBeguinDate and
efectivedate <= @.dtEndDate) and requestitem.state=1 and
(facturationtype.code = request.invoicetype
and request.destinationentity = entity.code
and request.service = service.code
and request.code=requestitem.request
and item.code = requestitem.item
and requestitem.brand = brand.code
and facturationtype.code = facturationstep.facturationtypecode
and request.campaign=campaign.code)
and item.state=1 and requestitem.state=1 and request.state=1 and
service.state=1 and brand.state=1 and campaign.state=1
and entity.state=1 and facturationtype.state=1 and
request.toinvoice=1 and facturationstep.state = 1
and service.code in (@.strService) and brand.code in (@.strBrand)
and (request.campaign = @.strCamp or @.strCamp IS NULL)
order by request.efectivedate, REQUESTITEM.REQUEST,BRAND.NAME
select *,
(select distinct sum(t1.weigth * t1.amount) from #ttemp t1 where
t1.request = t2.request and t1.brand = t2.brand) as pesomarca,
(select distinct sum(weigth * amount) from #ttemp t1 where t1.request
= t2.request) as pesopedido
from #ttemp t2
drop table #ttemp
GO
Can anyone help?sql

Friday, March 30, 2012

Parameter Prompt List & Filter selection

Wednesday, March 28, 2012

Parameter not refreshing default data selection

parameter modification within mdx

Parameter isn't refreshing

Parameter isn't refreshing

Monday, March 26, 2012

Parameter dropdown width

Parameter Display in Report: Display "Select All" rather than all selections

Parameter defaults not working

Parameter defaults not working

parameter collection

Friday, March 23, 2012

Parameter Available Values

Parameter All Values and Values

Parameter - Set default values - Some fields missing

paramater values inside IN clause

Page Numbers in Excel

Blog Archive

About Me