Sorting by a calculated value in a footer

[Paul Hammond]

Not sure how to describe this, but I have a report with 3 group footers.
There is a text box in each footer that is has a formula as the data source,
e.g.

=(Sum([Daily Comp])+Sum([Sunday
Comp]))/(Sum([TotalDailyDraw])+Sum([TotalSundayDraw]))*1000

First off I want to sort the report by this value, but I have no idea how to
do that.

Once I get that solved I plan to write code to have it sort by different
footers depending on the detail lever they want to view the report at. I
can't cross that bridge till I figure out how to sort by the footer.

Any suggestions?

Paul Hammond
Richmond, VA
--

 

[Rick Brandt]

Not sure how to describe this, but I have a report with 3 group
footers. There is a text box in each footer that is has a formula as
the data source, e.g.

=(Sum([Daily Comp])+Sum([Sunday
Comp]))/(Sum([TotalDailyDraw])+Sum([TotalSundayDraw]))*1000

First off I want to sort the report by this value, but I have no idea
how to do that.

Once I get that solved I plan to write code to have it sort by
different footers depending on the detail lever they want to view the
report at. I can't cross that bridge till I figure out how to sort
by the footer.

Any suggestions?

Paul Hammond
Richmond, VA

Reports cannot be sorted by aggregate calculations made in the report like
that. You could sort on something like...

=([Field1] + [Field2])/[Field3]

....but not when Sum() or other aggregation is being used since those are
calcualted per-section.

If you can figure out how to get those calculations moved into the query the
report is bound to then you can sort on them. Perhaps by using a spearate
query to perform those calcualtions that can then be joined to in the
current query?

 

[Paul Hammond]

That means 3 more queries to get the group values into the report query. Not
my preferred method, but looks necessary.

Thanks for the suggestion

Paul
--

Not sure how to describe this, but I have a report with 3 group
footers. There is a text box in each footer that is has a formula as
the data source, e.g.

=(Sum([Daily Comp])+Sum([Sunday
Comp]))/(Sum([TotalDailyDraw])+Sum([TotalSundayDraw]))*1000

First off I want to sort the report by this value, but I have no idea
how to do that.

Once I get that solved I plan to write code to have it sort by
different footers depending on the detail lever they want to view the
report at. I can't cross that bridge till I figure out how to sort
by the footer.

Any suggestions?

Paul Hammond
Richmond, VA

Reports cannot be sorted by aggregate calculations made in the report like
that. You could sort on something like...

=([Field1] + [Field2])/[Field3]

....but not when Sum() or other aggregation is being used since those are
calcualted per-section.

If you can figure out how to get those calculations moved into the query the
report is bound to then you can sort on them. Perhaps by using a spearate
query to perform those calcualtions that can then be joined to in the
current query?

 

[Paul Hammond]

Now that I've got the sort columns added to the query, how do I change the
sort dynamically. What I mean is if the user chooses to view the report at
the GroupLevel 3 my sort is still being affected by the sort on the hidden
levels.

Can I change this?

Paul
--

Not sure how to describe this, but I have a report with 3 group
footers. There is a text box in each footer that is has a formula as
the data source, e.g.

=(Sum([Daily Comp])+Sum([Sunday
Comp]))/(Sum([TotalDailyDraw])+Sum([TotalSundayDraw]))*1000

First off I want to sort the report by this value, but I have no idea
how to do that.

Once I get that solved I plan to write code to have it sort by
different footers depending on the detail lever they want to view the
report at. I can't cross that bridge till I figure out how to sort
by the footer.

Any suggestions?

Paul Hammond
Richmond, VA

Reports cannot be sorted by aggregate calculations made in the report like
that. You could sort on something like...

=([Field1] + [Field2])/[Field3]

....but not when Sum() or other aggregation is being used since those are
calcualted per-section.

If you can figure out how to get those calculations moved into the query the
report is bound to then you can sort on them. Perhaps by using a spearate
query to perform those calcualtions that can then be joined to in the
current query?