Indicators in SSRS

SQL Server Reporting Services provides several ways to analyze the data. Indicators are minimal gauges that convey the state of a single data value at a glance and are mostly used to represent the state value of Key Performance Indicator (KPI, a measurable value which has business significance with a specific target or goal that indicates whether things are going good or bad). Indicators can be used in either dashboards or free-form reports but more commonly are used in tabular or matrix reports to visualize data in rows or columns. 

Step by step implement Indicators in SSRS.

Here I am using AdventureWorksDW2008R2 data base and want to implement indicator based on the number of product sales unit. My sql query is below

select P.EnglishProductName,Count(FIS.ProductKey)as [Number of sales Unit] from FactInternetSales FIS Inner join DimProduct P ON P.ProductKey=FIS.ProductKey Group by P.EnglishProductName

Now open reporting service project.

Create new Report.

Create new data source.

Now create data set using the above sql query.

Click ok.

Now in report design insert a table having three columns as below.

In 3^rd Column we need to insert Indicator.

Click ok. You will get the following window

According to you requirements you can select the indicators. Click ok.

Select Indicator properties.

You will get below screen.

In general tab you can write the name of the Indicator. If you want to write the tool tip you can.

Now select Value and States Tab

A.     Select the value for the indictor.

B.    Select the Measurement unit either % or Numeric.

C.    Set the Icon, Color Minimum values and maximum value for the Icon

If you want to add more icon click on the add

In place of icon you can put your image for that you need to click on the Image button and browse the image.

Similarly for removing the icon you need to click delete button.

If you want to perform any action select Action tab.

D.   Click ok.

Now you can review the report you will get the desirer report.

 

Executing SSIS Package by using C# windows application

Sometimes we need to call/execute an SSIS Package through a C# application. This is just a small example for how we can Call/Execute an SSIS Package through a C# windows application.

Here I am going to create a package. I took Script task and want to display simple message through it.

Step by step calling SSIS package in C# application 

I have written simple message in script.

Save and click ok.

Now execute package.

Task Executed successfully.

Now we need to deploy the package. Right click on Solution explorer and select properties.

Select Deployment Utility form the Configuration Properties and select CreateDeploymentUtility as True.

Click apply and ok.

Now build the application. Deployment file has been created on bin\deployment.

Package has been created successfully.

Now we can call this package in c# application (Windows, web or console)

Open Visual Studio 2012.

Select Fileà New àProject.

Write the project name and select the project folder path.

Click ok.

Take a button on windows form.

Double click on it.

Before writing the c# code we need to add a Microsoft.SQLServer.ManagedDTS.dll in the reference.

We will do not get this dll in .net assembly. You can get this assembly on below path

“C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies”.

Click on the Add Reference.

Write the simple code in button click.

private void button1_Click(object sender, EventArgs e)         { Microsoft.SqlServer.Dts.Runtime.Application myApplication = new Microsoft.SqlServer.Dts.Runtime.Application();                 Package myPackage = myApplication.LoadPackage(@"F:\SSIS test\ SSISPackegedemoToCallin.net\SSISPackegedemoToCallin.net\bin\Deployment \Package.dtsx", null); lblStatus.Text = "Executing package"; DTSExecResult myResult = myPackage.Execute(); // Show the execution result lblStatus.Text = "Package result: " + myResult.ToString();         }

Now compile the code and execute it.

Click button.

Package executed successfully.

Enjoy hope this post will be helpful to you. Shortly I will post how to call parameterizes package and how to call store procedure.

Gauges Report in SSRS

The Gauge data region is a one-dimensional data region that displays a single value in your dataset. An individual gauge is always positioned inside a gauge panel, where we can add child or adjacent gauges. We can use the gauge panel to create multiple gauges inside a single gauge panel that share common functions such as filtering, grouping, or sorting.

We can use gauges to perform many tasks in a report:

Ø  Display key performance indicators (KPIs) in a single radial or linear gauge.

Ø  Place a gauge inside a table or matrix to illustrate values inside each cell.

Ø  Use multiple gauges in a single gauge panel to compare data between fields.

There are two types of gauges: radial and linear. The following illustration shows the basic elements of a single radial gauge in the gauge panel.

Step by Step creating Gauge report

Create data source connection

Create new data set. User this query. I am using AdventureWorksDW2008R2 database.

select p.EnglishProductName, Sum(SalesAmount)as [Total sales] from FactInternetSales f Inner join DimProduct p on P.ProductKey=f.ProductKey Group by  p.EnglishProductName

Report design we take table

In next column we are inserting Gauge.

Select the Gauge type. There are two type of Gauge

Ø  Redail

Ø  Linear

Here I am selecting Redial

Select Gauge data set Values.

Select the Gauge properties.

Write the Gauge name if you want to set the tool tip give the value for it.

Now select Gauge scale properties.

Here you can set the scale value and interval. You can also define the layout and action, Major and minor tick marks, borders etc.

Now view in browser. You will get this report.

Generating Table script and insert script of the table in SQL server

Generating table script in sql server

declare @vsSQL varchar(8000) declare @vsTableName varchar(50) select @vsTableName = 'FactInternetSales' –-Table Name select @vsSQL = 'CREATE TABLE ' + @vsTableName + char(10) + '(' + char(10) select @vsSQL = @vsSQL + ' ' + sc.Name + ' ' + st.Name + case when st.Name in ('varchar','varchar','char','nchar') then '(' + cast(sc.Length as varchar) + ') ' else ' ' end + case when sc.IsNullable = 1 then 'NULL' else 'NOT NULL' end + ',' + char(10) from sysobjects so join syscolumns sc on sc.id = so.id join systypes st on st.xusertype = sc.xusertype where so.name = @vsTableName order by sc.ColID select substring(@vsSQL,1,len(@vsSQL) - 2) + char(10) + ')'

Generating insert script

/****** Object:  StoredProcedure [InsertGenerator]     Script Date: 1/18/2015 10:20:16 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [InsertGenerator] ( @tableName varchar(100) ) as --Declare a cursor to retrieve column specific information  for the specified table DECLARE cursCol CURSOR FAST_FORWARD FOR SELECT column_name,data_type FROM information_schema.columns  WHERE table_name = @tableName OPEN cursCol DECLARE @string nvarchar(3000)  --for storing the first half of INSERT statement DECLARE @stringData nvarchar(3000)  --for storing the data (VALUES) related statement DECLARE @dataType nvarchar(1000) --data types returned for respective columns SET @string='INSERT INTO '+@tableName+'(' SET @stringData='' DECLARE @colName nvarchar(50) FETCH NEXT FROM cursCol INTO @colName,@dataType IF @@fetch_status<>0       begin       print 'Table '+@tableName+' not found, processing skipped.'       close curscol       deallocate curscol       return END WHILE @@FETCH_STATUS=0 BEGIN IF @dataType in ('varchar','char','nchar','nvarchar') BEGIN  --SET @stringData=@stringData+'''''''''   --+isnull('+@colName+','''')+'''''',''+' SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+' +'''''+''''',''NULL'')+'',''+' END ELSE if @dataType in ('text','ntext') --if the datatype is text or something else BEGIN       SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+' END ELSE IF @dataType = 'money' --because money doesn't get converted from varchar implicitly BEGIN       SET @stringData=@stringData+'''convert(money,'''''' +isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+' END ELSE IF @dataType='datetime' BEGIN             SET @stringData=@stringData+'''convert(datetime,'+''' +isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+''''' +''''',''NULL'')+'',121),''+'   --                         END ELSE IF @dataType='image' BEGIN       SET @stringData=@stringData+''''''''' +isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+' END ELSE --presuming the data type is int,bit,numeric,decimal BEGIN             SET @stringData=@stringData+''''+''' +isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''', ''NULL'')+'',''+' END SET @string=@string+@colName+',' FETCH NEXT FROM cursCol INTO @colName,@dataType END DECLARE @Query nvarchar(4000) SET @query ='SELECT '''+substring(@string,0,len(@string)) + ')  VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')''  FROM '+@tableName exec sp_executesql @query --select @query CLOSE cursCol DEALLOCATE cursCol ---------------

Execute the stored proc.

Exec InsertGenerator FactInternetSales*

*Table name