I Do Not See the Option to Upload Kpi in Reporting Services

Central Performance Indicator (KPI)

A Key Performance Indicator aka KPI is a metric which considerately measures the numeric equivalent of goals or targets of a company, team or an organization. Any individual or a community which has a numerical target can measure success of their goal with key performance indicators.

In all business roles and levels, if you manage a team you near likely have to prepare at least one primal performance indicator and try influence this metric to evidence a positive result or trend. KPIs should exist both logical and achievable. If not, team motivation and morale may decrease every bit a outcome of loss of self-confidence. For this reason, we have to exist careful and logical when we ready key performance indicators. At the aforementioned fourth dimension, we have to check KPIs at regular intervals to decide if they remain valid. We can illustrate a typical KPI life cycle below.

Define: The primal operation indicator should exist achievable, logical, simple and understandable.

Analyze: Nosotros take to analyze the event of key performance indicators at regular intervals and compare target and actual values.

Activeness: This is the pregnant process for primal operation indicator life cycle. In the analyzing stride, we had a basic idea about KPI target value and current value. It means that we measured the divergence between KPI target and actual values. If this difference is not acceptable, we have to take action to close the gap and achieve the KPI target value.

Review: In this step, nosotros take to review the event of analysis and actions and check the target goal of cardinal performance indicator value. This value can increase, decrease or may not change at all.

Below, y'all tin can find some KPI examples.

• Year over year growth.
• Customer lifetime value
• Render of investments (ROI)

After all these explanations virtually key operation indicators, we can mention innovate SQL Server Reporting Service primal performance indicators. SQL Server Reporting Service offers to add KPI objects directly to the web portal. In the SSRS portal we can set a lot of options for KPIs which we'll be demonstrating.

Demonstration: As a first step, we will generate a synthetic data for cardinal functioning indicator. This information includes sales per past years and sales and target of the current year. We can illustrate this sales data in this table.

Year	Sales	Target of Concluding Year
2014	77	81.six
2015	66	81.vi
2016	fifty	81.6
2017	60	81.half dozen
2018	87	81.six

The post-obit script will enable us to generate this dataset.

CREATE Table SalesPerYear ( Id INT PRIMARY KEY IDENTITY ( 1 , ane ) , SalesYear INT , Amount Bladder ) INSERT INTO SalesPerYear VALUES ( '2014' , '77' ) , ( '2015' , '66' ) , ( '2016' , '50' ) , ( '2017' , '60' ) , ( '2018' , '73' )

Before, we start to create a KPI in Reporting Service we have to create Data Source and Dataset.

Creating the Data Source

Open up the SQL Server Reporting Service home page and navigate (+) icon and so click Information Source

Fill the required fields (Name, Description) and click Enable this data source

In this step, we will ascertain Connexion and Credentials options. We will select connection type and so enter the connectedness string. Finally, fill the credentials options and click test connection.

If you go an error in this pace you have to review your connexion string and database connection credentials.

Creating a dataset

In this step, we volition create dataset to use for the KPI. The Dataset will permit usa to connect the effect of query to the central performance indicator object.

Open up the SQL Server Reporting Service abode page and navigate (+) icon and then click Information Fix

When y'all click Dataset, Microsoft SQL Server Report Builder will open up. If SQL Server Report Builder is not installed on your computer, yous can click the Become Report Builder link in the popular-up screen which appears in the Reporting Service dwelling house page and you can download Report Builder installation bundle.

We will select our data source which we defined in the data source stride and click Create.

In the Dataset console click Edit as Text.

Write or paste the query to query designer text box. Click (!) run button to test query.

SELECT * , Example WHEN KpiTarget > Amount So - i WHEN KpiTarget < Corporeality And then ane ELSE 0 END AS Sts FROM ( SELECT * , ( SELECT AVG ( Sl . Amount ) * i.1 FROM SalesPerYear Sl ) equally KpiTarget FROM SalesPerYear ) as tmp_tbl order past SalesYear ASC

After all these steps, we will salve dataset to Reporting Service.

Create KPI

Open the SQL Server Reporting Service abode page and navigate (+) icon then click KPI

In the New KPI console, nosotros tin can see some options which alter KPI visualization

Value Format: This option provides us the option to alter the format of the KPI value. In this option, we can choose currency, abbreviated or percent formats.

Value: This field identifies the actual value of the key performance indicator. This value shows us the current situation.

Goal: This field identifies target value of cardinal performance indicator.

Condition: This field compares the key operation indicator value and goal scores. It has simply three options -1 bad, 0 (neutral) and one (good). At the same time, this values changes the color of the KPI.

-1 (Bad): Reddish

0 (Neutral): Orange

1 (Good): Green

Trend Set: In this field, we can visualize irresolute of past KPI values in the trend chart. In this demo, the past year's sales amount can be viewed.

Visualization: In this selection, nosotros can enable and chose the type of trend chart.

Subsequently all these definitions we can start to create a central performance indicator.

Nosotros will choose Dataset field for Value text box and click browse button.

In this screen, nosotros will select the current value in the dataset. According to our demo data, the concluding yr (2018) refers to our current KPI value. Therefore, we volition choose Corporeality field and fix aggregation Last.

Now, nosotros will ready the Goal field. Nosotros volition choose Dataset field for Value text box and click browse push. Nosotros will cull KPITarget field. All field values are the same which is why the aggregation field can be selected at last or commencement.

Nosotros will set up the Status field. We will choose Dataset field for Value text box and click browse button. We will choose STS Field.

We will set the Trend Set field. Nosotros volition choose Dataset field for Value text box and click browse button. Nosotros will cull the Amount field.

Finally, we will choose Visualization for tendency nautical chart and click create.

After all these steps, our KPI is fix.

Now we will review part of KPI values with this epitome

1. Trend chart
2. Changing percentage of goal and value of KPI
3. Actual value of KPI
4. Status field which defines back color of KPI

Conclusion

In this article, we discussed cardinal operation indicators and how to create KPIs in SQL Server Reporting Services. Reporting service KPI'south are a user-friendly and efficient way to clarify and compare metrics in SQL Server.

References

• Working with KPIs in Reporting Services
• Introduction to Key Operation Indicators
• Report Datasets (SSRS)

• Author
• Recent Posts

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His electric current interests are in database administration and Business Intelligence. Y'all can observe him on LinkedIn.

View all posts by Esat Erkec

conleyteryiest.blogspot.com

Source: https://www.sqlshack.com/how-to-automatically-create-kpis-in-sql-server-reporting-services/

Share this post