Parameter is missing a value

Hi there,

I have an issue where I need to set a whole bunch of internal parameters' values to the result of a dataset first executed with the report...nothing new or majestic about it.

The problem is that the 'First dataset', under some parameter values, return no records..it is empty (which is fine). But this meas that these internal parameters error my report as the 'NEED' a value even though I have specified a default, I still get the 'Paremeter X is missing a value' error. I just want the other datasets, which are reliant on these erroringparameters to either execute with parameter values of 0 or not execute at all. Thus I want the sub datasets to return with blank data in this case or not execute at all.

How can I achieve this? I am at a loss.

Cheers

Milke

November 9th, 2008 1:08pm

Hi Mike,


How do you use your first dataset? Is it only for populating internal parameters? Do you use the results if it returns multiple rows?

Regards,

Krisztian

Free Windows Admin Tool Kit Click here and download it now
November 10th, 2008 8:33pm

Hi Krisztian,

The first dataset is used for both displaying of fields as well as populating internal parameters. There is only 1 row in the first dataset.

Regards
Mike
November 10th, 2008 9:30pm

If the internal parameters receive their defaults from the first dataset (and you dont display them, so it doesnt matter if they have multiple values) you can try adding an extra row to the first dataset:


Parameters take their default values from the first row of the referenced dataset, so you can add an extra row to the dataset which contains default values for the case when your query return no results.


SELECT Col1, Col2, Col3 FROM Table1 WHERE Col4 = @Param1
UNION ALL
SELECT Default1 as Col1, Default2 as Col2, Null as Col3


This query will always return at least one row, and your internal parameters will get their defaults.

If multiple rows cause problems, we can find another solution.

I hope it helps,

Krisztian

  • Proposed as answer by Arun Saxena BI Tuesday, June 17, 2014 10:28 AM
Free Windows Admin Tool Kit Click here and download it now
November 10th, 2008 10:37pm

Hi Krisztian,

I changed the query to always return the values as per your suggestion but I've found that the secondary dataset still bombs as soon as I set it's parameters mapping to the internal parameters. If I hard code the secondary dataset's parameters, it works..but it doesn't like me mapping it to my internal parameters. I even double checked the internal parameters' values by dropping it onto the report and it shows 0 (in cases where null) so that works fine.

I don't think my problem lies with the whole null thing at all.. its as though the report doesnt want me to use the internal parameters at all.

Cheers

mike

November 11th, 2008 7:33am

Hi Mike,

Are you getting the same "parameter is missing a value" message? In this case please make sure that the internal parameters allow null values.

Thanks,

Krisztian

Free Windows Admin Tool Kit Click here and download it now
November 11th, 2008 8:02pm

Hi Krisztian,

I managed to solve this issue by changing 2 things:

1) Changing the dataset property to execute using a single transaction for the queries
2) Changing the stored proc to never show null values and rather a 0 since they are integers

I'm not sure whether step 2 was an overkill ince I'm sure that SSRS can handle null values appropriately for numeric data types. I think the "parameter is missing a value" error only pops when I allocated a hard coded value to the "available values" of the parameter but the default value is still pointing to a dataset field on the primary dataset, which still does not have a value due to the secondary dataset finishing before the first one. This causes the default value to not be received and thus gives the error message.

I am just glad I managed to solve the issue Smile

Thanks for the help
Mike
November 11th, 2008 11:45pm

Hey Kris

This solved my problem, thanks a lot for this intelligent workaround.

I mark this suggestion as the solution of the problem mentioned above

BR

Arun saxena

Free Windows Admin Tool Kit Click here and download it now
June 17th, 2014 10:28am

I had this problem when I had a data set that was picking up ethnicities from a table and using this in an Ethnicity parameter.  This data set did not have a filter so was picking up every possible ethnicity regardless of the context. 

In my case I was using this parameter for medical practices.  The error seemed to occur when I wanted to pass this multi valued parameter to a another report in a 'Go to Report' action.  In sharepoint the error was 'The ethnicity parameter is missing a value'.

When I filtered the dataset so that it only gave ethnicities that existed at a particular medical practice the error disappeared.  I am not sure why this occurred as the dataset for the ethnicity parameter did not have any direct relationship to the practice? Maybe because the dataset for the main report only contained certain ethnicities it automatically filtered my ethnicity parameter dataset and when this tried to pass it to another report, the other report go upset because the Ethnicity parameter in that report contained more values?? 

June 18th, 2015 1:25am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics