Populating Columns from a Dynamic Stored Procedure

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

Populating Columns from a Dynamic Stored Procedure

Author

Topic

Wozer
Starting Member

29 Posts

Posted - 2009-04-06 : 09:47:15

I just finished with a Dynamic SP (With some help from some members here) and I am trying to find out if it is possible to have dynamic columns on a SSRS report. The procedure I am using is the following:


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




ALTER  Procedure [dbo].[spOpenMonthly]
	@prmStartDate datetime,
	@prmEndDate datetime
AS

DECLARE @listCol VarChar(2000)
DECLARE @query VarChar(max)
SELECT @listCol = Stuff((SELECT DISTINCT
							'],[' + ltrim(Month_Open+ ' '+Year_Open)
							FROM ClaimsOpenView01
							WHERE EntryDate >= convert(varchar, @prmStartDate)
							AND EntryDate < convert(varchar, @prmEndDate+1)
							ORDER BY '],['+ ltrim(Month_Open+ ' '+Year_Open)
							FOR XML PATH('')
										), 1, 2, '') + ']'

SET @query=
'SELECT * FROM
	(SELECT Contract_Name,
		Year# =Month_Open +'+ ''' '''+' + Year_Open,
		ClaimNum
	FROM ClaimsOpenView01
	WHERE EntryDate >= '''+ convert(varchar, @prmStartDate)+'''
	AND EntryDate < '''+ convert(varchar, @prmEndDate+1)+''') src
PIVOT (count(ClaimNum) FOR Year# IN ('+@listCol+')) AS pvt'

EXECUTE (@Query)

What the sp does is get a date range from a user and then reports back the number of claims open by month for each contract. So if someone put in the date range of 1/1/09 to 4/1/09 you would get something back like:

Contract January 2009 February 2009 March 2009 April 2009

Con1 4 2 1 0

Con2 10 0 5 7

Con3 3 1 4 9

Obviously the month/year across the top is entirely dependent on what the user puts in. Is there any way to make it so the month/year will populate in the report when a user puts in the dates?

Thanks

Wozer
Starting Member

29 Posts

Posted - 2009-04-07 : 10:04:39

I changed around my stored procedure and View somewhat so that I would be able to use a matrix for dynamic columns. When I did this and started trying to set it up in SSRS I got the following error when I went to preview

An Error occurred during local report processing.
The definition of the report '/Open Claims' is invalid
The Group expression for the grouping 'matrix1_April_2006' refers to the field 'April_2006'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Does anyone have any idea what this means?

Wozer
Starting Member

29 Posts

Posted - 2009-04-07 : 11:28:45

I got this working somehow, it seemed to be a refresh that was needed (even though I had done that)

I still am interested if the first question I asked is possible or not.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 11:41:37

you can achieve this using matrix control in reports. you dont need to PIVOt the results from query. just bring data as it is from table and use year as column group and Contract_Name as row group

Wozer
Starting Member

29 Posts

Posted - 2009-04-07 : 12:59:31

I did get it working going through that route and reworking my entire query. One issue that I am having is if there is contract/Date combo that has no count, it is not showing up in the view, so when it gets onto the report instead of a 0 it is just blank. Is there any easy way to make blank cells fill in as 0's?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources