losing some results

i have created a report that fits the layout to achieve the fields that i
require, i then created an aspx page where my users can select any number of
fields and values to use in the where clause of the sql statement. My aspx
page then builds an sql statement based on these selections and passes this
sql statement to the report as a parameter. The report calls a stored
procedure that executes the sql statement passed in. This works great for
all but one situation that i have found. When a user enters '%bel%' to use
in the where clause for some reason when it gets to reporting services
report the sql statement is modified to 'l%'. Dropping the '%be'. Is
'%be' a reserved command.
example:
if my table had the following entries in a column name city Boston,
Belville,New York,Detroit,Los Angeles, Lakeville
my user wants to find all cities that have 'bel' in the name
the resulting sql would be select city from table where city like '%bel%'
i setup up my report to show the parameters when the aspx page redirects to
the report using the url of the report
the sql that shows up in the parameter field is select city from table where
city like 'l%'
Any help would be appreciated.
Thank youSolved my own problem. what i had to do was replace all my '%' to '%25' to
encode my url before i issued a response.redirect.
"Mike" <mike.no.spam.please@.no.spam.com> wrote in message
news:u2LsSw6tEHA.1596@.TK2MSFTNGP10.phx.gbl...
>i have created a report that fits the layout to achieve the fields that i
>require, i then created an aspx page where my users can select any number
>of fields and values to use in the where clause of the sql statement. My
>aspx page then builds an sql statement based on these selections and passes
>this sql statement to the report as a parameter. The report calls a stored
>procedure that executes the sql statement passed in. This works great for
>all but one situation that i have found. When a user enters '%bel%' to use
>in the where clause for some reason when it gets to reporting services
>report the sql statement is modified to 'l%'. Dropping the '%be'. Is
>'%be' a reserved command.
> example:
> if my table had the following entries in a column name city Boston,
> Belville,New York,Detroit,Los Angeles, Lakeville
> my user wants to find all cities that have 'bel' in the name
> the resulting sql would be select city from table where city like '%bel%'
>
> i setup up my report to show the parameters when the aspx page redirects
> to the report using the url of the report
> the sql that shows up in the parameter field is select city from table
> where city like 'l%'
> Any help would be appreciated.
> Thank you
>

Losing my parameters and fields?

Dear MSDN!
I have designed a report in VS 2005 Report Server Projekt. The report
connects to a Analysis 2005 server. Everything works fine in Layout tab and
preview tab until i click data tab. After i have clicked the data tab the
report starts to show errors in the output and error list window:
[rsFieldReference] The Value expression for the textbox
â'Sales_Price_Gross_Incl_Discount_1â' refers to the field
â'Sales_Price_Gross_Incl_Discountâ'. Report item expressions can only refer to
fields within the current data set scope or, if inside an aggregate, the
specified data set scope.
Visual studio have automatically checked out the report and changed the xml,
deleted the parameters and field definitions.
What is the problem?
Thanks in advance!Hello Grundh,
I found some similar issue in our internal database. But I did not found
the solution yet.
I am performing research on this issue and I appreciate your patience.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Losing my margin!?

I am designing a report to output address labels (Avery 5160) and the
spacing of the data in the columns and the rows descrease as the print moves
down the page. By the final row of labels, the name line is in the row
preceeding row. I am using a list object and the margin settings are per
Avery's spec sheet.
Anyone else experience this and have a fix?
Thanks,
AndyWhat rendering output are you using? My guess is you'll have your best luck
with PDF or TIFF. HTML is pretty non-deterministic.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Andrew King" <acking@.cal.ameren.com> wrote in message
news:%23DfU0Z0IFHA.1176@.TK2MSFTNGP15.phx.gbl...
>I am designing a report to output address labels (Avery 5160) and the
>spacing of the data in the columns and the rows descrease as the print
>moves down the page. By the final row of labels, the name line is in the
>row preceeding row. I am using a list object and the margin settings are
>per Avery's spec sheet.
> Anyone else experience this and have a fix?
> Thanks,
> Andy
>|||I am using PDF. I have also tried to fix the size of the fields; unchecked
the "Can increase to accommodate contents".
Andy
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:%23869bC3IFHA.4028@.tk2msftngp13.phx.gbl...
> What rendering output are you using? My guess is you'll have your best
> luck with PDF or TIFF. HTML is pretty non-deterministic.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Andrew King" <acking@.cal.ameren.com> wrote in message
> news:%23DfU0Z0IFHA.1176@.TK2MSFTNGP15.phx.gbl...
>>I am designing a report to output address labels (Avery 5160) and the
>>spacing of the data in the columns and the rows descrease as the print
>>moves down the page. By the final row of labels, the name line is in the
>>row preceeding row. I am using a list object and the margin settings are
>>per Avery's spec sheet.
>> Anyone else experience this and have a fix?
>> Thanks,
>> Andy
>|||Solved! Placed the list object inside a rectangle to fix the size of the
label and removed the right and bottom padding from the field.
"Andrew King" <acking@.cal.ameren.com> wrote in message
news:e4hyBC$IFHA.2844@.TK2MSFTNGP10.phx.gbl...
>I am using PDF. I have also tried to fix the size of the fields; unchecked
>the "Can increase to accommodate contents".
> Andy
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:%23869bC3IFHA.4028@.tk2msftngp13.phx.gbl...
>> What rendering output are you using? My guess is you'll have your best
>> luck with PDF or TIFF. HTML is pretty non-deterministic.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "Andrew King" <acking@.cal.ameren.com> wrote in message
>> news:%23DfU0Z0IFHA.1176@.TK2MSFTNGP15.phx.gbl...
>>I am designing a report to output address labels (Avery 5160) and the
>>spacing of the data in the columns and the rows descrease as the print
>>moves down the page. By the final row of labels, the name line is in the
>>row preceeding row. I am using a list object and the margin settings are
>>per Avery's spec sheet.
>> Anyone else experience this and have a fix?
>> Thanks,
>> Andy
>>
>

Losing Format When Exporting to PDF

I have a report with a table. Some of the table header columns are
multi-worded and wrap on to a second line. For the table header columns that
are not multi-worded, I want the text to appear on the second line and not
the first line which is the default.
If I use the vbscript function â'vbcrlfâ', it renders perfectly in HTML.
However, I want to render in PDF and for some reason when it renders in PDF
the renderer is ignoring that particular formatting and so the single-worded
column text appears on the first line.
Any ideaâ's?
--
MikeOn Dec 10, 1:49 pm, Mike DeYoung
<MikeDeYo...@.discussions.microsoft.com> wrote:
> I have a report with a table. Some of the table header columns are
> multi-worded and wrap on to a second line. For the table header columns that
> are not multi-worded, I want the text to appear on the second line and not
> the first line which is the default.
> If I use the vbscript function "vbcrlf", it renders perfectly in HTML.
> However, I want to render in PDF and for some reason when it renders in PDF
> the renderer is ignoring that particular formatting and so the single-worded
> column text appears on the first line.
> Any idea's?
> --
> Mike
If I'm understand you correctly, you should be able to set the
vertical alignment of the table header column via selecting the header
row on the left-hand-side of the table control, then select F4 (for
the Properties window) and to the right of 'Vertical Align' select
'Bottom.' Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thank you Enrique.
--
Mike
"EMartinez" wrote:
> On Dec 10, 1:49 pm, Mike DeYoung
> <MikeDeYo...@.discussions.microsoft.com> wrote:
> > I have a report with a table. Some of the table header columns are
> > multi-worded and wrap on to a second line. For the table header columns that
> > are not multi-worded, I want the text to appear on the second line and not
> > the first line which is the default.
> >
> > If I use the vbscript function "vbcrlf", it renders perfectly in HTML.
> > However, I want to render in PDF and for some reason when it renders in PDF
> > the renderer is ignoring that particular formatting and so the single-worded
> > column text appears on the first line.
> >
> > Any idea's?
> >
> > --
> > Mike
>
> If I'm understand you correctly, you should be able to set the
> vertical alignment of the table header column via selecting the header
> row on the left-hand-side of the table control, then select F4 (for
> the Properties window) and to the right of 'Vertical Align' select
> 'Bottom.' Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Dec 11, 7:31 am, Mike DeYoung
<MikeDeYo...@.discussions.microsoft.com> wrote:
> Thank you Enrique.
> --
> Mike
> "EMartinez" wrote:
> > On Dec 10, 1:49 pm, Mike DeYoung
> > <MikeDeYo...@.discussions.microsoft.com> wrote:
> > > I have a report with a table. Some of the table header columns are
> > > multi-worded and wrap on to a second line. For the table header columns that
> > > are not multi-worded, I want the text to appear on the second line and not
> > > the first line which is the default.
> > > If I use the vbscript function "vbcrlf", it renders perfectly in HTML.
> > > However, I want to render in PDF and for some reason when it renders in PDF
> > > the renderer is ignoring that particular formatting and so the single-worded
> > > column text appears on the first line.
> > > Any idea's?
> > > --
> > > Mike
> > If I'm understand you correctly, you should be able to set the
> > vertical alignment of the table header column via selecting the header
> > row on the left-hand-side of the table control, then select F4 (for
> > the Properties window) and to the right of 'Vertical Align' select
> > 'Bottom.' Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

losing custom assembly

Hello all
I've got a problem with a custom assembly used in a Report. It works a
little (i see correct values where it is used), but after a while, it
doesn't seem to work anymore (no more correct values, only some 0 with
the same values in parameters)...
Some facts :
the dll is written in vb and is accessing the sql server to return
some values.
it contains the lines
Dim SQLPermission As New
SqlClientPermission(Security.Permissions.PermissionState.Unrestricted)
SQLPermission.Assert()
(seems to be important)
the dll is located in correct directories :
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin
and
C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer
the file rssrvpolicy.config has been modified with :
<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="RS.Utils_Group"
Description="This code group grants custom assembly OASCL01.dll to
access to database">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="file://C:/Program Files/Microsoft SQL Server/MSSQL/Reporting
Services/ReportServer/bin/OASCL01.dll"/>
</CodeGroup>
the DLL is defined in the report properties.
Why do you think it only work for a little time ? (maybe because I
am constantly modifying the report while working on it?)
what can I do to find what's wrong ?
I tried relaunching the reportserver service but nothing...If assembly is not being changed and works at least once when used from a
report published on report server then most likely the problem has nothing
to do with code access security (btw, it looks right).
There might be a bug in the assembly itself.
Usually it is a bad idea to use static variables. Make sure you properly
close all connections / commands / readers you open.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"marc hugon" <sorry@.nospam.com> wrote in message
news:atdbg11fsi5udafl3h881lki03r0lof3jr@.4ax.com...
> Hello all
> I've got a problem with a custom assembly used in a Report. It works a
> little (i see correct values where it is used), but after a while, it
> doesn't seem to work anymore (no more correct values, only some 0 with
> the same values in parameters)...
> Some facts :
> the dll is written in vb and is accessing the sql server to return
> some values.
> it contains the lines
> Dim SQLPermission As New
> SqlClientPermission(Security.Permissions.PermissionState.Unrestricted)
> SQLPermission.Assert()
> (seems to be important)
> the dll is located in correct directories :
> C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportServer\bin
> and
> C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer
> the file rssrvpolicy.config has been modified with :
> <CodeGroup class="UnionCodeGroup"
> version="1"
> PermissionSetName="FullTrust"
> Name="RS.Utils_Group"
> Description="This code group grants custom assembly OASCL01.dll to
> access to database">
> <IMembershipCondition
> class="UrlMembershipCondition"
> version="1"
> Url="file://C:/Program Files/Microsoft SQL Server/MSSQL/Reporting
> Services/ReportServer/bin/OASCL01.dll"/>
> </CodeGroup>
> the DLL is defined in the report properties.
> Why do you think it only work for a little time ? (maybe because I
> am constantly modifying the report while working on it?)
> what can I do to find what's wrong ?
> I tried relaunching the reportserver service but nothing...

Lose Security options in Report manager

I have installed RS on a machine and have deployed several reports. Now when
I go into Report Manager, there is no security settings on the properties tab
- This means I am not allowed to add/edit roles or any security functions at
all. Under the properties tab, i only see a general settings which lists
modified date, modified by, creation date, and created by information for the
report. The security functions used to be there - how do i get them back?
I used the RS.EXE tool to deploy my reports to the server - Could this have
caused my problem somehow? Any help would be appreciated!If you log on from the local machine as a local machine admin you should
always have permission to set security. It is hard to say why you lost your
security, perhaps somebody else came and removed them? Does you script
attempt to modify security?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"PCL" <PCL@.discussions.microsoft.com> wrote in message
news:BD224DBC-8998-45D9-A85A-4A73097C5D2C@.microsoft.com...
>I have installed RS on a machine and have deployed several reports. Now
>when
> I go into Report Manager, there is no security settings on the properties
> tab
> - This means I am not allowed to add/edit roles or any security functions
> at
> all. Under the properties tab, i only see a general settings which lists
> modified date, modified by, creation date, and created by information for
> the
> report. The security functions used to be there - how do i get them back?
> I used the RS.EXE tool to deploy my reports to the server - Could this
> have
> caused my problem somehow? Any help would be appreciated!|||Even when i loginto the local machine as Administrator, the security tabs are
missing. I dont think the RSS file does anything with security - It is a
modified version of the sample scripts that come with RS - we just used them
to deploy our reports.
"Daniel Reib [MSFT]" wrote:
> If you log on from the local machine as a local machine admin you should
> always have permission to set security. It is hard to say why you lost your
> security, perhaps somebody else came and removed them? Does you script
> attempt to modify security?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "PCL" <PCL@.discussions.microsoft.com> wrote in message
> news:BD224DBC-8998-45D9-A85A-4A73097C5D2C@.microsoft.com...
> >I have installed RS on a machine and have deployed several reports. Now
> >when
> > I go into Report Manager, there is no security settings on the properties
> > tab
> > - This means I am not allowed to add/edit roles or any security functions
> > at
> > all. Under the properties tab, i only see a general settings which lists
> > modified date, modified by, creation date, and created by information for
> > the
> > report. The security functions used to be there - how do i get them back?
> >
> > I used the RS.EXE tool to deploy my reports to the server - Could this
> > have
> > caused my problem somehow? Any help would be appreciated!
>
>|||Well if anyone is interested, I got all the security functionality back by
uninstalling and reinstalling reporting services. I tried rerunning my RSS
script and cannot get it to duplicate the problem i was having - Wierd, but i
guess it is fixed...
"PCL" wrote:
> I have installed RS on a machine and have deployed several reports. Now when
> I go into Report Manager, there is no security settings on the properties tab
> - This means I am not allowed to add/edit roles or any security functions at
> all. Under the properties tab, i only see a general settings which lists
> modified date, modified by, creation date, and created by information for the
> report. The security functions used to be there - how do i get them back?
> I used the RS.EXE tool to deploy my reports to the server - Could this have
> caused my problem somehow? Any help would be appreciated!|||Did you tweak the security on the IIS virtual server? If you use anonymous
authentication, you can't publish since it's not authenticating.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"PCL" <PCL@.discussions.microsoft.com> wrote in message
news:BD224DBC-8998-45D9-A85A-4A73097C5D2C@.microsoft.com...
>I have installed RS on a machine and have deployed several reports. Now
>when
> I go into Report Manager, there is no security settings on the properties
> tab
> - This means I am not allowed to add/edit roles or any security functions
> at
> all. Under the properties tab, i only see a general settings which lists
> modified date, modified by, creation date, and created by information for
> the
> report. The security functions used to be there - how do i get them back?
> I used the RS.EXE tool to deploy my reports to the server - Could this
> have
> caused my problem somehow? Any help would be appreciated!

Loosing text format in pdf-Export

Hi NG,
in my report (html-view) i got the following value (i.e) -43.988.224,29 ?
field-type is currency
After export in pdf i got something like that:
0761<; ; 1557/5<#0 ander othe unknown characters.
Any suggests?
frank
www.xax.deAdobe 5.00 and 5.05 do not render symbols (e.g. Euro symbol) correctly when
the Arial font is used. Which version are you using? Did you try other fonts
(e.g. Tahoma)?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Matthiesen" <fm@.xax.de> wrote in message
news:34hkssF46ueteU1@.individual.net...
> Hi NG,
> in my report (html-view) i got the following value (i.e) -43.988.224,29 ?
> field-type is currency
> After export in pdf i got something like that:
> 0761<; ; 1557/5<#0 ander othe unknown characters.
> Any suggests?
> frank
> www.xax.de
>sql

Loosing Bold when exporting to Excel

Hi,
I have a report that bolds font in a table cell based on an expression.
This works fine when I browse the report in reporting services. When I
export the report to Excel - all of the bold text returns to normal weight
text. Any ideas to get around this?
Thanks,
MelissaMelissa, this seems to be a hot topic this week!
I've seen three other posts on this already. This is the same as the
Excel font colour from expressions problem.
Whatever formatting the first cell evaluates too, the whole column is
set too. As far as I can tell, there is no way round this.
I've raised it as a bug with MS
Chris
Melissa wrote:
> Hi,
> I have a report that bolds font in a table cell based on an
> expression. This works fine when I browse the report in reporting
> services. When I export the report to Excel - all of the bold text
> returns to normal weight text. Any ideas to get around this?
> Thanks,
> Melissa|||Thanks Chris for the response!
Melissa
"Chris McGuigan" wrote:
> Melissa, this seems to be a hot topic this week!
> I've seen three other posts on this already. This is the same as the
> Excel font colour from expressions problem.
> Whatever formatting the first cell evaluates too, the whole column is
> set too. As far as I can tell, there is no way round this.
> I've raised it as a bug with MS
> Chris
> Melissa wrote:
> > Hi,
> >
> > I have a report that bolds font in a table cell based on an
> > expression. This works fine when I browse the report in reporting
> > services. When I export the report to Excel - all of the bold text
> > returns to normal weight text. Any ideas to get around this?
> >
> > Thanks,
> > Melissa
>

looping through report items of a rendered report

Hi all,
Situation: I have a WinForm application that uses the SQL 2005 reportviewer
component. That reportviewer uses a local report. The report definition
contains a report item (textbox) called "txtEmail" in the table header.
Would it be possible to walk (in runtime!) throught all the report items of
a rendered report so I can get the value of the report item "txtEmail"? I've
tried this code to walk through all the items in a rendered report:
ListControls(reportViewer.Controls);
private static void ListControls(Control.ControlCollection control)
{
foreach (Control e in control)
{
Console.WriteLine(" * " + e.ToString());
ListControls(e.Controls);
}
}
but it didn't list the actual report items. I expected to see some output
like " * txtEmail" but that control isn't listed.
Purpose: I have a report that lists a single email address in the table
header. I have overridden the
default export behaviour of the reportviewer so I can render the report to
PDF and mail it to a emailaddress that is available in the rendered report.
Any suggestions of how to accomplish this'
I am using VS2005 with local report attached to a WinForm reportviewer
component.
Kind regards,
PeterIn the past I passed a reference of ReportItems to my custom assembly, then
would walk through and grab the values I needed. For my purposes, turned
out there was a more efficient method with hidden textboxes. Also I had
some pages with static text and no rows. Inspecting the ReportItems
collection on these pages would throw an internal error and the report would
fail. Anyway, here's the code I used:
static string _SchoolName;
public string
SchoolName(Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItems
input)
{
string ReturnVal = null;
try
{
Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItem
rptItemSchoolNameHidden = null;
rptItemSchoolNameHidden = input["txtSchoolNameHidden"];
if (rptItemSchoolNameHidden != null)
{
if (rptItemSchoolNameHidden.Value.ToString().Length > 0)
{
_SchoolName =rptItemSchoolNameHidden.Value.ToString();
}
}
}
catch
{
//ignore report item not found error.
}
return _SchoolName;
}
Steve MunLeeuw
"Peter Bons" <joepie@.blakjsd.bl> wrote in message
news:uhlMDzS6GHA.4592@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> Situation: I have a WinForm application that uses the SQL 2005
> reportviewer
> component. That reportviewer uses a local report. The report definition
> contains a report item (textbox) called "txtEmail" in the table header.
> Would it be possible to walk (in runtime!) throught all the report items
> of a rendered report so I can get the value of the report item "txtEmail"?
> I've tried this code to walk through all the items in a rendered report:
> ListControls(reportViewer.Controls);
> private static void ListControls(Control.ControlCollection control)
> {
> foreach (Control e in control)
> {
> Console.WriteLine(" * " + e.ToString());
> ListControls(e.Controls);
> }
> }
> but it didn't list the actual report items. I expected to see some output
> like " * txtEmail" but that control isn't listed.
> Purpose: I have a report that lists a single email address in the table
> header. I have overridden the
> default export behaviour of the reportviewer so I can render the report to
> PDF and mail it to a emailaddress that is available in the rendered
> report.
> Any suggestions of how to accomplish this'
> I am using VS2005 with local report attached to a WinForm reportviewer
> component.
> Kind regards,
> Peter
>
>|||Hi!
Just checking if you managed to solve the problem. I actually wanted to
ask if I could have an embedded code function instead of an external
assembly where in I could access the ReportItems collection for given
"textbox" and loop through all the values.
Thanks in advance for any suggestions.
Steve MunLeeuw wrote:
> In the past I passed a reference of ReportItems to my custom assembly, then
> would walk through and grab the values I needed. For my purposes, turned
> out there was a more efficient method with hidden textboxes. Also I had
> some pages with static text and no rows. Inspecting the ReportItems
> collection on these pages would throw an internal error and the report would
> fail. Anyway, here's the code I used:
> static string _SchoolName;
> public string
> SchoolName(Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItems
> input)
> {
> string ReturnVal = null;
> try
> {
> Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItem
> rptItemSchoolNameHidden = null;
> rptItemSchoolNameHidden = input["txtSchoolNameHidden"];
> if (rptItemSchoolNameHidden != null)
> {
> if (rptItemSchoolNameHidden.Value.ToString().Length > 0)
> {
> _SchoolName => rptItemSchoolNameHidden.Value.ToString();
> }
> }
> }
> catch
> {
> //ignore report item not found error.
> }
> return _SchoolName;
> }
> Steve MunLeeuw
>
> "Peter Bons" <joepie@.blakjsd.bl> wrote in message
> news:uhlMDzS6GHA.4592@.TK2MSFTNGP04.phx.gbl...
> > Hi all,
> >
> > Situation: I have a WinForm application that uses the SQL 2005
> > reportviewer
> > component. That reportviewer uses a local report. The report definition
> > contains a report item (textbox) called "txtEmail" in the table header.
> > Would it be possible to walk (in runtime!) throught all the report items
> > of a rendered report so I can get the value of the report item "txtEmail"?
> > I've tried this code to walk through all the items in a rendered report:
> >
> > ListControls(reportViewer.Controls);
> >
> > private static void ListControls(Control.ControlCollection control)
> >
> > {
> >
> > foreach (Control e in control)
> >
> > {
> >
> > Console.WriteLine(" * " + e.ToString());
> >
> > ListControls(e.Controls);
> >
> > }
> >
> > }
> >
> > but it didn't list the actual report items. I expected to see some output
> > like " * txtEmail" but that control isn't listed.
> >
> > Purpose: I have a report that lists a single email address in the table
> > header. I have overridden the
> > default export behaviour of the reportviewer so I can render the report to
> > PDF and mail it to a emailaddress that is available in the rendered
> > report.
> >
> > Any suggestions of how to accomplish this'
> > I am using VS2005 with local report attached to a WinForm reportviewer
> > component.
> >
> > Kind regards,
> > Peter
> >
> >
> >

looping parameters collection

Is there any possibility to loop through all the parameters of the parameters
collection in the custom code of a report?
I tried using a "For Each" loop, but aparently that doesn't work.
The goal is to be able to use the parameter name - value pairs,
independently of the number of parameters in the report.Curt, I posted a simular question a few minutes ago ...
"Curt Biernaux" wrote:
> Is there any possibility to loop through all the parameters of the parameters
> collection in the custom code of a report?
> I tried using a "For Each" loop, but aparently that doesn't work.
> The goal is to be able to use the parameter name - value pairs,
> independently of the number of parameters in the report.|||There is no easy way to do it.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Curt Biernaux" <Curt Biernaux@.discussions.microsoft.com> wrote in message
news:8182188F-F2D9-4643-A933-2E9E4E69E6F7@.microsoft.com...
> Is there any possibility to loop through all the parameters of the
> parameters
> collection in the custom code of a report?
> I tried using a "For Each" loop, but aparently that doesn't work.
> The goal is to be able to use the parameter name - value pairs,
> independently of the number of parameters in the report.|||Even if there would be a hard way, there is still a way to solve this problem.
Can you tell me more about it?
"Lev Semenets [MSFT]" wrote:
> There is no easy way to do it.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Curt Biernaux" <Curt Biernaux@.discussions.microsoft.com> wrote in message
> news:8182188F-F2D9-4643-A933-2E9E4E69E6F7@.microsoft.com...
> > Is there any possibility to loop through all the parameters of the
> > parameters
> > collection in the custom code of a report?
> > I tried using a "For Each" loop, but aparently that doesn't work.
> > The goal is to be able to use the parameter name - value pairs,
> > independently of the number of parameters in the report.
>
>

Looping each record

Hi,

My report requirement of customer activity report.

For each customer, my report should show the summary by purchase catgory and then individual transaction details.

Customer Name: Tom Tim

Books: 100

Computers : 2

Others: 23

(then all 125 transactions should be listed).

Same summary and detail should be repeated for each customer in the same single report. (there are aound 200 customers)

How can I achieve this? (other than Sub report). Sub-report seems to be very slow in rendering?

Thanks,

Vasanth

Wouldn't a list work? The list would repeat for each customer.|||Within the list, I am not able to user another table to list the transaction because list allows only group (aggregate) functionalities.|||any ideas pls?

Loopig Each Record in dataset

Hi,
My report requirement of customer activity report.
For each customer, my report should show the summary by purchase
catgory and then individual transaction details.
Customer Name: Tom Tim
Books: 100
Computers : 2
Others: 23
(then all 125 transactions should be listed).
Same summary and detail should be repeated for each customer in the
same single report. (there are aound 200 customers)
How can I achieve this? (other than Sub report). Sub-report seems to be
very slow in rendering? Also If I use List control, it does not allow
table inside list with out aggregate.
So I couldn;t use list control
Thanks,
VasanthSubreport is how you have to solve this. Make sure that you have appropriate
indexing so the subreport query runs quickly.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bvasanth123@.rediffmail.com> wrote in message
news:1137791468.342737.278100@.g47g2000cwa.googlegroups.com...
> Hi,
> My report requirement of customer activity report.
> For each customer, my report should show the summary by purchase
> catgory and then individual transaction details.
> Customer Name: Tom Tim
> Books: 100
> Computers : 2
> Others: 23
> (then all 125 transactions should be listed).
> Same summary and detail should be repeated for each customer in the
> same single report. (there are aound 200 customers)
> How can I achieve this? (other than Sub report). Sub-report seems to be
> very slow in rendering? Also If I use List control, it does not allow
> table inside list with out aggregate.
> So I couldn;t use list control
> Thanks,
> Vasanth
>|||The performance is very poor when we try to render in PDF. We checked
SP (in sub-report) which takes 1-2 seconds for each customer. But
rendering that takes more than 30 mins depending on data.|||Your question was initially about subreports. Based on what you want to do,
subreports are the way to do it. There are differences with rendering
different formats. HTML and CSV are very fast, PDF and Excel are much slower
(order of magnitude slower). Your hypothesis that the problem is the
subreports most likely is not the issue. The issue with PDF is either
complexity or the size of the data (number of rows and size of rows). Also,
do you have any images? If so, what format is the image in. Try the report
without images and see if that helps. If you want to know how much the
optimal time is then render it to html and see how long that takes. That is
the best case.
Is this for a user viewing on the screen or is this destined for printing?
If it is the user then try using drill through. Drill through is quite fast.
Also, does it have to be pdf? As I said, html is much faster.
RS does all rendering in memory. So you can also try adding additional RAM
to see if that would help.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bvasanth123@.rediffmail.com> wrote in message
news:1137851207.701517.281770@.g14g2000cwa.googlegroups.com...
> The performance is very poor when we try to render in PDF. We checked
> SP (in sub-report) which takes 1-2 seconds for each customer. But
> rendering that takes more than 30 mins depending on data.
>|||Thanks Bruces
1) Total records: 20,000 + ( 100 customer * 200 recods average)
2) This is purely for audit record keeping purpose. Not online report
3) We do not have any images
4) HTML report is coming quickly around 10 mins. PDF seems to be the
killer.. Takes for ever. (HTML size is ? 50 MB which we do not prefer)
5) We tried another sample report that just display 20,000+. PDF comes
in 5-10 mins. But if the same 20K+ records have been run with
sub-reports, it goes down in performance.
5) RAM is 4 GB|||RAM is fine. If it takes 10 mins for html and 30 for PDF then you are doing
pretty good. Usually the difference is greater than that. You might be able
to do something to get the data down quicker (playing with indexes) but that
would only affect at most 10 mins of the total time. The extra 20 minutes is
PDF rendering issues. My suggestion is to just schedule it to run at night.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bvasanth123@.rediffmail.com> wrote in message
news:1138029125.861955.154170@.o13g2000cwo.googlegroups.com...
> Thanks Bruces
> 1) Total records: 20,000 + ( 100 customer * 200 recods average)
> 2) This is purely for audit record keeping purpose. Not online report
> 3) We do not have any images
> 4) HTML report is coming quickly around 10 mins. PDF seems to be the
> killer.. Takes for ever. (HTML size is ? 50 MB which we do not prefer)
> 5) We tried another sample report that just display 20,000+. PDF comes
> in 5-10 mins. But if the same 20K+ records have been run with
> sub-reports, it goes down in performance.
> 5) RAM is 4 GB
>

Loop in SSRS Expression

Hi guys,

I got a multi value parameter which displays all my 'companies'. On my report header, I wish to display all the companies selected by the user.

Parameters!Company.Label(0) only shows the first on selected

Please, any help will be greatly appreciated

W Wilmot

W

Try this.

Join( Parameters!Company.value," ,")

the second part of the Join is your delimiter.

Hammer

|||

Thanx Hammer

It worked fine if i use the 'Join( Parameters!Company.label," ,")' instead of 'Join( Parameters!Company.value," ,")'

Thank you, again!!!

|||

W,

Not a problem, can you mark this post helpful?

Thanks,

Hammer

Lookup inside a report

Hi,

I'm trying to create a report to generate all the members at a site. The problem is that there are about a thousand sites and so providing a drop-down parameter for the site name is not feasible. I was thinking along the lines of a pop-up window or sub-query where the user can type the site name and search, then select the name from the list, then generate the report. Any ideas on how I can do this?

try writing a web app that allow's for the search and then passing what ever site they chose in the call line to your report.

LookUp Entities and Roles against Multiple Entities?

Apologies if this has already been asked and answered, though I haven't found it via search.

In my report model I have a lookup entity that consists of a Code field (PK) and a Description field this is linked to a parent entity (table) and works fine, e.g. the description is shown in the parent entity instead of the FK code. However this lookup entity would be useful linked to many other entities (tables) which use the same FK code linked to the lookup entity's Code field (PK) for use of the Description field. In my first attempt I linked the lookup to a second table entity but unlike the originally linked table entity, this is not shown as the description in report builder, only the role appears and I have to select the role then the description from the 2 fields displayed (Code, Description), additionally I get shown the related first table entity.

Firstly can a lookup entity be linked to multiple tables?

Secondly, if so am I forgetting something so only the description is shown like in the first linked table entity?

Thirdly, if I correct the problem is there a property I need to enable/disable to stop the first linked table entity appearing in the second table entity entity list in report builder or should I just leave it?

Any help/advice/suggestions would be appreciated, and apologies for long list of questions but all the books and help files I have used don't seem to use imperfect/real life data sources and examples.

Thanks

Andy

This is a bit weird replying to myself, but think I need to let you know, and others who may come across the problem, I believe I have solved it.

The problem appeared to be related to my using more than 1 Identifying Attribute for the lookup entity. Once I amended this to one Identifying Attribute it would appear that all is well. I get the description instead of a role entity appearing and the lookup works with multiple table entities (e.g. more than one table entity is linked to the lookup-table entity). I think I misunderstood the IdentifyingAttributes property thinking this had to be the PK field.

Thanks

Andy

Looking for tutorials/walkthroughts on Report parameters

Could someone please point me to a tutorial on how to implement complex
parameters in Reporting Services reports? I ask this because the online help
available seems to be extremely minimal.
What I will probably be doing is writing reports with multiple parameters
including single values, dropdown lists, and calendars. Also I probably will
need to hide/expose/change-value-of some parameters based on the selection of
others.
Any sort of info or even a recognizable starting point to research this
would be of help. Thanks!Sorry. False alarm. Please cancel.
(I should say I found the help for the Report Parameters editing screen
minimal. Finally Googled and found the Technet tutorial on cascading
parameters which is roughly what I was looking for.
However this is all academic because I've just been taken off the project.)
"B. Chernick" wrote:
> Could someone please point me to a tutorial on how to implement complex
> parameters in Reporting Services reports? I ask this because the online help
> available seems to be extremely minimal.
> What I will probably be doing is writing reports with multiple parameters
> including single values, dropdown lists, and calendars. Also I probably will
> need to hide/expose/change-value-of some parameters based on the selection of
> others.
> Any sort of info or even a recognizable starting point to research this
> would be of help. Thanks!

Looking for suggestions. XML dataset

We have a table where each row holds an XML dataset. I need to extract the
dataset from each row and use the data in a SQL report server report. I
don't know a great deal about XML so, some suggestions would be great.
thanks
Start reading everything on www.sqlxml.org ...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"jerod" <jerod@.synergybusiness.com> wrote in message
news:%234XJpmezEHA.748@.TK2MSFTNGP14.phx.gbl...
> We have a table where each row holds an XML dataset. I need to extract the
> dataset from each row and use the data in a SQL report server report. I
> don't know a great deal about XML so, some suggestions would be great.
> thanks
>
|||Could you please elaborate on what it means " where each row holds an XML
dataset."... does this mean you store the Xml serialization of a dataset in
a given column?
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad/

Looking for some SQL Advice

I'm working on a project to create a Report. Currently, I have an
Excel spreadsheet with a macro running that creates the report that I
want to move to SQL reporting.
There are 2 tables. One Table has 2 sets of price ranges, with a
"StartPrice", "EndPrice", and a "Type" to select between the 2 set of
ranges.
One of the ranges is like:
Type1 $0.00 $49.00
Type1 $49.01 $79.00
Type1 $79.01 $99.00
Type1 $99.01 $129.00
Type1 $129.01 $149.00
Type1 $149.01 $179.00
Type1 $179.01 $199.00
Type1 $199.01 $249.00
Type1 $249.01 $299.00
Type1 $299.01 $349.00
Type1 $349.01 $399.00
Type1 $399.01 $499.00
Type1 $499.01 $599.00
Type1 $599.01 $699.00
The other table has sales data with a qty, sellingprice, Actualcost
that I calculate the Total Qty, Total Cost, Total SellingPrice, and
Profit, for each of the price ranges.
On the report its like:
Range Qty Price Cost Profit
$299.01 - $349.00 1 $349.00 $142.28 $206.72
$349.01 - $399.00 1 $362.38 $180.93 $181.45
$399.01 - $499.00
To achieve this output, I loop thru each of the Price ranges, pluging
the Start and End into a Select to select the single record for the
Report.
I was wondering if there was a way to simply by using only one SQL
command.
Any ideas?
BartYes, you can join the sales data to the range data where the Total Cost is between the Start Price and the End Price.
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.|||Could you show me an example?
Using my rough field names would be fine.
Bart
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:O5icBq7YEHA.3692@.TK2MSFTNGP09.phx.gbl...
> Yes, you can join the sales data to the range data where the Total Cost is
between the Start Price and the End Price.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Looking for guidence on Parameters.

I am trying to procduce a report using an " IN (x,y,z) "clause except that
x,y,z could contain 1 or 10 values, is it possible to change the base query
at run time or use a single parameter array to convert the values?
Any help and guuidence appriciated.Richard,
Maybe a filter might be an option and wite some VB.NET code to use instr
command to handle the variable values ?
- peteZ
"Namshub" <Richard._NoSpam_ForME_Pullen@.Southend.nhs.uk> wrote in message
news:%23bK82kOtEHA.3460@.TK2MSFTNGP15.phx.gbl...
>I am trying to procduce a report using an " IN (x,y,z) "clause except that
>x,y,z could contain 1 or 10 values, is it possible to change the base query
>at run time or use a single parameter array to convert the values?
> Any help and guuidence appriciated.
>
>|||Your report query can be expression-based. You can pass the whole IN clouse
(x, y, x) as a report parameter but be careful about SQL injection attacks.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Namshub" <Richard._NoSpam_ForME_Pullen@.Southend.nhs.uk> wrote in message
news:%23bK82kOtEHA.3460@.TK2MSFTNGP15.phx.gbl...
> I am trying to procduce a report using an " IN (x,y,z) "clause except that
> x,y,z could contain 1 or 10 values, is it possible to change the base
query
> at run time or use a single parameter array to convert the values?
> Any help and guuidence appriciated.
>
>|||No i've tried to look at what you are saying but cant see how you would
implement it
Do you put a parameter in the SQL code, or is it a filer? a simple example
would be really helpful.
Thanks
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
news:eOtAzSXtEHA.3564@.tk2msftngp13.phx.gbl...
> Your report query can be expression-based. You can pass the whole IN
> clouse
> (x, y, x) as a report parameter but be careful about SQL injection
> attacks.
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Namshub" <Richard._NoSpam_ForME_Pullen@.Southend.nhs.uk> wrote in message
> news:%23bK82kOtEHA.3460@.TK2MSFTNGP15.phx.gbl...
>> I am trying to procduce a report using an " IN (x,y,z) "clause except
>> that
>> x,y,z could contain 1 or 10 values, is it possible to change the base
> query
>> at run time or use a single parameter array to convert the values?
>> Any help and guuidence appriciated.
>>
>|||Hi,
What attached report definition are you referring to? I am also having to
pass multivalues to a parameter and I would like to use in(x,y).
Thanks
"Teo Lachev [MVP]" wrote:
> Namshub,
> Please look at the attached report definition which demostrates an
> expression-based SQL statement that takes comma-separated list of employee
> identifiers passed as a report parameter. It produces a report that shows
> the purchase orders associated with a single or mutiple employees.
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Namshub" <Richard._NoSpam_ForME_Pullen@.Southend.nhs.uk> wrote in message
> news:uSEkVxdtEHA.1368@.TK2MSFTNGP15.phx.gbl...
> > No i've tried to look at what you are saying but cant see how you would
> > implement it
> >
> > Do you put a parameter in the SQL code, or is it a filer? a simple
> example
> > would be really helpful.
> >
> > Thanks
> >
> > "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
> > news:eOtAzSXtEHA.3564@.tk2msftngp13.phx.gbl...
> > > Your report query can be expression-based. You can pass the whole IN
> > > clouse
> > > (x, y, x) as a report parameter but be careful about SQL injection
> > > attacks.
> > >
> > > --
> > > Hope this helps.
> > >
> > > ---
> > > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > > Author: "Microsoft Reporting Services in Action"
> > > Publisher website: http://www.manning.com/lachev
> > > Buy it from Amazon.com: http://shrinkster.com/eq
> > > Home page and blog: http://www.prologika.com/
> > > ---
> > >
> > > "Namshub" <Richard._NoSpam_ForME_Pullen@.Southend.nhs.uk> wrote in
> message
> > > news:%23bK82kOtEHA.3460@.TK2MSFTNGP15.phx.gbl...
> > >> I am trying to procduce a report using an " IN (x,y,z) "clause except
> > >> that
> > >> x,y,z could contain 1 or 10 values, is it possible to change the base
> > > query
> > >> at run time or use a single parameter array to convert the values?
> > >>
> > >> Any help and guuidence appriciated.
> > >>
> > >>
> > >>
> > >
> > >
> >
> >
>
>|||Hi,
Hi,
What you could do is create a function on the SQL server that will
convert your comma separated list to a table.
Like this:
CREATE FUNCTION dbo.UDF_CharCommaSeparatedListToTable
(
@.CommaSeparatedList VARCHAR(8000)
)
RETURNS @.ParsedTableValue TABLE (TableValue VARCHAR(1000))
AS
BEGIN
DECLARE @.TableValue VARCHAR(1000)
DECLARE @.Pos INT
SET @.CommaSeparatedList = LTRIM(RTRIM(@.CommaSeparatedList))+ ','
SET @.Pos = CHARINDEX(',', @.CommaSeparatedList, 1)
IF REPLACE(@.CommaSeparatedList, ',', '') <> ''
BEGIN
WHILE @.Pos > 0
BEGIN
SET @.TableValue = LTRIM(RTRIM(LEFT(@.CommaSeparatedList, @.Pos - 1)))
IF @.TableValue <> ''
BEGIN
INSERT INTO @.ParsedTableValue (TableValue)
VALUES (RTRIM(@.TableValue))
END
SET @.CommaSeparatedList = RIGHT(@.CommaSeparatedList,
LEN(@.CommaSeparatedList) - @.Pos)
SET @.Pos = CHARINDEX(',', @.CommaSeparatedList, 1)
END
END
RETURN
END
Then use this function in your query or stored procedure like this:
SELECT a.country, ..., ..., ...
FROM table1 a
JOIN dbo.UDF_CharCommaSeparatedListToTable(@.CountryList) b ON
RTRIM(b.TableValue) = a.country
HTH,
Eric|||Hi,
Thanks alot for the help.
Now I need to modify the function. I tried but was unsuccessful.
What I need is to be able to pass null values so if the user doesn't select
a value all the employee names will bel returned.
Also I need multiple where statements. I tried multiple inner joins but
obviously it returns the error:
The correlation name 'b' is specified multiple times in a FROM clause
How can I fix the problems?
Thanks
"Aiwa" wrote:
> Hi,
> Hi,
>
> What you could do is create a function on the SQL server that will
> convert your comma separated list to a table.
>
> Like this:
> CREATE FUNCTION dbo.UDF_CharCommaSeparatedListToTable
> (
> @.CommaSeparatedList VARCHAR(8000)
> )
> RETURNS @.ParsedTableValue TABLE (TableValue VARCHAR(1000))
> AS
> BEGIN
> DECLARE @.TableValue VARCHAR(1000)
> DECLARE @.Pos INT
>
> SET @.CommaSeparatedList = LTRIM(RTRIM(@.CommaSeparatedList))+ ','
> SET @.Pos = CHARINDEX(',', @.CommaSeparatedList, 1)
> IF REPLACE(@.CommaSeparatedList, ',', '') <> ''
> BEGIN
> WHILE @.Pos > 0
> BEGIN
>
> SET @.TableValue = LTRIM(RTRIM(LEFT(@.CommaSeparatedList, @.Pos - 1)))
> IF @.TableValue <> ''
> BEGIN
> INSERT INTO @.ParsedTableValue (TableValue)
> VALUES (RTRIM(@.TableValue))
> END
> SET @.CommaSeparatedList = RIGHT(@.CommaSeparatedList,
> LEN(@.CommaSeparatedList) - @.Pos)
> SET @.Pos = CHARINDEX(',', @.CommaSeparatedList, 1)
> END
> END
> RETURN
> END
>
> Then use this function in your query or stored procedure like this:
>
> SELECT a.country, ..., ..., ...
> FROM table1 a
> JOIN dbo.UDF_CharCommaSeparatedListToTable(@.CountryList) b ON
> RTRIM(b.TableValue) = a.country
>
> HTH,
> Eric
>|||I solved my problem for null values and for multiple statements in the where
clause as follows:
WHERE (@.param is null or userid in (Select tablevalue from
dbo.UDF_CharCommaSeparatedListToTable (@.param)))
"Aiwa" wrote:
> Hi,
> Hi,
>
> What you could do is create a function on the SQL server that will
> convert your comma separated list to a table.
>
> Like this:
> CREATE FUNCTION dbo.UDF_CharCommaSeparatedListToTable
> (
> @.CommaSeparatedList VARCHAR(8000)
> )
> RETURNS @.ParsedTableValue TABLE (TableValue VARCHAR(1000))
> AS
> BEGIN
> DECLARE @.TableValue VARCHAR(1000)
> DECLARE @.Pos INT
>
> SET @.CommaSeparatedList = LTRIM(RTRIM(@.CommaSeparatedList))+ ','
> SET @.Pos = CHARINDEX(',', @.CommaSeparatedList, 1)
> IF REPLACE(@.CommaSeparatedList, ',', '') <> ''
> BEGIN
> WHILE @.Pos > 0
> BEGIN
>
> SET @.TableValue = LTRIM(RTRIM(LEFT(@.CommaSeparatedList, @.Pos - 1)))
> IF @.TableValue <> ''
> BEGIN
> INSERT INTO @.ParsedTableValue (TableValue)
> VALUES (RTRIM(@.TableValue))
> END
> SET @.CommaSeparatedList = RIGHT(@.CommaSeparatedList,
> LEN(@.CommaSeparatedList) - @.Pos)
> SET @.Pos = CHARINDEX(',', @.CommaSeparatedList, 1)
> END
> END
> RETURN
> END
>
> Then use this function in your query or stored procedure like this:
>
> SELECT a.country, ..., ..., ...
> FROM table1 a
> JOIN dbo.UDF_CharCommaSeparatedListToTable(@.CountryList) b ON
> RTRIM(b.TableValue) = a.country
>
> HTH,
> Eric
>|||Hi,
To fix the null values issues maybe you could try someting like this:
*** It is not going to work if the parameter @.EmployeeList is null...
maybe en empty string could represent all possible values...
SELECT a.EmployeeName, a.col2,..., ...
FROM table1 a
LEFT OUTER JOIN dbo.UDF_CharCommaSeparatedListToTable(@.EmployeeList) b
ON 1 =1
WHERE a.EmployeeName = CASE WHEN RTRIM(@.EmployeeList) = '' THEN
a.EmployeeName
ELSE RTRIM(b.TableValue)
END
For the correlation name issue, you can change the alias for every join
you make to the function:
SELECT a.EmployeeName, a.col2,..., ...
FROM table1 a
LEFT OUTER JOIN dbo.UDF_CharCommaSeparatedListToTable(@.ParameterList1)
b ON ...
LEFT OUTER JOIN dbo.UDF_CharCommaSeparatedListToTable(@.ParameterList2)
c ON ...
LEFT OUTER JOIN dbo.UDF_CharCommaSeparatedListToTable(@.ParameterList3)
d ON ...
LEFT OUTER JOIN dbo.UDF_CharCommaSeparatedListToTable(@.ParameterList4)
e ON ...
LEFT OUTER JOIN dbo.UDF_CharCommaSeparatedListToTable(@.ParameterList5)
f ON ...
HTH,
Eric|||Hi,
Your way of managing null values is better than mine, I'll be using
that in the future!!
Eric|||Ouups,
After giving it a little more thought, I will stick to my way of
managing null (empty) values since your way of managing nulls would
require the function to be called for every records of your resultset
and my way will be calling the function only once...
Regards,
Eric