How to display NULL records?

[samratmuddasani]

Hi Experts,

I'm creating a dynamic prompt in Business View Manager. Here I have two columns as below

Column A	Column B
1	A
1	B
1	NULL
2	NULL
2	NULL
2	NULL

Now when I'm entering the value 1 then it should bring A & B as a selection. And when I enter 2, as the column B has all the NULL values It should custom value as "No Records found".

I tried the below formula for Column B

if isnull{column B} then

"No Records Found"

else

{column B}

But when using the above formula,  when we enter 1 then its bringing A,B, No Records found.

Could you please help me with the logic in solving this. Please let me know if you have any questions.

Thanks a lot in advance,

Samrat Muddasani

[Former Member]

Hi Samrat,

try this formula..

IFNot(IsNull(Column A)) And Column A='1'

then

Column B

Else IFNot(IsNull(Column A)) And Column A ='2'

then

Column B

Else

"No Records Found"

Thanks.

Ashwini

[samratmuddasani]

Ashwin,

Its just and example. Column values cannot be hardcoded. It may vary...

Thanks for your efforts,

Samrat Muddasani

[samratmuddasani]

HI Experts,

I'm still looking for solution. Can anyone help me??

Thanks,

Samrat Muddasani

[abhilash_kumar]

Hi Samrat,

I haven't had a chance to replicate this in BVM yet. I'll get back to you soon.

-Abhilash

[Former Member]

Hi Samrat,

1. I've created an Excel file (xls) and using as datasource in SAP Crystal Reports 2013 SP5 Patch6

A      |      B
-------------------

1              A

1              B

1              NULL

2              NULL

2              NULL

2              NULL

2. Create an isnull formula:

If isnull({Sheet1_.B}) Then 'This is NULL' Else {Sheet1_.B}

3. Check the Report Options and uncheck the NULL Values to Default

Please see the attached screnshoots about it.

I hope the above is useful to you

Regards,
István

[samratmuddasani]

Hi Istvan,

Thanks for your response. But I'm trying to create a dynamic cascading prompt in Business View Manager not in crystal reports.

When I select A it should just bring 1, 2 in the next drop down and when you select B It should say "NO RECS FOUND"(Custom text).

Hope you understand my question. Let me know if you need more details.

Thanks,

Samrat Muddasani

[samratmuddasani]

Sure.. Will be waiting.

[Former Member]

Hi Samrat,

Yes it is.

Can you answer the following question or show me the exact steps about "How to created dynamic cascading prompt in Business View Manager?"

Allow me that suggest to you an official document about the Business Views:

https://help.sap.com/businessobject/product_guides/boexir31/en/xi3-1_bv_admin_en.pdf


Using Business Views:

Chapter 9 Dynamic Prompts and Cascading Lists of Values 215 page

I've created a sample Business View and inserted the same formula.

If isnull ({Sheet1_.B}) Then 'This is NULL' Else ({Sheet1_.B})

Please see my new attachment.

I hope the above is useful to you

Regards,

István

[abhilash_kumar]

Hi Samrat,

Please see if this works:

1) In the Data Foundation, create a SQL Expression with this code:

(

SELECT Min("Table"."ColumnB") From "Table" T

Where "Table"."ColumnA" = T."ColumnA"

)

Notice how the alias for the table has been specified

2) Create a formula in the Data Foundation with this code:

IF {%SQL Expression} = '' then

    'No Records found'

else {ColumnB}

Select 'Default Values for Nulls' from the drop-down on the top

3) Save the Data Foundation and insert this formula field to the Business Element

4) Use the formula field from the Business Element for the second level of the dynamic prompt.

Let me know how this goes.

-Abhilash

[samratmuddasani]

Istvan,

Thanks again for working on this.

What I'm trying to say is, when the Dynamic prompt is integrated in the Crystal Reports it show prompt like below picture.

Thanks for working on my request.

Samrat Muddasani

[Former Member]

Hi Samrat,

Can you please check the original values in data source please?

Please trying to refresh it.

What's the result?

Regards,
István

[samratmuddasani]

Hi Abhilash,

I don't know why but the CR Tool is getting force closed when I'm trying to run the report. I think the query is taking long time to run or don't know why but its not working. Do you have any other Idea?

Thanks,

Samrat Muddasani

[abhilash_kumar]

Did you try scheduling the LOVs?

I'm not sure if there is another way to do this.

-Abhilash

[samratmuddasani]

Hmm yes but no luck...

Anyways thanks for your efforts. Please let me know if ever you get a chance to work on it.

Thanks,

Samrat Muddasani

[abhilash_kumar]

Hi Samrat,

Isn't the logic working as expected?

There is a NULL value in Column B for '1'.

-Abhilash

[samratmuddasani]

Hi Abhilash,

No, when there are other values in Column B apart from NULL it should not display "No Records found" for NULL in this case.

For Example:

When selected 1, then it should show only A and B but not "No Records Found" along with them.

A

B

"No Records Found"

When selected 2, then it should show only "No Records Found".