Frustrating nulls with average

I have a field of values of which any can be null. I can deal with these pretty easily in all cases but the average function. I need to be able to take an average that ignores null values.
So for example if I have 5, 7, null, 5 , 7 I want the average to be 6 not 4.8.You need to count the records where {table.that_field}<>''.

Create a running total, let's say 'count_not_null':

Select field to summarize;
Type of summaries: 'count'.
Evaluate: use a formula: {table.that_field}<>''
Reset it if you need.

Then create a formula Average:

sum({table.field})/{#count_not_null}|||Thanks. I figured it would be something like that...but I just couldn't figure out the angle of attack. Works great.|||Can anyone offer a solution for the same problem except when trying to calculate median?

Frustrated with Parameters Automatically Reloading the Report

Hello.
Here's the scenario: I have two report parameters, both with default values.
As I would expect, the report loads automatically in Preview mode. But why,
when I try to change either parameter value, it seems to automatically reload
the report on simply setting focus to the text box for the other parameter.
I can't seem to stop this from happening, and it actually prevents me from
being able to run the report with any other values than the default values.
Am I missing something?
Thanks,
JohnJohn,
The easiest way to stop it is not to populate parameter 2 with a default and
make sure the parameter does NOT allow blanks.
Normally you can stop the report, change the parameters and click view
report to re-run it.
But remember the 'preview' doesn't necessarily behave the same as when
deployed. I don't use the preview mode much anymore because of this. I find
it better to deploy (or run) to a developement area (use solutions
configuration to switch between Debug and Production to handle this) and then
you get a more accurate picture of it's likely behaviour in the wild.
It's not that much extra hassle to do it this way, if you right click the
report in the solution explorer window you can deploy or deploy & Run just
that report.
Solutions Configurator properties can be accessed by clicking the project
name in the Solution Explorer and selecting properties. You can set different
deployment folders for Debug and Production modes here.
Regards
Chris
"JohnJohn" wrote:
> Hello.
> Here's the scenario: I have two report parameters, both with default values.
> As I would expect, the report loads automatically in Preview mode. But why,
> when I try to change either parameter value, it seems to automatically reload
> the report on simply setting focus to the text box for the other parameter.
> I can't seem to stop this from happening, and it actually prevents me from
> being able to run the report with any other values than the default values.
> Am I missing something?
> Thanks,
> John

Front end display of two fields

I am doing a report which needs to display a field like

totals/percentages...

the actual data values is something like this

=(Sum(Fields!B.Value))& " / " &

(Sum(Fields!B.Value))/(sum(Fields!A.Value) + sum(Fields!B.Value)+sum(Fields!C.Value)+sum(Fields!D.Value)+sum(Fields!E.Value)+sum(Fields!F.Value))

*100

the problem is sum (fields!B.value) is an integer (sum of students) like 32

and the aggregate value in the denominator is a percentage value (0.014343443%)

I need to approximate the value to 0.014

something like this

totals/percentages... .......32/0,014 instead of 32 / 0.014343443 in the front end of the report properties

Please help

Thanks

Wouldn't math.Round() do the trick? You just want to round the decimal to three decimal places....Right?|||

="32" & " / " & round(0.014343443, 3)

Fromatting excel column

Hi,

I have an excel export with numeric values. When the SSIS package writes into the excel it treats all data types as strings and attaches an apostrophe in the beginning. I tried formatting the excel sheet with the number data type and saving it . It doesnt work.

Other strange thing is that when I go into the advanced editor for the excel destination and look into the properties of the external columns all of them have the data type as Unicode String[DT_WSTR] irrespective of what the data type is from the input.The UI allows me to change it to numeric for numeric columns without any error but the value is not saved at all which is very frustrating. I also tried changing it in the xml file of the package, it some how seems to overwrite it after validation. It would be really nice if it threw an error saying that it cannot be changed.

Anyways there seems to be no way of changing the destination data type if it s an existing file and if I create a new excel sheet there is no way of formatting data. Is there any way out?

Thanks

Interesting report on the behavior your seeing. My experience was quite the opposite when working with an Excel import package.

I found the Excel connector to be VERY tightly bound to the defined data types within the actual Excel file. I also found that it seemed to determine the type by the top few rows and not necessarily by the whole data set.|||chandrala's issues are with an Excel destination, not a source though.|||Ya my issue is with destination if I create a new excel file or if I already have an excel file it doesnt matter. It is kind of dumb to expect that the file already has to have some data before the export to determine the type. Most exports are into an unpopulated file. Please let me know how to tackle this.|||I'm having the same issue. Anyone come up with a resolution?|||On the destination connection, I had first row has column names. Set this to no, and everything worked.|||

Grant Mackay wrote:

On the destination connection, I had first row has column names. Set this to no, and everything worked.

This make sense as SSIS try to guess the data type based on the first values of the column. If you have the column names in you first row; then SSIS will treat it as String; which be correct. May that be your problem?

I also found few trheads taking about data conversition when excel is the destination:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=64475&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=193526&SiteID=1

Fromatting excel column

Hi,

I have an excel export with numeric values. When the SSIS package writes into the excel it treats all data types as strings and attaches an apostrophe in the beginning. I tried formatting the excel sheet with the number data type and saving it . It doesnt work.

Other strange thing is that when I go into the advanced editor for the excel destination and look into the properties of the external columns all of them have the data type as Unicode String[DT_WSTR] irrespective of what the data type is from the input.The UI allows me to change it to numeric for numeric columns without any error but the value is not saved at all which is very frustrating. I also tried changing it in the xml file of the package, it some how seems to overwrite it after validation. It would be really nice if it threw an error saying that it cannot be changed.

Anyways there seems to be no way of changing the destination data type if it s an existing file and if I create a new excel sheet there is no way of formatting data. Is there any way out?

Thanks

Interesting report on the behavior your seeing. My experience was quite the opposite when working with an Excel import package.

I found the Excel connector to be VERY tightly bound to the defined data types within the actual Excel file. I also found that it seemed to determine the type by the top few rows and not necessarily by the whole data set.|||chandrala's issues are with an Excel destination, not a source though.|||Ya my issue is with destination if I create a new excel file or if I already have an excel file it doesnt matter. It is kind of dumb to expect that the file already has to have some data before the export to determine the type. Most exports are into an unpopulated file. Please let me know how to tackle this.|||I'm having the same issue. Anyone come up with a resolution?|||On the destination connection, I had first row has column names. Set this to no, and everything worked.|||

Grant Mackay wrote:

On the destination connection, I had first row has column names. Set this to no, and everything worked.

This make sense as SSIS try to guess the data type based on the first values of the column. If you have the column names in you first row; then SSIS will treat it as String; which be correct. May that be your problem?

I also found few trheads taking about data conversition when excel is the destination:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=64475&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=193526&SiteID=1

sql

from view inserts the value of the last inserting at page reload a second time

Hi folks,

After getting a form view inserting some values into a mdb file, it inserts the same values a second time on page reload.

How may I cure this? Any suggestions?

VB Code is below.

many thanks in advance

Rosi

1<%@. Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="km_Eingabe.aspx.vb"Inherits="km_Eingabe" title="km-Eingabe" %>2<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">3 <table style="width: 750px; height: 210px">4 <tr>5 <td style="height: 38px">6 </td>7 <td style="height: 38px">8 <asp:DropDownList ID="dropdownlist1" runat="server" AutoPostBack="True" DataSourceID="AccessDataSource2"9 DataTextField="polKennz" DataValueField="polKennz">10 </asp:DropDownList>11 </td>12 <td style="height: 38px">13 </td>14 </tr>15 <tr>16 <td style="height: 235px">17 </td>18 <td style="height: 235px" valign="top">19   20 <asp:FormView ID="FormView1" runat="server" CellPadding="4" DataSourceID="SqlDataSource1"21 ForeColor="#333333">22 <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />23 <EditRowStyle BackColor="#999999" />24 <EditItemTemplate>2526 </EditItemTemplate>27 <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />28 <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />29 <EmptyDataTemplate>30 keine Daten vorhanden31 <br />32 <asp:LinkButton ID="NewButton" runat="server" CommandName="New" Text="Neuer Eintrag"></asp:LinkButton>33 </EmptyDataTemplate>34 <InsertItemTemplate>35 Datum:36 <asp:TextBox ID="DatumTextBox" runat="server" Text='<%# Bind("Datum", "{0:d}") %>'>37 </asp:TextBox>38 <br />39 Fahrer:40 <asp:TextBox ID="FahrerTextBox" runat="server" Text='<%# Bind("Fahrer") %>'>41 </asp:TextBox>42 <br />43 polKennz:44 <asp:TextBox ID="polKennzTextBox" runat="server" Text='<%# Bind("polKennz") %>'>45 </asp:TextBox>46 <br />47 neuer_Eintrag:48 <asp:TextBox ID="neuer_EintragTextBox" runat="server" Text='<%# Bind("neu") %>'></asp:TextBox>49 <br />50 aktuell:51 <asp:TextBox ID="aktuellTextBox" runat="server" Text='<%# Bind("aktuell") %>'></asp:TextBox> <br />52 <br />53 <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"54 Text="Einfügen" OnClick="InsertButton_Click">55 </asp:LinkButton> 56 <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"57 Text="Abbrechen">58 </asp:LinkButton>59 </InsertItemTemplate>60 <ItemTemplate>61 Datum:62 <asp:Label ID="DatumLabel" runat="server" Text='<%# Bind("Datum") %>'></asp:Label><br />63 Fahrer:64 <asp:Label ID="FahrerLabel" runat="server" Text='<%# Bind("Fahrer") %>'></asp:Label><br />65 polKennz:66 <asp:Label ID="polKennzLabel" runat="server" Text='<%# Bind("polKennz") %>'></asp:Label><br />67 neu:68 <asp:Label ID="neuLabel" runat="server" Text='<%# Bind("neu") %>'></asp:Label><br />69 lfdNr:70 <asp:Label ID="lfdNrLabel" runat="server" Text='<%# Eval("lfdNr") %>'></asp:Label><br />71 aktuell:72 <asp:Label ID="aktuellLabel" runat="server" Text='<%# Bind("aktuell") %>'></asp:Label><br />73 Dienststelle:74 <asp:Label ID="DienststelleLabel" runat="server" Text='<%# Bind("Dienststelle") %>'></asp:Label><br />75 <asp:LinkButton ID="NewButton" runat="server" CommandName="New" Text="Neuer Eintrag"></asp:LinkButton>76 </ItemTemplate>77 <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />78 </asp:FormView>79 </td>80 <td style="height: 235px">81 </td>82 </tr>83 <tr>84 <td>85 </td>86 <td>87 </td>88 <td>89 </td>90 </tr>91 </table>92<asp:AccessDataSource ID="AccessDataSource2" runat="server" DataFile="~/App_Data/KfzDaten_Ansicht.mdb"93 SelectCommand="SELECT DISTINCT [polKennz] FROM [qry_KennzeichenAlle_ohne_ausgesondert]">94 </asp:AccessDataSource>95 <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/KfzDaten_Ansicht.mdb"96 SelectCommand="SELECT DISTINCT [Datum], [Nutzer], [Fahrer], [polKennz], [aktuell], [neu], [gefahren] FROM [qry_Fahrtenbuch_letzter_Eintrag_pro_Kfz] WHERE ([polKennz] = ?)">97 <SelectParameters>98 <asp:ControlParameter ControlID="DropDownList1" Name="polKennz" PropertyName="SelectedValue"99 Type="String" />100 </SelectParameters>101 </asp:AccessDataSource>102 <asp:SqlDataSource ID="SqlDataSource1" DataSourceMode="DataSet" ConflictDetection="CompareAllValues" InsertCommandType="Text" runat="server" ConnectionString="<%$ ConnectionStrings:KfzDaten_Ansicht_mdbConnectionString %>" ProviderName="<%$ ConnectionStrings:KfzDaten_Ansicht_mdbConnectionString.ProviderName %>"103 InsertCommand="INSERT INTO Tab_import_Fahrtenbuch([Datum], [Fahrer], [polKennz], [neu], [aktuell]) VALUES (@.Datum, @.Fahrer, @.polKennz, @.Eingabe_neu, @.Eingabe_aktuell )" >104 <SelectParameters>105 <asp:ControlParameter ControlID="dropdownlist1" Name="newparameter" PropertyName="SelectedValue" />106 </SelectParameters>107 <InsertParameters>108 <asp:FormParameter FormField="DatumTextBox" Name="Datum" Type="string" />109 <asp:FormParameter FormField="FahrerTextBox" Name="Fahrer" Type="string" />110 <asp:FormParameter FormField="polKennzTextBox" Name="polKennz" Type="string" />111 <asp:FormParameter FormField="neuer_EintragTextBox" Name="neu" Type="Int32" ConvertEmptyStringToNull="false" />112 <asp:FormParameter FormField="aktuellTextBox" Name="aktuell" Type="Int32" ConvertEmptyStringToNull="false" Direction="Input" />113 </InsertParameters>114 </asp:SqlDataSource>115     116</asp:Content>117118

Hi Rosi,

It seems that the code has been re-executed when the page re-loads. So you may use "IsPostBack" property to provent the re-execute phenomenon.

If this does not answer your question, pls feel free to reply. Thanks！

|||

HiNai-Dong Jin

Thanks for your answer.

I'm doing my first steps with this stuff. How to use that "IsPostBack"?

Many thanks for any suggestions.

Rosi

|||

Hi Rosi,

Can you show me the km_Eingabe.aspx.vb(code-behind file) ?

Thanks!

|||

RosiRonja:

Hi folks,

After getting a form view inserting some values into a mdb file, it inserts the same values a second time on page reload.

How may I cure this? Any suggestions?

VB Code is below.

many thanks in advance

Rosi

1<%@. Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="km_Eingabe.aspx.vb"Inherits="km_Eingabe" title="km-Eingabe" %>
2<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
3 <table style="width: 750px; height: 210px">
4 <tr>
5 <td style="height: 38px">
6 </td>
7 <td style="height: 38px">
8 <asp:DropDownList ID="dropdownlist1" runat="server" AutoPostBack="True" DataSourceID="AccessDataSource2"
9 DataTextField="polKennz" DataValueField="polKennz">
10 </asp:DropDownList>
11 </td>
12 <td style="height: 38px">
13 </td>
14 </tr>
15 <tr>
16 <td style="height: 235px">
17 </td>
18 <td style="height: 235px" valign="top">
19   
20 <asp:FormView ID="FormView1" runat="server" CellPadding="4" DataSourceID="SqlDataSource1"
21 ForeColor="#333333">
22 <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
23 <EditRowStyle BackColor="#999999" />
24 <EditItemTemplate>
25
26 </EditItemTemplate>
27 <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
28 <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
29 <EmptyDataTemplate>
30 keine Daten vorhanden
31 <br />
32 <asp:LinkButton ID="NewButton" runat="server" CommandName="New" Text="Neuer Eintrag"></asp:LinkButton>
33 </EmptyDataTemplate>
34 <InsertItemTemplate>
35 Datum:
36 <asp:TextBox ID="DatumTextBox" runat="server" Text='<%# Bind("Datum", "{0:d}") %>'>
37 </asp:TextBox>
38 <br />
39 Fahrer:
40 <asp:TextBox ID="FahrerTextBox" runat="server" Text='<%# Bind("Fahrer") %>'>
41 </asp:TextBox>
42 <br />
43 polKennz:
44 <asp:TextBox ID="polKennzTextBox" runat="server" Text='<%# Bind("polKennz") %>'>
45 </asp:TextBox>
46 <br />
47 neuer_Eintrag:
48 <asp:TextBox ID="neuer_EintragTextBox" runat="server" Text='<%# Bind("neu") %>'></asp:TextBox>
49 <br />
50 aktuell:
51 <asp:TextBox ID="aktuellTextBox" runat="server" Text='<%# Bind("aktuell") %>'></asp:TextBox> <br />
52 <br />
53 <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"
54 Text="Einfügen" OnClick="InsertButton_Click">
55 </asp:LinkButton> 
56 <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
57 Text="Abbrechen">
58 </asp:LinkButton>
59 </InsertItemTemplate>
60 <ItemTemplate>
61 Datum:
62 <asp:Label ID="DatumLabel" runat="server" Text='<%# Bind("Datum") %>'></asp:Label><br />
63 Fahrer:
64 <asp:Label ID="FahrerLabel" runat="server" Text='<%# Bind("Fahrer") %>'></asp:Label><br />
65 polKennz:
66 <asp:Label ID="polKennzLabel" runat="server" Text='<%# Bind("polKennz") %>'></asp:Label><br />
67 neu:
68 <asp:Label ID="neuLabel" runat="server" Text='<%# Bind("neu") %>'></asp:Label><br />
69 lfdNr:
70 <asp:Label ID="lfdNrLabel" runat="server" Text='<%# Eval("lfdNr") %>'></asp:Label><br />
71 aktuell:
72 <asp:Label ID="aktuellLabel" runat="server" Text='<%# Bind("aktuell") %>'></asp:Label><br />
73 Dienststelle:
74 <asp:Label ID="DienststelleLabel" runat="server" Text='<%# Bind("Dienststelle") %>'></asp:Label><br />
75 <asp:LinkButton ID="NewButton" runat="server" CommandName="New" Text="Neuer Eintrag"></asp:LinkButton>
76 </ItemTemplate>
77 <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
78 </asp:FormView>
79 </td>
80 <td style="height: 235px">
81 </td>
82 </tr>
83 <tr>
84 <td>
85 </td>
86 <td>
87 </td>
88 <td>
89 </td>
90 </tr>
91 </table>
92<asp:AccessDataSource ID="AccessDataSource2" runat="server" DataFile="~/App_Data/KfzDaten_Ansicht.mdb"
93 SelectCommand="SELECT DISTINCT [polKennz] FROM [qry_KennzeichenAlle_ohne_ausgesondert]">
94 </asp:AccessDataSource>
95 <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/KfzDaten_Ansicht.mdb"
96 SelectCommand="SELECT DISTINCT [Datum], [Nutzer], [Fahrer], [polKennz], [aktuell], [neu], [gefahren] FROM [qry_Fahrtenbuch_letzter_Eintrag_pro_Kfz] WHERE ([polKennz] = ?)">
97 <SelectParameters>
98 <asp:ControlParameter ControlID="DropDownList1" Name="polKennz" PropertyName="SelectedValue"
99 Type="String" />
100 </SelectParameters>
101 </asp:AccessDataSource>
102 <asp:SqlDataSource ID="SqlDataSource1" DataSourceMode="DataSet" ConflictDetection="CompareAllValues" InsertCommandType="Text" runat="server" ConnectionString="<%$ ConnectionStrings:KfzDaten_Ansicht_mdbConnectionString %>" ProviderName="<%$ ConnectionStrings:KfzDaten_Ansicht_mdbConnectionString.ProviderName %>"
103 InsertCommand="INSERT INTO Tab_import_Fahrtenbuch([Datum], [Fahrer], [polKennz], [neu], [aktuell]) VALUES (@.Datum, @.Fahrer, @.polKennz, @.Eingabe_neu, @.Eingabe_aktuell )" >
104 <SelectParameters>
105 <asp:ControlParameter ControlID="dropdownlist1" Name="newparameter" PropertyName="SelectedValue" />
106 </SelectParameters>
107 <InsertParameters>
108 <asp:FormParameter FormField="DatumTextBox" Name="Datum" Type="string" />
109 <asp:FormParameter FormField="FahrerTextBox" Name="Fahrer" Type="string" />
110 <asp:FormParameter FormField="polKennzTextBox" Name="polKennz" Type="string" />
111 <asp:FormParameter FormField="neuer_EintragTextBox" Name="neu" Type="Int32" ConvertEmptyStringToNull="false" />
112 <asp:FormParameter FormField="aktuellTextBox" Name="aktuell" Type="Int32" ConvertEmptyStringToNull="false" Direction="Input" />
113 </InsertParameters>
114 </asp:SqlDataSource>
115     
116</asp:Content>
117
118

try to remove your cache,
<meta http-equiv="Cache-Control" content="no-cache"/>

here is the way to of ispost back

If Page.IsPostBack Then
/// place business logic

else

End If

|||

Hi folks

I did it with Response.Redirect("km_Eingabe.aspx")

It works well that way.

1Protected Sub SqlDataSource1_Inserted(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles SqlDataSource1.Inserted2 Response.Redirect("km_Eingabe.aspx")34End Sub

Thekm_Eingabe.aspx.vb (code behind file)in not more then this and works.

Either way I'll try the  other solutions (cache and postback) too.

What did you think is the best way to do?

Many Thanks

Rosi

sql

French Accent

Using MSDE 2000

I made a script to create my database and insert default values in some tables. The problem is that all the accent are replace by junk ("Unitús instead of Units"). But if I insert values with Server Explorer, ADO.Net or oSql it work fine...

Anyone as an idea on why accent are replace by junk only when insertion are made by a script.

Thanks

What is the collation and system locale when used this script?

I think there used to be problem with French collation in SQL 2000, You either have to support all collations or force your clients to reinstall SQL Server with the collation you chose. It is not quite clear why French data in French_CI_AS collation and Latin1_General_CI_AS collation are incompatible.

|||

I use the collation "SQL_Latin1_General_CP1_CI_AS". I also tried with the collation "French_CI_AS" but I had the same result.

|||

I've just installed Sql Server 2005 Express and I have the same problem. So this is not a Sql Server 2000 issue.

Here's a part of the database creation script:

Code Snippet

-- SQL Manager 2005 Lite for SQL Server (2.4.0.1)
--
-- Host : MyHost
-- Database : MyDatabase
-- Version: : Microsoft SQL Server 9.00.1399.06

DROP DATABASE MyDatabase
GO

CREATE DATABASE [MyDatabase]
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

USE [MyDatabase]
GO

--
-- Structure for table Langues :
--

CREATE TABLE [dbo].[Langues] (
[id] int NOT NULL,
[langue] varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[description] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GO

--
-- Data for table Langues (LIMIT 0,500)
--

INSERT INTO [dbo].[Langues] ([id], [langue], [description])
VALUES
(1, 'En', 'Rules')
GO

INSERT INTO [dbo].[Langues] ([id], [langue], [description])
VALUES
(1, 'Fr', 'Règlementations')
GO

INSERT INTO [dbo].[Langues] ([id], [langue], [description])
VALUES
(2, 'En', 'Repairs')
GO

INSERT INTO [dbo].[Langues] ([id], [langue], [description])
VALUES
(2, 'Fr', 'Rparations')
GO

ALTER TABLE [dbo].[Langues]
ADD CONSTRAINT [PK_Langues]
PRIMARY KEY CLUSTERED ([id], [langue])
WITH (
PAD_INDEX = OFF,
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

-- END OF THE SCRIPT

When I open server explorer to view the data I get

- "Rúparations" instead of "Rparations"

- "RTglementations" instead of "Règlementations"

I haven't figure out why so far...

Thanks

|||

I finally found the problem. It had nothing to do with sql server... my script file was save under the ANSI encoding instead of UNICODE. Dumb problem

Thanks for your time.

fRe: Multi-value parameters

Now do you know how to take multi-value parameter array values and pass to Sql Stored Proc for summing ?

Your multi value parameter gets passed to your stored proc as a comma delimited list of values, so you can use the IN keyword in your WHERE clause, like this:

Code Snippet

SELECT SUM(prices)

FROM Products

WHERE ProductID IN (@.myMultiValuedParameter)

|||

i'm trying to do this but i'm getting an error when the report compiles saying "Conversion failed when converting the nvarchar value '4998,5043' to data type int."

I'm passing in a multivalue parameter called "empno" of value "4998, 5043" and my sql for it is:

SELECT... FROM ... WHERE empno IN (@.empno)

Any ideas?

|||I have not tried it yet personally, but I have read from other posts that Stored procs do not take more than one parameter from Reporting Services...Is this true...?