Mircosoft ReportViewer with Sub Reports

Creating a report for report viewer and including a sub-report.

1. Create a new .ASPX page.
2. Go to the Toolbox and double click on Microsoft ReportViewer.
3. Right click on the project name in the Solution Explorer and right click.
4. Select Add New Item
5. Select Data Set and rename the Data Set.
6. Click Add
7. Click Yes to save the new Data Set in the App_Code folder.

In this instance I will be using a Stored Procedure already created.

8. Click on Server Explorer in the design view of the Data Set.
9. In the Server Explorer Window expand the folders for the database and then the Store Procedure folder to find the stored procedure you wish to use.
10.Hold the left button of your mouse down on the stored procedure you wish to use and drag the stored procedure onto the design view of the Data Set.
11. Save and close the data set.
12. Right click on the project name in the Solution Explorer and right click.
13. Select Add New Item
14. Select Report and rename the Report.
15. Click Add
16. Ensure your new report is open.
17. Expand Web Site Data Sources
18. Expand the Data Set that you created from step 8.
19. Drag and drop the field to be displayed in your report on to your report document in design mode.
20. Save your report document.
21. Go to your .ASPX page created in step 1.
22. Click on the arrow to the top right of the ReportViewer.
23. Under the drop drown list for choose report, select the report that you have created from step 14.
24. Then click on Choose Data Sources from below the drop down list called “Choose Report”
25. The Data Set you created in step 8 should automatically be chosen as Report Data Source for the Data Source Instance.
26. On the Object Data Source created below the ReportViewer click on the right arrow and select Configure Data Source.
27. On the page titled “Choose a Business Object”, the business object should be automatically created. Click Next
28. The next page titled “Define Data Methods” you will need to select the method from the drop down list.
29. Select the appropriate method and click Next.
30. On the page titled “Define Parameters”, you would only need to define a parameter if the SQL statement requires one.
31. Input your parameter method if needed.
32. ClickFinish
33.You can now test this report.
34. Now to create a sub report.
35. Again you will need a data set for your sub report so repeat steps 3 to 11.
36. Now you need to create the report form for your sub-report. For this you need to repeat steps 12 to 19 Only.
37. Next right click on the square on the top left axis of where both the axis meets.
38. From the context menu select Report Parameters.
39. In the Report Parameters wizard. Click add and enter a name in both Name and Prompt in the properties area. I tend to keep these names the same. Make a note of these names for future reference.
40. Click OK on the Report Parameters wizard.
41. Save and close your new sub report.
42. Find and open your original report created between steps 12 to 20.
43. Open the toolbox and drag and drop subreport on to your report. Resize the subreport so its shape will be displayed correctly in your report.
44. Right click on the grey area of the subreport and click properties in the context menu.
45. Under the General tab, find the drop down list subreport and find the name of the report you have created for you subreport and then click on it.
46. Then click on the parameters tab.
47. Click to the right of the star under the heading Parameter Name and enter the name of the parameter your created in step 39.
48. Click then under the heading Parameter Value and select the value you wish to use as a parameter to connect the report to your subreport, for which the subreport will need for its stored procedure.
49. Click OK.
50. Save and close the report.
51. Goto the code page of the ASPX Page you created in step 1. It will end in aspx.vb or aspx.cs dependent on which language you are using. In this case we are using VB.NET so it will be aspx.vb.
52. Above the Parital Class of the .aspx.vb document enter the following import commands

Imports Microsoft.Reporting.WebForms
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlClient.SqlCommand

53. In the Page Load sub of the .aspx.vb page enter:

AddHandler Me.ReportViewer1.LocalReport.SubreportProcessing, AddressOf ReportViewer1_SubreportProcessing

Note. ReportViewer1 is the name(ID) of your reportviewer in the HTML code. ReportViewer1_Subreportprocessing is the name of the subroutine in step 54.

54. Create the following subroutine.

Public Sub ReportViewer1_SubreportProcessing(ByVal sender As Object, ByVal e As SubreportProcessingEventArgs)

Dim strParameter1 As String = e.Parameters("").Values(0).ToString()
Dim rdS As New ReportDataSource("", (strParameter1))
e.DataSources.Add(rdS)

End Sub

Note: is the name of the parameter you created in step 39.
is created from the name of your dataset and the name of your stored proceedure in this fomrat “dataset_storedproceedure”.
is the name of the subroutine in step 55 used to call the data for the subreport.

55. Finally enter the following code for subroutine to open the database to and collect the data for the subreport.

Public Function (ByVal strParameter1 As String)
Dim dta As New SqlDataAdapter()
Dim con As New SqlConnection("")
Dim ds As New DataSet()
con.Open()
Dim cmd As New SqlCommand("", con)
dta.SelectCommand = cmd
dta.SelectCommand.Connection = con
dta.Fill(ds, "subrep_table")
con.Close()
Return ds.Tables("subrep_table ")
End Function

56. Save all document.
57. If you now run your report the report will display all data from both the report and the subreport.

Using the IIF command in Microsoft SQL

A little nitty problem of today I want to document. Basically came up with this command in an old Access Database

IIf([Length]=True,"Twelve Months"

Now, there is no IIf command in Microsoft SQL Statments, so this command needs to be converted to :

CASE
WHEN [Length]=1 THEN 'Twelve Months'
END

Simple!

Combine two Columns into one in a Microsoft SQL Statement

First post of the day, and just a quickie here for a Microsoft SQL Statement. This one is basically for use, if say, you have two fields for example one is first name and the other is last name. This short command in the 'SELECT' section of the SQL statement will condense two fields/columns into one.

(RTRIM(forname) + ' ' + RTRIM(lastname)) AS fullname

There go, short and simple! :)

Calculations and Formulas with Conditions in Crystal Report

A short post this time continuing with the Crystal Reports issues. This time I'm going to focus on doing calculations within the formula workshop, in particular with conditional uses.

1. From your crystal report, expand Unbound Fields in the Field Explorer.

2. Select Number from Unbound Fields and drag it to the position you require on your Crystal Report.

3. When the Unbound Number has been placed on your report, right click and select 'Edit Formula'.

4. Then follow the code below to create a percentage calculation based on two boolean true/false conditions:

Shared numberVar MyCount;

If

{Field 1 Selected from Report Field Window} = true

and

{Field 2 Selected from Report Field Window} = false

then

MyCount := MyCount + 1

Else

MyCount := MyCount;

MyCount/count({Field 3 Selected from Report Field Window})*100 & '%'

You can alter this code to your needs but hopefully it will give a broad understanding of a calculation done within Crystal Reports on data already called to the report.

Checkboxes in Crystal Report XI

This little problem niggled me for about an hour or so before rendering this soultion below :

1. Left click on the boolean field you wish to change to a checkbox in your Crystal Report.
2. Change the font to Wingdings. (This will change your boolean field to a lot of wierd and wonderful characters, but don't worry.
3. Right click on the boolean field and select "Format Object".
4. Select the common tab.
5. Click on the "X+2" button relevant to Display String.
6. In the formula box enter the following code :

If = true thenCHR(254) else CHR(111)

* I find it easier to double click on the boolean to be converted in the Report Field explorer window.

7. Save and close your new formula.
8. Click 'OK' on the Format Editor.
9. When you run your report, you'll find the boolean true/false will be displayed as a checkbox.

How to create a connection from a SQL Database to Crystal Reports with parameters.


1. Create a new Crystal Report
2. Set the new Crystal Report as a blank report
3. Right click on Database Fields
4. Select Database Expert
5. Expand Create New Connection
6. Double click on OLE DB (ADO)
7. Select OLE DB Provider for SQL Server
8. Click Next >
9. Input name of server
10. Input name of database
11. Check Integrated Security Checkbox
12. Click Next>
13. On the next page click Finish
14. Under Database Expert click on Current Connections
15. Click on database name
16. Click on dbo or specified user
17. Select Table or Stored Procedure
18. Table or Stored Procedure will now be available under Database Fields in the report.
19. Expand Database Fields
20. Expand Table or Stored Procedure
21. Select fields from Table or Stored Procedure and drag on to report.
22. Save the Crystal Report
23. On the web page from toolbox double click on Crystal Report Viewer
24. When the grey Crystal Report Viewer box appear click on the right arrow
25. Under Choose Report Source select
26. In the Create Report Source dialog box, under Specify a Crystal Report, select the Crystal Report you wish to display.
27. Click OK
28. If you do not require parameters debug here otherwise see below for parameters.
29. Enter the following code either on Page Load or with a sub/function and include the Import reference at the top of your document.

In VB.NET

Imports CrystalDecisions.ReportSource
Imports CrystalDecisions.Reporting
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Dim repDoc As ReportDocument = New ReportDocument
Dim repFilePath = Server.MapPath("~\Reports\CrystalReport.rpt")
repDoc.Load(repFilePath)
repDoc.SetParameterValue(0, )
repDoc.SetParameterValue(1, ))

CrystalReportViewer1.ReportSource = repDoc
CrystalReportViewer1.Visible = True

Or in C#

using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using CrystalDecisions.ReportSource;
using CrystalDecisions.Reporting;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

ReportDocument repDoc = new ReportDocument();
dynamic repFilePath = Server.MapPath("~\\Reports\\CrystalReport.rpt");
repDoc.Load(repFilePath);
repDoc.SetParameterValue(0, );
repDoc.SetParameterValue(1, );
CrystalReportViewer1.ReportSource = repDoc;
CrystalReportViewer1.Visible = true;

30. The report is now ready for display, and can be tested using the debugger.

That's how to basically use parameters on a Crystal Report using Visual Studio 2008 with ASP.NET 3.5.

Next will be how to use subGroups within a Crystal Report

Crystal Reports - The Basic

Hello, today I'm going to display how to create a basic Crystal Report by directly accessing a table or a stored procedure from a database.

1. Create a new Crystal Report
2. Set the new Crystal Report as a blank report
3. Right click on Database Fields
4. Select Database Expert
5. Expand Create New Connection
6. Double click on OLE DB (ADO)
7. Select OLE DB Provider for SQL Server
8. Click Next >
9. Input name of server
10. Input name of database
11. Check Integrated Security Checkbox
12. Click Next>
13. On the next page click Finish
14. Under Database Expert click on Current Connections
15. Click on database name
16. Click on dbo or specified user
17. Select Table or Stored Procedure
18. Table or Stored Procedure will now be available under Database Fields in the report.
19. Expand Database Fields
20. Expand Table or Stored Procedure
21. Select fields from Table or Stored Procedure and drag on to report.
22. Save the Crystal Report
23. On the web page from toolbox double click on Crystal Report Viewer
24. When the grey Crystal Report Viewer box appear click on the right arrow
25. Under Choose Report Source select
26. In the Create Report Source dialog box, under Specify a Crystal Report, select the Crystal Report you wish to display.
27. Click OK
28. The report is now ready for display, and can be tested using the debugger.

Later I will show how to use integer and sub groups within a crystal report.