Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

SSRS - Report Builder 2.0 - SQL query generation - Order of fields dragged on the report changes the generated query Expand / Collapse
Posted Thursday, August 13, 2009 12:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 12:23 PM
Points: 8, Visits: 60
say, I have 3 tables : Toys, Baby, Diapers.

one Baby can have zero or many Toys and zero or many Diapers as well. (Toy - M:1 - Baby - 1:M - Diaper)

now in the report I am trying to put together Count of Toys per Baby, showing also Diapers Descr, when available.

if I drag Baby Name first, then Diaper Descr and then right after that I drop Count of Toys - I get only result set for data with Diapers and Toys present, and data where Diapers=null is not in the report!

in the resulting query I get subselect A (Baby + Diaper) joined to subselect B (Baby + Toy) on Baby ID (which is OK) and Diaper Description!!! which is NOT OK, because all data about Toys for null Diapers gets cut off

if, however, I drag Diaper first, then Baby, then Count of Toys - I get full result set even for Babies without Diapers. In the resulting queries I have subselects too, but they are joined on BabyID only.

Why is the order of columns that we drag changes the query so drastically?

How can we control that?

(Note: Diapers and Toys are named queries which are full outer joined to Baby for all BabyIDs, so even if Baby doesn't have Diaper - there will be an entry in Diapers view with Diaper Descr = null)
Post #770434
Posted Wednesday, November 11, 2015 9:30 AM


Group: General Forum Members
Last Login: Monday, November 28, 2016 3:25 AM
Points: 11, Visits: 177
with a condom!
Post #1735743
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse