Start Documenting Your Day! Why Now?

I read a post about documenting your day by Steve Jones, here is where culture insists i write a few words about the person i refer to. But you don’t expect that for Steve. Do you? 😦

In this short post, I’ll present my view on why you should document your day and why now is the right time.Document your day

For most of us the job we do is more than just a pay check. It’s fun. It’s a sense of achievement. And documenting your accomplishments will only help you get better at what you do.

We all make new resolutions every year. Personal experience tells me that most of them are never visited. Documenting your every day is very hard and can be one of those that will stay in the parking lot for ever.

There are only a couple of weeks left in this year. So if you start now, you can find out if you can enjoy doing this and if this can help improve your productivity. And once you figure this out there is no going back.

Happy documenting!

~Sam.

New string function in SQL Server 2012 – FORMAT()

Formatting numbers in an SSRS report is a common task. For example, you may want to format a number as currency or percentage.

You can select a format from the number page of the properties window.

image

You can let sql handle the formatting, so data in the result set is pre-formatted.

DECLARE @Sales MONEY = 32182000.85;

SELECT ‘$’
+ CONVERT(VARCHAR(32),@Sales,1);

Results:

image

Finally, you can use the newly introduced FORMAT() function in SQL Server 2012. Format() will, according to books online, return a value formatted with the specified format and optional culture. So, instead of converting and concatenating like we did in the previous example, FORMAT() can be used:

DECLARE @Sales MONEY = 32182000.85;

SELECT FORMAT(@Sales,‘c’,‘en-us’);

Results:

image

FORMAT() accepts the following parameters:

  • Value. Actual value that needs to be formatted.
  • Format. Value will be formatted to the specified format. Currency, percentage, and date are few examples.
  • Optional Culture. Specifies the language. More about cultures on BOL.PARSE()

Consider the following query. Value is formatted to three different languages based on the culture:

Formatting Currency:

DECLARE @Sales MONEY = 32182000.85;

SELECT FORMAT(@Sales,‘c’,‘it-IT’) [Italy]
, FORMAT(@Sales,‘c’,‘fr’) [France]
, FORMAT(@Sales,‘c’,‘ru-RU’) [Russian];

Results:

image

Formatting percentages:

DECLARE @Per DECIMAL(2,2) = 0.72;

SELECT FORMAT(@Per,‘p0’,‘en-us’)
,
FORMAT(@Per,‘p2’,‘en-us’);

Results:

image

Conclusion:

Similar formatting is ideally done in the presentation layer, reporting services for example. But I would want to let reporting services do minimal processing. FORMAT() simplifies string formatting. It provides functionality that most developers have always wanted.

Aaron Bertrand (blog), MVP, wrote a more detail article on this topic. Don’t forget to check that out.

~Sam.

SSAS Processing Mode (Lazy Aggregations)

Processing mode property of a partition/measure group determines how partitions will be available to users.

Processing mode has two possible options.

  • Regular. This is the default setting. When set to regular, partitions will be available to users after data has been loaded and aggregations are created completely.
  • Lazy Aggregations. When set to lazy aggregations, partitions will be available to user queries immediately after data has been loaded. Aggregations will be created as a separate background process while users start to query the partition.

clip_image002

For those of you who likes technical lingo, Process Full will internally run Process Data and Process Index before the partition can be queried. If processing mode is set to Lazy Aggregations, partition will be released for user queries after Process Data is completed. Process Index will be executed in the background.

Faster availability of the partition comes with a price. Since aggregations don’t exist while users begin to query the partition they may experience slow performance.

~Sam.

Create Sparklines in SSRS

When creating a SSRS report, you want to add lines that display trends. Sparklines that were introduced in SQL Server 2008 R2 will allow you to do this easily. But wait there is more. You want to show trends for more than one data point. For example actual sales and target amount. And you want to conditionally format the data point. Show colors green or red depending upon if sales exceeds target.

Below is how the Sparkline with multiple data points and conditional formatting applied to them will look like. Columns represent sales by month and line represents sales quota by month. Column is in green when sales exceeds the quota for a given month and red when sales fall below the quota.

image

Sparklines are simplified versions of charts that can be used to display trends. Different types of Sparkline charts include line, area, bar, etc.

Sean Boon (Blog) described how to build Sparkline reports in an earlier post. If you haven’t read that post yet I highly recommend you do so. I am going to use the same example that Sean used in his post but show a different functionality.

Create dataset:

After opening report server project and creating a data source, create a data set. Right click on shared datasets and choose add new dataset. Following is the query I am using for this example which runs against AdventureWorksDW2008R2 sample database.

SELECT T.CalendarYear,
T.CalendarQuarter,
T.MonthNumberOfYear,
SUM(S.ExtendedAmount)AS Sales ,
COALESCE(SUM(Q.SalesAmountQuota )/ COUNT(SalesAmountQuota),
SUM(Q.SalesAmountQuota )/ COUNT(SalesAmountQuota))/3 AS Quota ,
E.FirstName + ' ' + E.LastName AS Employee
FROM
FactResellerSales AS  S
LEFT OUTER JOIN dbo.dimDate T ON S.orderdatekey = DateKey
JOIN dbo.DimEmployee E
ON S.EmployeeKey = E.EmployeeKey
LEFT OUTER JOIN dbo.FactSalesQuota Q ON
S.EmployeeKey= Q.EmployeeKey AND
T.CalendarYear = Q.CalendarYear AND
T.CalendarQuarter = Q.CalendarQuarter
WHERE S.EmployeeKey IN
(SELECT TOP 10 EmployeeKey FROM FactResellerSales GROUP BY factResellerSales.EmployeeKey ORDER BY SUM(ExtendedAmount) DESC)
GROUP BY T.CalendarYear ,T.CalendarQuarter,T.MonthNumberOfYear, E.FirstName, E.LastName, E.EmployeeKey
ORDER BY Employee, 1

Insert table:

We want to insert a table grouped on Employee so one row for each employee is shown. Sparklines will later be added to this table.

  • Drag and drop table report item from the toolbox to report body.
  • Drag Employee field from dataset and place it in the details row.

image

  • Right click on the employee text box and insert a row group by choosing row group, group properties. In the group properties window choose Employee from the drop down for group on property.

image
image

Insert Sparkline:

We will configure a Sparkline with two data points, apply conditional formatting, and add Sparkline to the above table.

  • Drag and drop Sparkline from toolbox on to the report body. Select column as the Sparkline type and click ok.
  • Add sales, Quota from dataset to values and CalendarYear, CalendarQuarter to category groups.

image

  • Click on Quota from values and choose change Sparkline type. From select Sparkline type page, choose smooth line and click ok.

image

  • Click on Sales from values and choose series properties.

image

  • From the series properties page, choose fill and type the following expression.=iif(Fields!Sales.Value<Fields!Quota.Value,
    “red”,”green”)

Finally drag and drop Sparkline to  a cell next to Employee in the table we created earlier.
image

Leave a comment with any questions.

~Sam.

#SSRS error – An item with the same key has already been added

Here is a simple yet annoying error I ran into while creating a dataset. Below is the query that I was using and it would run just fine when executed from management studio.

SELECT
CalendarYear Yr
, CalendarMonthNum Mth
, CalendarMonthName Mth
FROM
dbo.Orders o

But SSRS doesn’t like those two columns with the same name “Mth” resulting in the error “an item with the same key has already been added”.
To resolve this simply make sure that no two columns in your dataset have the same name.

~Sam

Column alias in order by and where

If you ever wonder why column alias can be used in order by and not in where clause, it’s the logical query processing order. Pinal Dave blogged about the complete order here.

SELECT
Name n, GroupName g
FROM  HumanResources.Department D
WHERE g = 'Executive General and Administration'

In the above query, from clause is executed first followed by where clause, and select goes last. So where clause is trying to use a column g which doesn’t exist until select completes execution and thus resulting in a “invalid column g” error.

SELECT
Name n, GroupName g
FROM  HumanResources.Department D
ORDER BY g

But order by in this above query is executed at the end after select has created a column g.

And order by lived happily ever after >:]

~Sam.

BI Pyramid (read categories of users in a BI solution)

Purpose of any business intelligence solution is to provide users information and enable them to make intelligent decisions that benefit their business. But one size solution doesn’t fit all.

Depending on their job duties and where they fall in the management chain, users can be categorized into at least three groups. A successful BI solution will come in different flavors to fulfill the requirements of these different users.

A pyramid resembles the users in an organization.

Operations: Draw two imaginary lines horizontally across the pyramid dividing it into three sections. The bottom section is the widest, this can be attributed to a group of users that are responsible for day-to-day operations, running business as usual (BAU). These users will be interested in transactions and detail data. Report requirements from these users are predictable, predefined and standard. Predefined or canned reports from components like SSRS, Excel are created for this purpose.

Analysts: The next level up is a group that can be called data analysts or middle management. Identifying trends, detail business analysis, strategic decision-making is carried out by this group. They usually start at a place, drill down into more details, slice and dice the data across different areas. Report requirements from this group are unpredictable. Ad-hoc reporting capabilities are to be provided to them by using tools like report builder, excel pivot tables, power pivot for excel.

Executives: Last but not the least top section of the pyramid represents the key group of users that make executive decisions. Users like sales managers, execs. They are highly interested in summary information like yearly sales amount, total items sold etc., In addition these users can’t spend a lot of time to go through all the reports and get what they want. Dashboards will provide executives the ability to get results fastly.

In summary

Canned Reports for Operations
Ad hoc reports for Middle Management
Dashboards for Executives

~Sam