I am dealing with a report which must display records in columns. The number of columns is dynamic as is the number of fields (rows) which will be displayed.
For example, my data is structured as follows:
ID Name Price State Category etc...
1 Widget $100 AL Tools
2 Gadget $45 NY Accessories
3 Thingy $12.50 TX Tools
etc.
.
.
In order to convert this to a matrix, I have queried the data to return in the following format:
ID Attribute Value
1 Name Widget
1 Price $100
1 State AL
1 Category Tools
2 Name Gadget
2 Price $45
2 State NY
2 Category Accessories
3 Name Thingy
3 Price $12.50
3 State TX
3 Category Tools
etc...
So, I create a matrix in my report with 1 column and 1 row. The column is grouped on ID. The row is grouped on Attribute. The detail displays the Value. Giving me:
1 2 3 etc...Name Widget Gadget Thingy
Price $100 $45 $12.50
State AL NY TX
Category Tools Accessories Tools
etc...
This all works great and is very simple. The problem stems from the fact that I only want to display a limited number of columns on each page. Once 8 columns are displayed, I would like the matrix to skip to a new page, which redisplays my header. Unfortunately, the matrix just keeps expanding to the right and never wraps. Is there any way to work around this? Has anyone found a way to force a matrix to wrap and repeat the headers?
Thanks
Hello:
I have just sstayed away from Matrix'es in that I have no control. I have not found a way around your above example - maybe someone else knows but I sure don't - so I have just stayed with simple lists and have manipulated the data via a stored procedure with a "crosstab" or "pivot table" to generate the data (with respective columns) that I want and then within RS just layout the report visibility similar to a matrix.
A different subject for a different day but the same occurs within Analysis Services in that the horizontal presentation just extrapolates itself forever based upon the dimensions and columns - maybe I did not say that corectly but the same thing occurs.
So here is a nice little "generic" SQL that will allow you to present your row data as column data. A side Note - that is the ONLY issue I have with Reporting Services in that all data is always represented as a ROW - so this Stored Procedure will definitely help you control what you want to present.
There are some initial definitions you should deploy such as storing these parameters for each report within a table in SQL and if you want I can help you - but nevertheless here is the code and I rename this Stored Procedure for the name of my Reporting Services Report.
CREATE procedure Ex_CrossTab or whatever name you want (@.SQL varchar(2500),
@.PivotCol varchar(100),
@.Summaries varchar(100),
@.GroupBy varchar(100),
@.OtherFields varchar(150) = Null,
@.Debug bit = 0)
AS
set nocount on
set ansi_warnings off
declare @.Vals varchar(8000);
declare @.Vals2 varchar(8000);
declare @.Vals3 varchar(8000);
declare @.tmp varchar(1000);
declare @.TotalLen int;
set @.Vals = '';
set @.Vals2 = '';
set @.Vals3 = '';
set @.TotalLen = len(@.SQL) + len(@.GroupBy) + Len(ISNULL(@.OtherFields,''))
set @.OtherFields = isNull(', ' + @.OtherFields ,'')
create table #temp (Pivot varchar(100))
insert into #temp
exec ('select distinct convert(varchar(100),' + @.PivotCol + ') as Pivot FROM (' + @.SQL + ') A')
select @.tmp =
replace(replace(@.Summaries,'(','(CASE WHEN ' + @.PivotCol + '=''' + replace(Pivot,'''',''') +
''' THEN '),')[', ' END) as [' + Pivot ),
@.TotalLen = @.TotalLen + Len(@.tmp),
@.Vals = case when @.TotalLen < 7800 then @.Vals + ', ' + @.tmp else @.Vals end,
@.Vals2 = case when @.TotalLen between 7800 and 15799 then @.Vals2 + ', ' + @.tmp else @.Vals2 end,
@.Vals3 = case when @.TotalLen between 15800 and 23799 then @.Vals3 + ', ' + @.tmp else @.Vals3 end
from
#Temp
order by
Pivot
drop table #Temp
if (@.Debug=0)
exec ( 'select ' + @.GroupBy + @.OtherFields + @.Vals + @.Vals2 +
@.Vals3 + ' from (' + @.SQL + ') A GROUP BY ' + @.GroupBy)
else
begin
create table #Temp2 (SQLText Text);
insert into #Temp2 (SQLText)
values ('select ' + @.GroupBy + @.OtherFields + @.Vals + @.Vals2 +
@.Vals3 + ' from (' + @.SQL + ') A GROUP BY ' + @.GroupBy);
select * from #Temp2
end
set nocount off
set ansi_warnings on
GO
The above code executes your SQL statement as Dynamic SQL so make sure that you get rid of all blanks within your SQL because it is passed as a parameter to the above stored procedure. So use Query Analyzer and once again eliminate all "white" space and then pass your SQL statement as a parameter to the above stored procedure. Based upon your SQL Select you can eliminate the data that is returned thus "eliminating" your columns from extroploating within Reporting Services.
Definitions on how to use the above stored procedure are below:
Select - This is the SQL statement or table you are querying. It can be any valid SELECT statement, with JOINS, WHERE clauses, TOP, etc -- anything that you can cut and paste into query analyzer and execute. (And, in fact, you should always do this when testing things out).
