Stop me if you've heard this one before:

Day three is winding down to a close and boy, what a day it was.  Food poisoning or no, I am going to finish this class like a champ.

Ok, so today was spent, for the majority of the day, working on stored procedures and report generation.  We also went over VIEWs and took a quick look back at the multitude of joins our class seemed to struggle with at times.  Overall, though, I think that the lightbulbs are coming on more often than not.

Jumping right on in, then:  Stored Procedures are a blast, so to speak.  Utilizing a query that the developer builds into the database to pull information in a report over and over again seems to make it a lot more efficient to generate the results requested (I know, duh, right?).  That being said, it isn’t as complicated as one might think.  You take your everyday, run of the mill query, like:

SELECT Sum(FRS.SalesAmount) as SalesAmount

, E.EmployeeKey

, E.FirstName+' '+E.LastName as EmpName

FROM FactResellerSales as FRS

Left Join DimEmployee as E

On E.EmployeeKey = FRS.EmployeeKey

Group by E.EmployeeKey, E.LastName, E.FirstName

Having Sum(FRS.SalesAmount) > 1000000

Order by Sum(FRS.SalesAmount) desc

This query pulls all sales associates with total sales of over $1,000,000.  (Yes, this is from the homework.)  Then, by inserting it into BIDS, you can presto change-o it into the beautiful report attached to this blog!  Ok, all cheese aside, this is obviously something that is very basic for a lot of people, but I believe I am correct in assuming that stored procedures are an essential building block of this industry.

Hoping that you are having a great day or night.  As always, please let me know if you have any feedback.  Or just general praise.  That’s good too.

Homework Queries below, reports that resulted from it will be attached.

RC

Alter PROC SSRS_Day3Activity1

(

@CountryName Varchar (100)

)

AS

SELECT Sum(FRS.OrderQuantity) as SalesQuantity

, Sum(FRS.SalesAmount) as TotalSales

, E.FirstName+' '+E.LastName as EmpName

, E.EmployeeKey

, G.EnglishCountryRegionName

FROM FactResellerSales as FRS

Left Join DimEmployee as E

On E.EmployeeKey = FRS.EmployeeKey

Join DimReseller as R

On FRS.ResellerKey = R.ResellerKey

join DimGeography as G

on G.GeographyKey = R.GeographyKey

Where G.EnglishCountryRegionName = @CountryName

Group By E.LastName , E.FirstName , E.EmployeeKey , G.EnglishCountryRegionName

CREATE Proc SSRS_Day3Activity2

(        

@Employee int

)

AS

SELECT FRS.SalesOrderNumber

, Sum(FRS.OrderQuantity) as SalesQuantity

, Sum(FRS.SalesAmount) as SalesAmount

, E.EmployeeKey

, E.LastName

, E.FirstName

, D.FullDateAlternateKey

FROM FactResellerSales as FRS

Left Join DimEmployee as E

On E.EmployeeKey = FRS.EmployeeKey

Left Join DimDate as D

On D.DateKey = FRS.OrderDateKey

Where E.EmployeeKey = @Employee

Group By E.EmployeeKey, E.LastName, E.FirstName, D.FullDateAlternateKey, FRS.SalesOrderNumber

CREATE Proc SSRS_Day3Activity3

AS

SELECT Sum(FRS.SalesAmount) as SalesAmount

, E.EmployeeKey

, E.FirstName+' '+E.LastName as EmpName

FROM FactResellerSales as FRS

Left Join DimEmployee as E

On E.EmployeeKey = FRS.EmployeeKey

Group by E.EmployeeKey, E.LastName, E.FirstName

Having Sum(FRS.SalesAmount) > 1000000

Order by Sum(FRS.SalesAmount) desc