May 6, 2014

about : managing-ssrs-subscriptions


So I was handed the responsibility of SSRS due to the departure of our Report Developer. I came to know that subscription reports are quite popular in the company and are heavily used across all departments. Also as it turned out there was no easy way to manage them i.e. no decent GUI based software's or service's exist according to the research that would help manage this enormous list of subscriptions.

So I thought, it can't be that hard considering all the Subscription/SSRS related information is stored somewhere on the Report Server. I then built the below query that I now use on daily basis. This query will give you the entire list of subscriptions you have setup on your ReportServer DB and many more details.

Note:
1) Don't forget to set the parameters/filters in the query
2) This query is currently only tested in SQL Server 2008 R2

Subscription Query:

USE [ReportServer]
GO

DECLARE @ReportServerURL varchar(max) = 'https://{YOUR-REPORT-SERVER-URL}'
DECLARE @ReportName VARCHAR(MAX) = null
DECLARE @To varchar(max) = null
DECLARE @Subject varchar(max) = null

SELECT c.Name AS ReportName, c.Description as ReportDescription
, 'Next Run Date' = CASE next_run_date
WHEN 0 THEN null
ELSE substring(convert(varchar(15),next_run_date),1,4) + '/' + substring(convert(varchar(15),next_run_date),5,2) + '/' + substring(convert(varchar(15),next_run_date),7,2)
END
, 'Next Run Time' = isnull(CASE len(next_run_time)
WHEN 3 THEN cast('00:0' + Left(right(next_run_time,3),1) +':' + right(next_run_time,2) as char (8))
WHEN 4 THEN cast('00:' + Left(right(next_run_time,4),2) +':' + right(next_run_time,2) as char (8))
WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1) +':' + Left(right(next_run_time,4),2) +':' + right(next_run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(next_run_time,6),2) +':' + Left(right(next_run_time,4),2) +':' + right(next_run_time,2) as char (8))
END,'NA')
, Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="TO"])[1]','nvarchar(max)') as [To]
, Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="CC"])[1]','nvarchar(max)') as [CC]
, Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="RenderFormat"])[1]','nvarchar(max)') as [Render Format]
, Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="Subject"])[1]','nvarchar(max)') as [Subject]
, Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="StartDateMacro"])[1]','nvarchar(max)') as [Start Date]
, Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="EndDateMacro"])[1]','nvarchar(max)') as [End Date]
, Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="Currency"])[1]','nvarchar(max)') as [Currency]
,[LastStatus],s.[EventType],s.[LastRunTime],[DeliveryExtension],[Version], S.Parameters, SCH.Name AS ScheduleName
, c.[Path] AS ReportPath, @ReportServerURL + '/Reports/Pages/Report.aspx?ItemPath=' + c.[Path] + '&SelectedTabId=PropertiesTab&ViewMode=Detail&SelectedSubTabId=SubscriptionsTab' AS ReportLink
, @ReportServerURL + '/Reports/Pages/SubscriptionProperties.aspx?ItemPath='+c.[Path]+'&IsDataDriven=False&SubscriptionID='+cast(S.SubscriptionID as nvarchar(500))+'&RedirectUrl='+@ReportServerURL+'%2fReports%2fPages%2fReport.aspx%3fItemPath%3d'+c.[Path]+'%26SelectedTabId%3dPropertiesTab%26ViewMode%3dDetail%26SelectedSubTabId%3dSubscriptionsTab' as SubscriptionLink
FROM dbo.[Catalog] c
INNER JOIN dbo.[Subscriptions] S ON c.ItemID = S.Report_OID
INNER JOIN dbo.ReportSchedule R ON S.SubscriptionID = R.SubscriptionID
INNER JOIN dbo.Schedule AS SCH ON R.ScheduleID = SCH.ScheduleID
INNER JOIN msdb.dbo.sysjobs J ON Convert(nvarchar(128),R.ScheduleID) = J.name
INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id
where 1 = case
when @ReportName is null then 1
when c.Name like '%' + @ReportName + '%' then 1
else 0 end
and 1 = case
when @To is null then 1
when Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="TO"])[1]','nvarchar(max)') like '%' + @To + '%' then 1
else 0 end
and 1 = case
when @Subject is null then 1
when Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="Subject"])[1]','nvarchar(max)') like '%' + @Subject + '%' then 1
else 0 end
ORDER BY c.Name


Please give it a try and let me know if you have any feedback. I'd love to make SSRS subscriptions easy to manage.

Aug 1, 2013

about : 401k-investments


P.S. - I've only enrolled once in 401k at my current employer.


Purpose: To increase returns on your 401k investments without any investing knowledge.

You may ask, how the hell can someone do that? The answer is by sharing your 401k portfolio to the community and of course without showing the actual contribution or total portfolio balance.

AssumptionsYour employer offers 401k plan for all of it's employees and this is for employees who are already enrolled or would be interested in enrolling in future.

How does it work? There are three parts to this....

1) Categories of People: There are 3 categories of people. One - who know nothing about investing, Second - who knows a little bit about investing and are semi active and Third - who are very active in investments in general

2) Sharing: Any type of online medium (see below) will give any and every employee an option to share what funds, bonds, etc. they have chosen in their portfolio, what is their allocation in percentage and what is their return since enrolling in 401k and return in last 3 or 6 months. It's up to the employee as to how they would like to share this details i.e. anonymously or using their name.

Once this is accessible online (private to the organization's employees only), anyone and everyone in the company can read the details and make suitable decisions.

3) Making Decision: Now let's say you found top 5 employee's with highest returns. You can choose two paths from here:

i. Easy approach: Just pick the employee you like the best and change your portfolio to match their portfolio. :)

ii. Not so difficult approach: Among top 5 in the list, you analyze the funds they have invested in, their returns and their reason as to why they have invested in such funds. If you agree with their approach, you can either change your portfolio to match their funds or you can mix and match from among the top 5 and balance your portfolio that way.

So for example, if an EmployeeA is new to 401k and investing world, they can take a baby step and go with the Easy approach. If EmployeeB has done 401k for some time and are looking for a decent change but not too aggressive then they can go with Not so difficult approach and take an easy path i.e. just match the highest return made by an employee i.e. the top candidate. And lets say EmployeeC who would like to be little bit aggressive but also take a safer approach then in that case, they can follow the Not so difficult approach but mix and match funds according to their needs.

I believe in sharing and if there is a way you can help someone achieve higher returns without any compromising then why the hell not. It's a win-win for all.

Online Medium: This can be a subscription based product or a cloud based software that the company pays for and keep their data private to just themselves or even a software built by your IT team as I don't imagine it being anything difficult to create.


Thanks for reading....

Jul 1, 2013

about : building-effective-tools


- Twitter launched Vine
- Facebook has Instagram
- LinkedIn integrating weather forecast & is trying to become a robust social network
- Microsoft can't stop bashing Google with different marketing tactics
- Google launched Google+ and shot down Google Reader

So why are this big corporations building such products?   Money!!!

Pretty much every single product/feature such companies launch, they do it for the sole reason of attracting customers and making them slaves of their products (free or paid). Some of those products (listed in previous blog post) are extremely useful and makes your life easier while some are just useless (at least for me).

Before launching Instagram Video, Facebook users had to either use Vine (which was still new), Vimeo or the most popular video sharing site YouTube. There is nothing wrong with YouTube or Vimeo but Twitter and Facebook both launched their own video sharing apps. Why? Just so they can their make customers stick to their product the whole time and not navigate away to a better 3rd party app.

Please stop making products that will increase customer interaction or avg. visit duration on your site but rather make something that's useful for the general public, something that will have a real world impact. There are so many areas of opportunity where you can not only make money but do something good compared to launching such useless tools. Twitter rather than launching Vine could make a paid platform to share tips about agriculture or any such area, help government reduce poverty, help companies by analyzing their twitter feeds, etc. Rather than launching balloons to provide internet, Google should use their massive data and help developing countries do better & grow faster, help wind turbines go to next level and produce steady and consistent clean energy, etc.

Don't become another $100 billion empire, keep launching helpful products & even if you stay as a $50 million company, I think it is worth it.

I do get the fact that competition is healthy but in certain cases, it is a lame excuse. And I also realize that for any company to grow, you need Money but at the same time there are other better ways to earn revenues than creating something that already exists in a great form.


This are just my views.....   Thanks for reading!!

Jun 19, 2013

about : free-products


I've realized that I use a lot of free services and I thought it'd be nice to appreciate all of them.

Free services I use:

Pandora: An amazing music recommendation service. Believe me, I've tried Grooveshark, iTunes Radio, Rhapsody, MOG and few indian radio services but I haven't found anything like Pandora. It just works. Thanks Pandora.

Hulu: Hulu in it's free version is quite amazing especially for replacing your cable television. I'm a long time Netflix subscriber and the combination of Hulu, Netflix and YouTube is all I've ever needed. I don't miss watching TV at all. Thanks Hulu.

Twitter: I was very jaded at first about Twitter and it's intentions but after using it for several months, I've come to realize that it's currently the most powerful medium to share anything and everything. If you follow the right people, Twitter can become the best resource to consumer information in the market i.e. even better than CNN, Google News or NY Times. Thanks Twitter.

PriceBlink: A browser extension that treats itself like a shopping assistant. Whatever product you are buying, this extension appears automatically on top of the web page and gives you the best price available for that product across many websites and if any coupons that are available. It has saved me lots of $$. Thanks Guys.

Clipix: Organize your life! Clipix is an online service for marking articles, images, tutorials, etc. for later access. After using Evernote/Read It Later/Instapaper, I've realized that Clipix works like a charm. Thanks Team.

Mint: One of the best tools available in the market for personal finances. It helps you set budgets, goals and recommends ways to save money. I've been a customer since day one and still loving it. Best tool for keeping all your finances in one place.

Credit Karma: An online service that provides credit scores for free. It keeps track of your credit scores and alerts you of any bad activities or changes. Also it provides some features for personal finances like Mint app. I'm a big fan of this tool as I can keep track of my credit scores easily and without any fees.

Google (Gmail, Reader, Analytics, etc): What can I say about Google... It is the king of all. Gmail is my only email client. Reader has been my only news feed. Analytics and Maps is one of the best free products out in the market. There is no comparison as to how much insights Analytics provides for free. I can keep naming more services offered by Google but I think you get the point. Big Thanks.

Notepad++ & Sublime Text: A simple utility but so damn helpful. The basic notepad tool that comes preloaded into Windows can't do much but Notepad++ has become very useful for editing txt, csv or even programming files and same applies for Sublime Text. Thanks Guys.

And many more....


Thanks to everyone involved in making this products amazing for everyone. Your determination and dedication seems to have paid off very well. I can't thank you all enough!!

Jun 14, 2013

about : rewards


Majority of the companies in America at least provide rewards in one way or another. No matter what, where or how you buy a product, a customer looks for a coupon and/or some type of a reward to either bring down the cost of the product or collect points for future discounts. The economics of loyalty program has grown so much that it's difficult to run a business without it. Customer always wants something in return or else they will go to your competitor who provides better rewards. There is some kind of a satisfaction when you get a reward in return even if you can't use it right away. Similarly companies have rapidly integrated Gamification into their products which increases customer retention by tempting them with lots & lots of rewards they will hardly ever use.

Types of rewards: Loyalty Rewards (e.g. cash, points or gift cards), Airline RewardsHotel RewardsEmployee Rewards, etc.


My point: 

The business of rewarding a customer is fantastic (I secretly love it). Although one thing that I wish existed was a whole different suite of rewards. For example, Helping Children, Supporting Families, Improving Communities, Fulfilling emotional needs, etc. etc. I'm sure getting cash in return is amazing and will make you happy but imagine how much satisfaction you'll get and how many good deeds you'll get to do if you contributed that cash or just your time for above such rewards. Spend a day with an autistic kid, Recycle your electronics and help communities too with recycling, Help communities create programs for not wasting food etc etc. Now if only there was an easy way to be part of this.



"Technology is ruling our lives & We can't seem to find time for others"


Thanks for reading...