report displaying nothing

hi

i have req,if report display nothing on that date,means i am getting no data,still i need to display two  field in the report which is text.how to do that

Use SP, which check number of rows returned, if none use select default vulues.

Are the 2 fields fixed data, or are you expecting to populate them with something from a query?

There is a NoRowsMessage property on a report that is made for this purpose specifically.  When your report does not retrieve any data it will display the NoRowsMessage.  You can find the NoRowsMessage property in the advanced properties for the tablix.

i have sp which has select stm that is returing using join.

how to caluclate no of rows and then field ,can u tell me that stm

Thank you Daniel, I didn't know about it

I did it with creating temperary table and instead of select used insert into #temp_table select .....

After select  @my_var  = count(*) from #temp_table and if @my_var =0 select default else select * from #temp_table

u mean

insert into #temp1

select fields1,fileds2 from name

select @my_var= count(*) from #temp1.

if @my_var = 0

then

select  field from #temp1

make me correct

how to declare @my_var and #temp1

plz explain me

CREATE PROCEDURE my_procedure

as

BEGIN

DECLARE @my_var as int

create table #temp_table(col1 int, col2 varchar(5))

 insert into #temp_table

select .....------------ YOUR QUERY-----

 select  @my_var  = count(*) from #temp_table

if @my_var =0

begin

select default as col1, default as col2

end

else

begin

select * from #temp_table

end

drop table #temp_table

END

Brian wrote an article on NoRowsMessage here

http://www.bidn.com/articles/ssrs-development/182/where-are-my-records-using-the-norowsmessage-property

ok.

i have tablix ,and i can out only 1 parameter in expression.

what about i need to show two filed when there is no row

Just select two default columns

select defaultExpression as col1, defaultExpession as col2

simon its not working

coz in my temp i am select using join

so if i do select from temp it shows me nothing

daniel,

what if i have to show header of tablix,its not showing header.

What data are you trying to return when the report is blank?  Can you give some sample data, and some sample returns.   How would your report look when it has data, how would it look when it has no data?

laki,

first you should insert into temp table using your join query,

after you check with count(*) how many records you inserted and if more than 0 - you select from temp table

other way you hardcode select.

when no data is there the out put is

item1    item2      item3

if i do that way you show me 

i am just getting output like

pencil

no header and i am not getting for two filed.like i have param for item1 but not for item2

how to write expression

" i have req,if report display nothing on that date,means i am getting no data,still i need to display two  field in the report which is text.how to do that "

by this req do you mean that when your query or SP doesn't return values you still to show two fields?

If so - I provided you solution for SP which will return data with any date, just if it's not data for  "on that date" - SP will return hardcoded default values.  Column names for hardcoded select must be same as in your query.

If you have matrix report and need tree pivot items shown on the report you can

select defValue as col1

union all

select defValue as col2

union all

select defValue as col3

If this all is not correct - just

"Can you give some sample data, and some sample returns.   How would your report look when it has data, how would it look when it has no data?"

simona,

this simy sample report when there is data

item1 item2 item3

1       pencil     30

2       pen     32

i am getting this using join 

now if there is no data

still i need to show 

1      pencil

2     pen

means first two column.

how to write in expression

if i do union all ,it will display all the item1 and item2

you can try

select 1 as item1, 'pencil' as item2, Null as item3
union all
select 2 as item1, 'pen' as item2, Null as item3

this is hard coded,i dont know what report will, so this is different for each report,if i do for 1 report .it wont work for other report

Can you show your query?

i cannot place my code here ,it is confidiential .

any other way i can do this

i am using main query by join,so if i do pnly 3 column to show it wont display

Ok, for default items you can do:

select  distinct  item1, ' item2, Null as item3

from ItemsTable

This way you'll have your items without creteria and not hardcoded.

my prob is i caanot display third as null

i just need to show first 2 column

and that i need to get from 2 views so i m doing inner and left ,but its not working

Can you do not use inner or join,

just

select  distinct  item1, item2

from ItemsView

It's very hard to make guesses without knowing all picture