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
> >>
> >>
> >>
> >>
>
>
