Calculations in Matrix report

I have a Report Designer problem, whereby I need to perform a calculation on
each row of data laid out in a crosstab.
My datasource is a flat xml file (read into a dataset), and consists of the
following repeat base units:-
<base>
<StartDate>2006-04-10</StartDate>
<EndDate>2006-04-17</EndDate>
<Category>Books</Category>
<Amount>100</Amount>
</base>
<base>
<StartDate>2006-04-18</StartDate>
<EndDate>2006-04-25</EndDate>
<Category>CDs</Category>
<Amount>200</Amount>
</base>
...etc
I need to present this in the following way:-
Week 1 Week 2
10th-17th April 18th-25th April Calculation
Category
Books 100 300 week2 - week1
CDs 200 400 week2 - week1
..etc
The two options I have in the designer toolbox are:-
1) To use a matrix - this allows variable column data (period start date to
period end date), with variable row data (category), to be laid out as a
crosstab, but I can find no way of identifying individual cells within the
matrix to use in the calculation at the end of each row. Matrices only
appear to support totals/subtotals'
2) To use a table - is it possible to present the repeat base units shown
above in the form of a table? I can find the max EndDate in the dataset, but
is there some method of filtering the data so I can work out (period + 1
week), (period + 2 weeks), etc and then reference individual cells in the
table for the calculation'
Neither approach appears to allow crosstab representation of the data, with
calculations on each row. Is this possible?
I would like to avoid having to assemble the raw data in the form of fixed
'buckets', i.e.
<base>
<Category>Books</Category>
<Period1>2006-04-10</Period1>
<Amount1>100</Amount1>
<Period2>2006-04-18</Period2>
<Amount2>200</Amount2>
<Period3>2006-04-26</Period3>
<Amount3>400</Amount3>
</base>
<base>
<Category>CDs</Category>
<Period1>2006-04-10</Period1>
<Amount1>100</Amount1>
<Period2>2006-04-18</Period2>
<Amount2>200</Amount2>
<Period3>2006-04-26</Period3>
<Amount3>400</Amount3>
</base>
...etc
Can anyone see a solution?Hi Pete,
Thank you for using MSDN Managed Newsgroup Support.
From your description, my undestanding of this issue is : You want to add a
Calculation column in the Matrix or Table region. If I misunderstood your
concern, please feel free to let me know.
Based on my scope, I think you could add a calculated field in the dataset
and then try to present this field in the layout:
1. In the Data page in the Report Designer, click the Edit Selected dataset
button.
2. In the Dataset Dialogbox, please click the Fields Page and then type the
Field name, choose the Type as Calculated Field and then type the
Expression you want to use.
After doing this, I think you could put the calculated field in any data
region.
Hope this information will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for your prompt response Wei.
Unfortunately, my issue is not as simple as adding a calculation to the
matrix/table.
My data is represented by a crosstab. I wish to add a calculation column,
which is an expression that references two preceding cells in the same row,
for example...
---Calculation
Row1 Cell1 Cell2 Cell3 Cell4 Cell4/Cell3
---
This does not appear possible with a matrix. And because I have variable
columns, it does not appear possible to lay the data out in tabular form
either (which would allow me to reference individual cells).
Can you see a solution to this more complex issue?
Thanks
Pete
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:SaUsE8MoGHA.2028@.TK2MSFTNGXA01.phx.gbl...
> Hi Pete,
> Thank you for using MSDN Managed Newsgroup Support.
> From your description, my undestanding of this issue is : You want to add
> a
> Calculation column in the Matrix or Table region. If I misunderstood your
> concern, please feel free to let me know.
> Based on my scope, I think you could add a calculated field in the dataset
> and then try to present this field in the layout:
> 1. In the Data page in the Report Designer, click the Edit Selected
> dataset
> button.
> 2. In the Dataset Dialogbox, please click the Fields Page and then type
> the
> Field name, choose the Type as Calculated Field and then type the
> Expression you want to use.
> After doing this, I think you could put the calculated field in any data
> region.
> Hope this information will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Pete,
Thank you for the update.
I would like to know what do you mean about the variable columns. Since
Reporting Services use dataset to get the data from the datasource, I would
like to know how you implement variable columns in the dataset.
If the field name does not consist, I don't think you could refer any field
in the Expression. So please let me know the exactly meaning of "variable
columns" so that I may provide further assistance. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei,
The dataset generated by our datasource consists of the following fields:
StartDate
EndDate
Category
Amount
i.e. the data is periodic (weekly). We need to present this as follows:
Week 1 Week 2 Week 3
10th-17th April 18th-25th April 26th-3rd
Calculation
Category
Books 100 300 200
week3 - week2
CDs 200 400 150
week3 - week2
..etc
We can do this using a matrix, with 3 groupings: Category (row), StartDate
(column), EndDate (column).
What I meant was the columns are variable in Start/EndDate. But we know no
way of performing the calculation. Is this possible?
Or is there a way to do it if we use a table instead of a matrix?
Thanks in advance
Pete
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:7vnx8XOoGHA.2028@.TK2MSFTNGXA01.phx.gbl...
> Hi Pete,
> Thank you for the update.
> I would like to know what do you mean about the variable columns. Since
> Reporting Services use dataset to get the data from the datasource, I
> would
> like to know how you implement variable columns in the dataset.
> If the field name does not consist, I don't think you could refer any
> field
> in the Expression. So please let me know the exactly meaning of "variable
> columns" so that I may provide further assistance. Thank you!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Sorry, my last post might be confusing... the matrix should look like:
Week 1 Week 2 Week 3
10th-17th 18th-25th 26th-3rd Calculation
Category
Books 100 300 200 week3 - week2
CDs 200 400 150 week3 - week2
..etc
"Pete D" <peted@.community.nospam> wrote in message
news:u7enVZQoGHA.4176@.TK2MSFTNGP05.phx.gbl...
> Hi Wei,
> The dataset generated by our datasource consists of the following fields:
> StartDate
> EndDate
> Category
> Amount
> i.e. the data is periodic (weekly). We need to present this as follows:
> Week 1 Week 2 Week 3
> 10th-17th April 18th-25th April 26th-3rd
> Calculation
> Category
> Books 100 300 200
> week3 - week2
> CDs 200 400 150
> week3 - week2
> ..etc
> We can do this using a matrix, with 3 groupings: Category (row), StartDate
> (column), EndDate (column).
> What I meant was the columns are variable in Start/EndDate. But we know no
> way of performing the calculation. Is this possible?
> Or is there a way to do it if we use a table instead of a matrix?
> Thanks in advance
> Pete
>
> "Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
> news:7vnx8XOoGHA.2028@.TK2MSFTNGXA01.phx.gbl...
>> Hi Pete,
>> Thank you for the update.
>> I would like to know what do you mean about the variable columns. Since
>> Reporting Services use dataset to get the data from the datasource, I
>> would
>> like to know how you implement variable columns in the dataset.
>> If the field name does not consist, I don't think you could refer any
>> field
>> in the Expression. So please let me know the exactly meaning of "variable
>> columns" so that I may provide further assistance. Thank you!
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>|||Hi Pete,
Thank you for the update. I would like to get your report file to do more
research.
Would you pleae send the report file to me?
You may send to me directly. I understand the information may be sensitive
to you, my direct email address is weilu@.ONLINE.microsoft.com ( Please
remove ONLINE when you send the email ), you may send the file to me
directly and I will keep it secure.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
Are you able to solve this issue with Matrix, i have the similar kind
of requirement. If yes pls let me know how could you achieve it
FYI: my req is as mentioned( hope i am clear, else let me know)
Thanks in advance..
Venkat
*******************
Hello,
I am using matrix control for which the rows, columns are all dynamic(
based on the group i specify). I am able to get the Subtotal for both
the rows and colums, which is Great!!..
my problem is i need to add more columns(some formula columns) which
might have "% difference", "varience" etc..
the report should look something like this (M1, M2 are model_id's)
Div/Sec M1 M2 Variance % Difference
----==AD--
O3580 0.71 1.47 -0.76 -107.04
8040 1.33 1.33 0 0
8110 9.98 14.47 -4.49 -44.99
11210 5.44 6.36 -0.92 -16.91
and my query is :
select div_sec, model_ID, model_time from table1
the output of my query is below( seperated by commas)
div_sec, model_ID, model_time
O3580,M1,0.7
O3580,M2,1.47
8040 ,M1,1.33
8040 ,M2,1.33
8110 ,M1,9.98
8110 ,M2,14.47
11210 ,M1,5.44
11210 ,M2,6.36
Any help is greatly appreciated.
Thanks,
Venkat
Wei Lu [MSFT] wrote:
> Hi Pete,
> Thank you for the update. I would like to get your report file to do more
> research.
> Would you pleae send the report file to me?
> You may send to me directly. I understand the information may be sensitive
> to you, my direct email address is weilu@.ONLINE.microsoft.com ( Please
> remove ONLINE when you send the email ), you may send the file to me
> directly and I will keep it secure.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D
> This posting is provided "AS IS" with no warranties, and confers no right=s=2E|||Hi Pete,
Thank you for the file.
After some research, I found that we could not meet the requirement in
Matrix. The Static column in Matrix does not meet your requirement.
As for the table control, it is not suitable to use table as a dynamic
columns data region.
I will try to contact internal to check whether there is an suitable
workaround for you. I appreciate your patience.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei,
Appreciate if you can look into my problem too(its up in this thread)..
I think its almost the same.
Let me know if you find any solution for this issue. This is a Top
reqirement for lots of my reports.
Thanks,
Venkat
Wei Lu [MSFT] wrote:
> Hi Pete,
> Thank you for the file.
> After some research, I found that we could not meet the requirement in
> Matrix. The Static column in Matrix does not meet your requirement.
> As for the table control, it is not suitable to use table as a dynamic
> columns data region.
> I will try to contact internal to check whether there is an suitable
> workaround for you. I appreciate your patience.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.|||I've posted details of my report/requirement to Wei to determine whether
there's a solution to my problem.
Its seems that Venkat and "dbro101" both have very similar issues (re:
separate postings on 12th July)
If I become aware of a solution I'll post details here.
Pete
<venkat.oar@.gmail.com> wrote in message
news:1152883485.115457.194350@.h48g2000cwc.googlegroups.com...
> Hi Wei,
> Appreciate if you can look into my problem too(its up in this thread)..
> I think its almost the same.
> Let me know if you find any solution for this issue. This is a Top
> reqirement for lots of my reports.
> Thanks,
> Venkat
> Wei Lu [MSFT] wrote:
>> Hi Pete,
>> Thank you for the file.
>> After some research, I found that we could not meet the requirement in
>> Matrix. The Static column in Matrix does not meet your requirement.
>> As for the table control, it is not suitable to use table as a dynamic
>> columns data region.
>> I will try to contact internal to check whether there is an suitable
>> workaround for you. I appreciate your patience.
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>|||Hi Pete,
Thank you for your followup. Since Wei is absent due to some urgent issues,
I'm continueing to help you on this issue. I've got the problem description
and sample report files from wei and will perform some research on them. I
will update you as soon as possible.
Thank you for the patience and understanding.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead|||Hello Pete,
Sorry for keep you waiting. After some further research, we've come out to
the same results as Wei has told you in the previous message, the built-in
reporting service report items(Table or Matrix) are not able to accomplish
the task. I've also involved some other SQL engineers to discuss the issue
and they've help confirmed on this. So far they pointed out that it may be
possible to use a custom data processing extension to precaculate the
additional results, and the key point is that you need to correctly pass
the parameters into the custom data processing engine. I'm sorry that we
haven't been able to help you further on this. If there is anything else we
can help, please feel free to let me know.
Thanks for your understanding!
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi
I was just wondering if there was any progress regarding this subject.
I am sitting with a similar problem and am unable to find a workaround.
Thanks for all the effort that has been put into solving the problem thus far.
"Steven Cheng[MSFT]" wrote:
> Hello Pete,
> Sorry for keep you waiting. After some further research, we've come out to
> the same results as Wei has told you in the previous message, the built-in
> reporting service report items(Table or Matrix) are not able to accomplish
> the task. I've also involved some other SQL engineers to discuss the issue
> and they've help confirmed on this. So far they pointed out that it may be
> possible to use a custom data processing extension to precaculate the
> additional results, and the key point is that you need to correctly pass
> the parameters into the custom data processing engine. I'm sorry that we
> haven't been able to help you further on this. If there is anything else we
> can help, please feel free to let me know.
> Thanks for your understanding!
> Sincerely,
> Steven Cheng
> Microsoft MSDN Online Support Lead
> ==================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||I know you can put a percent of total dim calculation on a dynamic matrix
report by using the year matrix group as a component of the calculation, i.e,
"matrix1_Year". See Brian Larson's Delivering Business Intelligence book
page 596 - 597. I wish there were an example of doing a variance column, but
if it can do this calculated field, then it looks like using the same
approach could do others. This example is based on a matrix report sourced
from an Analysis Services 2005 cube.
"Glaciered Pyro" wrote:
> Hi
> I was just wondering if there was any progress regarding this subject.
> I am sitting with a similar problem and am unable to find a workaround.
> Thanks for all the effort that has been put into solving the problem thus far.
> "Steven Cheng[MSFT]" wrote:
> > Hello Pete,
> >
> > Sorry for keep you waiting. After some further research, we've come out to
> > the same results as Wei has told you in the previous message, the built-in
> > reporting service report items(Table or Matrix) are not able to accomplish
> > the task. I've also involved some other SQL engineers to discuss the issue
> > and they've help confirmed on this. So far they pointed out that it may be
> > possible to use a custom data processing extension to precaculate the
> > additional results, and the key point is that you need to correctly pass
> > the parameters into the custom data processing engine. I'm sorry that we
> > haven't been able to help you further on this. If there is anything else we
> > can help, please feel free to let me know.
> >
> > Thanks for your understanding!
> >
> > Sincerely,
> >
> > Steven Cheng
> >
> > Microsoft MSDN Online Support Lead
> >
> > ==================================================> >
> >
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >|||I Have found that the easiest solution to this problem is
to write custom code using vb.net or c#. Though it might
not be the most efficient... it works. Just add a Table next to
the matrix, write the code to collect the data and calculate
whatever you need to, then simply call it from the table...