How to display message if no rows returned by query.

[hem852]

Hi,

I have a multiple query report which displays 8 different lists based on 8 seperate queries.

What I would like to do is, where any one of those queries returns no rows, display a message which states 'There are no results of this type'

I tried creating a query calculation in each query and making this a section. The formula for this query calculation was:

Code Select Expand


IF(COUNT(rows)=0)THEN('No Exceptions of this type to report')ELSE(CAST(COUNT(rows) AS CHAR)+' Exceptions')

This works fine when there are rows returned, it gives a nice header with a row count (handy if there are a high number), however when there are no rows returned by the query nothing is displayed at all.

Is there a way of achieving what I need? Any help/advice would be much appreciated. Sorry if this is a dumb question; I'm new to Report Studio having recently moved from BO and am finding to switch very hard going.

Regards
John

[Rajaggopal]

instead of creating a query calculation and sectioning it, try to do the following.
1. Create boolean variables for each report and have [Query].[DATA ITEM] > 0, where [DATA ITEM] is your report's row count.
2. Have a text item just before each report with the text "No Rows in report" or whatever that you wish to show.
3. Choose the Render variable for the report and the text item as the corresponding boolean variable.
4. Choose only "Yes" for the list and only "No" for the text.

Now when the report has no data, the row count will be 0 so the boolean variable will be "No" so the list will not be shown and the text item will be shown instead.

i still dint try this out practically... will work it out when i can and confirm if it will work...

Please lemme know if you encounter any problems with this solution.


EDIT: this doesnt work... :( sorry

Your list reports are placed each on one page or all are placed on the same page?

[hem852]

Ok, I guess I'm doing something wrong here because I cant get this working :(

Sorry, I'm very new to this so am battling with a lack of product knowledge!

Right this is what I have done so far...

Created a data item for each query called 'No Rows' with the expression of of 'COUNT(rows)'

Created a Boolean Variable called 'Exceptions' with an expression of 'No Rows > 0'

Added a text item before the List report.

Selected the Render Variable for the text item as the boolean variable created.....and thats where I hit problems......

In the Render Variable dialog box under the 'Render for' section only 'YES' appears and I need to be able to set this for 'NO'. Any ideas what I am doing wrong?

Thanks for all the help, it really is appreciated.

[dumbell]

If you are using 8.3 version, there is a propetery called "No data". Select "Yes" to that and it opens the door to enter a message for no data.

Hope this helps.

dumbell

[Rajaggopal]

Thats a very useful feature in 8.3, but are there any solutions for 8.2?

[hem852]

Sadly we are using 8.2 :(

[Stejawh]

Create a footer for the list with the custom message. Make it conditional based upon the number of rows returned. If the number of rows returned is not greater than 0, show the message.
Steps -

Create a list report and select the list using the select ancestor tool in the properties panel.
Create a List Footer and select the text item in the List Footer. Modify it to the custom message you want, ie: No Records Were Returned.
Create a new boolean variable. Using the built in function RowNumber(), create the expression: RowNumber()>0
Select the List Footer in the list in your report and set the Conditional Style in the properties panel to use the variable you just created.
Using the Conditional Explorer select the "Yes" value from your variable.
Set the "Box Type" property for the List Footer to "None".
Change the "Border" property to be style set to "None" on all sides and apply
Turn off the Conditional Explorer and use a prompt or filter to test your report.

The conditional custom message works properly unless you have a summary row in the list. If you have a summary row, and you are returning rows in the list, the conditional custom message still shows up. This is not correct.

To have this work always, add a calculation object into the query that will count the rows: Count(rows for report)
Then base your conditional variable off of this data item: [Count] >0. This will work even if you have summary rows in your list.

If this doesn't work ask Dave H, he'll sort it ;o)

[hem852]

That works perfectly, thank you Stejawh!

;D

[Stejawh]

Not a problem John.  Ask Dave, Kieran and the boys how they're doing for me.

Cheers.

[Rajaggopal]

I tried that too but i tried it with List Header instead of footer... also i used a calculated query like you said, but it still dint work for me... Maybe i did some mistake while doing the boolean variable part... :) will try it out again now and see if it works

EDIT: it dint work for me :( This is what i did...

1. Create 2 list reports on the same page and put them inside a table.
2. Selected the lists and created List Header in one list and List Footer in another.
3. Created calculated items in each query as Count(rows for report) with aggregate property as "Calculated".
4. Created 2 boolean variables one for each list and had [Report1].[Row Count] <=0 and [Report2].[Row Count] <=0 respectively.
5. Unlocked the report and selected the text items in header and footer and assigned the appropriate boolean variables to each of them. (i also tried setting style for the entire header or footer instead of the text item alone)
6. For "No" value of the boolean variable the corresponding text item's box type was set to "None"
7. Created prompt page to enable filtering report data to populate no rows for a particular list.

The header and footers in the list are not visible at all, whether there is data or not. All i see are the list titles with no rows when the list does not have any data... Is there anything im doing wrong?

[Rajaggopal]

Could someone please help me on this?

[Gollapudi]

Hi,

I just created a sample report, please find the attached specification.

Thanks,
Gollapudi.