SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MultiValue Parameter not working


MultiValue Parameter not working

Author
Message
ahosack
ahosack
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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?
David Burrows
David Burrows
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17202 Visits: 10138
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.


peterzeke
peterzeke
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2082 Visits: 1766
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).



ahosack
ahosack
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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?
peterzeke
peterzeke
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2082 Visits: 1766
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



ahosack
ahosack
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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.
David Burrows
David Burrows
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17202 Visits: 10138
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.


ahosack
ahosack
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search