September 19, 2008 at 2:10 pm
Hi,
I would really appreciate if somebody could help me out with this. I am not even sure if this is possible.
I am building a report using SSRS, I have 2 multi-value parameter. The 2nd one is based on the first parameter list. I am trying to populate this parameter with at least 'NULL' value all the times. This is what I am trying but not working as expected.
(SELECT DISTINCT COMPID
FROM
COMPANIES
WHERE
ORGKEY IN (@ORG) ---> This is my first parameter
)
UNION
(SELECT NULL
FROM
COMPANIES
)
ORDER BY
COMPID
Thank you guys in advance.
§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§
always Happy, always Prasanna 🙂
September 22, 2008 at 6:55 am
Do you want to include "NULL" at all times? Typically when I have parameters I have an ID I use as the value and a name or desc I use for display like, company_id, company_name and then when I need a null I pass out NULL as company_Id, 'ALL', as company_name with a union query similar to what you are doing.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 22, 2008 at 7:59 am
Thank you Jack for the reply. It would not mind having null as one of the option at all times. My main concern was to be able to have it at least null. It is a required parameter and I cannot run the report if the parameter list is empty i.e. no selection to choose from. So basically if I try to run the report I get a message saying select the parameter but nothing to select from. I am not sure if I understand what you explained. The query would work fine if the parameter is NOT multi-value parameter but in SSRS, it does not allow to check the option of "Allow null" for multivalue parameters and I was trying to insert it manually. If you have a suggestion regarding the matter, I would love to hear it. Once again I appreciate your time.
Thanks !!
§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§
always Happy, always Prasanna 🙂
September 22, 2008 at 8:17 am
Can you attach the RDL file so we can see how you have setup your parameters and datasets? It sounds like you have not mapped the parameter value from your first multi-value parameter to the SQL Parameter in the second dataset.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 22, 2008 at 8:58 am
Thank you again Jack. I would love to have you look at my rdl but I am not sure about the policy so let me try to lay out the picture as much as I can. The main dataset that populates the report table is something like this.. (Exact name has been changed..)
SELECT
FW.ORGID
, DL.UNIT
, FW.ORGNUM
, FW.DESCRIPTION
, DL.LOC
, FW.STATUS
, FW.PRIORITY
FROM
FW LEFT OUTER JOIN DL
ON FW.LOC_KEY = DL.LOC_KEY
WHERE
( (FW.ORGKEY IN (:ORG))
AND (DL.COMPID IN (:COMPID))
AND ((FW.REPORTDATE >= (:FromDate))
AND (FW.REPORTDATE <= (:ToDate))) )
ORDER BY
FW.ORGNUM DESC
The dataset for the first parameter is something like this...
SELECT DISTINCT
ORGID
, ORGKEY
FROM
DS
ORDER BY
ORGID
In the report parameter, the parameter named ORG is Integer DataType and Multi-value which is assigned the above Dataset with value field being ORGKEY and label field being ORGID.
The Dataset for the second parameter is something like this..
(SELECT DISTINCT COMPID
FROM
DL
WHERE
ORGKEY IN (@ORG) ---> This is my first parameter
)
UNION
(SELECT NULL
FROM
DL
)
ORDER BY
COMPID
In the report parameter, the parameter named COMPID is STRING DataType and Multi-value which is assigned the above Dataset with value field and label field BOTH being COMPID.
The other 2 parameters FromDate and ToDate are just DateTime data type.
When there is a match with the sites selected from from 1st parameter ORG i.e. if certain ORGKEY are only selected then I have data for COMPID parameter as well and the report runs perfectly fine but for certain selection from 1st parameter ORG, the dataset COMPID returns nothing so the parameter list is blank and cannot proceed with running the report. This is where I want to have null so that I can proceed with report so that the user can get a message at list like "No Data found" !!
Any thoughts ? Thank you and sorry I am unable to provide the rdl.
§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§
always Happy, always Prasanna 🙂
September 22, 2008 at 9:21 am
How about this code in the second parameter dataset:
[font="Courier New"]IF EXISTS (SELECT COMPID FROM DL WHERE ORGKEY IN (@ORG))
BEGIN
SELECT DISTINCT
CompId
FROM
DL
WHERE
ORGKEY IN (@ORG)
END
ELSE
BEGIN
SELECT
NULL AS CompID
END[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 22, 2008 at 9:47 am
Thanks Jack but I got an error running the statement. This is the error statement I got..
An error occurred while executing the query.
ORA-00900: invalid SQL statement
Additional information:
ORA-00900: invalid SQL statement
(System.Data.OracleClient)
I am sorry I might have missed to state the database I am running against is the Oracle database. So the Data Source is oracle. I am not sure if it would have something to do with it. But I really do appreciate your help.
§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§
always Happy, always Prasanna 🙂
September 22, 2008 at 9:56 am
Yeah, I don't know Oracle, so you may need a from clause also I don't know if Oracle supports IF EXISTS. I also don't know if anything other than the select is in correct Oracle syntax. I also don't have Oracle anywhere to give it a go.
I thinkthe basic concept is appropriate though, you just need to fix it to use Oracle syntax.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 22, 2008 at 10:05 am
Yup I see what you are saying. It is a PL SQL that I am using.. very similar to T-SQL ; I will definitely look at the way you suggested.
Thank you Jack for all your help and time. If I fix this I will definitely try to post it here.
§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§
always Happy, always Prasanna 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply