IIf function and inserting Null values in false part

[WillTRiker]

I have a calculated field (Bhours) with the following IIf function:

=IIf(Fields!BDate.Value<=Parameters!Pdate.Value and Fields!EDate.Value>Parameters!Pdate.Value,Fields!Hours.Value,0)

It works perfectly, but I don't like the fact that it has a zero for the false part as I like the field to the blanc instead of filled with a zero.

I can't use a space as I need to calculate with this field later on. I tried using "", but then the field displays #error". Any ideas how to accomplish this?

 

[redalert]

Use Null instead of 0 and you will be able to use this value as if it is a zero.

Do not put the Null in quotes.

 

[WillTRiker]

No joy.

Initially I get the message that Null is no longer supported and that I should use System.DBNull instead, but that gets the error message that it can't be used in an expression :banghead:

 

[pr2-eugin]

Could you show your complete SQL Query?

 

[WillTRiker]

Sorry, I'm very new to this, but you want this?

emas.microsoft.com/sqlserver/2004/10/semanticmodeling" xmlns:xsd="" xmlns:xsi="" xmlns:qd="" xmlns:rb=""> (((links removed)))
<Hierarchies>
<Hierarchy>
<BaseEntity>
<!--Meetwaarden-->
<EntityID>Gcd201a49-d0ec-4ae8-822c-9fe9ec9c5b52</EntityID>
</BaseEntity>
<Groupings>
<Grouping Name="ID Org Eenheid">
<Expression Name="ID Org Eenheid">
<Path>
<RolePathItem>
<!--Afdeling vd Medewerker-->
<RoleID>Gb6cbf131-fd5a-4cc9-8d8f-6c73246ee17a</RoleID>
</RolePathItem>
</Path>
<AttributeRef>
<!--ID Org Eenheid-->
<AttributeID>Gfc326115-dd0d-448c-90db-f199de69f21a</AttributeID>
</AttributeRef>
</Expression>
</Grouping>
<Grouping Name="Kr Naam Org Eenhd">
<Expression Name="Kr Naam Org Eenhd">
<Path>
<RolePathItem>
<!--Afdeling vd Medewerker-->
<RoleID>Gb6cbf131-fd5a-4cc9-8d8f-6c73246ee17a</RoleID>
</RolePathItem>
</Path>
<AttributeRef>
<!--Kr Naam Org Eenhd-->
<AttributeID>Gb04f86f3-00fe-4ce0-a5c6-da5f33ca1c6f</AttributeID>
</AttributeRef>
</Expression>
</Grouping>
<Grouping Name="Persnr">
<Expression Name="Persnr">
<Path>
<RolePathItem>
<!--Medewerker-->
<RoleID>G177a5c27-b502-4cc8-9f27-b90f1984ce70</RoleID>
</RolePathItem>
</Path>
<AttributeRef>
<!--Personeelsnummer-->
<AttributeID>G4e85f9a7-0d82-4d2e-84b0-93d096ffd3ca</AttributeID>
</AttributeRef>
</Expression>
</Grouping>
<Grouping Name="Volledige Naam">
<Expression Name="Volledige Naam">
<Path>
<RolePathItem>
<!--Medewerker-->
<RoleID>G177a5c27-b502-4cc8-9f27-b90f1984ce70</RoleID>
</RolePathItem>
</Path>
<AttributeRef>
<!--Volledige Naam-->
<AttributeID>G6f8a430a-a6c3-4a02-881a-0d1eb0dc86bd</AttributeID>
</AttributeRef>
</Expression>
</Grouping>
<Grouping Name="BDat-aanst">
<Expression Name="BDat-aanst">
<Path>
<RolePathItem>
<!--Aanstelling medewerker-->
<RoleID>G2150e8b5-94cc-4bb6-809a-b24c3a1897ce</RoleID>
</RolePathItem>
</Path>
<AttributeRef>
<!--Begindatum-->
<AttributeID>Ge3ea28a9-a6d8-4ed0-a315-ff18d1799a54</AttributeID>
</AttributeRef>
</Expression>
</Grouping>
<Grouping Name="EDat-aanst">
<Expression Name="EDat-aanst">
<Path>
<RolePathItem>
<!--Aanstelling medewerker-->
<RoleID>G2150e8b5-94cc-4bb6-809a-b24c3a1897ce</RoleID>
</RolePathItem>
</Path>
<AttributeRef>
<!--Einddatum-->
<AttributeID>Gba624db2-40c7-4e2c-8604-640953035755</AttributeID>
</AttributeRef>
</Expression>
</Grouping>
<Grouping Name="Werktijdfactor Week">
<Expression Name="Werktijdfactor Week">
<Path>
<RolePathItem>
<!--Aanstelling medewerker-->
<RoleID>G2150e8b5-94cc-4bb6-809a-b24c3a1897ce</RoleID>
</RolePathItem>
</Path>
<AttributeRef>
<!--Werktijdfactor Week-->
<AttributeID>Gee81a48e-13d2-4a36-85f5-9d45229afbbe</AttributeID>
</AttributeRef>
</Expression>
</Grouping>
<Grouping Name="BDat_deelb">
<Expression Name="BDat_deelb">
<Path>
<RolePathItem>
<!--Dienstverband Periode-->
<RoleID>Gd6c9b088-7c8a-4062-a0b2-c0f34b03ef65</RoleID>
</RolePathItem>
</Path>
<AttributeRef>
<!--Ingangsdatum-->
<AttributeID>G51161676-3b0b-4d73-b427-bc09ce507793</AttributeID>
</AttributeRef>
</Expression>
</Grouping>
<Grouping Name="EDat_deelb">
<Expression Name="EDat_deelb">
<Path>
<RolePathItem>
<!--Dienstverband Periode-->
<RoleID>Gd6c9b088-7c8a-4062-a0b2-c0f34b03ef65</RoleID>
</RolePathItem>
</Path>
<AttributeRef>
<!--Einddatum-->
<AttributeID>G2b8dd4ac-6be0-4d82-aab3-59f6c4583e66</AttributeID>
</AttributeRef>
</Expression>
</Grouping>
<Grouping Name="% Deelbetrekking">
<Expression Name="% Deelbetrekking">
<Path>
<RolePathItem>
<!--Dienstverband Periode-->
<RoleID>Gd6c9b088-7c8a-4062-a0b2-c0f34b03ef65</RoleID>
</RolePathItem>
</Path>
<AttributeRef>
<!--% Deelbetrekking-->
<AttributeID>Gcfb2d111-d8b4-49f6-90fc-b106e3a291a0</AttributeID>
</AttributeRef>
</Expression>
</Grouping>
<Grouping Name="BDat_BAPO">
<Expression Name="BDat_BAPO">
<Path>
<RolePathItem>
<!--Bapo-->
<RoleID>Ga831c304-539a-4a35-b466-5f1c3ba6c007</RoleID>
</RolePathItem>
</Path>
<AttributeRef>
<!--Begindatum-->
<AttributeID>G06fbe9bb-f16d-4e0c-b8ad-9524ef7c1418</AttributeID>
</AttributeRef>
</Expression>
</Grouping>
<Grouping Name="EDat_BAPO">
<Expression Name="EDat_BAPO">
<Path>
<RolePathItem>
<!--Bapo-->
<RoleID>Ga831c304-539a-4a35-b466-5f1c3ba6c007</RoleID>
</RolePathItem>
</Path>
<AttributeRef>
<!--Einddatum-->
<AttributeID>Ga9611879-ec2e-478d-9b8b-d736edd65264</AttributeID>
</AttributeRef>
</Expression>
</Grouping>
<Grouping Name="BAPO WTF">
<Expression Name="BAPO WTF">
<Path>
<RolePathItem>
<!--Bapo-->
<RoleID>Ga831c304-539a-4a35-b466-5f1c3ba6c007</RoleID>
</RolePathItem>
</Path>
<AttributeRef>
<!--BAPO WTF-->
<AttributeID>G916dacb0-c491-425b-b2a9-6713e631d1d8</AttributeID>
</AttributeRef>
</Expression>
</Grouping>
</Groupings>
<Filter>
<Expression Name="filter">
<Literal>
<DataType>Boolean</DataType>
<Value>true</Value>
</Literal>
<CustomProperties>
<CustomProperty Name="qd:Filter" />
<CustomProperty Name="qd:ContextEntityID">
<Value xsi:type="xsd:string">Gcd201a49-d0ec-4ae8-822c-9fe9ec9c5b52</Value>
</CustomProperty>
<CustomProperty Name="qd:AutoChangeBaseEntity" />
<CustomProperty Name="qdesign">
<Value xsi:type="xsd:string">expr1</Value>
</CustomProperty>
</CustomProperties>
</Expression>
</Filter>
</Hierarchy>
</Hierarchies>
</SemanticQuery>

 

[WillTRiker]

Sorry, I'm very new to this, but you want this?

Edited: posted xml.

 

[pr2-eugin]

I would like the SQL Query..

 

[WillTRiker]

Damn, it looks like we get a scaled down version so I don't have the option of an SQL view. I'll try and get it from our supplier .... might take a while if I get it at all.