Help with Report Filter on bit field

I have this filter for my report table:

expression operator Value

=Cstr(Fields!work.Value) = 'True'

My report's table isn't returning data, but in preview but if I run the dataset, there is clearly some valid records tha contain 'true' for the field Work. The field work in my SQL Server table is type bit

additional screenshot here: http:\\www.webfound.net\no_data_filter_on_bit_field.jpg

I would try the following filter:

Expression:
=CBool(Fields!work.Value)

Operator:
=

Value:
=True

Note that the filter value is an expression (=True).

-- Robert

|||

Robert, thanks very much, it works. Now let me ask you this. I had a total textbox that just did a COUNT(number). But I need to do a COUNT on number if CBool(Fields!work.Value) = True. I was wondering how to form an if statement behind my text field to do this. number is just the identity field in which I can count on.

|||

I tried this but it's malformed:

=IIf(CBool(First(Fields!home.Value, "Mismatch_Data")) == True, COUNT(Fields!number.Value, "Mismatch_Data"), 0)

|||

Do you really want to make the decision for the count based on the first data row value of the "home" field? If yes, then this expression should work (note - since RDL expressions are VB.NET based the comparison only needs one '='; in this particular case you can also omit it):

=IIf(CBool(First(Fields!home.Value, "Mismatch_Data")), COUNT(Fields!number.Value, "Mismatch_Data"), 0)

Also, are you really looking for the Count or for a Sum aggregate?

If you want to sum individual rows based the value of the "home" field in that particular row (rather than just looking at the first row), you would use conditional aggregation and the following expression would need to be put e.g. into a table header/footer bound to the Mismatch_Data dataset):

=Sum( iif(CBool(Fields!home.Value), Fields!number.Value, 0)

-- Robert

|||

Thanks, Robert. I was looking for the count of how many records were found. I have 2 tables....so I needed a count of records using the number field which was a unique field.

That should work...

help with percision? if you enter a number in the trillions such 9,999,999,999,999 .net or sql m

can you please explian this chart:

The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. The precision and scale for any expression that is not decimal is the precision and scale defined for the data type of the expression.

Operation

Result precision

Result scale *

e1 + e2

max(s1, s2) + max(p1-s1, p2-s2) + 1

max(s1, s2)

e1 - e2

max(s1, s2) + max(p1-s1, p2-s2) + 1

max(s1, s2)

e1 * e2

p1 + p2 + 1

s1 + s2

e1 / e2

p1 - s1 + s2 + max(6, s1 + p2 + 1)

max(6, s1 + p2 + 1)

e1 { UNION | EXCEPT | INTERSECT } e2

max(s1, s2) + max(p1-s1, p2-s2)

max(s1, s2)

* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

e1 = numeric(20,8)

e2 = numeric(20,8)

e1/e2

is this correct

max(6,s1 + p2 + 1)

8 + 20 + 1= 29 since under 38 use 29 scale

scale would be max(6,29) = 29 correct?

p1 - s1 + s2 + max(6, s1 + p2 + 1)

20 - 8 + 8 + 29 = 49 does that mean it truncate the least sugificant digits by 29 - 11 = 18 so the effective result should be numeric(38,18) or ##,###,###,###,###,###,###.000000000000000000 this does not seem to be what you get can some explain also we have seen that if you enter a number in the trillions such 9,999,999,999,999 neither .net or sql management studio cannot display the value?

I think you have it, though it turns out that the actual returned type is numeric(38,17). You can see this using a variant:

declare @.c sql_variant

declare @.a numeric(20,8) --set the datatypes here
declare @.b decimal(20,8) --set the datatypes here

set @.a = 1 --set a value here
set @.b = 1 --set a value here

select @.c = @.a / @.b --do the math
select cast(@.c as varchar(40)),
cast(sql_variant_property(@.c,'BaseType') as varchar(20)) + '(' +
cast(sql_variant_property(@.c,'Precision') as varchar(10)) + ',' +
cast(sql_variant_property(@.c,'Scale') as varchar(10)) + ')'

- -
1.000000000000000000 numeric(38,18)

Don't quite understand your issue with trillions (though you have to use 21,8 instead of 20, 8 for the datatype:

declare @.c sql_variant

declare @.a numeric(21,8) --set the datatypes here

set @.a = 9999999999999 --set a value here

select @.c = @.a
select cast(@.c as varchar(40)),
cast(sql_variant_property(@.c,'BaseType') as varchar(20)) + '(' +
cast(sql_variant_property(@.c,'Precision') as varchar(10)) + ',' +
cast(sql_variant_property(@.c,'Scale') as varchar(10)) + ')'

- -
9999999999999.00000000 numeric(21,8)

help with percision? if you enter a number in the trillions such 9,999,999,999,999 .net or s

can you please explian this chart:

The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. The precision and scale for any expression that is not decimal is the precision and scale defined for the data type of the expression.

Operation

Result precision

Result scale *

e1 + e2

max(s1, s2) + max(p1-s1, p2-s2) + 1

max(s1, s2)

e1 - e2

max(s1, s2) + max(p1-s1, p2-s2) + 1

max(s1, s2)

e1 * e2

p1 + p2 + 1

s1 + s2

e1 / e2

p1 - s1 + s2 + max(6, s1 + p2 + 1)

max(6, s1 + p2 + 1)

e1 { UNION | EXCEPT | INTERSECT } e2

max(s1, s2) + max(p1-s1, p2-s2)

max(s1, s2)

* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

e1 = numeric(20,8)

e2 = numeric(20,8)

e1/e2

is this correct

max(6,s1 + p2 + 1)

8 + 20 + 1= 29 since under 38 use 29 scale

scale would be max(6,29) = 29 correct?

p1 - s1 + s2 + max(6, s1 + p2 + 1)

20 - 8 + 8 + 29 = 49 does that mean it truncate the least sugificant digits by 29 - 11 = 18 so the effective result should be numeric(38,18) or ##,###,###,###,###,###,###.000000000000000000 this does not seem to be what you get can some explain also we have seen that if you enter a number in the trillions such 9,999,999,999,999 neither .net or sql management studio cannot display the value?

I think you have it, though it turns out that the actual returned type is numeric(38,17). You can see this using a variant:

declare @.c sql_variant

declare @.a numeric(20,8) --set the datatypes here
declare @.b decimal(20,8) --set the datatypes here

set @.a = 1 --set a value here
set @.b = 1 --set a value here

select @.c = @.a / @.b --do the math
select cast(@.c as varchar(40)),
cast(sql_variant_property(@.c,'BaseType') as varchar(20)) + '(' +
cast(sql_variant_property(@.c,'Precision') as varchar(10)) + ',' +
cast(sql_variant_property(@.c,'Scale') as varchar(10)) + ')'

- -
1.000000000000000000 numeric(38,18)

Don't quite understand your issue with trillions (though you have to use 21,8 instead of 20, 8 for the datatype:

declare @.c sql_variant

declare @.a numeric(21,8) --set the datatypes here

set @.a = 9999999999999 --set a value here

select @.c = @.a
select cast(@.c as varchar(40)),
cast(sql_variant_property(@.c,'BaseType') as varchar(20)) + '(' +
cast(sql_variant_property(@.c,'Precision') as varchar(10)) + ',' +
cast(sql_variant_property(@.c,'Scale') as varchar(10)) + ')'

- -
9999999999999.00000000 numeric(21,8)

Help with nonempty crossjoin....

Hello...

i have made a calculated measure named [Location Count] that has this expression:

NONEMPTYCROSSJOIN(descendants([dimTargetSet].currentmember, 9, LEAVES),descendants([dimLocation].currentmember,9,LEAVES)).count

In AS it seems to be working but when I call the measure in my query in reporting services it always returns zero. what am i doing wrong here?...

WITH SET [Locations] AS '{[dimlocation].&[2583]}' MEMBER [Measures].[Section Count] AS '[dimQuestionSection].&[323].children.count' MEMBER [dimSurveyQuestion].[Audits] AS 'Aggregate ({[dimSurveyQuestion].&[323]})' SELECT {[Measures].[Location Count]} ON COLUMNS, {FILTER(CROSSJOIN(DESCENDANTS([Locations],1,LEAVES), DESCENDANTS([dimQuestionSection].&[323],1)), [Measures].[Last Audited Date]>0)} ON ROWS FROM cubeAuditTargetSet WHERE ([dimSurveyQuestion].[Audits], [dimDate].&[2007], [dimUser].&[323], [dimTargetSet].&[323].&[9])

Could be because you're using a calculated member: [dimSurveyQuestion].[Audits] in the where clause. Does it work if you replace it with the actual member?|||

thanks for your reply... actually i need to use aggregate with [dimSurveyQuestion].[Audits] because there are instance we search for three members of the dimension...

MEMBER [dimSurveyQuestion].[Audits] AS 'Aggregate ({[dimSurveyQuestion].&[323].&[1], dimSurveyQuestion].&[323].&[2], dimSurveyQuestion].&[323].&[3]})'

any idea?... thanks...

Help with nonempty crossjoin....

Hello...

i have made a calculated measure named [Location Count] that has this expression:

NONEMPTYCROSSJOIN(descendants([dimTargetSet].currentmember, 9, LEAVES),descendants([dimLocation].currentmember,9,LEAVES)).count

In AS it seems to be working but when I call the measure in my query in reporting services it always returns zero. what am i doing wrong here?...

WITH SET [Locations] AS '{[dimlocation].&[2583]}' MEMBER [Measures].[Section Count] AS '[dimQuestionSection].&[323].children.count' MEMBER [dimSurveyQuestion].[Audits] AS 'Aggregate ({[dimSurveyQuestion].&[323]})' SELECT {[Measures].[Location Count]} ON COLUMNS, {FILTER(CROSSJOIN(DESCENDANTS([Locations],1,LEAVES), DESCENDANTS([dimQuestionSection].&[323],1)), [Measures].[Last Audited Date]>0)} ON ROWS FROM cubeAuditTargetSet WHERE ([dimSurveyQuestion].[Audits], [dimDate].&[2007], [dimUser].&[323], [dimTargetSet].&[323].&[9])

Could be because you're using a calculated member: [dimSurveyQuestion].[Audits] in the where clause. Does it work if you replace it with the actual member?|||

thanks for your reply... actually i need to use aggregate with [dimSurveyQuestion].[Audits] because there are instance we search for three members of the dimension...

MEMBER [dimSurveyQuestion].[Audits] AS 'Aggregate ({[dimSurveyQuestion].&[323].&[1], dimSurveyQuestion].&[323].&[2], dimSurveyQuestion].&[323].&[3]})'

any idea?... thanks...

help with Interactive Sort on a column...

I have been selecting the column name textbox at the top of a column
with an int value in sql server.
When I go to Interactive Sort/Sort Expression, I just put the same
expression as the one below the column name in the details "=Fields!
qtySold.Value".
The results is not what I was hoping for:
981
90
9
876
800
8
777
76
7
Instead of:
981
876
800
777
90
76
9
8
7
Any help is appreciated.
Thanks,
Trinttrint wrote:
> I have been selecting the column name textbox at the top of a column
> with an int value in sql server.
> When I go to Interactive Sort/Sort Expression, I just put the same
> expression as the one below the column name in the details "=Fields!
> qtySold.Value".
> The results is not what I was hoping for:
> 981
> 90
> 9
> 876
> 800
> 8
> 777
> 76
> 7
> Instead of:
> 981
> 876
> 800
> 777
> 90
> 76
> 9
> 8
> 7
> Any help is appreciated.
> Thanks,
> Trint
Just a guess, but it looks like it's sorting it as if it's text and not
a number. Hope I'm not pointing out the obvious. Have you tried
converting the value that you're sorting on to an integer explicitly?
James
--