setting display order of parameter boxes and dynamic parameter ranges

Question

setting display order of parameter boxes and dynamic parameter ranges

shnizzle

SSC Journeyman

Points: 91
More actions
July 22, 2008 at 1:57 am

#186735

Two problems that I'm trying to solve:
1) I have 3 parameter boxes - P1, P2, P3.
The user of the report usually sees them arranged as
P1 P2
P3
and I would like them to appear as
P1 P2 P3
is there any way to set the parameter placement?
Also, is there any way to write a custom parameter input control, for example: use a slider control instead of a drop-down list of numerical values.
2) The above parameters represent a partitioning of a segment. Let's say that the segment is 100 units long, so 20,50,80 would be a good partition but 20,14,80 would be illegal.
Now, I can check if the parameters represent a legal partition in the stored procedure that generates the report (and generate and empty report if it isn't) but what I would like to achieve is when a user enters 20 as the value for P1 then the range for P2 would be dynamically altered to [21,100] so the user is forced to pass legal parameters to the report.
Is this possible?

Viewing 6 posts - 1 through 5 (of 5 total)

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

nigel.c.west Hall of Fame Points: 3468 More actions · Answer 1

Two problems that I'm trying to solve:
1) I have 3 parameter boxes - P1, P2, P3.
The user of the report usually sees them arranged as
P1 P2
P3
and I would like them to appear as
P1 P2 P3
is there any way to set the parameter placement?
Also, is there any way to write a custom parameter input control, for example: use a slider control instead of a drop-down list of numerical values.
2) The above parameters represent a partitioning of a segment. Let's say that the segment is 100 units long, so 20,50,80 would be a good partition but 20,14,80 would be illegal.
Now, I can check if the parameters represent a legal partition in the stored procedure that generates the report (and generate and empty report if it isn't) but what I would like to achieve is when a user enters 20 as the value for P1 then the range for P2 would be dynamically altered to [21,100] so the user is forced to pass legal parameters to the report.
Is this possible?

The answer to your first question is No, as far as I am aware there is no way to achieve this in reporting services. I assume that if you are creating a .NET application and are embedding reporting services into that app then you could control the positioning of parameters exactly as you wish, but if you are relying on SSRS alone then I would say no.

On the second question, this is certainly possible, what you need to do is to use the parameter value of P1 to control the dataset that you use for P2. It is likely at the moment that you have fixed lists of numbers against these parameters, but I would suggest changing this to a dataset.

For example, dataset for P1 could be something like:

SELECT 10 AS P1Number

UNION ALL

SELECT 20

UNION ALL

SELECT 30

etc......

So, for your second dataset, you could then bring the value of P1 into play like so:

IF @P1<10 BEGIN

SELECT 10 AS P2Number

UNION ALL

SELECT 20 AS P2Number

UNION ALL

Etc.....

END

IF @P1>9 AND @P1<20 BEGIN

SELECT 20 AS P2Number

UNION ALL

SELECT 30 AS P2Number

UNION ALL

Etc.....

END

Note: the code is perhaps a little untidy, could be easier.

You then use the data from these datasets to populate your parameter.

Good luck,

Nigel West
UK

shnizzle SSC Journeyman Points: 91 More actions · Answer 2

Many thanks. I recall that I tried a similar solution to the second problem but had some syntax problems. I'll try this when I get to work tomorrow.

shnizzle SSC Journeyman Points: 91 More actions · Answer 3

Went over this again - it won't work in my case for two reasons:

less significant reason: Since the user can choose any value between 1 and 100 for P1, doing UNIONs based on IFs on every possible user input is not feasible.

more significant reason: while the above limitation could theoretically be overcome using

SELECT numbers FROM numbers_table
where numbers > @P1

there is still no way in reporting services to re-run the above query when a user changes the value of P1.

nigel.c.west Hall of Fame Points: 3468 More actions · Answer 4

I do understand the issues with your first point, this could be a painful exercise, however, on your second point you should understand that once you use a parameter value for a dataset used to supply a parameter, this will automatically refresh the available values is you change the source parameter.

Nigel West
UK

shnizzle SSC Journeyman Points: 91 More actions · Answer 5

You were correct about the re-querying on parameter change. That indeed solves my problem.

Thanks again.