April CTP superkey oddity

I've set up a table with a superkey composed of a uniqueidentifier column and a varchar column. The uniqueidentifier column is not set to be a row GUID. When I try to add a new row with a duplicate uniqueidentifier value but different varchar value, I am told it violates the IX uniqueness constraint. I can't figure out what's wrong with this since it looks valid to me. Thanks in advance for suggestions.

JoshJust an FYI- I dropped the table and recreated it as I did before and everything went as expected!

Josh

Approximate row count?

Is there any way to use the statistics to get an approximate row count on a
large table without having to do a SELECT COUNT(*)?EXEC sp_spaceused 'table_name'
SELECT MAX(rowcnt) FROM sysindexes WHERE id = OBJECT_ID('table_name')
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:384F4C0A-B30E-4074-86E9-1D48DF7EBE8B@.microsoft.com...
> Is there any way to use the statistics to get an approximate row count on
> a
> large table without having to do a SELECT COUNT(*)?|||Those will be more accurate, of course, if you first issue DBCC
UPDATEUSAGE(0) ...
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:384F4C0A-B30E-4074-86E9-1D48DF7EBE8B@.microsoft.com...
> Is there any way to use the statistics to get an approximate row count on
> a
> large table without having to do a SELECT COUNT(*)?|||Try:
select
rowcnt
from
dbo.sysindexes
where
id = object_id ('MyTable')
and
indid in (0, 1)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:384F4C0A-B30E-4074-86E9-1D48DF7EBE8B@.microsoft.com...
Is there any way to use the statistics to get an approximate row count on a
large table without having to do a SELECT COUNT(*)?|||Hi,
DBCC UPDATEUSAGE command will lock the table.
Use the below command to get the approximate table count:-
sp_spaceused <table_name>
Inacse if you have more descrepency then use the command UPDATEUSAGE which
scans the data pages in the table and makes any
necessary corrections to the sysindexes table regarding the storage space
used by each table
sp_spaceused <table_name>,@.updateusage = 'TRUE'
The above command will locks, so execute when the table access is less,
Thanks
Hari
SQL Server MVP
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:384F4C0A-B30E-4074-86E9-1D48DF7EBE8B@.microsoft.com...
> Is there any way to use the statistics to get an approximate row count on
> a
> large table without having to do a SELECT COUNT(*)?|||Thanks for the quick response. Is there any way to do something similar, but
find out how many rows fit a particular value for an indexed (or nonindexed)
column?
"Aaron Bertrand [SQL Server MVP]" wrote:

> EXEC sp_spaceused 'table_name'
> SELECT MAX(rowcnt) FROM sysindexes WHERE id = OBJECT_ID('table_name')
>
> "Ken" <Ken@.discussions.microsoft.com> wrote in message
> news:384F4C0A-B30E-4074-86E9-1D48DF7EBE8B@.microsoft.com...
>
>|||> Thanks for the quick response. Is there any way to do something similar,
> but
> find out how many rows fit a particular value for an indexed (or
> nonindexed)
> column?
No, values are stored in the table, so you need to query the table.
What is the actual problem? Does SELECT COUNT(*) take too long? How many
rows are there, is there a clustered index, and what kind of hardware is it?
I am currently looking at a table on a system here, with 70,000,000 rows,
and SELECT COUNT(*) comes back in a few seconds. Granted, I use the NOLOCK
hint for ad hoc stuff like this, and it is on enterprise-class hardware
(quad zeon, 16gb ram, emc clariion). But design is definitely going to have
a big impact here.
A|||> The above command will locks, so execute when the table access is less,
Yes Hari, I didn't mean to imply that anyone should run DBCC UPDATEUSAGE
before every query.sql

Appending from one column to another for every row in a table

Hi there,
I need to carry out a database cleaning/maintenance operation whereby I
take the text in one column called ShortDescription and add it to the
text in a column called AdditionalDetails.
I know how to copy from one to the other but that would have the effect
of overwriting the values in the AdditionalDetails column. I need
whatever is in the first column to be simply appended to whatever is in
the second. If the first has null in it, nothing should happen, and I
guess if the second has a null in it, the null should be replaced with
whatever's in the second column.
Can anyone advise me on how to do this? I'm sure it must be a relatively
simple operation but I've never seen it done.
Thanks to anyone who can help
SimonUpdate table1 set additionaldetails = isnull(additionaldetails,'') +
shortdescription
where shortdescription is not null
Jay
Simon Harvey wrote:
> Hi there,
> I need to carry out a database cleaning/maintenance operation whereby I
> take the text in one column called ShortDescription and add it to the
> text in a column called AdditionalDetails
> I know how to copy from one to the other but that would have the effect
> of overwriting the values in the AdditionalDetails column. I need
> whatever is in the first column to be simply appended to whatever is in
> the second. If the first has null in it, nothing should happen, and I
> guess if the second has a null in it, the null should be replaced with
> whatever's in the second column.
> Can anyone advise me on how to do this? I'm sure it must be a relatively
> simple operation but I've never seen it done.
> Thanks to anyone who can help
> Simon|||You need concatenation, which is implemented using the + character.
UPDATE
SET AdditionalDetails = AdditionalDetails + ShortDescription
WHERE ShortDescription IS NOT NULL
The problem with the query above is that if AdditionalDetails is NULL
the concatenation ends up as NULL. So the more robust version would
be:
UPDATE
SET AdditionalDetails = COALESCE(AdditionalDetails, '') +
ShortDescription
WHERE ShortDescription IS NOT NULL
Roy Harvey
Beacon Falls, CT
On Wed, 13 Dec 2006 16:20:40 +0000, Simon Harvey
<nothanks@.hotmail.com> wrote:
>Hi there,
>I need to carry out a database cleaning/maintenance operation whereby I
>take the text in one column called ShortDescription and add it to the
>text in a column called AdditionalDetails.
>I know how to copy from one to the other but that would have the effect
>of overwriting the values in the AdditionalDetails column. I need
>whatever is in the first column to be simply appended to whatever is in
>the second. If the first has null in it, nothing should happen, and I
>guess if the second has a null in it, the null should be replaced with
>whatever's in the second column.
>Can anyone advise me on how to do this? I'm sure it must be a relatively
>simple operation but I've never seen it done.
>Thanks to anyone who can help
>Simon|||You are both SQL Stalions!
I knew it would be simple!
Thank you - you've been a great help
Kindest Regards
Simon

Appending from one column to another for every row in a table

Hi there,
I need to carry out a database cleaning/maintenance operation whereby I
take the text in one column called ShortDescription and add it to the
text in a column called AdditionalDetails.
I know how to copy from one to the other but that would have the effect
of overwriting the values in the AdditionalDetails column. I need
whatever is in the first column to be simply appended to whatever is in
the second. If the first has null in it, nothing should happen, and I
guess if the second has a null in it, the null should be replaced with
whatever's in the second column.
Can anyone advise me on how to do this? I'm sure it must be a relatively
simple operation but I've never seen it done.
Thanks to anyone who can help
Simon
Update table1 set additionaldetails = isnull(additionaldetails,'') +
shortdescription
where shortdescription is not null
Jay
Simon Harvey wrote:
> Hi there,
> I need to carry out a database cleaning/maintenance operation whereby I
> take the text in one column called ShortDescription and add it to the
> text in a column called AdditionalDetails
> I know how to copy from one to the other but that would have the effect
> of overwriting the values in the AdditionalDetails column. I need
> whatever is in the first column to be simply appended to whatever is in
> the second. If the first has null in it, nothing should happen, and I
> guess if the second has a null in it, the null should be replaced with
> whatever's in the second column.
> Can anyone advise me on how to do this? I'm sure it must be a relatively
> simple operation but I've never seen it done.
> Thanks to anyone who can help
> Simon
|||You need concatenation, which is implemented using the + character.
UPDATE
SET AdditionalDetails = AdditionalDetails + ShortDescription
WHERE ShortDescription IS NOT NULL
The problem with the query above is that if AdditionalDetails is NULL
the concatenation ends up as NULL. So the more robust version would
be:
UPDATE
SET AdditionalDetails = COALESCE(AdditionalDetails, '') +
ShortDescription
WHERE ShortDescription IS NOT NULL
Roy Harvey
Beacon Falls, CT
On Wed, 13 Dec 2006 16:20:40 +0000, Simon Harvey
<nothanks@.hotmail.com> wrote:

>Hi there,
>I need to carry out a database cleaning/maintenance operation whereby I
>take the text in one column called ShortDescription and add it to the
>text in a column called AdditionalDetails.
>I know how to copy from one to the other but that would have the effect
>of overwriting the values in the AdditionalDetails column. I need
>whatever is in the first column to be simply appended to whatever is in
>the second. If the first has null in it, nothing should happen, and I
>guess if the second has a null in it, the null should be replaced with
>whatever's in the second column.
>Can anyone advise me on how to do this? I'm sure it must be a relatively
>simple operation but I've never seen it done.
>Thanks to anyone who can help
>Simon
|||You are both SQL Stalions!
I knew it would be simple!
Thank you - you've been a great help
Kindest Regards
Simon

Appending from one column to another for every row in a table

Hi there,
I need to carry out a database cleaning/maintenance operation whereby I
take the text in one column called ShortDescription and add it to the
text in a column called AdditionalDetails.
I know how to copy from one to the other but that would have the effect
of overwriting the values in the AdditionalDetails column. I need
whatever is in the first column to be simply appended to whatever is in
the second. If the first has null in it, nothing should happen, and I
guess if the second has a null in it, the null should be replaced with
whatever's in the second column.
Can anyone advise me on how to do this? I'm sure it must be a relatively
simple operation but I've never seen it done.
Thanks to anyone who can help
SimonUpdate table1 set additionaldetails = isnull(additionaldetails,'') +
shortdescription
where shortdescription is not null
Jay
Simon Harvey wrote:
> Hi there,
> I need to carry out a database cleaning/maintenance operation whereby I
> take the text in one column called ShortDescription and add it to the
> text in a column called AdditionalDetails
> I know how to copy from one to the other but that would have the effect
> of overwriting the values in the AdditionalDetails column. I need
> whatever is in the first column to be simply appended to whatever is in
> the second. If the first has null in it, nothing should happen, and I
> guess if the second has a null in it, the null should be replaced with
> whatever's in the second column.
> Can anyone advise me on how to do this? I'm sure it must be a relatively
> simple operation but I've never seen it done.
> Thanks to anyone who can help
> Simon|||You need concatenation, which is implemented using the + character.
UPDATE
SET AdditionalDetails = AdditionalDetails + ShortDescription
WHERE ShortDescription IS NOT NULL
The problem with the query above is that if AdditionalDetails is NULL
the concatenation ends up as NULL. So the more robust version would
be:
UPDATE
SET AdditionalDetails = COALESCE(AdditionalDetails, '') +
ShortDescription
WHERE ShortDescription IS NOT NULL
Roy Harvey
Beacon Falls, CT
On Wed, 13 Dec 2006 16:20:40 +0000, Simon Harvey
<nothanks@.hotmail.com> wrote:

>Hi there,
>I need to carry out a database cleaning/maintenance operation whereby I
>take the text in one column called ShortDescription and add it to the
>text in a column called AdditionalDetails.
>I know how to copy from one to the other but that would have the effect
>of overwriting the values in the AdditionalDetails column. I need
>whatever is in the first column to be simply appended to whatever is in
>the second. If the first has null in it, nothing should happen, and I
>guess if the second has a null in it, the null should be replaced with
>whatever's in the second column.
>Can anyone advise me on how to do this? I'm sure it must be a relatively
>simple operation but I've never seen it done.
>Thanks to anyone who can help
>Simon|||You are both SQL Stalions!
I knew it would be simple!
Thank you - you've been a great help
Kindest Regards
Simon

appending current Row ID

I am trying to append the current row ID to a string I am trying toinsert via a sproc. I have retrieved the @.@.Identity and I am passing itinto a class with a parameter and calling it using:

Listings.UpdateDB AddNewListing = new Listings.UpdateDB();

AddNewListing.InsertListing (Bathrooms.Text, Bedrooms.Text,Description.Text, Features.ToString(), Address.Text, Price.Text, FN);

I would like to add the current row ID to "FN" like:

Listings.UpdateDB AddNewListing = new Listings.UpdateDB();

AddNewListing.InsertListing(Bathrooms.Text, Bedrooms.Text,Description.Text, Features.ToString(), Address.Text, Price.Text, FN +ID);

Thanks in advance,
Justin.

Why wouldn't your UpdateDB() return the new ID (or set a property)?

Anyone work with Formula for Row?

I see the "lame" formula there in the editor for visibility.
=Fields!routing_Number.Value That is the rows identifier. I want to
compare the
Fields!NumRows.Value for the two Fields!status.Value.
Status could be "Cleared" or "Returned".
In Pseudo code
( Sum(Fields!NumRows[0].Value<Sum(Fields!NumRows[1].Value))
or maybe ?
=( Sum(Fields!NumRows[Fields!status.Value
="Cleared"].Value<Sum(Fields!NumRows[Fields!status.Value="Returned].Value))
Both fail?
This is an atempt to only show rows of report that have Returned >
Collected.
any ideas?
TIAHere is how I set the background color as expression:
=IIF(Fields!Todays_destination.Value <> Fields!Todays_source.Value, "Red",
"White")
Modify the above sample expression for your own needs.
Regards,
Roman Kiss Jr.
"_Stephen" wrote:
> I see the "lame" formula there in the editor for visibility.
> =Fields!routing_Number.Value That is the rows identifier. I want to
> compare the
> Fields!NumRows.Value for the two Fields!status.Value.
> Status could be "Cleared" or "Returned".
>
> In Pseudo code
> ( Sum(Fields!NumRows[0].Value<Sum(Fields!NumRows[1].Value))
> or maybe ?
> =( Sum(Fields!NumRows[Fields!status.Value
> ="Cleared"].Value<Sum(Fields!NumRows[Fields!status.Value="Returned].Value))
> Both fail?
> This is an atempt to only show rows of report that have Returned >
> Collected.
> any ideas?
>
> TIA
>
>
>
>
>
>|||"Roman JR." <rkiss_at_opentext_dot_com> wrote in message
news:8692CF17-5343-4917-9238-0A156E7178C0@.microsoft.com...
> Here is how I set the background color as expression:
> =IIF(Fields!Todays_destination.Value <> Fields!Todays_source.Value, "Red",
> "White")
I see that your comparing two separate columns in your data. Mine is
normalized, sample here:
NumRows RoutingNumber Status
60 011000138 CLEARED
36 011000138 RETURNED
13 011301798 RETURNED
13 011400071 CLEARED
23 011400495 CLEARED
12 011400495 RETURNED
In this case I don't want to print the row in the Matrix for 011000138
because 36 < 60. I want to print the row for 011301798 because they are all
returned. I want to skip the other rows.
Do you know how the data is held in the object so I can attempt to enumerate
between them?
TIA
__Stephen|||Hi Stephen,
Can't you just limit your resultset in your SQL data source?
I would create my report logic with the SQL statement.
(sample sql)
SELECT
sql1.returned_records,
sql1.RoutingNumber,
'Returned' as Status
FROM
(
SELECT
RoutingNumber,
MAX(CASE WHEN status = 'Cleared' THEN count(some_unique_identifier) ELSE 0
END) AS cleared_records,
MAX(CASE WHEN status = 'Returned' THEN count(some_unique_identifier) ELSE 0
END) AS returned_records,
MAX(CASE WHEN status = 'Returned' THEN count(some_unique_identifier) ELSE 0
END) - MAX(CASE WHEN status = 'Cleared' THEN count(some_unique_identifier)
ELSE 0 END) as returned_vs_cleared
FROM some_table
GROUP BY RoutingNumber
)sql1
WHERE sql1.returned_vs_cleared > 0
Regards,
Roman
"_Stephen" wrote:
> "Roman JR." <rkiss_at_opentext_dot_com> wrote in message
> news:8692CF17-5343-4917-9238-0A156E7178C0@.microsoft.com...
> >
> > Here is how I set the background color as expression:
> >
> > =IIF(Fields!Todays_destination.Value <> Fields!Todays_source.Value, "Red",
> > "White")
> I see that your comparing two separate columns in your data. Mine is
> normalized, sample here:
> NumRows RoutingNumber Status
> 60 011000138 CLEARED
> 36 011000138 RETURNED
> 13 011301798 RETURNED
> 13 011400071 CLEARED
> 23 011400495 CLEARED
> 12 011400495 RETURNED
> In this case I don't want to print the row in the Matrix for 011000138
> because 36 < 60. I want to print the row for 011301798 because they are all
> returned. I want to skip the other rows.
> Do you know how the data is held in the object so I can attempt to enumerate
> between them?
> TIA
> __Stephen
>
>|||Or you can simply look for visibility under properties of the report, click
the plus sign that will expand the VISIBILITY properties, select expression
for HIDDEN and copy/paste the following expression:
=Fields!status.Value="Cleared"
You must apply this expression to every row in your report, this way only
RETURNED will show up.
Roman
"Roman JR." wrote:
> Hi Stephen,
> Can't you just limit your resultset in your SQL data source?
> I would create my report logic with the SQL statement.
> (sample sql)
> SELECT
> sql1.returned_records,
> sql1.RoutingNumber,
> 'Returned' as Status
> FROM
> (
> SELECT
> RoutingNumber,
> MAX(CASE WHEN status = 'Cleared' THEN count(some_unique_identifier) ELSE 0
> END) AS cleared_records,
> MAX(CASE WHEN status = 'Returned' THEN count(some_unique_identifier) ELSE 0
> END) AS returned_records,
> MAX(CASE WHEN status = 'Returned' THEN count(some_unique_identifier) ELSE 0
> END) - MAX(CASE WHEN status = 'Cleared' THEN count(some_unique_identifier)
> ELSE 0 END) as returned_vs_cleared
> FROM some_table
> GROUP BY RoutingNumber
> )sql1
> WHERE sql1.returned_vs_cleared > 0
>
> Regards,
> Roman
> "_Stephen" wrote:
> >
> > "Roman JR." <rkiss_at_opentext_dot_com> wrote in message
> > news:8692CF17-5343-4917-9238-0A156E7178C0@.microsoft.com...
> > >
> > > Here is how I set the background color as expression:
> > >
> > > =IIF(Fields!Todays_destination.Value <> Fields!Todays_source.Value, "Red",
> > > "White")
> >
> > I see that your comparing two separate columns in your data. Mine is
> > normalized, sample here:
> > NumRows RoutingNumber Status
> > 60 011000138 CLEARED
> > 36 011000138 RETURNED
> > 13 011301798 RETURNED
> > 13 011400071 CLEARED
> > 23 011400495 CLEARED
> > 12 011400495 RETURNED
> >
> > In this case I don't want to print the row in the Matrix for 011000138
> > because 36 < 60. I want to print the row for 011301798 because they are all
> > returned. I want to skip the other rows.
> >
> > Do you know how the data is held in the object so I can attempt to enumerate
> > between them?
> >
> > TIA
> >
> > __Stephen
> >
> >
> >
> >|||Roman thanks. I think your missing the ease of the Matrix report, where
your presenting normalized data to the report engine and it does it's layout
voodoo keeping totals to the grouping conditions that you set.
That being said, my display for the DATA section is probably an array '
because this is the display formula in the textbox:
=Sum(Fields!NumRows.Value)
So the engine iterates through a distinct call for the column Fields!status.
It sees 2 Cleared and Returned.
I am surprised that the intellisence isn't picking this up, that the data is
an array.
I have been doing cross tab reports for 10+ years and don't ever remember
having to limit the rows in the report like this. Normally I'd do it in the
data collection phase.
If you have any other ideas drop me a line.
"Roman JR." <rkiss_at_opentext_dot_com> wrote in message
news:4F9367C4-0971-4947-B488-4592F4E7756D@.microsoft.com...
> Or you can simply look for visibility under properties of the report,
> click
> the plus sign that will expand the VISIBILITY properties, select
> expression
> for HIDDEN and copy/paste the following expression:
> =Fields!status.Value="Cleared"
> You must apply this expression to every row in your report, this way only
> RETURNED will show up.
> Roman
>
> "Roman JR." wrote:
>> Hi Stephen,
>> Can't you just limit your resultset in your SQL data source?
>> I would create my report logic with the SQL statement.
>> (sample sql)
>> SELECT
>> sql1.returned_records,
>> sql1.RoutingNumber,
>> 'Returned' as Status
>> FROM
>> (
>> SELECT
>> RoutingNumber,
>> MAX(CASE WHEN status = 'Cleared' THEN count(some_unique_identifier) ELSE
>> 0
>> END) AS cleared_records,
>> MAX(CASE WHEN status = 'Returned' THEN count(some_unique_identifier) ELSE
>> 0
>> END) AS returned_records,
>> MAX(CASE WHEN status = 'Returned' THEN count(some_unique_identifier) ELSE
>> 0
>> END) - MAX(CASE WHEN status = 'Cleared' THEN
>> count(some_unique_identifier)
>> ELSE 0 END) as returned_vs_cleared
>> FROM some_table
>> GROUP BY RoutingNumber
>> )sql1
>> WHERE sql1.returned_vs_cleared > 0
>>
>> Regards,
>> Roman
>> "_Stephen" wrote:
>> >
>> > "Roman JR." <rkiss_at_opentext_dot_com> wrote in message
>> > news:8692CF17-5343-4917-9238-0A156E7178C0@.microsoft.com...
>> > >
>> > > Here is how I set the background color as expression:
>> > >
>> > > =IIF(Fields!Todays_destination.Value <> Fields!Todays_source.Value,
>> > > "Red",
>> > > "White")
>> >
>> > I see that your comparing two separate columns in your data. Mine is
>> > normalized, sample here:
>> > NumRows RoutingNumber Status
>> > 60 011000138 CLEARED
>> > 36 011000138 RETURNED
>> > 13 011301798 RETURNED
>> > 13 011400071 CLEARED
>> > 23 011400495 CLEARED
>> > 12 011400495 RETURNED
>> >
>> > In this case I don't want to print the row in the Matrix for 011000138
>> > because 36 < 60. I want to print the row for 011301798 because they
>> > are all
>> > returned. I want to skip the other rows.
>> >
>> > Do you know how the data is held in the object so I can attempt to
>> > enumerate
>> > between them?
>> >
>> > TIA
>> >
>> > __Stephen
>> >
>> >
>> >
>> >|||You were right Roman I had to get the data straight in a new SP. Then take
it to a non matrix report.
"Roman JR." <rkiss_at_opentext_dot_com> wrote in message
news:A7457C10-5776-400D-BF22-24777A9EB523@.microsoft.com...
> Hi Stephen,
> Can't you just limit your resultset in your SQL data source?
> I would create my report logic with the SQL statement.
> (sample sql)
> SELECT
> sql1.returned_records,
> sql1.RoutingNumber,
> 'Returned' as Status
> FROM
> (
> SELECT
> RoutingNumber,
> MAX(CASE WHEN status = 'Cleared' THEN count(some_unique_identifier) ELSE 0
> END) AS cleared_records,
> MAX(CASE WHEN status = 'Returned' THEN count(some_unique_identifier) ELSE
> 0
> END) AS returned_records,
> MAX(CASE WHEN status = 'Returned' THEN count(some_unique_identifier) ELSE
> 0
> END) - MAX(CASE WHEN status = 'Cleared' THEN
> count(some_unique_identifier)
> ELSE 0 END) as returned_vs_cleared
> FROM some_table
> GROUP BY RoutingNumber
> )sql1
> WHERE sql1.returned_vs_cleared > 0
>
> Regards,
> Roman
> "_Stephen" wrote:
>> "Roman JR." <rkiss_at_opentext_dot_com> wrote in message
>> news:8692CF17-5343-4917-9238-0A156E7178C0@.microsoft.com...
>> >
>> > Here is how I set the background color as expression:
>> >
>> > =IIF(Fields!Todays_destination.Value <> Fields!Todays_source.Value,
>> > "Red",
>> > "White")
>> I see that your comparing two separate columns in your data. Mine is
>> normalized, sample here:
>> NumRows RoutingNumber Status
>> 60 011000138 CLEARED
>> 36 011000138 RETURNED
>> 13 011301798 RETURNED
>> 13 011400071 CLEARED
>> 23 011400495 CLEARED
>> 12 011400495 RETURNED
>> In this case I don't want to print the row in the Matrix for 011000138
>> because 36 < 60. I want to print the row for 011301798 because they are
>> all
>> returned. I want to skip the other rows.
>> Do you know how the data is held in the object so I can attempt to
>> enumerate
>> between them?
>> TIA
>> __Stephen
>>
>>|||Hi Stephen,
I am sorry for the delayed response.
Well, it is hard to give the exact solution to a problem without knowing
other report specifications/requirements.
I always try to control my report with SQL statement. I like to think that I
am pretty good in SQL, therefore any complex report requests are ususally
solved within my SQL statement and then easily brought over to Visual Studio
for data output manipulation (conditional expressions, hidden parameters,
etc..)
I am glad that I could help at least a little bit.
Regards,
Roman Kiss Jr.
"_Stephen" wrote:
> You were right Roman I had to get the data straight in a new SP. Then take
> it to a non matrix report.
>
> "Roman JR." <rkiss_at_opentext_dot_com> wrote in message
> news:A7457C10-5776-400D-BF22-24777A9EB523@.microsoft.com...
> > Hi Stephen,
> >
> > Can't you just limit your resultset in your SQL data source?
> >
> > I would create my report logic with the SQL statement.
> > (sample sql)
> >
> > SELECT
> > sql1.returned_records,
> > sql1.RoutingNumber,
> > 'Returned' as Status
> > FROM
> > (
> > SELECT
> > RoutingNumber,
> > MAX(CASE WHEN status = 'Cleared' THEN count(some_unique_identifier) ELSE 0
> > END) AS cleared_records,
> > MAX(CASE WHEN status = 'Returned' THEN count(some_unique_identifier) ELSE
> > 0
> > END) AS returned_records,
> > MAX(CASE WHEN status = 'Returned' THEN count(some_unique_identifier) ELSE
> > 0
> > END) - MAX(CASE WHEN status = 'Cleared' THEN
> > count(some_unique_identifier)
> > ELSE 0 END) as returned_vs_cleared
> > FROM some_table
> > GROUP BY RoutingNumber
> > )sql1
> > WHERE sql1.returned_vs_cleared > 0
> >
> >
> > Regards,
> >
> > Roman
> >
> > "_Stephen" wrote:
> >
> >>
> >> "Roman JR." <rkiss_at_opentext_dot_com> wrote in message
> >> news:8692CF17-5343-4917-9238-0A156E7178C0@.microsoft.com...
> >> >
> >> > Here is how I set the background color as expression:
> >> >
> >> > =IIF(Fields!Todays_destination.Value <> Fields!Todays_source.Value,
> >> > "Red",
> >> > "White")
> >>
> >> I see that your comparing two separate columns in your data. Mine is
> >> normalized, sample here:
> >> NumRows RoutingNumber Status
> >> 60 011000138 CLEARED
> >> 36 011000138 RETURNED
> >> 13 011301798 RETURNED
> >> 13 011400071 CLEARED
> >> 23 011400495 CLEARED
> >> 12 011400495 RETURNED
> >>
> >> In this case I don't want to print the row in the Matrix for 011000138
> >> because 36 < 60. I want to print the row for 011301798 because they are
> >> all
> >> returned. I want to skip the other rows.
> >>
> >> Do you know how the data is held in the object so I can attempt to
> >> enumerate
> >> between them?
> >>
> >> TIA
> >>
> >> __Stephen
> >>
> >>
> >>
> >>
>
>