foreign key

Hello,

I want to make a poll and have to use a foreign key in my database. This is not possible in Web Matrix so I have to use SQL Enterprise Manager. But don't know how. I read the help but can't figure it out. Can someone help me? Thanks in advance.

Regards,

Roel AlblasHi Roel,

I'm not quite sure what you mean. Do you need to know how to write the SQL statement that uses a foreign key? Or create tables with a foreign key?

Tell us more about what you want to do and we'll try to help.

Don|||I'm making a poll following an example. In that example I have to make tables wich has an relation to each other with a foreing key. I use SQL Server 2000 and Web Matrix.

Roel|||So you're building a table. And you'll use EM to do it. Okay.

Here's a simple example. Since I don't know the particulars of the data you'll use, I'll use a simple contact management example, where each person can have multiple phone numbers.

The Person table would look something like this:

PersonID int (identity, primary key)
Name varchar(30)
...

The Phone table would look something like this:

PhoneID int (identity, primary key)
PhoneNumber varchar(15)
PersonID int
... (type of number, etc.)

In this case, I've named the linking field, PersonID, the same in each table but that is not necessary. You may want to use a naming standard that identifies both primary and foreign keys in your tables. Note in particular that Phone.PersonID isnot an identity field, because it can have duplicate data when a person has several phone numbers.

Using this structure you can now do joins on the two tables to return all of the numbers for a person, or a list of everyone and their phone numbers.

If you want the database to enforce referential integrity (make sure that there are no phone numbers without a person, cascade deletes, etc.) you can also create a relationship between the tables. The easiest way to do this in EM is to create a database diagram with the two tables and create it visually.

Is this enough information? If not, ask away.

Don|||Hi

Just wondering if you know of a similar feature in web matrix to create relationships?
I am using MSDE?

Thanks

Ramila|||No, I sure don't. It's been a while since I did a project with Web Matrix.

There are some other admin tools available, such as these, but I don't know their capabilities for creating relationships:

ASP.NET Enterprise Manager, an open source SQL Server and MSDE management tool.

Microsoft's Web Data Administrator is a free web-based MSDE management program written using C# and ASP.NET, and includes source code.

You can also use T-SQL through the osql command-line utility to create or modify your tables.

How are you creating the structure of your database? Through Matrix? Another way?

Don

Forcing matrix static elements to display despite empty record set

I created a nice matrix report and it works great when the parameter I pass
returns some records, however when no records are returned the matrix simply
does not render at all leaving a big empty hole where that part of the report
should appear.
The matrix has static row heading that I want to always appear even if no
data appears in the columns, even better if there was a way to set a default
value to the columns in case no data is returned say 0s. This is an example
of my matrix.
Static Heading
Column Group
Cars
people
animals
So, Cars, People, and Animals are static row headings and the values fill in
next to them for the column groupings. Now, if no records are returned I
would still like to have the static column heading and static row heading to
appear even if there is no data to display. How is that done?
ThanksTry setting something in the NoRows property - like display a message "no
data for this timeframe" or something and I think that will make your
headings show in addition to the message. I have used this for tables ...
havent tried it with a matrix but I would think it should work.
"Ramez" wrote:
> I created a nice matrix report and it works great when the parameter I pass
> returns some records, however when no records are returned the matrix simply
> does not render at all leaving a big empty hole where that part of the report
> should appear.
> The matrix has static row heading that I want to always appear even if no
> data appears in the columns, even better if there was a way to set a default
> value to the columns in case no data is returned say 0s. This is an example
> of my matrix.
> Static Heading
> Column Group
> Cars
> people
> animals
>
> So, Cars, People, and Animals are static row headings and the values fill in
> next to them for the column groupings. Now, if no records are returned I
> would still like to have the static column heading and static row heading to
> appear even if there is no data to display. How is that done?
> Thanks
>
>
>

forcing column to appear

I have a matrix that shows figures by year, broken down by months (1-12).
When the query runs and that particular month has not values, the column is
not displayed. I previously came across something regarding the use of a
function to force the columns to appear but can't seem to find it again.
Anyone have a suggestion for doing this? It would be similar to how the PIVOT
in access works.On Jun 6, 10:08 am, brian <b...@.discussions.microsoft.com> wrote:
> I have a matrix that shows figures by year, broken down by months (1-12).
> When the query runs and that particular month has not values, the column is
> not displayed. I previously came across something regarding the use of a
> function to force the columns to appear but can't seem to find it again.
> Anyone have a suggestion for doing this? It would be similar to how the PIVOT
> in access works.
I traditionally look for the columns (value in the pivot column) that
I am expecting in the dataset and if they do not appear union an empty
record with the column name to the returned dataset (as part of the
stored procedure/query that is sourcing the report). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Force columns to appear in matrix.

I have created the report below in SSRS to show the total by month for the calendar year.

As of my January running it runs great as there is data for all 12 months of the year and the matrix is created with the required 12 columns.

01

02

03

03

4

05

06

07

08

09

10

11

12

Total

AA

17.5

22

17.5

75

30

162

BB

15

15

15

15

15

15

15

15

15

15

15

15

15

195

CC

15

15

15

15

15

337

15

233.3

15

325.5

117

15

15

1147.8

DD

315

290

1231

1231

1231

269

953

384.8

706.8

1798.8

290

602

419

9720.25

Total

345

320

1261

1261

1261

638

983

633.1

758.8

2156.8

497

662

449

11225.1

As I go to project this report for January 2007 I am having trouble.

I want the report to appear as below with the 12 requisite columns.

01

02

03

03

4

05

06

07

08

09

10

11

12

Total

AA

0

BB

15

15

CC

15

15

DD

315

315

Total

345

345

But as only data for the first column is returned from my query I only get one column to appear.

01

Total

AA

0

BB

15

15

CC

15

15

DD

315

315

Total

345

345

How do I force the other 12 columns / months to appear in my crosstab/matrix and to populate with data as it becomes available?

Hi,

One option I can think of is adding some dummy records to the query, using the UNION statement. One for each month of the year, with a value 0.

Regards, Jeroen

|||

At the risk of sounding like a total noob. Can you modify the query below or give an example?

SELECT

bo.AS400Billingid,

OrgName,

ih.date,

ih.rev

FROM billingorg bo

INNER JOIN

(SELECT

AS400BillingID,

substring(InvoiceDate,4,2) date,

sum(SalesAmount) rev

FROM

InvoiceHeader

WHERE

left(InvoiceDate,3)='106'

GROUP BY

AS400BillingID,substring(InvoiceDate,4,2)) ih

ON bo.AS400BillingID=ih.AS400BillingID

|||

I found a solution.

SELECT

id,

OrgName,

AddedDate,

SalesTerritory,

IndustryCode,

AccountManager,

[01] AS 'JAN',

[02] AS 'FEB',

[03] AS 'MAR',

[04] AS 'APR',

[05] AS 'MAY',

[06] AS 'JUN',

[07] AS 'JUL',

[08] AS 'AUG',

[09] AS 'SEP',

[10] AS 'OCT',

[11] AS 'NOV',

[12] AS 'DEC'

FROM

(SELECT

bo.AS400Billingid id,

bo.OrgName,

bo.AddedDate,

bo.SalesTerritory,

bo.IndustryCode,

bo.AccountManager,

substring(ih.InvoiceDate,4,2) date,

ih.SalesAmount rev

FROM

billingorg bo INNER JOIN InvoiceHeader ih

ON bo.AS400BillingID=ih.AS400BillingID

WHERE

left(InvoiceDate,3)='106') as data

PIVOT

(

sum(rev)

FOR date IN([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])

) XTab

For each Year Group in Matrix return last quarter''s data

Hi, I have a matrix report with three data points

1. Inventory

2. Occupancy

3. Absorption

They are grouped in columns by Year and the data is returned by the query at Quarter granularity

My problem is that in the report, I need to display the Inventory data for the last quarter in each year however for Absorption it is the SUM of all 4 quarters

So, for 2006

Want Q4 data for Inventory, sum of all 4 quarters for Absorption

For 2007 Want Q2 data for Inventory (as it's the last loded quarter) and sum of Q1&Q2 for Absorption

How would I (or could I) do this in a Matrix Report - or is there a better way ?

Reporting Services provides many aggregation functions and it sounds like what you need is to use the Last() aggregation for Inventory and Sum() for Absorption.

|||

Thanks Adam but that does not seem to give me the sum of all the last records, just the last record in the year at Market level. If I expand to Submarket, it includes the correct values but subtotals incorrectly.

How can I get the Market level to aggregate the "last" records at submarket level as opposed to displaying the last submarket record (and also further up the hierarchy this behaviour is the same).

Thanks,

Will

|||

Please knock up an example in excel in copy paste into a post. I'm finding it difficult visualising you report and issue.

|||

I posted some pic links here but they do not show for me so I posted the issue with pics here

I was trying to use a matrix report to return just the last quarter's data for a specific dataset.

I didn't want to show the whole year data, so Sum was out - Likewise I couldn't use Last as the results went skewy as

below, returning the last record as opposed to the SUM of the last quarters member data

Using TopN for the Quarter Filter works to return the max quarter data for each year though, but obviously it does it for all the data points. Competitive Base Inventory needs to be the last quarter per year's data (like a balance if you like) but another measure in the matrix should sum all 4 periods. I realise this is due to the way the data is entered.

Can I do this with a matrix or should I say go to SSAS and create some sort of calculation for Competitive Base Inventory

This was the formula I used to get the max quarter per year BTW

Just got to work out how to use the topN filter just for one data set (i.e. competitive base) but not for the other (Occupancy %) Hmmmmm

|||

So by the look of it, you don't actually want to see the querters, you're just bringing them back because you have to base your measure calculations on them?

I suggest you sort this out in your query.

The logic as far as I see that depending on the measure you want to either return the full year value or the value from the last quarter, with the complication that for the current year that won't necessarily by Q4.

Code Snippet

WITH

[Measures].[Inventory] AS

( Tail(NonEmpty([Time].[Standard].CurrentMember.Childeren))

, [Measures].[Competitive Base Inventory]

)

SELECT

{[Measures].[Inventory], [Measures].[Some other measure]} ON 0

[Time].[Stnadard].[Year].Members * [Geography].[City].[All].Children ON 1

FROM [Your Cube]

WHERE (YOUR FILTERS HERE)

For each Year Group in Matrix return last quarter''s data

Hi, I have a matrix report with three data points

1. Inventory

2. Occupancy

3. Absorption

They are grouped in columns by Year and the data is returned by the query at Quarter granularity

My problem is that in the report, I need to display the Inventory data for the last quarter in each year however for Absorption it is the SUM of all 4 quarters

So, for 2006

Want Q4 data for Inventory, sum of all 4 quarters for Absorption

For 2007 Want Q2 data for Inventory (as it's the last loded quarter) and sum of Q1&Q2 for Absorption

How would I (or could I) do this in a Matrix Report - or is there a better way ?

Reporting Services provides many aggregation functions and it sounds like what you need is to use the Last() aggregation for Inventory and Sum() for Absorption.

|||

Thanks Adam but that does not seem to give me the sum of all the last records, just the last record in the year at Market level. If I expand to Submarket, it includes the correct values but subtotals incorrectly.

How can I get the Market level to aggregate the "last" records at submarket level as opposed to displaying the last submarket record (and also further up the hierarchy this behaviour is the same).

Thanks,

Will

|||

Please knock up an example in excel in copy paste into a post. I'm finding it difficult visualising you report and issue.

|||

I posted some pic links here but they do not show for me so I posted the issue with pics here

I was trying to use a matrix report to return just the last quarter's data for a specific dataset.

I didn't want to show the whole year data, so Sum was out - Likewise I couldn't use Last as the results went skewy as

below, returning the last record as opposed to the SUM of the last quarters member data

Using TopN for the Quarter Filter works to return the max quarter data for each year though, but obviously it does it for all the data points. Competitive Base Inventory needs to be the last quarter per year's data (like a balance if you like) but another measure in the matrix should sum all 4 periods. I realise this is due to the way the data is entered.

Can I do this with a matrix or should I say go to SSAS and create some sort of calculation for Competitive Base Inventory

This was the formula I used to get the max quarter per year BTW

Just got to work out how to use the topN filter just for one data set (i.e. competitive base) but not for the other (Occupancy %) Hmmmmm

|||

So by the look of it, you don't actually want to see the querters, you're just bringing them back because you have to base your measure calculations on them?

I suggest you sort this out in your query.

The logic as far as I see that depending on the measure you want to either return the full year value or the value from the last quarter, with the complication that for the current year that won't necessarily by Q4.

Code Snippet

WITH

[Measures].[Inventory] AS

( Tail(NonEmpty([Time].[Standard].CurrentMember.Childeren))

, [Measures].[Competitive Base Inventory]

)

SELECT

{[Measures].[Inventory], [Measures].[Some other measure]} ON 0

[Time].[Stnadard].[Year].Members * [Geography].[City].[All].Children ON 1

FROM [Your Cube]

WHERE (YOUR FILTERS HERE)

For each Year Group in Matrix return last quarter''s data

Hi, I have a matrix report with three data points

1. Inventory

2. Occupancy

3. Absorption

They are grouped in columns by Year and the data is returned by the query at Quarter granularity

My problem is that in the report, I need to display the Inventory data for the last quarter in each year however for Absorption it is the SUM of all 4 quarters

So, for 2006

Want Q4 data for Inventory, sum of all 4 quarters for Absorption

For 2007 Want Q2 data for Inventory (as it's the last loded quarter) and sum of Q1&Q2 for Absorption

How would I (or could I) do this in a Matrix Report - or is there a better way ?

Reporting Services provides many aggregation functions and it sounds like what you need is to use the Last() aggregation for Inventory and Sum() for Absorption.

|||

Thanks Adam but that does not seem to give me the sum of all the last records, just the last record in the year at Market level. If I expand to Submarket, it includes the correct values but subtotals incorrectly.

How can I get the Market level to aggregate the "last" records at submarket level as opposed to displaying the last submarket record (and also further up the hierarchy this behaviour is the same).

Thanks,

Will

|||

Please knock up an example in excel in copy paste into a post. I'm finding it difficult visualising you report and issue.

|||

I posted some pic links here but they do not show for me so I posted the issue with pics here

I was trying to use a matrix report to return just the last quarter's data for a specific dataset.

I didn't want to show the whole year data, so Sum was out - Likewise I couldn't use Last as the results went skewy as

below, returning the last record as opposed to the SUM of the last quarters member data

Using TopN for the Quarter Filter works to return the max quarter data for each year though, but obviously it does it for all the data points. Competitive Base Inventory needs to be the last quarter per year's data (like a balance if you like) but another measure in the matrix should sum all 4 periods. I realise this is due to the way the data is entered.

Can I do this with a matrix or should I say go to SSAS and create some sort of calculation for Competitive Base Inventory

This was the formula I used to get the max quarter per year BTW

Just got to work out how to use the topN filter just for one data set (i.e. competitive base) but not for the other (Occupancy %) Hmmmmm

|||

So by the look of it, you don't actually want to see the querters, you're just bringing them back because you have to base your measure calculations on them?

I suggest you sort this out in your query.

The logic as far as I see that depending on the measure you want to either return the full year value or the value from the last quarter, with the complication that for the current year that won't necessarily by Q4.

Code Snippet

WITH

[Measures].[Inventory] AS

( Tail(NonEmpty([Time].[Standard].CurrentMember.Childeren))

, [Measures].[Competitive Base Inventory]

)

SELECT

{[Measures].[Inventory], [Measures].[Some other measure]} ON 0

[Time].[Stnadard].[Year].Members * [Geography].[City].[All].Children ON 1

FROM [Your Cube]

WHERE (YOUR FILTERS HERE)

For each Year Group in Matrix return last quarter''s data

Hi, I have a matrix report with three data points

1. Inventory

2. Occupancy

3. Absorption

They are grouped in columns by Year and the data is returned by the query at Quarter granularity

My problem is that in the report, I need to display the Inventory data for the last quarter in each year however for Absorption it is the SUM of all 4 quarters

So, for 2006

Want Q4 data for Inventory, sum of all 4 quarters for Absorption

For 2007 Want Q2 data for Inventory (as it's the last loded quarter) and sum of Q1&Q2 for Absorption

How would I (or could I) do this in a Matrix Report - or is there a better way ?

Reporting Services provides many aggregation functions and it sounds like what you need is to use the Last() aggregation for Inventory and Sum() for Absorption.

|||

Thanks Adam but that does not seem to give me the sum of all the last records, just the last record in the year at Market level. If I expand to Submarket, it includes the correct values but subtotals incorrectly.

How can I get the Market level to aggregate the "last" records at submarket level as opposed to displaying the last submarket record (and also further up the hierarchy this behaviour is the same).

Thanks,

Will

|||

Please knock up an example in excel in copy paste into a post. I'm finding it difficult visualising you report and issue.

|||

I posted some pic links here but they do not show for me so I posted the issue with pics here

I was trying to use a matrix report to return just the last quarter's data for a specific dataset.

I didn't want to show the whole year data, so Sum was out - Likewise I couldn't use Last as the results went skewy as

below, returning the last record as opposed to the SUM of the last quarters member data

Using TopN for the Quarter Filter works to return the max quarter data for each year though, but obviously it does it for all the data points. Competitive Base Inventory needs to be the last quarter per year's data (like a balance if you like) but another measure in the matrix should sum all 4 periods. I realise this is due to the way the data is entered.

Can I do this with a matrix or should I say go to SSAS and create some sort of calculation for Competitive Base Inventory

This was the formula I used to get the max quarter per year BTW

Just got to work out how to use the topN filter just for one data set (i.e. competitive base) but not for the other (Occupancy %) Hmmmmm

|||

So by the look of it, you don't actually want to see the querters, you're just bringing them back because you have to base your measure calculations on them?

I suggest you sort this out in your query.

The logic as far as I see that depending on the measure you want to either return the full year value or the value from the last quarter, with the complication that for the current year that won't necessarily by Q4.

Code Snippet

WITH

[Measures].[Inventory] AS

( Tail(NonEmpty([Time].[Standard].CurrentMember.Childeren))

, [Measures].[Competitive Base Inventory]

)

SELECT

{[Measures].[Inventory], [Measures].[Some other measure]} ON 0

[Time].[Stnadard].[Year].Members * [Geography].[City].[All].Children ON 1

FROM [Your Cube]

WHERE (YOUR FILTERS HERE)

For each Year Group in Matrix return last quarter''s data

Hi, I have a matrix report with three data points

1. Inventory

2. Occupancy

3. Absorption

They are grouped in columns by Year and the data is returned by the query at Quarter granularity

My problem is that in the report, I need to display the Inventory data for the last quarter in each year however for Absorption it is the SUM of all 4 quarters

So, for 2006

Want Q4 data for Inventory, sum of all 4 quarters for Absorption

For 2007 Want Q2 data for Inventory (as it's the last loded quarter) and sum of Q1&Q2 for Absorption

How would I (or could I) do this in a Matrix Report - or is there a better way ?

Reporting Services provides many aggregation functions and it sounds like what you need is to use the Last() aggregation for Inventory and Sum() for Absorption.

|||

Thanks Adam but that does not seem to give me the sum of all the last records, just the last record in the year at Market level. If I expand to Submarket, it includes the correct values but subtotals incorrectly.

How can I get the Market level to aggregate the "last" records at submarket level as opposed to displaying the last submarket record (and also further up the hierarchy this behaviour is the same).

Thanks,

Will

|||

Please knock up an example in excel in copy paste into a post. I'm finding it difficult visualising you report and issue.

|||

I posted some pic links here but they do not show for me so I posted the issue with pics here

I was trying to use a matrix report to return just the last quarter's data for a specific dataset.

I didn't want to show the whole year data, so Sum was out - Likewise I couldn't use Last as the results went skewy as

below, returning the last record as opposed to the SUM of the last quarters member data

Using TopN for the Quarter Filter works to return the max quarter data for each year though, but obviously it does it for all the data points. Competitive Base Inventory needs to be the last quarter per year's data (like a balance if you like) but another measure in the matrix should sum all 4 periods. I realise this is due to the way the data is entered.

Can I do this with a matrix or should I say go to SSAS and create some sort of calculation for Competitive Base Inventory

This was the formula I used to get the max quarter per year BTW

Just got to work out how to use the topN filter just for one data set (i.e. competitive base) but not for the other (Occupancy %) Hmmmmm

|||

So by the look of it, you don't actually want to see the querters, you're just bringing them back because you have to base your measure calculations on them?

I suggest you sort this out in your query.

The logic as far as I see that depending on the measure you want to either return the full year value or the value from the last quarter, with the complication that for the current year that won't necessarily by Q4.

Code Snippet

WITH

[Measures].[Inventory] AS

( Tail(NonEmpty([Time].[Standard].CurrentMember.Childeren))

, [Measures].[Competitive Base Inventory]

)

SELECT

{[Measures].[Inventory], [Measures].[Some other measure]} ON 0

[Time].[Stnadard].[Year].Members * [Geography].[City].[All].Children ON 1

FROM [Your Cube]

WHERE (YOUR FILTERS HERE)

For each Year Group in Matrix return last quarter''s data

Hi, I have a matrix report with three data points

1. Inventory

2. Occupancy

3. Absorption

They are grouped in columns by Year and the data is returned by the query at Quarter granularity

My problem is that in the report, I need to display the Inventory data for the last quarter in each year however for Absorption it is the SUM of all 4 quarters

So, for 2006

Want Q4 data for Inventory, sum of all 4 quarters for Absorption

For 2007 Want Q2 data for Inventory (as it's the last loded quarter) and sum of Q1&Q2 for Absorption

How would I (or could I) do this in a Matrix Report - or is there a better way ?

Reporting Services provides many aggregation functions and it sounds like what you need is to use the Last() aggregation for Inventory and Sum() for Absorption.

|||

Thanks Adam but that does not seem to give me the sum of all the last records, just the last record in the year at Market level. If I expand to Submarket, it includes the correct values but subtotals incorrectly.

How can I get the Market level to aggregate the "last" records at submarket level as opposed to displaying the last submarket record (and also further up the hierarchy this behaviour is the same).

Thanks,

Will

|||

Please knock up an example in excel in copy paste into a post. I'm finding it difficult visualising you report and issue.

|||

I posted some pic links here but they do not show for me so I posted the issue with pics here

I was trying to use a matrix report to return just the last quarter's data for a specific dataset.

I didn't want to show the whole year data, so Sum was out - Likewise I couldn't use Last as the results went skewy as

below, returning the last record as opposed to the SUM of the last quarters member data

Using TopN for the Quarter Filter works to return the max quarter data for each year though, but obviously it does it for all the data points. Competitive Base Inventory needs to be the last quarter per year's data (like a balance if you like) but another measure in the matrix should sum all 4 periods. I realise this is due to the way the data is entered.

Can I do this with a matrix or should I say go to SSAS and create some sort of calculation for Competitive Base Inventory

This was the formula I used to get the max quarter per year BTW

Just got to work out how to use the topN filter just for one data set (i.e. competitive base) but not for the other (Occupancy %) Hmmmmm

|||

So by the look of it, you don't actually want to see the querters, you're just bringing them back because you have to base your measure calculations on them?

I suggest you sort this out in your query.

The logic as far as I see that depending on the measure you want to either return the full year value or the value from the last quarter, with the complication that for the current year that won't necessarily by Q4.

Code Snippet

WITH

[Measures].[Inventory] AS

( Tail(NonEmpty([Time].[Standard].CurrentMember.Childeren))

, [Measures].[Competitive Base Inventory]

)

SELECT

{[Measures].[Inventory], [Measures].[Some other measure]} ON 0

[Time].[Stnadard].[Year].Members * [Geography].[City].[All].Children ON 1

FROM [Your Cube]

WHERE (YOUR FILTERS HERE)