SSRS Int64 Multi Select Report Parmeter error
-
I have a SSRS report in VS2017 that pulls Int64 values into a Report Parameter named "pCampusEquipment" but fails at runtime with a "The property "ValidValues" of report parameter "pCampusEquipment" doesnt have the expected type" error. The parameter works fine if I changed parameter Data Type to "Text" instead of "Integer" but the SQL Call fails because the sql query is expecting an Integer array.
Is this at all possible? Also too I'm using the OSI PI SQL Client driver so a store proc is not possible.
-
This one is actually answered in the documentation. First I went here:
which gives an overview of report parameters in SSRS 2016. From there, I searched for "Data Type" which listed the data type and had a link to here:
Which lists the RDL data types. Looking at that table, you can see that "Integer" corresponds to "Int16, Int32, UInt16, Byte, SByte". There is not an option for INT64 (or BIGINT in the SQL world).
What you will likely need to do is take in a "Text" input from the report side and process it on the SQL side to break it up into int64/BIGINT values.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
"What you will likely need to do is take in a "Text" input from the report side and process it on the SQL side to break it up into int64/BIGINT values."
Yes, that is the question. How can I convert a multi select array of strings to a "WHERE [ID] IN (1,2,3)"?
-
That part is the EASY bit. Set the report parameter to allow multiple values. Then set the report parameter to be a variable in the query and use your WHERE [ID] IN (@inputParameter).
For example, I did a query like this:
DECLARE @test AS BIGINT
DECLARE @test2 AS BIGINT
SELECT @test= 10000000000
SELECT @test2= 10000000000
DECLARE @test3 AS BIGINT
DECLARE @test4 AS BIGINT
SELECT @test3= 10000000002
SELECT @test4= 10000000002
select @test as test, @test2 as test2
where @test2 IN (@input)
union
select @test3, @test4
where @test4 in (@input)as my "data set" for the report. Then I did a second query the same as above, but selecting only @test2 and @test4 for use in my parameter. Parameter was named "input" and I mapped that up in the report data set to @input variable. Then set the input parameter as allowing multiple selections with no default (so I can force the end user to pick a value before continuing) and it just works. No fancy work converting the BIGINTs over, just need to keep in mind that it will do implicit conversion of the input values from VARCHAR (or NVARCHAR... I forget how ssrs handles that) to BIGINT to match the datatypes in ID.
This way they have a dropdown where they can pick multiple values and then they are good to go.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
The SQL portion doesn't seem to be the problem, it's getting a Parameter Long value or a Text value and converting to long for the SQL Call.
-
I am not familiar with PLSQL, but with the method you are doing in that query, you are doing a 1-1 comparison, so @ID MUST be a single value, unlike if you use IN where SSRS will automatically put it as a comma separated list for you.
The error you are getting of "unknown data type "bigint"" makes me think that PLSQL doesn't support BIGINT and uses something else for a 64-bit integer (possibly int64, but I am not certain).
Does PLSQL not support implicit conversions like TSQL? If so, you should be able to skip out on the CAST bit of code, no?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply