Parameters: How to use them exactly

[Brix]

Hi,

I have been browsing the forum since the beginning of this morning, trying to find examples on how to implement parameters in the correct way.

What's troubling me is how I'm going to code my project with these ideas.
Sometimes the parameters won't be used and all the data should be visible. On the other hand, there are the parameters depending on the request.
One person would like to view all data from just one place, for example, all persons from one city. And there is another who would like to see how all the men or women in the report.

For this only I would need 2 parameters. But what if 1 parameter does not get set. In the report designer (windows tool) I get an exception because a parameter is not set.

Example:

I have a query: SELECT * FROM persons WHERE Gender = @Gender AND City = @City

Now I would like to see only the persons in a specific city.. But the report requires 2 parameters. I prefer to work with named parameters.

I hope you understand what my intentions are.

Thank you in advance.

[Jan]

Hello,

First variant:

You need change your query. For example you can do it with help of variable in query. For example:

Code: Select all

SELECT *
FROM CUSTOMERS
WHERE {MyVariable}

You need create MyVariable in report dictionary. Type is string. Before report rendering you need initialize it:

Code: Select all

report.Compile();
report["MyVariable"] = "CUSTOMERID + 123"
report.Show();

or

Code: Select all

report.Compile();
report["MyVariable"] = "CUSTOMERID = @CustomerID"
report.Show();

Second variant:

You can change query from code before report rendering:

Code: Select all

Stimulsoft.Report.Dictionary.StiSqlSource source = report.Dictionary.DataSources["MySqlSource"] as Stimulsoft.Report.Dictionary.StiSqlSource;
            source.SqlCommand = "select * from customers";
            report.Render();

Thank you.

[Brix]

Hi,

Thanks for the reply, but it doesn't answer my question just yet.

What if I have 2 sets of variables, one for each column type.

for example:

Code: Select all

SELECT * FROM customers
WHERE {variable1} AND {variable2}]

Or can I use the whole {variable1} for the entire WHERE clause?

[Ivan]

Hello,

Other variant. Please use the following query, for example:

Code: Select all

SELECT * FROM CUSTOMERS {MyVariable}

Create MyVariable in report dictionary. Type is string. Before report rendering you need initialize it:

Code: Select all

            StringBuilder querry = new StringBuilder();
            if (use_param_1 || use_param_2)
            {
                querry.Append("where ");
                if (use_param_1)
                {
                    querry.Append("{variable1}");
                }
                if (use_param_1 && use_param_2)
                {
                    querry.Append(" and ");
                }
                if (use_param_2)
                {
                    querry.Append("{variable2}");
                }
            }
            report.Dictionary.Variables["MyVariable"] = querry.ToString();
            report.Compile();
            report.Show();

Thank you.

[Pio Leonardo V. Rapirap]

Hi,

Thanks for the reply, but it doesn't answer my question just yet.

What if I have 2 sets of variables, one for each column type.

for example:

Code: Select all

SELECT * FROM customers
WHERE {variable1} AND {variable2}]

Or can I use the whole {variable1} for the entire WHERE clause?

you can also try this:
Query

Code: Select all

SELECT * FROM Customers
WHERE {vWhereClause}

Create vWhereClause in report dictionary of type String and put the following before report rendering:

Code: Select all

 vWhereClause = " Region =  ' " + vStringRegion + " ' " + " AND " + " City = ' " + vStringCity + " ' ";

or add conditions:

Code: Select all

if (pCondition == 1){
vWhereClause = " Region =  ' " + vStringRegion + " ' " + " AND " + " City = ' " + vStringCity + " ' ";
}else{
vWhereClause = " PostalCode = ' " + vStringPostalCode + " ' " + " OR PostalCode IS NULL ";
}

Hope this helps!

[Ivan]

Hello,

Let us know if you need any additional help.

Thank you.