Using a formula in a query

[smooveb]

I've got this huge formula:

=<Current Period Actuals(Query 2 with GDW)>/Sum(<Current Period Actuals(Query 2 with GDW)>) ForAll (<C C ID(Query 2 with GDW)> ,<Project Code(Query 2 with GDW)>)

that shows up when I press Data > Variable. However, I wish to use this is the query, so I can create a greater than condition (greater than 1% in this instance).

What's the easiest way to do this? I have no access to the Universe from a designer's point of view. Thanks everyone!
B

 

[blom0344]

Hello Smooveb,

You actually want something that is inherently impossible: restrict the number of data retrieved from a query by using a condition that is based on data from the query.
However, there is no need to do this. If you assign your complicated calculation to a variable, you can do almost anything to it, provided that you define the variable as a dimension or detail. You can than filter,rank and sort on it or create a second variable to group the percentages to different groups.

Here is an example from a report I build concerning the free space in containers in our warehouse:

variable 1: (PCT_Full)

=1-((<Loc Volume2>-<Vol_qty2>)/<Loc Volume2>)

variable 2: (CATEGORY)

= If<PCT_full> > 1.00 Then &quot;1.>100%&quot; Else(If <PCT_full> > 0.90 Then &quot;2.>90%&quot; Else (If <PCT_full> > 0.80 Then &quot;3.>80%&quot; Else (If <PCT_full> > 0.70 Then &quot;4.>70%&quot; Else (If <PCT_full> > 0.60 Then &quot;5.>60%&quot; Else (If <PCT_full> > 0.50 Then &quot;6.>50%&quot; Else &quot;7.<50%&quot))))

Now I can use category as a section to group data according to which category they belong. I can sort this section, or show only the data belonging to PCT_full > 90% or anything else.