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?
>
>

2005: Publishing (not exporting) Report in Excel format

My end users want me to post 2 versions of my 2005 Reporting Services
reports. One that when they click on it automatically opens in Excel w/o
grouping...sort of a data dump. Then a second that is grouped, formatted and
pretty.
I know that I can have them export the report once run into Excel, but they
don't want that. (of course...;) So, does anyone know how to publish in
straight Excel in Reporting Services?
thanks in advance for any help!
MachelleYou didn't say if you are using Report Manager or not. The best way is to
use jump to URL and render in CSV ASCII format. The default for CSV is
unicode which Excel opens up with all the data in its own column.
Depending on your report you may or may not want to have a duplicate report
that you have cleaned up and call that one rather than the pretty one.
Regardless, if you have any data size at all URL is much faster.
If you are using Report Manager you could have a report that has no data,
just the parameters and then textboxes, one for pretty, one for Data Export
(I call it that in my link to differentiate that it is a data export, not
the pretty Excel export).
Note, the fields texboxes of the report have a name property that is set
during design to the name of the field. You can give it friendlier names if
you want.
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
Very nice and very fast.
Bruce Loehle-Conger
"Machelle" <Machelle@.discussions.microsoft.com> wrote in message
news:A5360BDE-440C-4AAF-8D07-46A9D3472567@.microsoft.com...
> My end users want me to post 2 versions of my 2005 Reporting Services
> reports. One that when they click on it automatically opens in Excel w/o
> grouping...sort of a data dump. Then a second that is grouped, formatted
> and
> pretty.
> I know that I can have them export the report once run into Excel, but
> they
> don't want that. (of course...;) So, does anyone know how to publish in
> straight Excel in Reporting Services?
> thanks in advance for any help!
> Machelle