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
insert into #temp1
select fields1,fileds2 from name
select @my_var= count(*) from #temp1.
if @my_var = 0
select field from #temp1
make me correct
how to declare @my_var and #temp1
plz explain me
CREATE PROCEDURE my_procedure
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
select default as col1, default as col2
select * from #temp_table
drop table #temp_table
Brian wrote an article on NoRowsMessage here
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
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?
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
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
select defValue as col2
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?"
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
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 item3union allselect 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
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,
select distinct item1, item2
It's very hard to make guesses without knowing all picture