Find startup parameters using QA

I have some startup parameters that i have added using the EM->
Properties->General->Startup Parameters
Is there a way i can run a query and see them using QA instead of using the
GUI . Using SQL 2000Hi Hassan,
Those parameters are stored in the registry, not in SQL Server itself. The
key they are stored is:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters.
Replace the second MSSQLServer with the instance name if you use an
instance.
You can get these values with the undocumented extended procedure
xp_instance_regenumvalues :
EXEC xp_instance_regenumvalues N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters'
--
Jacco Schalkwijk
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OHb1eWmxDHA.1616@.TK2MSFTNGP11.phx.gbl...
> I have some startup parameters that i have added using the EM->
> Properties->General->Startup Parameters
> Is there a way i can run a query and see them using QA instead of using
the
> GUI . Using SQL 2000
>

Filters using an 'OR' condition

I am dealing with a set of 5 parameters. I want to filter the data based on
these parameters using the 'or' condition instead of an 'And'. In the filter
tab the condition defaults to an 'And' and is greyed out. How do I change it
to use an 'Or' condition.You can rewrite filter to use singe expression that combines all parameters
instead of 5 separate expressions.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sumi" <Sumi@.discussions.microsoft.com> wrote in message
news:9BD59230-0A07-4AA3-9E58-4F11640D3D4A@.microsoft.com...
>I am dealing with a set of 5 parameters. I want to filter the data based on
> these parameters using the 'or' condition instead of an 'And'. In the
> filter
> tab the condition defaults to an 'And' and is greyed out. How do I change
> it
> to use an 'Or' condition.|||Combine the in one expression
fe
filter-expresion:
Switch( Parameters!par1.Value = x or Parameters!par2.Value = y, 1, true, 0)
filter-value:
=1
"Sumi" wrote:
> I am dealing with a set of 5 parameters. I want to filter the data based on
> these parameters using the 'or' condition instead of an 'And'. In the filter
> tab the condition defaults to an 'And' and is greyed out. How do I change it
> to use an 'Or' condition.|||Thank you very much! This made things very clear.
"Antoon" wrote:
> Combine the in one expression
> fe
> filter-expresion:
> Switch( Parameters!par1.Value = x or Parameters!par2.Value = y, 1, true, 0)
> filter-value:
> =1
> "Sumi" wrote:
> > I am dealing with a set of 5 parameters. I want to filter the data based on
> > these parameters using the 'or' condition instead of an 'And'. In the filter
> > tab the condition defaults to an 'And' and is greyed out. How do I change it
> > to use an 'Or' condition.|||Antoon,
Thanks a bundle.I tried this and it works wonderfully. But this has
triggered off another question.
I have a stored proc that takes in Startdate and Enddate as two parameters.
Then I have a set of 5 parameters which I am using to further filter the
data, this is to give the user flexibility to choose whichever parameter they
want. These parameters have a drodown box which has all the values they can
select along with a blank row, incase the user does not want to use that
parameter to filter.
Using the expression described below, I can filter by one or more
parameters, but if I select blank for all the 5 parameter, I was expecting
the expression to be ignored and the result set to have all data for the
entered Startdate and EndDate. Which are not a part of this filter
expression. But the report pulls up blank.
Any ideas as to how to fix this?
filter-expresion:
Switch( Parameters!par1.Value = x or Parameters!par2.Value = y, 1, true, 0)
filter-value:
=1
"Antoon" wrote:
> Combine the in one expression
> fe
> filter-expresion:
> Switch( Parameters!par1.Value = x or Parameters!par2.Value = y, 1, true, 0)
> filter-value:
> =1
> "Sumi" wrote:
> > I am dealing with a set of 5 parameters. I want to filter the data based on
> > these parameters using the 'or' condition instead of an 'And'. In the filter
> > tab the condition defaults to an 'And' and is greyed out. How do I change it
> > to use an 'Or' condition.|||I use a specific value for the all parameter
Select parname, parvalue from parlist
union
select '<All>', -1 from dual
You can then filter in the query
Select ...
where
... (table.value = :parameter or :paremeter = -1)
or you can change the filter, and add
Parameters!par1.Value = -1, 1
to the switch statement
(I work in oracle)
"Sumi" wrote:
> Antoon,
> Thanks a bundle.I tried this and it works wonderfully. But this has
> triggered off another question.
> I have a stored proc that takes in Startdate and Enddate as two parameters.
> Then I have a set of 5 parameters which I am using to further filter the
> data, this is to give the user flexibility to choose whichever parameter they
> want. These parameters have a drodown box which has all the values they can
> select along with a blank row, incase the user does not want to use that
> parameter to filter.
> Using the expression described below, I can filter by one or more
> parameters, but if I select blank for all the 5 parameter, I was expecting
> the expression to be ignored and the result set to have all data for the
> entered Startdate and EndDate. Which are not a part of this filter
> expression. But the report pulls up blank.
> Any ideas as to how to fix this?
> filter-expresion:
> Switch( Parameters!par1.Value = x or Parameters!par2.Value = y, 1, true, 0)
> filter-value:
> =1
>
> "Antoon" wrote:
> > Combine the in one expression
> > fe
> > filter-expresion:
> > Switch( Parameters!par1.Value = x or Parameters!par2.Value = y, 1, true, 0)
> > filter-value:
> > =1
> > "Sumi" wrote:
> >
> > > I am dealing with a set of 5 parameters. I want to filter the data based on
> > > these parameters using the 'or' condition instead of an 'And'. In the filter
> > > tab the condition defaults to an 'And' and is greyed out. How do I change it
> > > to use an 'Or' condition.|||The only way I've achieved any 'clever' from parameters regardin
optional parameters ands and ors is by building up a where string o
the fly
DECLARE @.select as varchar(100
DECLARE @.where as varchar(100
DECLARE @.sql as varchar(100
SET @.select = 'SELECT * FROM poext WHERE '
SET @.where = ''
IF @.vendor <> 'not set' SET @.where = @.where + 'vendor = '''
@.vendor + ''''
IF len(@.where) > 0 SET @.where = @.where + ' and '
IF @.buyer <> - 1 SET @.where = @.where + 'buyer = '
cast(@.buyer as varchar(5))
if len(@.where)>0 and right(@.where,5) <> ' an
' SET @.where = @.where + ' and '
set @.where = @.where + 'orddate between '''+@.orderdatefrom + ''' an
''' + @.orderdateto + ''''
SET @.sql = @.select + @.where
EXEC sp_executesql @.sq
And use that as my dataset query. This works fine, although you hav
to refresh it a few times or the preview doesn't work, it tends t
get a little confused as it has no idea about the data you're pullin
until it's got it.[/code

Filters for dataset from Parameter - How to ignore if the parameter is NULL ?

Hi,

I am starting to use reporting services and I created a report that takes 4 parameters for a Filter on the dataset.

The idea is the create snapshot nightly and then display the report filtered based on parameters to users.

I'd like that the filter be ignored for a parameter if the parameter is passed as NULL,

Right now I defined it this way :

Left =Fields!RegionCode.Value Operator = Right =IIF(IsNothing(Parameters!RegionCode.Value),Fields!RegionCode.Value,Parameters!RegionCode.Value)

I do this for the 4 parameters that are passed, if they are null, make then equals to the field.

I was wondering if there is a way to ignore the whole parameter all together, I would guess it'll be faster to execute.

Thanks

One way would be to use this in your stored procedure or query, with coalesce...

eg. AND COALESCE(@.RegionCode,[RegionCode]) = [RegionCode]

That way you're not bringing the data into the report and then filtering it out, you're filtering at the source.

|||

No I don't want to do that,

The idea is the get ALL the data in a snapshot nightly then apply filters on the snapshot when we display to the users.

The result is that the DB will not get hammered everytime a report is run but only once at night.

So the coalesce will not work.

|||

I guess you could do something then with the left side of the filter and an if statement?

If the parameter on the left side is null, then set it to 1, otherwise set it to the field. If the parameter on the right side is null, set it to 1, otherwise set it to the parameter. Then 1=1 and it will ignore the parameter.

Not sure if this will work but it sounds faster than comparing field to field in theory.

cheers,

Andrew

|||

Andrew - T4G wrote:

I guess you could do something then with the left side of the filter and an if statement?

If the parameter on the left side is null, then set it to 1, otherwise set it to the field. If the parameter on the right side is null, set it to 1, otherwise set it to the parameter. Then 1=1 and it will ignore the parameter.

Not sure if this will work but it sounds faster than comparing field to field in theory.

cheers,

Andrew

Thanks, I'll try something like that.

Filters & Parameters pane missing in MDX Query Designer

I'm developing my first Analysis Server report in SRS. Everything was going well when I discovered the filters & parameters pane was missing in the MDX Query Designer in data mode. I have my columns and rows but need to drag a dimension into the filters & parameters pane in MDX Query Designer and set a filter and parameter on it. Any idea how to make it viewable?

I may have made a discovery why I am not seeing the filter and parameter pane in the MDX Query Designer in SQL Server Business Intelligence Development Studio.The filter and parameter pane does appear in the MDX Query Designer in SQL Server Business Intelligence Development Studio on a report created from a SQL Server 2005 database, but not on a SQL Server 2000 database. Am I on to something and is there a work around for SQL Server 2000 databases?

Filters & Parameters pane missing in MDX Query Designer

I'm developing my first Analysis Server report in SRS. Everything was going well when I discovered the filters & parameters pane was missing in the MDX Query Designer in data mode. I have my columns and rows but need to drag a dimension into the filters & parameters pane in MDX Query Designer and set a filter and parameter on it. Any idea how to make it viewable?

I may have made a discovery why I am not seeing the filter and parameter pane in the MDX Query Designer in SQL Server Business Intelligence Development Studio.The filter and parameter pane does appear in the MDX Query Designer in SQL Server Business Intelligence Development Studio on a report created from a SQL Server 2005 database, but not on a SQL Server 2000 database. Am I on to something and is there a work around for SQL Server 2000 databases?

Filters ! Filters ! Filters - Cascading parameters

Hi,
I have a table with 100 rows in the following format
---
country name state name city name
---
USA NY NY
USA NJ Newark
USA FL Orlando
USA FL Miami
USA CA LA
USA CA SF
China XX XX
India XX XX
Australia XX XX
China XX XX
India XX XX
----
I have 3 dropdown cascading parameters (all of them have default values
when the report loads) - say USA - CA - LA
Country
State
City
So here is my problem. The report loads first time once i click on the
report name. (because it has the default values). But when the user
clicks on the country drop down to select another country say China,
the report down goes blank (white page) UNITL THE USER CLICKS THE "VIEW
REPORT" BUTTON (WHICH IS VERY VERY ANNOYIG). Is there anyway i can
leave the table as it is without making it blank.
Please help
Thanks
Prashhi
I don't think there is a work around to this (but I would be interested in
finding out one). This is just the default behavior which appears to be a
postback on change.
Also, coming from the other direction AND just as annoying (my defaults, by
request of the customer, are 'ALL','ALL','ALL' - which is a waste of computer
resources). Fortunately, we have an ASP.NET interface which 90% of the end
users use.
Rob
"Prash" wrote:
> Hi,
> I have a table with 100 rows in the following format
> ---
> country name state name city name
> ---
> USA NY NY
> USA NJ Newark
> USA FL Orlando
> USA FL Miami
> USA CA LA
> USA CA SF
> China XX XX
> India XX XX
> Australia XX XX
> China XX XX
> India XX XX
> ----
>
> I have 3 dropdown cascading parameters (all of them have default values
> when the report loads) - say USA - CA - LA
> Country
> State
> City
>
> So here is my problem. The report loads first time once i click on the
> report name. (because it has the default values). But when the user
> clicks on the country drop down to select another country say China,
> the report down goes blank (white page) UNITL THE USER CLICKS THE "VIEW
> REPORT" BUTTON (WHICH IS VERY VERY ANNOYIG). Is there anyway i can
> leave the table as it is without making it blank.
>
> Please help
> Thanks
> Prash
>

Filters

A common question is on reports with parameters - is there some way to also
display what the parameter values were for this given report?I did find some ways to display the paramters - but man are they ugly:
Expression:
="Type: "&Join(Parameters!AssessmentTypeStatusATSStatus.Value)
The value selected is "Current" - but what is display on report with above
expression is:
Type: [Assessment Type Status].[ATS Status].&[Current]
"Joe" <hortoristic@.gmail dot com> wrote in message
news:eRTuvaQrGHA.3564@.TK2MSFTNGP03.phx.gbl...
>A common question is on reports with parameters - is there some way to also
>display what the parameter values were for this given report?
>
>

Filtering the dataset

Hi All,
I have reports which return employee information. I want the report to have
an optional 3 parameters representing Entity, Business Unit and Cost Centre.
If any of these parameters(or a combination is used) I want to filter the
returned dataset to display only relevant rows.
Before anyone suggests using the parameters in the stored procedure, forget
it! The stored procedure is complex enough that it has to figure out the
users access level...so I'd rather filter after the fact.
I've hit a brick wall with this one, so if anyone has any fantastic ideas,
Id love to hear them.
Kind Regards,
Terry PinoFiltering the dataset can be done with the filter tab in the dataset.
U can try with the filters - Expression & Value. using custom expressions.
To help U out, I need more details abt the parameters and returned columns
with which u filter the dataset.
"Terry" wrote:
> Hi All,
> I have reports which return employee information. I want the report to have
> an optional 3 parameters representing Entity, Business Unit and Cost Centre.
> If any of these parameters(or a combination is used) I want to filter the
> returned dataset to display only relevant rows.
> Before anyone suggests using the parameters in the stored procedure, forget
> it! The stored procedure is complex enough that it has to figure out the
> users access level...so I'd rather filter after the fact.
> I've hit a brick wall with this one, so if anyone has any fantastic ideas,
> Id love to hear them.
> Kind Regards,
> Terry Pino|||Thanks Chans,
I worked it out after posting this article.Basically I ended up with 3
filter values each one something like this:
iif(Parameter.value1 is null,"",Fields.value1) = iif(Parameter.value1 is
null,"",Parameter.value1)
Getting around my problem of the filter parameter being null.
Always helps to talk about things;-)
Cheers,
Terry
"Chans" wrote:
> Filtering the dataset can be done with the filter tab in the dataset.
> U can try with the filters - Expression & Value. using custom expressions.
> To help U out, I need more details abt the parameters and returned columns
> with which u filter the dataset.
>
> "Terry" wrote:
> > Hi All,
> >
> > I have reports which return employee information. I want the report to have
> > an optional 3 parameters representing Entity, Business Unit and Cost Centre.
> > If any of these parameters(or a combination is used) I want to filter the
> > returned dataset to display only relevant rows.
> > Before anyone suggests using the parameters in the stored procedure, forget
> > it! The stored procedure is complex enough that it has to figure out the
> > users access level...so I'd rather filter after the fact.
> > I've hit a brick wall with this one, so if anyone has any fantastic ideas,
> > Id love to hear them.
> >
> > Kind Regards,
> > Terry Pino|||Did you filter the report or the dataset?
"Terry" wrote:
> Thanks Chans,
> I worked it out after posting this article.Basically I ended up with 3
> filter values each one something like this:
> iif(Parameter.value1 is null,"",Fields.value1) = iif(Parameter.value1 is
> null,"",Parameter.value1)
> Getting around my problem of the filter parameter being null.
> Always helps to talk about things;-)
> Cheers,
> Terry
> "Chans" wrote:
> > Filtering the dataset can be done with the filter tab in the dataset.
> > U can try with the filters - Expression & Value. using custom expressions.
> >
> > To help U out, I need more details abt the parameters and returned columns
> > with which u filter the dataset.
> >
> >
> > "Terry" wrote:
> >
> > > Hi All,
> > >
> > > I have reports which return employee information. I want the report to have
> > > an optional 3 parameters representing Entity, Business Unit and Cost Centre.
> > > If any of these parameters(or a combination is used) I want to filter the
> > > returned dataset to display only relevant rows.
> > > Before anyone suggests using the parameters in the stored procedure, forget
> > > it! The stored procedure is complex enough that it has to figure out the
> > > users access level...so I'd rather filter after the fact.
> > > I've hit a brick wall with this one, so if anyone has any fantastic ideas,
> > > Id love to hear them.
> > >
> > > Kind Regards,
> > > Terry Pino