sql server reporting services: Calculations in Matrix report -- sql server reporting services

You're in the

sql server reporting services

group:

Calculations in Matrix report

Pete D
7/5/2006 8:15:55 PM

sql server reporting services:

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?

RE: Calculations in Matrix report

weilu NO[at]SPAM online.microsoft.com
7/6/2006 12:00:00 AM

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.