Complex report, I think

Hi,
I have a report that is basically a Matrix report that has totalling rows
throughout the vertical aspect of the report (not just at the end) and a
totalling columns at the end. Can Reporting Services deal with this using
the toolbox and so on, or do I need to get coding? I am pretty sure the
wizard aint going to help. I have split my data into a number of datasets
and hidden the column headings on all but the top Matrix, but then how do I
add the totalling rows? Especially as they need to be the sum of a number
of fields up the length of the column that aren't even contiguous. I
basically want to add totals at various places that I define and define
which values should be added. I know that there will always be the same row
and column headings popping up each time, so from that side there is no
problem.
Please give me some direction on this as I am sort of looking at this a bit
:-s at the moment.
TIA,
JarrydOn Jul 3, 5:23 am, "Jarryd" <jar...@.community.nospam> wrote:
> Hi,
> I have a report that is basically a Matrix report that has totalling rows
> throughout the vertical aspect of the report (not just at the end) and a
> totalling columns at the end. Can Reporting Services deal with this using
> the toolbox and so on, or do I need to get coding? I am pretty sure the
> wizard aint going to help. I have split my data into a number of datasets
> and hidden the column headings on all but the top Matrix, but then how do I
> add the totalling rows? Especially as they need to be the sum of a number
> of fields up the length of the column that aren't even contiguous. I
> basically want to add totals at various places that I define and define
> which values should be added. I know that there will always be the same row
> and column headings popping up each time, so from that side there is no
> problem.
> Please give me some direction on this as I am sort of looking at this a bit
> :-s at the moment.
> TIA,
> Jarryd
The most common way I have dealt w/this same issue is to use either
cursors or while loops to loop through the dataset in the stored
procedure/query that is sourcing the report (usually one cursor/while
loop per desired subtotal/etc). It can get a bit messy; however, it
does get the job done. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi,
Yeah I suppose that is the way. Just kind of a bit long winded and I was
just hoping I could get the basic Matrix and use tools to add things like
totaling rows and columns in with a bit more control and flexability as what
is available. Cos what I am going to have to do now means that RS isn't
really going to do much more than add some colour to cells and gridlines.
TIA,
Jarryd
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1183468763.165468.188890@.m36g2000hse.googlegroups.com...
> On Jul 3, 5:23 am, "Jarryd" <jar...@.community.nospam> wrote:
>> Hi,
>> I have a report that is basically a Matrix report that has totalling rows
>> throughout the vertical aspect of the report (not just at the end) and a
>> totalling columns at the end. Can Reporting Services deal with this
>> using
>> the toolbox and so on, or do I need to get coding? I am pretty sure the
>> wizard aint going to help. I have split my data into a number of
>> datasets
>> and hidden the column headings on all but the top Matrix, but then how do
>> I
>> add the totalling rows? Especially as they need to be the sum of a
>> number
>> of fields up the length of the column that aren't even contiguous. I
>> basically want to add totals at various places that I define and define
>> which values should be added. I know that there will always be the same
>> row
>> and column headings popping up each time, so from that side there is no
>> problem.
>> Please give me some direction on this as I am sort of looking at this a
>> bit
>> :-s at the moment.
>> TIA,
>> Jarryd
>
> The most common way I have dealt w/this same issue is to use either
> cursors or while loops to loop through the dataset in the stored
> procedure/query that is sourcing the report (usually one cursor/while
> loop per desired subtotal/etc). It can get a bit messy; however, it
> does get the job done. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||Doesn't help you now but RS 2008 will have a lot more functionality around
both tables and matrixes.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jarryd" <jarryd@.community.nospam> wrote in message
news:%23LyQVYYvHHA.4012@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Yeah I suppose that is the way. Just kind of a bit long winded and I was
> just hoping I could get the basic Matrix and use tools to add things like
> totaling rows and columns in with a bit more control and flexability as
> what is available. Cos what I am going to have to do now means that RS
> isn't really going to do much more than add some colour to cells and
> gridlines.
> TIA,
> Jarryd
> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
> news:1183468763.165468.188890@.m36g2000hse.googlegroups.com...
>> On Jul 3, 5:23 am, "Jarryd" <jar...@.community.nospam> wrote:
>> Hi,
>> I have a report that is basically a Matrix report that has totalling
>> rows
>> throughout the vertical aspect of the report (not just at the end) and a
>> totalling columns at the end. Can Reporting Services deal with this
>> using
>> the toolbox and so on, or do I need to get coding? I am pretty sure the
>> wizard aint going to help. I have split my data into a number of
>> datasets
>> and hidden the column headings on all but the top Matrix, but then how
>> do I
>> add the totalling rows? Especially as they need to be the sum of a
>> number
>> of fields up the length of the column that aren't even contiguous. I
>> basically want to add totals at various places that I define and define
>> which values should be added. I know that there will always be the same
>> row
>> and column headings popping up each time, so from that side there is no
>> problem.
>> Please give me some direction on this as I am sort of looking at this a
>> bit
>> :-s at the moment.
>> TIA,
>> Jarryd
>>
>> The most common way I have dealt w/this same issue is to use either
>> cursors or while loops to loop through the dataset in the stored
>> procedure/query that is sourcing the report (usually one cursor/while
>> loop per desired subtotal/etc). It can get a bit messy; however, it
>> does get the job done. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>|||Fair enough, Rome wasn't built in a day.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:uNDwjfYvHHA.4552@.TK2MSFTNGP03.phx.gbl...
> Doesn't help you now but RS 2008 will have a lot more functionality around
> both tables and matrixes.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jarryd" <jarryd@.community.nospam> wrote in message
> news:%23LyQVYYvHHA.4012@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> Yeah I suppose that is the way. Just kind of a bit long winded and I was
>> just hoping I could get the basic Matrix and use tools to add things like
>> totaling rows and columns in with a bit more control and flexability as
>> what is available. Cos what I am going to have to do now means that RS
>> isn't really going to do much more than add some colour to cells and
>> gridlines.
>> TIA,
>> Jarryd
>> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
>> news:1183468763.165468.188890@.m36g2000hse.googlegroups.com...
>> On Jul 3, 5:23 am, "Jarryd" <jar...@.community.nospam> wrote:
>> Hi,
>> I have a report that is basically a Matrix report that has totalling
>> rows
>> throughout the vertical aspect of the report (not just at the end) and
>> a
>> totalling columns at the end. Can Reporting Services deal with this
>> using
>> the toolbox and so on, or do I need to get coding? I am pretty sure
>> the
>> wizard aint going to help. I have split my data into a number of
>> datasets
>> and hidden the column headings on all but the top Matrix, but then how
>> do I
>> add the totalling rows? Especially as they need to be the sum of a
>> number
>> of fields up the length of the column that aren't even contiguous. I
>> basically want to add totals at various places that I define and define
>> which values should be added. I know that there will always be the
>> same row
>> and column headings popping up each time, so from that side there is no
>> problem.
>> Please give me some direction on this as I am sort of looking at this a
>> bit
>> :-s at the moment.
>> TIA,
>> Jarryd
>>
>> The most common way I have dealt w/this same issue is to use either
>> cursors or while loops to loop through the dataset in the stored
>> procedure/query that is sourcing the report (usually one cursor/while
>> loop per desired subtotal/etc). It can get a bit messy; however, it
>> does get the job done. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>>
>

Complex expression in matrix subtotal

Hello.
How can I make an expression in matrix subtotal as (sum1) & "/" & (sum2)?
Thank's.=Sum(Fields!Field1.Value) / Sum(Fields!Field2.Value)
If you want something other than this in the detail cells, you'll need to
use the InScope function. Something like this:
=iif(InScope("matrix1_Category"),Sum(Fields!Amount.Value),Sum(Fields!Amount.
Value)/Sum(Fields.ItemCount.Value))
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Efim" <Efim@.discussions.microsoft.com> wrote in message
news:587C7384-4DC7-492F-84C5-CD895512FAC9@.microsoft.com...
> Hello.
> How can I make an expression in matrix subtotal as (sum1) & "/" & (sum2)?
> Thank's.|||Thanks.
"Chris Hays [MSFT]" wrote:
> =Sum(Fields!Field1.Value) / Sum(Fields!Field2.Value)
> If you want something other than this in the detail cells, you'll need to
> use the InScope function. Something like this:
> =iif(InScope("matrix1_Category"),Sum(Fields!Amount.Value),Sum(Fields!Amount.
> Value)/Sum(Fields.ItemCount.Value))
> --
> My employer's lawyers require me to say:
> "This posting is provided 'AS IS' with no warranties, and confers no
> rights."|||As indicated by Chris, you would use the InScope function. The subtotal
column is not in the scope of the detail columns of the group. E.g.
=iif(InScope("matrix1_Category"), Sum(Fields!Amount.Value),
Avg(Fields!Amount.Value))
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Shai" <Shai@.discussions.microsoft.com> wrote in message
news:E2641A91-9CAF-4C01-8693-A5BB142CCD29@.microsoft.com...
> Hi Chris !
> When you add subtotal to matrix it's add a column but you don't have
access to the expression of the actual calculation. I would like my "Total
column" to calculate Average of the columns on the left. How is this
possible?
> TIA
> Shai
> "Chris Hays [MSFT]" wrote:
> > =Sum(Fields!Field1.Value) / Sum(Fields!Field2.Value)
> >
> > If you want something other than this in the detail cells, you'll need
to
> > use the InScope function. Something like this:
> >
> >
=iif(InScope("matrix1_Category"),Sum(Fields!Amount.Value),Sum(Fields!Amount.
> > Value)/Sum(Fields.ItemCount.Value))
> >
> > --
> > My employer's lawyers require me to say:
> > "This posting is provided 'AS IS' with no warranties, and confers no
> > rights."
> >
> > "Efim" <Efim@.discussions.microsoft.com> wrote in message
> > news:587C7384-4DC7-492F-84C5-CD895512FAC9@.microsoft.com...
> > > Hello.
> > > How can I make an expression in matrix subtotal as (sum1) & "/" &
(sum2)?
> > > Thank's.
> >
> >
> >|||Hi Robert,
I tried this option and i am getting #Error in the data column.Moreover i am
getting the below error on the preview of the report.
Aggregate functions other than First, Last, Previous, Count, and
CountDistinct can only aggregate data of a single data type.
--
Regards,
Raja
â'Smile is a curve that sets everything straight"
"Robert Bruckner [MSFT]" wrote:
> As indicated by Chris, you would use the InScope function. The subtotal
> column is not in the scope of the detail columns of the group. E.g.
> =iif(InScope("matrix1_Category"), Sum(Fields!Amount.Value),
> Avg(Fields!Amount.Value))
> See also:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Shai" <Shai@.discussions.microsoft.com> wrote in message
> news:E2641A91-9CAF-4C01-8693-A5BB142CCD29@.microsoft.com...
> > Hi Chris !
> >
> > When you add subtotal to matrix it's add a column but you don't have
> access to the expression of the actual calculation. I would like my "Total
> column" to calculate Average of the columns on the left. How is this
> possible?
> >
> > TIA
> > Shai
> >
> > "Chris Hays [MSFT]" wrote:
> >
> > > =Sum(Fields!Field1.Value) / Sum(Fields!Field2.Value)
> > >
> > > If you want something other than this in the detail cells, you'll need
> to
> > > use the InScope function. Something like this:
> > >
> > >
> =iif(InScope("matrix1_Category"),Sum(Fields!Amount.Value),Sum(Fields!Amount.
> > > Value)/Sum(Fields.ItemCount.Value))
> > >
> > > --
> > > My employer's lawyers require me to say:
> > > "This posting is provided 'AS IS' with no warranties, and confers no
> > > rights."
> > >
> > > "Efim" <Efim@.discussions.microsoft.com> wrote in message
> > > news:587C7384-4DC7-492F-84C5-CD895512FAC9@.microsoft.com...
> > > > Hello.
> > > > How can I make an expression in matrix subtotal as (sum1) & "/" &
> (sum2)?
> > > > Thank's.
> > >
> > >
> > >
>
>

Compatible versions of Ms SQL Server, ODBC driver, Driver Manager and ODBC API s

Hi,

Please help share with me if you know the version compatibility matrix of Ms SQL Server, ODBC driver (sqlsrv32.dll), Driver Manager (odbc32.dll) and ODBC API spec. For instance, how can I know Ms SQL Server 2000 can work with which version of sqlsrv32.dll, a particular version of sqlsrv32.dll can work with which version of odbc32.dll and a certain version of sqlsrv32.dll/odbc32.dll conforms to which version of ODBC API spec (e.g. 3.5).

Any help will be appreciated.

Thanks,

vtluu.Instead of sweating bullets trying to juggle chainsaws, why not just use the current MDAC (http://msdn.microsoft.com/data/mdac/)?

-PatP

Comparing values between 2 matrices (matrix)

Hello
I have two matrices. One contains sales data for the current year, the other prior year. Both matrices use different data sets

I'd like to compare the two - possibly by creating a third matrix that subtracts prior year from current year.

Any ideas? When I create a third matrix and substitute a formula like =sum(values, "Data source for matrix 1") - sum(values, "Data source for matrix 2"), the resultant matrix subtracts the grand total from the first matrix - not the individual "cell".

Any suggestions are appreciated.
Thanks

I would like to know the official answer to this too...

see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1673719&SiteID=1 for simular question...