如何过滤报表中的表达式？ [英] How to filter on an expression in a report?
问题描述
大家好，
这是漫长的一天，更糟糕的是，我今晚一走出门就开始度假。话虽这么说，我的智力并不像他们应该的那样尖锐......所以如果这是我应该知道的事情，请原谅我。
我有一个报告基于交叉表查询。交叉表查询具有一个名为[Total by Form]的字段，该字段总计表单的所有匹配项。在报告中，我使用[Total by Form]和子报表上的字段来创建百分比表达式，我将其放在一个名为FormPercent的文本框中。
我已经现在，我们的任务是向用户提供用于启动过滤掉所有表格（即报表上的行）的报表的选项，该报表小于10％。
我已经设置了表单来调用报告，并且我向报告传递了一个OpenArgs，告诉报告是否将FilterOn属性设置为是或否。然后，我将把我的过滤器表达式放在报告的Filter属性中，以便在报告运行时触发，并且用户表示他们想要查看异常（那些表单/行等于或大于10％）。
虽然我在使用过滤器表达式时遇到了麻烦。我可以为报表中的表达式创建过滤器表达式，还是只能为基础查询中的字段创建过滤器表达式？
仅供参考，这里是Percent11文本框中的表达式：
Hi everybody,
It''s been a long day and, to make matters worse, I start my vacation as soon as I walk out the door tonight. That being said, my mental faculties aren''t as sharp as they should be....so if this is something I should otherwise know, please forgive me.
I have a report that is based on a crosstab query. The crosstab query has a field called [Total by Form], which totals all occurrences of a form. On the report, I use the [Total by Form] with a field on a subreport to create a percentage expression, which I put in a text box called FormPercent.
I''ve now been given the task of giving the user the option on the form that''s used to launch the report of filtering out all forms (i.e. rows on the report) that are less than 10%.
I''ve got the form setup to call the report and I pass the report an OpenArgs that tells the report whether or not to set the FilterOn property to ''Yes'' or ''No''. Then, I was going to put my filter expression in the Filter property of the report so that it would trigger when the report was run and the user indicated that they wanted to view the exceptions (those forms/rows that are equal to or greater than 10%).
I''m having trouble with the filter expression though. Can I create a filter expression for an expression that is in the report or can I only create filter expressions for fields that are in the underlying query?
Just for reference, here''s the expression in the Percent11 text box:
推荐答案
嗨Beacon。当您将过滤器应用于表单或报表时，您实际上将WHERE子句应用于表单或报表的当前记录源中的字段，而不是窗体或报表上的控件。
在过滤器表达式中，您试图引用一个看似来自报告总数的字段。您不能使用它来过滤报告本身。总字段计算为当前记录源中相关字段或字段中的值的总和。显然，您不能将值应用为在应用过滤器时尚未计算的过滤器！
我会进一步评论如果您那么基于交叉表的报告是有问题的必须引用某种表达式中的旋转字段 - 当您尝试在许多单独命名的字段中引用值时，会导致大量重复工作。
我建议你最好不要再考虑一个不同的过滤解决方案，一个不涉及报告的解决方案（因为在计算之前你无法访问总数） ），或尝试访问交叉表查询本身的透视字段（因为旋转有效地使数据不正常）。
例如，您可以将交叉表查询连接到单独的查询，该查询计算相关行的组总计。由于这些组总计将在您选择的任何分组中的每一行重复，因此您可以将适当的过滤器应用于报告的相关行，而无需经历您目前正在尝试的扭曲。
-Stewart
Hi Beacon. When you apply a filter to a form or a report you are in effect applying a WHERE clause to the fields in the current recordsource of the form or the report, not the controls present on the form or report.
In your filter expression you are trying to refer to a field which appears to be from a report total. You cannot use this to filter the report itself. A total field is calculated as the sum of the values in the relevant field or fields in the current recordsource. Clearly, you cannot apply a value as a filter that has not yet been calculated at the time you apply the filter!
I would further comment that basing reports on crosstabs is problematic if you then have to refer to the pivoted fields in expressions of some kind - it leads to massive duplication of effort as you try to refer to values in many separately-named fields.
I would suggest instead that you would be better off looking at a different solution for the filtering, one that does not involve the report (because you cannot access a total until it has been calculated), or trying to access the pivoted fields of the crosstab query itself (because pivoting effectively unnormalises the data).
You could, for example, join your crosstab query to a separate query which calculates the group totals of the rows concerned. As these group totals would be repeated for each row within whatever grouping you have chosen you could then apply a suitable filter to the relevant rows of your report without having to go through the contortions you are trying at present.
-Stewart
感谢斯图尔特的回复。
我想我知道答案我的下一个问题，但无论如何我要问它只是为了确认。
如果我保留报告的当前设置，我可以在报告后面添加VBA来隐藏那些计算字段中的值不大于或等于10％的行？
我的交叉表到目前为止一直很好，我基本上只使用它们来显示在多个其他基础查询中缓慢编译的数据。我已经很好地查看了这些查询，我不确定我是否可以以相同的方式设置报告，并包含另一个计算百分比的报告......这可能超出了我的智力/想象力范围。如果我包含报表所基于的查询的SQL以及组成报表中包含的子报表的四个查询，您认为您能够理解它并给我一些指示吗？ br />
感谢您的帮助，
beacon
Thanks for the reply Stewart.
I think I know the answer to my next question, but I''m going to ask it anyway to just to confirm.
If I keep the current setup of my report, can I add VBA behind the report to hide those rows where the value in the calculated field isn''t greater than or equal to 10%?
My crosstabs have been great up to this point and I basically use them just to display the data that is slowly compiled in multiple other underlying queries. I''ve looked over these queries a good deal and I''m not sure I can setup the report the same way and include another report that calculates the percentage...this may be outside the realm of my intelligence/imagination. If I included the SQL for query that the report is based on and the four queries that make up the subreports that are included in the report, do you think you''d be able to make sense of it and give me some pointers?
Thanks for your help,
beacon
嗨Beacon。可以使用On Format或On Print事件有选择地打印行，但是没有看到行数据的示例，我不确定这是否是可行的方法。
如果你认为如何在Excel中解决这个问题，你可以先创建一个你希望以百分比表示的值的总和，然后将各个值除以此值得到百分比的总和。这里也是如此。
你不需要子报告来做这件事;您需要的是在交叉表本身中包含相关的总计，或者创建单独的总计查询并将其连接到相关的交叉表。
在交叉表的行中添加行总数很容易 - 取交叉表的值字段表达式并在交叉表中再次添加相同的表达式作为行标题字段名称。
如果您需要一个与单个交叉表行不同的分组级别，那么创建一个合适的总计查询仍然是一个简单的任务，您可以将其加入到有关的交叉表。如果您可以为您的PIVOT（交叉表）查询发布SQL并提供您需要哪个字段的示例，我将很乐意为您查看它并建议如何完成。
-Stewart
Hi Beacon. It possible to use the On Format or On Print events to print rows selectively, but without seeing an example of your row data I am not sure if this would be a viable approach or not.
If you think how you would approach this problem in Excel, you would create a sum of whatever value it is you want to express as a percentage first, then divide the individual values by this sum to get the percentage. The same is true here.
You don''t need a subreport to do this; what you need is either to include the relevant total in the crosstab itself, or to create a separate totals query and join it to the crosstab concerned.
Adding a row total to the rows of a crosstab is easy - take the crosstab''s Value field expression and add the same expression as a row heading again within the crosstab under a new field name.
If you need a total that is at a different level of grouping than the individual crosstab rows it is still a straightforward task to create a suitable totals query which you can join to the crosstab concerned. If you could post the SQL for your PIVOT (crosstab) query and give an example of which field you need the total of I''d be happy to look at it for you and advise how it can be done.
-Stewart
这篇关于如何过滤报表中的表达式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
