New post
Question
· Aug 25, 2021

Writing simple SQL code within BIDS / SSRS / Visual Studio (issue with parameters)

#InterSystems IRIS

Hi,

I wonder if anyone could help me.

I'm used to writing Microsoft SQL database queries, but at the moment I am also trying to write reports on an IRIS database (used to be cache) in Microsoft BIDS.

My chief headache is parameters. They don't behave the "normal way" in BIDS, in that you can't rename them. Therefore, referencing them in the main code is a nightmare for me.

Say my report has two parameters. If i want to refer to both in a dataset, I have to list them in the order they are in the report and both by using a question mark, ie:

WHERE
column1 = ?

and column 2 = ?

 

But if I only want to refer to the second one in a dataset, I can't figure out how to do this?

 

Can anyone help please? It's driving me crazy!

 

Thanks.

Discussion (4)0
Log in or sign up to continue
Robert Cemper · Aug 25, 2021

to rephrase your problem:
- You look for a way to switch on/off  column 1
so you need a switch:  >>> new parameter 
and use it like this:

... WHERE (0=? OR  column1=?) AND column2=?

if your first param is 0   then 2nd param (for column1) is irrelevant 
if your first param is 1   then 2nd param (for column1) is effective 

so execute(0,a,b)   uses any value of column1
whille execute(1,a,b) filters column1 and column2

0 0
Andrew Barber · Aug 25, 2021

Many thanks, this will give me some weapons in my arsenal! ANd thanks for the quick reply also.

But what about if I'm trying to cascade parameters? Parameter one influences dataset one. Then I have a parameter two which is populated by the results of dataset 1. This parameter is used in dataset two, but the field relating to parameter one doesn't exist in dataset two.

Does that make sense? Basically, in your example, pretend column one doesn't exist as it relates to a different dataset. So I have multiple parameters, some of which are only able to be used on certain datasets.

Thanks.

0 0
Robert Cemper · Aug 25, 2021

You might be able to achieve the first part of your description by some tricky JOIN constructs.
Just access to a non-existing column will fail at generating the Query.
Cascading might be easier (also for maintenance)  by filling a TempTable over several cycles
and so assemble the final result.

I would suggest to create a ClassMethod projected to SQL as Stored Procedure.
then instead of the trick with the switch you just provide the parameters that you really have

0 0
Andrew Barber · Aug 25, 2021

Many thanks for your reply.

That all sounds a bit beyond me! I know I've tried making a temp table in SSRS on the IRIS database but it didn't like it at all (giving vague errors), so gave up on that idea.
I don't know what a classmethod is or how to create it.

It seems very strange to me that you simply can't simply reference a specific parameter whilst ignoring others. I don't doubt it, however!!

Regards,

0 0