Percent column in Matrix - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Percent column in Matrix

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-31 : 12:29:44

Hi

I'm new to creating reports in reporting services. I have just created matrix with the figures that I require. Is there a way of adding another a column in the matrix to show the percent values?
For example it will look something similar to this:

Region1 20 20%
Region2 20 20%
Region3 20 20%
Region4 40 40%
Total 100 100%

Thanking you in advance!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-31 : 13:04:20

How is your dataset now? Can you post sample of data?

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-31 : 13:16:23

Hi

I'm calling a stored procedure in the dataset.

Here is a sample of the stored procedure:

ALTER PROCEDURE [dbo].[getNumberOfOrderLinesPerOrder]
(@Period DATETIME)
AS

DECLARE @BeginDate DATETIME
DECLARE @EndDate DATETIME

SELECT @BeginDate = [BeginDate] FROM DatePeriods AS dp WHERE dp.ID = @Period
SELECT @EndDate = [EndDate] FROM DatePeriods AS dp WHERE dp.ID = @Period

SELECT
1
,'1 Order Line' AS 'Number of Copies'
,'BERR' AS 'Family'
,COUNT(*) AS 'Orderlines'
FROM(

SELECT
(oh.[Sales Order Reference])
,COUNT(ol.[Sales Order Reference]) AS 'Orderlines'
FROM dbo.OrderHeaders AS oh
LEFT JOIN dbo.OrderLines AS ol ON oh.[Sales Order Reference] = ol.[Sales Order Reference]
LEFT JOIN dbo.StockHeaders AS sh ON ol.[Part Number] = sh.[Part Number]
LEFT JOIN dbo.StockCategories AS sc ON sh.[Stock Category] = sc.[Stock Category]
WHERE oh.[Order Date] BETWEEN @BeginDate AND @EndDate
AND sc.[Stock Group] IN (SELECT sg.[Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'B')
AND ol.[Sales Order Reference] NOT IN (SELECT col.[Sales Order Reference]
FROM dbo.CancelledOrderLines AS col
WHERE col.[Part Number] = ol.[Part Number])

GROUP BY oh.[Sales Order Reference]
HAVING COUNT (*) = 1
) AS OrderLines

UNION

SELECT
2
,'2-4 Order Lines' AS 'Number of Copies'
,'BERR' AS 'Family'
,COUNT(*) AS 'Orderlines'
FROM(

SELECT
(oh.[Sales Order Reference])
,COUNT(ol.[Sales Order Reference]) AS 'Orderlines'
FROM dbo.OrderHeaders AS oh
LEFT JOIN dbo.OrderLines AS ol ON oh.[Sales Order Reference] = ol.[Sales Order Reference]
LEFT JOIN dbo.StockHeaders AS sh ON ol.[Part Number] = sh.[Part Number]
LEFT JOIN dbo.StockCategories AS sc ON sh.[Stock Category] = sc.[Stock Category]
WHERE oh.[Order Date] BETWEEN @BeginDate AND @EndDate
AND sc.[Stock Group] IN (SELECT sg.[Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'B')
AND ol.[Sales Order Reference] NOT IN (SELECT col.[Sales Order Reference]
FROM dbo.CancelledOrderLines AS col
WHERE col.[Part Number] = ol.[Part Number])

GROUP BY oh.[Sales Order Reference]
HAVING COUNT (*) BETWEEN 2 AND 4
) AS OrderLines

UNION

SELECT
3
,'5-9 Order Lines' AS 'Number of Copies'
,'BERR' AS 'Family'
,COUNT(*) AS 'Orderlines'
FROM(

SELECT
(oh.[Sales Order Reference])
,COUNT(ol.[Sales Order Reference]) AS 'Orderlines'
FROM dbo.OrderHeaders AS oh
LEFT JOIN dbo.OrderLines AS ol ON oh.[Sales Order Reference] = ol.[Sales Order Reference]
LEFT JOIN dbo.StockHeaders AS sh ON ol.[Part Number] = sh.[Part Number]
LEFT JOIN dbo.StockCategories AS sc ON sh.[Stock Category] = sc.[Stock Category]
WHERE oh.[Order Date] BETWEEN @BeginDate AND @EndDate
AND sc.[Stock Group] IN (SELECT sg.[Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'B')
AND ol.[Sales Order Reference] NOT IN (SELECT col.[Sales Order Reference]
FROM dbo.CancelledOrderLines AS col
WHERE col.[Part Number] = ol.[Part Number])

GROUP BY oh.[Sales Order Reference]
HAVING COUNT (*) BETWEEN 5 AND 9
) AS OrderLines

UNION

SELECT
4
,'10-19 Order Lines' AS 'Number of Copies'
,'BERR' AS 'Family'
,COUNT(*) AS 'Orderlines'
FROM(

SELECT
(oh.[Sales Order Reference])
,COUNT(ol.[Sales Order Reference]) AS 'Orderlines'
FROM dbo.OrderHeaders AS oh
LEFT JOIN dbo.OrderLines AS ol ON oh.[Sales Order Reference] = ol.[Sales Order Reference]
LEFT JOIN dbo.StockHeaders AS sh ON ol.[Part Number] = sh.[Part Number]
LEFT JOIN dbo.StockCategories AS sc ON sh.[Stock Category] = sc.[Stock Category]
WHERE oh.[Order Date] BETWEEN @BeginDate AND @EndDate
AND sc.[Stock Group] IN (SELECT sg.[Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'B')
AND ol.[Sales Order Reference] NOT IN (SELECT col.[Sales Order Reference]
FROM dbo.CancelledOrderLines AS col
WHERE col.[Part Number] = ol.[Part Number])

GROUP BY oh.[Sales Order Reference]
HAVING COUNT (*) BETWEEN 10 AND 19
) AS OrderLines

UNION

SELECT
5
,'20-39 Order Lines' AS 'Number of Copies'
,'BERR' AS 'Family'
,COUNT(*) AS 'Orderlines'
FROM(

SELECT
(oh.[Sales Order Reference])
,COUNT(ol.[Sales Order Reference]) AS 'Orderlines'
FROM dbo.OrderHeaders AS oh
LEFT JOIN dbo.OrderLines AS ol ON oh.[Sales Order Reference] = ol.[Sales Order Reference]
LEFT JOIN dbo.StockHeaders AS sh ON ol.[Part Number] = sh.[Part Number]
LEFT JOIN dbo.StockCategories AS sc ON sh.[Stock Category] = sc.[Stock Category]
WHERE oh.[Order Date] BETWEEN @BeginDate AND @EndDate
AND sc.[Stock Group] IN (SELECT sg.[Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'B')
AND ol.[Sales Order Reference] NOT IN (SELECT col.[Sales Order Reference]
FROM dbo.CancelledOrderLines AS col
WHERE col.[Part Number] = ol.[Part Number])

GROUP BY oh.[Sales Order Reference]
HAVING COUNT (*) BETWEEN 20 AND 39
) AS OrderLines

UNION

SELECT
6
,'40+ Order Lines' AS 'Number of Copies'
,'BERR' AS 'Family'
,COUNT(*) AS 'Orderlines'
FROM(

SELECT
(oh.[Sales Order Reference])
,COUNT(ol.[Sales Order Reference]) AS 'Orderlines'
FROM dbo.OrderHeaders AS oh
LEFT JOIN dbo.OrderLines AS ol ON oh.[Sales Order Reference] = ol.[Sales Order Reference]
LEFT JOIN dbo.StockHeaders AS sh ON ol.[Part Number] = sh.[Part Number]
LEFT JOIN dbo.StockCategories AS sc ON sh.[Stock Category] = sc.[Stock Category]
WHERE oh.[Order Date] BETWEEN @BeginDate AND @EndDate
AND sc.[Stock Group] IN (SELECT sg.[Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'B')
AND ol.[Sales Order Reference] NOT IN (SELECT col.[Sales Order Reference]
FROM dbo.CancelledOrderLines AS col
WHERE col.[Part Number] = ol.[Part Number])

GROUP BY oh.[Sales Order Reference]
HAVING COUNT (*) >= 40
) AS OrderLines

Is there a built in function which would convert the figure in the first column into a percent value for the second?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-31 : 14:17:52

Give the same expression for second column too and give custome formating of "##0%"

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-31 : 14:25:47

So I basically add the orderline column matrix twice and change the formatting for one of them?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-31 : 14:54:47

quote:
Originally posted by rcr69er

So I basically add the orderline column matrix twice and change the formatting for one of them?

Yup if you want to display same value as in first column but with % sign.

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-31 : 16:31:28

Hi

Is there a way to calculate the percentage if possible. Thats what I'm ideally looking for!

This would be a great help.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-01 : 02:59:46

quote:
Originally posted by rcr69er

Hi

Is there a way to calculate the percentage if possible. Thats what I'm ideally looking for!

This would be a great help.

Thanks

Sure what is the expression used to get individual values currently?

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-06-01 : 06:48:02

Hi

The expression that I am currently dropping in to the data section of the matrix is:

=Sum(Fields!Orderlines.Value)

I hope that helps

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-01 : 15:01:51

try this
=Sum(Fields!Orderlines.Value)/Sum(Fields!Orderlines.Value,"YourDatasetName") and give formating of "##0%"

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-06-01 : 17:58:33

Hi

Thanks for that, but it doesn't seem to be working.

I tried using the following expression:
=Sum(Fields!Orderlines.Value)/Sum(Fields!Orderlines.Value, Fields!Orderlines.Value)

But still get an error:
The Value expression for the textbox ‘textbox4’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.
Build complete -- 1 errors, 0 warnings

To add a column I right clicked the previous cell and selected 'Add Column'.

Any ideas?

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-06-01 : 18:06:44

Sorry my mistake. It does work!!!

The only thing is that, it is calculating the percentages for all the columns together instead of indivually.

Any ideas on how to overcome this. Should I paste the whole query to this board?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 02:28:01

quote:
Originally posted by rcr69er

Sorry my mistake. It does work!!!

The only thing is that, it is calculating the percentages for all the columns together instead of indivually.

Any ideas on how to overcome this. Should I paste the whole query to this board?

Thanks

Where are you putting this? on table footer? or deatil area?

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-06-02 : 03:25:07

On the data area for the matrix.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 03:34:49

quote:
Originally posted by rcr69er

On the data area for the matrix.

then it shouldnt be a problem as it will for each cell calculate this
Sum(Fields!Orderlines.Value) and then return the total value using this

Sum(Fields!Orderlines.Value,"YourDateset") and calculate the fraction and give result as percentage. Are you not getting the correct result? What was expression used?What were groupings applied on matrix?

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-06-02 : 06:58:40

Hi

I'm not getting the correct results.

To start with I added a matrix to the report. Placed 'Number of Copies' in rows, 'Family' in 'Columns' and 'Orderlines' in 'Data' which shows =Sum(Fields!Orderlines.Value). I then right clicked the Ordelines cell and selected ‘Add row’. In the new cell created I added the expression you created along with the formatting.

When I preview the report it show percentage values but for all families combined instead of for just one family.

Hope this helps!

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 07:28:19

quote:
Originally posted by rcr69er

Hi

I'm not getting the correct results.

To start with I added a matrix to the report. Placed 'Number of Copies' in rows, 'Family' in 'Columns' and 'Orderlines' in 'Data' which shows =Sum(Fields!Orderlines.Value). I then right clicked the Ordelines cell and selected ‘Add row’. In the new cell created I added the expression you created along with the formatting.

When I preview the report it show percentage values but for all families combined instead of for just one family.

Hope this helps!

Thanks

Not sure i get you here. Can you provide a screen shot?

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-06-02 : 07:37:13

Yeah sure, but how do you add it on this forum?

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-06-02 : 13:42:18

Hi

The results look like:

I hope that helps

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-06-02 : 14:37:18

Sorry my screen shot doesn't seem to be working.

In summary the results come out similar to the following

Family:B
1 Copy: 4083 54%
2-4 Copies: 85 1%
5-9 Copies: 173 2%
10-19 Copies: 271 4%
20-39 Copies: 548 7%
40+ Copies: 237 3%

Family:OG
1 Copy: 1656 22%
2-4 Copies: 30 0%
5-9 Copies: 7 0%
10-19 Copies: 19 0%
20-39 Copies: 90 1%
40+ Copies: 54 1%

Family: OD
1 Copy: 0 0%
2-4 Copies: 0 0%
5-9 Copies: 0 0%
10-19 Copies: 0 0%
20-39 Copies: 0 0%
40+ Copies: 0 0%

So basically it calcualting the percentage for all families together instead on individually. I hope that makes sense?

Any ideas on what I could do?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-03 : 01:01:54

quote:
Originally posted by rcr69er

Sorry my screen shot doesn't seem to be working.

In summary the results come out similar to the following

Family:B
1 Copy: 4083 54%
2-4 Copies: 85 1%
5-9 Copies: 173 2%
10-19 Copies: 271 4%
20-39 Copies: 548 7%
40+ Copies: 237 3%

Family:OG
1 Copy: 1656 22%
2-4 Copies: 30 0%
5-9 Copies: 7 0%
10-19 Copies: 19 0%
20-39 Copies: 90 1%
40+ Copies: 54 1%

Family: OD
1 Copy: 0 0%
2-4 Copies: 0 0%
5-9 Copies: 0 0%
10-19 Copies: 0 0%
20-39 Copies: 0 0%
40+ Copies: 0 0%

So basically it calcualting the percentage for all families together instead on individually. I hope that makes sense?

Any ideas on what I could do?

Thanks

Ok . then try filtering inside on family value like


Sum(IIF(Fields!Family.Value="B",Fields!Orderlines.Value,0),"YourDateset")

Similarly for other families

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources