Help with Relational Division

I am trying to create a matrix of counts to help identify our data holdings.
Thanks to Joe Celko I now know I need to perform relational division to find
datasets that match the criteria set up in a secondary table.
I'd be happy to initially just get the counts for each category, as I could
pass the "DSType" parameter in on the fly.
Problem is I can't figure out how from the "Plane/Hanger" example how to
form the relational division query to come up with the counts. Or even a
view listing the datasets that match each "Category"s "Topic" criteria.
There are 4 main tables involved:
-Datasets that we provide information on.
-Types of data that they hold (Regional, Historical, Global, etc.)
-Topics that describe the datasets (Ocean, Carbon, Sea Level, etc.)
-GOOS Matrix that lists Categories that we want to correlate the datasets to
The SQL script to create the tables and insert data into them is at:
http://oceanic.cms.udel.edu/matrix/..._Tables.sql.txt
A PDF of the relationships between the tables is at:
http://oceanic.cms.udel.edu/matrix/..._GOOSMatrix.pdf
And a PDF of the desired matrix view is at:
http://oceanic.cms.udel.edu/matrix/...trix_Output.pdf
TIA,
D
--
"If Pro is the opposite of Con, does that mean that Congress is the opposite
of Progress?"
--GallegherDoes this help? (using Joes's example):
CREATE TABLE PilotSkills (pilot CHAR(15) NOT NULL, plane CHAR(15) NOT
NULL, PRIMARY KEY (pilot, plane));
CREATE TABLE Hangar (plane CHAR(15) NOT NULL PRIMARY KEY);
INSERT INTO PilotSkills (pilot, plane)
SELECT 'Celko', 'Piper Cub' UNION ALL
SELECT 'Higgins', 'B-52 Bomber' UNION ALL
SELECT 'Higgins', 'F-14 Fighter' UNION ALL
SELECT 'Higgins', 'Piper Cub' UNION ALL
SELECT 'Jones', 'B-52 Bomber' UNION ALL
SELECT 'Jones', 'F-14 Fighter' UNION ALL
SELECT 'Smith', 'B-1 Bomber' UNION ALL
SELECT 'Smith', 'B-52 Bomber' UNION ALL
SELECT 'Smith', 'F-14 Fighter' UNION ALL
SELECT 'Wilson', 'B-1 Bomber' UNION ALL
SELECT 'Wilson', 'B-52 Bomber' UNION ALL
SELECT 'Wilson', 'F-14 Fighter' UNION ALL
SELECT 'Wilson', 'F-17 Fighter' ;
INSERT INTO Hangar (plane)
SELECT 'B-1 Bomber' UNION ALL
SELECT 'B-52 Bomber' UNION ALL
SELECT 'F-14 Fighter' ;
SELECT PS1.pilot,
COUNT(PS1.plane) total_skills,
T.total_planes
FROM PilotSkills AS PS1, Hangar AS H1,
(SELECT COUNT(plane) FROM Hangar) AS T(total_planes)
WHERE PS1.plane = H1.plane
GROUP BY PS1.pilot, T.total_planes ;

> The SQL script to create the tables and insert data into them is at:
> http://oceanic.cms.udel.edu/matrix/..._Tables.sql.txt
That's not up to the task in my opinion, which is why I've not used it.
No alternate keys and mostly nullable columns. IDENTITY should not be
the only key of any table and it's very hard to write and verify
queries against such a schema.
David Portas
SQL Server MVP
--

Help with matrix report.

Hi All,

I have the following matrix report.

Health 1 2 3 4 Std: 1 Health Promotion and Disease Prevention 4 2 4 3 Std: 2 Accessing Health Information, Products, and Services 2 2 Std: 3 Self-Management 2 Std: 4 Influence of Culture, Media, and Technology 2 2 Std: 5 Interpersonal Communication 3 Std: 6 Goal Setting and Decision Making Skills 4 2 Std: 7 Advocating for Health 3

I need a bit of help to do two things.

One is the first row needs to be the average of the matrix columns (1,2,3,4).

Two is for a header row that will span the matrix columns.

So the final output would be;

Average Proficiency Level Health 1 2 3 4 Overall 2.8 2.3 3 2.5 Std: 1 Health Promotion and Disease Prevention 4 2 4 3 Std: 2 Accessing Health Information, Products, and Services 2 2 Std: 3 Self-Management 2 Std: 4 Influence of Culture, Media, and Technology 2 2 Std: 5 Interpersonal Communication 3 Std: 6 Goal Setting and Decision Making Skills 4 2 Std: 7 Advocating for Health 3

The number of columns can vary from 1 to 4.

Any help would be very much appreciated.

For the header, you can put a fixed-width table above the report.

Another is here..

http://blogs.msdn.com/bwelcker/archive/2005/05/11/416720.aspx

For the average, did you try looking at the AVG() function?

cheers,

Andrew|||

Andrew,

The header worked great. I have looked at the AVG() function, but how can I make it the first row in the column?

When I try I get "A group expression for the matrix includes an aggregate function. Aggregate functions can not be used in group expressions."

Help with Matrix report and design

I've got this data source that returns member information.. great.
I've got a function that when passed the member number and a month/
year returns days participating in the club.
I need to build a report that will output some of the columns for the
member, but then report other information for 6 months for each member
that all calulated based on days particpating for that month. Which 6
months will be dictated by single parameter passed to the report for
starting month/year... basically the next 6 months.
Is a matrix report a good option for this? Or should I just have the
function called 6 times in my data source? Ideally I layout the report
for one month and then *somehow* join in my data source for 6
iterations where month is 1 through 6.
Right now I'm having trouble wrapping my mind around how and where to
join the data? Especially if matrix and I have two data sources. I'm
also not sure I can call my function from cells in the matrix or if I
can even have mulitple data buckets in the matrix. I'm tempted to do
this the only way I know how, but I suspect there is a better way.
I've never built a matrix report in ssrs, any information or help is
appreciated.
Thank you!If you have a table that lists visits for your members with a datetime
field, your query should look like this:
select MemberFullName, Month(ActivityDate), count(*) from MyTable
group by MemberFullName, Month(ActivityDate)
Then put a matrix control on your report's layout. Drag the MemberFullName
field on the top row, the Month(ActivityDate) on the left column and the
count(*) field in the middle cell. Preview your report.
John Smith | Mary Poppins | Joe Shmoe
Jan 12 | 15 | 17
Feb 14 | 19 | 10
Mar 11 | 12 | 12
Apr 12 | 8 | 11
May 9 | 7 | 18
Jun 10 | 3 | 14
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
<wildman@.noclient.net> wrote in message
news:8cb4193c-acf5-405d-b6b3-d03d512bdcfe@.e53g2000hsa.googlegroups.com...
> I've got this data source that returns member information.. great.
> I've got a function that when passed the member number and a month/
> year returns days participating in the club.
> I need to build a report that will output some of the columns for the
> member, but then report other information for 6 months for each member
> that all calulated based on days particpating for that month. Which 6
> months will be dictated by single parameter passed to the report for
> starting month/year... basically the next 6 months.
> Is a matrix report a good option for this? Or should I just have the
> function called 6 times in my data source? Ideally I layout the report
> for one month and then *somehow* join in my data source for 6
> iterations where month is 1 through 6.
> Right now I'm having trouble wrapping my mind around how and where to
> join the data? Especially if matrix and I have two data sources. I'm
> also not sure I can call my function from cells in the matrix or if I
> can even have mulitple data buckets in the matrix. I'm tempted to do
> this the only way I know how, but I suspect there is a better way.
> I've never built a matrix report in ssrs, any information or help is
> appreciated.
> Thank you!
>

Help with Matrix

Ok, I am having a little trouble figuring out if this is possible...

At first it looked easy (It always does), but as I started messing with matrix groups, it seems that i cant be done in 1 matrix.

Here is the display I want:

Value 1 Value 2 Total

Period 1 Period 2 Var % Period 1 Period 2 Var % Period 1 Period 2 Var %

Row

Row

Row

Row

I was thinking of having 2 groups, and then add a column to the 2nd group to calculate the variance column, and have that total. I cannot seem to get it to do that.

This is the first time I have really pushed the matrix for more than the basics.

Any ideas?

Thanks!!

you can have multiple column groups

e.g. sales region, sales person

but these will always relate to the same data

e.g. amount of sales in $

so if you want to report different things

e.g. percentages, sales, volts, centigrage, apples, bananas

you need to create a pseudo grouping

e.g. group on "1"

and then hide the grouping's borders/cells

and then you will need to use the inscope function to determine which column the report is being against

--

so in your example you will have the initial column grouping "period" and then another grouping called "variance"

"period" will be grouped on the actual period, but "variance" will be grouped on "1"

then in the data cell you just put

carnt remember the syntax but its something like....

=iif( inscope("matrixname_variance", myvariancedata, myperioddata)

you can also do this for rows

|||

Great, thanks! I now have the variance column... However I am assuming that I cannot use the calculations within the matrix to calculate the variance, is this correct? I see no way of referencing Period 1 and Period 2 from the Variance cell.

Thanks again!

BobP

|||

there should only be one cell in the 'data' area

this is the contents of my only 'data' cell in a particular report

=

IIF(

InScope("TradeCount"),

IIF(InScope("NotionalToBaseCurrency"),

switch(

Parameters!RevenueDisplayType.Value = 1,cdbl(SUM(Fields!RevenueAmountToBaseCurrency.Value)),

Parameters!RevenueDisplayType.Value = 2,cdbl(SUM(Fields!RevenueBPA.Value*Fields!RevenueAmountToBaseCurrency.Value)/ iif(Sum(Fields!RevenueAmountToBaseCurrency.Value)=0, 1, Sum(Fields!RevenueAmountToBaseCurrency.Value))),

Parameters!RevenueDisplayType.Value = 3,cdbl(Sum(Fields!RevenuePIPS.Value*10000*Fields!RevenueAmountToBaseCurrency.Value)/ iif(Sum(Fields!RevenueAmountToBaseCurrency.Value)=0, 1, Sum(Fields!RevenueAmountToBaseCurrency.Value))),

true=true,cdbl(Fields!RevenueAmountToBaseCurrency.Value)

)

,

iif(inscope("ParentGroup")

,cdbl(SUM(Fields!NotionalToBaseCurrency.Value)/countdistinct(Fields!RevenueTypeID.Value))

,cdbl(Code.CalculateSum(Fields!DealID.Value,Fields!NotionalToBaseCurrency.Value))

)

)

,

cdbl(CountDistinct(Fields!DealID.Value))

)

Where "parent group" is a row grouping and the top 2 are column groupings

so....

trade count shows as a single column with a numeric integer

whereas the "notional to base currency" displays some other data

in your scenario, trade count = variance, n2bc = period

sorry it is a bit of complex expression but i don't have any others to hand

|||

Ok, here is what I wound up doing.

First of all, thanks for the great ideas above! They were invaluable.

I created the matrix with 1 column as indicated, then I created 3 groups.

1. International/Domestic/Total

2. 1 (Called Variance)

3. Period 1/Period2

Then I added a sub total to the Variance group.

In the data cell, I used the InScope to display either the sum(data) or 1-Sum(first(data))/Sum(Last(data)), which gave me the variance %.

In the SQL, I had 2 SQL statements unioned. The first one grouped on International/Domestic and the Period (using a case statement on the data parameters passed in)

This worked like a charm.

Thanks again for all of the help and the direction!!

BobP