Stored Procedure QUOTED IDENTIFIER, ANSI NULLS and NOCOUNT

While working on stored procedures in SQL Server I came across a few keywords which are not commonly used while writing queries: QUOTED IDENTIFIER ON/OFF, ANSI NULLS ON/OFF and NOCOUNT ON/OFF.

I had created a new stored procedure (by right clicking on Stored Procedures folder in left pane of SSMS and then “New Stored Procedure”). I tried working on it, but my curiosity wouldn’t let me finish without figuring out what these settings are do and whether they are important.

1) QUOTED IDENTIFIER ON/OFF

The syntax is:

[code lang=”sql”]SET QUOTED_IDENTIFIER ON[/code]

or

[code lang=”sql”]SET QUOTED_IDENTIFIER OFF[/code]

.

When SET QUOTED_IDENTIFIER is ON (default), all strings delimited by double quotation marks are interpreted as object identifiers.

When SET QUOTED_IDENTIFIER is OFF, literal strings in expressions can be delimited by single or double quotation marks. If a literal string is delimited by double quotation marks, the string can contain embedded single quotation marks, such as apostrophes.

You can read more about it on http://msdn.microsoft.com/en-us/library/ms174393.aspx.

2) ANSI NULLS ON/OFF

The syntax is

[code lang=”sql”]SET ANSI_NULLS ON[/code]

or

[code lang=”sql”]SET ANSI_NULLS OFF[/code]

.

For stored procedures, SQL Server uses the SET ANSI_NULLS setting value from the initial creation time of the stored procedure.

Whenever the stored procedure is subsequently executed, the setting of SET ANSI_NULLS is restored to its original value and takes effect. When invoked inside a stored procedure, the setting of SET ANSI_NULLS is not changed.

When this is ON, any query that compares a value with a NULL returns a False.

When OFF, any query that compares a value with a NULL returns a NULL value.

You can read more about it on http://msdn.microsoft.com/en-us/library/aa259229(v=sql.80).aspx.

3) NOCOUNT ON/OFF

The syntax is

[code lang=”sql”]SET NOCOUNT ON[/code]

or

[code lang=”sql”]SET NOCOUNT OFF[/code]

.

When this is OFF, it will display the count of rows Inserted/Deleted/Updated.

For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

You can read more about it on http://msdn.microsoft.com/en-us/library/ms189837.aspx.

Identify Missing IDENTITY Values in SQL Server

There are 3 ways of identifying the missing identity values in a table containing an IDENTITY column:

  1. Sequentially through the table until we reach the maximum value.
  2. Using OUTER JOIN with a temporary table that contains all possible values.
  3. Using a user-defined table-valued function.

Before showing how these three methods work, we’re going to need some data with missing identity values.

[code lang=”sql”]CREATE TABLE #CarType (
[ID] INT IDENTITY,
[Name] VARCHAR(20) )

INSERT INTO #CarType ( [Name] ) VALUES ( ‘Bentley’ )
INSERT INTO #CarType ( [Name] ) VALUES ( ‘BMW’ )
INSERT INTO #CarType ( [Name] ) VALUES ( ‘Ferrari’ )
INSERT INTO #CarType ( [Name] ) VALUES ( ‘Lamborghini’ )
INSERT INTO #CarType ( [Name] ) VALUES ( ‘Hummer’ )
INSERT INTO #CarType ( [Name] ) VALUES ( ‘Jaguar’ )
INSERT INTO #CarType ( [Name] ) VALUES ( ‘Lexus’ )
INSERT INTO #CarType ( [Name] ) VALUES ( ‘Mercedes Benz’ )
INSERT INTO #CarType ( [Name] ) VALUES ( ‘Porsche’ )
INSERT INTO #CarType ( [Name] ) VALUES ( ‘Volvo’ )

SELECT * FROM #CarType

— Let’s say certain records have been deleted from the table, as shown in the following script:
— Step #2: Delete some IDs

DELETE FROM #CarType WHERE [ID] IN (3, 4, 9)

SELECT * FROM #CarType[/code]

IDENTITY Values Sequentially Through a Table

In this method, we loop through the table starting from 1 up to the maximum identity value.

[code lang=”sql”]DECLARE @ID INT</pre>
DECLARE @MaxID INT
DECLARE @MissingCarTypeIDs TABLE ( [ID] INT ) — The missing identity values are temporarily stored in a table variable for later processing.

SELECT @MaxID = [ID] FROM #CarType

SET @ID = 1
WHILE @ID <= @MaxID
BEGIN
IF NOT EXISTS (SELECT ‘X’ FROM #CarType
WHERE [ID] = @ID)
INSERT INTO @MissingCarTypeIDs ( [ID] )
VALUES ( @ID )

SET @ID = @ID + 1
END

SELECT * FROM @MissingCarTypeIDs — The output of the SELECT statement shows which are the deleted identity values.[/code]

Using a Temporary Table

We can create a one column temporary table that contains all possible values of an identity column and then compare the temporary table with the actual one.

[code lang=”sql”]DECLARE @IntegerTable TABLE ( [ID] INT )
DECLARE @ID INT
DECLARE @MaxID INT
/* The first part of the script is the population of a table variable (@IntegerTable) that contains one column (ID),
which will hold all possible values from 1 to the maximum identity value of the table (@MaxID).
The second part of the script is the joining of this table variable with the table being determined the deleted identity values.
Since the table variable contains all possible identity values, a LEFT OUTER JOIN is used and the deleted identity values are identifed by checking for NULL IDs from the table (WHERE B.[ID] IS NULL).
*/
SELECT @MaxID = [ID] FROM #CarType

SET @ID = 1
WHILE @ID <= @MaxID
BEGIN
INSERT INTO @IntegerTable ( [ID] )
VALUES ( @ID )

SET @ID = @ID + 1
END

SELECT A.*
FROM @IntegerTable A LEFT OUTER JOIN #CarType B
ON A.[ID] = B.[ID]
WHERE B.[ID] IS NULL[/code]

User-Defined Table-Valued Function

We can create a table-valued function that will return a table containing the same data as the table variable used above and then OUTER JOIN the two to find the missing values.

[code lang=”sql”]DECLARE @MaxID INT

SELECT @MaxID = [ID] FROM #CarType

SELECT A.*
FROM [dbo].[ufn_GenerateIntegers] ( @MaxID ) A LEFT OUTER JOIN #CarType B — create a user-defined table-valued function(ufn_GenerateIntegers) and then use it there.
ON A.[IntValue] = B.[ID]
WHERE B.[ID] IS NULL[/code]

Report Development in SSRS vs. X++ for Dynamics AX 2012

Last week’s discussion with one of our Business Intelligence clients, prompted me to document the following questions and responses for the benefit of many organizations who fall in to the following classes:

  • Companies planning to implement Microsoft Dynamics AX 2012.
  • Companies planning to develop, modify or migrate reports into Microsoft Dynamics AX 2012.
  • Companies wanting to leverage SharePoint infrastructure that they have in place.

Why is developing reports in SSRS better than developing/modifying reports in the current format (using X++ in AOT)?

The main advantages of developing reports in SSRS are:

  • Report performance is better in SSRS than in X++.
  • Future change requests and report development is less expensive on SSRS than X++. So it is better to transition to SSRS now.
  • SSRS reports can be published to SharePoint that can be used by users without AX 2012 licenses saving on license costs.

Will these SSRS reports be available inside AX?

The SSRS reports will be available inside specific AX modules along with other out-of-box reports just like with the old X++ reports.

Is it possible to add report links to other ERP systems into AX? Can users access these reports without navigating out from AX.

The answer depends on the ERP system, but generally, yes.

Reconciliation reports, which take data from more than one application, can be embedded into the AX Module and can be accessed by the users in the same manner as other SSRS reports. This is possible as the report connects with the databases of the various applications. If the data in the other application database is correct then the report will show the correct information.

Is it possible to publish the same report in SharePoint so that users who do not have access to AX still run the report?

A report copy can be published to SharePoint, but there will be 2 copies of the .rdl report file. The same file cannot be configured to run in AX and SharePoint as they both have different report servers.

Is it possible to provide specific access of the SSRS reports published in AX to end users?

Yes, users can get specific access to SSRS reports. Depending on the access to the specific module in AX, users can view and run the report(s) published in that module.

We would be happy to sit with you to demonstrate our capabilities and identify where you can save significant costs in the reports development projects. Feel free to contact us.

PowerPivot for Excel Sample

One of our clients needed a single report that served the needs of several different users each with slightly different requirements. In addition to satisfying different needs, the report also needed to select and filter on different columns. Finally, the report had to be fast.

We proposed two solutions, a robust and extensible data mart and data cube, and a quick and dirty solution using PowerPivot for Excel which we will cover here.

The end-users analyze the data using Microsoft PowerPivot extension of Microsoft Excel. The extension allows for drag and drop pivot table analysis very similar to the data mart and OLAP data cube.

However, the variety of the attributes that can be used for analysis is strictly limited to the fields present in the relevant Crystal Report.

This solution entails creating a report on Microsoft Reporting Services that reuses the stored procedure that the Crystal Report is using. The report will refresh regularly and provide a data source for PowerPivot.

The processing and analysis of the data happens on end-user workstations within the PowerPivot extension of Excel.

The features of this solution are:

  1. The solution would, unfortunately, not work for large datasets as there will be performance issues associated with reading and processing large amounts of data on end-user workstations.
  2. Excel 2010 is required with a free PowerPivot extension installed. This is in contrast to the data mart solution that works with Excel 2007 and higher.
  3. The solution can’t be easily extended or reused to cover slicing and dicing along new dimensions, attributes or measures in the subdomain.
  4. Extending the solution to a wider variety of dimensions or measure may result in more effort than is required for the data mart and data cube solution
  5. The solution is prone to introducing inconsistencies. Even when extreme care is taken in extending the solution, it is difficult to maintain complete consistency between many report data sources.

PowerPivot Architecture

SSRS reuses queries to gather data in to Excel where it can be moulded in to a usable report.

powerpivot-architecture PowerPivot for Excel Sample

PowerPivot Schema

The PowerPivot data reuses existing report queries limiting the ways that you can pivot the data but still providing useful options.

powerpivot-schema PowerPivot for Excel Sample

Output Samples of PowerPivot for Excel

sample-outputs PowerPivot for Excel Sample

sample-reports PowerPivot for Excel Sample

Evaluating SSRS Report Developers for Continuous Improvement

SSRS-reporting Evaluating SSRS Report Developers for Continuous Improvement

We have a standard set of criteria that we use after any SSRS project to help our SSRS report developers evaluate each other and improve on their work.

The evaluation criteria are:

  • Always keep your audience in mind.
  • Thoroughly capture report requirements before writing queries.
  • Make upfront effort to reuse design.
  • Use and reuse datasets.

Keep your Audience in Mind

Always keep the audience or end user of the report in mind. If the end user is an accounting analyst, then the report will be multi-page and have a very detailed view of the transactions, such as line items of each transaction. If the end user is a CFO or senior management, then the report will be most likely single page.

If the audience is not kept in mind, then the report will be ignored by the end users. Including the right the level of detail in your report is a key.

Thoroughly Capture Report Requirements before Writing Queries

The challenge faced by many report development teams is they do not get the requirements in enough detail at the start of the project.

Report developers who dive right in to developing reports after receiving the initial requirements will often discover that the requirements didn’t fully capture what the end user really wanted. The result is that the developer puts in a full round of development before properly capturing the requirements and has to revisit development.

The key to solving this problem is to spend more time capturing report design requirements. Prepare mock-ups using tools like Excel before implementing the design. Get the template approved and then spend time on the query writing required to power the reports. Putting in extra effort at the start will save a lot of time and cost of report development over the long run.

Upfront Effort to Reuse the Design

Developers should always keep reusability in mind when designing reports.

Large reporting projects always present opportunities to reuse elements like templates and functions. The key is to think about reusability from the beginning.

Use and Reuse Datasets

SSRS provides ability to create and reuse datasets.

The advantage of reusing datasets is that they save time and also let you update the query in one place regardless of how often it appears in different reports across the organization. The plug and play functionality of SSRS not only saves time and cost up front, but also reduces the cost of future updates.

Conclusion

By keeping these principles in mind, we find that we are able to continually improve on our ability to deliver useful SSRS reports on time and under budget.

We have many such checklists and best practices, a few of them are:

Integrated and Native Mode SSRS Performance Comparison

In my previous blog post I shared integration benefits of SSRS in SQL Server 2012 and SharePoint. In response to the post, a reader asked about the performance of reporting services in SharePoint Integrated mode on SQL Server 2008 R2 compared with Native mode.

SharePoint Integration Mode with SSRS in SQL Server 2008 R2

Before looking at SSRS performance in Native and Integration modes, we should look at the checkered performance history of SSRS-SharePoint integrations.

The integration of the SharePoint environment with SQL Server Reporting Services (SSRS) provides many benefits such as shared security, shared storage, well-defined workflows and one-stop shop for all documents in an organizations. However, the price of this integration is paid in additional performance overhead.

Since the initial release of the integration, about seven to eight years ago, Microsoft has made significant performance improvements. The SQL Server 2008 R2 and later version with SharePoint 2010 have much better performance than older versions with better individual report performance as well as improved overall operations per seconds (throughput).

The performance hit on older versions can often be seen when running a complex SSRS report, drill-down or drill-through reports or any report with .NET coding, in parallel on SharePoint 2010 or older.

SQL Server 2008 R2 has a significant performance advantages over older versions of SQL Server. Newer versions of SQL Server are pretty close performance-wise the main difference being features like those highlighted in my last blog post.

SSRS Performance in Native and Integration Mode

To begin with, let me briefly touch on process in both.

SSRS in Native Mode:

 

SSRS-native-mode Integrated and Native Mode SSRS Performance Comparison

Steps:

  1. End user requests a report to a Reporting Service Engine.
  2. Reporting Service Engine retrieves report definition from the Report Server Catalogue.
  3. Reporting Service Engine sends query requests to the database(s).
  4. Reporting Service Engine sends the extracted report to the end user.
  5. End user sends request to Reporting Service Engine to format output (Excel, PDF etc..).
  6. Reporting Service Engine returns the report in the end user’s requested format.

SSRS in Integration Mode:

 

SSRS-integration-mode Integrated and Native Mode SSRS Performance Comparison

Steps:

  1. End user requests a report to SharePoint Web Services.
  2. SharePoint Web Services retrieves the report definition from the SharePoint Catalogue.
  3. SharePoint Web Services requests a report from the Reporting Service Engine.
  4. Reporting Service Engine queries Report Server Catalogue to compare and authenticate report definition with the report definition received from the SharePoint Catalogue.
  5. Reporting Service Engine sends query requests to the database(s).
  6. Reporting Service Engine sends the extracted report to the SharePoint Web Services.
  7. SharePoint Web Services sends the extracted report to the end user.
  8. End user sends request to SharePoint Web Services to format output (Excel, PDF etc…).
  9. SharePoint Web Services requests Reporting Service Engine to send report in the end user’s requested format.
  10. Reporting Service Engine returns the report in the end user’s requested format to SharePoint Web Services.
  11. SharePoint Web Services returns the report in the end user’s requested format to the end user.

We see that communication in SSRS in Integration Mode takes more steps than in SSRS in Native Mode and this is reflected in performance. However, the difference is microseconds.

The choice between Native and Integration mode really depends on the organization as performance isn’t much of an issue. If they prefer the SSRS in Integration Mode with improved performance, they can add more computing resources to the infrastructure.

New SSRS-SharePoint Integration Features

ssrs-orbit-sharepoint New SSRS-SharePoint Integration Features

An interesting question was asked by a prospect in the recent meeting, “What does SQL Server Reporting Services 2012 bring?” To bring some context, my prospect wants to develop some SSRS reports for their financial ERP and would like to use SharePoint as a collaboration portal.

There are many changes to SQL Server Reporting Services 2012 SharePoint integrations. The prime difference between SQL Server 2008 R2 and 2012 is that the latter is now a Windows Service Application which helps take advantage of a number of SharePoint features while providing more flexibility in deployment.

Leveraging SharePoint’s load balancing feature: SSRS now can leverage SharePoint’s load balancing features without much overhead and configuration. Fault tolerance and load balancing infrastructure are the part of SharePoint feature set for Service Applications.

Shared Services: The new architecture helps in scalability of reporting services. Reporting services can switch between running on a single server and on multiple servers depending on the load. The sharing of resources across servers become seamless with Service Applications helping with scaling.

Flexible Deployment: As a Service Application, the new version of SSRS allows for more flexible  deployment. Depending on the resources and scenario, SSRS can run either on a frontend web server or in a separate application server.

Administration Made Easy: The administration of Service Applications is integrated directly with the SharePoint architecture streamlining administration.

For more information on integrating SSRS and SharePoint,

  • watch Microsoft’s video on Configuring and Securing Complex BI Applications in a SharePoint 2010 Environment with Microsoft SQL Server 2012
  • or contact OptimusBI to see how we can help you with your setup.

 

 

SSRS Reporting in Microsoft Dynamics AX 2012

Microsoft-Dynamics-AX-2012 SSRS Reporting in Microsoft Dynamics AX 2012

Microsoft has made great changes to reporting on Dynamics AX 2012.

This latest version uses SQL Server Reporting Services (SSRS) as the primary platform for reporting. Developing in SSRS is much faster than X++ making custom reports cheaper to implement.

In addition to the use of SQL Server tools, there is an integration of the report development environment with Visual Studio 2010 making report development a lot more simple and straight-forward.

Most of the out-of-the-box reports are converted to SSRS platform.

There are two ways to design SSRS reports in AX.

  • Precision Design: created using SQL Report Designer when a precise layout is required.
  • Auto Design: automatically generated with the help of drag and drop action and selecting elements by using Model Editor.

AX 2012 also comes with the drillthrough reporting. This is a popular feature helps developers to link general reports to more specific ones and help the end users find insightful data.

The last AX 2012 feature that we are going to touch on is the interactive reports which can produce some insightful reports under the right circumstances.

Visit the report development page for more information on how OptimusBI can help you with Microsoft Dynamics AX 2012 reports.

 

Report Server Role Management in SSRS

Microsoft Sequel Server Reporting Services (SSRS) provide us with a role management functionality which helps us in enhancing its security. For every user we can define its role and accessibility in a well defined manner.

For configuring roles use Report Manager to assign users to a role. This role management works at two levels:

  • Item-level roles: Item level roles are user to manage report server content and report processing. Item-level roles are defined on the root node (the Home folder) or on specific folders or items farther down the hierarchy.
  • System-level roles: In System level roles there is no restrictions in terms of specific folder or an item .It grant access to site-wide operations. For e.g. using Report Builder and other such accesses.

Both the types are good enough to be used together. That is why, adding a user to a report server is a two-part operation. It means if you assign a user to an item-level role, you should also assign them to a system-level role. When assigning a user to a role, you must select a role that is already defined.

Before we proceed

Let us go through following list before we add users to a report server.

  • You must be an Administrator on the computer where report server is configured.
  • If you want to add more users then you must map user accounts (Administrator) to Content Manager and System Administrator roles (if not mapped by default) because only users who have “Content Manager” and “System Administrator” permissions can add users to a report server.
  • Report server can be accessed by both users with Administrator account as well as Standard User Account.

Note: “Site Settings” option (top right of the screen upon starting Report Manager) will be only visible to the user with “Administrator” Account and not to the user with “Standard” Account.

Let us begin

System role – To add a user/group to a system role

  1. First start Report Manager (SSRS).
  2. Now click on Site Settings (top right of the screen).
  3. After that click on Security.
  4. Then click on New Role Assignment.
    ssrs-role-assignment1 Report Server Role Management in SSRS
  5. In Group or user name, enter a user or group account (Windows domain) in this format: <domain name><account name>.
  6. After that select a system role, and then click OK.
    Both the roles can be selected as well, if selected; a user or group will be able to perform the tasks in both roles.
    ssrs-role-assignment-details Report Server Role Management in SSRS
  7. Repeat above steps for adding more users or groups.

Item role – To add a user/group to an item role

  1. First Start Report Manager (SSRS) if not already started.
  2. Now click on Folder Settings.
    folder-settings-ssrs Report Server Role Management in SSRS
  3. After Folder Settings click on Security.
  4. Followed by New Role Assignmentnew-role-assignment-ssrs Report Server Role Management in SSRS
  5. In Group or user name, enter a user or group account (Windows domain) in this format: .
  6. In the given example, the domain name is “Enterprise” and user name is “test1”, so in Group or username field “Enterprisetest1” is used.
  7. Select role definitions (you can select one or more) that describe how the user or group should access the item, and then click OK.
    ssrs-role-assignment-details1 Report Server Role Management in SSRS
  8. Now go back to Home and look for the report item for which you want to add a user or group.
  9. Hover over on the item, and click on the drop-down arrow.
  10. Now in the drop-down menu, click Security (as shown below)
    ssrs-security Report Server Role Management in SSRS
  11. After this click New Role Assignment. (Before that you might need to click Edit Item Security in the toolbar to change the security settings.)
    ssrs-new-role-security Report Server Role Management in SSRS
  12. In Group or user name, enter a user or group account (Windows domain) in this format: .
  13. For e.g. my machine’s domain name is “Enterprise” and user name is “test1”, so in Group or username field I have written “Enterprisetest1”.
  14. Select role definitions (you can select one or more) that describe how the user or group should access the item, and then click OK.

Repeat above steps for adding more users or groups.

How to Set Up Dynamic Grouping in SSRS

Businesses are looking for more complex and interactive reports because they help gain valuable insights. One common report requirement is dynamic grouping which allows the users to choose which fields to group their data around.

In SSRS, dynamic grouping can be set up using the following steps.

Define the grouping parameters

To enable a dynamic grouping on a set of fields, create a parameter. List the fields you want to use for grouping and add labels. If you want grouping to be optional, also include null as a value (with a label like “None”).

  1. Click on Parameters, and then Add Parameter. In the Report Parameter Properties window, set the parameter properties.
    add-parameter How to Set Up Dynamic Grouping in SSRS
  2. Select Available Values in the Report Parameter Properties window and add label and value pairs for all groups.
    set-available-values How to Set Up Dynamic Grouping in SSRS
  3. Select Default Values in the Report Parameter Properties window and enter the desired default report settings.
    default-values How to Set Up Dynamic Grouping in SSRS
  4. Click OK.

Repeat these steps for each group of fields that you wish to enable dynamic grouping.

Create the report with grouping

Create a table report with the dynamic groups you set up. Groups like month, year and category are very common.

report-grouping How to Set Up Dynamic Grouping in SSRS

Set the group expression

Open the Group Properties window and Add the desired group expression.

Here is an sample expression where we perform some basic checks on our Month, Year and Category grouping and set the default display.

iif (Parameters!Group1.Value is Nothing,1,Fields(iif(Parameters!Group1.Value is Nothing, “Year”,Parameters!Group1.Value)).Value)

The outer iif checks for empty parameters and displays the entire group if this condition is met.
Otherwise, the second iif checks if the parameter value is Nothing which means none is selected from the drop-down and then it will return the value of the Year field without using it for any sorting or grouping.

group-properties1 How to Set Up Dynamic Grouping in SSRS

 

Change the grouping display value (label) on basis of selected report parameter

The final step is to change the group label depending on the display value.

In this example, we are setting the Null value to display “Year” as the label in order to match the behaviour in the previous expression.

=Fields(if(Parameters!Group1.Value is Nothing “Year”, Parameters!Group1Value)).Value

group-display How to Set Up Dynamic Grouping in SSRS

And that is how you set up a very basic dynamic grouping. SSRS allows you to create some complex reports with sets of dynamic groupings

To learn more about OptimusBI and SSRS,