reporting services stored procedure help

Hi all,
I need help using stored procedures in Reporting Services. I have a
dataset set to type stored procedure and when I run the sp it doesn't
return all the data that it should. When I run it in query analyzer it
returns approxiamately 4500 rows, but in RS it only returns around
1000. Is there a maximum number of rows that RS can return? Does
anyone have any idea what the problem could be? I've looked everywhere
and can't seem to find any answers.
Thanks in advance.
MissyAre you sure you are running the same sproc? from the same environement? do a
test by adding a top 10 to your sproc and check the results.|||Kyriakos wrote:
> Are you sure you are running the same sproc? from the same environement? do a
> test by adding a top 10 to your sproc and check the results.
The results are correct that I am getting, I am just not getting all
the results. I am also having trouble using the parameters with the
sproc. I am using temp tables in my sproc, would that mess everything
up?|||There is not a maximum number of rows (if rendering in PDF or Excel there
might be a practical limit, Excel has a limit of 65K).
First, no problem with temp tables. I use them all the time
One thing that could be happening is an artifact of the development
environment. If you don't change the values of the parameter (at least as
far as the preview tab, I'm not sure about the dataset tab) it uses data
that it caches. Look in the directory where you .rdl files are. You will see
a file called filename.rdl.data. You can delete the file and make sure that
it is requerying the database.
You mentioned that you are having trouble with passing parameters. You can
hard code the parameters. You can either click on ..., parameters tab, set
the mapping to an expression, and put in the value. OR you can do this:
pr_myprocedurename 'somevalue',1.0,'some'
I.e. you can put exactly the same in as you do in query analyzer. In this
way you can make sure that it isn't a parameter mapping/passing problem.
Doing this you might want to switch to generic query design mode. There is a
button to the right of the ... that allows you to do this.
And, you might have to switch from stored procedure to text to hard code the
parameters.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"aggiechick717" <mhoppe@.chkenergy.com> wrote in message
news:1156427092.932270.196010@.75g2000cwc.googlegroups.com...
> Hi all,
> I need help using stored procedures in Reporting Services. I have a
> dataset set to type stored procedure and when I run the sp it doesn't
> return all the data that it should. When I run it in query analyzer it
> returns approxiamately 4500 rows, but in RS it only returns around
> 1000. Is there a maximum number of rows that RS can return? Does
> anyone have any idea what the problem could be? I've looked everywhere
> and can't seem to find any answers.
> Thanks in advance.
> Missy
>|||Thanks Bruce. In my sproc do I need to specify the parameters after I
create the sproc and before the if and select statements? If so, when I
try to run the sproc in RS it still is only pulling the records that
have a district (one of my fields) name what begins with the letter A.
So it is missing tons of records. Any ideas? Thanks for your help.
Missy|||Does your stored procedure have parameters? Is RS recognizing the
parameters? When you execute from the dataset you should be prompted to fill
in the query parameters.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"aggiechick717" <mhoppe@.chkenergy.com> wrote in message
news:1156435417.812409.21310@.m79g2000cwm.googlegroups.com...
> Thanks Bruce. In my sproc do I need to specify the parameters after I
> create the sproc and before the if and select statements? If so, when I
> try to run the sproc in RS it still is only pulling the records that
> have a district (one of my fields) name what begins with the letter A.
> So it is missing tons of records. Any ideas? Thanks for your help.
> Missy
>|||yes it is recognizing the parameters in my sproc. I'm going to post my
sproc, this is my first time ever using a sproc, so I could have it all
wrong. :)
CREATE PROCEDURE [dbo].[meterquery]
@.district char
as
BEGIN
IF OBJECT_ID('tempdb..#TmpTable1') IS NOT NULL
BEGIN
DROP TABLE #TmpTable1
END
IF OBJECT_ID('tempdb..#ZonesInDelSys') IS NOT NULL
BEGIN
DROP TABLE #ZonesInDelSys
END
IF OBJECT_ID('tempdb..#DelSysHier') IS NOT NULL
BEGIN
DROP TABLE #DelSysHier
END
IF OBJECT_ID('tempdb..#MetersInDelSys') IS NOT NULL
BEGIN
DROP TABLE #MetersInDelSys
END
IF OBJECT_ID('tempdb..#TempAttr') IS NOT NULL
BEGIN
DROP TABLE #TempAttr
END
/******************* BEGIN CREATE TABLES **************************/
SELECT Root.DelSysHID AS DelSysHID
, H.HdrCode AS DelSysCode
, H.HdrName AS DelSysName
, CfgHist.DelSysTID
, Mp.MeasPtHID AS ZoneID
INTO #TmpTable1
FROM pdMasDelSys Root (NOLOCK)
INNER JOIN fbMasHdr H (NOLOCK) ON Root.DelSysHID = H.HdrHID
LEFT JOIN pdDetDelSysConfigHistory CfgHist (NOLOCK) ON
Root.DelSysTID = CfgHist.DelSysTID
LEFT JOIN pdDetDelSysMeasPoints Mp (NOLOCK) ON
CfgHist.DsConfigTID = Mp.DsConfigTID
LEFT JOIN fbMasHdr Mp1 (NOLOCK) ON Mp.MeasPtHID =Mp1.HdrHID
LEFT JOIN fbMasHdrType Typ1 (NOLOCK) ON Mp1.HdrTypeTID =Typ1.HdrTypeTID
WHERE 1=0
CREATE CLUSTERED INDEX IX1_#TmpTable1 ON
#TmpTable1(DelSysTID,DelSysHID,DelSysCode,DelSysName)
SELECT DISTINCT
ZR.District
, ZR.FieldOffice
, T.DelSysHID as DelSysID
, T.DelSysCode
, T.DelSysName
, ZR.ZoneID
, ZR.ZoneName
, ZR.PropertyNumber
, ZR.Area
, ZR.Route
, ZR.Operator
INTO #ZonesInDelSys
FROM productioncache..cache_zonerelations ZR
INNER JOIN #TmpTable1 T ON T.ZoneID = ZR.ZoneID
WHERE 1=0
--select * from #ZonesInDelSys
SELECT DISTINCT DelSysID, District, FieldOffice
INTO #DelSysHier
FROM #ZonesInDelSys
WHERE 1=0
CREATE CLUSTERED INDEX IX1_#DelSysHier ON #DelSysHier (DelSysID,
District, FieldOffice)
SELECT ZD.District
, ZD.FieldOffice
, T.DelSysHID as DelSysID
, T.DelSysCode
, T.DelSysName
, T.ZoneID
, R.MpDestHID AS GasMeterHID
, H.HdrCode as MeterCode
, HT.HdrTypeName as MeterType
, HA.hdrattrcode as MeterAttr
INTO #MetersInDelSys
FROM #TmpTable1 T (TABLOCKX)
INNER JOIN CHK.dbo.pdDetDelSysRelations AS R (NOLOCK) ON
T.DelSysTID = R.DsConfigTID
INNER JOIN #DelSysHier as ZD (TABLOCKX) ON ZD.DelSysID =T.DelSysHID
INNER JOIN CHK..fbMasHdr H (NOLOCK) ON H.HdrHID=R.MpDestHID
AND H.HdrTypeTID=150005
INNER JOIN CHK..fbMasHdrType HT (NOLOCK) ON
H.HdrTypeTID=HT.HdrTypeTID
INNER JOIN CHK..fbMasHdrAttribute HA (NOLOCK) ON
HA.HdrHID=R.MpDestHID
WHERE 1=0
SELECT District
, FieldOffice
, DelSysID
, DelSysCode
, DelSysName
, GasMeterHID
, MeterCode
, HA.hdrattrcode
INTO #TempAttr
FROM #MetersInDelSys A
INNER JOIN chk..fbmashdrattribute ha (NOLOCK) ON
ha.HdrHID=A.GasMeterHID
WHERE 1=0
/************* END CREATE TABLES ***********************************/
INSERT INTO #TmpTable1 WITH (TABLOCKX)
SELECT Root.DelSysHID AS DelSysHID
, H.HdrCode AS DelSysCode
, H.HdrName AS DelSysName
, CfgHist.DelSysTID
, Mp.MeasPtHID AS ZoneID
FROM pdMasDelSys Root (NOLOCK)
INNER JOIN fbMasHdr H (NOLOCK) ON Root.DelSysHID = H.HdrHID
LEFT JOIN pdDetDelSysConfigHistory CfgHist (NOLOCK) ON
Root.DelSysTID = CfgHist.DelSysTID
LEFT JOIN pdDetDelSysMeasPoints Mp (NOLOCK) ON
CfgHist.DsConfigTID = Mp.DsConfigTID
LEFT JOIN fbMasHdr Mp1 (NOLOCK) ON Mp.MeasPtHID = Mp1.HdrHID
LEFT JOIN fbMasHdrType Typ1 (NOLOCK) ON Mp1.HdrTypeTID =Typ1.HdrTypeTID
WHERE 1=1
AND (Typ1.HdrTypeCode = 'Zone')
AND (H.HdrInactive = 0)
AND (Mp1.HdrInactive = 0)
AND (CfgHist.DsConfigEffEnd = '12/31/2078')
AND CfgHist.DsObsolete=0
--and root.DelSysHID in(1754810,1711525)
GROUP BY Root.DelSysHID, H.HdrCode, H.HdrName, Mp.MeasPtHID,
CfgHist.DelSysTID
ORDER BY H.HdrName
--Zones in DelSys
INSERT INTO #ZonesInDelSys WITH (TABLOCKX)
SELECT DISTINCT
ZR.District
, ZR.FieldOffice
, T.DelSysHID as DelSysID
, T.DelSysCode
, T.DelSysName
, ZR.ZoneID
, ZR.ZoneName
, ZR.PropertyNumber
, ZR.Area
, ZR.Route
, ZR.Operator
FROM productioncache..cache_zonerelations ZR (NOLOCK)
INNER JOIN #TmpTable1 T (TABLOCKX) ON T.ZoneID = ZR.ZoneID
WHERE getdate() BETWEEN ZR.OperationBeginDate and ZR.OperationEndDate
--DelSys Enertia Hierarchy
INSERT INTO #DelSysHier WITH (TABLOCKX)
SELECT DISTINCT
DelSysID
, District
, FieldOffice
FROM #ZonesInDelSys (TABLOCKX)
truncate table #MetersInDelSys
--Gas Meters in DelSys
INSERT INTO #MetersInDelSys WITH (TABLOCKX)
SELECT DISTINCT
ZD.District
, ZD.FieldOffice
, T.DelSysHID as DelSysID
, T.DelSysCode
, T.DelSysName
, T.ZoneID
, R.MpDestHID AS GasMeterHID
, H.HdrCode as MeterCode
, HT.HdrTypeName as MeterType
, HA.hdrattrcode as MeterAttr
FROM #TmpTable1 T (TABLOCKX)
INNER JOIN CHK.dbo.pdDetDelSysRelations AS R (NOLOCK) ON
T.DelSysTID = R.DsConfigTID
INNER JOIN #DelSysHier as ZD (TABLOCKX) ON ZD.DelSysID =T.DelSysHID
INNER JOIN CHK..fbMasHdr H (NOLOCK) ON H.HdrHID=R.MpDestHID
AND H.HdrTypeTID=150005
INNER JOIN CHK..fbMasHdrType HT (NOLOCK) ON
H.HdrTypeTID=HT.HdrTypeTID
LEFT JOIN CHK..fbMasHdrAttribute HA (NOLOCK) ON
HA.HdrHID=R.MpDestHID
WHERE HA.HdrAttrCode in
('Transp','Fuel','Alloc','Check','Custody','Custdy'
,'ChCalc','Irrig','Deduct')
INSERT INTO #TempAttr WITH (TABLOCKX)
SELECT District
, FieldOffice
, DelSysID
, DelSysCode
, DelSysName
, GasMeterHID
, MeterCode
, HA.hdrattrcode
FROM #MetersInDelSys A (TABLOCKX)
INNER JOIN chk..fbMasHdrAttribute HA (NOLOCK) ON
HA.HdrHID=A.GasMeterHID
WHERE HA.HdrAttrCode in
('Transp','Fuel','Alloc','Check','Custody','Custdy'
,'ChCalc','Irrig','Deduct')
--SELECT where only 1 meter in DelSys
SELECT A.District, @.district as districtname
, A.FieldOffice
, A.DelSysID
, A.DelSysCode
, A.DelSysName
, A.ZoneID
, HZ.HdrName
, A.GasMeterHID
, HG.HdrName as MeterName
, A.MeterCode
, A.MeterType
, A.MeterAttr
FROM #MetersInDelSys A (TABLOCKX)
INNER JOIN CHK..fbMasHdr HZ (NOLOCK) ON HZ.HdrHID=A.ZoneID
INNER JOIN CHK..fbMasHdr HG (NOLOCK) ON
HG.HdrHID=A.GasMeterHID
INNER JOIN (SELECT DelSysID
, count(*) as num
FROM #MetersInDelSys
GROUP BY DelSysID
HAVING count(*) = 1) G1 ON
G1.DelSysID=A.DelSysID
where @.district=a.district
/**************** CLEAN UP TEMP TABLES**********************/
IF OBJECT_ID('tempdb..#TmpTable1') IS NOT NULL
BEGIN
DROP TABLE #TmpTable1
END
IF OBJECT_ID('tempdb..#ZonesInDelSys') IS NOT NULL
BEGIN
DROP TABLE #ZonesInDelSys
END
IF OBJECT_ID('tempdb..#DelSysHier') IS NOT NULL
BEGIN
DROP TABLE #DelSysHier
END
IF OBJECT_ID('tempdb..#MetersInDelSys') IS NOT NULL
BEGIN
DROP TABLE #MetersInDelSys
END
IF OBJECT_ID('tempdb..#TempAttr') IS NOT NULL
BEGIN
DROP TABLE #TempAttr
END
END
Then inside RS I select a dataset and set the command to stored
procedure. I then go to the layout tab and specify a parameter called
district and it pulls from the sproc query district name. if that
makes sense...|||Several things are problematic. First, my guess is you first wrote this in
query analyzer and then turned it into a stored procedure. SQL Server cleans
up for you. You never ever have to drop temp tables manually. Let SQL Server
handle that. Remove all of that at the beginning of your stored procedure.
Next, do not manually drop your temp tables at the end of your stored
procedure. That can definitely cause problems with RS. You should just let
it fall out of scope and let SQL Server manage the temp tables lifespan.
So, the drop tables at the beginning are just unnecessary. The drop tables
at the end are dangerous (from the perspective of Reporting Services).
Remove every single drop table and see what happens. The rest of it looks
good.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"aggiechick717" <mhoppe@.chkenergy.com> wrote in message
news:1156437458.749343.239350@.m79g2000cwm.googlegroups.com...
> yes it is recognizing the parameters in my sproc. I'm going to post my
> sproc, this is my first time ever using a sproc, so I could have it all
> wrong. :)
>
> CREATE PROCEDURE [dbo].[meterquery]
> @.district char
> as
> BEGIN
> IF OBJECT_ID('tempdb..#TmpTable1') IS NOT NULL
> BEGIN
> DROP TABLE #TmpTable1
> END
>
> IF OBJECT_ID('tempdb..#ZonesInDelSys') IS NOT NULL
> BEGIN
> DROP TABLE #ZonesInDelSys
> END
>
> IF OBJECT_ID('tempdb..#DelSysHier') IS NOT NULL
> BEGIN
> DROP TABLE #DelSysHier
> END
>
> IF OBJECT_ID('tempdb..#MetersInDelSys') IS NOT NULL
> BEGIN
> DROP TABLE #MetersInDelSys
> END
>
> IF OBJECT_ID('tempdb..#TempAttr') IS NOT NULL
> BEGIN
> DROP TABLE #TempAttr
> END
> /******************* BEGIN CREATE TABLES **************************/
> SELECT Root.DelSysHID AS DelSysHID
> , H.HdrCode AS DelSysCode
> , H.HdrName AS DelSysName
> , CfgHist.DelSysTID
> , Mp.MeasPtHID AS ZoneID
> INTO #TmpTable1
> FROM pdMasDelSys Root (NOLOCK)
> INNER JOIN fbMasHdr H (NOLOCK) ON Root.DelSysHID = H.HdrHID
> LEFT JOIN pdDetDelSysConfigHistory CfgHist (NOLOCK) ON
> Root.DelSysTID = CfgHist.DelSysTID
> LEFT JOIN pdDetDelSysMeasPoints Mp (NOLOCK) ON
> CfgHist.DsConfigTID = Mp.DsConfigTID
> LEFT JOIN fbMasHdr Mp1 (NOLOCK) ON Mp.MeasPtHID => Mp1.HdrHID
> LEFT JOIN fbMasHdrType Typ1 (NOLOCK) ON Mp1.HdrTypeTID => Typ1.HdrTypeTID
> WHERE 1=0
>
> CREATE CLUSTERED INDEX IX1_#TmpTable1 ON
> #TmpTable1(DelSysTID,DelSysHID,DelSysCode,DelSysName)
>
> SELECT DISTINCT
> ZR.District
> , ZR.FieldOffice
> , T.DelSysHID as DelSysID
> , T.DelSysCode
> , T.DelSysName
> , ZR.ZoneID
> , ZR.ZoneName
> , ZR.PropertyNumber
> , ZR.Area
> , ZR.Route
> , ZR.Operator
> INTO #ZonesInDelSys
> FROM productioncache..cache_zonerelations ZR
> INNER JOIN #TmpTable1 T ON T.ZoneID = ZR.ZoneID
> WHERE 1=0
> --select * from #ZonesInDelSys
> SELECT DISTINCT DelSysID, District, FieldOffice
> INTO #DelSysHier
> FROM #ZonesInDelSys
> WHERE 1=0
>
> CREATE CLUSTERED INDEX IX1_#DelSysHier ON #DelSysHier (DelSysID,
> District, FieldOffice)
>
> SELECT ZD.District
> , ZD.FieldOffice
> , T.DelSysHID as DelSysID
> , T.DelSysCode
> , T.DelSysName
> , T.ZoneID
> , R.MpDestHID AS GasMeterHID
> , H.HdrCode as MeterCode
> , HT.HdrTypeName as MeterType
> , HA.hdrattrcode as MeterAttr
> INTO #MetersInDelSys
> FROM #TmpTable1 T (TABLOCKX)
> INNER JOIN CHK.dbo.pdDetDelSysRelations AS R (NOLOCK) ON
> T.DelSysTID = R.DsConfigTID
> INNER JOIN #DelSysHier as ZD (TABLOCKX) ON ZD.DelSysID => T.DelSysHID
> INNER JOIN CHK..fbMasHdr H (NOLOCK) ON H.HdrHID=R.MpDestHID
> AND H.HdrTypeTID=150005
> INNER JOIN CHK..fbMasHdrType HT (NOLOCK) ON
> H.HdrTypeTID=HT.HdrTypeTID
> INNER JOIN CHK..fbMasHdrAttribute HA (NOLOCK) ON
> HA.HdrHID=R.MpDestHID
> WHERE 1=0
>
> SELECT District
> , FieldOffice
> , DelSysID
> , DelSysCode
> , DelSysName
> , GasMeterHID
> , MeterCode
> , HA.hdrattrcode
> INTO #TempAttr
> FROM #MetersInDelSys A
> INNER JOIN chk..fbmashdrattribute ha (NOLOCK) ON
> ha.HdrHID=A.GasMeterHID
> WHERE 1=0
> /************* END CREATE TABLES ***********************************/
> INSERT INTO #TmpTable1 WITH (TABLOCKX)
> SELECT Root.DelSysHID AS DelSysHID
> , H.HdrCode AS DelSysCode
> , H.HdrName AS DelSysName
> , CfgHist.DelSysTID
> , Mp.MeasPtHID AS ZoneID
> FROM pdMasDelSys Root (NOLOCK)
> INNER JOIN fbMasHdr H (NOLOCK) ON Root.DelSysHID = H.HdrHID
> LEFT JOIN pdDetDelSysConfigHistory CfgHist (NOLOCK) ON
> Root.DelSysTID = CfgHist.DelSysTID
> LEFT JOIN pdDetDelSysMeasPoints Mp (NOLOCK) ON
> CfgHist.DsConfigTID = Mp.DsConfigTID
> LEFT JOIN fbMasHdr Mp1 (NOLOCK) ON Mp.MeasPtHID = Mp1.HdrHID
> LEFT JOIN fbMasHdrType Typ1 (NOLOCK) ON Mp1.HdrTypeTID => Typ1.HdrTypeTID
> WHERE 1=1
> AND (Typ1.HdrTypeCode = 'Zone')
> AND (H.HdrInactive = 0)
> AND (Mp1.HdrInactive = 0)
> AND (CfgHist.DsConfigEffEnd = '12/31/2078')
> AND CfgHist.DsObsolete=0
> --and root.DelSysHID in(1754810,1711525)
> GROUP BY Root.DelSysHID, H.HdrCode, H.HdrName, Mp.MeasPtHID,
> CfgHist.DelSysTID
> ORDER BY H.HdrName
>
> --Zones in DelSys
> INSERT INTO #ZonesInDelSys WITH (TABLOCKX)
> SELECT DISTINCT
> ZR.District
> , ZR.FieldOffice
> , T.DelSysHID as DelSysID
> , T.DelSysCode
> , T.DelSysName
> , ZR.ZoneID
> , ZR.ZoneName
> , ZR.PropertyNumber
> , ZR.Area
> , ZR.Route
> , ZR.Operator
> FROM productioncache..cache_zonerelations ZR (NOLOCK)
> INNER JOIN #TmpTable1 T (TABLOCKX) ON T.ZoneID = ZR.ZoneID
> WHERE getdate() BETWEEN ZR.OperationBeginDate and ZR.OperationEndDate
>
> --DelSys Enertia Hierarchy
> INSERT INTO #DelSysHier WITH (TABLOCKX)
> SELECT DISTINCT
> DelSysID
> , District
> , FieldOffice
> FROM #ZonesInDelSys (TABLOCKX)
> truncate table #MetersInDelSys
> --Gas Meters in DelSys
> INSERT INTO #MetersInDelSys WITH (TABLOCKX)
> SELECT DISTINCT
> ZD.District
> , ZD.FieldOffice
> , T.DelSysHID as DelSysID
> , T.DelSysCode
> , T.DelSysName
> , T.ZoneID
> , R.MpDestHID AS GasMeterHID
> , H.HdrCode as MeterCode
> , HT.HdrTypeName as MeterType
> , HA.hdrattrcode as MeterAttr
> FROM #TmpTable1 T (TABLOCKX)
> INNER JOIN CHK.dbo.pdDetDelSysRelations AS R (NOLOCK) ON
> T.DelSysTID = R.DsConfigTID
> INNER JOIN #DelSysHier as ZD (TABLOCKX) ON ZD.DelSysID => T.DelSysHID
> INNER JOIN CHK..fbMasHdr H (NOLOCK) ON H.HdrHID=R.MpDestHID
> AND H.HdrTypeTID=150005
> INNER JOIN CHK..fbMasHdrType HT (NOLOCK) ON
> H.HdrTypeTID=HT.HdrTypeTID
> LEFT JOIN CHK..fbMasHdrAttribute HA (NOLOCK) ON
> HA.HdrHID=R.MpDestHID
> WHERE HA.HdrAttrCode in
> ('Transp','Fuel','Alloc','Check','Custody','Custdy'
> ,'ChCalc','Irrig','Deduct')
>
> INSERT INTO #TempAttr WITH (TABLOCKX)
> SELECT District
> , FieldOffice
> , DelSysID
> , DelSysCode
> , DelSysName
> , GasMeterHID
> , MeterCode
> , HA.hdrattrcode
> FROM #MetersInDelSys A (TABLOCKX)
> INNER JOIN chk..fbMasHdrAttribute HA (NOLOCK) ON
> HA.HdrHID=A.GasMeterHID
> WHERE HA.HdrAttrCode in
> ('Transp','Fuel','Alloc','Check','Custody','Custdy'
> ,'ChCalc','Irrig','Deduct')
>
>
> --SELECT where only 1 meter in DelSys
> SELECT A.District, @.district as districtname
> , A.FieldOffice
> , A.DelSysID
> , A.DelSysCode
> , A.DelSysName
> , A.ZoneID
> , HZ.HdrName
> , A.GasMeterHID
> , HG.HdrName as MeterName
> , A.MeterCode
> , A.MeterType
> , A.MeterAttr
> FROM #MetersInDelSys A (TABLOCKX)
> INNER JOIN CHK..fbMasHdr HZ (NOLOCK) ON HZ.HdrHID=A.ZoneID
> INNER JOIN CHK..fbMasHdr HG (NOLOCK) ON
> HG.HdrHID=A.GasMeterHID
> INNER JOIN (SELECT DelSysID
> , count(*) as num
> FROM #MetersInDelSys
> GROUP BY DelSysID
> HAVING count(*) = 1) G1 ON
> G1.DelSysID=A.DelSysID
> where @.district=a.district
>
> /**************** CLEAN UP TEMP TABLES**********************/
> IF OBJECT_ID('tempdb..#TmpTable1') IS NOT NULL
> BEGIN
> DROP TABLE #TmpTable1
> END
>
> IF OBJECT_ID('tempdb..#ZonesInDelSys') IS NOT NULL
> BEGIN
> DROP TABLE #ZonesInDelSys
> END
>
> IF OBJECT_ID('tempdb..#DelSysHier') IS NOT NULL
> BEGIN
> DROP TABLE #DelSysHier
> END
>
> IF OBJECT_ID('tempdb..#MetersInDelSys') IS NOT NULL
> BEGIN
> DROP TABLE #MetersInDelSys
> END
>
> IF OBJECT_ID('tempdb..#TempAttr') IS NOT NULL
> BEGIN
> DROP TABLE #TempAttr
> END
> END
>
> Then inside RS I select a dataset and set the command to stored
> procedure. I then go to the layout tab and specify a parameter called
> district and it pulls from the sproc query district name. if that
> makes sense...
>|||It's working yay!!! The only thing I have left to do is figure out the
report parameters. Do I need to specify them in my sproc? Or can I
create them through RS?
Thanks so much!|||Your stored procedure has a parameter. This is the query parameter. If you
are using SQL Server then RS recognizes the parameter and creates a
corresponding report parameter and maps to it. So when you create a dataset
based on the stored procedure you will then have a report parameter called
district created. I notice you had a parameter question in another posting.
I suggest starting with a new report so you don't have problems left over
from before.
Your query parameters have this mapping occur automatically but you can also
change the mappng to an expression by clicking on the ..., parameters tab.
Then if the report parameter is not needed you go to the layout tab, report
menu->report parameters and delete the now unused parameter (this is not
necessary in your case).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"aggiechick717" <mhoppe@.chkenergy.com> wrote in message
news:1156441135.234902.131430@.i3g2000cwc.googlegroups.com...
> It's working yay!!! The only thing I have left to do is figure out the
> report parameters. Do I need to specify them in my sproc? Or can I
> create them through RS?
> Thanks so much!
>