Archive

Posts Tagged ‘MDX’

SSRS Report Parameter change does not refresh default values of dependent parameters Using SSAS cube as Data Source

February 14, 2016 Leave a comment

I have already resolved this issue inmy previous blog with SQL server as data source .

Now, I tried to resolve this using SSAS cube as datasource.

Follow below Video  link to see, how to do things to get the default value of parameter to refresh.

 

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Advertisements
Categories: Home, MDX, SSAS, SSRS Tags: , , ,

SSRS parameters measures filters

March 12, 2015 Leave a comment

From MSDN I got this question  SSRS parameters measures filters 

This is the comment of user which he want to do.

For that I created 3 parameter
– the 1st parameter @Typeofmeasure lists the measures that I want to display. I have 2 values (Volume, Sales)
I created these values in specifying these values in the parameters properties

– The 2nd parameter @TypeOfAggregation lists the way that I want these measures to aggregate. I have 3 values ( Year To date, Month to date and Running 12 Month). I created these 3 values in specifying these values in the parameters properties too

– The 3rd parameter @Measures lists the measures that I want to display in my report.
So when user choose “Volume” in the 1rst parameter and “Year To date” for the 2nd parameter I want to have [Measures].[Volume YTD] in the 3rd parameter. Like a cascading parameter.

Before going to SSRS and MDX, please create the Calculated Measures in SSAS for Volume and Sales like Sales YTD , Sales MTD.

In my example I have two Measures and two calculated Measures

[Measures].[Sales Amount]

[Measures].[Order Quantity]

[Measures].[Sales Amount YTD]

[Measures].[Order Quantity YTD]

Lets come to SSRS report, create first parameter

Parameter 1 : – Typeofmeasure

Available values are as shown in below screen:-

Typeofmeasure

Parameter 2 : – TypeOfAggregation

Available values are as shown in below screen:-

TypeOfAggregation

Parameter 3 : – Measures

Available values are as shown in below screen:-

Measures

Measures parameter is cascading one, it has expression based on above two parameters

expression:

=“[Measures].[“+Parameters!Typeofmeasure.Value+ ” “+Parameters!TypeOfAggregation.Value+“]”

Lets create the main dataset for the report as shown in below screen.

MainDataset

MDX is like :

WITH MEMBER [Measures].[KPI]
as
strtomember(@Measures)
select  [Measures].[KPI] on 0,
[Order Date].[Calendar Year].[Calendar Year] on 1
from [Adventure Works DW2008R2]

Lets run the report

Case 1 Sales YTD

SalesYTD

Case 2 Quantity YTD

QuantityYTD

Note : Please keep the Common Name for the Measure in SSRS report so that  field Column will be fixed and it can be used in the SSRS report.

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Categories: MDX, SSAS, SSRS Tags: , ,

MDX – String Functions

September 17, 2012 Leave a comment

String functions used mostly when one wants to write dynamic MDX.  Input parameters for the MDX used as string which converted into member or set using these string functions.

Please follow the below link for more details

MDX-String-Functions

More technical stuff can be found in http://msbitips.com

 

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Categories: MDX Tags:

MDX – Set Functions

September 13, 2012 Leave a comment

Please follow the below link for more details

MDX-Set-Functions

More technical stuff can be found in http://msbitips.com

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Categories: MDX Tags:

MDX – Time Functions

September 13, 2012 Leave a comment

Please follow the below link for more details

MDX-Time-Functions

More technical stuff can be found in http://msbitips.com

 

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Categories: MDX Tags:

MDX – Navigation Functions

September 11, 2012 Leave a comment

Please follow the below link for more details

MDX-Navigation-Functions

More technical stuff can be found in http://msbitips.com

 

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Categories: MDX Tags:

Multi – Dimensional Queries (MDX)

September 10, 2012 2 comments

MDXMulti Dimensional Expressions . MDX used to query OLAP (Online Analytical Processing) databases.

SQL is used to query relational database like that MDX is used to query OLAP database/ Cubes.

MDX looks like T-SQL but in MDX we can define axis where we can do slice and dice of the data.

Basic Structure of MDX

SELECT
{Measures} ON Columns,
{<Set> or <Tupple> } ON Rows
FROM [Cube]

Please follow the below link for more details

Multi-dimensional-queries (MDX)

More technical stuff can be found in http://msbitips.com

 

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Categories: MDX Tags: