Multiple datasources

Hi,
I've written a report that uses a matrix to display some data that all
comes from a funky select statement. The report is month based, so
there is a where clause specifying the month in the select
The user now wants to add a Year to Date column. Because of the data
structure of the data i don't believe it is possible to pull the YTD
figure in the same statment.
Is it possible to have one of the columns in a matrix (or any other
control) that is based on a different dataset which takes a parameters
from the matrix row?
I'm guessing theres not, but I just want to make sure, theres alot of
smart people out there :)
Thanks
AndrewWhat you want to do is to investigate subreports. You can embed a subreport
into a column (I have done this). If the subreport returns multiple rows
then that column would go down multiple rows. In your case you will only be
returning a single row. You will want the subreport to be very simple
(obviously) since you are embeding it. Note that the subreport is called for
every row. What I do in this case is hide the report in list view so users
don't see it when selecting a report to run.
To develop a subreport you develop it like a normal report with parameters.
Make sure it works. Drag and drop the report into the column you want (add
an extra column/field that is blank and drop it into it). Then do a right
mouse click on the embeded subreport and select parameters to map the
subreport parameters to the appropriate field.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<ajharvey@.gmail.com> wrote in message
news:1114049920.896557.185450@.o13g2000cwo.googlegroups.com...
> Hi,
> I've written a report that uses a matrix to display some data that all
> comes from a funky select statement. The report is month based, so
> there is a where clause specifying the month in the select
> The user now wants to add a Year to Date column. Because of the data
> structure of the data i don't believe it is possible to pull the YTD
> figure in the same statment.
> Is it possible to have one of the columns in a matrix (or any other
> control) that is based on a different dataset which takes a parameters
> from the matrix row?
> I'm guessing theres not, but I just want to make sure, theres alot of
> smart people out there :)
> Thanks
> Andrew
>|||Thanks,
I'll give that a try

Multiple Columns in report Matrix?!

Hi there,

I'm trying to use a matrix to create report with the following format.

Location Carrier Period Total Total YTD

C1 C2 C3

Bangkok 1 2 3 6 5

CLT 1 1 1 3 5

Totals 9 10

I'm having trouble designing my report using a matrix. Any ideas how to add "total ytd" column and the totals below??

Thanks in advance,

Elias

Which version of SQL Server are you using?

With SQL2005 there is a PIVOT command that would allow you to produce a more conventional shape of report and then flip it.

|||

I'm using SQL reporting 2005. Could you please walk me through how to do that? Many thanks.

|||

Have you just got SQL2005 Reporting or have you got a backend SQL2005 database?

|||

Hello I had the same problem. I just adjusted the body to allow 2 text fields at the top of the colum and put my Sum expression in each of them for those colums. It gives the totals at the top but that can be a good thing if some one is looking for the totals and doesn't want to scroll to the bottom of your report.

|||

using both SSRS and SQL Server 2005

|||

Could you please give more details on how to do this? Still no able to solve my problem. Thanks

|||

The solution to creating the required matrix, will require some different thinking.

Location Carrier Period Total
C1 C2 C3 Total YTD
Bangkok 1 2 3 6 5
CLT 1 1 1 3 5
Totals 9 10

Is the number of carriers fixed? If yes then the problem simplifies enormously, as there besides using a dynamic temporary table (dynamic in the sense that when an extra carrier appears an additional column will be created), a new table can be created as an intermediate in the analysis. This can be either a table within the database or an ordinary temporary table. The SELECT to read this can include a UNION to a SUM on PeriodTotal and TotalYTD as in
SELECT 'A', Location, CONVERT(VARCHAR(10), C1) AS C1,
CONVERT(VARCHAR(10), C2) AS C2,
CONVERT(VARCHAR(10), C3) AS C3, PeriodTotal, TotalYTD FROM Fred
UNION
SELECT 'B', '' AS LOCATION, '' AS C1, '' AS C2, 'Totals' AS C3, SUM(PeriodTotal), SUM(TotalYTD) FROM FRED
ORDER BY 1, 2

|||

Neither the location nor carrier are fixed. Thanks!

|||

>Neither the location nor carrier are fixed.

Since their numbers are not fixed. you will need to adopt an array of arrays approach to accumulate the data and then generate the HTML yourself. The generation of the HTML sounds complicated, but once you make a mockup of what you want to generate, it becomes quite simple.

Multiple Column Reports Problem

Have a report containing a single matrix. Have narrowed the matrix to
2.5 inches wide and specified 2 columns in the report layout.
I am aware that previewing the report will only display data in the
first column and that's what it does. However, when I Print Preview or
Export to PDF, I still only see data in the first column.
What am I missing? Thanks.
JeffCorrection: the report contains a table, not a matrix. Any advice
would be greatly appreciated.|||"JeffW" <jwilson@.telnetww.com> wrote in message
news:1112223102.818689.231190@.o13g2000cwo.googlegroups.com...
> Have a report containing a single matrix. Have narrowed the matrix to
> 2.5 inches wide and specified 2 columns in the report layout.
> I am aware that previewing the report will only display data in the
> first column and that's what it does. However, when I Print Preview or
> Export to PDF, I still only see data in the first column.
> What am I missing? Thanks.
> Jeff
Have you added content to the second column? Or is it conditional or
something?
Wrong font color?
Sounds weird.
Kaisa M. Lindahl|||Not sure I follow. It's not a problem displaying a column inside the
table. I want the report itself to display as two columns on the page.|||"JeffW" <jwilson@.telnetww.com> wrote in message
news:1112392187.972596.312270@.z14g2000cwz.googlegroups.com...
> Not sure I follow. It's not a problem displaying a column inside the
> table. I want the report itself to display as two columns on the page.
Not sure if I'm following you either.
Have you deployed your report to Report Manager? Does it show one or two
columns there?
If you know it won't display more than one column during preview, I don't
think it will display any more than what is there if you export the preview
to PDF. The pdf just renders what's in the webpage to the pdf format.
Kaisa|||Largely solved my problem. Must set the following to display 2 columns
on 1 page:
1. Report parameter columns=2 (obviously)
2. Report Body parameter width=3in or so (less obvious). Must set the
body width to correspond to the column. Otherwise, the body fills the
page and only contains one column.
Note that the report preview still displays only one column. Also the
Report Manager still only displays the report with one column.
Exporting to "HTML with Office Web Components" still only displays one
column. However, exporting to PDF or TIFF generates the report I'm
looking for -- 2 columns on a single page.
For what I need, this will do the trick. However, not seeing 2 columns
in the Report Manager is a bit inconvenient, not being WYSIWYG and all.
Any suggestions here?
Thanks.