Dynamic Query - SQL Server Forums

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

Development Tools

Reporting Services Development

Dynamic Query

Author

Topic

syd_bloke
Starting Member

6 Posts

Posted - 2005-06-13 : 04:15:51

Hi,

My DataSet contains mulitple stored procedures as per my dynamic query below under the data tab in reporting services.

=IIF (Parameters!ListType.Value="B", "EXEC ls003", IIF (Parameters!ListType.Value="M", "EXEC ls004", "EXEC ls004"))

The stored procedures ls003 and ls004 returns Date parameters as Financial year and Period.

I have created the @ListType, @financial_year and @period under Report parameter, however, when I execute the report in report viewer, after supplying the values it generates an error of
" query execution failed Dataset 'dataset1'.
Procedure 'ls003' expects parameter '@financial_year' which was not supplied.

Your help will be much appreciated.

jhermiz

3564 Posts

Posted - 2005-06-13 : 07:54:27

I've experienced similiar issues in the past, the solution is to not look directly at the parameters list, what you need to do is go directly to the XML RDL of that report and make sure the parameters look right.

Also please post the stored procedures and whether you have supplied defaults to the parameters.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

syd_bloke
Starting Member

6 Posts

Posted - 2005-06-13 : 18:56:44

jhermiz thanks for your response, Stored proc is as under:

CREATE PROCEDURE ls003(@financial_year integer, @period as integer) AS
set dateformat dmy

declare @todate as varchar(10)
declare @fromdate as varchar(10)
declare @temp as varchar(8)
declare @period_1 as integer
declare @year_1 as integer

set @period_1=@period
set @year_1=@financial_year

/* Get the first calender date for the given period */
set @temp=(select period_date from scheme.cecaldrm where calendar_year=@financial_year and calendar_period=@period)
set @fromdate = right(@temp, 2) +'/' + substring(@temp,5,2)+'/'+left(@temp,4)

/* Establish the end of financial period */
set @temp =(
select min(period_date)
from scheme.cecaldrm
where (calendar_year=@financial_year and calendar_period>@period)
or
(calendar_year=@financial_year+1 and calendar_period=1))
/* Format date */
set @todate = right(@temp, 2) +'/' + substring(@temp,5,2)+'/'+left(@temp,4)

select product_group_a as product,
(select dsc from scheme.stkpgm where scheme.stkpgm.product_group=product_group_a) as product_desc,
sum(val)as ActualSales,
0 as BudgetSales,
sum(val-cost) as ActualGP,
0 as BudgetGP,
0 as BudgetGPPerc,
region,
(SELECT region_desc from scheme.l_regions WHERE region_nbr=region) as region_desc
from scheme.opsadetm inner join scheme.sthierm on product_group_a=scheme.sthierm.child
where product_group_a<>''
and warehouse<>'TL' and warehouse <>'TF' and warehouse<>'TM' and warehouse<>'TD'
and dated >= @fromdate and dated < @todate and excluded_details='N'
group by region,product_group_a

UNION ALL

select prod_group as product,
scheme.stkpgm.dsc as product_desc,
0 as ActualSales,
bud_val as BudgetSales,
0 as ActualGP,
gp_val as BudgetGP,
gp_perc as BudgetGPPerc,
region,
(SELECT region_desc from scheme.l_regions WHERE region_nbr=region) as region_desc
from scheme.opsalebud inner join scheme.stkpgm on scheme.opsalebud.prod_group = scheme.stkpgm.product_group
where cast(slperiod as integer)=@period_1 and slyear=right(@year_1,2)

order by region,product_group_a, ActualSales desc
GO

There are no defaults for SP parameters, and I could not find much in RDL.

syd_bloke
Starting Member

6 Posts

Posted - 2005-06-15 : 05:16:17

I worked it out, I declared the parameters in the IIF query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources