question

NavinkumarKanagaraj-1813 avatar image
0 Votes"
NavinkumarKanagaraj-1813 asked Isabellaz-1451 answered

Parameters in SSRS _ Based on previous parameter

https://docs.microsoft.com/en-us/answers/questions/810809/ssrs-default-parameter-based-on-the-data-with-limi.html

hi, In the Below link, we could see that the default parameter is fixed on conditions. But I have a Concern that what if Based on the previous parameter, what if we have no values. then in that case I want to see none available as a result.

Points: 1. I have tried using the IF statement in the Above query using putting the result in the tmp table, but facing error.

sql-server-reporting-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Isabellaz-1451 avatar image
0 Votes"
Isabellaz-1451 answered

Hi @NavinkumarKanagaraj-1813
You can try this:

  declare @hasappleororange int
  declare @hasnovalue int   
    set @hasnovalue =(select count(*)from TABLEC)
    if(@hasnovalue=0)
    begin
      select NAME= null
     end
     else 
     begin
     set @hasappleororange =(select count(*)from TABLEC where NAME in('APPLE','ORANGE'))
     if (@hasappleororange>=1)
     SELECT distinct NAME from TABLEC where NAME in('APPLE','ORANGE')
     
     else
     SELECT * FROM (VALUES('GRAPE'),('KIWI')) AS p(NAME)
     end

when there is no data, result is:

202631-image.png

Best Regards,
Isabella


If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



image.png (2.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.