Topic: ‘SQL Server’

When developing SQL Reporting Services Reports, you surely are aware of the checkbox to allow the “Page break at start” option when grouping data. However, you cannot allow a user to toggle whether or not they want a page break after each group. I didn’t like the idea of creating a second report that had the Page break option checked, so here is another solution starting with an existing report.

Our starting point is a simple Contact report that groups Contacts by the Account name. The initial report layout would look something like the one below. Please note that the group we currently have does not have the “Page break at start” option checked.

The first thing we need to do is to create a report parameter for the user to set as they please. Click the Report menu and select Report Parameters. Create a parameter similar to the one below. Click the Add button and name it “PageBreak.” Set the Data type to “Boolean” and enter a Prompt of “Put a Page Break after each group?” Set the Default Values to “Non-queried” and type in “False” without any punctuation. Click OK.

Next, we need to add another group above our existing group. This group will contain a formula based on our parameter and display the page break or not. Right click the left most cell of the header row (left of the cell showing FullName) and choose Insert Group. Enter values as in the screen shot below.

Name it PageBreak, Expression value is: =IIF(Parameters!PageBreak.Value,Fields!SomeField.Value,”")

Make sure to check the “Page break at start” checkbox. Lastly, click the Sorting tab and be sure to sort on Account Name (=Fields!accountidname.Value). This should be the same sorting as on the original group in your report.

Your report should now look like the screen shot below. The new group is highlighted.

Now, preview your report. You will see your Page Break parameter at the top of the report defaulted to false. Take a look at the page count.

Now, change the parameter to True, and click View Report. You will notice the page count will increase quite a bit.

That will do it.

“Microsoft SQL Server® 2008 contains a variety of features that, when implemented properly, can improve the performance of a Microsoft Dynamics® CRM 4.0 implementation and secure the data within that deployment….”

Click on the link below to navigate to the Microsoft Download Center and download the white paper:

http://www.microsoft.com/downloads/details.aspx?FamilyID=b5bb47a4-5ece-4a2a-a9b5-5435264f627d&DisplayLang=en

This one-day interactive workshop teaches you the basics on SQL Server Reporting Services (SSRS) reports. This course will teach you how to create and deploy SSRS reports. This course is for SQL savvy report writers and other technical personnel running on Microsoft Dynamics CRM 3.0 and higher and SQL 2005.

Here’s a link to the class:

http://www.ibisinc.com/SSRS_CRM_Class_Description.html

A SQL guru in our office put together this document on how to setup an IFD with Exchange Server installed:

1. Install the IIS resource kit.

2. Navigate to the CRM4IFDtool.exe

3. Set the tool to these settings.

4. Set Authentication to IFD+OnPremise

5. Add the local subnets that will not be using external addressing.

6. Set the external to https and app root to external name:plus crm port

7. Set the internal app root name: plus port. Find the site ID that you want to add the certificate to. IISweb.vbs /query “name of site using http name.” Look at the first column (“default web site <w3svc/1>. The 1 tells you the ID number for this site. Enter it in the command below “/S:#”

8. Then create a self-signed certificate.

9. Open All Programs, IIS resources, self-signed. Type selfssl.exe /N:CN=”certname” /K:1024 /S:1 /P:443

10. This will add the certificate to the server’s web site.

11. This does not put the certificate in the trusted store for the machine.

12. Open cmd line and type mmc.

13. Select File, then select Add/Remove Snap-in.

14. Select ADD and Certificates.

15. Then select ADD, Computer Acct, Next, Finish, Close, and OK.

16. Expand certificates to trusted store, certificates.

17. Now open IIS Manager.

18. Right click on the CRM site and select Properties. Select Directory Security, Server Certificate. You will go into a wizard mode (you are going to export the certificate to add to trusted certificates).

Select Export the current certificate and NEXT. You can select any path as long as you can get to it from the machine. Set a short password and mark key as exportable. Next, click Finish.

19. Import the certificate to the trusted store. Right-click the certificate folder. Select All Tasks, and then select Import.

Select Next and Browse. Change file type to .pfx. Select File and OPEN.

Click on NEXT and type in short password created from export, NEXT. Mark key as exportable. The Trusted Root Certificate store s/b selected, NEXT and Finish.

21. Close MMC you do not have to save the console, and close IIS.

22. If the above example is exact and you are using port:555 for application then you will need to have network admin allow the port through the firewall to this server

23. Add a public dns name to their external zone (ex: crm.XXXXX.com)

24. Test the application. Have a user open https://crm.XXXXX.com:5555

Take the scripts from this document, and create a SQL job to run nightly after a backup to ensure optimal SQL performance.

Rebuild Indexes

For SQL Server 2005:

a) Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

b) In the Connect to Server dialog box enter the server name and select Windows authentication credentials, and then click OK.

c) In the Database Selection menu, select the Microsoft Dynamics CRM database that you want to run this script against.

d) In the Query window, type the following commands:

On the toolbar, click Execute Query. The results appear in the results pane.
De-Fragmenting Indexes with a Script

For SQL Server 2005:

e) Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

f) In the Connect to Server dialog box enter the server name and select Windows authentication credentials, and then click OK.

g) In the Database Selection menu, select the Microsoft Dynamics CRM database that you want to run this script against.

h) In the Query window, type the following commands:

Update Statistics: