MAX and DATEDIFF: columns

Showing posts with label columns. Show all posts

Friday, March 23, 2012

MCP Exam Study Query

Your question is not very clear.
You can join on any two columns with similar datatypes.
There does not have to be an explicit Parent-Child (PK-FK) relationship.
However, without a relationship, the question is: would the data provide any
useful information? And the answer is: it depends.
If that helps, then I was lucky. If not, please ask again, offering some
information about what you are seeking.
Regards,
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:%23t9mnTWlGHA.4268@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I'm currently studying for MCP exam 70-229. I'm on the database logical
> modeling section, and have come up with this question:
> In a non-identifying relationship, how would a join work, or is the
> term used to describe a situation where a relationship is at fault?
> Many Thanks,
> Simon.
>Hi Arnie,
Thanks for the post, I do understand that a relatioship can be either
explicit or implicit, but I'm not sure this is what the question refers to,
to bo honest, I'm not 100% sure what the question means myself!

Heres an excerpt from the book I'm working from:
"A relationship is said to be identifying if the primary key of a child
entity contains all the attributes of a foreign key. If the primary key of
the child entity does not contain all the attributes of a foreign key, the
the relationship is non-identifying"
The more I read that statement, the more confused I get! 8o)
Any ideas?
Simon.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uTMqldWlGHA.884@.TK2MSFTNGP05.phx.gbl...
> Your question is not very clear.
> You can join on any two columns with similar datatypes.
> There does not have to be an explicit Parent-Child (PK-FK) relationship.
> However, without a relationship, the question is: would the data provide
> any useful information? And the answer is: it depends.
> If that helps, then I was lucky. If not, please ask again, offering some
> information about what you are seeking.
> Regards,
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
> news:%23t9mnTWlGHA.4268@.TK2MSFTNGP05.phx.gbl...
>|||Hi All,
I'm currently studying for MCP exam 70-229. I'm on the database logical
modeling section, and have come up with this question:
In a non-identifying relationship, how would a join work, or is the term
used to describe a situation where a relationship is at fault?
Many Thanks,
Simon.|||Your question is not very clear.
You can join on any two columns with similar datatypes.
There does not have to be an explicit Parent-Child (PK-FK) relationship.
However, without a relationship, the question is: would the data provide any
useful information? And the answer is: it depends.
If that helps, then I was lucky. If not, please ask again, offering some
information about what you are seeking.
Regards,
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:%23t9mnTWlGHA.4268@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I'm currently studying for MCP exam 70-229. I'm on the database logical
> modeling section, and have come up with this question:
> In a non-identifying relationship, how would a join work, or is the
> term used to describe a situation where a relationship is at fault?
> Many Thanks,
> Simon.
>|||Hi Arnie,
Thanks for the post, I do understand that a relatioship can be either
explicit or implicit, but I'm not sure this is what the question refers to,
to bo honest, I'm not 100% sure what the question means myself!

Monday, March 19, 2012

Maximum value of mulitple columns

Hi all

Using sql server 2005, Im trying in a query to get the maximum value of multiple columns of a table for each of its records.

What im trying to get is the last date an index was used using the table sys.dm_db_usage_stats using the date fields (last_user_seek, last_user_update...).

I looked around the forum for a solution but those i found dont seem to apply really easily to a query.

Anyone got a suggestion?

Dale:

There have been a number of discussions about a similar issue; give a look to this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=733186&SiteID=1

|||

Dale L.,

Try using new operators UNPIVOT and "CROSS APPLY".

Code Snippet

create table dbo.t1 (

pk int not null identity unique,

c1 int,

c2 int,

c3 int

)

insert into dbo.t1(c1, c2, c3) values(1, 2, 3)

insert into dbo.t1(c1, c2, c3) values(4, 6, 5)

insert into dbo.t1(c1, c2, c3) values(9, 7, 8)

select

a.pk,

b.max_value

from

dbo.t1 as a

cross apply

(

select

max(unpvt.[value]) as max_value

from

(

select

t.pk,

t.c1 as [1],

t.c2 as [2],

t.c3 as [3]

from

dbo.t1 as t

where

t.pk = a.pk

) as pvt

unpivot

([value] for [col] in ([1], [2], [3])) as unpvt

group by

unpvt.pk

) as b

drop table dbo.t1

AMB

maximum row size problem

Hi,
I've got a table in a database [columns: int, int, int, nvarchar(100), nvarchar(200), nvarchar(500), nvarchar(4000) ]

The problem I have is that when I try to insert a record, sqlserver is returning "Cannot create a row of size 9629 which is greater than the allowable maximum of 8060."

I had a search before posting here and best answer I could find was to alter maxlen in sysindexes - however, I'm far from being an expert on sqlserver so I dont really want to just blindly alter things and find I screw something up.

Any ideas on how to solve this?

Thanksnvarchar(4000)? Why? Why not an NTEXT?

NTEXT can be up to 2gb, and does NOT count against the limit.

Otherwise you can NOT change this limit. 8060 is a hardcoded limit.|||ok, i'll try that, thanks

maximum row size exceeds the maximum number of bytes per row (8060

Hi,
I have created a table (say Table1) with few columns, with one of the
columns (say column1) having the data type as Varchar(8000).
Now I run the below query -
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
if not exists (select * from dbo.syscolumns
where id = object_id(N'[dbo].[Table1]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1
and name = 'Column1')
ALTER TABLE dbo.Table1 ADD
Column1 varchar(500) NULL
GO
COMMIT
Now that the column Column1 already exists, the add column statement won't
be executed. But still I get the warning -
"Warning: The table 'Table1' has been created but its maximum row size
(8579) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
of a row in this table will fail if the resulting row length exceeds 8060
bytes."
My question is, now that the column already exists, the statement itself
won't be executed. Then why do I still get this warning?That has to do with the sequence of query processing and when the warning is generated. Obviously
the warning (the fact that this table will have a row size for which you can exceed the limit in
your data) is generated in a stage which is earlier than when the statements are actually executed.
In other words, the If statement haven't been executed at this stage.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"swap@.@.@." <swap@.discussions.microsoft.com> wrote in message
news:73AC8FFC-4041-4B22-AB25-4CCDB94598FC@.microsoft.com...
> Hi,
> I have created a table (say Table1) with few columns, with one of the
> columns (say column1) having the data type as Varchar(8000).
> Now I run the below query -
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> if not exists (select * from dbo.syscolumns
> where id = object_id(N'[dbo].[Table1]')
> and OBJECTPROPERTY(id, N'IsUserTable') = 1
> and name = 'Column1')
> ALTER TABLE dbo.Table1 ADD
> Column1 varchar(500) NULL
> GO
> COMMIT
> Now that the column Column1 already exists, the add column statement won't
> be executed. But still I get the warning -
> "Warning: The table 'Table1' has been created but its maximum row size
> (8579) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
> of a row in this table will fail if the resulting row length exceeds 8060
> bytes."
> My question is, now that the column already exists, the statement itself
> won't be executed. Then why do I still get this warning?
>

Friday, March 9, 2012

Maximum number of columns in an index

I am upgrading from Access, where you can only have 10 fields in a primary key or unique index. Is this also the limit in SQL Server? If not, what is the limit?

Thanks for any help on this.

From Books Online, Topic: 'Create Index'

"Up to 16 columns can be combined into a single composite index key."

Maximum number of columns exception

Folks, I'm having trouble with one of my DB's (on SQL server 2005).
I can't seem to be able to add a new column due to the following:
System.Data.SqlClient.SqlException: CREATE TABLE failed because column
'_Test1' in table 'TestAccountsProperties' exceeds the maximum of 1024
columns.
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
exception, Boolean breakConnection)
The offending call looks like:
ALTER TABLE [TestAccountsProperties] ADD [_Test1] VARCHAR(128);
But this also doesn't work just using the Management Studio UI.
The mystery to me is that the table doesn't have anywhere close to 1024
columns. It has 22.
Any ideas?
Eloy
Can you post your .NET code + table's structure to be tested?
"Eloy Perez" <EloyPerez@.discussions.microsoft.com> wrote in message
news:CA48A7AA-6FE0-45F0-A3FD-AF366E852428@.microsoft.com...
> Folks, I'm having trouble with one of my DB's (on SQL server 2005).
> I can't seem to be able to add a new column due to the following:
> System.Data.SqlClient.SqlException: CREATE TABLE failed because column
> '_Test1' in table 'TestAccountsProperties' exceeds the maximum of 1024
> columns.
> at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception,
> Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
> exception, Boolean breakConnection)
> The offending call looks like:
> ALTER TABLE [TestAccountsProperties] ADD [_Test1] VARCHAR(128);
> But this also doesn't work just using the Management Studio UI.
> The mystery to me is that the table doesn't have anywhere close to 1024
> columns. It has 22.
> Any ideas?

Maximum number of columns exception

Folks, I'm having trouble with one of my DB's (on SQL server 2005).
I can't seem to be able to add a new column due to the following:
System.Data.SqlClient.SqlException: CREATE TABLE failed because column
'_Test1' in table 'TestAccountsProperties' exceeds the maximum of 1024
columns.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
The offending call looks like:
ALTER TABLE [TestAccountsProperties] ADD [_Test1] VARCHAR(128);
But this also doesn't work just using the Management Studio UI.
The mystery to me is that the table doesn't have anywhere close to 1024
columns. It has 22.
Any ideas?Eloy
Can you post your .NET code + table's structure to be tested?
"Eloy Perez" <EloyPerez@.discussions.microsoft.com> wrote in message
news:CA48A7AA-6FE0-45F0-A3FD-AF366E852428@.microsoft.com...
> Folks, I'm having trouble with one of my DB's (on SQL server 2005).
> I can't seem to be able to add a new column due to the following:
> System.Data.SqlClient.SqlException: CREATE TABLE failed because column
> '_Test1' in table 'TestAccountsProperties' exceeds the maximum of 1024
> columns.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
> The offending call looks like:
> ALTER TABLE [TestAccountsProperties] ADD [_Test1] VARCHAR(128);
> But this also doesn't work just using the Management Studio UI.
> The mystery to me is that the table doesn't have anywhere close to 1024
> columns. It has 22.
> Any ideas?

Maximum number of columns exception

Folks, I'm having trouble with one of my DB's (on SQL server 2005).
I can't seem to be able to add a new column due to the following:
System.Data.SqlClient.SqlException: CREATE TABLE failed because column
'_Test1' in table 'TestAccountsProperties' exceeds the maximum of 1024
columns.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
The offending call looks like:
ALTER TABLE [TestAccountsProperties] ADD [_Test1] VARCHAR(128);
But this also doesn't work just using the Management Studio UI.
The mystery to me is that the table doesn't have anywhere close to 1024
columns. It has 22.
Any ideas?Eloy
Can you post your .NET code + table's structure to be tested?
"Eloy Perez" <EloyPerez@.discussions.microsoft.com> wrote in message
news:CA48A7AA-6FE0-45F0-A3FD-AF366E852428@.microsoft.com...
> Folks, I'm having trouble with one of my DB's (on SQL server 2005).
> I can't seem to be able to add a new column due to the following:
> System.Data.SqlClient.SqlException: CREATE TABLE failed because column
> '_Test1' in table 'TestAccountsProperties' exceeds the maximum of 1024
> columns.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
> The offending call looks like:
> ALTER TABLE [TestAccountsProperties] ADD [_Test1] VARCHAR(128);
> But this also doesn't work just using the Management Studio UI.
> The mystery to me is that the table doesn't have anywhere close to 1024
> columns. It has 22.
> Any ideas?

Wednesday, March 7, 2012

Maximum Columns in a Matrix?

Hi,

Does anyone know what is the maximum number of columns allowed in a matrix?

Thanks.

The whole point of using a Matrix is to have dynamic column grouping. Hence the number of columns will grow depending on the values for the field used in the column grouping expression.

-Aayush

|||That doesn't answer the question.

If I have a single column grouping with 800 different types, each displaying as a column, will it fall in a heap? If its > 256 then excel will die anyway.|||Well..what I meant is that I don't think there is any way through the report, to limit the number of columns. I suppose this solely depends upon the grouping. Hopefully I'm right.|||

For this particular report, the grouping is coming back with 400+ groups, hence 400+ columns and it is giving a rendering error when just trying to view the report. Is 256 actually the limit in Excel? Maybe it is the same in a matrix report. These reports will mostly be exported to Excel anyway (after initially being viewed on screen) so if 256 is the maximum there that will kill us anyway.

Thanks.

|||from a UI point of view, 400 anything is going to be a killer to the user.

I think you're going to have to do something will drill through.

The alternative is to build a cube and slap a pivot table on the top, at least this should be able to handle the data.

What am I saying? you'd still need to use OWC or excel to view the pivot table....|||

I remeber that I had the same problem before, and also there were an issue for printing it.

SO What I did is that in the query I did a count on the the groups so lets say I wanted to print only 10 columns on a page, I added a counter in the querry (each 10 columns will have the same number) so it gave the possibility to add a list in the report where the group is the counter that I added and then I embeded a matrix in the list. This way You will end up having multiple matrix in the report with a max of 10 columns each.

Hope this will help.

Paul.G

|||I think I might have to find a way for them to restrict the number of groups, and therefore the columns. Otherwise I might have to try out that list idea. Thanks for the help guys !

Maximum # of columns for FullText engine ?

Hi ,

I am trying to run a fulltext query and I get the following error message:

"Too many full-text columns or the full-text query is too complex to be executed"

Does anybody know if there is a limitation in the number of columns? Or what can cause this error?

Here is the sql:

SELECT DISTINCT FT_TBL.CapId, FT_TBL.Title, FT_TBL.PubMedId, FT_TBL.IssueYear, Rank

FROM ClinicalLiteratureTbl AS FT_TBL,

CONTAINSTABLE(ClinicalLiteratureTbl, *, '("body mass index" OR "BMI" OR "Quetelet`s Index" OR "Quetelet Index" OR "Quetelets Index") AND ("myocardial infarction" OR "myocardial infarct" OR "MI" OR "myocardium infarct" OR "myocardium infarction" OR "cardiac infarction" OR "myocardial necrosis" OR "coronary attack" OR "myocardium necrosis" OR "myocardial infarction syndrome" OR "myocardial necrosis syndrome" OR "heart attack" OR "coronary thrombosis" OR "AMI" OR "post-AMI" OR "post AMI" OR "post infarction" OR "post-infarction")') AS KEY_TBL WHERE FT_TBL.ArticleID = KEY_TBL.[KEY] AND FT_TBL.RaterGroupId IN (1,2,3,4) ORDER BY IssueYear DESC

Thanks

gigel,

Fulltext works with 16 columns max on indexes. Because this, your query return error.

|||

Hi,

create a new field, call it keywords, copy all relevant data in this field. Now you have just one field instead of 17.

You might consider also to eliminate the "NoiseWords" in the new Keywords-field. Keep the original data including noise in their current fields.

NoiseWords are kept in a table in your SQL-Server ProgramFiles

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\noiseENG.txt

browse this forum for more about noise

Thomas

Maximum # of columns for FullText engine ?

Hi ,

I am trying to run a fulltext query and I get the following error message:

"Too many full-text columns or the full-text query is too complex to be executed"

Does anybody know if there is a limitation in the number of columns? Or what can cause this error?

Here is the sql:

SELECT DISTINCT FT_TBL.CapId, FT_TBL.Title, FT_TBL.PubMedId, FT_TBL.IssueYear, Rank

FROM ClinicalLiteratureTbl AS FT_TBL,

Thanks

gigel,

Fulltext works with 16 columns max on indexes. Because this, your query return error.

|||

Hi,

create a new field, call it keywords, copy all relevant data in this field. Now you have just one field instead of 17.

You might consider also to eliminate the "NoiseWords" in the new Keywords-field. Keep the original data including noise in their current fields.

NoiseWords are kept in a table in your SQL-Server ProgramFiles

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\noiseENG.txt

browse this forum for more about noise

Thomas

Maximum # of columns for FullText engine ?

Hi ,

I am trying to run a fulltext query and I get the following error message:

"Too many full-text columns or the full-text query is too complex to be executed"

Does anybody know if there is a limitation in the number of columns? Or what can cause this error?

Here is the sql:

SELECT DISTINCT FT_TBL.CapId, FT_TBL.Title, FT_TBL.PubMedId, FT_TBL.IssueYear, Rank

FROM ClinicalLiteratureTbl AS FT_TBL,

Thanks

gigel,

Fulltext works with 16 columns max on indexes. Because this, your query return error.

|||

Hi,

create a new field, call it keywords, copy all relevant data in this field. Now you have just one field instead of 17.

You might consider also to eliminate the "NoiseWords" in the new Keywords-field. Keep the original data including noise in their current fields.

NoiseWords are kept in a table in your SQL-Server ProgramFiles

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\noiseENG.txt

browse this forum for more about noise

Thomas

Saturday, February 25, 2012

Max. No. of Columns in a Table

How many columns can be created in a table in SQL Server 2000? Is it affect
performance?1024.
The point is that a properly normalized database will never reach that
number.
Don't forger, the maximum row length is 8060 (excluding BLOB data types).
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Moorthy" <Moorthy@.discussions.microsoft.com> wrote in message
news:AB5DCB81-C11E-4128-BA9C-97E0A84F4830@.microsoft.com...
> How many columns can be created in a table in SQL Server 2000? Is it
> affect
> performance?|||The very fact that you ask this question scares me for your database design.
There are so few good RELATIONAL database designs that could actually need
the 1024 column limit that it almost a moot point. And yes, having 1000
columns in a table will not be good for performance either. For starters
every column would have to average less than 9 bytes each, so that would be
bothersome, but it would require that the server deal with all 1000 when you
do a select *
Now, I did highlight RELATIONAL (well, perhaps I screamed it,) because if
you are just using SQL Server as a data storage engine only, rather than
using a file system for all singleton access then who cares. But as that is
so unlikely, I will suggest you might want to get a database design book and
read it before doing any serious design :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Moorthy" <Moorthy@.discussions.microsoft.com> wrote in message
news:AB5DCB81-C11E-4128-BA9C-97E0A84F4830@.microsoft.com...
> How many columns can be created in a table in SQL Server 2000? Is it
> affect
> performance?|||The one stop reference for such queries is
"Maximum Capacity Specifications" section BOL.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Moorthy" <Moorthy@.discussions.microsoft.com> wrote in message
news:AB5DCB81-C11E-4128-BA9C-97E0A84F4830@.microsoft.com...
> How many columns can be created in a table in SQL Server 2000? Is it
> affect
> performance?|||
"Roji. P. Thomas" wrote:

> The one stop reference for such queries is
> "Maximum Capacity Specifications" section BOL.
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Moorthy" <Moorthy@.discussions.microsoft.com> wrote in message
> news:AB5DCB81-C11E-4128-BA9C-97E0A84F4830@.microsoft.com...
>
> Thanks for you all.
I'm not going to design new database. I'm just going to alter the database
for implementing new features in my project. That'swhy I asked this question
.

Monday, February 20, 2012

Max value of table column

hello,

i have a table with different columns

my table has 1 group (group per hour)

my last column of my table is a calculation of different values from that line.

in my footer of my column i want to display the max value of that column.

somehow i can't manage to achieve this.

this i an example of my table (how i want i to look like)

hour | ColA | ColB | ColC | ColD

00 15 14 16 16

03 20 21 23 23

....

23 55 16 75 75

90 51 114 75

ColD is the max value of ColA, ColB and ColC.

The footer contains the sumed values for A, B and C

In the footer for ColD i would like to display the max value of ColD (in this case 75).

This may be a stupid question, but i'm stuck on it ..

Vinnie

Here the query,

Code Snippet

Create Table #data (

[hour] Varchar(100) ,

[ColA] int ,

[ColB] int ,

[ColC] int ,

[ColD] int

);

Insert Into #data Values('00','15','14','16',NULL);

Insert Into #data Values('03','20','21','23',NULL);

Insert Into #data Values('23','55','16','75',NULL);

Select

hour,

max(case when colid=1 Then case when hour is not null then data1 else data2 end end) as [cola],

max(case when colid=2 Then case when hour is not null then data1 else data2 end end) as [colb],

max(case when colid=3 Then case when hour is not null then data1 else data2 end end) as [colc],

max(case when colid=4 Then data1 end) as [cold]

from

(

select

hour,

Isnull(id,4) colid,

Max(Case When id=1 Then cola

When id=2 Then colb

When id=3 Then colc End) data1,

Sum(Case When id=1 Then cola

When id=2 Then colb

When id=3 Then colc End) data2

from

#data

cross join

(select 1 id Union Select 2 Union Select 3) as d

Group By

hour,id

With Cube

) as Data

Group by

hour

Order By

isnull(hour,99999)

|||

Hi,

my problem is with a table in reporting services.

my data already comes from a dataset.

so my question is how to retreive the max value of a table column.

also, i work with reporting models and not with query based datasets.

but that has nothing to do with my question.

Greetings

Vinnie

|||

How much of this table have you implemented successfully? Everything but the last value at the bottom right?

Are you using report builder or Business Intelligence Studio?

|||

Hello Greg,

Thanks for the answer.

I am using visual studio to build my reports.

The table itself works fine. The only thing i cannot accomplish is to

have the max value of that specific table column in my footer.

Is what i'm asking impossible to do or am i that stupid that it's right under

my nose and cannot figure it out ?

any help is welcome.

Greetings

Vinnie

|||

VSempoux wrote:

Hello Greg,

Is what i'm asking impossible to do or am i that stupid that it's right under

my nose and cannot figure it out ?

Neither.

VSempoux wrote:

also, i work with reporting models and not with query based datasets

I don't understand this. So there is no query to generate your report? You said you weren't using report builder, so how could you be using reporting models?

We have to understand how you are going about this before we can help you. The only way I know of how to get that max value is to write it into a query....

|||

Okay Greg,

Let me clarify my situation.

I am writing reports for an application that is in dev for the moment.

We want to give our customers the opportunity to also create their own report, based on the data that

we use in our standard reports that are supplied with the app.

So, in order to don't have to do the work twice, i am making report models that i use in my reports.

With VS or BIS you can choose your original datasource, so in my case it is a report model.

My orignal report models are based on queries that i have put into views.

So in my report model data source view i select all the data from the sql view and from that i generate

my model. Afterwards i add some calculated fields in the model itself, if i have the need for them.

Then in the report itself i define datasets.

But for my datasets i can only do drag and drop the fields (enteties) i need in my report.

Of course i can define filters for the datasource.

Does this give you a better insight into my situation ?

If it would make your life easier, i could sent you some screenshots ...

Greetings

Vinnie

|||

VSempoux wrote:

My orignal report models are based on queries that i have put into views.

Then I suppose the answer is you need to create a field in the model with a query that selects the max of the dataset that produces ColA, ColB and ColC.

When I say dataset here, I am not talking about the SQL dataset. I am talking about the collection of data. It really makes no difference whether it comes from a dataset, a view, or a donkey for that matter.

hour | ColA | ColB | ColC | ColD

00 15 14 16 16

03 20 21 23 23

....

23 55 16 75 75

90 51 114 75

|||

Hi,

I already have such a field and it's doing what it's suposed to do.

But this field is set into the table group (remember the group that groups per hour)

So, when my table is displayed after generation, i have 24 lines with in the last column the max values

of several columns.

example :

this is how my table layout looks like

table group= hour(Fields!TimeValue.value) -> this groups all my data per hour

table group header contains 7 colums

column 1 : hour(Fields!TimeValue.value) sorted ascending

column 2-3-4-5-6 contains a calculation like this : (sum(Fields!CountTest1.Value)/sum(Fields!CountTotalTest.Value))*100

this returns a ratio value

column 7 contains an expression that gives me max value of column 2-3-4-5. this is the expression i use :

=CInt(math.Max((sum(Fields!CountTest1.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest2.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest3.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest4.Value)/sum(Fields!CountTotalTest.Value)*100), (sum(Fields!CountTest5.Value)/sum(Fields!CountTotalTest.Value)*100))))))

This expression returns the highest ratio value for a specif hour.

At the end i have 24 values in column 7, each time the highest value per hour.

In my table footer i cannot use the expression above, because this will calculate the highest value for the total 24 hours (which is of course different from the highest single value per hour).

Do you see now what i want to do ?

Since i do various other calculations inside my report with fields like Fields!CountTest1.value, .... i cannot do this (i think) in my sql view.

Fields!CountTest1 is also a calculated field within my dataset (=iif(Fields!TestState.Value=101 and Fields!ContactTotalTest.Value=1,1,0))

Soooooo, the only simple thing i want ... the max value, of the generated max values per hour, in my table footer !

I don't know how i can make this more clear.

Greetings

Vinnie

|||See this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2021871&SiteID=1&mode=1

Ian