Adhoc Reporting Using SSRS 2008 R2

By Franco Lopes
Reporting Services comes with a built-in modeling tool called Report Model Designer which is used for developing adhoc reports. Due to their ease of creation, adhoc reports are often developed by end users as opposed to developers. The main purpose of the report model is to shred the dependency on the coding and give a semantic model to the user to  develop their own reports on the fly without consulting the developer.

How to use the Adhoc Reporting Model?

The report model can be developed for an OLTP or an OLAP database using a wizard based approach. The Report model developed using the model designer is then deployed on the report server for developing the adhoc reports. The model is accessed using the Report Builder tool which is freely available for download from Microsoft. Report Builder 3.0 allows the users to develop reports not only on the report model but also on the variety of data sources. Report Builder 3.0 comes with rich visualizations including the sparklines, databars, indicators, a variety of charts and graphs and also with a the map visualization.

Building the Report Model

Now that we know what the report model and report builder is let us have look how a report model is developed from scratch and the adhoc reports are built using the same. For this exercise we will use the AdventureWorks database.
 Open the Business intelligence development studio.
1. Click File->New Project->add Report Model project->Name it as Adhoc Reporting and click Ok.

2. Add Data Source by right clicking on the data sources folder->Add New Data Source

3. Click New.

4. Specify the server name and the Database as the AdventureWorks. Click Ok to add the data source.

5. Click Next and then click Finish to add the AdventureWorks.ds as the data source to the solution explorer.

6. Add the Data Source view – right-click the Data Source views->Add New Data Source view.
Note: A DSV lets you work on the database schema to change and extend it without affecting the underlying data source. You can add custom calculations and named queries (similar to view building in the database). Using the Explore Data feature you can view the content of the table in the Visual Studio’s BIDS. Explore Data allows you to manipulate the data using OWC components.

7. Click Next.

8. In this example the data source is a relational database. Select the required tables from the database. I have selected tables for sales analysis.

9. Click Finish to complete the wizard.


10. To add Report Model, right-click on the Report Models->Add New Report Model

11. Click Next.

12. Click Next , we will use the default rules for the model generation but you can always select the required rules for model. To understand the rules in more detail click help.

13.  The first option is recommended for the report model generation rather than using the current statistics of the model. Click Next.

14. Click Run to generate the model.

15. After the model is generated click Finish to complete the wizard.

16. Now to deploy the model to the ReportServer. Right-click on the Adhoc Reporting solution and go to properties to set the ReportServer properties.

17. Right Click on the Solution and Click Deploy.

18. Open Internet explorer and type http://localhost/Reports to open the report manager. Report Manager is the Web Application provided by Microsoft to manage the deployed reports. You can see the Models and Data Sources deployed on the report server. Click on the on the Report Builder link to start the Report Builder.

Building An Adhoc Report

1. The Report Builder will be downloaded on your machine after clicking run.

2. Report Builder 3.0 will open once it has downloaded. As you can see Report Builder 3.0 gives you four options to begin report building. There is a table or matrix wizard, chart wizard, map wizard and finally a blank report. Click on the blank report for this run-through.



3. Right click the data sources and Select the option Use shared connection or report model.

4. Browse to the Models folder and select the model AdventureWorks. Click Open and then OK.

5. Right-click on Datasets and add dataset. Click Select the dataset embedded in my report and click on the query designer.

6. The below dialog box opens where you can now drag and drop the fields from the left pane.

7. I have chosen sales group, sales current year and sales last year fields to analyze. Click OK.

8. Click Ok.

9. Thus using the report model and Report Builder the following report was created in less than 5 mins.

Pareto Charts in SSRS

By Dinesh Asanka

The purpose of a Pareto chart is to highlight the most important amongst a set of factors. For example, in quality control for a manufacturer, a Pareto chart can highlight the most common sources of defects and the highest occurring type of defect.
The Pareto principle is also known as 80-20 rule, so for quality control in a manufacturing environment, that 80% of defects may be expected to come from 20% of the manufacturing issues..

Let us say we need to display the below data in a Pareto chart.
\
After creating the SSRS report, drag and drop the chart and configure it as bar chart. Then drag and drop the Model Name to the x-axis and Sales Amount to the data region.
Select the graph area or chart series (note that you need to select the bars of the graph) and then press F4 (properties). In the custom attributes, select Pareto for ShowColumnAs as shown below:

You will then be able to generate your Pareto Chart.

I think graph the individual sales items are shown as the bars, and the line is the cumulative total which shows that 80% of the sales are generated from the five best selling models.

Adding Custom Code to the Reporting Services 2008 R2

By Franco Lopes

There are numerous circumstances when we wish to add a custom function to a SSRS report in order to cater for needs of the customer which exceeds the capability of the built-in functions in SSRS. In these scenarios we will have to write our own functions. In this article I will demonstrate how to add custom code to SSRS.

Using SQL Server 2008 R2 Business Intelligence Studio

 Using Custom Code inside a report:  In this article we will consider a scenario where we want to design a KPI using custom code inside a report using the code tab of the report properties. For this we will have to write the below VB code as VB is the only language currently supported for the custom code in SSRS. Create a new SSRS project in BIDS studio and add new item as a report from the templates.
1. Create a dataset using AdventureWorks as datasource the following query:

SELECT  top 1   Name,  StandardCost, ListPrice, ListPrice - StandardCost AS ProductProfit
FROM    Production.Product
WHERE   (StandardCost >= 1000)

2. Select Report-> Report Properties from the menu.

3. Select Code tab from the report property window

4. At present only VB  supported for writing  custom code inside the report. Copy and paste the below code in the code window and click ok:

 Public Shared Function Test(ByVal profit As Decimal) As String
        Dim st As String
        If profit >= 1000 Then
            st = "High Profit"
        ElseIf profit >= 500 Then
            st = "Moderate Profit"
        Else
            st = "Average Profit"
        End If
        Return st
   End Function

5. Right-click on the textbox  and go to expressions.

6. To call the function written in the custom code window you will have to enter  Code.FunctionName . In the expression in our example it would be something like this:

=Code.Test(Fields!ProductProfit.Value)

7. The final Output should be something like this.

 Using Custom Assemblies:
The custom assemblies can be created using a class library project to create more advanced functionality for your reporting solution. The code reference for function to be used in the reporting services can be given in the references section in the report properties. To do this create a custom assembly and create a class library project called TestClass and add the below code to it:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace TestClass
{
    public class Class1
    {
        public static int Cal(int a, int b)
        {
            return a + b;
        }
    }
} 

Compile the code to generate the TestClass.dll file. To access the code inside this file we need to place this dll  in the default location of the Report Designer which is  \Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies.
To add a reference to the file inside the report perform the following steps:
1. Select Report-> Report Properties from the menu.

2. Select the Reference  tab from the report property window

3. Add a reference to the TestClass.dll by browsing to the location of the default report designer i.e \Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies

4. Add the following code in the expression of the textbox of the tablix.

=TestClass.Class1.Cal(3,4) This code calls the function defined in the assembly reference. Here you don’t need to use the Code keyword to access the function defined in the Custom assembly. You can also define an instance of the class used in the custom assembly but this is optional as you can access the function using the namespace.classname.functioname inside the expression as shown below:-
5. The output should be as follows

For the custom code to be working after deploying the report you will need to paste the testClass.dll  – i.e the dll file in the bin folder of the reporting services whose default location is
\Program Files\Microsoft SQL Server\Instance_Name\Reporting Services\ReportServer\bin
Finally, just click right on the solution and deploy it on the report server to view it in the production environment.

Design A Hybrid Report in SSRS

By Dinesh Asanka

Hybrid reports are the reports which have two types of graphs using different axes within the same graph .
For example.

In the above report, Product cost is on the right axis while Sales Amount Percentage is shown on the left axis, so that viewers can easily compare Product Cost vs Sales Amount Percentage.
Below is the query which is required for the above graph.

WITH TotalSales ( SumTotal)
AS(
SELECT  SUM(S.SalesAmount)
FROM dbo.FactInternetSales S
)
SELECT T.CalendarYear,SUM(S.TotalProductCost) TotalProductCost,
100 * SUM(S.SalesAmount)/ MAX(TotalSales.SumTotal)  SalesAmountPercentage
FROM dbo.FactInternetSales S
INNER JOIN DimTime T ON S.OrderDateKey = T.TimeKey
CROSS JOIN TotalSales
GROUP BY T.CalendarYear
ORDER BY T.CalendarYear

You can use the AdventureWorksDW sample database to get the above data.
To start the report , create an SSRS project and add a data source and a data set with the above query.
Next, add a chart control to the report. So your report should look as below:

Then drag the Calender Year to the X axis and TotalProductCost and SalesAmountPercentage to the graph area and your report should look as below:

Then select the SalesAmoutPecentage as shown in the above graph and change the graph type to Line by right clicking and selecting Change Chart Type.
You will see following configuration, if you look closely you will see that in the chart data and chart type has changed.

Right-click and select Series  Properties and you will be taken to the following dialog:

From here you can select the Secondary option from the Axes and Chart Area.
Finally, you can change the marker to complete the process.

SQL Server Reporting Services – Insufficient Rights Error

Ref: Franco Lopes

Introduction
At times when we successfully deploy the reports to the report server and try to view them through report manager we may encounter am insufficient rights error. This article focuses on resolving that error.
Solution
After deploying the reports to the report server we can view them in the Report Manager web app provided by Microsoft for managing, delivering and applying the security to deployed reports. Generally the path to the report server is http://localhost/ReportServer if it is a default instance or http://localhost/ReportServer_InstanceName  for a named instance. Similarly the report manager can be viewed at http://localhost/Reports for the default instance and http://localhost/Reports_InstanceName for the named instance.
By default the built-in Administrators group has permission on the report server to access all the reports and the data sources deployed on the report server. If you are in the Administrators group you may not come across the following error while viewing the reports
The permissions granted to user ‘domain\username’ are insufficient for performing this operation. (rsAccessDenied)
But if you are a domain user (which in most cases we are). you will have to follow the below steps to resolve the error.
1. Right-click on the Internet Explorer icon and select on Run as administrator.

2.  Type the report manager’s URL in my case http://localhost/reports
3. The report manager window opens where you can see various icons and links. Go to site settings link in the report manager.

4. Click on Security.

5. Click on New Role Assignment and add the Domain\user and depending upon the role of the user in the organization assign them the appropriate role. You can either assign the role of System Administrator or System User both have different rights. In this example I will be providing the user with the role of System Administrator.  Click Ok.

6. You will see that the user has been added to the role specified. Click on Home button.

7. Go to the Folder Settings on the Home page.

8. Click on New Role Assignment and add the Domain\user and depending upon the role of the user in the organization assign them the appropriate role. You can either assign the role of the Content Manager who can delete, modify and manage all the content on the report server or Browser who can view the reports and subscriber depending upon your need. In this example I will assign the Content Manager role.

9. You will then see the user added to the Folder Setting.

10. Close the browser which was currently running into the Administrator mode.
11. Now you can open the report manager in my case  using http://localhost/reports