TransWikia.com

Crystal Reports won't display related table data

Stack Overflow Asked by RHarris on November 22, 2020

I’m new to Crystal Reports and trying to maintain someone else’s code — so bear with me. I have a crystal report that is tied to a DataSet with 2 related DataTables.

enter image description here

enter image description here

I’m grouping by JurorProfile.JurorPK. In my Group Header, I display information from the JurorProfile DataTable.

In the report detail, I have RowNumber, JurorQuestionAnswers.Question and JurorQuestionAnswers.Answer.

In my c# code, I’m populating the data like this:


public void SetDataSource(AdoProvider provider, int sessionPK, int jurorPK)
{
   string commandText;
   AdoQuery query;

   commandText = $@"select j.* from Juror j
   where j.JurorPK in (select JurorFK from SessionJurorLink where SessionFK = @sessionId)
   {(jurorPK > 0 ? " and jurorPK = @jurorId" ? "")}
   ";
   query = provider.CreateQuery(commandText, null
      , provider.CreateParameter("sessionId", DbType.Int32)
      , provider.CreateParameter("jurorId", DbType.Int32)
   );
   AdoReader jReader = query.ExecuteReader(sessionPK, jurorPK);
   report.Database.Tables["JurorProfile"]
     .SetDataSource((IDataReader)jReader.DataReader);

   commandText = $@"select ... from JurorAnswers ...
   where sessionFK = @sessionId
   {(jurorPK > 0 ? " and jurorFK = @jurorId " : "")}
   order by JurorFK, Rank, ParentFK, QuestionPK
   ";
   query = provider.CreateQuery(commandText, null
      , provider.CreateParameter("sessionId", DbType.Int32)
      , provider.CreateParameter("jurorId", DbType.Int32)
   );
   AdoReader qaReader = query.ExecuteReader(sessionPK, jurorPK);
   report.Database.Tables["JurorQuestionAnswers"]
     .SetDataSource((IDataReader)qaReader.DataReader);
}

However, when my report runs, the Group Heading displays and I get a record number printed for each question but the Question and Answer are not displayed.

enter image description here

It would appear, that Crystal Reports is not mapping the data results from my second query to the DataTable.

What’s even weirder is that JurorQuestionAnswers DataTable currently has a redundant Lastname and Firstname field. If I include those fields in the detail area (JurorQuestionAnswers.Lastname, JurorQuestionAnswers.Firstname), they actually show up — the data was properly mapped.

What am I doing wrong?

Update – Image of Links Tab

enter image description here

2 Answers

I finally got this to work. Basically, it involved replacing the DataSet that existed on the report with a new DataSet.

I had to change my code as follows:

public void SetDataSource(AdoProvider provider, int sessionPK, int jurorPK)
{
   string commandText;
   AdoQuery query;
   
   // ** Added Direct Reference to DataSet **//
   var ds = new QuestionAnswersData(); 

   commandText = $@"select j.* from Juror j
   where j.JurorPK in (select JurorFK from SessionJurorLink where SessionFK = @sessionId)
   {(jurorPK > 0 ? " and jurorPK = @jurorId" ? "")}
   ";
   query = provider.CreateQuery(commandText, null
      , provider.CreateParameter("sessionId", DbType.Int32)
      , provider.CreateParameter("jurorId", DbType.Int32)
   );
   AdoReader jReader = query.ExecuteReader(sessionPK, jurorPK);

   // ** REPLACED THIS LINE **//
   // report.Database.Tables["JurorProfile"]
   //  .SetDataSource((IDataReader)jReader.DataReader); 

   //** WITH THIS LINE **//
   ds.Tables["JurorProfile"].Load((IDataReader)jReader.DataReader, LoadOption.OverwriteChanges, null);  

   commandText = $@"select ... from JurorAnswers ...
   where sessionFK = @sessionId
   {(jurorPK > 0 ? " and jurorFK = @jurorId " : "")}
   order by JurorFK, Rank, ParentFK, QuestionPK
   ";
   query = provider.CreateQuery(commandText, null
      , provider.CreateParameter("sessionId", DbType.Int32)
      , provider.CreateParameter("jurorId", DbType.Int32)
   );
   AdoReader qaReader = query.ExecuteReader(sessionPK, jurorPK);

   //** REPLACED THIS LINE **//
   //report.Database.Tables["JurorQuestionAnswers"]
   // .SetDataSource((IDataReader)qaReader.DataReader); 

   //** WITH THIS LINE **//
   ds.Tables["JurorQuestionAnswers"].Load((IDataReader}jReader.DataReader, LoadOption.OverwriteChanges, null);

   //** NOW TIE THE DATASOURCE DIRECTLY TO THE REPORT **//
   base.SetDataSource(ds); 
}

Correct answer by RHarris on November 22, 2020

When you say "JurorQuestionAnswers DataTable currently has a redundant Lastname and Firstname field" I'm curious to know if, when you click on them in the dataset designer and look in the Expression property, you see something like Parent.Firstname (or whether the code contains anything like somedatatable.FirstnameColumn.Expression = "Parent.Firstname").

It's been a long time since I used CR, but my instant assumption is that CR cannot navigate DataRelations and someone worked this out and instead used the built in ability for a DataColumn Expression to reference other tables to conceptually import the parent data into the child table so that CR only has one table of data to deal with. CR will then preform a group on the parent columns itself (and because they reliably repeat the same data they group down to just one value) so you achieve the same effect, you just had to repeat the data

If this is the case, follow the pattern - if you want parent column X in the header, add a datacolumn (in the designer) to the child table, called ParentX (I like to put the word Parent in columns that are derived by relation, so people don't assume theyre redundant, but delberate), set its Experssion to Parent.X and then update/refresh your CR so it sees the new column and group on it

Answered by Caius Jard on November 22, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP