SQL Server Report Builder: total at the bottom of the group

65 pts.
SQL Server Report Builder
I am writing a report that breaks on the first 4 fields. That part is working fine. I also want a count for each Group (the 4 fields), and a grand total. Since I want to break on all 4 fields as if they are one combined field, I made a concatenated column (called Break_key) and had the report total on that. I was surprised when the count appeared at the top of each group, rather than at the bottom. The grand total is at the very bottom of the report, as I would have thought. How can I get the sub-totals at the bottom, rather than the top? Thanks, Wayne Report Builder My query: select distinct fs.FUNDING_SOURCE , lpad(l.circuit, 2, '0') circuit, l.DEPARTMENT , l.REIMB_TYPE , p.POSITION_NO , decode (p.position_type, 'F', 'Full', 'S', 'Shared') position_type , ph.phasein, (case ph.status when 'F' then 'Filled' when 'H' then 'Hold' when 'V' then 'Vacant' when 'Z' then 'Frozen' else ' ' end) Position_Status, p.position_active, ph.abbrev_ttl title , ph.aoic_code , fs.FUNDING_SOURCE||lpad(l.circuit, 2, '0')||l.DEPARTMENT||l.REIMB_TYPE Break_key from position p, pos_history ph, location l, funding_source fs where p.position_id = ph.position_id and l.location_id = ph.location_id and fs.SOURCE_ID = l.SOURCE_ID and ( ( :PhaseIn_or_Shared = 'P' and ph.phasein = 'Y' ) or ( :PhaseIn_or_Shared = 'S' and p.position_type = 'S' ) ) and fs.source_id = nvl(:P_FUNDING_SOURCE, fs.source_id) and ltrim(rtrim(upper(l.circuit))) = ltrim(rtrim(upper(nvl(:P_CIRCUIT, l.circuit)))) and ltrim(rtrim(upper(l.department))) = ltrim(rtrim(upper(nvl(:P_DEPARTMENT, l.department)))) order by 1, 2, 3, 4, 5;

Answer Wiki

Thanks. We'll let you know when a new response is added.

OK, I will now answer my own question. The count for each Group was appearing at the top of each group BECAUSE the concatenated column (4 fields stuck together) was not a column in the report. I have the individual fields, so I did not put the concatenated column in the report. I put the concatenated column in the report, making it not visible, and the sub-total appeared at the bottom like I wanted.

As far as I can tell Oracle Reports will not let you total (i.e count) on more than one column. That is why I was sticking these 4 fields together in the first place.


Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Kccrosser
    I am a little puzzled by your title ("SQL Server Report Builder") and your comment about Oracle. Is this an Oracle or SQL Server system, or are you using SSRS against an Oracle database? If this is a SQL Server database, then you don't need the "upper" functions in the where clause - SQL Server isn't case sensitive. I am not sure how your records are distributed or keyed, but in general, using "ltrim", "rtrim", and "upper" functions in a Where clause can cause performance issues. If these are only running on a small set of records selected by the other Where expressions, that may not be a problem. I have a pathological dislike of functions in Where clauses, as these mean that the query optimizer will ignore indexes on columns referenced in the function calls and this can in turn cause full-table scans.
    3,830 pointsBadges:
  • Waynefmck
    This is Oracle. I do think I asked this question in the wrong place. In the system, the rtrim, ltrim and upper are needed to ensure the matches we want. The report runs quickly.
    65 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: