Tuesday 15 May 2012

Know Your Calculated Measure Definition Using MDX Query

select measure_name, expression from$system.mdschema_measures
where measure_unique_name = '[Measures].[Internet Ratio to Parent Product]'






Definition used for calculated Measure [Measures].[Internet Ratio to Parent Product]

 Case    When [Product].[Product Model Categories].CurrentMember.Level.Ordinal   = 0 Then 1          Else
[Measures].[Internet Sales Amount]    /    [Product].[Product Model Categories].CurrentMember.Parent,                [Measures].[Internet Sales Amount] )      End 

Sunday 6 May 2012

DrillDown Dashboard In SSRS

Drilldown Chart (Dashboard)in SSRS without using Sub Reports.
In SSRS as we develop drilldown reports in the same way we can develop drill down charts. Once you click on the main chart the corresponding detail level chart should display on the same report without using Sub report.
Let’s develop a drilldown chart
We have one bar chart showing total sale yearly. Once you click on the any year bar the corresponding pie chart should display with product wise sale.
Steps to create a drilldown chart.
1. Create a new report and  name it dashboard with your shared data source (I am going to use sample Adventure works database)
2. Create a dataset with the following query
 SELECT   Floor(SalesLT.SalesOrderDetail.LineTotal) as  Sale, DATEPART(yyyy, SalesLT.Product.SellStartDate) AS Year FROM  SalesLT.Product INNER JOIN                         SalesLT.SalesOrderDetail ON SalesLT.Product.ProductID = SalesLT.SalesOrderDetail.ProductID
3. Now you need to add a bar chart to your report 
 4. Right click on the chart and set the chart properties



Now our first bar chart is ready to preview.
Our main task is to create a sub chart on clicking on the year bar(Bar Chart) corresponding pie chart should display with information.
5.Now create another data set for the second chart and name it Product.
SELECT   top 5    SalesLT.ProductCategory.Name, Floor(Sum(SalesLT.SalesOrderDetail.LineTotal)) as LineTotal FROM    SalesLT.Product INNER JOIN  SalesLT.SalesOrderDetail ON SalesLT.Product.ProductID=SalesLT.SalesOrderDetail.ProductID INNER JOIN  SalesLT.ProductCategory ON SalesLT.Product.ProductCategoryID= SalesLT.ProductCategory.ProductCategoryID WHERE        (DATEPART(yyyy, SalesLT.Product.SellStartDate) = @year)
Group by SalesLT.ProductCategory.Name
 The above query has parameter named year
6. Now drag another pie chart on the report and map it with the new dataset.
7. Right click on the chart and set its properties


8. Pass the parameter from main chart(bar) to pie chart
How to pass the parameter from main chart to detail chart (pie)
Now right click on the bar and then click on the bar chart series properties.

8.1   Go to Action tab and click on option Go to report.
8.2    Choose the same report i.e. Dashboard
8.3   Click on Add button  and add parameters the report

9. Set some default parameter to your report so when you preview your report it should not ask you for parameter it should render with some set of parameter.
10.Go to the year parameter and right click on it and set default value to it as -1


11. Now we need to set some visibility to second chart so when you run your report first time only bar chart should display on clicking on the any bar of the first chart than only second chart should display.
12.  Now you right click on the Pie chart and then click on the chart properties.
13.  Then you click on visibility.
14.  On show and hide based expression place this  
 =IIF(Parameters!year.Value>0,false,true)

Now you report is ready to preview
For the look and feel of the charts format your charts as per your requirements.
Preview your report  first you will see this chart in pic. below

Click on any year bar  you will have your pie chart along with bar chart