How do I add a Percentage Column after the Total on a Matrix?

Hi. I am looking to create a Matrix-based Report in SQL Server 2005 Reporting Services. I have my query, and a dynamic number of both rows and columns. I have Totals on the "bottom" and "right".

What I'd like to do is add a Percentage column to the right of the right-based Totals column. So the columns would read Col1, Col2, .., ColN, Total, Percentage.

Is this possible?

Thanks.

You could do something like - matrixcoltotal / matrixtotal and set your Format Code to P0 on the properties window.

Your expression will look something like this. =Sum(Fields!ReportField.Value,"MatrixGroup")/Sum(Fields!ReportField.Value,"dataset"). Dont forget to change the FormatCode.

|||How would I place this column to the RIGHT of the Subtotal column?

|||

Add an invisible column group which will be grouped by the percentage expression and add the subtotal to this colum group have the which will give you the sum of all individual percentages.

Shyam

|||Here is the error message I am receiving:

"A group expression for the matrix 'matrix1' includes an aggregate function. Aggregate functions cannot be used in group expressions."

I created a new Column group, outside of my month-by-month column group, and made it invisible. I do see a new "Total" to the right....however when I put in my sum(value, "InnerRowGroup")/sum(value, "OuterRowGroup") as the group expression, I get the above error.

Thank you both greatly for your help. I feel about 75% towards getting this to work.

Jason

|||

No, you dont have to group by the aggregate which is an obvious logical error. Just group by the expression without the sum function which would be:

Fields!InnerGroupField/Fields!OuterGroupField

Then using the subtoal would automatically mean a sum which is what you want I guess.

Shyam

|||What if the percentage I want to calculate is the ratio between two columns? Is this possible, or will I have to pivot the data in the database before I send it to SSRS? This would suck since I have to completely revamp the stored proc.

How do I add a Percentage Column after the Total on a Matrix?

Hi. I am looking to create a Matrix-based Report in SQL Server 2005 Reporting Services. I have my query, and a dynamic number of both rows and columns. I have Totals on the "bottom" and "right".

What I'd like to do is add a Percentage column to the right of the right-based Totals column. So the columns would read Col1, Col2, .., ColN, Total, Percentage.

Is this possible?

Thanks.

You could do something like - matrixcoltotal / matrixtotal and set your Format Code to P0 on the properties window.

Your expression will look something like this. =Sum(Fields!ReportField.Value,"MatrixGroup")/Sum(Fields!ReportField.Value,"dataset"). Dont forget to change the FormatCode.

|||How would I place this column to the RIGHT of the Subtotal column?

|||

Add an invisible column group which will be grouped by the percentage expression and add the subtotal to this colum group have the which will give you the sum of all individual percentages.

Shyam

|||Here is the error message I am receiving:

"A group expression for the matrix 'matrix1' includes an aggregate function. Aggregate functions cannot be used in group expressions."

I created a new Column group, outside of my month-by-month column group, and made it invisible. I do see a new "Total" to the right....however when I put in my sum(value, "InnerRowGroup")/sum(value, "OuterRowGroup") as the group expression, I get the above error.

Thank you both greatly for your help. I feel about 75% towards getting this to work.

Jason

|||

No, you dont have to group by the aggregate which is an obvious logical error. Just group by the expression without the sum function which would be:

Fields!InnerGroupField/Fields!OuterGroupField

Then using the subtoal would automatically mean a sum which is what you want I guess.

Shyam

|||What if the percentage I want to calculate is the ratio between two columns? Is this possible, or will I have to pivot the data in the database before I send it to SSRS? This would suck since I have to completely revamp the stored proc.

How do I add a Percentage Column after the Total on a Matrix?

Hi. I am looking to create a Matrix-based Report in SQL Server 2005 Reporting Services. I have my query, and a dynamic number of both rows and columns. I have Totals on the "bottom" and "right".

What I'd like to do is add a Percentage column to the right of the right-based Totals column. So the columns would read Col1, Col2, .., ColN, Total, Percentage.

Is this possible?

Thanks.

You could do something like - matrixcoltotal / matrixtotal and set your Format Code to P0 on the properties window.

Your expression will look something like this. =Sum(Fields!ReportField.Value,"MatrixGroup")/Sum(Fields!ReportField.Value,"dataset"). Dont forget to change the FormatCode.

|||How would I place this column to the RIGHT of the Subtotal column?|||

Add an invisible column group which will be grouped by the percentage expression and add the subtotal to this colum group have the which will give you the sum of all individual percentages.

Shyam

|||Here is the error message I am receiving:

"A group expression for the matrix 'matrix1' includes an aggregate function. Aggregate functions cannot be used in group expressions."

I created a new Column group, outside of my month-by-month column group, and made it invisible. I do see a new "Total" to the right....however when I put in my sum(value, "InnerRowGroup")/sum(value, "OuterRowGroup") as the group expression, I get the above error.

Thank you both greatly for your help. I feel about 75% towards getting this to work.

Jason|||

No, you dont have to group by the aggregate which is an obvious logical error. Just group by the expression without the sum function which would be:

Fields!InnerGroupField/Fields!OuterGroupField

Then using the subtoal would automatically mean a sum which is what you want I guess.

Shyam

|||What if the percentage I want to calculate is the ratio between two columns? Is this possible, or will I have to pivot the data in the database before I send it to SSRS? This would suck since I have to completely revamp the stored proc.

How do can I do this?

Hi,

I have this table:

colA ; colB

1) 1 2

2) 2 1

3) 2 6

4) 3 6

5) 6 3

6) 6 7

7) 7 6

and I need to select from it only the rows that do not have an opposite pair, i.e. only line (3) as it is 2 - 6 and there is no line 6 - 2, all the other lines have opposites, like line (1) 1 - 2 has line (2) 2 - 1 and line (4) has line (5), etc.

any ideas?

thanks.

SELECT b.id, b.colA, b.colB FROM mytest b WHERE b.id NOT IN (SELECT a.id

FROM myTest a INNER JOIN (SELECT id, colA, colB

FROM myTest ) d ON a.colA=d.colB AND a.colB=d.colA)

|||

thanks a lot,

but I have only colA and colB, the "x)" in my post was only for being more clear about it.

|||

Maybe something like this:

declare @.mock table (colA int, colB int)
insert into @.mock values (1, 2)
insert into @.mock values (2, 1)
insert into @.mock values (2, 6)
insert into @.mock values (3, 6)
insert into @.Mock values (6, 3)
insert into @.mock values (6, 7)
insert into @.mock values (7, 6)

select *
from @.mock x
where not exists
( select 0 from @.mock y
where x.cola = y.colb
and x.colb = y.cola
)

-- - Output: -

-- colA colB
-- -- --
-- 2 6

|||

Hi,

thanks to all but here is the solution:

SELECT *

FROM (SELECT * FROM tbl) AS t3

EXCEPT

SELECT *

FROM (SELECT t1.*

FROM tbl AS t1 INNER JOIN

tbl AS t2

on t1.colA = t2.colB AND

t1.colB = t2.colA) AS t4

better to know the new operators... :)

|||

Thank you, Alan; you caught me. I have missed the EXCEPT join at least once before, too. Please keep after me until I get it right. :-)

Dave

|||Using NOT EXISTS is the easiest way to write the query. This version using EXCEPT and joins will be much slower.

How could I modify it as MDX Parameter?

Hi,
I have a MDX syntax as follow
select
[Measures].[Amount] on columns,
[XX03Buyer] on rows
from cubeef
where [XX01Product].[XX01Product].&[2212]
and want to change .&[2212] as Parameter,
that I can select Product code like 2200, 2211.
So, how should I do?
Thanks for any advice!
AngiSet up your parameter as you would normally then in the mdx change it to
="select" & "[Measures].[Amount] on columns, "
& "[XX03Buyer] on rows "
& "from cubeef "
& "where [XX01Product].[XX01Product].&["& Parameters!ProductID.Value &"]"
That should do it, as long as i got the syntax right. Problem with this is
that you can't test it unless you preview it. So if you want to get more
from you MDX suxh as more fields you need to take out all the quotes and &'s
and the = at the beginning. So my advice is save a copy of the MDX query
for a particular product so you can refresh your fields easily.
hope that helps
Greg
"Angi" <enchiw@.msn.com> wrote in message
news:%23pZmQPw9FHA.2644@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a MDX syntax as follow
> select
> [Measures].[Amount] on columns,
> [XX03Buyer] on rows
> from cubeef
> where [XX01Product].[XX01Product].&[2212]
> and want to change .&[2212] as Parameter,
> that I can select Product code like 2200, 2211.
> So, how should I do?
> Thanks for any advice!
> Angi
>sql

How can we trust Outer Joins

In the future =* or *= will not be suportted but I found more then one examples that show outer join does not bring all the rows =* does.

Can any one tell me what is wrong? Market table has 19 markets Budget table has rows for BudgetYear=2006 but none for 2007

BTW the same is true in SQL2005 and 2000

-

SELECT Market_code, Description,
Revenue = SUM( CASE WHEN BudgetType = 'B' THEN BudgetAmount ELSE 0 END ),
CM = SUM( CASE WHEN BudgetType = 'C' THEN BudgetAmount ELSE 0 END ),
Tooling = SUM( CASE WHEN BudgetType = 'T' THEN BudgetAmount ELSE 0 END )
FROM Budget RIGHT OUTER JOIN Market ON Budget.Customer_Market_code = Market.Market_code
Where Budget.BudgetYear=2007 or Budget.BudgetYear is null
GROUP BY Market_code, Description
ORDER BY Description

-- GIVES only 12 results -

SELECT Market_code, Description,
Revenue = SUM( CASE WHEN BudgetType = 'B' THEN BudgetAmount ELSE 0 END ),
CM = SUM( CASE WHEN BudgetType = 'C' THEN BudgetAmount ELSE 0 END ),
Tooling = SUM( CASE WHEN BudgetType = 'T' THEN BudgetAmount ELSE 0 END )
FROM Budget, Market
Where Budget.Customer_Market_code =* Market.Market_code
AND Budget.BudgetYear=2007
GROUP BY Market_code, Description
ORDER BY Description

-- GIVES 19 results -the true

? You're falling into what some call the "trap" of the outer join... Putting predicates in the WHERE clause vs. the JOIN clause has a different logical meaning when working with outer joins. Keep in mind that the join expression is logically evaluated before the WHERE clause. With that in mind, it's a simple move: SELECT Market_code, Description,Revenue = SUM( CASE WHEN BudgetType = 'B' THEN BudgetAmount ELSE 0 END ),CM = SUM( CASE WHEN BudgetType = 'C' THEN BudgetAmount ELSE 0 END ),Tooling = SUM( CASE WHEN BudgetType = 'T' THEN BudgetAmount ELSE 0 END )FROM Budget RIGHT OUTER JOIN Market ON Budget.Customer_Market_code = Market.Market_code AND Budget.BudgetYear=2007GROUP BY Market_code, DescriptionORDER BY Description -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Ofer Gal@.discussions.microsoft.com> wrote in message news:641c616b-1b8a-4316-9278-f52db877ae74@.discussions.microsoft.com... In the future =* or *= will not be suportted but I found more then one examples that show outer join does not bring all the rows =* does. Can any one tell me what is wrong? Market table has 19 markets Budget table has rows for BudgetYear=2006 but none for 2007 BTW the same is true in SQL2005 and 2000 - SELECT Market_code, Description,Revenue = SUM( CASE WHEN BudgetType = 'B' THEN BudgetAmount ELSE 0 END ),CM = SUM( CASE WHEN BudgetType = 'C' THEN BudgetAmount ELSE 0 END ),Tooling = SUM( CASE WHEN BudgetType = 'T' THEN BudgetAmount ELSE 0 END )FROM Budget RIGHT OUTER JOIN Market ON Budget.Customer_Market_code = Market.Market_codeWhere Budget.BudgetYear=2007 or Budget.BudgetYear is nullGROUP BY Market_code, DescriptionORDER BY Description -- GIVES only 12 results - SELECT Market_code, Description,Revenue = SUM( CASE WHEN BudgetType = 'B' THEN BudgetAmount ELSE 0 END ),CM = SUM( CASE WHEN BudgetType = 'C' THEN BudgetAmount ELSE 0 END ),Tooling = SUM( CASE WHEN BudgetType = 'T' THEN BudgetAmount ELSE 0 END )FROM Budget, Market Where Budget.Customer_Market_code =* Market.Market_codeAND Budget.BudgetYear=2007GROUP BY Market_code, DescriptionORDER BY Description -- GIVES 19 results -the true|||when ther outer table does not meet the requirements of the JOIN clause, the values of all its' columns is NULL, thus, any further filtering (other then null-check) on those will cause the row to be removed from the result.

How Can I Use a SubQuery to Sum a Column "Quantity" where subqry.item=mainqry.it

More to the point, how do I get the results to show one row per Item? I thought if I got all the rows evaluate the same, I would be able to use DISTINCT to return one row per item.

Actually, I thought if I did a RIGHT JOIN that for each instance (record) of an item in my ItemInventory table (in which all rows are unique) with my PurchaseOrderLine table (in which there can be multiple instances of an item) that I would get one lie, with the quantities on PO's that are open and received summed. Instead, I get multiple lines that all appear to have correct data but that are duplicates.

Here is my current query

SELECT ItemInventory.Name AS [Product Number],
ItemInventory.SalesDesc AS Description,
ItemInventory.PrefVendorRefFullName AS Vendor,
ItemInventory.QuantityOnHand AS Available,
ItemInventory.ReorderPoint AS Minimum,
(ItemInventory.QuantityOnHand-ItemInventory.ReorderPoint) AS Under,

(SELECT SUM(PurchaseOrderLine.PurchaseOrderLineQuantity)
FROM PurchaseOrderLine
WHERE ItemInventory.Name =
PurchaseOrderLine.PurchaseOrderLineItemRefFullName )
AS [On Order],

(SELECT SUM
(PurchaseOrderLine.PurchaseOrderLineReceivedQuanti ty)
FROM PurchaseOrderLine
WHERE ItemInventory.Name =
PurchaseOrderLine.PurchaseOrderLineItemRefFullName )
AS Recvd

PurchaseOrderLine.TxnDate AS [PO Date],
ItemInventory.PurchaseCost AS Price

FROM PurchaseOrderLine RIGHT JOIN ItemInventory ON PurchaseOrderLine.PurchaseOrderLineItemRefListID=I temInventory.ListID;

Here are my results:

"Large Box","Large Packing Box",,455,100,355,,,2
"Medium Box","Medium cardboard packing box",,55,100,-45,,,1.5
"Packing Tape","Packing Tape, 100 Yard roll",,100,100,0,150,100,1.5
"Packing Tape","Packing Tape, 100 Yard roll",,100,100,0,150,100,1.5
"Small Box","Small cardboard packing box",,55,100,-45,,,1
"Small wooden crate","Small wooden crate","Crate Guys",100,50,50,54,10,8
"Small wooden crate","Small wooden crate","Crate Guys",100,50,50,54,10,8
"Small wooden crate","Small wooden crate","Crate Guys",100,50,50,54,10,8
"Widget","A widget","Texaco",199,200,-1,224,125,5
"Widget","A widget","Texaco",199,200,-1,224,125,5
"Widget","A widget","Texaco",199,200,-1,224,125,5
"Will Excel Delete This Item",,,100,,,,,0

If I use DISTINCT, like I thought I could, MS ACCESS blows up on me. I don't know if it is my SQL or MS ACCESS (or if my data source has issues).

If I can verify my SQL is valid, then I'll know if I need to look elsewhere for a resolution to my problem.

TIA

Danielyou need to develop a query like this in two steps

the first step is to ensure that the join is working correctly, and the second step is to add GROUP BY and aggregate functions like SUM (i think your subqueries in the SELECT clause are wrong, but let's do that later after we fix the join)

for your first step, you need to understand that your RIGHT JOIN will return each ItemInventory with or without matching rows from PurchaseOrderLine

in other words, all products

and for any product that appears on any PurchaseOrderLine, you will get a row for each such occurrence, and that product will appear in the results multiple times

is that what you want? if not, we need to fix the join before you start thinking about SUMs|||I need to look at every item in the InventoryItem table.

For those items where there are purchase orders in the PurchaseOrderLine table, I need to sum the total number on all open purchase orders, minus the number already received against those open purchase orders (they receive partial fills on orders).

In the end, I need somethig that looks like this:

Name Description Vendor #Available Reorder_Point Max_On_Hand #Under Reorder_Point #On_Order #To_Reorder Cost_Per_Unit Cost_to_Reorder Total_Cost_to_Reorder

#Under = Max_On_Hand - #Avaialble (if #Available < Max_On_Hand)

We reorder an item if #Under < 0

#To_Reorder = #Under - #On_Order (we never want the total headed for the shelves to be > Max_On_Hand|||select II.Name AS [Product Number]
, II.SalesDesc AS Description
, II.PrefVendorRefFullName AS Vendor
, II.QuantityOnHand AS Available
, II.ReorderPoint AS Minimum
, (II.QuantityOnHand
-II.ReorderPoint) AS Under
, SUM(POL.PurchaseOrderLineQuantity)
AS [On Order]
, SUM(POL.PurchaseOrderLineReceivedQuantity)
AS Recvd
, MAX(POL.TxnDate) AS [PO Date]
, II.PurchaseCost AS Price
from ItemInventory as II
left outer
join PurchaseOrderLine as POL
ON POL.PurchaseOrderLineItemRefListID
= II.ListID
group
by II.Name
, II.SalesDesc
, II.PrefVendorRefFullName
, II.QuantityOnHand
, II.ReorderPoint
, II.PurchaseCost|||I will try this.

I may ask what makes it tick, but I think I get it ... I will play with it before I ask again tho.

How can i TRUNCATE my table?

How can i TRUNCATE my table (removes rows that were produced at testing), so that in the actual running the automated IDNumber for my table start with 1.

you need to execute a sql state like:

TRUNCATE TABLE myTable;

http://msdn2.microsoft.com/en-us/library/ms177570.aspx

I usually do this through query analyzer.

|||

my table is called 'objects'. i can view my records/data under by right clicking on my Table ('Objects') in the 'Server Explorer'.

but don't know where to place this SQL code so that i could execute it, could you please advice?

and for my code would it be;

TRUNCATE TABLE ObjectsTable;

|||

right click the table and select: new query

put the truncate command in place of the select statement.

dont save it - just run it

you'll get a warning about the truncate not being supported for a graphical display, but it can still be executed.

note: if your table is named 'objects' then the sql would be

TRUNCATE TABLE objects;

|||

I have used the code from your recommended website, but don't know what is my 'schema_name', could you please advice me where I can find this in my 'Server Explorer'?

this is what i have changed so far - is it right to leave the name as it is i.e.Objects_name ? also is it right to keep all the brackets and full stops?

TRUNCATE TABLE     [ { LTrails_name.[ schema_name ]. | schema_name . } ]    Objects_name[ ; ]

|||

if you right click your table and start to create a new query and add that table you'll get something like this:

SELECT FROM objects

the word 'objects' should already be your exact table name that you added to the query.

now you just need to replace the SELECT FROM portion of the query with TRUNCATE TABLE

TRUNCATETABLE objects

Then run the query.

|||

Your very detailed explanation is most appreciated,