quote:

---

Originally posted by Taurkon

I have a number of reports where the number of rows returned is one, yet the row count at the bottom of the report returns a number far greater. When I write the SQL, I receive the correct number of rows returned (not just a single row).

I profiled the SQL and it also generates SQL the correct number of rows, in this case it returns multiple rows.

Any ideas?

---

quote:

---

Originally posted by Taurkon

Ok, here is the detailed version.

Dataset sample: **Note white space formatting not working. Billing Id's are last numeric character

Accouting_Period Charge_Amt Billing_Id
200401 10545.6100 1
200401 633.8100 2
200402 30.1100 1
200402 10817.6800 2
200402 31.8500 3
200403 -77.6100 1
200403 17.1400 2

etc.....

SQL statement run in Management Studio as baseline:

select accounting_period,
charge_amount,
billing_id
from service_revenue_admin
order by accounting_period,
billing_id

Result: 1373 rows returned

sample results returned:



Report Builder Design
Report Model Design: Contains 2 entities.
1. Service_Revenue_Admin
2. Scenario_Version - related to Service_Revenue_Admin through Scenario_Id (not shown)

Report Builder ad-hoc design
2 Entities available to user, Service_Revenue_Admin and Scenario_Version.

Create First group on canvas that contains Accounting_Period from Service_Revenue_Admin

Create Second group ob canvas that constains Charge_Amount and Billing_ID

Filter: None
Sort and Group: no sorting

Results:

One row for each accounting period. Total accounting periods equals 26, and 26 rows returned by Report Builder.

Text at bottom of report states: Total SERVCIE_REVENUE_ADMIN: 1373
** Same number as results from SQL query run in management studio baseline

design


results:



** Note, the results are what you see. 1 page, yet 1373 records.

I have worked this design many ways and cannot seem to get the desired results. I can create 3 different groups, using billing_id, accouting_period, and then another field to get the correct results, but in many cases, our users don't want to show billing_id. They may want to see all charge amounts by accounting period, and not display the billing_id.

---