OpenReport using aggregate SQL, trying to apply filter fields not in results

[Cerial]

I have a report that does an aggregate (Total Charges by Person), and I want to apply dynamic filters (user-entered on a form) that refer to fields not in the report (Ex: Date, Type).

In SQL, it might look like this:
SELECT [Person].[FullName], SUM([Charge].[Total]) AS TotalAmount
FROM [Person] INNER JOIN [Charge] ON [Person].[PersonID] = [Charge].[PersonID]
WHERE [Charge].[ChargeType] = "Professional Fees"
AND [Charge].[ChargeDate] Between #01/01/2011# And #12/31/2011#
(The form allows the user to choose the type and date.)

When I call OpenReport, Access prompts the user to supply values for those fields not in the SELECT clause ([Charge].[ChargeType] and [Charge].[ChargeDate]), as if the fields were parameters.

The OpenReport call looks like this:
DoCmd.OpenReport "Summarized Charges by Salesperson", acViewPreview, strSelect
(where I dynamically generate the SQL you see above)

If I run the SQL directly in an Access query, I don't get the prompts (in case someone thought I might have misspelled the field names by accident).

I even tried revising the OpenReport call to this, splitting out the Where clause as a separate string, but I get the same result:
DoCmd.OpenReport "Summarized Charges by Salesperson", acViewPreview, strSelectWithoutWhere, strWhere


How can I define the report or SQL so that it won't prompt for those fields?

 

[sxschech]

Do you refer to the form for the prompts in your code? Change frmMain to the name of your form and change txtPrompt to the name of your prompt on the form.

...
...
Dim stwhere As String
...
..
stwhere = "fieldfromsql = Forms!frmMain.txtPrompt"
DoCmd.OpenReport "Summarized Charges by Salesperson", acViewPreview, strSelectWithoutWhere, strWhere
...
...

 

[Cerial]

Hi,

Thank you for the reply.

I tried referring to the prompts and embedding the values directly (separate attempts, same result). The problem is that the user is prompted for the field I'm actually trying to compare to. So by way of your example, if the code was:
stwhere = "fieldfromsql = Forms!frmMain.txtPrompt"The user was being prompted for fieldfromsql. Note that fieldfromsql is not one of the fields in the SELECT, but it is a field in one of the tables in the FROM.


The only way I was able to get this to work was to open the report (Hidden using AccessBasic), save my strSQL as the new RecordSource, save the report, then run the doCmd.OpenReport. It's not an elegant solution (I don't want the Report design to be overwritten everytime), so I'm hoping for something better.

 

[sxschech]

Is it not possible to include the "hidden" field in your initial sql statement since it won't show up on the report anyway if you don't print it. There are other options I can show you as you don't need to open and save the report in order to change the sql string, but the easiest solution would be to include those fields in your original query if possible.

 

[Cerial]

I'm not sure I follow your reply.

If my Charge table has Person(FK), Date, Type, Amount, and I want to aggregate the Amounts by Person but filter by Date and Type, the report can only display Person and Sum(Amount). If I include Date and Type in the SELECT, the values won't aggregate properly.
The only way for that to work would be for me to reformat the report to display all the records and do the aggregation for me (and I'd hide the detail). That seems inefficient.

If I take the generated SQL from my form and manually place it in my Report, I don't get the prompt -- so I believe it is possible to include these fields in the WHERE clause because I don't get a syntax error.
If you're saying it isn't possible to do this in code without prompting for those fields (and without saving the report each time) -- that's fair, I'll take that as the best answer. But it seems like Access isn't evaluating the SQL in the same way when it's passed as a parameter in the OpenReport method. I was hoping someone might be able to explain why?

 

[sxschech]

You can do aggregates on a report while keeping the detail in your sql. Don't put your fields in the detail section of the report. Put the fields you want displayed in a group header or group footer. Collapse the detail section so it doesn't take up space. I'm attaching a screen shot.

 

[Cerial]

Okay, that's what I figured you meant. I'll bring it up to my team as the alternative solution. I'm doubtful it would be as scalable or as efficient as allowing Access to calculate the sum for me.

We'll weigh the performance against modifying the report (risky if multiple users are running the same report).

Thanks for looking into it.

 

[sxschech]

I'm puzzled why you are saying that

I'm doubtful it would be as scalable or as efficient as allowing Access to calculate the sum for me.

This method allows access to calculate the sums for you. You don't need a detail section in order to summarize or calculate data. I have many summary reports (of which the screen shot was a sample) that are simply grouped and they don't run slower. What could slow things down are the underlying query or if you are performing looping calculations in code behind a report. What do you mean by scalable?

If you need further example how to build a "detail less" summary report, let me know.

 

[vbaInet]

With regards passing filter criteria to your report, you only need to pass the WHERE clause in the Where argument of the OpenReport command. See here for an explanation (substituting OpenForm for OpenReport):

http://baldyweb.com/wherecondition.htm

 

[sxschech]

Hi vbaInet,

That is where we started, the problem was that the sql statement Cerial was using already excluded the field needed for the where clause, so couldn't do the where clause in the open report command. Since Cerial mentioned that the report was an aggregate, then we could add the field needed for the sql statement and perform the agregation on the report itself rather than in the sql, seems like Cerial wasn't too keen on that idea, so the last idea, which I hadn't posted yet was probably to do a querydef using vba to change the where clause in the sql, but hopefully using aggregation directly on the report method would work, so that wouldn't need to use vba to alter the query's where clause.

 

[vbaInet]

I see. Well, if the field doesn't exist in the report how does Cerial hope to filter it? He/she would have to go with your report aggregation idea. I can see you've already mentioned that anyway. Or Cerial can use the domain aggregate functions like DCount and DSum.,