Indicator in SSRS

Guage in SSRS

Sparkline in SSRS

Sparkline:( Multiple values)


   Sparkline is kind of line report
 Usually data bar show single data value whereas Sparkline is used to indicate multiple data values over a
period of time.
 Here vertical axis represent value whereas horizontal axis usually represents time.
 Sparkline is intended to show the trend in measure values and how it is fluctuating.


Prepare Dataset : DataSet_Sparkline

SELECT DimDate.CalendarYear, DimProductCategory.EnglishProductCategoryName AS Category,

SUM(FactResellerSales.OrderQuantity) AS OrderQuantity, SUM(FactResellerSales.SalesAmount) AS

SalesAmount, AVG(FactResellerSales.SalesAmount) AS AvgSales, DimDate.EnglishMonthName,

DimDate.MonthNumberOfYear

FROM DimDate INNER JOIN FactResellerSales ON DimDate.DateKey = FactResellerSales.DueDateKey

INNER JOIN DimProduct ON DimProduct.ProductKey = FactResellerSales.ProductKey

INNER JOIN DimProductSubcategory ON DimProductSubcategory.ProductCategoryKey =

DimProduct.ProductSubcategoryKey INNER JOIN DimProductCategory ON

DimProductCategory.ProductCategoryKey = DimProductSubcategory.ProductCategoryKey

GROUP BY DimDate.CalendarYear, DimProductCategory.EnglishProductCategoryName,

DimDate.EnglishMonthName, DimDate.MonthNumberOfYear

Design :

now add a sparkline control--configure it 

output :

same as above we can can group it --Category wise also our sparkline. but above line showing overall salesmount. ( in 2002---may some month don't have sales then need to know --configure as monthwise)

now output :

Change Sparkline Type :  Column [ to know more clearly monthwise bars]

Data Bar in SSRS

Data Bar :  (Single value)

 The data bar is a new visualization (2008 R2) that essentially provides a bar chart to convey a lot of
information in a little space.
 Data bars are often used in table and matrices
 This enables you to easily compare the data in one row to another based on the relative length of their
data bars.
 Data bars can represent multiple data points, but typically illustrate only one.
 Data bars usually depict single value in a cell.
 We cannot add a data bars to a detail group in a table.
 By default when you create a databar you cannot see axis, legend etc.

Prepare a Dataset :

Select DimSalesTerritory.SalesTerritoryGroup,DimDate.CalendarYear,

SUM(FactResellerSales.SalesAmount) As SalesAmt

From DimSalesTerritory INNER JOIN

FactResellerSales ON DimSalesTerritory.SalesTerritoryKey

=FactResellerSales.SalesTerritoryKey INNER JOIN

  DimDate ON FactResellerSales.OrderDateKey=

DimDate.DateKey

GROUP BY DimSalesTerritory.SalesTerritoryGroup,DimDate.CalendarYear

Design :

then

Now observe it

Now we will make them Grouping SalesTerritory Group wise.

difference -- Yearwise

1. Yearwise

   

2. SaleTerritoryGroup

  

Compare the values(Numaric ) in row we have to use DataBar control.

it supporting Grouping Tablix or Particular column also possiable.

Chart Control in SSRS

In SSRS we many rich Data Visualization controls---like Chart,Data Bar,Sparklinke,Gauage.

advantages :
vast infomation is represented through Data visualization control/Graphical representation.

Chart
Bar Chart
Sparkline
Gauge

Working with Chart :

Chart Report contains three sections:

1.  Data Section (Values): Represent actual data means values along Y-Axis. Values must be a numeric data type.

      2.  Category Section (Category groups): It gives group wise information along X-Axis.
      3.  Series Section (Series Group) The chart control can also layer multiple sets of data onto the same chart. Each layer is called a series

Prepare a Dataset 

SELECT  DimSalesTerritory.SalesTerritoryCountry, DimDate.CalendarYear, DimDate.CalendarQuarter,FactInternetSales.SalesAmount

FROM DimSalesTerritory INNER JOIN

FactInternetSales ON DimSalesTerritory.SalesTerritoryKey = FactInternetSales.SalesTerritoryKey INNER JOIN

DimDate ON FactInternetSales.OrderDateKey = DimDate.DateKey

Chart :

now

Grouping Yearwise overwise graph ---Serices

 

i customized as above chart. and Each year is a data point(we can consider as data bar) with different colors(we can customize these yearwise colors also). multiple data points is possiable in chart control

Note:
This bar chart showing Yearwise ---as --color bar but not amount, sales is SUM Amount of the Year.

Drill Through Report and Sub Reports in SSRS

Yes,

Parameter as Filter in SSRS

            If we want to make a Dataset as Shared Dataset---Datasource also be a sharedDataSource.

Query :

Select DimDate.CalendarYear,DimReseller.BusinessType,SUM(FactResellerSales.SalesAmount) as TotalSales,
DimDate.EnglishMonthName,DimDate.CalendarQuarter,SUM(FactResellerSales.TaxAmt) as Totaltax
From DimReseller INNER JOIN
FactResellerSales ON DimReseller.ResellerKey=FactResellerSales.ResellerKey INNER JOIN
DimDate ON FactResellerSales.OrderDatekey=DimDate.DateKey
Group by DimDate.CalendarYear,DimReseller.BusinessType,DimDate.EnglishMonthName,DimDate.CalendarQuarter

   

shareddataset has query/storeprocedure other datasets which are refereced this query/stroedprocedure.

Filters:  We can apply Filters at while design Dataset itself.

without filter Yearwise getting the matrix report: 

1. Apply Filter at Dataset level.

Dataset--Properties--> Filters 

output :

2. Adding Filter at DataResign level.( Matrix Report level)

            We can apply Filter at Runtime---end user prospective also.

then Apply Filter

output for both is same. we have wonderful advantage is Tablix data region Filter is upto that resigion only. which should not effect other data region.

Example :  Add a table ---CalenderYear wise then--set visiablity property also.

 set visiablity property for--DrillDown --option

output :

we can apply Filter at Group level also

Now done(Dataset,DataRegions,Grouplevel Filter) .-----done at report design time itself. 

Best Practice : 

                  we given option to end user to apply at runtime this Filter option. for this delete existing applied Fitler from above Matrix report--Tablix Filter

now we created a parameter--@Year --with 4 year( 2005,2006,2007,2008).

Now we can apply this parameter at any level --same as above(Dataset,Tablix,Grouplevel)

Dataset level :

=Parameters!Year.Value

output

Apply @Year at Tablix level also which doesn' effect other regions.

Query :

SELECT        DimDate.CalendarYear, DimReseller.BusinessType, SUM(FactResellerSales.SalesAmount) AS TotalSales, DimDate.EnglishMonthName, DimDate.CalendarQuarter, SUM(FactResellerSales.TaxAmt)
                         AS TotalTax
FROM            DimReseller INNER JOIN
                         FactResellerSales ON DimReseller.ResellerKey = FactResellerSales.ResellerKey INNER JOIN
                         DimDate ON FactResellerSales.OrderDateKey = DimDate.DateKey
GROUP BY DimReseller.BusinessType, DimDate.CalendarYear, DimDate.EnglishMonthName, DimDate.CalendarQuarter