Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

MultiValue Parameter not working Expand / Collapse
Author
Message
Posted Friday, March 8, 2013 9:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 11, 2013 1:25 PM
Points: 9, Visits: 54
I'm using SSRS 2005.
SQL.
There is no real reason for multiple datasets other than I was trying to split up "sections of columns" that belonged with each other.

Most of the datasets are something like this...

SELECT (SELECT XY FROM XX WHERE RMPROPID IN (@PROPERTIES)), (SELECT YY FROM XX WHERE RMPROPID IN (@PROPERTIES)),(SELECT YX FROM XX WHERE RMPROPID IN (@PROPERTIES))

Could how this is structured be a problem?


Post #1428657
Posted Friday, March 8, 2013 10:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 7,102, Visits: 6,934
I was thinking more on line of

SELECT XX.XY, XX.YY, XX.YX 
FROM Properties
JOIN XX ON XX.RMPROPID = Properties.RMPROPID
WHERE Properties.RMPROPID IN (@PROPERTIES)

Which will give you one row per property and each column required in the report (i.e. XY = Units)

This is only guesswork since you have not supplied any DDL or sample data as requested by others.



Far away is close at hand in the images of elsewhere.

Anon.

Post #1428680
Posted Friday, March 8, 2013 10:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, July 27, 2014 8:47 PM
Points: 316, Visits: 1,483
David Burrows (3/8/2013)
I was thinking more on line of

SELECT XX.XY, XX.YY, XX.YX 
FROM Properties
JOIN XX ON XX.RMPROPID = Properties.RMPROPID
WHERE Properties.RMPROPID IN (@PROPERTIES)

Which will give you one row per property and each column required in the report (i.e. XY = Units)

This is only guesswork since you have not supplied any DDL or sample data as requested by others.


+1 -- I agree -- this way the data are related by RMPROPID. Also, David Burrows's example query as an idea may not necessarily return one row per property, however, but at the very least, all data related to a specific RMPROPID will be linked together correctly and reliably, which means that aggregating values together by RMPROPID would be correct.

Lastly, if multiple tables need to be referenced, but not all tables have data for a given RMPROPID, you'll want to use outer joins (.e.g, Left Join) from a core table (e.g., Properties) to your related tables (e.g. XX).



Post #1428690
Posted Friday, March 8, 2013 11:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 11, 2013 1:25 PM
Points: 9, Visits: 54
I'm getting closer guys.

Here is the SELECTEDPROPERTIES dataset, this is the dataset the table is based on.

SELECT RMPROP.RMPROPID,propname, AVB_GMSCM.PDSPDID, AVB_PDSPD.VPID, AVB_VICEPRES.SVPID, AVB_SVICEPRES.EVPID, AVB_EVICEPRES.OWNID, COUNT(UNIT.UNITID) units 
from RMPROP INNER JOIN AVB_GMSCM ON RMPROP.GMSCMID = AVB_GMSCM.GMSCMID
INNER JOIN AVB_PDSPD ON AVB_GMSCM.PDSPDID = AVB_PDSPD.PDSPDID
INNER JOIN AVB_VICEPRES ON AVB_PDSPD.VPID = AVB_VICEPRES.VPID
INNER JOIN AVB_SVICEPRES ON AVB_VICEPRES.SVPID = AVB_SVICEPRES.SVPID
INNER JOIN AVB_EVICEPRES ON AVB_SVICEPRES.EVPID = AVB_EVICEPRES.EVPID
INNER JOIN UNIT ON RMPROP.RMPROPID = UNIT.RMPROPID
WHERE RMPROP.RMPROPID in (@PROPERTIES)
GROUP BY RMPROP.RMPROPID,PROPNAME, AVB_GMSCM.PDSPDID, AVB_PDSPD.VPID, AVB_VICEPRES.SVPID, AVB_SVICEPRES.EVPID, AVB_EVICEPRES.OWNID

I added the inner join unit, and count(unit.unitid) to this dataset, before it was its own dataset. And it's being populated correctly now! That is great. However, here is an example of the next dataset I need (the next column over)

select COUNT(DISTINCT(NAME.NAMEID)) initialvisits froM rmaction inner join name on name.nameid=rmaction.nameid   inner join PROSPECT on PROSPECT.nameid=rmaction.nameid where rmaction.actcode='WI' and name.RMPROPID IN (@PROPERTIES) and rmaction.actdate>=@BEGIN and rmaction.actdate <= @END

I just don't see a viable way to include this query into the SELECTEDPROPERTIES dataset...

If anyone can explain to me how to get this portion to work, then I'm sure I can get the remaining 20 columns inserted into this statement as well...

Does this make sense?
Post #1428704
Posted Friday, March 8, 2013 11:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, July 27, 2014 8:47 PM
Points: 316, Visits: 1,483
Without knowing the relationships of your tables (i.e. one-to-one vs. one-to-many vs. many-to-many), try the following code.

SELECT  RMPROP.RMPROPID
,PROPNAME
,AVB_GMSCM.PDSPDID
,AVB_PDSPD.VPID
,AVB_VICEPRES.SVPID
,AVB_SVICEPRES.EVPID
,AVB_EVICEPRES.OWNID
,UNITS = COUNT(UNIT.UNITID)
,INITIALVISIT = COUNT(DISTINCT NM.NAMEID)

FROM RMPROP
JOIN AVB_GMSCM ON RMPROP.GMSCMID = AVB_GMSCM.GMSCMID
JOIN AVB_PDSPD ON AVB_GMSCM.PDSPDID = AVB_PDSPD.PDSPDID
JOIN AVB_VICEPRES ON AVB_PDSPD.VPID = AVB_VICEPRES.VPID
JOIN AVB_SVICEPRES ON AVB_VICEPRES.SVPID = AVB_SVICEPRES.SVPID
JOIN AVB_EVICEPRES ON AVB_SVICEPRES.EVPID = AVB_EVICEPRES.EVPID
JOIN UNIT ON RMPROP.RMPROPID = UNIT.RMPROPID

JOIN NAME ON NAME.RMPROPID = RMPROP.RMPROPID
JOIN RMACTION ON RMACTION.NAMEID = NAME.NAMEID
JOIN PROSPECT ON PROSPECT.NAMEID = RMACTION.NAMEID

WHERE RMPROP.RMPROPID IN ( @PROPERTIES )
AND NAME.ACTCODE = 'WI'
AND RMACTION.ACTDATE BETWEEN @BEGIN AND @END

GROUP BY RMPROP.RMPROPID
,PROPNAME
,AVB_GMSCM.PDSPDID
,AVB_PDSPD.VPID
,AVB_VICEPRES.SVPID
,AVB_SVICEPRES.EVPID
,AVB_EVICEPRES.OWNID ;


If this query blows out your count of "Units", then you may need to consider using either derived tables, a CTE, or temp tables, before piecing all of the data together in a final result set.

--pete



Post #1428709
Posted Friday, March 8, 2013 11:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 11, 2013 1:25 PM
Points: 9, Visits: 54
Hey,

Yeah the Units count blew up. Initialvisit was correct though ha.

This is the reason I had different datasets, because it is too complex of information spanning many different areas, to have only 1 select statement.
Post #1428711
Posted Friday, March 8, 2013 11:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 7,102, Visits: 6,934
In which case you will have to produce several sub queries to produce totals as sugggested by pete.

Simplistically put

SELECT t.ID,u.Units,v.Initialvisit
FROM [table] t
LEFT JOIN (SELECT COUNT(*) AS [Units] FROM [unittable] GROUP BY ID) u ON u.ID = t.ID
LEFT JOIN (SELECT SUM(X) AS [Initialvisit] FROM [visittable] GROUP BY ID) v ON v.ID = t.ID

and repeat the joins for each subset of count/sums

Another way would be to create a temp table containing all possible output columns and do inserts into this table for each subset of data making columns not in each subset zero. Then sum the data accordingly.



Far away is close at hand in the images of elsewhere.

Anon.

Post #1428718
Posted Friday, March 8, 2013 11:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 11, 2013 1:25 PM
Points: 9, Visits: 54
David Burrows, you are a life saver.

I'm doing the joining to the dataset, acting as a table.

I'm not a beginner to SQL, but this trick was definitely not in my toolbox.

Also, thanks Pete, both of you have helped me out tremendously getting over this hurdle, it's been one of those things where I've stared at it for so long I wasn't seeing all the options out there.

I'll post again if I run into anymore issues, thanks guys.
Post #1428723
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse