parameters in list from called SP in RS report

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

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

parameters in list from called SP in RS report

Author

Topic

tempus
Starting Member

47 Posts

Posted - 2011-01-10 : 09:20:52

Hey there. i am in need of your help regarding a report in RS using a stored procedure.

my stored procedure in sql looks like this.

USE [iScalaDB]
GO
SET ANSI_NULLS ON
GO

/*
exec [dbo].[BT_verif_preturi_agreate_intern]
@client = 'ELECTROARGES SA',
@produs = 'PF10300',
@parametru1 = '2',
@parametru2 = '2',
@parametru3 = '2',
@parametru4 = '2',
@parametru5 = '2',
@parametru6 = '2'

*/

SET QUOTED_IDENTIFIER ON
GO

alter procedure [dbo].[BT_verif_preturi_agreate_intern]

@client as nvarchar (35),
@produs as nvarchar (35),
@parametru1 as numeric(4,2),
@parametru2 as numeric(4,2),
@parametru3 as numeric(4,2),
@parametru4 as numeric(4,2),
@parametru5 as numeric(4,2),
@parametru6 as numeric(4,2)

as

SELECT left(SL01001,8),
SL01002,
SL01035,
@produs,
SC01002,
RMC_clasa_intern,
@parametru1,
@parametru2,
@parametru3,
@parametru4,
@parametru5,
@parametru6,
round((RMC_clasa_intern*(1+@parametru1)+@parametru2)/(1-@parametru3-@parametru4)/(1-@parametru5)+@parametru6,2),
getdate()
FROM RMC_CLASA_VS_REAL , SL010100, SC010100
WHERE SC01001 = @produs and
@produs = cod and
SL01002 = @client and
SL01010 = 'INTR'
GO

i have created the report in RS and created the data source which is ok.

in the Data tab of the report i called the sp like this :

exec [dbo].[BT_Romcolor_verif_preturi_agreate_intern]
@client = 'ELECTROARGES SA',
@produs = 'PF10300',
@parametru1 = '2',
@parametru2 = '2',
@parametru3 = '2',
@parametru4 = '2',
@parametru5 = '2',
@parametru6 = '2'

what i would like is when the report is run ( in preview mode or run by a user) is that the parameter @client and @produs to get the values from the database @client to be SL01002 and @produs to be SC01001. those 2 must be in a list so the user can select what ever they want.

Thanks in advance guys, i cranked my head on this today, but since i m new to RS i guess it's normal.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 11:38:40

you need to add a dataset for parameter values and inside dataset write query to retrieve your values. then go to parameter tab of your report and select option from query and map your dataset and field name to retrieve the values which will be populated in a combobox in preview as well as in report viewer.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tempus
Starting Member

47 Posts

Posted - 2011-01-11 : 01:39:02

any chance that you can help me further and tell me how can i create a dataset?

Thanks in advance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-11 : 11:25:12

just like you created dataset by calling your original procedure in data tab

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources