2-Dimensional reports.

I have a number of reports to implement that have the following
characteristics:
1) column headers come from database tables - these are normally 4 to 10
items. These tables consist of 1 text field and a record-id field.
2) rows come from other tables according to specified selection criteria.
3) Row summaries based on some calculations are included in a last column
4) group and final calculations are required.
I've implemented one report using a multi-join SQL statement with a GROUP BY
statement and a CUBE statement, putting the results into a Grid report. This
worked except that the final total also shows details that I don't want on
the report. I've been able to blank out the rows for these entries, but
wasn't able to get rid of the rows themselves in the report, so that the
Final total follows a number of blank lines after the last Group-level
subtotal.
Is there any technique that would allow me to use a Table-report and fill in
the column headers from one DataSet in an extensible manner, and then fill in
the row data from a different dataset?You should be able to use aggregates from another data set in the headers of
a table. You say =First(Fields!Header.Value, "headerdataset").
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Altair" <Altair@.discussions.microsoft.com> wrote in message
news:6CA5C7DE-E2D2-4968-A070-9B47D2354518@.microsoft.com...
>I have a number of reports to implement that have the following
> characteristics:
> 1) column headers come from database tables - these are normally 4 to 10
> items. These tables consist of 1 text field and a record-id field.
> 2) rows come from other tables according to specified selection criteria.
> 3) Row summaries based on some calculations are included in a last column
> 4) group and final calculations are required.
> I've implemented one report using a multi-join SQL statement with a GROUP
> BY
> statement and a CUBE statement, putting the results into a Grid report.
> This
> worked except that the final total also shows details that I don't want on
> the report. I've been able to blank out the rows for these entries, but
> wasn't able to get rid of the rows themselves in the report, so that the
> Final total follows a number of blank lines after the last Group-level
> subtotal.
> Is there any technique that would allow me to use a Table-report and fill
> in
> the column headers from one DataSet in an extensible manner, and then fill
> in
> the row data from a different dataset?|||In trying your suggestion, I only get the first element from the dataset.
I've not been successfull, in trying to populate the rest of the column
headers with successive values from the header dataset. From what I've found
in the documentation, there are First(), and a Last() aggregate functions,
but nothing to allow you to iterate through the values.
"Brian Welcker [MSFT]" wrote:
> You should be able to use aggregates from another data set in the headers of
> a table. You say =First(Fields!Header.Value, "headerdataset").
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Altair" <Altair@.discussions.microsoft.com> wrote in message
> news:6CA5C7DE-E2D2-4968-A070-9B47D2354518@.microsoft.com...
> >I have a number of reports to implement that have the following
> > characteristics:
> > 1) column headers come from database tables - these are normally 4 to 10
> > items. These tables consist of 1 text field and a record-id field.
> > 2) rows come from other tables according to specified selection criteria.
> > 3) Row summaries based on some calculations are included in a last column
> > 4) group and final calculations are required.
> >
> > I've implemented one report using a multi-join SQL statement with a GROUP
> > BY
> > statement and a CUBE statement, putting the results into a Grid report.
> > This
> > worked except that the final total also shows details that I don't want on
> > the report. I've been able to blank out the rows for these entries, but
> > wasn't able to get rid of the rows themselves in the report, so that the
> > Final total follows a number of blank lines after the last Group-level
> > subtotal.
> >
> > Is there any technique that would allow me to use a Table-report and fill
> > in
> > the column headers from one DataSet in an extensible manner, and then fill
> > in
> > the row data from a different dataset?
>
>