Tip - SSRS Time Format functions for converting to am/pm

Dear Friends,

Sometime back I was working on few standalone SSRS reports which uses time fields from AX 2009 database like for e.g. smmActivities table and the time here is stored in form of integer values.

How to convert int field into time (am/pm)

=Format(TimeSerial(0,0,Fields!starttime.Value),"hh:mm:ss tt") - 12 hour clock
=Format(TimeSerial(0,0,Fields!starttime.Value),"HH:mm:ss tt") - 24 hour clock

Troubleshooting Linked Server Error The OLEDB provider MSDASQL for linked server reported an error. The provider ran out of memory

Dear Friends,

Some months ago, I was working on a SSRS Report which sources data from a third party Oracle database. I had shared my experiences in this post http://daxdilip.blogspot.com.au/2013/03/ssrs-troubleshooting-error-im014.html

I have a Linked Server connection from my SQL box which will talk to the Oracle db and do a nightly refresh of the local data. Now, the source database (oracle db) got upgraded and some of the tables/fields were changed.

Error: I got this error in one of my data transfer jobs.

Solution:

You can refer to the Microsoft KB Article over here http://support.microsoft.com/kb/268520

Re-create the linked server to use the Microsoft OLE DB Provider for ODBC (MSDASQL).

• Use the ODBC driver for ORACLE that is provided by Microsoft:
  
  

  EXEC sp_addlinkedserver @server = 'ORACLEODBC', @srvproduct = 'MSDASQL', @provider = 'MSDASQL',
   @provstr = 'DRIVER={Microsoft ODBC for Oracle};SERVER=MyOracleServer;UID=USERNAME;PWD=Password;'
  go
  
  sp_addlinkedsrvlogin 'ORACLEODBC', false, NULL, 'USERNAME', 'Password'

SSRS Troubleshooting ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

Dear All,

I realized this post was sitting in my drafts folder for a while now. This is not going to be an AX post but an interesting experience on troubleshooting SSRS Configuration connecting to an external database.

For one of my customer, I have developed a series of SSRS Reports to extract data from a third party provider - Intersystems, http://www.intersystems.com/trakcare/index.html who specializes in HealthCare Information Systems.

To give a bit background, Intersystems uses Oracle database for their healthcare systems and inorder to extract data from their systems, I had to create a Linked Server connection in SQL Server which talks to Intersystems ODBC Driver (DSN). The IT team at my customer end had installed the 3rd party ODBC Driver on the box where SSRS was installed.

I was able to connect to their database using BIDS and the report rendered without any issues. However, when I publish the SSRS report to Report Server and then try to browse the report via report URL, it throws out this error "ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application”


Solution:

Since we are using SSRS reporting we would need both 32-bit and 64-bit ODBC drivers of Intersystems as In SSRS, the Report Manager and Report server are 64-bit components and BIDS Environment (Development Studio of SSRS) is 32-bit.

After few hours of research on this error and by going through the forums and InterSystems website, it seems like they have a 64-bit ODBC Driver available. The 32-bit driver which we are currently using is the version released in 2008.

I downloaded the 64-bit Cache 2008.2.6 from their FTP site  and installed it on our SSRS box ftp://ftp.intersystems.com/pub/cache/odbc/ and this fixed the error

About SysListPanelRelationTable Class in Dynamics AX

Hi there!

Recently for one of my clients, I built a custom SSRS Security Maintenance form for their Financial Reporting.

The requirement was to built a custom security form wherein the users were mapped to Financial Dimensions (also known as Programs in our client's terminology)  and the security should flow into SSRS Reporting so that the programs are automatically filtered based on the user's login.

Ok, so the main point I want to highlight here is when I was developing this form, I leveraged the class SysListPanelRelationTable in AX. You can have a look in AOT/Forms/Tutorial_ListPanel to see how the class is implemented. The other example of this class implementation is Standard AX User/User Groups form in Administration menu.

So, in my implementation I used the same class, however the tables I used were different i.e my own custom table and standard Dimension Table, The key here to understand is the table and field relationships while passing them as parameters to the newForm constructor

Troubleshooting SSRS Report Error - An item with the same key has already been added

Error:

While building one of the SSRS Reports, I ran into this error "An item with the same key has already been added" while loading my datasets from Visual Studio.

Solution:

Thanks to this blog, it came to my rescue.

SSRS Tips and Tricks Recursive Sum of Fields

Dear All,

A while ago, I posted on Conditional Summing Group Totals in SSRS


Here's another small but useful tip if you ever want to do a recursive sum then we have an optional parameter in the SUM function

Syntax :

Sum(expression, scope, recursive)

 
Example :
Sum(Fields!ORIGINALFORECAST.Value,"PROJID",Recursive)

Read more here in this msdn article
 

SSRS Tips and Tricks - Conditionally Summing Group Totals

Dear All,

Hope you are doing good. It's been a while since I have written some posts. I have lots of things to write about, but just not able to manage my time. Have been busy working on different areas:
a. Installing and configuring Sharepoint 2010 Setup with SQL Server 2012
b. Configuring SSRS 2012 Sharepoint Integrated Mode
c. Dynamics AX and Faxing
d. Configuring Remote Blob Storage in SQL Server 2012

I will be soon covering these areas in different posts as I have learned quite interesting things in my journey.

However, thought to start with a small tip on one of the powerful functions in SSRS Reporting. E.g. My requirement was to sum a group total conditionally.

So, we can use a conditional iif() within a sum() within an expression as shown below:

=Sum(iif(Fields!Status.value = "ValueA", Cdbl(Fields!Payment.Value), 0.0))

SSRS Format Date tips

Hi there!

Recently, I was working on a SSRS Report wherein I need to use the Format function of SSRS for date formatting.

e.g. Format(Parameters!Date.Value,"dd-MM-yyyy")

Note here, if you use "mm" (lowercase), then it won't produce the proper results, as it's case sensitive and we need to pass "MM" (uppercase)

I found the below link useful which describes more on Format function in SSRS - (A good read)
http://thavash.wordpress.com/2007/04/10/working-with-dates-in-reporting-services/

SQL Server CTEs and Recursive Parent Grouping in Advanced Mode Group Properties in SSRS

Hello!

Few weeks ago, I was developing an interesting report in SSRS which pulls out data from AX - Projects module.

It was interesting for 2 reasons:

1. I learned to use more effectively SQL Server CTE's (Common table expressions) while developing this report.

From a layman's perspective, CTEs were introduced since SQL Server 2005 and it’s more useful to do a recursive search (display hierarchical data e.g. employee-manager, BOM etc). In my scenario, I had to do a recursive search for Project-Parent Id in my Stored proc)

Below sharing some links, which will explain further more in detail about CTE's and how to implement them.

http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

http://msdn.microsoft.com/en-us/magazine/cc794278.aspx

2.  The second concept was about recursive parent Grouping (in SSRS)

In SSRS, we can group the child field based on the recursive parent key (There’s an option in the Edit Group > Advanced Section to key in the Recursive parent field) – explained here in this blog - http://sql-bi-dev.blogspot.com.au/2010/08/recursive-hierarchy-group-in-ssrs-2008.html

Troubleshooting SSRS Report Manager Error Unable to connect to server

Been a while since I did my last post. It has been a hectic last month for me personally and professionally (involving some travel)

Issue

Some weeks ago, I was troubleshooting a Report Manager Error on one of our build boxes. The Report Server url works fine and I can get to the reports, but when I try to browse the Report Manager URL, it throws me this error "Unable to connect to server".

In our topology, we have multiple reporting instances configured on the same build box.

Solution:

I did some googl-ing but couldn't find a solution specific to my issue.

Next step was to delve into the SSRS Reporting log file (C:\Program Files\Microsoft SQL Server\MSRS10.TEST\Reporting Services\LogFiles). After a while I figured out that calls were made to an ip address which was no longer valid.

Then, I had to go to Reporting Services Configuration (Start > Programs > Microsoft SQL Server 2008 R2 > Configuration Tools > Reporting Services Configuration) and remove the invalid ip address mappings from both Web Service  and Report Manager URL Sections. That fixed the issue!

How to Configure Kerberos Authentication for SQL and MOSS

Apart from AX, In my current role, I have been extensively working on MOSS/Sharepoint Administration and Troubleshooting, Configuring Kerberos Authentication for SQL, MOSS Server and SSRS, Planning for Disaster Recovery, Troubleshooting SSRS.


This post is on my experience on setting up Kerberos for our standalone MOSS, SQL and SSRS Servers and troubleshooting IIS for Kerberos.

1. Server Farm Topology

TEST-DEV2 Server: MOSS Server 2007 Farm  (Primary) – Central Administration, IIS

TEST-DEV1 Server:


a.   SQL Server 2008 Database Engine, SSRS 2008 (Sharepoint Integrated Mode)

b. MOSS WFE (Web Front End Part of Server Farm configured on TEST-DEV1)

2. Setup Kerberos for SQL Server (TEST-DEV1)

Before we setup Kerberos for Sharepoint Server, we need to configure SQL for Kerberos

a. Register SPN’s for SQL Server Service (one  with NETBIOS and other with FQDN) as shown below

Setspn -a http/<computer-name>.<domain-name>:<port> <domain-user-account>

Setspn Command line Reference - http://technet.microsoft.com/en-us/library/bb735885.aspx

2. Test  the connectivity on SQL Server is via Kerberos and not NTLM

Logon to SSMS (staying in the same box) and fire the below SQL, if SPN’s are properly setup, then you should see KERBEROS in auth_scheme field.

               

3. Another way to test if Kerberos is being used as Authentication scheme is to bring up Central Administration site and now go back to TEST-DEV1 (the host box where SQL is running). Bring up the Event Viewer (check Security tab)

4. In the Security Log, you should be able to see Success audit record for Logon/Logoff category event

Check the Detailed Authentication information and should be able to see the Login mechanism as Kerberos which confirms our test that MOSS is communicating with SQL via Kerberos

3. Setup Kerberos for MOSS Server (TEST-DEV2)

Register SPN’s for MOSS Server (Service account) in the similar fashion as we did for SQL:

1. Register with NETBIOS name
2. Register with FQDN name as shown below

3. Browse to Central Administration website and confirm by going to the Event Viewer of the hostmachine in Security log, (similar to the steps we performed for SQL) that the authentication package here is Kerberos

TROUBLESHOOTING:

A.

Troubleshooting IIS Authentication HTTP Error 401 after Kerberos is setup for MOSS Server Farm

Note: If you are running IIS 7.0 server in a Web farm the KDC will not know in advance which individual server the request may go to and hence ticket decryption may fail

Bring up IIS Manager, Go to each of your websites and turn off the Kernel Mode Authentication under Windows Authentication Advanced Settings option

OR

Let Kernel mode authentication be enabled and the Application pool's identity be used for Kerberos ticket decryption. The only thing you need to do here is:

1. Run the Application pool under a common custom domain account.

2. Add this attribute "useAppPoolCredentials" in the ApplicationHost.config file.

<system.webServer>

  <security>

     <authentication>

        <windowsAuthentication enabled="true" useKernelMode="true" useAppPoolCredentials="true" />

     </authentication>

  </security>

</system.webServer>

Remember there is no GUI setting for this. You need to modify the ApplicationHost.config file from

<%SystemDrive%>/Windows/System32/inetsrv/config folder on the IIS 7.0 machine.

Note:
Tried both the options but Option A works fine with Option B, there’s this frequent system crash as I reckon it’s changing the applicationhost.config file settings. Need to check this behaviour in Production Server as Option B is more performance friendly and recommended approach

B.

Configuring Kerberos for SSRS (running in Sharepoint Integrated Mode):

1. Register SPN for SSRS Server Instance  (both with NETBIOS and FQDN)

Setspn -a http/<computer-name>.<domain-name>:<port> <domain-user-account>

Reference - http://technet.microsoft.com/en-us/library/cc281382.aspx

2. Open the RsReportServer.config file and locate the <AuthenticationTypes> section. Add <RSWindowsNegotiate/> as the first entry in this section to enable Kerberos

3. Test: Deploy a Report through BIDS to  a Report Center site, http://test-dev2/rc/

Troubleshooting tip: You may run into error rsaccessdenied: The account SHarepoint/System doesn’t have permissions to deploy the report. In order to troubleshoot this error, check if spn’s are properly registered with the sharepoint service account and there’s no duplicity of SPN’s.

Step by Step Guide How to Install Dynamics AX 2012 SSRS and Analysis Extensions

This is the 3rd part in the series - Step by Step Installation of Dynamics AX 2012 Components:

Part 1 - Basic Dynamics AX 2012 Installation http://daxdilip.blogspot.com/2011/08/step-by-step-guide-how-to-install.html

Part 2 - Dynamics AX 2012 Enterprise Portal Installation http://daxdilip.blogspot.com/2011/08/how-to-install-dynamics-ax2012.html


INSTALL REPORTING EXTENSIONS
Run Pre-Requisite Validation

Go to the Link below and Download Cumulative Update 3 for SQL Server 2008 R2
http://support.microsoft.com/?kbid=2261464










Troubleshooting Tip (SSRS Errors)

Error during Install of AX 2012 SSRS Extensions

Microsoft.ReportingServices.WmiProvider.WMIProviderException: The profile for the user is a temporary profile. (Exception from HRESULT: 0x80090024)   at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.ThrowOnError(ManagementBaseObject mo)

   at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.ReencryptSecureInformation()

   at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ReencryptSecureInformation()

An error occurred during setup of Reporting Services extensions.

Reason: Unable to restore the SQL Server Reporting Services encryption key. The operation failed with error code 2148073508

Solution

a. Restart the box

b. In SQL Server 2008, SSRS Reporting Configuration Manager, Under Encryption Keys, Restore the key

c. Generate Backup encryption key for the service account

d. Check you are able to browse Report Server and Report Manager URL

e. Now, Install SSRS Extensions again, it should work

Deploy Reports (Powershell)



Wait for a few mins to populate the variable, it will return back to the PS Prompt
TIP - You can also output to a file by appending out-file as shown


803 SSRS reports out of the box
Publishing reports
Note – takes around 45 mins to deploy (with some warnings which can be ignored I guess)

INSTALL/CONFIGURE ANALYSIS SERVICES

1. Install SQL Server Analysis
2. Restart Server

Troubleshooting Dynamics AX 2012 SSRS Extensions Install - An Error occured during Reporting Service Extensions

Error during Install of AX 2012 SSRS Extensions

Microsoft.ReportingServices.WmiProvider.WMIProviderException: The profile for the user is a temporary profile. (Exception from HRESULT: 0x80090024)   at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.ThrowOnError(ManagementBaseObject mo)

   at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.ReencryptSecureInformation()

   at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ReencryptSecureInformation()

An error occurred during setup of Reporting Services extensions.

Reason: Unable to restore the SQL Server Reporting Services encryption key. The operation failed with error code 2148073508

Solution

a. Restart the box

b. In SQL Server 2008, SSRS Reporting Configuration Manager, Under Encryption Keys, Restore the key

c. Generate Backup encryption key for the service account

d. Check you are able to browse Report Server and Report Manager URL

e. Now, Install SSRS Extensions again, it should work

Troubleshooting Dynamics AX 2009 SSRS Report Data methods displaying #Error

I had to modify a custom Dynamics AX SSRS  Report to use Data Methods and whenever I preview the report, it was giving me the below #Error text for data methods.











Solution
Note, that Reporting tools is here on my local machine and we have SSRS Installed on a different box. I did the usual routine of building the SSRS Project from Visual Studio and then going to AX > Reports Library node and then right-clicking the custom report library to deploy it.

After some minutes of troubleshooting, found that the custom model dll was not being deployed on the SSRS Report directory (/bin folder). Rest all the dll's were in place, so copied this dll manually from my local machine and placed it in the server's SSRS path. Now, the values started displaying properly.

Out of the below dlls, as mentioned above the model dll was not getting deployed on the server.

RCMSO.Model.dll
SharedLibrary.BusinessLogic.dll
RCMSO.BusinessLogic.dll
SharedLibrary.Model.dll

Troubleshooting Dynamics AX 2009 SSRS Report Generation Error

None of the SSRS reports were rendering correctly. This was working well before the Xmas break.

Error:

An error has occurred during report processing.
The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040) 

Solution:

As you see from the error above, it's not much helpful and sort of misleading as I thought it could be the SSRS components which got installed in GAC may have got corrupted. After some troubleshooting, found that the Validate button on Administration > Setup > Reporting Services > Reporting Servers was not validating the datasource folder properly. Then, performed the following steps to rectify it.

a. Recreated the reporting data source and folder and now the Validate button started functioning without any issues.

b. Restarted SSRS Service from the Control Panel > Services window (As without restarting I was getting the same dll error) to make it work.

Deciding on Reporting Architecture (SSRS + Dynamics AX 2009) for a new Implementation

A few days ago, we were brainstorming on the reporting architecture for a new implementation.

We had the following options on the table:

 

1. SSRS fetching data through EP

Pros: 

•  Ax Security (Configuration key, Security key etc.) set at table levels can be leveraged 
•  No bypass of Business Logic as the communication is through AOS
•  Leverage data methods in Reports  

Cons:

• Tight integration of Reporting IDE with AOT
• AX Resource need to spend time learning SSRS
• Additional components such as EP and Role Centers needs to be configured

2. SSRS fetching data from Ax Database using Stored Procedure

 Pros:

 

• Ax team and SSRS team can simultaneously work on Data and Report design resp
• Additional component such as EP does not have to be installed 
• It provides an option of writing logic inside Ax or in SQL store procedure
• Stored Procedures provide faster access to data
• Reports can be loosely coupled and extendable say tomorrow if you want to extract data from another data source say for that matter SAP, you need to just write one more SP inorder to do that

 

 Cons:

•         Internal AX Security is bypassed as SP directly access the data
•         In case of an upgrade, the metadata or the field names used in SP needs to be modified if they change    

3. AX Client

         Client was not interested in using AX thick client as it will be tedious job to install AX Client on each PC just for reports purpose and then tomorrow if the client is upgraded, then again the setup has to be done on each PC. Client preferred web based UI.

 

So, our vote was for the second option as it's loosely coupled and provides flexibility to have multiple datasources