Bluewolf IT Salary Guide for 2012

February 5th, 2012 / No Comments » / by admin

Bluewolf Salary Guide for 2012 has just been released and not surprisingly, if your expertise falls into one of the ‘buzz word realms’ of IT for 2012 i.e. cloud, mobile, virtualization and big data, you’re in the money! Naturally, high strata, mananagerial positions e.g. CIO, CTO are the highest-paid on the list, averaging $200,000 per annum. Not going into details (the full report can be found here) but from BI perspective it looks like this:

  • Data Analyst and BI Professional salaries ‘will creep past pre-recession levels, rising between 5-6% annually
  • Top tier ERP, BI and CRM Developer salaries will raise from $84,000 – $105,000 to $88,000 – $110,000
  • BI Developer  with 3+ years experience (part of business applications development strand)  has recorded an average increase in salary and for 2012 this should average around $120,000 depending on which part of U.S. (Long Island, Bay Area, Boston etc.) you live in
  •  Data Warehouse Analyst together with Data Warehouse Engineer, Senior Data Analyst and BI Analyst also averaged approx. $120,000 salary (again, depends on the exact location)
  • As far as industry hot list, for healthcare, Data Warehouse Analyst took third place with $88,000 – $104,000. For retail, BI Developer was in second place with $108,000 – $121,000 and Data Architect was in fifth with $114,000 – $189,000. For financial services Data Architect was in number four with $89,000 – $114,000. BI was not considered to be a ‘hot placement’ for such industries as high tech/software and media/telecom.

I think that some of those can be taken with a grain of salt as I found some of this data strange to interpret in the context of the position description e.g. according to this report, for TriState area, Database Administrator with 1-3 years experience will have higher salary then a DBA with 5+ years of experience. Weird?!

Posted in: This-and-That

Tags:

SSRS Report Execution With Multi-Valued Parameter Through Stored Procedure – Part 2

February 2nd, 2012 / No Comments » / by admin

In the first post of this two part series I outlined some of the deficiencies related to SSRS reports execution through a stored procedure, where multivalued parameters needed to be used in a report. In this post I would like to show how you can execute a report with multivalued parameters by simply typing in individual values, separated by commas into a parameter textbox. This is just an alternative method of filtering the report content. Why would this method be any more useful than allowing checkboxes to appear next to each value in the parameter?  Well, imagine that you have hundreds of values to filter by therefore hundreds of checkboxes to tick. Scanning through all the values to select only the ones applicable to what we want to filter by would be very time-consuming and frustrating. Additionally, this method can be used as a search interface for values which we are not completely certain about. All we need to do is to list them one by one and if the data related to the value is found in the source then it will be displayed in the report output, otherwise it will not come up after the report generation.

To demonstrate it hands-on, let’s create a simple dataset and populate it with some dummy data using the below script.

CREATE TABLE [dbo].[Table_1]
(
	[Item_SKU] [int] NULL,
	[Item_Category] [varchar](50) NULL,
	[Item_Price] [money] NULL,
	[Item_Price_Bracket] varchar (50)
)
ON [PRIMARY]

GO

DECLARE @Item_SKU int
SET @Item_SKU = 1
WHILE @Item_SKU <1001
	BEGIN
		INSERT INTO Table_1
			(Item_SKU, Item_Category, Item_Price, Item_Price_Bracket)
			VALUES
			(@Item_SKU, CASE WHEN @Item_SKU<100 THEN 'Category A'
							 WHEN @Item_SKU>=100 AND @Item_SKU<200 THEN 'Category B'
							 WHEN @Item_SKU>=200 AND @Item_SKU<300 THEN 'Category C'
							 WHEN @Item_SKU>=300 AND @Item_SKU<400 THEN 'Category D'
							 WHEN @Item_SKU>=400 AND @Item_SKU<500 THEN 'Category E'
							 WHEN @Item_SKU>=500 AND @Item_SKU<600 THEN 'Category F'
							 WHEN @Item_SKU>=600 AND @Item_SKU<700 THEN 'Category G'
							 WHEN @Item_SKU>=700 AND @Item_SKU<800 THEN 'Category H'
							 WHEN @Item_SKU>=800 AND @Item_SKU<900 THEN 'Category I'
							 ELSE 'Category J' END,
			@Item_SKU + 0.99, NULL
			)
	SET @Item_SKU = @Item_SKU + 1
END

GO

UPDATE Table_1
SET  Item_Price_Bracket = CASE WHEN Table_1.Item_Price < 500 THEN 'Price Bracket 1'
						  ELSE 'Price Bracket 2' END

When finished, the output should look like the table below (partial view only).

Now, let’s say I want a simple report that in a table format will display all the data contained in the table we have just created but filtered by only certain Item_SKU numbers. Instead of creating a multivalued parameter which will contain no fewer than 1000 rows, we can input multiple ‘Item_SKU’ numbers separated by commas. The only deficiency to this method is that the user will need to know the values for this parameter, rather than relying on the system to generate the filter list. In order to achieve this through stored procedure we can create a temp table within a SP and pass the parameter values from the report into the SP statement as per below.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE   PROCEDURE [dbo].[SP_Select_data_from_Table1]
(
--This variable will hold all our Item_SKUs
@Item_SKU varchar(100)
)

AS
--DROP (if exists) AND CREATE TEMP TABLE TO HOLD SKUs SELECTED BY THE USER
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'#Temp_SKUs'))
DROP TABLE #Temp_SKUs
CREATE TABLE #Temp_SKUs (SKUs varchar(100))

--DECLARE SQL STATEMENT AND INSERT SKUs
DECLARE @SQL as varchar (1000)
SET @SQL = 'INSERT INTO #Temp_SKUs (SKUs) Select Item_SKU from Table_1 WHERE Item_SKU IN'
SET @SQL = @SQL + '(' + @Item_SKU + ')'
EXEC ( @SQL )

SELECT * FROM Table_1 WHERE Item_SKU IN (SELECT * FROM #Temp_SKUs)

This piece of SQL is pretty much self-explanatory – we pass the multiple values that the user want to filter by into a temp table created on the fly and then use them in the final ‘SELECT’ statement to get the ‘Table_1’ table data ‘WHERE’ values are in the previously created temp tables which again links to the SSRS parameters. Clean and easy! The report output is as per the image below where the values, separated by commas, are typed into the blank text box and then the report content filtered as per those.

Please also visit the first part to this post which can be found HERE.

Posted in: How To's, SQL, SSRS

Tags: , ,

Older Entries
Newer Entries