SSRS nested grouping

Initial steps from: http://www.ienablemuch.com/2012/11/ssrs-simple-grouping.html

This how-to guide shows how to do nested grouping with SSRS. On next post, we will make it dynamic.

14 steps

1

2

3

4. You'll notice there's a duplicate error. We need to hide that column

5

6. We need to merely hide the column, so we can re-use the same report when grouping on columns dynamically

7

8

9

10

11

12

13

14

Done.

Happy Coding! ツ

SSRS simple grouping

Initial steps from: http://www.ienablemuch.com/2012/11/ssrsprimer.html

This how-to guide shows how to do simple grouping with SSRS. Used 20 steps

1

2

3

4

5

6

7

8

9

10

11 

12

SSRS Primer

Data Set source:

create procedure [dbo].[GetTheSalesOfSalesPerson]
as

begin

 select 

  h.SalesPersonID, p.LastName, p.MiddleName, p.FirstName

  , h.SalesOrderNumber
  , h.OrderDate
  , s.Name as CustomerName

 from Person.Person p
 join Sales.SalesOrderHeader h on h.SalesPersonID = p.BusinessEntityID
 join Sales.Customer c on c.CustomerID = h.CustomerID
 join Sales.Store s on s.BusinessEntityID = c.StoreID


 where h.OrderDate >= convert(date, '2008-01-01')


 order by h.SalesPersonID;

end;

20 Steps (all in pictures)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

Alphabet Soup 101: Sargable

Programming industry is notorious for alphabet soup of acronyms and jargons. If some concept or phrase don't have any acronym or catchy word yet, someone will make a made-up one.



When I'm talking to someone about database optimization, I'm referring to an index-friendly condition as index-friendly condition. Could it be any harder than that? But lo and behold, there's a handy word for that programming discipline, it's called SARGable

In relational databases, a condition (or predicate) in a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query. The term is derived from a contraction of Search ARGument ABLE.

A query failing to be sargable is known as Non-Sargable query and has an effect in query time, so one of the steps in query optimization is convert them to be sargable.

The typical thing that will make a sql query non-sargable is to include a function in left part of a condition of a Where clause.

Read the rest of the article at: http://en.wikipedia.org/wiki/Sargable

http://www.sql-server-performance.com/2007/t-sql-where/

Collection initializer

public class Country
    {
        public int CountryId { get; set; }
        public string CountryText { get; set; }
        public List<City> Cities { get; set; }
    }

Found out some (not-so)obscure syntax allowed in C#, whereas given the above class, this is allowed, albeit has null runtime exception on Cities:

public List<Country> Countries = new List<Country>
{
 new Country 
 { 
  CountryId = 1, CountryText = "Philippines",
  Cities = 
  {
   new City { CityId = 1, CityText = "Manila" },
   new City { CityId = 2, CityText = "Makati" },
   new City { CityId = 3, CityText = "Quezon" }
  }
 }
};

Where normally, I do it as this:

public List<Country> Countries = new List<Country>
{
 new Country 
 { 
  CountryId = 1, CountryText = "Philippines",
  Cities = new List<City>
  {
   new City { CityId = 1, CityText = "Manila" },
   new City { CityId = 2, CityText = "Makati" },
   new City { CityId = 3, CityText = "Quezon" }
  }
 }
};

If you don't want to use new List<City> in collection initializer, assign it an instance first, otherwise the collection initializer would result to null runtime exception. The following could allow no new List<City> on collection initializer:

    public class Country
    {
        public int CountryId { get; set; }
        public string CountryText { get; set; }
        public List<City> Cities = new List<City>(); // instantiate List
    }

But that would break the sanctity of everything-must-be-a-property, property makes your code more future-proof