sql server reporting services: Serious issue with report builder - please help -- sql server reporting services

RE: Serious issue with report builder - please help

Charleh
9/22/2005 3:51:01 AM

I'm trying to work out exactly what you mean here - are we talking actually
building a query to simply get the raw data here, i.e. no report design
involved?

If thats the case there is no limitation on what you can do with the data...

The multiple customer addresses I can see to be a problem.

If you are creating a join from [customer] to [customeraddress] and the
[customeraddress] has multiple entries per [customer] you are going to be
recieving data rows containing the [customer] data multiple times, in turn
relating this to the [SalesOrderHeader] table, will give you all the sales
order headers multiplied by the number of addresses the customer has.

If you are choosing just one [customeraddress] this would solve any
redudancy problems in your dataset. If you wished to see multiple customer
addresses for a customer then you would need to use aggregate functions to
retrieve the data for the sales order headers.

In essence the data you recieve looks like this with multiple customer
addresses

CustID | CustName | AddressLine1 | SalesOrderNo
1 | Bob | 1 bob house | 000001
1 | Bob | 34 bob road | 000001
1 | Bob | 1 bob house | 000002
1 | Bob | 34 bob road | 000002
2 | Jill | 1 jill road | 000003
3 | Dan | 3 dan terrace | 000004
3 | Dan | 53 dan mill | 000004
3 | Dan | 94 dan row | 000004
3 | Dan | 3 dan terrace | 000005
3 | Dan | 53 dan mill | 000005
3 | Dan | 94 dan row | 000005

You would need aggregates to get the correct data - unless you did some
heavy grouping (which is the way the query builder would handle it...but is
usually not very efficient and quite time consuming for the server)

Give an example of the actual data you would expect to see in this situation
and I will explain how to get the data

I apologise if I'm barking up the wrong tree, however :P

--
Charleh

[quoted text, click to view]

Re: Serious issue with report builder - please help

Millen
9/22/2005 7:20:38 PM

Hi Charleh,

thanks for following up on my question.

Let me first state what I see as one of the major appeals of Report
Builder: that non-technical users should be able to easily create ad
hoc queries. The main goal here is to replace the use of MS Access as
an ad-hoc query tool for business users of the system.

Here is an example of the actual schema:
[Claims] many-to-one [Firm] many-to-one [Policy]
or in other words a firm can have many policies associated with it as
well as many claims.

so let us say that a user wants to see a simple list of the claims for
the firms which have certain types of policies.

In report builder, I drag the Claims table on the report canvas. Three
columns from the table show up on the screen. Now I navigate to the
related Firms table via the Claims-Firms relationship, and again choose
Firm Name from the Firms table. So far everything is fine. I can also
set up a filter - let's say I specify value for PolicyType and also
that the policy is from a certain year. Work just fine - looks very
promising ... However, if I try to drag a field (let's say PolicyType)
from the Policy table I cannot do it - the report designer just ignores
the drag-and-drop. So here it gets complicated for the end users - I
could create a new field MIN(Policy Type) and this will work - but this
is not what I really need as I may just want to see the records even if
there are duplicates. So in this case I would expect that there be a
number of multiple records i.e. one and the same claim would show up
twice if a firm has two policies, etc, but duplicates are not
necessarily bad for an *ad-hoc* report.

The problem for me is that the users cannot just drag the fields on the
report - there is no way to group by fields from both the Claims and
Policy tables. And this really confuses the *non-technical* users.
Sometimes the duplicates can ever be resolved by a filter (and the
nature of the underlying data) - let's say I specify a single policy
type and each firm can't have more than one policy of each type. In
this case there would be no duplicate claim records, but the users
still can't show any information from the Policy table without creating
aggregate expressions (which makes it by far more complex for them to
use the Report Builder).

So in general I don't see why the report builder should prevent the
users from grouping on data from both tables. I can see the danger in
creating a huge resultset but on the other hand each report assumes a
certain familiarity with the data (otherwise a weak filter could also
cause a report that returns millions of records).

Going back to the CustomerAddresses and Customer and SaleOrderHeader
tables, let's say that I want list the SalesOrder Numbers for all
customers that have an address somewhere in Australia. A possible
resultset could be something like

OrderNo Customer City
1 joe Sydney
1 joe Brisbane
1 joe Melbourne
2 joe Sydney
2 joe Brisbane
2 joe Melbourne

but unfortunately the Report Builder interface does not allow me to
select both the Order Number and the City fields at the same time (JUNE
CTP and SEPT CTP) (unless I use something like MIN(City) - which is
unnecessary complex for an end user).

Hopefully all this makes sense ...

Best regards,

Millen

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.