Drilldown links with DataSet and Crystal Reports Visual Studio edition

[former_member979790]

Hi, we are using Crystal Reports with ASP.NET for one of our older applications and decided that we would convert it to use local reports rather than RAS reports as it did not need the extra power of a Crystal Server install. Originally each report had a SQL query embedded in it and so we ended up converting each report to bind to a DataSet. This seems to have worked out for all our reports except for the ones that have on-demand SubReport links.

Previously a linked subreport had the SQL query embedded in it and it would execute that query using the linked parameters being passed from the main report. This would end up filtering the data shown in the subreport because certain links only passed certain parameters.

To give you an example, the subreport query would look something like this...

select * from detail_table
where detail_group_id = {non-optional variable}
and provider = {optional variable}
and client = {optional variable}

So, in this example some subreport links might pass provider and client, some might pass a blank value for provider or a blank value for client.

In our current implementation using a DataSet, when we create the ReportDocument we fill each table in the DataSet with the correct data and then bind the main report and subreport datasource properties to the proper tables in the DataSet. This works in that the links open the subreport and display the data. The problem is that you end up getting all the data for that subreport without any filtering. So, the reports themselves are working it's just that there doesn't seem to be a way to filter on the parameters being passed in with our current design.

I'd be interested to know if there was a way to do this, where a main/subreport linked to a DataSet can be filtered off a main report link. This would solve my problem entirely.

I'm open to alternatives though. I started exploring whether I could just intercept the postback and load the subreport from our .NET code. I was able to see that there is a ReportOutput_DrillDownSubreport_ event being created that I can handle, but unfortunately it has nothing regarding the parameters that are linked from that particular subreport link and so I have no way to obtain the filter criteria. Would there be a way to get access to that information? Is there another event I can use? If there was some way to obtain the linked parameters being passed that would also make it simple to solve my problem.

Other alternatives to get a filtered subreport I was considering...

- I can change the subreport links into custom hyperlinks that link to a local page and put formula values into the querystring, but this obviously is a GET, not a POST and so I would have to reconstruct the whole page to account for these calls. I'd rather not have to do this.

- I notice the Crystal Viewer uses JavaScript for postbacks. Can I embed JavaScript in custom links that will post the form with the subreport link values?

I'd appreciate any help.

[DellSC · Accepted solution]

When you reset the subreport to use a dataset, did you update the subreport links? In this case, you should be able to use the subreport links in the Select Expert in the subreport to get the data you're looking for. It won't filter the dataset, but will filter the data that the report is using from the dataset.

-Dell

[former_member979790 · Accepted solution]

Dell, I appreciate your quick reply.

I'm fine with the subreport just filtering the data that is displayed and not reloading the DataSet.

We did update the subreport links, mostly because the report would not load without prompting for parameters until that was done.

Can you go into some more detail on this procedure for using the select expert? I've only just picked this Crystal Reports thing up second hand and I'm afraid I don't quite understand how I would make this happen.

I think I see what you are saying though, I have the various command fields and formula fields in the select expert that I can choose from and then I can set a condition to check if one of those is equal to one of the parameter fields?

[DellSC · Accepted solution]

Yes, that's exactly correct. If you're using commands, you'll put the filter in the command and use the links as parameters there. However, when you're connecting to a data set, you use that instead of a command, in which case the filter goes in the Select Expert, which Crystal uses to build the "where" clause of the SQL it creates to query the dataset.

-Dell

[former_member979790 · Accepted solution]

Thanks Dell! It worked! I created record selection formulas in the linked subreports and it's filtering the data for me.

[DellSC · Accepted solution]

Awesome! Please accept the answer as correct. Thanks!