max mem field not working

Hi,
i explained to SQL 2005 express that the max mem it could use was 500mb (via
the Max server memory field in Mem option on properties).
yet when i examine task manager its using 800mb.
Any idea why ?
Need to stop restart for changes to take effect maybe ?
Thanks
Scott
> i explained to SQL 2005 express that the max mem it could use was 500mb
> (via the Max server memory field in Mem option on properties).
> yet when i examine task manager its using 800mb.
> Any idea why ?
> Need to stop restart for changes to take effect maybe ?
Yes, it is not going to choke a SQL Server process that is already running
in more memory space.
You can see details in:
select * from sys.configurations where [name] = 'max server memory (MB)';
I'll bet "value_in_use" is > "maximum"... that will correct itself once you
stop and start SQL Server.
|||Additionally to what Aaron said, take in consideration that SQL Server
2005 Express will take a bit more memory than that anyway, because the
memory specified is exclusively for the memory pool. Except something
between 30mb to 100mb extra to that 800mb.
Cesar Vega
http://www.databasecorner.com
On Mar 21, 9:58 am, "Scott" <s...@.yahoo.com> wrote:
> Hi,
> i explained to SQL 2005 express that the max mem it could use was 500mb (via
> the Max server memory field in Mem option on properties).
> yet when i examine task manager its using 800mb.
> Any idea why ?
> Need to stop restart for changes to take effect maybe ?
> Thanks
> Scott
sql

max mem field not working

Hi,
i explained to SQL 2005 express that the max mem it could use was 500mb (via
the Max server memory field in Mem option on properties).
yet when i examine task manager its using 800mb.
Any idea why ?
Need to stop restart for changes to take effect maybe ?
Thanks
Scott> i explained to SQL 2005 express that the max mem it could use was 500mb
> (via the Max server memory field in Mem option on properties).
> yet when i examine task manager its using 800mb.
> Any idea why ?
> Need to stop restart for changes to take effect maybe ?
Yes, it is not going to choke a SQL Server process that is already running
in more memory space.
You can see details in:
select * from sys.configurations where [name] = 'max server memory (MB)'
;
I'll bet "value_in_use" is > "maximum"... that will correct itself once you
stop and start SQL Server.|||Additionally to what Aaron said, take in consideration that SQL Server
2005 Express will take a bit more memory than that anyway, because the
memory specified is exclusively for the memory pool. Except something
between 30mb to 100mb extra to that 800mb.
Cesar Vega
http://www.databasecorner.com
On Mar 21, 9:58 am, "Scott" <s...@.yahoo.com> wrote:
> Hi,
> i explained to SQL 2005 express that the max mem it could use was 500mb (v
ia
> the Max server memory field in Mem option on properties).
> yet when i examine task manager its using 800mb.
> Any idea why ?
> Need to stop restart for changes to take effect maybe ?
> Thanks
> Scott

max mem field not working

Hi,
i explained to SQL 2005 express that the max mem it could use was 500mb (via
the Max server memory field in Mem option on properties).
yet when i examine task manager its using 800mb.
Any idea why ?
Need to stop restart for changes to take effect maybe ?
Thanks
Scott> i explained to SQL 2005 express that the max mem it could use was 500mb
> (via the Max server memory field in Mem option on properties).
> yet when i examine task manager its using 800mb.
> Any idea why ?
> Need to stop restart for changes to take effect maybe ?
Yes, it is not going to choke a SQL Server process that is already running
in more memory space.
You can see details in:
select * from sys.configurations where [name] = 'max server memory (MB)';
I'll bet "value_in_use" is > "maximum"... that will correct itself once you
stop and start SQL Server.|||Additionally to what Aaron said, take in consideration that SQL Server
2005 Express will take a bit more memory than that anyway, because the
memory specified is exclusively for the memory pool. Except something
between 30mb to 100mb extra to that 800mb.
Cesar Vega
http://www.databasecorner.com
On Mar 21, 9:58 am, "Scott" <s...@.yahoo.com> wrote:
> Hi,
> i explained to SQL 2005 express that the max mem it could use was 500mb (via
> the Max server memory field in Mem option on properties).
> yet when i examine task manager its using 800mb.
> Any idea why ?
> Need to stop restart for changes to take effect maybe ?
> Thanks
> Scott

Max length for an if statement

I am working on a formula in crystal using Visual Basic. I need to pull out lists of workers titles from a set list I have written down. I am using a fairly long if statement here and would like to know if there is a max number of conditions I can use in a given if statement.

Here is an example of what I am using.
*******************************

If (InStr({Concurrence.ConcurrenceName}, " MD", 1) > 0 _
or InStr({Concurrence.ConcurrenceName}, " COS ", 1) > 0 _
or InStr({Concurrence.ConcurrenceName}, ", M.D.", 1) > 0 _
or InStr({Concurrence.ConcurrenceName}, "M.D.", 1) > 0 _
or InStr({Concurrence.ConcurrenceName}, " M D", 1) > 0 _
or InStr({Concurrence.ConcurrenceName}, "D.O.", 1) > 0 _
or InStr({Concurrence.ConcurrenceName}, " DO", 1) > 0 _
or Instr({Concurrence.ConcurrenceName}, " DR.", 1) > 0 _
or Instr({Concurrence.ConcurrenceName}, " DR ", 1) > 0) _
or InStr({Concurrence.ConcurrenceName}, "MD", 1) > 0 _
or InStr({Concurrence.ConcurrenceName}, " DO ", 1) > 0 _
or InStr({Concurrence.ConcurrenceName}, "DR", 1) = 1) then
formula = "Physician"

ElseIf (InStr({Concurrence.ConcurrenceTitle}, "PHYSICIAN", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "ATTENDING", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "ACOS", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "COS ", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "CHIEF MEDICAL", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "CHIEF OF STAFF", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, " M.D.", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, " MD,", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "MD", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "M.D", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "D.O.", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "SURGEON", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "INTERNIST", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "DOCTOR", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, " CHIEFOFSTAFF", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "ANESTHESIOLOGIST", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "CARDIOLOGIST", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "CHIEFSOFSTAFF", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "AOD", 1) > 0) then
formula = "Physician"

End If

For some reason crystal does not pull out the "PHYSICIAN" title in the second if else statement. However, if I put it in its own if statement it will evaluate properly. (I broke the if statement up for maintenance ease)

Like this:

If InStr({Concurrence.ConcurrenceTitle}, "PHYSICIAN", 1) > 0 Then
formula = "Physician"
End If

So, I am wondering if I simply have too long of an if statement and need to break this up in order to get all of the titles I am looking for.

Any suggestions on how long each if statement can be, or possibly a more efficient way to write this?

Thanks in advance.

RyanIf there are many IFs then why cant you use Case statement?sql

Max Date for multiple columns

Hi all,
Okay... this should be a pretty question, but, can't seem to figure out
how to do it. In this database I'm working with, they have created 8 column
s
(Version1, Date1, Version2, Date2, Version3,Date3, Version4, Date4).
I need to get the max date (Date1, Date2, Date3 or Date4) and the
information from the appropriate column (So, if Date2 is the max date, then
return the result set containing Version2 and Date2 data)
Any idea about how to approach this problem?
Any help is greatly apprectiated.
DougSure...
CREATE TABLE [dbo].[TEST] (
[ACCOUNT] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SYSTEM1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PURCHASEDATE1] [datetime] NULL ,
[SYSTEM2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PURCHASEDATE2] [datetime] NULL ,
[SYSTEM3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PURCHASEDATE3] [datetime] NULL ,
[SYSTEM4] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PURCHASEDATE4] [datetime] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
"Doug" wrote:

> Hi all,
> Okay... this should be a pretty question, but, can't seem to figure ou
t
> how to do it. In this database I'm working with, they have created 8 colu
mns
> (Version1, Date1, Version2, Date2, Version3,Date3, Version4, Date4).
> I need to get the max date (Date1, Date2, Date3 or Date4) and the
> information from the appropriate column (So, if Date2 is the max date, the
n
> return the result set containing Version2 and Date2 data)
> Any idea about how to approach this problem?
> Any help is greatly apprectiated.
> Doug|||I see several solutions
A. Self joins
B. Big if statement
or
C. Temp table
Create a temp table that has the record number, Version, and Date. Each
row represents a single Version / Date pair. Each row in your original
table would then become 4 rows in the temp table.
Then you could do
select recordid, max(datecol)
from tempTable
group by recordid|||>> Any idea about how to approach this problem?
The very fact that a simple query as this requires a complex solution itself
suggest that the table design could be improved. Rather than using column
names to represent data, consider something along the lines of:
CREATE TABLE tbl (
key_col ...
version ..
date_col DATETIME ) ;
This will allow you to add more versions without having to alter the schema.
Moreover the design is more flexible & allows for better constraint
enforcement as well.
If you are somehow forced to stick with the existing schema consider using a
view/ derived table to logically abstract the data like:
SELECT key_col,
CASE n WHEN 1 THEN version1
WHEN 2 THEN version2
WHEN 3 THEN version3
WHEN 4 THEN version4
END AS "version",
CASE n WHEN 1 THEN date1
WHEN 2 THEN date2
WHEN 3 THEN date3
WHEN 4 THEN date4
END AS "version_date"
FROM tbl, ( SELECT 1 UNION SELECT 2 UNION
SELECT 3 UNION SELECT 4 ) T ( n );
Now, it is just a matter of using aggregate function MAX() on the
version_date column to get the required value.
Anith|||On Mon, 20 Mar 2006 06:59:42 -0800, Doug wrote:

>Hi all,
> Okay... this should be a pretty question, but, can't seem to figure out
>how to do it. In this database I'm working with, they have created 8 colum
ns
>(Version1, Date1, Version2, Date2, Version3,Date3, Version4, Date4).
> I need to get the max date (Date1, Date2, Date3 or Date4) and the
>information from the appropriate column (So, if Date2 is the max date, then
>return the result set containing Version2 and Date2 data)
> Any idea about how to approach this problem?
Hi Doug,
Normalise your design. You should have a seperate table with Date and
Version as columns, plus a foreign key to the table where these 8
columns now are. Then, it's quite easy.
Assuming the normalised table looks like this
CREATE TABLE YourTable
(CustomerID int NOT NULL,
TheDate datetime NOT NULL,
Version varchar(20) NOT NULL,
PRIMARY KEY (CustomerID, TheDate),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
)
The query is like this
SELECT a.CustomerID, a.TheDate, a.Version
FROM YourTable AS a
INNER JOIN (SELECT CustomerID, MAX(TheDate) AS MaxDate
FROM YourTable
GROUP BY CustomerID) AS b
ON b.Customer = a.Customer
AND b.MaxDate = a.TheDate
Hugo Kornelis, SQL Server MVP|||Hi all,
Thanks for all of the help! Unfortunately, the design can't be
normalised. We are using the Goldmine application (commercial product) and
they designed the tables to work this way. This design has caused me a
number of headaches.
Overall, I went with a stored procedure to get the data into a
temporary table that was more normalized and then got my information using
standard techniques.
Dougie
"Hugo Kornelis" wrote:

> On Mon, 20 Mar 2006 06:59:42 -0800, Doug wrote:
>
> Hi Doug,
> Normalise your design. You should have a seperate table with Date and
> Version as columns, plus a foreign key to the table where these 8
> columns now are. Then, it's quite easy.
> Assuming the normalised table looks like this
> CREATE TABLE YourTable
> (CustomerID int NOT NULL,
> TheDate datetime NOT NULL,
> Version varchar(20) NOT NULL,
> PRIMARY KEY (CustomerID, TheDate),
> FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
> )
> The query is like this
> SELECT a.CustomerID, a.TheDate, a.Version
> FROM YourTable AS a
> INNER JOIN (SELECT CustomerID, MAX(TheDate) AS MaxDate
> FROM YourTable
> GROUP BY CustomerID) AS b
> ON b.Customer = a.Customer
> AND b.MaxDate = a.TheDate
> --
> Hugo Kornelis, SQL Server MVP
>

matrix: more than 1 column in static rowgroups ?

Hello,

I am working with a matrix report item and would like to display something like that :

DataGroup1
DataGroup2
StaticText11 StaticText12 Data1
StaticText21 StaticText22 Data2
StaticText31 StaticText32 Data3

But I cannot find a simple way to have 2 columns of static text on the left. The only way I've found is inserting a table report item in the matrix cells, but it cannot be exported in Excel.

The only samples I found do'nt have multiple colums in the static rowgroup.

Is it possible ?

Kind regards,
Xavier Miller.

Xavier,

This is a known issue since Ms release Reporting Services 2000, they promised to fix it on a service pack... but it seems that this has not been fixed even in 2005.

Apart from the table inside the matrix... there's another nasty trick, place a rectanlge and inside textboxes with the headers (you have to adjust the size pixel by pixel), BUT.... you will get horrible columns merged when you export into excel and you cannot use dynamic column sizes....

I'm looking as well for a better solution... but it seems that nobody cares about this.

Regards

Braulio

|||oh cool, that's what I expected...

Thanks anyway for the answer ;)|||

Hi,

It is also possible to add a second Row Group and set the group expression the same value as the first. This way you will receive an additional column. In this column, you can then set the expression to the required value.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Geert Verhoeven wrote:

Hi,

It is also possible to add a second Row Group and set the group expression the same value as the first. This way you will receive an additional column. In this column, you can then set the expression to the required value.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

Hello,

The expression is static text. How do you proceed, please ?|||

Mmm... but I guess this is only valid in the data area (the right hand side of the matrix report), in the columns that identify the rows (left hand side), I guess you can not set that columns on top of the left area, isn't it ?

|||

Brauliod wrote:

Mmm... but I guess this is only valid in the data area (the right hand side of the matrix report), in the columns that identify the rows (left hand side), I guess you can not set that columns on top of the left area, isn't it ?

Yes, that's why I posted that message to confirm that it is impossible.

In fact, I can double the static rows in the RDL source. The designer shows well 2 columns, but the renderer refuses to show them.|||

Gert I'm trying to group rows, but I get them as an addition column not as a row above the left hand side of columns, could you tell us which steps must be performed ?

Thanks in advance, Regards

Braulio

|||

Hi,

I'll try to give an example. I'm using the following query on the AdventureWorks db:
SELECT Production.Product.Name, Production.ProductSubcategory.Name AS ProductSubCategory
FROM Production.Product INNER JOIN Production.ProductSubcategory ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID

If I get it right you want to have something like this:

ProductSubCategory
Product statictext

To do this, you need to follow these steps:

add a matrix to your report|||Yes, that works for dynamic data and grouping, but NOT for static data, multiple columns by row, which was my initial question...|||

Sorry, what I want to get is something like:

May 07 June 07

Project Name Location

Tiger Spain 20 40 (...)

I need the header on top ( the matrix reports that I'm using is more an horizontal report, the data columns are dynamic, but the left hand side columns are static and have the ID column and some attributes).Having the data columns dynamic does not allow me to use tabular reports :-(.

Any idea about how to do this?

Thanks a lot for your help.

Braulio

|||

Sorry, I didn't understand well the query (my problem is putting columns on top)... you want columns in the left hand side... mmm... for horizontal reports there is an interesting post in a blog tell me if this is what you are looking for

Does Reporting Services support horizontal tables (fixed rows and dynamic columns)http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx

HTH

Braulio

|||

Brauliod wrote:

Sorry, I didn't understand well the query (my problem is putting columns on top)... you want columns in the left hand side... mmm... for horizontal reports there is an interesting post in a blog tell me if this is what you are looking for

Does Reporting Services support horizontal tables (fixed rows and dynamic columns)http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx

HTH

Braulio

Hmm, and does this hack work well for Excel exporting ?|||

Mmmm...

I'm not sure I found this solution meanwhile I was looking for another, I just bookmarked it :-(.

I think it's worth to give a try, it doesn't use subtables or strange stuff, and the guy that wrote that stuff owns a lot of kudos, must be good stuff.

Good luck

Braulio

Matrix Totals Problem - SSRS 2005

I wasn't able to get my totals working accurately in a table footer using SUM, so I though hmm, maybe try matrix...well, still same type of problems...a bit different but still, my totals are not totalling the columns!

Screen Shot:

http://www.photopizzaz.biz/matrix_totals_problem.jpg

I don't know what else to do, I give up at this point and need help from someone here who has already gone through these headaches!

I don't understand why BOL doesn't address more complicated issues such as this...they barely tell you anything there.

I believe you need to preface your column expressions with sum: ie: the expression under customer number would be: sum(fields!customernumber.Value)sql

Matrix Report - Subtotals

Good moring,
I am working on my first matrix report and trying to add in a total for a
group and rows
Table is as follows:
Account Manager Job Number Jan Sales Feb Sales
Peter J54455 598 60
Peter J65559 500 70
David J76666 400 80
Jane J74445 700 90
I would like to have:
1) a subtotal row when the Account Manager changes.
2) a total for each row.
Example:
Account Manager Job Number Jan Sales Feb Sales Total
Peter J54455 500 60
560
Peter J65559 500 70
570
Total Peter 1000 130
1130
David J76666 400 80
480
Total David 400 80
480
Jane J74445 700 90
790
Total Jane 700 90
790
I know this is inherent in the matrix report, however I have spent hours
trying to figure out and still cannot determine how to achieve both of these
2 things (simple as they may be).
Can anyone help?
Thanks JamesAs an amendment,
I have discovered how to add row total so ignore that part.
As to the subtotal on the Account Manager I still have an issue with that.
I tried right clicking on the Account Manager name and choose subtotal.
However this puts a grand total for all account Managers, not a subtotal for
each account manager. Any ideas'?

Matrix Question

Hello,
I have been working away with the SSRS and am getting pretty comfortable
with some of it's features, benefits and limitations. I have created a
series of Matrix reports (pivot tables) that show sales from 2004, 2005,
2006, 2007 and even future sales of 2008. My goal is to have a comparison of
2004 versus 2005; 2005 versus 2006 and so on. In Excel I understand how to
acomplish this goal but I don't see how I acomplish this calculation in a
matrix. My field for the year is 'year(eve_date)'.
Thanks in advance - I'm sure it's simply and I'm just being brain dead.
ChrisOn May 9, 2:32 pm, "Chris Marsh" <cma...@.synergy-intl.com> wrote:
> Hello,
> I have been working away with the SSRS and am getting pretty comfortable
> with some of it's features, benefits and limitations. I have created a
> series of Matrix reports (pivot tables) that show sales from 2004, 2005,
> 2006, 2007 and even future sales of 2008. My goal is to have a comparison of
> 2004 versus 2005; 2005 versus 2006 and so on. In Excel I understand how to
> acomplish this goal but I don't see how I acomplish this calculation in a
> matrix. My field for the year is 'year(eve_date)'.
> Thanks in advance - I'm sure it's simply and I'm just being brain dead.
> Chris
I would suggest handling this functionality in the query/stored
procedure that is sourcing the report. I normally use while loops or
cursors to accomplish this. Hope this is helpful.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks - I hoped that this could be done on the report but we can try the
query idea.
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1178755079.558957.64180@.y80g2000hsf.googlegroups.com...
> On May 9, 2:32 pm, "Chris Marsh" <cma...@.synergy-intl.com> wrote:
>> Hello,
>> I have been working away with the SSRS and am getting pretty comfortable
>> with some of it's features, benefits and limitations. I have created a
>> series of Matrix reports (pivot tables) that show sales from 2004, 2005,
>> 2006, 2007 and even future sales of 2008. My goal is to have a comparison
>> of
>> 2004 versus 2005; 2005 versus 2006 and so on. In Excel I understand how
>> to
>> acomplish this goal but I don't see how I acomplish this calculation in a
>> matrix. My field for the year is 'year(eve_date)'.
>> Thanks in advance - I'm sure it's simply and I'm just being brain dead.
>> Chris
>
> I would suggest handling this functionality in the query/stored
> procedure that is sourcing the report. I normally use while loops or
> cursors to accomplish this. Hope this is helpful.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||Best possible is to bring from query, if there is any limitations on writing
the query or access etc... try calculated fields
Amarnath
"Chris Marsh" wrote:
> Thanks - I hoped that this could be done on the report but we can try the
> query idea.
> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
> news:1178755079.558957.64180@.y80g2000hsf.googlegroups.com...
> > On May 9, 2:32 pm, "Chris Marsh" <cma...@.synergy-intl.com> wrote:
> >> Hello,
> >>
> >> I have been working away with the SSRS and am getting pretty comfortable
> >> with some of it's features, benefits and limitations. I have created a
> >> series of Matrix reports (pivot tables) that show sales from 2004, 2005,
> >> 2006, 2007 and even future sales of 2008. My goal is to have a comparison
> >> of
> >> 2004 versus 2005; 2005 versus 2006 and so on. In Excel I understand how
> >> to
> >> acomplish this goal but I don't see how I acomplish this calculation in a
> >> matrix. My field for the year is 'year(eve_date)'.
> >>
> >> Thanks in advance - I'm sure it's simply and I'm just being brain dead.
> >>
> >> Chris
> >
> >
> > I would suggest handling this functionality in the query/stored
> > procedure that is sourcing the report. I normally use while loops or
> > cursors to accomplish this. Hope this is helpful.
> >
> > Regards,
> >
> > Enrique Martinez
> > Sr. Software Consultant
> >
>
>

Matrix order of totals

I have a matrix that shows sums by month. The dataset returns both month and quarter. I want to have subtotals by Quarter. I have this working and the matrix returns columns as follows:
Jan Feb March Q1 April May June Q2 ... Total

What I would like to have is
Jan Feb March April May ... Q1 Q2 Q3 Q4 Total

Is there anyway to have subtotals displayed at the end of the row?

For dynamic groups (e.g. quarter), the subtotals are always part of that group. They currently cannot be moved out of that group instance.

Another way of doing this, is to use a table in the report with explicit columns for Jan, Feb, ..., Dec, Q1, ..., Q4, Total. You would then need to use expressions to calculate your month/quarter/year aggregations.

-- Robert

Matrix Like Table

Hi Friends,

I am working on a sport site, where i have to create a Calender Likething where i have to show which sport is avaible in which month,we can have any numbers of sports as the club grow it can add 100s ofdifferent sport to itself, so i was not able to understand how to makea database for this kind of thing, can anyone please help

Games - Jan Feb Mar Apr May Jun Jul …..Dec
Cricket Yes Yes Yes No No No No ....
Hockey No Yes Yes No No Yes Yes ....
Horse R Yes No No No Yes Yes Yes ....

Thanks in Advance
Regards
Viking

Hi
you can try this structure...

Tbl_Sport
Sports_ID(Unique Key , Auto Incremented)
MonthsChar(10)
SportsVarChar(30)
AvailbilityBinary

Matrix Expression Problem

Have a matrix with three columns depicting the following table:
# of Results # of Results
% of Results
within 5 working days > 5 working days >5
working days
January 33 1
February 24 16
etc
The two # of Results colums have a count(Fields!January.Value) in the data
box.
I now need to get the % column done. I am having difficulty in doing this.
Any help would be greatly appreciated.You could add a subtotal column. In the matrix cell, you then need to take
into account the subtotal scope and use a different expression to calculate
the percentage. Something similar to this:
=iif(InScope("MatrixColumnGroupingName"), Count(Fields!Cases.Value),
Sum(iif( /* condition for the first column group instance */, 1, 0)) /
Sum(iif( /* condition for the second column group instance */, 1, 0)))
The comments in the expression above need to be replaced with the matrix
column grouping expression (basically identify the data rows that contribute
to the first grouping column vs. the second grouping column). For more
information about the usage of the InScope(...) function, please check the
MSDN documentation: http://msdn2.microsoft.com/en-us/library/ms226955
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
news:A5123C30-6A02-4480-A75E-8D3D6FD9AD43@.microsoft.com...
> Have a matrix with three columns depicting the following table:
> # of Results # of Results
> % of Results
> within 5 working days > 5 working days
> >5
> working days
> January 33 1
> February 24 16
> etc
> The two # of Results colums have a count(Fields!January.Value) in the data
> box.
> I now need to get the % column done. I am having difficulty in doing
> this.
> Any help would be greatly appreciated.
>|||BTW: in your case it seems like you don't really need a matrix. You can use
a table with a table grouping based on the month. Then inside the table
columns, you use expressions like shown below to determine the actual
counts:
=Sum(iif( /* condition */, 1, 0))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:ucv9B0uwFHA.720@.TK2MSFTNGP15.phx.gbl...
> You could add a subtotal column. In the matrix cell, you then need to take
> into account the subtotal scope and use a different expression to
> calculate the percentage. Something similar to this:
> =iif(InScope("MatrixColumnGroupingName"), Count(Fields!Cases.Value),
> Sum(iif( /* condition for the first column group instance */, 1, 0)) /
> Sum(iif( /* condition for the second column group instance */, 1, 0)))
> The comments in the expression above need to be replaced with the matrix
> column grouping expression (basically identify the data rows that
> contribute to the first grouping column vs. the second grouping column).
> For more information about the usage of the InScope(...) function, please
> check the MSDN documentation:
> http://msdn2.microsoft.com/en-us/library/ms226955
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
> news:A5123C30-6A02-4480-A75E-8D3D6FD9AD43@.microsoft.com...
>> Have a matrix with three columns depicting the following table:
>> # of Results # of Results
>> % of Results
>> within 5 working days > 5 working days >5
>> working days
>> January 33 1
>> February 24 16
>> etc
>> The two # of Results colums have a count(Fields!January.Value) in the
>> data
>> box.
>> I now need to get the % column done. I am having difficulty in doing
>> this.
>> Any help would be greatly appreciated.
>>
>|||Thanks for the point in the right direction Robert.
Still not sure how to get the percentage column. Below is my code for the
report thus far. Probably crude but hey im still learning this stuff.
SELECT
PMI.LaboratorySentToOranisationId as LabId,
O.Name as LabName,
TTLI.Name as TestType,
LTD.DateRecieved as DateReceived,
LTD.TestCompletedDate as CompleteDate,
/*# of Results within 5 working days*/
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '01') then 1
Else 0
End) as January,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '02') then 1
Else 0
End) as February,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '03') then 1
Else 0
End) as March,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '04') then 1
Else 0
End) as April,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '05') then 1
Else 0
End) as May,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '06') then 1
Else 0
End) as June,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '07') then 1
Else 0
End) as July,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '08') then 1
Else 0
End) as August,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '09') then 1
Else 0
End) as September,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '10') then 1
Else 0
End) as October,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '11') then 1
Else 0
End) as November,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) <=5) and
(DATEPART(month, LTD.TestCompletedDate) = '12') then 1
Else 0
End) as December,
/*# of Results >5 Working Days*/
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '01') then 1
Else 0
End) as Jan1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '02') then 1
Else 0
End) as Feb1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '03') then 1
Else 0
End) as Mar1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '04') then 1
Else 0
End) as Apr1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '05') then 1
Else 0
End) as May1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '06') then 1
Else 0
End) as Jun1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '07') then 1
Else 0
End) as Jul1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '08') then 1
Else 0
End) as Aug1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '09') then 1
Else 0
End) as Sep1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '10') then 1
Else 0
End) as Oct1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '11') then 1
Else 0
End) as Nov1,
(CASE when (DATEDIFF(day, LTD.DateRecieved, LTD.TestCompletedDate) >5) and
(DATEPART(month, LTD.TestCompletedDate) = '12') then 1
Else 0
End) as Dec1
FROM
PostMortemInspection PMI
INNER JOIN LabTestRequest LTR on LTR.PostMortemInspectionId = PMI.Id
INNER JOIN Organisation O on PMI.LaboratorySentToOranisationId = O.Id
AND (O.EndDate is NULL)
INNER JOIN TestTypeLookUpItem TTLI on LTR.TestTypeLookUpItemId = TTLI.Id and
(TTLI.Id = '13')
INNER JOIN LabTestDetail LTD on LTR.Id = LTD.LabTestRequestId
--where (ltd.DateRecieved is NOT NULL)
As you can see im using Case statements for each column of data. In the
field of the table im using =SUM(Fields!January.Value). So getting the % is
hard. What I was trying to do was take (column B * 100) / column A.
Something along these lines: =SUM(Fields!Jul1.Value * 100) /
Fields!July.Value. However im getting Infinity displayed.
Any more help would be greatly appreciated.
Cheers
"Robert Bruckner [MSFT]" wrote:
> BTW: in your case it seems like you don't really need a matrix. You can use
> a table with a table grouping based on the month. Then inside the table
> columns, you use expressions like shown below to determine the actual
> counts:
> =Sum(iif( /* condition */, 1, 0))
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:ucv9B0uwFHA.720@.TK2MSFTNGP15.phx.gbl...
> > You could add a subtotal column. In the matrix cell, you then need to take
> > into account the subtotal scope and use a different expression to
> > calculate the percentage. Something similar to this:
> >
> > =iif(InScope("MatrixColumnGroupingName"), Count(Fields!Cases.Value),
> > Sum(iif( /* condition for the first column group instance */, 1, 0)) /
> > Sum(iif( /* condition for the second column group instance */, 1, 0)))
> >
> > The comments in the expression above need to be replaced with the matrix
> > column grouping expression (basically identify the data rows that
> > contribute to the first grouping column vs. the second grouping column).
> > For more information about the usage of the InScope(...) function, please
> > check the MSDN documentation:
> > http://msdn2.microsoft.com/en-us/library/ms226955
> >
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
> >
> > "Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
> > news:A5123C30-6A02-4480-A75E-8D3D6FD9AD43@.microsoft.com...
> >> Have a matrix with three columns depicting the following table:
> >>
> >> # of Results # of Results
> >> % of Results
> >> within 5 working days > 5 working days >5
> >> working days
> >>
> >> January 33 1
> >> February 24 16
> >>
> >> etc
> >>
> >> The two # of Results colums have a count(Fields!January.Value) in the
> >> data
> >> box.
> >> I now need to get the % column done. I am having difficulty in doing
> >> this.
> >> Any help would be greatly appreciated.
> >>
> >>
> >
> >
>
>

Matrix doubt!

Hi!

I have a query that returns the productivity of a project during a period, and other that gives me which employees where working on the project at the period. I can't tell how much each employee producted, all I can say is that one employee worked on the project on that period.
When I join both queries I obviously get repeated rows:

Proj1 Employee1 1000
Proj1 Employee2 1000
Proj1 Employee3 1000

But I would like to have both information on the same matrix like this:

Project Employees Total Project Productivity

Proj1 Employee1 1000
Employee2
Employee3

I mean, I don't want the total project productivity to appear once for each employee.
I also tryed to change the order, putting the employees on the data cell:

Project Total Project Productivity Employees

Proj1 1000 Employee1
Employee2
Employee3

but I got only the first employee name (it uses function first).
Is there anyway I can do any of the matrix above?

Thank you!

On this process

When I join both queries I obviously get repeated rows:

Proj1 Employee1 1000
Proj1 Employee2 1000
Proj1 Employee3 1000

Use suppress duplicate rows on the property on the proj1 and 1000 column, that should get you this

Proj1 Employee1 1000
Employee2
Employee3

Carl

|||Thank you!

Matrix - Show Percentage

Hi,
I'm hoping that someone can help me out; I'm working under a (past-due)
deadline and cannot seem to get this to work...
I need to create a matrix that looks like this:
Shipped Pending Total %Complete
Customer A
Order Type A $100 0 $100 100%
Order Type B $150 $150 $300 50%
Order Type C $100 $300 $400 25%
Customer B
Order Type A $900 0 $900 100%
----
Totals $1250 $450 $1700 n/a
I have no problems until I get to the percentage part... how can I add it to
the subtotal?
Any help would be greatly appreciated!!What will the total percent be based on? Or do you just want something else
than "n/a"?
If you want to know if you're in the totals rows or not, you add the InScope
function. This will give you some control over what calculation happens
where in the matrix:
=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In
Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of
ColumnGroup1", "In Subtotal of entire matrix"))
Kaisa
"Kathy" <Kathy@.discussions.microsoft.com> wrote in message
news:C92E5D92-DE6C-4402-B756-493E5181069A@.microsoft.com...
> Hi,
> I'm hoping that someone can help me out; I'm working under a (past-due)
> deadline and cannot seem to get this to work...
> I need to create a matrix that looks like this:
> Shipped Pending Total
> %Complete
> Customer A
> Order Type A $100 0 $100 100%
> Order Type B $150 $150 $300 50%
> Order Type C $100 $300 $400 25%
> Customer B
> Order Type A $900 0 $900 100%
> ----
> Totals $1250 $450 $1700 n/a
> I have no problems until I get to the percentage part... how can I add it
> to
> the subtotal?
> Any help would be greatly appreciated!!
>|||in the totals line take the total/shipped this will give the percent of
the total. if that is what you are looking for|||I was trying to figure out how to have the matrix show that detail. The
inscope function was my saving... Thanks!
"dt1820@.gmail.com" wrote:
> in the totals line take the total/shipped this will give the percent of
> the total. if that is what you are looking for
>|||How does InScope work? where did you put it? I am not familiar with that
but I will need to be shortly.
"Kathy" wrote:
> I was trying to figure out how to have the matrix show that detail. The
> inscope function was my saving... Thanks!
> "dt1820@.gmail.com" wrote:
> > in the totals line take the total/shipped this will give the percent of
> > the total. if that is what you are looking for
> >
> >|||Where do you put the InScope function? I need to do the following:
Cat A1 Cat B2 Total %
Order Type A 5 2 7 8.52%
(7/82)
Order Type B 10 15 25 30.48%
(25/82)
Order Type C 20 30 50 60.97%
(50/82
----
Totals 35 47 82 100%
I have everything but the % column.
Thanks
Todd Bannar
"Kaisa M. Lindahl" wrote:
> What will the total percent be based on? Or do you just want something else
> than "n/a"?
> If you want to know if you're in the totals rows or not, you add the InScope
> function. This will give you some control over what calculation happens
> where in the matrix:
> =iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In
> Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of
> ColumnGroup1", "In Subtotal of entire matrix"))
> Kaisa
> "Kathy" <Kathy@.discussions.microsoft.com> wrote in message
> news:C92E5D92-DE6C-4402-B756-493E5181069A@.microsoft.com...
> > Hi,
> >
> > I'm hoping that someone can help me out; I'm working under a (past-due)
> > deadline and cannot seem to get this to work...
> >
> > I need to create a matrix that looks like this:
> >
> > Shipped Pending Total
> > %Complete
> > Customer A
> > Order Type A $100 0 $100 100%
> > Order Type B $150 $150 $300 50%
> > Order Type C $100 $300 $400 25%
> > Customer B
> > Order Type A $900 0 $900 100%
> > ----
> > Totals $1250 $450 $1700 n/a
> >
> > I have no problems until I get to the percentage part... how can I add it
> > to
> > the subtotal?
> >
> > Any help would be greatly appreciated!!
> >
>
>

Matching relational records. Is it possible using Data Minig?

Problem:
I am working on a price comparison system which matches the best prices for a purchase (or an order) from exisiting purchase data.
The order is stored in multiple tables including order details (stores major items purchased: e.g., PC) and order sub-details (optional items purchased with the major items: e.g., speakers, backup device, webcam etc.).
There could be a number of major items in an order and each major item could have multiple related sub items. The other variables that affect the price include trade-ins if any, sales going on at the time of order, number of units etc.

Now, for any new configuration (major items/related sub items), the system should be able to return a list of previous purchases made with similar configurations, and similar variables (quatities, trade-ins etc). Even if the same model is not present, similar pcs by the same vendor should be considered. etc etc.

Questions:
Is this possible using Data mining?
If yes, which algorithm is recommended?

Also, can I assign/modify any kind of weights to certain variables (if same model: .6 ; if same model not available but pcs made by same manufacturer available: .3 ; by other manufacturers: .1)?

Any help will be greatly appreciated.

Thanks,
Jojy

This seems like a reasonable problem for data mining. I would recommend decision trees, neural nets, or logistic regression. There is no way in SS2k5 to weight attributes, however, you can simulate gross weighting in NN and LR by duplicating columns. E.g. if you have a column "model" which you want to weight twice as much as other columns, you duplicate it to "model" and "model1" with the same data inside.

Master-Detail Searching, best approach?

Hello, I'm relatively new to using full-text search. I'm working on a =
document management program.
Basically my data it stored in two tables, [Documents], and =
[DocDetails], with a one-to-many relationship. [Documents] contains a =
record for each document stored, and [DocDetails] has many records =
concerning a document, each with an associated text field.
I'm really after the ability to search on a query '"jane" and "doe"', =
where I would be able to pull from [Documents] all documents that have =
matching records in [DocDetails]. My problem comes in how to query =
[DocDetails], some records would have "jane" in them, and some records =
would have "doe" in them, never would you see one record in [DocDetails] =
having both terms in the same text field. (I think this kinda negates =
using a strategy where the query has an "and" or "or" in it.
Maybe there is a way around this, I just haven't thought of. Right now, =
the only thing I can this is to split up my queries into only one word, =
and join the results.
If someone could give me a nudge in the right direction I'd appreciate =
it!
Thanks!,
--Michael
Raterus,
The below code was posted recently in regards to a "Parent - Child"
relationship, and should also work with your "Master - Detail" searching.
Tables:
contact(contact_id, name)
role(role_id, description)
contact_role (contact_id, role_id)
How can I find all contact where the search string is in the contact name
and or roles description? This is the quick and simplest way. You can
rewrite this query using outer-joins. The code is not tested. So expect some
syntax errors.
select * from contact where contactid in
(select contactid from contact_role where contactid in (select key from
containstable(contact,name,'search string') or
roleid in (select key from containstable(role,description,'search
string'))
You should be able to alter the above code to fit your tables. If you have
an alternative approach or additional questions, please post them here..
Regards,
John
"Raterus" <raterus@.spam.org> wrote in message
news:OI#yuv1NEHA.556@.TK2MSFTNGP10.phx.gbl...
Hello, I'm relatively new to using full-text search. I'm working on a
document management program.
Basically my data it stored in two tables, [Documents], and [DocDetails],
with a one-to-many relationship. [Documents] contains a record for each
document stored, and [DocDetails] has many records concerning a document,
each with an associated text field.
I'm really after the ability to search on a query '"jane" and "doe"', where
I would be able to pull from [Documents] all documents that have matching
records in [DocDetails]. My problem comes in how to query [DocDetails],
some records would have "jane" in them, and some records would have "doe" in
them, never would you see one record in [DocDetails] having both terms in
the same text field. (I think this kinda negates using a strategy where the
query has an "and" or "or" in it.
Maybe there is a way around this, I just haven't thought of. Right now, the
only thing I can this is to split up my queries into only one word, and join
the results.
If someone could give me a nudge in the right direction I'd appreciate it!
Thanks!,
--Michael
|||Actually that was the first post I read before I posted, it helped =
initally. I've adapted it a little bit and have come up with this. =
It's a mess, I know it :-) It is working how I want it to though, the =
only problem I forsee, is how do I have different queries for a =
different number of search terms. Using this strategy I'll have to have =
a different query if they search for "jane doe john brown" rather than =
just "jane doe" This is all for an asp.net web application, so it =
wouldn't be that difficult for me to create my own query on the fly and =
send it over, but if there is a better way, I'd love to hear it!
select * from documents where docID in
(
select t1.docID
from (
select docID
from docdetails
where ddID in
(
select [key]
from containstable(docdetails,value,'"jane"')
)
) as t1 inner join=20
(
select docID
from docdetails
where ddID in
(
select [key]
from containstable(docdetails,value,'"doe"')
)
) as t2 on t1.docID =3D t2.docID
)
"John Kane" <jt-kane@.comcast.net> wrote in message =
news:%235axQM2NEHA.3348@.TK2MSFTNGP09.phx.gbl...
> Raterus,
> The below code was posted recently in regards to a "Parent - Child"
> relationship, and should also work with your "Master - Detail" =
searching.
>=20
> Tables:
> contact(contact_id, name)
> role(role_id, description)
> contact_role (contact_id, role_id)
>=20
> How can I find all contact where the search string is in the contact =
name
> and or roles description? This is the quick and simplest way. You can
> rewrite this query using outer-joins. The code is not tested. So =
expect some
> syntax errors.
>=20
> select * from contact where contactid in
> (select contactid from contact_role where contactid in (select key =
from
> containstable(contact,name,'search string') or
> roleid in (select key from containstable(role,description,'search
> string'))
>=20
> You should be able to alter the above code to fit your tables. If you =
have
> an alternative approach or additional questions, please post them =
here..
>=20
> Regards,
> John
>=20
>=20
>=20
>=20
> "Raterus" <raterus@.spam.org> wrote in message
> news:OI#yuv1NEHA.556@.TK2MSFTNGP10.phx.gbl...
> Hello, I'm relatively new to using full-text search. I'm working on a
> document management program.
>=20
> Basically my data it stored in two tables, [Documents], and =
[DocDetails],
> with a one-to-many relationship. [Documents] contains a record for =
each
> document stored, and [DocDetails] has many records concerning a =
document,
> each with an associated text field.
>=20
> I'm really after the ability to search on a query '"jane" and "doe"', =
where
> I would be able to pull from [Documents] all documents that have =
matching
> records in [DocDetails]. My problem comes in how to query =
[DocDetails],
> some records would have "jane" in them, and some records would have =
"doe" in
> them, never would you see one record in [DocDetails] having both terms =
in
> the same text field. (I think this kinda negates using a strategy =
where the
> query has an "and" or "or" in it.
>=20
> Maybe there is a way around this, I just haven't thought of. Right =
now, the
> only thing I can this is to split up my queries into only one word, =
and join
> the results.
>=20
> If someone could give me a nudge in the right direction I'd appreciate =
it!
>=20
> Thanks!,
> --Michael
>=20
>