How to runtime modify datasource of crystal report

Ali 110

Hello, I have a requirement of modifying the datasource of crystal report as per user select option from form. As records per each selection are too much which reduces the performance of application, so I need to do in this way. I tried to change the datasource by setting setdatasource command but it doesn't refresh the data in report. Thanks in advance Regards Ali Raza

Wendelius

Have you tried using Refresh on the report document. If that's not working, could you post the code since it would be easier to see the problem.

The need to optimize rises from a bad design.My articles[^]

Ali 110

Hi, Thanks for your reply. Basically I need to change the datasource of an On-demand sub report in the Drill event of Group in the crystal report viewer. The first time when I set datasource to On-demand subreport it works perfectly and sub report load with the data. But next time when I set datasource to subreport the subreport doesn't come up with the new data. The code I m using to set datasource is :

Private Sub crView_Drill(ByVal source As Object, ByVal e As CrystalDecisions.Windows.Forms.DrillEventArgs) Handles crView.Drill

    Dim strsql As String
    Dim objUtil As New clsReportUtil
    Dim rpt As CrystalDecisions.CrystalReports.Engine.ReportDocument
    Dim ds As New DataSet

    rpt = Me.crView.ReportSource

    strsql = "Select \* From tblDeliveryOrderHead " \_
            & "INNER JOIN tblCustomer ON tblDeliveryOrderHead.CustomerID = tblCustomer.CustomerID " \_
            & "Where CustomerName = '" & e.NewGroupName & "' "
    ds = objUtil.GetDataSet(IBS\_Utility.clsUtLib.GetConnectionString, strsql)

rpt.Subreports(0).SetDataSource(ds)
Dim subc As New CrystalDecisions.Shared.SubreportContext()
subc.SubreportName = rpt.Subreports(0).Name
subc.PageNumber = CType(source, CrystalDecisions.Windows.Forms.PageView).GetCurrentPageNumber
subc.Position = New System.Drawing.Point(1827, 2889)

tN.GroupLevel = e.NewGroupLevel
tN.GroupName = e.NewGroupName
tN.GroupNamePath = e.NewGroupNamePath
tN.GroupPath = New Integer() {e.NewGroupPath}
subc.ContainingGroupInfo = tN

CTyp(source,CrystalDecisions.Windows.Forms.PageView).DrillDownOnSubreport(subc)

rpt.Refresh()
End Sub

Regards

Wendelius

Just had a quick look but the first thing I noticed is that you don't set e.Handled to true. This prevents the default actions for drilling. Could that be the cause.

The need to optimize rises from a bad design.My articles[^]

Ali 110

I have also tried this but it doesn't make any effect.

Wendelius

It's been a really long time since I last used Crystal so I don't remember the details accurately. However another thing is that you don't really use source parameter. I'm wondering that are you actually creating a new subreport, but the one that you see is the one already defined in the main report. I didn't find what's the type of source in this case, but you could use debugger to see if you can take that as a starting point (use source for rpt variable instead of Me.crView.ReportSource) and so own.

The need to optimize rises from a bad design.My articles[^]

Ali 110

Thanks for your suggestions. I am not creating new sub report. Sub report already exist in main report. The source parameter in this event is PageView. Regards

Wendelius

Not sure about this, but it seems that the main report and the subreport are connected so the subreport is linked to the data in the main report datasource. Try if you can break the subreport connection (or link) in the designer with linking expert. Another approach could be that you set and refresh both datasources (for the main report and the subreport):

...
rpt.Subreports(0).SetDataSource(ds)
rpt.SetDataSource(someOtherDs)
...

The need to optimize rises from a bad design.My articles[^]

Ali 110

I've tried both the ways i.e linked & Unlinked Subreport. The other approach I have not tried coz in case of main report I will need to hold a large dataset which holds some memory. Regards

Wendelius

I think you should give it a try. Even if it's not the final solution, it could tell if that would solve the problem and if the problem is actually related to the main report.

The need to optimize rises from a bad design.My articles[^]