Categories
SSRS

Parameter caching in SSRS

This week I ran into strange issue. Out of no where my Who Is report stopped working. To be exact, InfoDB API stopped working ! With little digging I found that it needs to have format=xml not XML !! For some reasons InfoDB guys just decided that it has to be in lower case, but then again I can’t complain about it because it is free and you don’t expect much out of free service Open-mouthed smile

Error

Categories
How To SSRS

Setting up Email Subscriptions in SSRS

One of many things that I liked very much for SSRS over Crystal Reports is “Report Subscription”. At work we use Crystal Reports as our primary reporting system. And to provide scheduled report deliveries via email we have designed our own little .NET application that allows users to create schedule and manage automated report deliveries via email. But if it was SSRS that whole thing can be just removed and we will not need to worry about that application anymore (and this was one of my WIIFM presentation when I was showing why we should atleast consider working with SSRS) in addition to better management of reports. I think both Crystal Reports and SSRS have pros and cons, but I am not going to discuss them here because I use both and I am OK with both of them.

This post is about Configuring SSRS to setup email subscription for Reports. This will allow uses (or DBAs) to schedule automated report delivery via email. Before anyone ask, let me say that for this demo I’ve used SQL Server 2012 dev edition but this feature is not changed from previous versions.

First step to configure SSRS to use SMTP server. And just like almost all MSFT products you cant use basic authentication for SMTP. Which simply means you can’t use external SMTP servers (say Gmail or Yahoo). But fear not … you can use IIS and create Virtual SMTP server for the purpose. Actually my last post was exactly about how you can use GMail for virtual SMTP server.

Categories
Reporting SSRS

MAPS in SSRS

In SQL Server 2008 R2 Microsoft introduced really cool new feature … MAPS. With that user can use their existing data on top of maps to have better visualization of information (say customer base distributed across USA). What’s really good about this feature is user can use maps from maps gallery, or spatial data or ESRI (Environmental System Research Institute) shape files, hell you can even add bing maps background on top of it ! In short I think maps add one more tool in BI arsenal Smile

For this demo, I have used VS 2010 and SQL Server 2012 for generating map report. I have my little table based on AdventureWorks 2012 which I am using as data source for this report. First step is create new report project in VS. And add new report, create new database connection (for data source) and a query (for data set).

[sql] select StateProvinceCode,count(1) as TotalCustomers from tablebar
where countryregioncode = ‘US’ group by StateProvinceCode order by StateProvinceCode [/sql]

My above query gets count of customers per state and sorted by state. So this report basically shows count of customers located in each state of USA. Once that is done, its time to design the report. All you need is to drop map control in empty report area. As soon as you drop map control in report, it will start layer wizard. This wizard is used to set different settings of map, like map type, data source for map, layout etc.

 

1 step_2 step_3
Categories
SSRS Tips

WHO IS … SSRS Report

I know it sounds grammatically incorrect … it should say WHAT IS SSRS REPORT ? But this title is very intentional. It just says that WHO IS SSRS REPORT, means “Who Is” report in SSRS. “Who IS” is a query that is used to find details of registration information of any given public IP address.

Recently I helped our network guy in creating a security report. We were using user access log stored in database to generate some sort of simple report to display what / who / how connected to our system using VPN. This report also included an external IP of users who connected using VPN. Today I was just thinking about creating something cool in SSRS, and it came to my mind …. how cool that will be if you can just click on IP of user and get more details of it … like location ?? Or name of City/State ?? I know it is not so easy to get accurate information about users IP because most of the time ISPs have some remote location which is being used as gateway (bare with me, I am not an expert of this topic) and due to that user IP are usually not so accurate (or TOR lovers like me).

Ok, so about report. Actually it is pretty simple report with just an extra kick. For this demo, I created a table which was using following query,

[sql] CREATE TABLE [dbo].[loginLog](
[userId] [varchar](100) NULL,
[userIP] [varchar](100) NULL
) ON [PRIMARY]
— Some dummy data
— These IPs are of Google’s
Insert into loginLog values(‘User1′ ,’66.102.0.0’)
Insert into loginLog values(‘User6′,’66.102.0.0’)
Insert into loginLog values(‘User2′,’66.102.15.255’)
Insert into loginLog values(‘User4′,’74.125.0.11’)
Insert into loginLog values(‘User’,’209.85.128.90′)[/sql]

Categories
How To SSRS

Template for Report … Unifying report layouts

Most of the companies use some sort of unique format for their reports and this layout is kept same for all of their reports to create some sort of identity or brand. And for that developers usually use some kind of template to create default layout of report, and most of all reporting systems supports creating these kind of templates including SSRS. Actually it is very easy to create a report template which can be used for all other reports.

In this “How To”, I am creating a basic report template for report which once finalized can be used  for any future use.

First step is to fire up VS2008 and create new report server project. Then add an empty report in it. Next step is to modify report the way want in our template. For sake of simplicity, I am just adding some basic fields. But in reality, we can add number of things including company logo as images.

Once it is done, all you have to do is to browse to

Categories
ASP.NET Reporting SSRS

Crystal Reports in .NET

Ok, after surviving 5 days without electricity I guess I am ready for new post. We had great storm last week and it caused state wide power outages. Some cities still don’t have electricity yet !! Anyways, this post is about creating basic report using Crystal Reports in ASP.NET

I think Crystal Report has been part of Visual Studio from beginning of time. And it has been very popular reporting tool due to its simplicity and flexibility (compared to SSRS). I personally think that it is more easy to work with compared to SSRS but it still misses features like report management and control. So I like to work with both of them Smile.

With VS 2010, MSFT has changed it strategy. They still support CR, but they also has begun to promote SSRS. When you install VS 2010, it doesn’t install CR runtime components, so you will need to download it from SAP website (both Runtime and Controls).

Ok, so I begin with creating empty website project for ASP.NET. And then add web form using add new item. Then add report viewer control to page. Next step is strange but required in order to avoid error. Once control is added to page, close the project and open it again. With surprise you will be presented with upgrade wizard for your crystal report to upgrade to newer version !! If you look at CR, reference in page before upgrade is,

CR_upgrade_Screen

<%@ Register Assembly="CrystalDecisions.Web, Version=10.5.3700.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"
    Namespace="CrystalDecisions.Web" TagPrefix="CR" %>
Categories
Reporting SSRS Visual Studio

Developing reports in BIDS using Report Wizard

In my last two posts about reports, I explained how can we develop & deploy reports using Business Intelligence Development Studio (BIDS). But I think that even though I titled it as 101, it was not as easy as it sounds. Because there is even easier way exists to create a report that is by using Report Wizard. This wizard guides user at every step and they can create simple report with few clicks and even without being much “expert” at it. In today’s post, I will explain how can we use Report Wizard to create report.

Before doing anything, we need to create new reporting project from BIDS section of VS 2008. And select Report Server Project Wizard. Once we enter name and location of project and clicks OK, a wizard appear and thus our journey begins.

NewProject ReportWizard_1

First step is to create datasource for our report, which is basically connection to database.