dynamic table columns

I have a sproc that returns sales amount for each month in a given range of
dates. The date range is over the last 8 years, and will grow automatically
as time goes by.
I really do not want to create a table in RS with over 120 columns of data
and then set each of them to display/hide based on the parameters passed.
Matrix table doesnt seem to work for me as I dont want to un-pivot my data
just so it can pivot it back out.
Is there some solution to this problem? I have 5 base columns that will show
regardless of what yeear/month is passed, and usually anywhere from 12 to 48
result columns. is there a way for the table to recognize how many columns
are in the result set and create enough columns automagically?
Thanks!!On Dec 6, 4:03 pm, Carl Henthorn
<CarlHenth...@.discussions.microsoft.com> wrote:
> I have a sproc that returns sales amount for each month in a given range of
> dates. The date range is over the last 8 years, and will grow automatically
> as time goes by.
> I really do not want to create a table in RS with over 120 columns of data
> and then set each of them to display/hide based on the parameters passed.
> Matrix table doesnt seem to work for me as I dont want to un-pivot my data
> just so it can pivot it back out.
> Is there some solution to this problem? I have 5 base columns that will show
> regardless of what yeear/month is passed, and usually anywhere from 12 to 48
> result columns. is there a way for the table to recognize how many columns
> are in the result set and create enough columns automagically?
> Thanks!!
I guess I'm failing to see why you have to return all of the data
unaggregated to RS. Why not just limit the amount of months by
passing the correct parameters to your stored procedure? Then you
could just let the Matrix do its job and dynamically create the
columns.
If you absolutely have to do it this way, go to the Matrix Properties,
Filter tab, and add some log here to determine which of the Months
(that Group) you want to see. This will have the same effect as
setting the Visible on the columns. It will not speed up the data
fetch portion of your report.
If you could provide a little more detail as to what you are actually
trying to do, it would be helpful. What kind of groupings do you have
in the rows, since Months are your columns? Are you manually creating
multiple Rows, each with a different field in your dataset? There are
many ways to get to the same outcome.
-- Scott