encoding problem

Hi
I use Oracle data source. The report is in Polish. When I try display some text from database or use report parameters, this text is not written correctly. The environment on the machine, where is installed Reporting Services is rather correct, when I am using SQL*PLUS it is ok. I linked Oracle database to MSSQL, I used Query Analyzer, the text was written correct in Polish. What to do?
morganWhat is the report language set to?
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"morgan" <morgan@.discussions.microsoft.com> wrote in message
news:9E40E53A-C040-470F-89E2-0E9807BA98D7@.microsoft.com...
> Hi
> I use Oracle data source. The report is in Polish. When I try display some
text from database or use report parameters, this text is not written
correctly. The environment on the machine, where is installed Reporting
Services is rather correct, when I am using SQL*PLUS it is ok. I linked
Oracle database to MSSQL, I used Query Analyzer, the text was written
correct in Polish. What to do?
> morgan|||Polish is the report language. By I used English ( US) too.
morgan
"Ravi Mumulla (Microsoft)" wrote:
> What is the report language set to?
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "morgan" <morgan@.discussions.microsoft.com> wrote in message
> news:9E40E53A-C040-470F-89E2-0E9807BA98D7@.microsoft.com...
> > Hi
> >
> > I use Oracle data source. The report is in Polish. When I try display some
> text from database or use report parameters, this text is not written
> correctly. The environment on the machine, where is installed Reporting
> Services is rather correct, when I am using SQL*PLUS it is ok. I linked
> Oracle database to MSSQL, I used Query Analyzer, the text was written
> correct in Polish. What to do?
> >
> > morgan
>
>

Enabling "Auto create statistics" / "Auto update statistics" at the tempdb databas

Should I enable the "Auto create statistics" and "Auto update statistics" parameters at the tempdb database? I've heard that it's necessary to increase tempdb performance. Could you confirm if it's true?
Thanks in advance.
VagnerStatistics is very important for performance. SQL Server uses this info when
it decides whther to use an index or not. You should have the auto
parameters set to on in all databases. Well, if you are not using tempdb a
lot, then it is not so important for tempdb, but I would set them on anyway.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Vagner Cuccino" <anonymous@.discussions.microsoft.com> wrote in message
news:A324064F-3E90-4E6D-B851-3AE1B3F7825A@.microsoft.com...
> Should I enable the "Auto create statistics" and "Auto update statistics"
parameters at the tempdb database? I've heard that it's necessary to
increase tempdb performance. Could you confirm if it's true?
> Thanks in advance.
>
> Vagner
>|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OJjT2l%23vDHA.2304@.TK2MSFTNGP12.phx.gbl...
> Statistics is very important for performance. SQL Server uses this info
when
> it decides whther to use an index or not. You should have the auto
> parameters set to on in all databases. Well, if you are not using tempdb a
> lot, then it is not so important for tempdb, but I would set them on
anyway.
I would agree with you for new systems. For older systems previously based
and tweaked on earlier SQL Server versions like 6.5, especially real time
OLTP apps, I would suggest not unless you're in a position to open the code
up and re-tweak the app.
In general, auto create and update statistics will make older tweaked apps
run worse than before!
But bear in mind for both new and legacy applications, the serious
consideration that auto create and update stats don't do full scans, which
for anything more than fairly trivial amounts of data can be a significant
performance problem. The optimiser may choose a non-optimum execution plan
as it is basing its decision on a potentially non-representative sample of
data. You need to understand your data's distribution and cardinality to
make a call on this. In addition often there is some mutuality between
distinct column values in a table, for example if you have status flags or
values. In these cases it is often well worth manually creating composite
statistics to help the optimiser understand this.
Kind Regards, Howard

Enable parameter selection

I have two reports. On Report1, I have a hyperlink action that brings up
Report2, passing the required parameters it needs. But when Report2 is shown,
I don't have the ability to change the parameters, E.g. in Report2, I have a
parameter in a list with a Show\Hide options. When getting redirected from
Report1, the default is set to hide. When I'm in Report 2, I then want to
change it to Show, but the parameter "section" is not on the report header.
Looking at the link, I can see the following
...%3d0%26rc%253aParameters%3dFalse%26rc%253aReplacementRoot%3dhttp...When I
change the False to True, I can then see the parameter area, but I'm not sure
how to enable it in the report. Can't seem to find the property I need to
change.Use jump to URL instead of jump to report and you can set this the way you
want.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Alex" <alex_dinu@.adp.com.(nospam)> wrote in message
news:EFDE9B94-308C-4050-9214-F7B9913F3659@.microsoft.com...
>I have two reports. On Report1, I have a hyperlink action that brings up
> Report2, passing the required parameters it needs. But when Report2 is
> shown,
> I don't have the ability to change the parameters, E.g. in Report2, I have
> a
> parameter in a list with a Show\Hide options. When getting redirected from
> Report1, the default is set to hide. When I'm in Report 2, I then want to
> change it to Show, but the parameter "section" is not on the report
> header.
> Looking at the link, I can see the following
> ...%3d0%26rc%253aParameters%3dFalse%26rc%253aReplacementRoot%3dhttp...When
> I
> change the False to True, I can then see the parameter area, but I'm not
> sure
> how to enable it in the report. Can't seem to find the property I need to
> change.

Empty Recordset

New to Reporting Services.
I have written a stored procedure which I call from a report built with RS.
Certain parameters being passed to the procedure does not materialize a
record set, therefore, the report comes up blank, with no labels etc. What I
need to do is detect an empty record set, from within the report, and set
default values so the report does not appear blank. I have tried the
IsNothing function within each text boxes expressions, however, it does not
work. Has anyone run into this and if so, would you be so kind to provide a
solution?
Regards,
Fred
Regards,
FredHi,
maybe you can use the NoRows property of your table to display the desired
information?
"FredG" <FredG@.discussions.microsoft.com> schrieb im Newsbeitrag
news:A5EDE86A-C658-4FF7-B0B1-F4CD33F196FF@.microsoft.com...
> New to Reporting Services.
> I have written a stored procedure which I call from a report built with
> RS.
> Certain parameters being passed to the procedure does not materialize a
> record set, therefore, the report comes up blank, with no labels etc. What
> I
> need to do is detect an empty record set, from within the report, and set
> default values so the report does not appear blank. I have tried the
> IsNothing function within each text boxes expressions, however, it does
> not
> work. Has anyone run into this and if so, would you be so kind to provide
> a
> solution?
> Regards,
> Fred
>
> Regards,
> Fred|||Hello Ralph,
Thank you for responding. I am not familiar with the NoRows property, do you
have an example? I tried searching the RS BOL but came up empty.
"Ralph Watermann" wrote:
> Hi,
> maybe you can use the NoRows property of your table to display the desired
> information?
> "FredG" <FredG@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:A5EDE86A-C658-4FF7-B0B1-F4CD33F196FF@.microsoft.com...
> > New to Reporting Services.
> > I have written a stored procedure which I call from a report built with
> > RS.
> > Certain parameters being passed to the procedure does not materialize a
> > record set, therefore, the report comes up blank, with no labels etc. What
> > I
> > need to do is detect an empty record set, from within the report, and set
> > default values so the report does not appear blank. I have tried the
> > IsNothing function within each text boxes expressions, however, it does
> > not
> > work. Has anyone run into this and if so, would you be so kind to provide
> > a
> > solution?
> >
> > Regards,
> > Fred
> >
> >
> > Regards,
> > Fred
>
>|||Hi Fred,
You could modify your stored procedure to test whether or not you
retrieve records depending on the parameters you pass. If the query in your
stored procedure does not return records, then you can pass a single record
with the default values in a Select statement, that you want to show in the
report...
Cheers,
Daniel.
"FredG" wrote:
> Hello Ralph,
> Thank you for responding. I am not familiar with the NoRows property, do you
> have an example? I tried searching the RS BOL but came up empty.
> "Ralph Watermann" wrote:
> > Hi,
> >
> > maybe you can use the NoRows property of your table to display the desired
> > information?
> >
> > "FredG" <FredG@.discussions.microsoft.com> schrieb im Newsbeitrag
> > news:A5EDE86A-C658-4FF7-B0B1-F4CD33F196FF@.microsoft.com...
> > > New to Reporting Services.
> > > I have written a stored procedure which I call from a report built with
> > > RS.
> > > Certain parameters being passed to the procedure does not materialize a
> > > record set, therefore, the report comes up blank, with no labels etc. What
> > > I
> > > need to do is detect an empty record set, from within the report, and set
> > > default values so the report does not appear blank. I have tried the
> > > IsNothing function within each text boxes expressions, however, it does
> > > not
> > > work. Has anyone run into this and if so, would you be so kind to provide
> > > a
> > > solution?
> > >
> > > Regards,
> > > Fred
> > >
> > >
> > > Regards,
> > > Fred
> >
> >
> >|||"NoRows" is an element that is a part of the "Properties" for a table. You
can specify a value in the table's "Properties" (under "Data") for the case
when there is no row to display.
"daniel_xi" wrote:
> Hi Fred,
> You could modify your stored procedure to test whether or not you
> retrieve records depending on the parameters you pass. If the query in your
> stored procedure does not return records, then you can pass a single record
> with the default values in a Select statement, that you want to show in the
> report...
> Cheers,
> Daniel.
>
> "FredG" wrote:
> > Hello Ralph,
> > Thank you for responding. I am not familiar with the NoRows property, do you
> > have an example? I tried searching the RS BOL but came up empty.
> >
> > "Ralph Watermann" wrote:
> >
> > > Hi,
> > >
> > > maybe you can use the NoRows property of your table to display the desired
> > > information?
> > >
> > > "FredG" <FredG@.discussions.microsoft.com> schrieb im Newsbeitrag
> > > news:A5EDE86A-C658-4FF7-B0B1-F4CD33F196FF@.microsoft.com...
> > > > New to Reporting Services.
> > > > I have written a stored procedure which I call from a report built with
> > > > RS.
> > > > Certain parameters being passed to the procedure does not materialize a
> > > > record set, therefore, the report comes up blank, with no labels etc. What
> > > > I
> > > > need to do is detect an empty record set, from within the report, and set
> > > > default values so the report does not appear blank. I have tried the
> > > > IsNothing function within each text boxes expressions, however, it does
> > > > not
> > > > work. Has anyone run into this and if so, would you be so kind to provide
> > > > a
> > > > solution?
> > > >
> > > > Regards,
> > > > Fred
> > > >
> > > >
> > > > Regards,
> > > > Fred
> > >
> > >
> > >|||Hello Daniel,
That is exactly what I did to solve the problem. Thanks
"daniel_xi" wrote:
> Hi Fred,
> You could modify your stored procedure to test whether or not you
> retrieve records depending on the parameters you pass. If the query in your
> stored procedure does not return records, then you can pass a single record
> with the default values in a Select statement, that you want to show in the
> report...
> Cheers,
> Daniel.
>
> "FredG" wrote:
> > Hello Ralph,
> > Thank you for responding. I am not familiar with the NoRows property, do you
> > have an example? I tried searching the RS BOL but came up empty.
> >
> > "Ralph Watermann" wrote:
> >
> > > Hi,
> > >
> > > maybe you can use the NoRows property of your table to display the desired
> > > information?
> > >
> > > "FredG" <FredG@.discussions.microsoft.com> schrieb im Newsbeitrag
> > > news:A5EDE86A-C658-4FF7-B0B1-F4CD33F196FF@.microsoft.com...
> > > > New to Reporting Services.
> > > > I have written a stored procedure which I call from a report built with
> > > > RS.
> > > > Certain parameters being passed to the procedure does not materialize a
> > > > record set, therefore, the report comes up blank, with no labels etc. What
> > > > I
> > > > need to do is detect an empty record set, from within the report, and set
> > > > default values so the report does not appear blank. I have tried the
> > > > IsNothing function within each text boxes expressions, however, it does
> > > > not
> > > > work. Has anyone run into this and if so, would you be so kind to provide
> > > > a
> > > > solution?
> > > >
> > > > Regards,
> > > > Fred
> > > >
> > > >
> > > > Regards,
> > > > Fred
> > >
> > >
> > >

Empty multi-value parameter

Several people have asked about this, but I have not seen a response that works. I have several parameters that I want to use as filters. If a particular filter parameter is not entered, the report should not apply a filter on that criterion. It works fine to create a single value parameter which allows null, and use the following in SQL:

WHERE ( [Field1] = @.Field1_Filter OR @.Field1_Filter IS NULL )

How can the same effect be achieved for a multi-value parameter? If you try to create a multi-value parameter which allows null, the report builder gives an error. I can't use "allow blank value" because some of my parameters are integer parameters. Without the allow null, the report generator requires me to enter at least one value. So I don't know how you can get a value of "not entered" into a multi-value parameter.

I also don't know how you would check for the "not entered". Someone suggested the following SQL:

WHERE ( [Field1] IN (@.Field1_Filter) OR @.Field1_Filter IS NULL )

This generates an error when generating the report if multiple values are entered for the parameter (the normal condition). The error is " An expression of non-boolean type specified in a context where a condition is expected, near ',' ".

How is this supposed to be done?

Thanks for any insight

Would the following work?

WHERE (

[Field1] in (@.Field1_Filter)

OR (select count(*) from table where [Field1] in (@.Field1_Filter)) = 0

)

Or if that's too expensive, you might add an extra query parameter and pass in the following expression:

=Parameters!Field1_Filter.Value.Count

Then your SQL could read:

WHERE ( [Field1] IN (@.Field1_Filter) OR @.Field1_FilterCount = 0 )

|||

Dont use the filter in the SQL query. Instead keep your SQL query as a simple select statement and implement the filter in the dataset Filter using expressions like this:

For example, if your SQL query is "SELECT * FROM Table 1 WHERE ( [Field1] IN (@.Field1_Filter) OR @.Field1_Filter IS NULL )", change it to "SELECT * FROM Table1"

Now edit the dataset and go to Filter tab.

Under expression type this:

=IIf(Parameters!Field1_Filter.Value <> Nothing and Parameters!Field1_Filter.Count > 0, Fields!Field1.Value, 1)

Under Operator select "IN"

Under Value type this:

=IIf(Parameters!Field1_Filter.Value <> Nothing and Parameters!Field1_Filter.Count > 0, Parameters!Field1_Filter.Value, 1)

This way, when the filter parameter is null/nothing or count = 0, 1=1 will be evaluated which means no filter condition is applied.

Shyam

|||Thanks for the suggestions.

furmangg:

Your first suggestion wouldn't work because it would return everything anytime the filter didn't match, not just when the filter is empty.

I didn't think about using the count of the multivalue parameter. I think it's an array, so that could work either in the query (using another query parameter) or in a report filter. But how do I get an empty array in there? I'm using integer parameters. I can't set the parameter to allow null, and when I preview the report and leave the parameter blank, it tells me to enter at least one value. I tried it with string parameters, and the report just doesn't run in preview if you don't enter any values, even if you choose "allow blank".

How do you specify "empty" for a multivalue parameter?
|||Anyone have any ideas? Can someone at least confirm that this is a problem and not some screwy setup on my machine?
|||

If you set allow null or allow blank for your multi-valued parameter, one of those values must be null or blank. For example, if your parameter is getting its values from a query, make the query to return one more row with blank value or null value. Or if your list of values is non-queried, add another value to the list with blank or null value.

Shyam

|||Has anyone found a solution for this as I am having the same problem.

I have a multi-value parameter (Dim1Code) that is populated from a query:

SELECT
Dim1Code,
Dim1Code + ', ' + Description AS Description
FROM
Dim1

For some clients the query returns no data (which is fine) and so the drop down list is empty.

I have another parameter (EmployeeList) based on the following query:

SELECT
E.EmployeeCode,
E.EmployeeCode + ' - ' + E.Lastname + ', ' + E.Firstnames AS Name
FROM
Employee E
WHERE
Dim1Code IN (@.Dim1Code)

If the Dim1Code list is empty then it should return all employees, and I have not been able to find a way to get this working.

I have tried adding a union statement to the first query to also return a null row, but it wont let me set the "Allow Null" property on my parameter (I get an error as this is an invalid option for multi-value parameters).

If I set "Allow Blank" for my parameter and then run the report I am told that I need to select a value for the Dim1Code parameter.

If I union my first select with a hard coded value like "<None>" and change my second select statement to:

WHERE
Dim1Code IN (@.Dim1Code) OR @.Dim1Code = '<None>'

I then get an error when I try to run the report:

"An expression of non-boolean type specified in a context where a condition is expected, near ','"

I have tried the filtering options suggested further up in this thread and none of them worked for me either.

Is there really no way of having a valid "None" or "Blank" or "Null" option for multi-value parameters?

Why are multi-value parameters ALWAYS required to have a value?
|||

How about the following for the parameter query:

SELECT
Dim1Code,
Dim1Code + ', ' + Description AS Description
FROM
Dim1

union all

SELECT
'' as Dim1Code,
'-All Codes-' AS Description

WHERE (select count(*) from Dim1) = 0

Then the main query would read:

SELECT
E.EmployeeCode,
E.EmployeeCode + ' - ' + E.Lastname + ', ' + E.Firstnames AS Name
FROM
Employee E
WHERE
Dim1Code IN (@.Dim1Code)

OR '' in (@.Dim1Code)

Empty multi-value parameter

Several people have asked about this, but I have not seen a response that works. I have several parameters that I want to use as filters. If a particular filter parameter is not entered, the report should not apply a filter on that criterion. It works fine to create a single value parameter which allows null, and use the following in SQL:

WHERE ( [Field1] = @.Field1_Filter OR @.Field1_Filter IS NULL )

How can the same effect be achieved for a multi-value parameter? If you try to create a multi-value parameter which allows null, the report builder gives an error. I can't use "allow blank value" because some of my parameters are integer parameters. Without the allow null, the report generator requires me to enter at least one value. So I don't know how you can get a value of "not entered" into a multi-value parameter.

I also don't know how you would check for the "not entered". Someone suggested the following SQL:

WHERE ( [Field1] IN (@.Field1_Filter) OR @.Field1_Filter IS NULL )

This generates an error when generating the report if multiple values are entered for the parameter (the normal condition). The error is " An expression of non-boolean type specified in a context where a condition is expected, near ',' ".

How is this supposed to be done?

Thanks for any insight

Would the following work?

WHERE (

[Field1] in (@.Field1_Filter)

OR (select count(*) from table where [Field1] in (@.Field1_Filter)) = 0

)

Or if that's too expensive, you might add an extra query parameter and pass in the following expression:

=Parameters!Field1_Filter.Value.Count

Then your SQL could read:

WHERE ( [Field1] IN (@.Field1_Filter) OR @.Field1_FilterCount = 0 )

|||

Dont use the filter in the SQL query. Instead keep your SQL query as a simple select statement and implement the filter in the dataset Filter using expressions like this:

For example, if your SQL query is "SELECT * FROM Table 1 WHERE ( [Field1] IN (@.Field1_Filter) OR @.Field1_Filter IS NULL )", change it to "SELECT * FROM Table1"

Now edit the dataset and go to Filter tab.

Under expression type this:

=IIf(Parameters!Field1_Filter.Value <> Nothing and Parameters!Field1_Filter.Count > 0, Fields!Field1.Value, 1)

Under Operator select "IN"

Under Value type this:

=IIf(Parameters!Field1_Filter.Value <> Nothing and Parameters!Field1_Filter.Count > 0, Parameters!Field1_Filter.Value, 1)

This way, when the filter parameter is null/nothing or count = 0, 1=1 will be evaluated which means no filter condition is applied.

Shyam

|||Thanks for the suggestions.

furmangg:

Your first suggestion wouldn't work because it would return everything anytime the filter didn't match, not just when the filter is empty.

I didn't think about using the count of the multivalue parameter. I think it's an array, so that could work either in the query (using another query parameter) or in a report filter. But how do I get an empty array in there? I'm using integer parameters. I can't set the parameter to allow null, and when I preview the report and leave the parameter blank, it tells me to enter at least one value. I tried it with string parameters, and the report just doesn't run in preview if you don't enter any values, even if you choose "allow blank".

How do you specify "empty" for a multivalue parameter?
|||Anyone have any ideas? Can someone at least confirm that this is a problem and not some screwy setup on my machine?
|||

If you set allow null or allow blank for your multi-valued parameter, one of those values must be null or blank. For example, if your parameter is getting its values from a query, make the query to return one more row with blank value or null value. Or if your list of values is non-queried, add another value to the list with blank or null value.

Shyam

|||Has anyone found a solution for this as I am having the same problem.

I have a multi-value parameter (Dim1Code) that is populated from a query:

SELECT
Dim1Code,
Dim1Code + ', ' + Description AS Description
FROM
Dim1

For some clients the query returns no data (which is fine) and so the drop down list is empty.

I have another parameter (EmployeeList) based on the following query:

SELECT
E.EmployeeCode,
E.EmployeeCode + ' - ' + E.Lastname + ', ' + E.Firstnames AS Name
FROM
Employee E
WHERE
Dim1Code IN (@.Dim1Code)

If the Dim1Code list is empty then it should return all employees, and I have not been able to find a way to get this working.

I have tried adding a union statement to the first query to also return a null row, but it wont let me set the "Allow Null" property on my parameter (I get an error as this is an invalid option for multi-value parameters).

If I set "Allow Blank" for my parameter and then run the report I am told that I need to select a value for the Dim1Code parameter.

If I union my first select with a hard coded value like "<None>" and change my second select statement to:

WHERE
Dim1Code IN (@.Dim1Code) OR @.Dim1Code = '<None>'

I then get an error when I try to run the report:

"An expression of non-boolean type specified in a context where a condition is expected, near ','"

I have tried the filtering options suggested further up in this thread and none of them worked for me either.

Is there really no way of having a valid "None" or "Blank" or "Null" option for multi-value parameters?

Why are multi-value parameters ALWAYS required to have a value?
|||

How about the following for the parameter query:

SELECT
Dim1Code,
Dim1Code + ', ' + Description AS Description
FROM
Dim1

union all

SELECT
'' as Dim1Code,
'-All Codes-' AS Description

WHERE (select count(*) from Dim1) = 0

Then the main query would read:

SELECT
E.EmployeeCode,
E.EmployeeCode + ' - ' + E.Lastname + ', ' + E.Firstnames AS Name
FROM
Employee E
WHERE
Dim1Code IN (@.Dim1Code)

OR '' in (@.Dim1Code)