0

I'm using Reporting Services to show some data from a dataset in SQL Server, which has two parameters. Now, if the button 'View Report' isn't clicked, the data won't show on page load (Preview tab in Visual Studio / Web Browsing at localhost). What can I do that on page load, the data would show nonetheless if parameters are filled or not?

I have tried changing SQL code to include IF conditionals so that if parameters are null, select all the data, ELSE, display data by parameters but this didn't work, it seems like the query of data set is executed only when clicking the 'View Report' button.

Currently, I am using this SQL code which works fine when searching with filled parameters and clicking the aforementioned button:

IF(@SearchName IS NULL OR @SearchName  = '')
SELECT * FROM dbo.person;
ELSE
SELECT * FROM dbo.person WHERE ((name LIKE '%' + @SearchName + '%') OR (surname LIKE '%' + @SearchSurname+ '%'));

The expected outcome is returning all the data set (SELECT * FROM dbo.person) unless the parameter is filled and the button is clicked.

The VaLo
  • 35
  • 1
  • 12
  • How are you launching the report (your code)? Or do you mean adding default values so it shows data when navigating to the report on SSRS in a browser? – HardCode Nov 05 '19 at 14:35
  • I do add data to the database using SSMS then in VS I have made a new SSRS project using the data source of the data that I created in SSMS. They display fine, just as I wrote the query but now only in the browser the data doesn't display automatically, however thanks to @Larnu it works fine in the Preview tab in VS. – The VaLo Nov 05 '19 at 18:26

2 Answers2

1

You need to give the parameters a default value in the report; in this case it seems it needs to be NULL.

Right click your parameter in the Report Data pane and go to Parameter Properties. Ensure that the property "Allow null value" is enabled. Then go to the Default Values pane, select "Specify values" and the Add. Then OK.

Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Thanks for the input. This has partially solved the problem: in Visual Studio, in the Preview tab now the data are displayed without entering the parameters, however, this doesn't work when viewing in the web browser. Any idea why? – The VaLo Nov 05 '19 at 16:15
  • Have you reployed the report, or reconfigured the default values on the server (as they aren't always set when a report is redeployed), @TheVaLo ? – Thom A Nov 05 '19 at 16:19
  • Deploy you mean? Yes, I deploy the report for almost every change. That's how it opens a new tab in browser automatically after deploying to the localhost. In VS it works, but not yet in the browser. Strange. – The VaLo Nov 05 '19 at 18:22
  • Yes. Did you manually reconfigure them on the server then? As I said, someone the deployment doesn't pass them over. – Thom A Nov 05 '19 at 18:26
  • I have only reconfigured default values of parameters (which are (Null) actually) on the VS project of SSRS. I don't think I've touched 'default (?)' values of the data in the SQL server though, at least not consciously. – The VaLo Nov 05 '19 at 18:31
  • Not on the data side, on the report server, @TheVaLo . Like I said, when you (re)deploy a report they aren't always passed. Check the report on the report server. – Thom A Nov 05 '19 at 18:57
  • Otherwise delete the report on the server (which will delete everything related to it) and redeploy it, but setting the default values on the report is far easier. – Thom A Nov 05 '19 at 18:58
  • [Add, Change, or Delete Default Values for a Report Parameter](https://learn.microsoft.com/en-us/sql/reporting-services/report-design/add-change-or-delete-default-values-for-a-report-parameter?view=sql-server-ver15) – Thom A Nov 05 '19 at 19:00
  • I'm baffled. What value do I set in the Default Value in Parameter Properties so that all the data would be displayed? – The VaLo Nov 05 '19 at 22:36
  • According to your SQL, @TheVaLo, `NULL`. So unless you're using a different SQL source on the deploy report, `NULL` will work on that too. If `NULL` *isn't* working on the deployed report, (and isn't return results) then the SQL you have on the developed report and the deployed report is different; which likely means someone else has deployed a version since you did. I would consult your Source Control it that's the case. – Thom A Nov 05 '19 at 22:56
  • No, `NULL` doesn't work, unfortunately. Thanks for the input. The only thing that's working is on the Preview on the VS. I still gotta search for a solution somewhere so that it'd be displayed on the web too. Thanks again, it seems like *generally* this would've fixed the problem. – The VaLo Nov 05 '19 at 23:38
  • @TheVaLo but why haven't you checked the default values on the deployed report like I asked so many times...? If it works in VS, it'll work on the deployed report. If it's not working on the deployed report, then the deployed report **is** different and you need to tell me what SQL that is running. – Thom A Nov 06 '19 at 07:42
  • The only reason a report won't load automatically is because it's parameters don't have default values, or if they are set and aren't valid. Which is is here, @TheVaLo ? Add those details to your question and I can explain how to fix that. At the moments the report isnt loading as you've not checked the deployed reorts settings; of that I am sure (because you've not confirmed you've checked). – Thom A Nov 06 '19 at 08:02
  • I have created a new Reporting Services project in VS, using the same data and properties, and now it works! Thanks for the help. – The VaLo Nov 07 '19 at 12:22
  • I don't follow what you're asking there, @TheVaLo . – Thom A Nov 07 '19 at 12:42
  • @Larnu, in my case, all the parameters values are correctly populated. There is no null value, still facing the issue of report waiting to press View Results. – Kasim Husaini Jan 30 '23 at 06:43
0

In my case, the issue was caused by my default value(s) query for one of my parameters returning rows that are not present in my available values query results.

This is not immediately obvious as the erroneous rows aren't shown — because they are not in the available list.

blackgreen
  • 34,072
  • 23
  • 111
  • 129
will valintine
  • 25
  • 6