Unable to Pull Data from Table Elements Containing Null Values 3

[cohibaman]

I have a problem running a report in MicroStrategy version 7.2.1. The problem occurs when two separate tables are linked on common elements(ie. tblx_fld1, tblx_fld2 and tbly_fld1, tbly_fld2.)and the elements contains null values. When the match elements contains a null value, I cannot pull any data from the tables. However, when I replace the value with anything but nulls, the report pulls all the data. Changing the value back to nulls recreates the problem. How can I work around this issue with physically changing the tables values?

Thanks

 

[FLB]

Some databases say that null <> null
Some other databases say null = null
This seems like a philosophycal question... and I won't answer it! ;-)

To answer your question: as we do not exactly know in what context the null values affect the data coming back from the database, we can not be of much help (I think). [If this a join issue as describe in the first line?]

In theory you should not have any null value in your tables (drilling does not work if null values are present on the report template because they are considered empty string instead ''). In a good datawarehouse schema, we should remove any null (specially if we need to join two tables together....

FLB.

 

[NaughtyVicar]

Check out Tech Note: TN4000-007-0323 &quot;How to retrieve data when null values prevent joins from occuring in MicroStrategy Agent 7.0&quot;.

I know the tech note sounds dated but the theory sounds viable.

 

[cohibaman]

I've check the Microstrategy Knowledge base and did review tech note TN4000-7X0-0323, as well as others. However my problem does not involve lookup tables.

Thanks

 

[nlim]

it would probably be helpful to post the sql generated, as well as your NullChecking option.

 

[omarmtz]

if you want to join two tables that have in the id's null values...

select * from table1 join
table 2 on (table1.id = table2.id)

it doesn-t work, but if you use the following, it can work...

select * from table1 join
table 2 on (isnull(table1.id, 0) = isnull(table2.id, 0))

or use another value (not 0) that never id will have.

Traduce this in MicroStrategy, go to the Attribute editor, in the id form, and replace id, and use ApplySimple('isnull(#0, 0)', id)

I hope this helps...!

 

[cohibaman]

I've tried the &quot;ApplySimple&quot; function, but it was to no avail. As suggested, below is an example of the extracted data generated by the sql:

table:ZZSP00
UND_GROUP_CD PRD_LN_ID WJXBFS1
USPP 1304 57727
USPT 1310 536
1100 62767.45
table:ZZSP01
UND_GROUP_CD PRD_LN_ID WJXBFS1
USPP 1304 -1000
USPT 1310 -19428.81
1100 356312.63

Below is a static translation table:

table: P_PRD_LN_DIM
PRD_LN_ID PRD_DESC
1100 OCEAN MARINE
1304 HPR
1310 COMM PROP

Below is the last sql statement generated by Microstrategy:

select distinct pa1.UND_GROUP_CD UND_GROUP_CD,
pa1.PRD_LN_ID PRD_LN_ID,
a11.PRD_DESC PRD_DESC,
pa1.WJXBFS1 WJXBFS1,
pa2.WJXBFS1 WJXBFS2
from ZZSP00 pa1,
ZZSP01 pa2,
P_PRD_LN_DIM a11
where pa1.PRD_LN_ID = pa2.PRD_LN_ID and
pa1.UND_GROUP_CD = pa2.UND_GROUP_CD and
pa1.PRD_LN_ID = a11.PRD_LN_ID

As noted earlier, I can only pull the UND_GROUP_CD values &quot;USPP&quot; and &quot;USPT&quot;.

Thanks for ya'll help thus far.

 

[jonhoward]

earlier you said your query doesnt involve lookup tables - so what is P_PRD_LN_DIM ? Sounds like a lookup to me...

try changing the Report Data Options, Calculations, Attribute Join Type and select the &quot;Preserve lookup table elements joined to final pass result table based on template attributes w/ filter&quot;.

and while you're in Report Data Options, go to Display, Null Values and, for your own piece of mind, put something like WHNULL and XTABNULL in the appropriate boxes

finally, you may want Outer Joins set for the Attribute Join Type and Metric Join Type, but I think if you do the Preserve Lookup... you should be OK

 

[nlim]

cohibaman, 2 more clarifications, and we can close this post.

1) your SQL extract looks like part of a multi-pass SQL, is this so? If yes, you're going to have to post the original problem. ie. what is your physical data schema?

2) What is the report you would like to generate? It looks like
CD ID DESC WJXBFS1 WJXBFS21
-- -- ---- ------- --------
USPP 1304 HPR
USPT 1310 COMMPROP
null 1100 OCEANMARINE

Now you can try Ormantz's suggestion. But instead of using applysimple, in the &quot;UND Group Code&quot; attribute id form, type NulltoZero(UND_GROUP_CD). This will convert all null values in the UND_GROUP_CD column to zeros.

Update your schema and try the report again.

 

[cohibaman]

Thanks MLIM and others for ya'lls help. I'm going thru the &quot;fast track&quot; learning curve with Microstrategy. I'll gather the info and post.

 

[cohibaman]

It worked!!!! The resolution was a combination of all of ya'lls suggestions and comments. One main problem was the &quot;UND Group Code&quot; attribute id was not defined correctly, hence the reason for the &quot;NulltoZero&quot; and &quot;ApplySimple&quot; condition not producing the desired results. If it were not for ya'lls help, I'd still be banging my head against the wall.

Thanks again,
Rick