Zahara Hirani, MCAD
Crowe Chizek and Company LLC
August 21st, 2007
http://www.crowecrm.com
Summary: Zahara shows the steps needed to build a sub-report for a Chart Report for Microsoft Dynamics CRM using Visual Studio 2005.
Creating a sub report for a chart report using Visual Studio 2005
1. Open Visual Studio 2005.
2. Click on the File menu on the menu bar, point to Open, and then click Project/Solution.
3. In the New Project pop-up,
a. Under Project Types, select the Business Intelligence Projects. Under Templates, select Report Server Project Wizard.
b. Give the project a new name (e.g. AdventureWorksReports). Browse to a location where you wish to create the project.
c. The Solution Name will be automatically populated with the Project Name that you specified earlier.
d. Click OK.
4. Click the View menu bar, and select Solution Explorer.
5. This should open the solution Explorer on the right.
6. Right click Shared Data Sources
7. Enter the name of the shared Data source (e.g. AdventureWork_MSCRM)
8. Select Type as Microsoft SQL Server
9. Enter the Connection string as Data Source=Servername;Initial Catalog=Databasename
(E.g. Data Source=triton;Initial Catalog=Adventure_Works_Cycle_Demo_MSCRM)
10. Click OK.
11. Right click the folder named Report
12. Select Add
13. Select Existing Item
14. This should open a pop-up window to browse to the location of the item to add:
a. Select the chart report to add (e.g. SalesPipelineByStage.rdl)
b. Click Add
15. This should add the report and place it under the Report folder in the Solution Explorer.
16. This should also open the report and select the Data tab.
17. Close the report. (This report will be used later to link to the sub report).
18. Create the sub report.
19. Right click the folder named Report
20. Select Add
21. Select New Item
22. This should open a pop-up window to specify what type of item to add
a. Select Report
b. Enter the name of the report (e.g. OpportunityByOwner.rdl)
c. Click Add
23. This should create a new report and place it under the Report folder in the Solution Explorer.
24. This should also open the report and select the Data tab.
25. Click the Dataset dropdown list and select <New Dataset...>
26. Enter the name of the Dataset (e.g. SalesProcessDS)
27. Select the Data Source to use for the Data Set (e.g. AdventureWork_MSCRM)
28. Select the Command Type (e.g. Text)
29. Enter the query to get the data.
30. Click Ok.
31. Repeat the process for any other parameters
32. Create the criteria drop down list.
a. Select ‘Report’ from the top menu bar.
b. Select ‘Report Parameters’.
i. Click Add
ii. Enter the parameter value name
iii. Select the data type for the parameter
iv. Enter the parameter display name
v. Check the ‘Hidden’ checkbox as the parameters are going to be passed from the parent report to this sub report.
vi. For the Default Value,
1. Enter the data set to get a default value. (e.g. SalesProcessDS)
c. Click Add (to add another parameter)
i. Notice the value for the parameter below depends on the value for the previous parameter.
33. Click the Dataset dropdown list and select <New Dataset...>
34. Enter the name of the Dataset (e.g. SalesPipelineDetailDS)
35. Select the Data Source to use for the Data Set (e.g. AdventureWork_MSCRM)
36. Select the Command Type (e.g. Text)
37. Enter the query to get the data.
38. Click Ok.
39. Click on (…) near the drop down list for data sets available to open SalesPipelineDetailDS for edit.
40. Navigate to the Parameters tab.
Notice the parameters added earlier are linked to the variables that need to be passed to the query.
41. Execute the query by clicking the (!) Run button.
42. The records should display under the query statement.
43. Click on the ‘Layout’ tab.
44. Click on ‘View’ from the top menu bar and select ‘Toolbox’
45. This should give you a list of all tools that can be used to setup the report layout.
46. Drag and Drop the ‘Table’ tool from the Toolbox to your Layout tab body section.
47. Right click the table in the Layout tab and select Properties.
48. Select the Dataset that will be used to populate the table.
49. Select other header and footer properties for the report.
50. Navigate thru the tabs and select other properties for the report.
a. Sorting:
51. Click on ‘View’ from the top menu bar and select ‘Datasets’
52. This should give you a tree view of all datasets contained in the report and the fields each data set returns.
53. Enter the display name for each column in the Header section of the table.
54. Drag and drop the fields to display in each column as desired in the Detail section of the table.
55. Edit the style information based on what you would like to display.
56. Click on the ‘Preview’ tab to see the report.
57. Link the sub report to the main report:
a. Open the Main report.
b. Navigate to the Data Tab.
c. Right click the chart and select properties.
d. Navigate to the Data Tab of the chart properties.
e. Select the Data field
f. Click Edit
g. Navigate to the Action tab.
h. Select the ‘Jump to Report’ radio button
i. Select the SalesPipelineDetailByStage report from the dropdownlist.
j. Click on the Parameters button.
k. Select the appropriate parameters
l. Click OK to accept the parameters
m. Click OK to accept the action changes.
n. Click on the other data fields and repeat the process.
o. Click OK to accept changes to the report properties.
58. Navigate to the Preview tab and preview the report.
59. Click on a Sales Stage bar to view sub-report.
The report should now open in preview mode with the results.
You will notice the main report shows the opportunities for the Enrollment Sales Process. The X-Axis represents the Sales Stage. The two bars represent Revenue for each stage and Weighted Revenue based on the closing probability for each stage.
Clicking a Sales Stage should open the sales pipeline detail report with a table view of the sales details.
Zahara Hirani, MCAD is a Microsoft CRM developer at Crowe Chizek (www.crowecrm.com) Zahara currently focuses on .NET / SQL / SRS development on the Microsoft Dynamics CRM 3.0 platform.
