CODEWITHSUNDEEP
.netreporting-servicesssrs-2012
Andy K
Andy K

Reputation: 5044

SSRS/.net setup a dynamic where clause in the dataset

updated version

enter image description here

Above is one of my dataset

My aim is to have the WHERE part , dynamic e.g. to appear or not appear, depending on what is generated by the .net code

Results would be like this

FROM table_name %string%

Within %string%, there can conditions like this one

SELECT Ent_SE_Devise,EntId_CodeJur_Code,Ent_SE_APE_ICS,Ets_Co_NonEnvoi,Ets_Code_Retour2 FROM TableV_EtablissementsListeActifs WHERE cdt1 = 'test1' OR cdt2 LIKE '%somosa%'

Or condition like this one

SELECT Ent_SE_Devise,EntId_CodeJur_Code,Ent_SE_APE_ICS,Ets_Co_NonEnvoi,Ets_Code_Retour2 FROM EtablissementsListeActifs WHERE (cdt1 IS NOT NULL AND cdt2 <> 'Yes') OR cdt3 IS NOT NULL

Or no condition at all

SELECT Ent_SE_Devise,EntId_CodeJur_Code,Ent_SE_APE_ICS,Ets_Co_NonEnvoi,Ets_Code_Retour2 FROM EtablissementsListeActifs

I created a parameter @Iu_Ets in which, .net code put strings like this

SELECT Ent_SE_Devise,EntId_CodeJur_Code,Ent_SE_APE_ICS,Ets_Co_NonEnvoi,Ets_Code_Retour2 FROM EtablissementsListeActifs WHERE cdt1 IS NOT NULL AND col1 IN @Iu_Ets

It is not doing the trick

I have workaround now but to Anup Aggrawal, you said it is possible. Does what I've put in the question makes sense?

Upvotes: 1

Views: 765

Answers (2)

Mike Honey
Mike Honey

Reputation: 15017

I would add a parameter to the report (not the dataset) called "Criteria_String". The default would be an single space (no criteria). You can pass WHERE clauses in to that parameter from .net, URL or wherever for your other scenarios.

Then in the Dataset Properties window, I would copy your existing SELECT code (not including the WHERE clause), click the fx button and enter the following:

= "SELECT ... <existing SELECT statement> " & Parameters!Criteria_String.Value

Upvotes: 2

Ken Clubok
Ken Clubok

Reputation: 1238

I believe that the reason you haven't received an answer to your satisfaction is not that SSRS is rarely used, but rather that your question isn't specific enough to give a more specific answer, and a full tutorial on expressions is a much bigger topic than can be addressed here. Nonetheless, I'll try to help.

The idea of the expression is that you can build up a string programmatically, from lots of parts. For example, an overly simple expression might be ="SELECT * " + "FROM MYTABLE", which simply combines two strings, so that it evaluates to the query SELECT * FROM MYTABLE.

That example, of course, isn't particularly useful. The power of expressions comes from the number of provided building blocks such as functions and operators, and the vast number of ways those can be combined to accomplish what you want. To assist you, a list of those functions and operators is provided at the bottom of the expression dialog box that appears when you click the Fx button.

Without knowing what you are trying to accomplish, I and the other respondents here cannot give you much more guidance than this. But I suggest you review the MSDN documentation on expressions. In particular, familiarize yourself with the IIF function, which is a good way to include conditional text in your query. Also, the answer to this Stack Overflow question contains a nice discussion of expression-based vs. editor-based queries, and also includes another example of an expression-based query.

All of this might still be difficult for you if you do not have a programming background. If that is the case, then you should consider enlisting the help of somebody who does, rather than trying to learn how to program expressions yourself.

Upvotes: 0

Related Questions