[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 373
  • Last Modified:

how to retrieve a specific value or all values from a parameter in sql reporting services

I have to tables :

Table 1
Id numeric
Name  varchar
Type    varchar

Table 2
Type  varchar
Type_name  varchar



Table1
Id   name            type
1   aaaaaa          a
2   bbbbb           b
3   cccccc           b
4   ddddd            b
5   eeeeee           c


Table 2
Type    Type name  
A         type a
B          type b
C          type c
D          type d


This is the main dataset select statement

Select id,name,type
From table1
Where id = @id
And
            Type = @type


What I’m facing is some times the user want to enter a value for the id parameter and a specific value for the type parameter, and some times the user want to enter a value for the id parameter and all the values for the type parameter.

I have been searching lately and I found a solution below but I’m not trying to get it together.
I create a dataset named DS.
1.      SELECT ‘0’ AS type, 'All' AS Type Name
2.      UNION
3.      SELECT type,type_name
4.      FROM table2
5.      ORDER BY type_name


And then I selected Available values from the parameter properties screen just to use a combobox, then I have been enforced to use the generic query designer to replace the original query with the following expression:
="SELECT id,name, type FROM table1" & IIf(Parameters!type.Value = “0”,""," WHERE (type =   " & Parameters!type.Value & ")") & " ORDER BY type"

This expression works fine but what about the main dataset because I have another parameters to select also, I triad to work with this expression by including  both statement but I get errors.


If you need any more info then let me know.

Thanks  in Advance :)


 







0
bduhaish
Asked:
bduhaish
  • 7
  • 4
1 Solution
 
volkingCommented:
Your question is a bit confusing so I'm not sure this will help, but here goes.

I've found assembling a CSV (Comma Separated Value) string can be fairly easy. For example: 12,234,1,982
The hard part is using the CSV to select a group of records.

=================================================================
First we create a function to convert the CSV into a table ...
=================================================================
CREATE FUNCTION fnCSVtoTable (@CSVs varchar(8000))
    RETURNS @t TABLE(IDs int) AS
    BEGIN
        DECLARE @pos int, @comma varchar(1)
        SET @comma=','
        SET @pos=CHARINDEX(@comma,@CSVs)
        WHILE @pos>0
             BEGIN
                 INSERT @t values (cast((LEFT(@CSVs, CHARINDEX(@comma, @CSVs) - 1 )) as int))
                 SET @CSVs = SUBSTRING(@CSVs, CHARINDEX(@comma,@CSVs) + 1, LEN(@CSVs))
                 SET @pos = CHARINDEX(@comma,@CSVs)
             END
        IF LEN(@CSVs)>0
             BEGIN
                 INSERT @t(IDs) SELECT @CSVs
             END
        RETURN
    END

======================================================================
Then we use the above function to select items appearing in the list
======================================================================
CREATE PROC EntitiesByCSV_ID_list
(
    @ListOfEntityIDs varchar(1000)
)
    AS

    SELECT
        Entities.EntityID,
        Entities.FullName
    FROM Entities
    WHERE
        (Entities.EntityID IN (SELECT IDs from fnCSVtoTable(@ListOfEntityIDs)))

======================================================================
And ultimately we call our stored proc
======================================================================
Exec EntitiesByCSV_ID_list('12,234,1,982')

===================================================================================================
This approach can be used to selectively gather multiple similar items based on different criteria.
Here's a sample stored proc which accomplishes some fancy selections on multiple parameters
===================================================================================================
CREATE PROC EntitiesByCSVlists
(
    @ListOfEntityIDs varchar(1000),
    @ListOfEntityTypeIDs varchar(1000),
    @ListOfEntityIDsToExclude varchar(1000)
)
    AS

    SELECT
        Entities.EntityID,
        Entities.FullName,
        Entities.EntityTypeID
    FROM Entities
    WHERE
    (
        (Entities.EntityID IN (SELECT IDs from fnCSVtoTable(@ListOfEntityIDs)))
        OR
        (Entities.EntityTypeID IN (SELECT IDs from fnCSVtoTable(@ListOfEntityTypeIDs)))
    )
    AND
    (
        Entities.EntityID NOT IN (SELECT IDs from fnCSVtoTable(@ListOfEntityIDsToExclude))
    )
0
 
BillAn1Commented:
rather than using an expression to create a dynamic query, you can put a conditional clause within your query , e.g.

Select id,name,type
From table1
Where id = @id
And (Type = @type OR @type = '0')

that way, you can similarly string together multiple AND clauses in your query, with out having to use th IIF construct at all.
0
 
bduhaishAuthor Commented:
ok guys my question was not clear all explain it again, all i want is to parameters i want to select all the id's or a specific id AND all the types or a specific type that's it

Note the ID is not a primary key in table1............
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
bduhaishAuthor Commented:
BillAn1 ,

rather than using an expression to create a dynamic query, you can put a conditional clause within your query , e.g.

Select id,name,type
From table1
Where id = @id
And (Type = @type OR @type = '0')

how can i retrieve all values of  @type when i write this conditional clause  

0
 
bduhaishAuthor Commented:
If there is any more info then let me know.
0
 
BillAn1Commented:
I'm not sure the exact question, but I'll outline how I think your solution is working. Correct me where I have gotten any assumptions worng, and ask any questions about anything I have not given enough detail about.

You have a report, which returns data from your Table1, and you want to be able to limit the data to a specific ID or all IDs, and also to limit it to a specific type, or all types.
To achieve this, you need to have 2 parameters, 1 called @ID and 1 called @type.
The @ID parameter is an integer parameter, and the user simply types in a value. Make sure you have set the type of parameter to integer (the default is string) and then tick the checkbox "Allow null values" - this will meanthe user can simpy not enter a value for the @ID parameter if they want all IDs
The @TYPE parameter is to be populated by a drop-down list, prepopulated with the data in your table2. In order to populate this drop-down you create a dataset called DS which you populate with a SQL query from table2. To enable the users to select ALL types, you want to add an extra value in the drop-down of type 'ALL' - and this you ahve achieved by adding a union to your query
SELECT ‘0’ AS type, 'All' AS Type Name
UNION
SELECT type,type_name
FROM table2
ORDER BY type_name
you ensure that your parameter is mapped to the type field of this record set, so if the user selects 'ALL' the parameter is 0 etc.

Now you come on to your main dataset query which returns the data for your report - you need this query to be able to handle the case that if @ID is null then all IDs should be included, and if @TYPE = 0 then all types should be included,
SO, your SQL should be


Select id,name,type
From table1
Where (id = @id OR @id IS NULL)
And (Type = @type OR @type = '0')

note, sometimes reporting services takes it upon itself to rejig your query, e.g. it may change the above to be

Select id,name,type
From table1
Where id = @id And (Type = @type OR @type = '0')
OR @id IS NULL And (Type = @type OR @type = '0')

this is logically the same thing, so don;t get alarmed if it happens. I haven't figured out why it does it!!


Does this do justice to your problem?
0
 
bduhaishAuthor Commented:
thanks ,BillAn1

testing
..
.
.
.
.
.
.
.
.
0
 
bduhaishAuthor Commented:
BillAn1  ur answer is clear ,but actually i need one or two hours testing because my example is bigger than the one i post but it gives the same function.

So I'll tell you the results later.

Thanks in advance




0
 
bduhaishAuthor Commented:
Sorry for been late
now you solved my problem and what take me so long is like you said sometimes reporting services takes it upon itself ,what i did is not logical like i studded  but any way it's been solved with exact data retrieving, now after my question has been cleared i have a small tiny thing related to what i posted previously.

Assuming that i want to make the type parameter a combobox  or drop-down.
 by opening the report parameters window and
1- Check allow null values
2- In available values ---> from query and selecting the dataset i created for table 2 and assigning value field to type and label field to Type name.

now what i'm facing is in the preview report the null check box don’t appears but if i go back and check non queried in the available values of the report parameters window it will appears, so is this a fact in Sql Reporting Services or I’m missing something.

note :all data in type column are like A,B,C,D only .
 
0
 
BillAn1Commented:
" so is this a fact in Sql Reporting Services or I’m missing something."
As far as I can tell, it is a fact of SQL reporting services. If a parameter has a data set as the available values, then the "allow null values" checkbox has no effect. This is the reason why you need to add an extra "ALL" value to the dataset, so that there is a valid option the users could choose. It would be better if the "allow null values" option functioned as expected, but for some reason it doesn't. Perhaps other experts can shet light on whether this is intentional on Microsoft's part, or a bug?
0
 
bduhaishAuthor Commented:
BillAn1 thanks,
now what shall I do I'm afraid that other expert will shift me to another way after i have build my requirement on the way I want , so shall I  post the same question a gain or do you have another way  to solve it.
0
 
BillAn1Commented:
As far as I know, the way to solve it is as you have done, using the 'ALL' entry in the dataset, I do not know of any other method.
Do you still have an outstanding problem or are you just looking for a different way to implement the solution you currently have? If you still have a problem, feel free to post further question here, or start a new question if you want to elicit other experts' opinions.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now