Filter the Dataset Fields through Parameters

Question

Post reply

Filter the Dataset Fields through Parameters

subasuga

Mr or Mrs. 500

Points: 510
More actions
July 19, 2010 at 10:05 pm

#237804

Hi all..
Would like to know how to filter specific fields in a dataset from a given parameter.
For eg. I'm having a dataset with fields F1,F2,F3...F10
I need to show the fields only through a parameter.. If in the parameter, fields F8 & F10 are selected, only those fields must be shown.. Also note that the field names are in TEXT and the values they hold are integers.
Pl help asap..

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Abs-225476 Hall of Fame Points: 3483 More actions · Answer 1

Hi,

If you are using a sql query your dataset should be like the script below:

select * from mytable

where myfield in (@parameter)

Then run your query in the data tab, it will prompt you for the parameter value enter a value and make sure it works.

To allow more than one value to be netered in the parameter go into the report menu and parameters and check the multi-value check box.

subasuga Mr or Mrs. 500 Points: 510 More actions · Answer 2

Hi..

Thanks for the response.

But then i had already mentioned that i want to find the fields and not values in the fields.

select * from mytable

where myfield in (@parameter) In the above query, I dont want '*' . Instead i want the user to select the fields.. Also I dont know the 'myfield' .

So the query should be something like

Select @parameter from my table where date between @date1 and @date2

subasuga Mr or Mrs. 500 Points: 510 More actions · Answer 3

subasuga

Mr or Mrs. 500

Points: 510

July 22, 2010 at 2:52 am

#1196688

Suggestions??

DataDog Hall of Fame Points: 3305 More actions · Answer 4

DataDog

Hall of Fame

Points: 3305

July 22, 2010 at 2:59 am

#1196694

Dynamic SQL

build a SQL string and execute it

weitzera Hall of Fame Points: 3442 More actions · Answer 5

Not quite sure I understand exactly what you want. Do you want to build a report that shows a table of user selected columns from a list of available columns? you can do this with dynamic sql, or by dynamically hiding the columns the user didn't select. Or are you trying to show different parameters depending on what the user selected previously? This is not possible in SSRS 2005 (not sure about 2008) What you can do is always show your parameters, but restrict the available values in those parameters. Perhaps a better way is to use a custom front end to capture the parameter values, and then pass those to the report server.

I'm assuming you are using SSRS...

Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'

subasuga Mr or Mrs. 500 Points: 510 More actions · Answer 6

Yeah..am using SSRS

Exactly what u had assumed/ understood.

I want to build a report that shows a table of user selected columns from a list of available columns..

How do you do it?

oi21 SSC Rookie Points: 44 More actions · Answer 7

oi21

SSC Rookie

Points: 44

July 24, 2010 at 6:20 am

#1197852

only solution is use dynamic MDX query

oi21 SSC Rookie Points: 44 More actions · Answer 8

step1: create one aspx page which select colums

step2: convert tht quey into MDX format

step3:send tht in report viewer web part as a dataset

... we resenty work on this only....n we got only this solution

weitzera Hall of Fame Points: 3442 More actions · Answer 9

One simple way to do this is to simply set your datasource so that it returns all possible columns and set up your report to display based on this default case. Then, for each optional column, set the hidden property to an expression that checks the value of the paramter you created for that field.

Creating dynamic sql in your dataset is also viable, but can be more of a pain than it's worth.

Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'

MMartin1 One Orange Chip Points: 27795 More actions · Answer 10

The above post makes a good suggestion. The alternative would be to do something like

If @parameter = 'column1'

select column2, .., column N

from ...

else if @parameter ='column2'

select column1, column3, ...., column N

The CASE statment wouldn't make the code more readable unless you represented every possible recordset with a stored procedure, which you would need to create so it is still tedious work.

----------------------------------------------------

subasuga Mr or Mrs. 500 Points: 510 More actions · Answer 11

Hi guys..

Thanks for the responses.

I have dealt it with Unpivot-ing the table. I converted all the required fields into one column and the corresponding values to another column. And the rest of the procedure is as simple as than.

Here is the code for reeference:-

SELECT <Losses>, sum(value) AS totvalue

FROM (SELECT Loss1,Loss2,Loss3,Loss4,Loss5 FROM Losses) <L> UNPIVOT (value FOR Losses IN (Loss1,Loss2,Loss3,Loss4,Loss5))

AS <unpvt>

GROUP BY losses

Note: The Names given within <> can be anything