/*---------------------------------------------------

DATA TIER SQL

Show list of roles with marginal totals
DROP PROC GetUserRoleListWithCount

*/---------------------------------------------------

CREATE PROC GetUserRoleListWithCount
AS
BEGIN

  SELECT tblUser.UserID, tblUser.Login, tblRole.Name, 
         tblRole.Description, RoleCount,
         (SELECT COUNT(*) FROM tblUserRole) AS TotalRoleCount
  FROM tblUser, tblUserRole, tblRole, (SELECT UserID, COUNT(*) AS RoleCount
                                       FROM tblUserRole
                                       GROUP BY UserID) AS vwRoleCnt
  WHERE tblUser.UserID=tblUserRole.UserID AND
        tblRole.RoleID=tblUserRole.RoleID AND 
        tblUser.UserID=vwRoleCnt.UserID
  ORDER BY tblUser.Login, tblRole.RoleID

END
go

GRANT EXEC ON GetUserRoleListWithCount TO jhp
go
EXEC GetUserRoleListWithCount

//---------------------------------------------------

PRESENTATION TIER CODE-BEHIND

//---------------------------------------------------

using CrystalDecisions.CrystalReports.Engine;


//Create a public class for the actual .rpt file so that it is visible to the code behind.
public partial class MyReport : ReportClass
{

    public MyReport()
    {
    }

    public override String ResourceName
    {
        get
        {
            //Return an instance of the crystal report file
            //Replace with your file name
            return "RoleListByUser.rpt";
        }
        set
        {
            // Do nothing 
        }
    }
}


    protected void Page_Load(object sender, EventArgs e)
    {
        SetCrystalReportVariables();
    }

    protected void SetCrystalReportVariables()
    {
        //create a local instance of the report class (in the same way we 
        //created an instance of the data access tier.
        MyReport objMyReport = new MyReport();

        //create an instance of the logInfo for the Crystal Report
        CrystalDecisions.Shared.TableLogOnInfo myLogin;

        //scroll through the info table and assign login and password
        foreach (CrystalDecisions.CrystalReports.Engine.Table myTable in objMyReport.Database.Tables)
        {
            myLogin = myTable.LogOnInfo;
            //Note: we should return this from the AppSettings in the Web.Config file
            //But for simplicity of illustration and testing, I'm hard coding the values
            myLogin.ConnectionInfo.UserID = "jhp";
            myLogin.ConnectionInfo.Password = "jhppass";
            myTable.ApplyLogOnInfo(myLogin);
        }
        //Assign the report source. We could have done this at design time, but then we couldn't
        //pass in logInfo or parameters.
        CrystalReportViewer1.ReportSource = objMyReport;
    }
}