SET functions not working in reporting services

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

General SQL Server Forums

New to SQL Server Programming

SET functions not working in reporting services

Author

Topic

joblenis
Starting Member

29 Posts

Posted - 2007-08-10 : 13:10:05

Hello,

From starting SQL earlier this year I have come quite more familiar with it and getting into harder statements. I do have a question about using the set statement. It kinda works and kinda doesn't.

SCENERIO:

I have all my company's IIS Logs in various tables and I have statistic tables as well. I display all the data on various reporting service pages. All is good. I want to add functionality that allows me to sort the data by the various columns (IE: PageViews, Hits, Visitors, etc). So I made some stored procs and datasets to do this.

I have 2 datasets on my form. My main one with the data and one that calls a lookup table (SortBy) which lists the different methods the user can sort by.

If I run the proc from the data tab in reporting services, it prompts for the sortby and I select one and it works. However, if I go to view none of the columns show up, possibly because its in a set statement? Im not exactually sure.

Here are my procs and data:

MAIN PROC:

MODIFY PROCEDURE [Reporting].[CountrySummarySORT]
-- Add the parameters for the stored procedure here
@SortBy nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @sql nvarchar(1000)
-- Insert statements for procedure here
SET @sql = 'SELECT [countrycode]
,[countryname]
,"Hits"=sum([Hits])
,"PageViews"=sum([PageViews])
,"Visitors"=sum([Visitors])
,"ServerKB"=sum([ServerKB])
,"ClientKB"=sum([ClientKB])
,"TimeTaken"=sum([TimeTaken])
,"AverageTimeTaken"=dbo.getAverageTimeTaken( sum([timetaken]),sum([pageviews]))
,"AverageCount"=sum(case when pageviews >0 then 1 else 0 end)
FROM [IISLOG_REPORTS].[statistics].[countryYear] ss
left join [IP_TABLE].[dbo].[IPCountries] cou on ss.countryid=cou.countryid
GROUP BY [countrycode], [countryname]
ORDER BY ' + @SortBy

exec(@sql)

END

My SORTBY proc:

MODIFY PROCEDURE [dbo].[GetSortBy]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT SortByName
FROM lookups.SortBy
END

.. which gives 3 options (PageViews, Hits, Visitors)

As I said, when I run it from the data tab it works fine, but will not display on the form. I get this error "The value expression for #### refers to the field 'countryname'/etc

Any help would be great. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources