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

RowGroup properties for ASC and Desc order

yes, Pervisously we seen SaleTerritory Region as asc order, now we will make as desc for this.

then

we want to provide as a parameter to enduser then need to create parameter (sortorder)

Then

based on enduser selection of "Ascending" or "Descnding" sorting will be performed.

Asc :expression=IIf(Parameters!SortOrder.Value="Asc",Fields!SalesTerritoryRegion.Value,1)
Desc  : expression = IIf(Parameters!SortOrder.Value="Desc",Fields!SalesTerritoryRegion.Value,1)

How to Create ChildGroup for grouping data in SSRS

Yes, Last article we seen how to get the data using parametarized sql query now we see query wise also

YearWise--Quarterwise(default hide)---SalesAmount.

Now i want generate quarter wise also but not default ( Bydefault -need to hide).

Step add new parameter --Quarter:  every year has 4 Quaters i added as hardcoded values

Bydefaut i need Yearwise report.then quarter wise also so i need to create a Child group to Parent group( CalenderYear wise grouping: Pervious article).

for creating a child group

Now

 

output

till now we seen our Paramters(@Year, @Quarter) are hardcoded as

now some time we don't know input parameter value then need to get it as dynamic***  create a dataset.(DST_Month)

select distinct EnglishMonthName,MonthNumberOfYear from DimDate

Order by MonthNumberOfYear

Create a Parameter : Month. append this Dataset for data to this parameter.

now we added. need to change existing dataset(DSR_Parameters)--Query where condition 

WHERE        (DimDate.CalendarYear = @Year and DimDate.CalendarQuarter =@Quarter and DimDate.EnglishMonthName=@Month)

if your @Month accepts multiple parameters then

WHERE        (DimDate.CalendarYear = @Year) and (DimDate.CalendarQuarter =@Quarter) and DimDate.EnglishMonthName in (@Month)

check existing Dataset(DSR_Parameters) 

ad this parameter to report using DSR_Parameter dataset Parameter collection

then output:

SSRS Report paramter for sql query

 Generate SSRS Report based on user inputs done with the help of Parameters.

T-SQL Query:

SELECT   DimSalesTerritory.SalesTerritoryRegion, DimDate.CalendarYear, SUM(FactResellerSales.SalesAmount) AS SalesAmount, SUM(FactResellerSales.TaxAmt) AS TaxAmount, DimDate.CalendarQuarter,

                         DimDate.EnglishMonthName

FROM            FactResellerSales 

INNER JOIN      DimSalesTerritory ON 

FactResellerSales.SalesTerritoryKey = DimSalesTerritory.SalesTerritoryKey 

INNER JOIN      DimDate ON DimDate.DateKey = FactResellerSales.DueDateKey

WHERE  (DimDate.CalendarYear = @Year)

GROUP BY DimSalesTerritory.SalesTerritoryRegion, DimDate.CalendarYear, DimDate.CalendarQuarter, DimDate.EnglishMonthName

@Year is parameter for this DataSet Query.

Now Prepare a Dataset.

now we added parameter information. as a enduser don't know avaialble years of database  so execute db query based it add years as Lables

now perview no data beacuse no defult value of @Year parameter

Through this demonstration we will know it as Text query also(without stored procedure) we can pass parameter to query.  

Parameter (@Year) is passed for sql Query.

How to use ListReport

    Now prepared a Dataset using Advantureworks DimCustomer using required fields(FirstName,LastName,BirthDate,Email,Address) prepare a dataset.

How to use Calculated Column ?

Right click on Dataset(FullName --Expression)

now write expression

2nd Requirement :

Sorting using TableProperites(not Rectangular Properties)-->Sorting--Add--Full name : A-Z order

once we sorted rows will be sorted in Alphabets order.

3rd Requirement

Difference between List and Table and Matrix reports ?