Missing rows in ad hock reports...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Missing rows in ad hock reports...

Author

Topic

ARC
Starting Member

10 Posts

Posted - 2007-02-07 : 07:53:25

Hi Again all,

Having found the SQL that report builder is generating, I now find that it generates a SELECT DISTINCT... by default whenever there is no ...GROUP BY... in the query.

Does anyone out there know how to turn this off!?

Added:

for the benefit of anyone else looking for the info on viewing the generated sql, here's the link

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78024

Taurkon
Starting Member

26 Posts

Posted - 2007-02-09 : 14:01:56

I have run into the same (similar) problem. Where do you find the SQL that report builder generates?

Edit: NM, found the thread describing how to view the generated SQL

Taurkon
Starting Member

26 Posts

Posted - 2007-02-09 : 14:10:17

This is the issue I posted on another SQL Server forum, with no response. I cannot believe I need to add a unique identifier to a report to get all records!

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1206736&SiteID=1

Our users use Report Builder for writing ad-hoc queries. They have run into a problem where Report Builder is returning only distinct records is the entities unique identifier is not added to the report.

Example:

TransID Type TransQuantity

1 CS 4
2 CS 4
3 CP 2
4 CN 3

Adding the above fields to the report builder canvas will return 4 rows. However, if I were to add only Type and TransQuantity to the canvas as such, Report Builder will only return 3 rows:

Type TransQuantity

CS 4
CP 2
CN 3

Is there any way to have Report Builder return the actual number of records? Our users often will export the results from Report Builder into Excel to slice and dice the data, and with the functionality the Report Builder has right now, incorrect values will be certain.

Taurkon
Starting Member

26 Posts

Posted - 2007-02-09 : 15:52:31

Ok, some more light on the subject, but not a clear answer. Manipulating the groupings seems to have an effect on whether or not all rows are returned or now. This only applies to key attributes.

Retrieve all rows:

Add multiple attributes at once

Retrieve distinct rows:

Add attributes individually

... more to come
Finally!

Taurkon
Starting Member

26 Posts

Posted - 2007-02-20 : 15:29:53

Still have issues with this. To be clear, if each attributes is in a separate group and the sum of the attributes equals a unique record, then you will not generate a "DISTINCT" query (trace SQL in query profiler to verify). The issue I run into is that our users may not wish to display all the data that makes the record unique. In many cases, this will be an ID attributes.

Still no solution to this, but I have opened a case with Microsoft. Will update with further info once it is made available.

ARC
Starting Member

10 Posts

Posted - 2007-02-26 : 02:20:44

Sorry Guys,
I should have got back to you with this. We've had an admision from Microsoft that the standard query builder app is 'crippled' by design to do this. There is an upgrade available that has what they call 'infinite drilldown' that allows users to drill into the top level summary to see the records within.

The workaround we are using is to expose the Sum() aggrigate fo any numeric values that the users may wish to work with instead of the native value. comon examples are amount and units. We also expose teh count() attribute of any entities that we do this with. This way the numbers always add up and if the users are interested in how many 'copies' of a particular row there are they can use the count() attribute.

Again, sorry for not getting back with this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources