Creating Report in Sharepoint

Whenever microsoft talks about reports, it talks about the reports created in excel. But, when we talk about creating report of some data that is entered in lists in Microsoft Sharepoint Server, it is a difficult task.

What we have to do is get the entire lists in sharepoint, store them in datatables and then join them.

#region Datatable DocInfo
DataTable dtDocInfo = new DataTable();
SPQuery qDoc = new SPQuery();
DataRow drDocInfo;
dtDocInfo.Columns.Add("Description");
dtDocInfo.Columns.Add("KCAno");
dtDocInfo.Columns.Add("KCAid");

SPListItemCollection docInfoCol;
#endregion

#region Datatable Eval Info
//Data table Dt Eval info

DataTable dtEvalInfo = new DataTable();
DataRow drEvalInfo;

dtEvalInfo.Columns.Add("Evaluation");
dtEvalInfo.Columns.Add("KCAno");
dtEvalInfo.Columns.Add("KCAid");
SPQuery qEval = new SPQuery();
SPListItemCollection evalInfoCol;
#endregion

#region Datatable Audit Finding
DataTable dtAuditFinding = new DataTable();
DataRow drAuditFinding;

dtAuditFinding.Columns.Add("Audit No");
dtAuditFinding.Columns.Add("KCAno");
dtAuditFinding.Columns.Add("KCAid");

SPQuery qAudit = new SPQuery();
SPListItemCollection auditFindingCol;
#endregion

#region Create First Relation

DataSet ds = new DataSet();
ds.Tables.Add(dtDocInfo);
ds.Tables.Add(dtEvalInfo);
DataTable jt = new DataTable("Joinedtable");
jt.Columns.Add("Description", typeof(String));
jt.Columns.Add("Evaluation", typeof(String));
jt.Columns.Add("KCAno", typeof(String));
#endregion

#region Create second relation
//Third table
DataSet thirdDS = new DataSet();
thirdDS.Tables.Add(jt);
thirdDS.Tables.Add(dtAuditFinding);

DataTable finaljt = new DataTable("FinalJoinedtable");
finaljt.Columns.Add("Description", typeof(String));
finaljt.Columns.Add("Evaluation", typeof(String));
finaljt.Columns.Add("KCAno", typeof(String));
//thirdDS.Tables.Add(finaljt);

#endregion

for (int h = 0; h < kcaCount; h++)
{

#region getdata from Audit finding
//Get data from auditFindingList///////////////////////////////////////////////////////////////////////////////////////
qAudit = new SPQuery();
qAudit.Query = @"


" + kcas[h] + @"



";
qAudit.RowLimit = 1;

auditFindingCol = auditFindingList.GetItems(qAudit);
foreach (SPListItem audititem in auditFindingCol)
{
//dtAuditFinding.Clear();
drAuditFinding = dtAuditFinding.NewRow();
drAuditFinding["Audit No"] = audititem["Audit No"];
drAuditFinding["KCAno"] = audititem["KCAno"];
drAuditFinding["KCAid"] = audititem["KCAid"];
dtAuditFinding.Rows.Add(drAuditFinding);
}

#endregion

#region getdata from eval info list
// //Get data from evalInfoList///////////////////////////////////////////////////////////////////////////////////////

qEval = new SPQuery();
qEval.Query = @"


" + kcas[h] + @"



";
qEval.RowLimit = 1;

evalInfoCol = evalInfoList.GetItems(qEval);
//dtEvalInfo = evalInfoCol.GetDataTable();
foreach (SPListItem evalitem in evalInfoCol)
{
//dtEvalInfo.Clear();
drEvalInfo = dtEvalInfo.NewRow();
drEvalInfo["Evaluation"] = evalitem["Evaluation"];
drEvalInfo["KCAno"] = evalitem["KCAno"];
drEvalInfo["KCAid"] = evalitem["KCAid"];
dtEvalInfo.Rows.Add(drEvalInfo);
}

#endregion

#region get data from doc info list
//Get data from docInfoList////////////////////////////////////////////////////////////////////////////////////
qDoc = new SPQuery();
qDoc.Query = @"


" + kcas[h] + @"




";
qDoc.RowLimit = 1;

docInfoCol = docInfoList.GetItems(qDoc);
//dtDocInfo = docInfoCol.GetDataTable();
if (docInfoCol != null)
{
foreach (SPListItem docitem in docInfoCol)
{
//dtDocInfo.Clear();
drDocInfo = dtDocInfo.NewRow();
drDocInfo["Description"] = docitem["Description"];
drDocInfo["KCAno"] = docitem["KCAno"];
drDocInfo["KCAid"] = docitem["KCAid"];
dtDocInfo.Rows.Add(drDocInfo);
}
}
#endregion

#region join table 1 & 2

//ds.Tables.Add(jt);
foreach (DataRow dr1 in dtDocInfo.Rows)
{
foreach (DataRow dr2 in dtEvalInfo.Rows)
{
string Kcano = (string)dr2["KCAno"];
string newKcaNo = "";
if (Kcano.Contains("#"))
{
string[] split = Kcano.Split(new Char[] { '#' });
newKcaNo = split[1].ToString();
}
else { newKcaNo = Kcano; }

if ((dr1.RowState != DataRowState.Deleted) && ((string)dr1["KCAno"] == newKcaNo))
{
try
{
DataRow newt = jt.NewRow();
newt["Description"] = dr1["Description"];
newt["Evaluation"] = dr2["Evaluation"];
newt["KCAno"] = dr2["KCAno"];
jt.Rows.Add(newt);
}
catch (Exception ex)
{ }
}
}
}
#endregion

# region Join table jt and 3

foreach (DataRow dr1 in jt.Rows)
{
foreach (DataRow dr2 in dtAuditFinding.Rows)
{
string Kcano = (string)dr2["KCAno"];
string newKcaNo = "";
if (Kcano.Contains("#"))
{
string[] split = Kcano.Split(new Char[] { '#' });
newKcaNo = split[1].ToString();
}
else { newKcaNo = Kcano; }

if ((dr1.RowState != DataRowState.Deleted) && ((string)dr1["KCAno"] == newKcaNo))
{
DataRow newt = finaljt.NewRow();
newt["Description"] = dr1["Description"];
newt["Evaluation"] = dr1["Evaluation"];
newt["KCAno"] = dr2["KCAno"];
finaljt.Rows.Add(newt);
}
}
}

#endregion

//End loop for KCA's
}

thirdDS.Tables.Add(finaljt);

DataSet FullFinalDs = new DataSet();
FullFinalDs = thirdDS.Clone();
DataRow drfinal = null;
for (int z = 0; z < kcas.Length; z++)
{
DataRow[] foundRows = thirdDS.Tables[2].Select("KCAno = '" + kcas[z] + "'");
if (foundRows.Length > 0)
{
drfinal = FullFinalDs.Tables[2].NewRow();
for (int count = 0; count < thirdDS.Tables[2].Columns.Count; count++)
{
drfinal[count] = Convert.ToString(foundRows[0][count]);
}

FullFinalDs.Tables[2].Rows.Add(drfinal);
}
}
DataView dvnew = FullFinalDs.Tables[2].DefaultView;
if (dvnew.Count == 0)
{
grdResultView.Visible = false;
lblMessage.Visible = true;
lblMessage.Text = "No Records are available for this selection";
}
grdResultView.DataSource = dvnew;
grdResultView.DataBind();
grdResultView.Visible = true;
}