Blog - Step 2 – Setting up Visual Studio for your first report

Step 2 – Setting up Visual Studio for your first report

Sep8

Written by:Kbase Q-Service
9/8/2010 11:07 AM

Open Visual Studio 2008.
You may be prompted to select your Default set of settings. You may select any option you wish here.
Select File –> New Project from the drop down menus.
Select Business Intelligence Projects from the Project types list.
Select Report Server Project
Enter a name and location for your project.
Click OK when you are done.
On the right hand side you will find the solution explorer. This area will list the reports currently in your project and allow you to add\create new ones.
Right click on project name (in this case “MyReports”). From here you can do two things.
1. Add –> New Item : Create a new report from scratch.
2. Add –> Existing : Add an existing report to the project. Use this when downloading and editing existing reports from Microsoft CRM.
For the purposes of this tutorial we will create a new report so select Add –> New Item
Select Report from the installed templates. and enter a name for the report in the area provided.
Click Add
A blank report with that name will be created and added to the project.
To begin creating our report we firstly need to connect to the sql server.
Select View from the Visual Studio drop down menus and click Report Data.
The Report Data pane will pop up. Click the Pin icon on the top right hand corner of the pane to fix it to your visual studio window.
Click New –> Data Source.
The Data Source properties dialog will appear. Click Edit to open the Connection Properties Dialog.
Enter the server name (and instance) or select it from the drop down list.
You should be connecting over Windows Authentication but if its necessary select SQL Server Authentication.
Select or enter a database name.
Your CRM database name will be in the format OrganizaionName_MSCRM.
Click Test Connection to confirm all details are correct. If so then click OK.
Optional: You may give the Data Source a name by entering one into the name box.
Click OK again to accept the Data Source properties.
A Data Source item with the name eg: DataSource1 will appear in the Report Data pane
Right click this and select Add DataSet
Optional: Enter a name for the DataSet by entering one into the name box.
Click Query Designer to create your sql statement.
If the designer does not look like the screen below then click the “Edit as Text” button on the top left hand corner.

This tool will allow you to retrieve the data you require for your report by various means.
The first section allows you to add and remove tables\views from your query by right clicking in the space.
The second section once you have added a table will allow you to select columns and define sorting and filters.
The third is the actual sql statement. You may enter the statement here manual if you so which.
The fourth and final section will display the retrieved data when you click on the run button (!) on the toolbar.
For you purposes we will enter the following SQL Statement.

SELECT name
FROM FilteredAccount AS CRMAF_FilteredAccount
ORDER BY name

This statement will retrieve the name of every account and order it alphabetically.

NOTE: We use the Filtered views (in this case FilteredAccount) in place of actual tables as there have been created specifically by Microsoft for this purpose. They collate all entity fields in one table\view and also take security roles into account so as not to display data in the report that a user would not otherwise have elsewhere.
Click the ! button and you should see something similar to the screen below.
Click OK to accept the query.
Click OK again to create the Dataset.
You will now see the Dataset and its field(s) on the Report Data pane.
To list the accounts in the report we will now need to add a table.
Select View from the Visual Studio drop down menus and click Toolbox.
Double click or drag the table onto the report.
Select View from the Visual Studio drop down menus and click Report Data.
Drag the name field from the Report Data pane onto the first column in the table.
Click the Preview button and you will see the report listing all the accounts in your CRM.
Make sure to save your report before continuing onto publishing your report.

Trackback Print

Location: Blogs

CRM Reporting Services

Tags:

3 comment(s) so far...

Re: Step 2 – Setting up Visual Studio for your first report
Will VS 2010 work for this as well

By Mark on 9/14/2010 9:57 AM

Re: Step 2 – Setting up Visual Studio for your first report
Mark,

You will find that sql server setup will install the required version of visual studio regardless.
If you already have the tools in visual studio 2010 they might be a higher version that what your reporting servers supports also.

Thank you
john

By John on 9/14/2010 10:01 AM

Re: Step 2 – Setting up Visual Studio for your first report
I see, I was thinking VS on my Workstation but now I see you mean to use VS that is installed with SQL server. Thank you.

By Mark on 9/15/2010 9:03 AM

Your name:
Your email:
	(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:

Add Comment Cancel