MDX : Removing All from parameter list in SSRS

Problem Domain : Remove All option from your parameter drop down list.

Workaround : To remove All option from your parameter drop down list, just right click   

on Datasets and click on ‘Show Hidden Datasets’.

It will show you different datasets for different parameter.(Refer below screenshot)

There are following two ways to remove All from selection-

1.     First one requires query modification-

        Double click on desired dataset and open it into query designer mode

        then Change ALLMEMBERS to CHILDREN as highlighted below.

2.    Second solution requires adding  filter using the filter dataset method-
       right click on desired  dataset (use above technique to unhide the hidden                          datasets),and then select Query Properties.

       Within the Dataset Properties Window, click on the Filter Option,eXclude All by                using a filter of ParameterLevel > 0 as displayed below.

Save and run the report ,The parameter will not show All option. 

Likewise you can remove unnecessary options available in parameter dropdown list

Like UNKNOWN , Blank and also any particular value.

"Excel Connection Manager": The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered

Sometimes while importing data from Excel source following error is generated.This can be solved by using simple steps listed below.

Error Message-

 0xC0209303 at Package, Connection manager "Excel Connection Manager": The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.

Solution –

IN Business Intelligence Development Studio

Go to Project --> Project Properties --> configuration Properties --> Debugging -->

Run64bit runtime-

Set it to False.

Truncation error while executing SSIS package-Importing tables from different data sources.

Error Message-

0xC020901C at Data Flow Task, OLE DB Source [79]: There was an error with OLE DB Source.Outputs[OLE DB Source Output].Columns[XYZ] on OLE DB Source.Outputs[OLE DB Source Output]. 

The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

Solution-

Double click on your source (oledb ,Flat file etc) --> OleDB Source Editer --> Error Output.

Select column having truncation error -->  set it to ignore failure.

Do the same for all columns having truncation error.

 Thats it now save and run the package.:-)