SQL SERVER Disk Space Alert

Step 1: Create the database mail profile account using SQL Server Management Studio. Give the profile name to "FreeSpaceAlertMails"

Step2: Create the below procedure in master database which will check the disk space.

CREATE PROCEDURE sendAlertMails
AS
BEGIN
SET NOCOUNT ON
DECLARE @availableSpace AS FLOAT
DECLARE @alertMessage AS Varchar(4000)

CREATE TABLE #tbldiskSpace
(
driveName VARCHAR(3),
freeSpace FLOAT
)

INSERT INTO #tbldiskSpace EXEC master..XP_FixedDrives
SELECT @availableSpace = ROUND((freeSpace)/1024,1) FROM #tbldiskSpace WHERE driveName = 'E'

SET @alertMessage = '(host:jshah.sqldbpool.com)E:\ Disk Space Critical. Free Space Available on E:\ Drive is ' + CAST(@availableSpace AS VARCHAR) + 'GB'

IF @availableSpace < 10
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'FreeSpaceAlertMails',
@recipients = 'jshah143@gmail.com',
@body = @alertMessage,
@importance = 'High',
@subject = 'host:jshah.sqldbpool.com Disk Critical E Drive' ;
END

DROP TABLE #tbldiskSpace

END

Step 3: Create the job which will execute the above procedure at every 2 hours interval.

Oracle Predefined Roles

Spatial DBA - Oracle and ArcSDE: Oracle Predefined Roles

File ID 5 of database ID 8 cannot be shrunk as it is either being shrunk by another process or is empty

"File ID 5 of database ID 8 cannot be shrunk as it is either being shrunk by another process or is empty"

 

One more thing to try that worked for me when I received this error message.

I just increased the data file by a few MB and then tryed to do the Shrink again. It worked fine.

It seems like adding a few MB to the data file resets an internal counter or switch that tells it it's not in the middle of a shrink now.

LINKUP

-- target server name

EXECUTE
sp_addlinkedserver
@server =
N'INSTANCENAME\DEV2005',

@srvproduct =
'',

@provider =
N'SQLOLEDB',

@datasrc =
N'TARGETINSTANCE\DEV2005',

@catalog =
N''

select
*
from
sys.servers;

-- add users who want to see that

EXECUTE
sp_addlinkedsrvlogin
@rmtsrvname =
'TARGETINSTANCE\DEV2005',

@useself =
'false',

@locallogin =
'DOMAIN\hemesh.patel',

@rmtuser =
'HM_ReadONLY',

@rmtpassword =
'passwd'

EXECUTE
sp_addlinkedsrvlogin
@rmtsrvname =
'TARGETINSTANCE\UAT2008',

@useself =
'false',

@locallogin =
'DOMAIN\AsiriF',

@rmtuser =
'HM_ReadONLY',

@rmtpassword =
'passwd'

EXECUTE
sp_droplinkedsrvlogin

@rmtsrvname =
'TARGETINSTANCE\LIVE2008'
,

@locallogin =
'DOMAIN\Gihan.Desilva'

Uninstall Oracle 9i from Windows

The simplest way to remove Oracle is to run the Oracle installer:

Start > Programs > Oracle Installation Products > Universal Installer

1. On the first screen click on "Deinstall Products..."
2. Expand the tree view (just so that the second level is visible) and make sure you select everything that is selectable.
3. Click on "Remove..."
4. On the confirmation screen click "Yes"
5. When it has finished click "Close" and then "Exit" to quit the installer

Whilst the Oracle installer removes many components there are a number of things that it leaves behind. In order to completely remove all traces of Oracle the following additional steps will need to be taken:

1. Stop any Oracle services that have been left running. (Start > Settings > Control Panel > Services.)
   Services which I have found left behind are 'OracleOraHome90TNSListener' and 'OracleServiceORACLE'. However there may be others depending on your installation. Look for any services with names starting with 'Oracle'.
2. Run regedit (Start > Run > Enter "regedit", click "Ok"), find and delete the following keys:
   
   HKEY_LOCAL_MACHINE
\SOFTWARE
\ORACLE

HKEY_LOCAL_MACHINE
\SYSTEM
\CurrentControlSet
\Services
\EventLog
\Application
\Oracle.oracle
   
   Note: I have had it reported that some people also have registry entries saved under HKEY_CURRENT_USER\SOFTWARE\ORACLE, this registry entry may be created by some Oracle utilities. If it exists then delete it.
3. Delete the Oracle home directory:
   "C:\Oracle"
   This will also remove your database files (unless you located them elsewhere, in which case you will need to delete them separately).
4. Delete the Oracle program Files directory:
   "C:\Program Files\Oracle"
5. Delete the Oracle programs profile directory:
   "C:\Documents and Settings\All Users\Start Menu\Programs\Oracle - OraHome90"
   if you did not first run the Oracle installer to remove Oracle then there may be other Oracle profile group directories to remove.
6. Some of the Oracle services may be left behind by the uninstall. Open ‘services’ on the control panel, make a note of which Oracle services remain and see the notes ‘How to remove a service’ to remove them.
7. If you didn't first run the Oracle Installer to remove Oracle then you may have some references to Oracle left in the path. To remove these: Start > Settings > Control Panel > System > Advanced > Environment Variables, look at both the use and system variable 'PATH' and edit them to remove any references to Oracle.

Acknowledgements: My appreciation to Alistair Jones for help on this procedure and for encouragement to write it.

---

These notes have been tested with Oracle 9i under Windows XP.

This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.

HOW TO DELETE SERVICE IN WINDOWS

Normally it should not be necessary to manually delete a service. Uninstalling an application should remove its associated service (if any).

However, should it be necessary to manually remove a service:

1. Run Regedit or regedt32.
2. Fnd the registry entry:

   HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services

3. Find the service there and delete it.
   You may wish to look at the keys and see what files the service was using and perhaps delete them also.

Note:

• You will have to reboot before the list gets updated in server manager.
• I have had it pointed out that some programs are cunning and prevent you from removing their service easily. (For example the spyware\malware called ‘Command Service’ - "cmdService".) These change the permissions to make it more difficult for you to delete them. For these you will have to right click on the ‘service’ in regedit (regedt32), go to permissions and grant the administrator full control before the service can be deleted.

SSRS Query Where clause

In The SQL SERVER ::

Where C.name IN (@country) AND (R.first_name like ISNULL( @firstName , '%%') )

AND (R.last_name like ISNULL(@lastName , '%%') )

AND (R.company_name like ISNULL( @CompanyName , '%%'))

AND ( R.email like ISNULL ( @email , '%%') )

In The Oracle ::

Where C.name IN (:country)

or

FROM TWITTER

where date_created >= :startdate and date_created <= :enddate

and channel like nvl(:channel,'%%')

and username like nvl(:username,'%%')

order by date_created asc

------> above :enddate parameter's default value '=Today()'

Sum Total:

[="Total Logins : "&Sum(Fields!No_of_Unique_Logins.Value)]

Count Total:

[="Total Users : "&count(Fields!USERNAME.Value)]

Execution Time:

="Executed On @ " + Format(Globals!ExecutionTime,"dd/MM/yyyy hh:mm:ss tt")

Report Properties:

Tital = 'Normal, Tahoma, 13pt, Medium'

Column='Normal, Cambria, 11pt, Bold'

value field='Normal, Verdana, 10pt, Normal'

Analyzing Memory Requirements for SQL Server

Memory: Available Bytes simply indicates how many bytes are available. If the counter is high, then your server is working fine, but check the counter when the server is undergoing the normal load that it is expected to handle.

Memory: Pages/sec. determines how many pages are read/written to the disk because of hard page faults. A hard page fault pertains to data fetching from hard disk, which adds latency to the throughput of the data. So, this counter should be as low as possible.

SQL Server: Buffer Manager: Buffer cache hit ratio should be more than 90% for a SQL Server.

SQL Server: Buffer Manager: Page Life Expectancy should be above 350. If it is lower or almost equal to 350-400 mark and you are expecting much growth of the database, then be ready to buy more memory modules for your server.

SQL Server: Memory Manager: Total Server Memory (KB) counter is consistently high compared to the amount of physical memory in the computer, it may indicate that more memory is required.

Process: Working Set tells you much memory is SQL Server consuming.

If it is way below than what you have configured by MIN SERVER MEMORY then you have allocated more memory than the server needs

and

if it exceeds MAX SERVER MEMORY, then look TO buy new memory.

In the figure below, you can see that the upper perfmon is showing signs of danger in page life expectancy even though the buffer cache hit ration is above 90. The lower one show things improved after adding more memory and getting more page life expectancy than the previous one.

http://www.sqlservercentral.com/articles/Performance+Tuning/analyzingmemoryrequirementsforsqlserver/2498/

SQL SERVER & OS MEMORY

http://www.eraofdata.com/blog/2008/10/sql-server-memory-configuration/

http://msdn.microsoft.com/en-us/library/ms178067.aspx

Comparing Failover Clustering to Other Availability Technologies

Understanding the importance of transaction logs in SQL Server

Transaction logs are a vital yet often overlooked component of database architecture. They are often forgotten because they are not something actively maintained like the schema contained within a database. In this article we'll examine how transaction logs are used in Microsoft SQL Server, maintenance and potential problems with them, how they can be used to restore a database, and finally, optimizing them for performance.

SQL Server transaction logs
A transaction log is a sequential record of all changes made to the database while the actual data is contained in a separate file. The transaction log contains enough information to undo all changes made to the data file as part of any individual transaction. The log records the start of a transaction, all the changes considered to be a part of it, and then the final commit or rollback of the transaction. Each database has at least one physical transaction log and one data file that is exclusive to the database for which it was created.

SQL Server keeps a buffer of all of the changes to data for performance reasons. It writes items to the transaction log immediately, but does not write changes to the data file immediately. A checkpoint is written to the transaction log to indicate that a particular transaction has been completely written from the buffer to the data file. When SQL Server is restarted, it looks for the most recent checkpoint in the transaction log and rolls forward all transactions that have occurred from that point forward since it is not guaranteed to have been written to the data file until a checkpoint is entered in the transaction log. This prevents transactions from being lost that were in the buffer but not yet written to the data file.

Transaction log maintenance
Transaction logs can present problems because they are often forgotten about until an issue occurs. The log continues to grow as operations are performed within the database. While the log continues to grow, the available disk space decreases. Unless routine action is taken to prevent it, the transaction log will eventually consume all available space allocated to it. If the log is configured to grow indefinitely as is the default, it will grow to consume all available physical disk space where it is stored. Either scenario causes the database to stop functioning.

Regular backups of the transaction log will help prevent it from consuming all of the disk space. The backup process truncates old log records no longer needed for recovery. The truncation process involves marking old records as inactive so they can be overwritten, which prevents the transaction log from growing too large. If frequent backups are not made, then the database should be configured with the "simple recovery model" option. The simple recovery model will force the transaction log to be truncated automatically each time a checkpoint is processed.

The truncation process that occurs as a result of a backup or the checkpoint will mark old log records as inactive so they can be overwritten, but it does not reduce the actual disk space allocated to the transaction log. The logs will keep the space allocated even if it is not used. This is where shrinking comes into the maintenance picture. The act of shrinking the log removes the inactive records and reduces the physical size of the log file.

A log is shrunk when a DBCC SHRINKDATABASE statement is executed against the owning database, a DBCC SHRINKFILE is executed against the specific transaction log, or an autoshrink operation occurs if it is enabled on the database. When shrinking the log, it is first truncated to mark inactive records and then the inactive records are removed. Depending upon how you are trying to shrink the log, you may or may not see immediate results. Ideally, shrinking should be performed on a scheduled basis so that it is forced to occur at points when the database utilization is lower.

Restoring a database
Transaction log backups can be used to restore a database to a specific point in time. A transaction log backup alone is not sufficient to restore a database. A backup of the data file is required as well. The data file backups are applied first. A full data file backup is restored and followed by any differential backups of the data file. It is very important when restoring the data file backup not to mark the recovery as complete, otherwise no transaction log backups can be restored. Once the data file restore is complete, the backups of the transaction logs are applied to return the database to its desired state. If there have been multiple transaction log backups since the last database backup, then the transaction log backups need to be restored in the order in which they were created.

There is another process known as log shipping that can be used to keep a hot stand-by of a database available. When log shipping is configured, a full backup of the database is copied to another server. From that point forward, transaction logs are periodically sent to the other server where they are automatically restored in the stand-by database. This keeps a hot spare of the server available. It is also commonly used to keep a reporting server up to date with recent data changes. Another server, known as a monitor server, can be configured to watch the shipping to make sure that it occurs on the scheduled interval. If it does not occur for some reason, then the monitor server will record an event to the event log. This makes log shipping a popular choice to be included as a part of disaster recovery planning.

Optimizing for performance
Transaction logs play a vital role in the function of a database. As a result, they can have a direct impact on the overall system performance. There are certain configurations that can be made that will optimize the performance of transaction logs. The transaction log is a sequential write to the physical disk, and there are no reads that occur as a part of the logging process. Thus, if the logs are isolated on a separate disk, it will optimize the performance because there will be nothing interfering with the writing of the transaction log.

Another optimization relates to the growth of the transaction log size. The log can be configured to grow as a percentage of the total size or at a set physical rate. Regardless of the growth option, the size of the growth should be large enough to prevent the log from needing to continually expand. If the growth rate is set to low the log may be forced to continually expand, which will slow the database performance.

To Get The Result in One Lane

create table #tmp7 (col1 varchar(2000))

insert #tmp7 exec master..xp_cmdshell 'osql -E -w2000 -Q "exec sp_helplogins"'

select * from #tmp7

drop table #tmp7

Linked Servers

Add Linked server and Logins !!

EXECUTE sp_addlinkedserver @server = N'BSQ-SQlxxxxx\xxxxx',

@srvproduct = '',

@provider = N'SQLOLEDB',

@datasrc = N'BSQ-SQLxxxxx\xxxxxx',

@catalog = N''

select * from sys.servers;

create logion Just for read access in Remote SERVER as SQL SERVER Authentication.

assign only db_datareader,public role and In userMapping only Databases you wanted to give access to read .

EXECUTE sp_addlinkedsrvlogin @rmtsrvname = 'BSQ-SQlxxxxx\xxxxx',

@useself = 'false',

@locallogin = 'DOMAIN\hem.p',

@rmtuser = 'HM_ReadONLY',

@rmtpassword = 'xxxx'

EXECUTE sp_droplinkedsrvlogin

@rmtsrvname = 'BSQ-SQlxxxxx\xxxxx' ,

@locallogin = 'DOMAIN\hem.p'

You can see your Linked server at SSMS-->Object Explorer --> Source sql Instance-->Server Objects --> Linked Servers.

That's It!!

Done!!

Cheers !!

SSRS Time And Total

To display date and time

= FORMAT(Globals!ExecutionTime, "MM/dd/yyyy hh:mm tt")

Only Time

= FORMAT(Globals!ExecutionTime, "hh:mm tt")

And To get

Total No Of Lines e.g. =Count(Fields!Account_ID.Value)

Sum Of Particular Field e.g. =sum(Fields!No_of_Times_Logged.Value)

To get Between WeekDays

="User Registered " & now().AddDays(-7).tostring("dd/MM/yyyy") & " - " & now().AddDays(-1).tostring("dd/MM/yyyy")

To get Between Months

="Oracle Core Idea Last Month Stats Rerport ( " & now().AddMonths(-1).Adddays(-(today.DAY-1)).tostring("dd/MM/yyyy") & " - " & today.Adddays(-(today.DAY-1)).tostring("dd/MM/yyyy") & " )"

SSRS Query Parmeter for Oracle/SQL

Using Query Parameters with Specific Data Sources (Reporting Services)

When you define a query for a dataset, you choose the data source type. The data source type specifies the data processing extension or registered .NET Framework data provider used to connect to the data source. The data provider is the software layer that interacts with the data source and determines what support there is for using variables in a query. This topic shows how, for a given result set, a query changes depending on the data source to which you are connected, and whether it contains a variable in the query.

Query Variables and Data Providers

The following table shows the results of data from the AdventureWorks sample database. You can create this result set by running a Transact-SQL query against the AdventureWorks sample database or an MDX query against the Adventure Works cube deployed as part of the AdventureWorks Sample Reports. The result set shows two columns: the names of product models that contain the word "Mountain" and the corresponding number of Internet sales.

Name	InternetSales
Fender Set - Mountain	2121
HL Mountain Tire	1396
LL Mountain Tire	862
ML Mountain Tire	1161
Mountain Bottle Cage	2025
Mountain Tire Tube	3095
Mountain-100	396
Mountain-200	3552
Mountain-400-W	543
Mountain-500	479
Women's Mountain Shorts	1019

In the next sections, you will see the query used to create this result set, first from a relational database and then from a multidimensional database.

Relational Database Data Sources

For a relational database like Microsoft SQL Server, you can create queries using Transact-SQL. Although this query limits the results by including a WHERE clause that is set to a hard-coded value, the query does not yet contain a variable.

Transact-SQL Query
SELECT PM.Name, Count(*) as InternetSales FROM Sales.SalesOrderHeader SOH INNER JOIN Sales.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN Production.Product PP ON SOD.ProductID = PP.ProductID INNER JOIN Production.ProductModel PM ON PP.ProductModelId = PM.ProductModelID WHERE (SOH.OnLineOrderFlag = 1 AND PM.Name LIKE ('%Mountain%')) GROUP BY PM.Name ORDER BY PM.Name

To add a variable for the Product Model name, PM.Name, you need to specify a query parameter in the WHERE clause. The following table shows how to specify query parameters for different data providers. The data provider is automatically selected for you when you choose the data source type. The connection string for this data provider is shown in the second column. The query in the table is identical to the query above but has added a parameter in the form supported by the data provider.

Data Source Type	Data Provider	Query
Microsoft SQL Server	Microsoft SQL Server (SqlClient) Connection String: Data Source=.;Initial Catalog=AdventureWorks	SELECT PM.Name, Count(*) FROM Sales.SalesOrderHeader SOH INNER JOIN Sales.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN Production.Product PP ON SOD.ProductID = PP.ProductID INNER JOIN Production.ProductModel PM ON PP.ProductModelId = PM.ProductModelID WHERE (SOH.OnLineOrderFlag = 1 AND (PM.Name LIKE '%' + @Mountain + '%')) GROUP BY PM.Name ORDER BY PM.Name
OLE DB	Microsoft OLE DB Provider for SQL Server Connection String: Provider=SQLOLEDB.1;Data Source=.;Initial Catalog=AdventureWorks	SELECT PM.Name, Count(*) FROM Sales.SalesOrderHeader SOH INNER JOIN Sales.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN Production.Product PP ON SOD.ProductID = PP.ProductID INNER JOIN Production.ProductModel PM ON PP.ProductModelId = PM.ProductModelID WHERE ((SOH.OnLineOrderFlag = 1) AND PM.Name LIKE ('%' + ? + '%')) GROUP BY PM.Name ORDER BY PM.Name
Oracle	Microsoft SQL Server (OracleClient)	SELECT PM.Name, Count(*) FROM Sales.SalesOrderHeader SOH INNER JOIN Sales.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN Production.Product PP ON SOD.ProductID = PP.ProductID INNER JOIN Production.ProductModel PM ON PP.ProductModelId = PM.ProductModelID WHERE ((SOH.OnLineOrderFlag = 1) AND PM.Name LIKE '%' + :1 + '%') GROUP BY PM.Name ORDER BY PM.Name
OLE DB	Any registered .NET Framework Data Provider	Provider-specific.

In these examples, a variable was added to the WHERE clause of the query, changing the test for the word "Mountain" to a test for the variable value.

• The .NET data provider for SQL Server supports named variables that begin with the at (@) character. For example, PM.Name LIKE '%' + @Mountain + '%').
  
• The OLE DB provider for SQL Server does not support named variables. Use the question mark (?) character to specify a variable. Parameters passed to the OLE DB provider must be passed in the order they occur in the WHERE clause. For example, PM.Name LIKE ('%' + ? + '%').
  
• The .NET data provider for Oracle supports numbered parameters that begin with the colon (:) character. For example, PM.Name LIKE '%' + :1 + '%').
  
• Support for variables by other OLE DB data providers are specific to the data provider. Check the data provider's documentation for more information.
  

The query designer associated with a data source type creates a query parameter for each variable identified. These query parameters are listed on the Parameter tab of the Dataset property pages. For every query parameter, a report parameter of type String is created. The query parameter values defined for the dataset are set to the corresponding report parameter. You can edit the report parameter data type and other properties in the Report Properties dialog box, which is accessible from the Report menu when you are in Data view. You can also create, edit, or delete these query parameters manually. For more information, see Dataset Properties Dialog Box, Parameters.

Multidimensional Database Data Sources

For a multidimensional database like Analysis Services, you can create queries using MDX. Although this query limits the results by including a Filter clause set to a hard-coded value, the query does not yet contain a variable.

MDX Query

SELECT NON EMPTY { [Measures].[Internet Order Count] } ON COLUMNS, NON EMPTY { ([Product].[Model Name].[Model Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( Filter( [Product].[Model Name].[Model Name].ALLMEMBERS, Instr([Product].[Model Name].currentmember.Properties( 'Member_Caption' ), 'Mountain' ) > 0 ) ) ON COLUMNS FROM [Adventure Works]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

To add a variable for the Product Model name, @ProductModelName, you need to add a FILTER clause. The following table shows that, for multidimensional databases, variables are part of a FILTER clause. The data source type is the value you choose from the drop-down list when you create a new data source for Reporting Services. The data provider is automatically selected for you when you choose the data source type. The connection string for this data provider is also shown in the second column. The query in the table is identical to the query above but has added a parameter in the form supported by the data provider.

Data Source Type	Data Provider	Query
Microsoft SQL Server Analysis Services	SQL Server Analysis Services (AdomdClient) Connection String: Data Source=.;Initial Catalog="Adventure Works DW"	SELECT NON EMPTY { [Measures].[Internet Order Count] } ON COLUMNS, NON EMPTY { ([Product].[Model Name].[Model Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( Filter( [Product].[Model Name].[Model Name].ALLMEMBERS, Instr( [Product].[Model Name].currentmember.Properties( 'Member_Caption' ), @ProductModelName ) > 0 ) ) ON COLUMNS FROM [Adventure Works]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
OLE DB	Microsoft OLE DB Provider for Analysis Services 9.0 Connection String: Provider=MSOLAP.3;Data Source=.;Initial Catalog="Adventure Works DW"	OLE DB for OLAP does not support parameterized queries. Workarounds are described in this whitepaper: "Integrating Analysis Services with Reporting Services".

In these examples, a variable was added to the WHERE clause of the query, changing the test for the word Mountain to a test for the variable value.

• The Microsoft SQL Server Analysis Services supports named parameters defined in the Filter section of the MDX query designer. Named variables begin with the at (@) character. For example, @ProductModelName.
  
• The Microsoft OLE DB Provider for Analysis Services 9.0 does not support parameterized queries. For more information, search for "Integrating Analysis Services with Reporting Services" on msdn.microsoft.com.
  
• Support for variables by other multidimensional data providers are specific to the data provider. Check the data provider's documentation for more information.
  

The query designer associated with a data source type creates a query parameter for each variable identified. A special dataset is automatically created to provide valid values for each parameter. These query parameters are listed on the Parameter tab of the Dataset property pages. For every query parameter, a report parameter of type String is created. The query parameter values defined for the dataset are set to the corresponding report parameter. You can also create, edit, or delete these dataset parameters manually. For more information, see How to: Define Parameters in the MDX Query Designer for Analysis Services.

The datasets to provide valid values for each query are created whenever the query is processed.

E.g.

Microsoft QL Server (SqlClient)

Where C.name IN (@country) AND (R.first_name like ISNULL( @firstName , '%%') )

AND (R.last_name like ISNULL(@lastName , '%%') )

AND (R.company_name like ISNULL( @CompanyName , '%%'))

AND ( R.email like ISNULL ( @email , '%%') )

OLE DB Provider for SQL Server/Oracle

Select First_Name,Last_Name From Account

where First_Name like nvl( ? ,'%%') AND Last_Name like nvl( ? , '%%')

Microsoft SQL Server (OracleClient)

Select First_Name,Last_Name From Account

where First_Name like nvl( :first_name ,'%%') AND Last_Name like nvl( :last_name , '%%')

=====>>>>>>>

Thenafter go to 'Layout' ==>> 'Report' Tab ===>> 'Report Parameter' ==>> edit Parameter1,parameter2 -- 'prompt:' as your desire name & check 'Allow null value' & leave other option default ,OK. ==> go to 'Preview' and check your report with condition.

Enjoy It!!

Thanks !!