Pass filter to subreport command

building reports crystal reports
1

I have a sub report that links to the main report via an ID. The database source for my subreport is a SQL Command. When I link the subreport with the main report it creates the parameter and then places that parameter in the record selection formula.

However, what I’d like to do is put that parameter in the where clause of my command as well to speed up the query. The subreport has about 1700 records or so that take just over a second to return. If I put in a filter in the command SQL logic to filter on just the account I want, it comes back nearly instantaneously.

The problem is that I have over 700 accounts on the report, so every time the subreport is generated, it runs the query and pulls back all 1700 records, then applies the record select filter. This causes the report to run for 12 minutes! :sleeping:

Is there any way to get it to apply the filter at the command level in the subreport instead?
Thanks for your help!

thizben :us: (BOB member since 2009-08-06)

2

Create a prompt on the command for the sub-report and then add it to the linking.

BDeLong (BOB member since 2008-01-15)

3

I had a similar situation. I used an execute statemenet in the command.

EXAMPLE: (place in command window)

DECLARE @param1 VARCHAR(100)
DECLARE @param2 VARCHAR(100)
DECLARE @query VARCHAR(1000)

SET @param1 = ‘{?command_param1}’ – from command parameter1
SET @param2 = ‘{?command_param2}’ – from command parameter2

SET @query = ’
SELECT * FROM <table_name> WHERE [column1] = ‘’’ + @param1 + ‘’’ AND [column2] = ‘’’ + @param2 + ‘’’’
EXEC(@query)

Hope this helps.

Toebee (BOB member since 2008-05-22)

4

Hi Guys, I’m having this exact same issue.

I believe the proposed solution would work. However I can’t do it.
CR is not allowing me to link to the parameter I created within the Command.

I have much more than 1700 records and the filtering approach (though it works data wise) will take more than an hour to run. This is of course not acceptable.

Thanks

-Russ

russellhogg (BOB member since 2009-10-20)

5

Actually you can link to it.
It’s just counterintuitive over on the left of the dialog.

Sorry for the forum noise.

-Russ

russellhogg (BOB member since 2009-10-20)