Hiding SubReport in Detail when no sub-data for given detail

Hiding SubReport in Detail when no sub-data for given detail

Posted by: softcomlimited on 18 August 2019, 10:02 pm EST

• 

  softcomlimited

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 18 August 2019, 10:02 pm EST

  Hello,
  
  In my detail section of my rpx I have a subreport that has labels in its report header. The subreport sometimes does not have any rows for a given main detail but I then get just the subreport header labels.
  
  I am manually filtering and setting the subreport.datasource in my main detail format…
  
  dr3 = _dt3.Select("LoanNo = " + Me.txtLoanNo.Value.ToString)
  
  subrpt3.DataSource = dr3
  
  me.subrprtLoanLinkedFDs.Report = subrpt3
  
  If dr3.Length > 0 Then
  
  me.subrprtLoanLinkedFDs.Visible = TRUE
  
  Else
  
  me.subrprtLoanLinkedFDs.Height = 0
  
  me.subrprtLoanLinkedFDs.Visible = FALSE
  
  End If

  Is there any way that I can hide the header labels or the whole subreport for that particular main detail?
  
  If I take out the subreport header label the subreport shrinks nicely, you just don’t know what you are looking at.
  
  I tried hiding the subreport in the main detail format event but then I don’t get any subreports for those main detail that are supposed to have subreports.
  
  I hope this is clear.
  
  Thanks
  
  Brian

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 19 August 2019, 12:18 am EST

  Hello Brian,

  Please do the following thing:
  
  1: Set the “CanGrow” and “CanShrink” property of SubReport to True
  
  2: Use the following line of code so that it will increase the performance:

  
  If dr3.Length > 0 Then
  me.subrprtLoanLinkedFDs.Report = subrpt3	
  Else
  me.subrprtLoanLinkedFDs.Report = Nothing	
  End If
  
  

  When you set the visible property of SubReport to false, it will render the subreport but make it hidden, on another hand when you set the Report property to null, then it will no render the subreport which will increase performance.

  Hope it helps.

  Thanks.

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 19 August 2019, 9:41 am EST

  Mr Mohitg,
  
  Actually my problem was that when I set it to false it would not show at all, even if a main report detail row is supposed to have report. Remember the subreport is inside the detail section.
  
  I will try doing the “nothing”.
  
  Thanks
  
  Brian

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 19 August 2019, 9:52 am EST

  Hello Brian,

  Actually my problem was that when I set it to false it would not show at all, even if a main report detail row is supposed to have report.
  
  One of the reasons for this, “CanGrow” property of SubReport control set to false that’s why your subreport can’t grow even when data is present.
  
  Please set the “CamGrow” property of SubReport to “True” to see the difference.

  Thanks.

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 19 August 2019, 10:25 am EST

  Mr Mohitg,
  
  “Nothing” has the same effect of totally disabling the subreport for all detail rows.
  
  I am attaching my rpx files (no database) so that you can see the layout.
  
  I tried your suggestions in line 134 of the script. As it is now I get no subreport. If I move line 135: me.subrprtColateral.Report = subrpt6 to before the If dr6.Length > 0 then I get something.

  Thanks
  
  BrianTestMain.zip

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 20 August 2019, 5:16 am EST

  Hello Brian,

  I have found no issue in your script. Request to please confirm that length of dr6 DataRow array is greater than 0. Could you please check with the following code for test:

  subrpt6.DataSource = dr6
  		Me.textbox1.Text= dr6.Length.tostring
  		Dim Test As Boolean = True
  		If  Test > 0 Then	'bFullDetail AND
  			Me.subrprtColateral.Report = subrpt6
  			Test=False
  			'Me.subrprtColateral.Height = .167
  			'me.subrprtColateral.Visible = TRUE
  		Else
  			Me.subrprtColateral.Report = Nothing
  		    Test=True
  		'	me.subrprtColateral.Height = 0
  		'	me.subrprtColateral.Visible = FALSE
  		End If
  
  

  Please share the result with us. Also, is it possible to share the application so that I can replicate the issue at my end?

  Thanks.

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 20 August 2019, 5:25 am EST

  Also, request to please confirm why are you providing the datasource to the subreport “subrpt10-1Colateral.rpx” at design time and runtime both?

  Thanks.

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 20 August 2019, 10:31 am EST

  Having both does not seem to be a a problem, in fact it is an excellent feature for me. At design-time I can use a temporary datasource and then at runtime it overrides and takes the one the application sets.

  Yes I know for sure it is > 0 for some detail rows because Me.textbox1.Text= dr6.Length.tostring shows the values for me to make sure.

  The data is in a SQLServer. Let me see if I can extract an make a demo for you.

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 20 August 2019, 12:21 pm EST

  You used “If Test > 0 Then” instead of “If Test = True then” remember TRUE = -1.
  
  I changed that so the subreport shows but, gosh, so sorry, I understand why you are confused. I gave you the sample where I had deleted the header labels in the subreport.
  
  If you edit subrpt10-1Colateral.rpx and make the reportheader visible and put a label in it you will see the subreport’s header even when me.subrprtColateral.Report = Nothing.

  Sorry again,
  
  Brian

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 20 August 2019, 5:52 pm EST

  Mr Mohitg,
  
  While I was making the demo for you I made a mistake and set me.subrprtColateral.Report.DataSource = Nothing instead of me.subrprtColateral.Report = Nothing .
  
  This actually worked perfectly, so if there are no subreport rows I get no subreport header. If there are subreport rows I get the subreport header and rows.
  
  Would I get any memory or performance problems doing it this way? My script is now…
  
  dr6 = _dt6.Select("LoanNo = " + Me.txtLoanNo.Value.ToString)
  
  '**subrpt6.DataSource = dr6
  
  me.subrprtColateral.Report = subrpt6
  
  me.textbox1.Text= dr6.Length.tostring
  
  If dr6.Length > 0 Then
  
  me.subrprtColateral.Report.DataSource = dr6
  
  me.subrprtColateral.Visible = TRUE
  
  '**me.subrprtColateral.Report = subrpt6
  
  Else
  
  me.subrprtColateral.Height = 0
  
  me.subrprtColateral.Visible = FALSE
  
  me.subrprtColateral.Report.DataSource = Nothing
  
  '**me.subrprtColateral.Report = Nothing
  
  End If

  Thanks
  
  Brian

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 21 August 2019, 12:32 am EST

  Brian,

  I am still confused the why your previous script does not work at your end. I have tried with a demo sample, it shows correct data. Please refer to the attached sample.
  
  Could you please share the version of AR that you are using at your end.

  Would I get any memory or performance problems doing it this way?
  
  Not as much. Because “Detail” section render only one time when there is no datasource which will not create any memory/performance issue.

  Thanks.
  
  SectionReportApplication21.zip

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 21 August 2019, 1:36 pm EST

  I’m confused too, it seems so simple. Spent too much time so as soon as I got something working I was glad and hoped I won’t get problems later. I am using 12.2.13986.0
  
  I’ll have a look at your sample.
  
  Thanks a lot once again.
  
  Brian

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 21 August 2019, 6:36 pm EST

  Mr Mohitg,
  
  I fiddled with your sample and your way is perfect and simple and works. I implemented what works for me and it works but I see something worrying. If lines 44 and 45 of Detail_Format() are commented, notice that wrong data appears for the 2nd record for “Ron Digital”; uncomment and correctly there are no products for Ron.
  
  I did the same thing on my report, leaving the subreport visible and I see a bunch of “Control Script failed” errors in the rows where there should be no data. Is this bad?

  On your sample is I leave it visible and then do Me.SubReport1.Report = Nothing, it is correct. On my report the subreport is lost completely which is wrong.
  
  I am attaching your modified sample for you to look at and I am going to try and get my data into an Access mdb for you to see my RPX in action.
  
  SectionReportApplication21.zip

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 22 August 2019, 2:51 am EST

  Mr Mohitg,
  
  I am attaching my working RPX that I load and run from my application. The PDF is the output I get.
  
  The _test.rpx is one that uses the Demo10.accdb. However when I preview it in the Standalone Report Designer I get entirely different output although the Detail_Format is the same. This I really don’t understand either.
  
  Both versions use the same subreports. The only other difference is that my app pushes data from SQL Server.
  
  Regards
  
  Brianrpt10-1LoanAnalysisDisbursed_test.zip

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 22 August 2019, 8:18 am EST

  Hello Brian,

  if lines 44 and 45 of Detail_Format() are commented, notice that wrong data appears for the 2nd record for "Ron Digital
  
  I got the correct data at my end. Please refer to the attached image.

  I get entirely different output although the Detail_Format is the same
  
  Do you get correct result or different incorrect result?

  I see a bunch of “Control Script failed” errors in the rows where there should be no data. Is this bad?
  
  Yes, there is no error should be displayed on the report.

  Could you please check with the latest version of AR12. You can download the same from the following link:
  
  http://cdn.grapecity.com/ActiveReports/ar12/ActiveReports-v12.3.17127.0.msi

  Both versions use the same subreports. The only other difference is that my app pushes data from SQL Server.
  
  You can send us the backup of your database so that I can check the same at my end.

  Thanks.
  
  ActiveReports.zip[img]https://gccontent.blob.core.windows.net/forum-uploads/file-9847c90d-a1df-4fef-8500-a44713bc780a.png[/img]

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 22 August 2019, 11:13 am EST

  Mr Mohitg,
  
  v12.3.17127.0 came out after my one year subscription ended so I can’t install that version.

  I will follow up on the rest of your reply later today.
  
  Thanks a lot
  
  Brian

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 22 August 2019, 11:31 am EST

  C1 and AR license scheme completely different. So, don’t confuse between them.

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 22 August 2019, 11:40 am EST

  Hello Brian,

  AR license is a perpetual license for the corresponding AR product. You are eligible to download all the service pack of the AR12 for lifetime although this is last service pack of AR12. One-year subscription is related to your maintenance support.

  Thanks.

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 22 August 2019, 3:04 pm EST

  Oh, brilliant!
  
  I just download and will install it now.
  
  Thanks for the great support.
  
  Brian

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 22 August 2019, 5:28 pm EST

  Mr Mohitg,
  
  After the install of the update, every time I try to open my project in VS2017 I now keep getting “The ArVsPackage package did not load correctly.”
  
  Restarting VS does not help. The error points to an ActivityLog.xml for more info but that file does not exist.
  
  Regards
  
  Brian Morris

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 22 August 2019, 11:49 pm EST

  Hello Brian,

  Could you please try after clearing the visual studio cache.
  
  https://www.matteopozzani.com/visual-studio-cache-cleanup/

  If the issue still occurs, please try to open VS after running the following command:
  
  “devenv /resetsettings”
  
  “devenv /resetuserdata”

  Hope it helps.

  Thanks.

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 23 August 2019, 2:22 am EST

  Thanks, had to do the devenv /resetuserdata. Something also went weird with my vs2017Pro license so I’m trying to get MS support on that.
  
  I still have to get back to you on the initial issue.
  
  Regards
  
  Brian

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 24 August 2019, 12:08 am EST

  Mr Mohit,
  
  I finally got vs2017Pro license sorted out. I am now using the updated AR12.

  I did try using my app to load the rpx sample that I sent you that uses the Access db, but it kept getting an error in the Detail_Format. If I used the Report Designer to preview it, it was fine. I could not figure it out.

  I made a SQL2017 Dummy db and and rpt10-1LoanAnalysisDisbursed_testsql.rpx that works fine in either my app or the Report Designer. I don’t know what is the problem with Access.

  So the sample that is attached is showing all the rows the way I want them, but I had to use my way. Please pay attention to what you get on pgs 1 , 2, 3, and 4.
  
  Thanks Brian
  
  rpt10-1LoanAnalysisDisbursed_testsql.zip

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 26 August 2019, 5:08 am EST

  Hello Brian,

  In my understanding, you have some incorrect code related to data-binding in the “rpt10-1LoanAnalysisDisbursed_test.rpx” which is bound with AccessDB. Please change the following code in your report:

  
    da = New System.Data.OleDb.OleDbDataAdapter("Select * From _dump1 ORDER BY SubAc, AcNo, LoanNo", cnn)
          da.Fill(ds, "dump1")
          _dt1 = ds.Tables(0)
          da = New System.Data.OleDb.OleDbDataAdapter("Select * From _dump2 ORDER BY LoanNo, ID", cnn)
          da.Fill(ds, "dump2")
          _dt2 = ds.Tables(1) 'instalment schedule
          da = New System.Data.OleDb.OleDbDataAdapter("Select * From _dump3 ORDER BY LoanNo, ID", cnn)
          da.Fill(ds, "dump3")
          _dt3 = ds.Tables(2) 'linked FDs
          da = New System.Data.OleDb.OleDbDataAdapter("Select * From _dump4 ORDER BY SubAc, ReasonGrp", cnn)
          da.Fill(ds, "dump4")
          _dt4 = ds.Tables(3) 'LoansDisbursedSumrybySubAc
          da = New System.Data.OleDb.OleDbDataAdapter("Select * From _dump5 ORDER BY ReasonGrp", cnn)
          da.Fill(ds, "dump5")
          _dt5 = ds.Tables(4) 'LoansDisbursedSumry
          'If myInfo.bPledges Then
          da = New System.Data.OleDb.OleDbDataAdapter("Select * From _dump6 ORDER BY LoanNo, PledgeID, PledgeRev DESC", cnn)
          da.Fill(ds, "dump6")
          _dt6 = ds.Tables(5)
  
  

  Also, please refer to the attached report.

  Hope it helps.

  Thanks.

  rpt10-1LoanAnalysisDisbursed_test.zip

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 26 August 2019, 1:46 pm EST

  Mr Mohitg,
  
  Going to have a look at it now. Don’t usually use oledb and access in .net.
  
  Thanks
  
  Brian

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 26 August 2019, 2:04 pm EST

  Mr Mohitg,
  
  The file you sent back does not have any script in it. Is it a change in the layout settings that I’m looking for ?

  Regards
  
  Brian Morris

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 26 August 2019, 2:11 pm EST

  Mr Mohitg,
  
  I applied you script changes to my original version and, yes, it now works correctly in both designer preview and loading in the app report viewer.

  OK, so are you able to duplicate my issue when I try to hide the subreport using your “correct” method of me.subrprt.Report = Nothing ?
  
  Regards
  
  Brian

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 27 August 2019, 1:38 am EST

  Hello Brian,

  Yes, but the issue is solved after using the modified code which I gave you in my previous reply.

  
  
     	If bPledges Then
     		'get the rows from the dt for this loan for the subreport subrprtColateral
     		dr6 = _dt6.Select("LoanNo = " + Me.txtLoanNo.Value.ToString)
     		subrpt6.DataSource = dr6		
     		Me.textbox1.Text = dr6.Length.tostring		
     		If bFullDetail AND dr6.Length > 0 Then
     			me.subrprtColateral.Report = subrpt6	
     		Else
     			me.subrprtColateral.Report = Nothing
     		End If
     	Else
     		me.subrprtColateral.Height = 0
     		me.subrprtColateral.Visible = FALSE		
     		me.subrprtColateral.Report = Nothing
     	End If
  
  

  Could you please check at your end and share the result.

  Thanks.

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 27 August 2019, 1:40 am EST

  rpt10-1LoanAnalysisDisbursed_test.zip

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 27 August 2019, 10:45 am EST

  Mr Mohitg,
  
  Do you notice that in what you sent there are blank lines between every name?
  
  Those are the headers from the blank subreports that I don’t want.
  
  When I do it my way I don’t get the blank lines.
  
  Thank you for your patience.
  
  Regards
  
  Brian
  
  MrMohitg.zip

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 27 August 2019, 11:03 am EST

  Hello,

  Use the attached code. I modified the code to avoid blank lines.

  Thanks.
  
  rpt10-1LoanAnalysisDisbursed_test_Modifed.zip

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 27 August 2019, 12:17 pm EST

  MrMohitg,
  
  Also you will notice in your method that you are not seeing any of the rows in the table Dump2 for subrptLoanSchedule that should appear on page 2 and 4 on the right-hand side.

  subrptLoanSchedule does not have a header but subrprtColateral does. I think am able to fix the blank lines using your method AND also
  
  me.subrprtColateral.Height = 0
  
  me.subrprtColateral.Report = Nothing

  Regards
  
  Brian

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 27 August 2019, 3:13 pm EST

  Mr Mohitg,
  
  When I change my script to exactly as you have it above I do not get the subrptColateral showing at all for anyone. However, oddly, you get stuff for subrptLoanSchedule. See attached comparison.
  
  Regards
  
  Brian

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 27 August 2019, 3:15 pm EST

  Attachment did not work, trying again.
  
  MrMohitg2.zip

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 28 August 2019, 7:45 am EST

  Hello Brian,

  I am able to observe the issue at my end. In my understanding, this code should work. I am discussing with our developer team(275292) on the same and will inform you once I get any reply from them.

  Sorry for wasting your precious time on this.

  Thanks.

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 28 August 2019, 9:21 am EST

  Mr Mohitg,
  
  Oh, ok. I thought it was working for you.
  
  Can you also find out if my “bad” way is likely to give me any memory/performance issues as this is the only way I can get it to work?
  
  Regards
  
  Brian

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 28 August 2019, 9:35 am EST

  Hello Brian,

  Oh, ok. I thought it was working for you.
  
  subrptLoanSchedule subreport is not visible on page 2 and page 4 at my method which I noticed after your reply.

  Your way is don’t a bad way. It does not affect any memory/performance issue which I said earlier. Our developer team also suggest a way similar to your way.

  
  Private _dt1 As New System.Data.DataTable
  Private _dt2 As New System.Data.DataTable
  
  Private _dt6 As New System.Data.DataTable
  Private dr2 As System.Data.DataRow()
  
  Private dr6 As System.Data.DataRow()
  
  
  Private LoanNumber As String = ""
  
  Sub ActiveReport_ReportStart
  .
  .
  .
      dim subrpt2 As GrapeCity.ActiveReports.SectionReport
      dim subrpt6 As GrapeCity.ActiveReports.SectionReport
  .
  .
  .
      subrptLoanSchedule.Report = subrpt2
      subrprtColateral.Report = subrpt6
  
  End Sub
  
  
  Sub Detail_Format
          
      Me.LoanNumber= Me.txtLoanNo.Value.ToString
  
      dr2 = _dt2.Select("LoanNo = " + LoanNumber)
      If dr2.Length > 0 Then
          subrptLoanSchedule.Report.DataSource = dr2
          subrptLoanSchedule.Visible = True
      Else
          subrptLoanSchedule.Visible = False
      End If 
      
      dr6 = _dt6.Select("LoanNo = " + LoanNumber)
      If dr6.Length > 0 Then
          subrprtColateral.Report.DataSource = dr6
          subrprtColateral.Visible = True
      Else
          subrprtColateral.Visible = False
      End If
      
  End Sub
  
  

  You can use either way to display the subreport. If any issue occurs please let me know. I would be more than happy to help you.

  Thanks.

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 28 August 2019, 11:23 am EST

  Mr Mohitg
  
  Ok, thanks, I will try this. I don’t really like my way since I have to use a different method for each subreport so as to get both to show.
  
  Thanks
  
  Brian

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 28 August 2019, 12:48 pm EST

  Mr Mohitg,
  
  Yes, this does seem to work consistently for both subreports. I will use this thanks a lot.
  
  Regards
  
  Brian