Cheap Export to Word

I was wondering how everyone (if anyone) is exporting reports to Word? Does
anyone know of an open source attempt at this or even one that is only a few
hundred bucks?
Thanks
ScottHello Scott,
The Reporting Services does not support the Word Extension. You need to
develop the custom extension.
I would like to provide an article which used for Reporting Services 2005
and Office 2007. In this article, the author create a component which makes
you could export the report to a word document.
Designing and Delivering Rich Office Reports with SQL Server Reporting
Services 2005 and SoftArtisans OfficeWriter
http://msdn2.microsoft.com/en-us/library/aa964136.aspx
Hope this will be some help for you.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
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.|||That's the component that I was looking at, but it's VERY expensive!
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:HE%23aMMDGHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> Hello Scott,
> The Reporting Services does not support the Word Extension. You need to
> develop the custom extension.
> I would like to provide an article which used for Reporting Services 2005
> and Office 2007. In this article, the author create a component which
> makes
> you could export the report to a word document.
> Designing and Delivering Rich Office Reports with SQL Server Reporting
> Services 2005 and SoftArtisans OfficeWriter
> http://msdn2.microsoft.com/en-us/library/aa964136.aspx
> Hope this will be some help for you.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Wow - $1500 for a component to export into word (ultimately anyway).
Why did MS never implement it? I mean, with SP2 just released for all SQL
2005 products, it would have been the perfect opportunity.
"Scott M" <scott_M@.nospam.nospam> wrote in message
news:uzVlWyEVHHA.4832@.TK2MSFTNGP04.phx.gbl...
> That's the component that I was looking at, but it's VERY expensive!
> "Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
> news:HE%23aMMDGHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
>> Hello Scott,
>> The Reporting Services does not support the Word Extension. You need to
>> develop the custom extension.
>> I would like to provide an article which used for Reporting Services 2005
>> and Office 2007. In this article, the author create a component which
>> makes
>> you could export the report to a word document.
>> Designing and Delivering Rich Office Reports with SQL Server Reporting
>> Services 2005 and SoftArtisans OfficeWriter
>> http://msdn2.microsoft.com/en-us/library/aa964136.aspx
>> Hope this will be some help for you.
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> Get notification to my posts through email? Please refer to
>> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
>> ications.
>> Note: The MSDN Managed Newsgroup support offering is for non-urgent
>> issues
>> where an initial response from the community or a Microsoft Support
>> Engineer within 1 business day is acceptable. Please note that each
>> follow
>> up response may take approximately 2 business days as the support
>> professional working with you may need further investigation to reach the
>> most efficient resolution. The offering is not appropriate for situations
>> that require urgent, real-time or phone-based interactions or complex
>> project analysis and dump analysis issues. Issues of this nature are best
>> handled working with a dedicated Microsoft Support Engineer by contacting
>> Microsoft Customer Support Services (CSS) at
>> http://msdn.microsoft.com/subscriptions/support/default.aspx.
>> ==================================================>> (This posting is provided "AS IS", with no warranties, and confers no
>> rights.)
>|||On Feb 19, 10:25 pm, "Immy" <therealasianb...@.hotmail.com> wrote:
> Wow - $1500 for a component to export intoword(ultimately anyway).
> Why did MS never implement it? I mean, with SP2 just released for all SQL
> 2005 products, it would have been the perfect opportunity.
> "Scott M" <scot...@.nospam.nospam> wrote in message
> news:uzVlWyEVHHA.4832@.TK2MSFTNGP04.phx.gbl...
>
> > That's the component that I was looking at, but it's VERY expensive!
> > "Wei Lu [MSFT]" <w...@.online.microsoft.com> wrote in message
> >news:HE%23aMMDGHHA.2304@.TK2MSFTNGHUB02.phx.gbl...
> >> Hello Scott,
> >> TheReportingServicesdoes not support theWordExtension. You need to
> >> develop the custom extension.
> >> I would like to provide an article which used forReportingServices2005
> >> and Office 2007. In this article, the author create a component which
> >> makes
> >> you could export the report to aworddocument.
> >> Designing and Delivering Rich Office Reports with SQL ServerReporting
> >>Services2005 and SoftArtisans OfficeWriter
> >>http://msdn2.microsoft.com/en-us/library/aa964136.aspx
> >> Hope this will be some help for you.
> >> Sincerely,
> >> Wei Lu
> >> Microsoft Online Community Support
> >> ==================================================> >> Get notification to my posts through email? Please refer to
> >>http://msdn.microsoft.com/subscriptions/managednewsgroups/default.asp...
> >> ications.
> >> Note: The MSDN Managed Newsgroup support offering is for non-urgent
> >> issues
> >> where an initial response from the community or a Microsoft Support
> >> Engineer within 1 business day is acceptable. Please note that each
> >> follow
> >> up response may take approximately 2 business days as the support
> >> professional working with you may need further investigation to reach the
> >> most efficient resolution. The offering is not appropriate for situations
> >> that require urgent, real-time or phone-based interactions or complex
> >> project analysis and dump analysis issues. Issues of this nature are best
> >> handled working with a dedicated Microsoft Support Engineer by contacting
> >> Microsoft Customer SupportServices(CSS) at
> >>http://msdn.microsoft.com/subscriptions/support/default.aspx.
> >> ==================================================> >> (This posting is provided "AS IS", with no warranties, and confers no
> >> rights.)- Hide quoted text -
> - Show quoted text -
Check out Aspose.Words for Reporting Services that we have just
released:
http://www.aspose.com/Products/Aspose.WordsRS/
It is a true rendering extension for Microsoft SQL Server 2005
Reporting Services that adds export to DOC, RTF and WordprocessingML.|||Why is "export to Word" not built into report services?

Charts slow report rendering to a crawl

I've searched the forums on this issue, haven't really found the answer.

I have several nifty little sales reports which crunch a ton of data quite efficiently and render in just a few seconds in Report Manager. I've pushed as much of the data processing back to the server as possible, use a stored procedure (with parameters) in a shared datasource, don't return unneccessary data, all that. It works great.

When I first developed the reports, I continued generating my charts (which use the same data as the reports, just grouped differently) in Excel and pasting them in as images. Now I want to stop that nonsense and use the SSRS charts. I fooled around with the charting function and got a reasonable facimile of my Excel charts, two per report, which use their own separate stored procedures and the same shared datasource.

Now, reports that used to render in 5-8 seconds may take 1-5 MINUTES. Help! It's definitely the charts--taking them back out fixes the problem.

I have complete control over the datasources--would it make more sense to use non-shared sources, or to create totally separate shared sources? I saw a post that recommended "making data calls non-synchronous," but I have no idea how to do that.

Thanks for any suggestions.

On further investigation, it appears that deleting EITHER ONE of the charts brings the rendering time down almost to the same time as no chart at all. It's apparent that having MULTIPLE charts on a page multiplies the rendering time exponentially (I'm gonna tell Edward Tufte!)

This happens whether I put the charts side-by-side (preferred) or one above the other on the page--they just take FOREVER to render.

Anybody...?

|||

I know this post is a bit old but im having exactly the same problem.

I have a report that has around 150 charts on it once expanded and it is taking around 3 seconds to load each chart.

Is there a resolution for this?

Regards

Will

Charts slow report rendering to a crawl

I've searched the forums on this issue, haven't really found the answer.

I have several nifty little sales reports which crunch a ton of data quite efficiently and render in just a few seconds in Report Manager. I've pushed as much of the data processing back to the server as possible, use a stored procedure (with parameters) in a shared datasource, don't return unneccessary data, all that. It works great.

When I first developed the reports, I continued generating my charts (which use the same data as the reports, just grouped differently) in Excel and pasting them in as images. Now I want to stop that nonsense and use the SSRS charts. I fooled around with the charting function and got a reasonable facimile of my Excel charts, two per report, which use their own separate stored procedures and the same shared datasource.

Now, reports that used to render in 5-8 seconds may take 1-5 MINUTES. Help! It's definitely the charts--taking them back out fixes the problem.

I have complete control over the datasources--would it make more sense to use non-shared sources, or to create totally separate shared sources? I saw a post that recommended "making data calls non-synchronous," but I have no idea how to do that.

Thanks for any suggestions.

On further investigation, it appears that deleting EITHER ONE of the charts brings the rendering time down almost to the same time as no chart at all. It's apparent that having MULTIPLE charts on a page multiplies the rendering time exponentially (I'm gonna tell Edward Tufte!)

This happens whether I put the charts side-by-side (preferred) or one above the other on the page--they just take FOREVER to render.

Anybody...?

Charts on multiple pages?

Hi All,

I am using SQL Reportig Services 2005 for displaying reports in Bar Graph charts. The problem is that the width of each bar in chart is not fixed and changes based on volume of data. If number of rows retrieved by dataset connection are many, then the graph appears too cramped and is not readable.

Is there any way, programmatically or at design time, that we can change the width of chart based on number of rows retrieved in the dataset?

Any help or reference will be highly appreciated.

Thanks in Advance !!!

Assuming all the data you are showing as bars are really detail rows rather than grouped data, you could consider the following approach:

Add a (detail) group to the list with the following grouping expression:

=Int((RowNumber(Nothing)-1)/20)

As a result you would get 20 detail rows per (repeating) list instance. Then just put the chart inside the list. Those chart instances will never "see" more than 20 detail rows at once.

-- Robert

|||

Thanks Robert !!!

The solution given by you works fine. But I did not get what you mean by 'data you are showing as bars are really detail rows rather than grouped data'.

Is there any limitation to the approach that you have given?

Thanks for reply once again !!!

Charts Not showing in some machines

Hello I am using crystatl reports 8.5 and Visual Basic 6.0

I have created a set up with all assemblies and installed in some machines and found that in some machines the Charts created using Crystal reports were showing but for some Blank Crystal reports appearing.

One of the machine where the chart now showing is XP PRofessional.

Need help urgently

Thanks in advanceWhere you ever able to resolve the problem? I am also having the same problem and have been looking for a week to try and resolve it and have tried all sort.

Please can you help?|||I have no luck with that.
So i have moved to vb.net from vb6.0 and with .net you have no such problems.

Sorry i cannot help you.

thanks and regards
vimal

Charts don't update when using dropdown parameters

I have recently upgraded from RS2000 to RS2005. The reports seem to have
converted without any errors. In the report designer, everything works fine.
When deploying to the server the reports don't update properly.
Details:
The reports have two components (a table and a chart) - both are based on
the same query. There are three parameters in a report. One is a dropdown
box that includes a list of products based on a subquery. The other two
parameters are textboxes that provide start and end months for the reporting
period. When changing either of the month parameters, the table and chart
updates with proper data. When changing the product (dropdown box), the
table information updates, but the chart does not (it still includes the
title and data from the previous update when a month was changed; or it
includes the results of the first query if a month was not changed).
Has anyone experienced anything like this? Is there a fix?Hello Piell,
I tested on my side and do not reproduce this issue.
Also, I did not find any related issue in our internal database.
I would like to know if this issue appeared on all reports?
If you apply SP1 on your SSRS 2005, does this issue persist?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Wei,
We have the same problem on all reports and we have even deleted the chart
and re-created it and get the same results. I will be checking with the
admins later today to find out if SP1 has been applied. If it hasn't, we
will have them update it.
Thanks.|||Wei,
We have installed SQL 2005 SP1 and still have the same problem. Would the
available hot fix help
http://download.microsoft.com/download/6/e/8/6e85f7ab-9f6c-4f3c-8f89-da0f78e026dc/rs2005-kb918222-x86-enu.exe|||We installed hot fix and charts still don't update when dropdown parameter
changes.|||Hello piell,
I would like to suggest you to check your IE security setting and try to
set the security level to low.
If this issue still persist, would you please send a sample report to me so
that I could try to reproduce this issue on my side.
To reach me, please remove the ONLINE in my email address.
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.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
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.

Chart transparency problem

I have an XY scatter chart on one of my reports where some of the data points
either partially or completely overlap one another.
I've setup my chart value markers to have a transparent fill so that users
can see when two points overlap. My problem is that when reports are viewed
each data point is displayed with a solid fill for some reason. I've tried to
manually edit the RDL file and add a BackgroundColor value of 'Transparent'
however I get a build error when I do this (Transparent is not a valid
background colour).
Any ideas.Just found this in the SP1 release notes:
"A fill color of Transparent will cause the chart elements to display using
the automatic color assignment from the chart palette. "
Explains why my data points are not displaying with a transparent fill but
is there any way to fix this?
"Martin Hinchy" wrote:
> I have an XY scatter chart on one of my reports where some of the data points
> either partially or completely overlap one another.
> I've setup my chart value markers to have a transparent fill so that users
> can see when two points overlap. My problem is that when reports are viewed
> each data point is displayed with a solid fill for some reason. I've tried to
> manually edit the RDL file and add a BackgroundColor value of 'Transparent'
> however I get a build error when I do this (Transparent is not a valid
> background colour).
> Any ideas.

chart slows report render to crawl in Report Manager

I've searched the forums on this issue, haven't really found the answer.
I have several nifty little sales reports which crunch a ton of data quite
efficiently and render in just a few seconds in Report Manager. I've pushed
as much of the data processing back to the server as possible, use a stored
procedure (with parameters) in a shared datasource, don't return unneccessary
data, all that. It works great.
When I first developed the reports, I continued generating my charts (which
use the same data as the reports, just grouped differently) in Excel and
pasting them in as images. Now I want to stop that nonsense and use the SSRS
charts. I fooled around with the charting function and got a reasonable
facimile of my Excel charts, two per report, which use their own separate
stored procedures and the same shared datasource.
Now, reports that used to render in 5-8 seconds may take 1-5 MINUTES. Help!
It's definitely the charts--taking them back out fixes the problem.
I have complete control over the datasources--would it make more sense to
use non-shared sources, or to create totally separate shared sources? I saw
a post that recommended "making data calls non-synchronous," but I have no
idea how to do that.
Thanks for any suggestions.On further investigation, it appears that deleting EITHER ONE of the charts
brings the rendering time down almost to the same time as no chart at all.
It's apparent that having MULTIPLE charts on a page multiplies the rendering
time exponentially (I'm gonna tell Edward Tufte!)
This happens whether I put the charts side-by-side (preferred) or one above
the other on the page--they just take FOREVER to render.
Anybody...?|||RS 2005
I'm not sure how you mean your question about grouping. The charts each
have one grouping level, the table below them has three. Each component
(table, each chart separately) renders in roughly 5 seconds by itself. When
I put all three of them in the same report, the rendering time goes up to
well over one minute. Weird.
"Bruce L-C [MVP]" wrote:
> Hmmm, I don't know, I have a report with 3 charts and I don't see this
> issue. Do you have any grouping, anything special? Also, RS 2000 or RS 2005?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "elinde" <elinde@.discussions.microsoft.com> wrote in message
> news:301D52EC-E38F-423D-9A01-B4386A282B36@.microsoft.com...
> > On further investigation, it appears that deleting EITHER ONE of the
> > charts
> > brings the rendering time down almost to the same time as no chart at all.
> > It's apparent that having MULTIPLE charts on a page multiplies the
> > rendering
> > time exponentially (I'm gonna tell Edward Tufte!)
> >
> > This happens whether I put the charts side-by-side (preferred) or one
> > above
> > the other on the page--they just take FOREVER to render.
> >
> > Anybody...?
> >
>
>|||Is this deployed or in the IDE? If in the development environment try
deploying and see if that makes a difference in performance.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"elinde" <elinde@.discussions.microsoft.com> wrote in message
news:54603F35-85B8-405F-B979-DC03DD43AB16@.microsoft.com...
> RS 2005
> I'm not sure how you mean your question about grouping. The charts each
> have one grouping level, the table below them has three. Each component
> (table, each chart separately) renders in roughly 5 seconds by itself.
> When
> I put all three of them in the same report, the rendering time goes up to
> well over one minute. Weird.
> "Bruce L-C [MVP]" wrote:
>> Hmmm, I don't know, I have a report with 3 charts and I don't see this
>> issue. Do you have any grouping, anything special? Also, RS 2000 or RS
>> 2005?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "elinde" <elinde@.discussions.microsoft.com> wrote in message
>> news:301D52EC-E38F-423D-9A01-B4386A282B36@.microsoft.com...
>> > On further investigation, it appears that deleting EITHER ONE of the
>> > charts
>> > brings the rendering time down almost to the same time as no chart at
>> > all.
>> > It's apparent that having MULTIPLE charts on a page multiplies the
>> > rendering
>> > time exponentially (I'm gonna tell Edward Tufte!)
>> >
>> > This happens whether I put the charts side-by-side (preferred) or one
>> > above
>> > the other on the page--they just take FOREVER to render.
>> >
>> > Anybody...?
>> >
>>|||This is happening in deployment, unfortunately. The server is big, new &
fast--it's not that.
"Bruce L-C [MVP]" wrote:
> Is this deployed or in the IDE? If in the development environment try
> deploying and see if that makes a difference in performance.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "elinde" <elinde@.discussions.microsoft.com> wrote in message
> news:54603F35-85B8-405F-B979-DC03DD43AB16@.microsoft.com...
> > RS 2005
> >
> > I'm not sure how you mean your question about grouping. The charts each
> > have one grouping level, the table below them has three. Each component
> > (table, each chart separately) renders in roughly 5 seconds by itself.
> > When
> > I put all three of them in the same report, the rendering time goes up to
> > well over one minute. Weird.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Hmmm, I don't know, I have a report with 3 charts and I don't see this
> >> issue. Do you have any grouping, anything special? Also, RS 2000 or RS
> >> 2005?
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "elinde" <elinde@.discussions.microsoft.com> wrote in message
> >> news:301D52EC-E38F-423D-9A01-B4386A282B36@.microsoft.com...
> >> > On further investigation, it appears that deleting EITHER ONE of the
> >> > charts
> >> > brings the rendering time down almost to the same time as no chart at
> >> > all.
> >> > It's apparent that having MULTIPLE charts on a page multiplies the
> >> > rendering
> >> > time exponentially (I'm gonna tell Edward Tufte!)
> >> >
> >> > This happens whether I put the charts side-by-side (preferred) or one
> >> > above
> >> > the other on the page--they just take FOREVER to render.
> >> >
> >> > Anybody...?
> >> >
> >>
> >>
> >>
>
>|||Hmmm, I don't know, I have a report with 3 charts and I don't see this
issue. Do you have any grouping, anything special? Also, RS 2000 or RS 2005?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"elinde" <elinde@.discussions.microsoft.com> wrote in message
news:301D52EC-E38F-423D-9A01-B4386A282B36@.microsoft.com...
> On further investigation, it appears that deleting EITHER ONE of the
> charts
> brings the rendering time down almost to the same time as no chart at all.
> It's apparent that having MULTIPLE charts on a page multiplies the
> rendering
> time exponentially (I'm gonna tell Edward Tufte!)
> This happens whether I put the charts side-by-side (preferred) or one
> above
> the other on the page--they just take FOREVER to render.
> Anybody...?
>

Chart Series Total

I have a chart on one of my reports that has two series as a stracked bar
chart, and displays two point labels on each bar for the series value. What
I'd like to do is show the series value as a percentage relative to the total
bar value. The real problem here is I don't know how to get programmatically
get at the value shown in the point label, since this value calculated by
reporting services and not in my query (i.e. my query returns the total for
each bar, and SRS breaks it down into the two series values).
Any help would be appreciated.I haven't done many charts but can't you reference the label by
ReportItems!LabelName.Value.....I don't know if this is what you are looking
for.
"blabore" wrote:
> I have a chart on one of my reports that has two series as a stracked bar
> chart, and displays two point labels on each bar for the series value. What
> I'd like to do is show the series value as a percentage relative to the total
> bar value. The real problem here is I don't know how to get programmatically
> get at the value shown in the point label, since this value calculated by
> reporting services and not in my query (i.e. my query returns the total for
> each bar, and SRS breaks it down into the two series values).
> Any help would be appreciated.

Chart Resolution in PDF

When I export reports with charts to PDF, the server really takes a beating
and the resulting PDF file can get quite large. We would like to reduce the
resolution of the chart images that are being embedded in the PDF and/or
reduce the resolution of the PDF itself.
The help topic "Designing for PDF Output"
(ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/rptsrvr9/html/e266ff64-ce8e-427d-980d-a93c6a069b7d.htm)
mentions that the resolution of the PDF can be configured in device settings,
but the PDF Device Settings help topic does not mention any setting like this
(nor does the Image Device Settings help).
Any input is appreciated.
ScottI have been tracking down an answer to this question as well.
It looks like in older versions you could set the DpiX and DpiY. See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_soapapi_dev_8bld.asp
But this is not an option for SQL 2005. See
http://msdn2.microsoft.com/en-us/library/ms154682(SQL.90).aspx
If you find anything else that will prove this wrong, please post. Thanks!
"ScottB" wrote:
> When I export reports with charts to PDF, the server really takes a beating
> and the resulting PDF file can get quite large. We would like to reduce the
> resolution of the chart images that are being embedded in the PDF and/or
> reduce the resolution of the PDF itself.
> The help topic "Designing for PDF Output"
> (ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/rptsrvr9/html/e266ff64-ce8e-427d-980d-a93c6a069b7d.htm)
> mentions that the resolution of the PDF can be configured in device settings,
> but the PDF Device Settings help topic does not mention any setting like this
> (nor does the Image Device Settings help).
> Any input is appreciated.
> Scott|||We believe the problem is caused by defects in the 64-bit version of SQL
2005. When we moved back to 32-bit, this (and other) anomalies disappeared.
Scott
"shelley" wrote:
> I have been tracking down an answer to this question as well.
> It looks like in older versions you could set the DpiX and DpiY. See
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_soapapi_dev_8bld.asp
> But this is not an option for SQL 2005. See
> http://msdn2.microsoft.com/en-us/library/ms154682(SQL.90).aspx
> If you find anything else that will prove this wrong, please post. Thanks!
> "ScottB" wrote:
> > When I export reports with charts to PDF, the server really takes a beating
> > and the resulting PDF file can get quite large. We would like to reduce the
> > resolution of the chart images that are being embedded in the PDF and/or
> > reduce the resolution of the PDF itself.
> >
> > The help topic "Designing for PDF Output"
> > (ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/rptsrvr9/html/e266ff64-ce8e-427d-980d-a93c6a069b7d.htm)
> > mentions that the resolution of the PDF can be configured in device settings,
> > but the PDF Device Settings help topic does not mention any setting like this
> > (nor does the Image Device Settings help).
> >
> > Any input is appreciated.
> >
> > Scott

Chart in Crystal Reports

Hai Babu,

i want to draw graph in reports. i have X-Axis value in a Column and Y-Axis Value in another column.

By using chart wizzard in crystal reports in Data tab.

i assign on every change of X-Axis Value, show values of Y-Axis(problem started here)

by default it will give summarize value of that Y-Axis Column, there is a option Don't Summarize value but by default it is in disable mode. how to make it enable.

very urgent, reply as soon as possible.

with regards
-amjathDid you ever figure out how to fix this problem? I am having the same difficulties.

Chart does not display when Toolbar=False

I have a basic bar chart that runs fine on a local 32 bit server both within an IFRAME and within the reports manager. However, on another server that is brand new and clean install of SQL Server 2005 (64 bit) that same chart will run fine within reports manager but not through a direct link (whether in an IFRAME or not).

I narrowed the problem down to the parameter Toolbar=False. If I leave off that parameter, the report runs fine but includes the header. If I set the parameter to True, it also displays fine with a direct link. I can also specify Parameters=False and that works fine. However, no matter what I try, setting Toolbar=False returns a report (showing text in textboxes) but the chart itself returns as an image placeholder with the little red x.

Obviously, I do not want the user to see the toolbar within the application. I tried looking at the logs and event viewer for clues and found nothing.

Can someone please help?

Well it appears I have run across a bug: http://support.microsoft.com/?kbid=921405

Why would Microsoft not release this hotfix without having to call them if it is a known problem when not in anonymous mode and should have been included in the latest hotfix post-SP1 rollup release?

Does anyone know where I might can simply get this hotfix?

Chart Colours

Hi,

I have a problem relating to customising colours in SQL reporting services 2000. On one of my reports i have managed this no problem. This report however used series which were stored on individual fields in the data base. i.e.

Chanrt Type : Stacked column chart

Xaxis : dates

Yaxis : counts of various status' (Known, Unknown, Understood, Designed, Verified)

each of the status types has a seperate field in the database to store the count on a given date.

The new version changes the structure of the database. I now hav a date field as before, but now a Status field (stores whatever status for that date), and a Statuscount field (storing the count for that status on the given date).

My chart now has an X axis of dates and Y axis of the value of the status field. The difference here is that there are not individual series from which i can change the colour.

Does anyone know of a way to customise the colour of this chart? I appreciate i may not have explained myself too well so apologies if this post makes no sense.

Many thanks in advance

Grant

Hi again,

Nevermind. I have managed to resolve the problem. It seems that i needed to put a function into the code section of the report properties which selects a colour depending on the value of the series filed. I can then call this function from the series style fill attribute. Seems to work just perfect.

Cheers

Grant

Chart borders not hiding when no data is returned

I do not have much experience with reporting services or Visual Studio.
I'm trying to upgrade some reports from reporting services 2000 to
reporting services 2005. One of the reports I'm working on contains 5
charts. In the 2000 environment when no data is returned for a single
chart, the chart is not displayed and the next chart that contains data
is "moved up" in the display. In 2005 when no data is returned for a
chart, a blank chart (basically an outline) is displayed. So may I end
up seeing a chart or two, then a blank area, and then another chart
instead of seeing all charts that return data displayed sequentially.
I hope that wasn't too confusing and that someone may be able to help
me with this.I want to clear this up a little... bascially I'm getting blank charts
as placeholders in the report. If no data is returned then I do not
want an empty chart /placeholder... instead I want charts with data to
shift up.|||Use an expression on the Visibility --> Hidden property for the chart
and test for rows returned, something like:
=IIF(rownumber("YourQuery") = 0, True, False)
I've also hit this same issue after upgrading reports from RS2000 to
RS2005.
Matt A
Rabbit wrote:
> I want to clear this up a little... bascially I'm getting blank charts
> as placeholders in the report. If no data is returned then I do not
> want an empty chart /placeholder... instead I want charts with data to
> shift up.

Chart and image rendering problem

Hello,
Has anybody found a solution to the problem where images and charts doesn't
show when the report is opened from ../Reports, but is OK in Visual Studio,
../Reportserver and in subscriptions?
Best regards,
VemundPlease check this related posting:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=628a4607-e2b1-49ad-ad0b-37f3c2f148d9&sloc=en-us
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Vemund Haga" <vemund.haga@.nospam.nospam> wrote in message
news:uDYyXUYlEHA.3760@.TK2MSFTNGP12.phx.gbl...
> Hello,
> Has anybody found a solution to the problem where images and charts
doesn't
> show when the report is opened from ../Reports, but is OK in Visual
Studio,
> ../Reportserver and in subscriptions?
>
> Best regards,
> Vemund
>|||Hello,
Many thanks for the answer.
The solution to my problem was to disable the use of session cookies by
modifying the ConfigurationInfo table. Even though I was already using the
IP address in the configuration files, all images and charts are now
displaying correctly from the Report Manager.
Best regards,
Vemund
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:e4xg3hulEHA.3760@.TK2MSFTNGP12.phx.gbl...
> Please check this related posting:
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=628a4607-e2b1-49ad-ad0b-37f3c2f148d9&sloc=en-us
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Vemund Haga" <vemund.haga@.nospam.nospam> wrote in message
> news:uDYyXUYlEHA.3760@.TK2MSFTNGP12.phx.gbl...
>> Hello,
>> Has anybody found a solution to the problem where images and charts
> doesn't
>> show when the report is opened from ../Reports, but is OK in Visual
> Studio,
>> ../Reportserver and in subscriptions?
>>
>> Best regards,
>> Vemund
>>
>|||i was having the same problem and i had an underscore in
my server name once i removed the underscore the images
and charts all showed up but some of them were not lined
up as they were in visual studio
>--Original Message--
>Hello,
>Has anybody found a solution to the problem where images
and charts doesn't
>show when the report is opened from ../Reports, but is OK
in Visual Studio,
>.../Reportserver and in subscriptions?
>
>Best regards,
>Vemund
>
>.
>

charlist_to_table for mvp function

Hi, I found the following function on this site and am trying to use it my
reports.
The dataset for my mvp is different from my stored proc I'm using.
The data set for my mvp is simple
codes dataset = select distinct codes from tbl_codes
values are
AAA-2222
BBB-3333
CCC-444
In my stored procedure I call the function
select * from dbo.tbl_codes as a
where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
the issue is that it only retrives the first code instead of all three.
this is how I test it:
select nstr from charlist_to_table
('AAA-2222,
BBB-3333,
CCC-444
',',')
I get the following
AAA-2222,
BBB-3333,
CCC-444
I don't think the function is working in the sp because there is a space in
front of the values. Even when I put a space in the before the codes data
set I still only get the data for the first code AAA-2222.
Am I missing something in the code below. Thanks, Lisa
CREATE FUNCTION [dbo].[charlist_to_table]
(@.list ntext, @.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
ENDI call it using default keyword.
select str from charlist_to_talbe(@.codes,default)
I use a join.
select a.* from dbo.tbl_codes a inner join charlist_to_table(@.CODES,Default)
b on a.codes = b.str
change b.str to b.nstr depending on the datatype of a.codes.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
> Hi, I found the following function on this site and am trying to use it my
> reports.
> The dataset for my mvp is different from my stored proc I'm using.
> The data set for my mvp is simple
> codes dataset => select distinct codes from tbl_codes
> values are
> AAA-2222
> BBB-3333
> CCC-444
> In my stored procedure I call the function
> select * from dbo.tbl_codes as a
> where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
> the issue is that it only retrives the first code instead of all three.
> this is how I test it:
> select nstr from charlist_to_table
> ('AAA-2222,
> BBB-3333,
> CCC-444
> ',',')
> I get the following
> AAA-2222,
> BBB-3333,
> CCC-444
> I don't think the function is working in the sp because there is a space
> in
> front of the values. Even when I put a space in the before the codes data
> set I still only get the data for the first code AAA-2222.
> Am I missing something in the code below. Thanks, Lisa
> CREATE FUNCTION [dbo].[charlist_to_table]
> (@.list ntext, @.delimiter nchar(1) = N',')
> RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> str varchar(4000),
> nstr nvarchar(2000)) AS
> BEGIN
> DECLARE @.pos int,
> @.textpos int,
> @.chunklen smallint,
> @.tmpstr nvarchar(4000),
> @.leftover nvarchar(4000),
> @.tmpval nvarchar(4000)
> SET @.textpos = 1
> SET @.leftover = ''
> WHILE @.textpos <= datalength(@.list) / 2
> BEGIN
> SET @.chunklen = 4000 - datalength(@.leftover) / 2
> SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> SET @.textpos = @.textpos + @.chunklen
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> WHILE @.pos > 0
> BEGIN
> SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> END
> SET @.leftover = @.tmpstr
> END
> INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> ltrim(rtrim(@.leftover)))
> RETURN
> END|||Thanks for your help. I understand, but there is still something missing.
see the test
declare @.codes varchar(50)
select
@.codes = ('SWA35-2948,
SWAP2-2892,
SWA27-2946,
GRE1-2936,
ADM2-2930,
SWA28-2938,
SWUA2-2938,
SWA31-2948,
SWAP4-2950,
SWUA3-2938)
--test
print @.codes
this come out correct
SWA35-2948,
SWAP2-2892,
SWA27-2946,
GRE1-2936,
ADM2-2930,
SWA28-2938,
SWUA2-2938,
SWA31-2948,
SWAP4-2950,
SWUA3-2938
but when I run this
select * from charlist_to_table(@.promo_code,default)
I get the following
listpos str nstr
1 SWA35-2948 SWA35-2948
2 SWAP2-2892 SWAP2-2892
3 SWA27-2946 SWA27-2946
4 GRE1-2936 GRE1-2936
5
I should have 10 listpos and there still spaces in front out the other values.
so this only returns the first row's value for code SWA35-2948
select a.* from dbo.swp_camps as a
inner join dbo.charlist_to_table(@.codes,Default) as b on a.codes = b.nstr
Any suggestions. Thanks, Lisa
"Bruce L-C [MVP]" wrote:
> I call it using default keyword.
> select str from charlist_to_talbe(@.codes,default)
> I use a join.
> select a.* from dbo.tbl_codes a inner join charlist_to_table(@.CODES,Default)
> b on a.codes = b.str
> change b.str to b.nstr depending on the datatype of a.codes.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
> > Hi, I found the following function on this site and am trying to use it my
> > reports.
> > The dataset for my mvp is different from my stored proc I'm using.
> > The data set for my mvp is simple
> >
> > codes dataset => > select distinct codes from tbl_codes
> >
> > values are
> > AAA-2222
> > BBB-3333
> > CCC-444
> >
> > In my stored procedure I call the function
> >
> > select * from dbo.tbl_codes as a
> > where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
> >
> > the issue is that it only retrives the first code instead of all three.
> >
> > this is how I test it:
> > select nstr from charlist_to_table
> > ('AAA-2222,
> > BBB-3333,
> > CCC-444
> > ',',')
> >
> > I get the following
> > AAA-2222,
> > BBB-3333,
> > CCC-444
> >
> > I don't think the function is working in the sp because there is a space
> > in
> > front of the values. Even when I put a space in the before the codes data
> > set I still only get the data for the first code AAA-2222.
> >
> > Am I missing something in the code below. Thanks, Lisa
> >
> > CREATE FUNCTION [dbo].[charlist_to_table]
> > (@.list ntext, @.delimiter nchar(1) = N',')
> >
> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> > str varchar(4000),
> > nstr nvarchar(2000)) AS
> > BEGIN
> > DECLARE @.pos int,
> > @.textpos int,
> > @.chunklen smallint,
> > @.tmpstr nvarchar(4000),
> > @.leftover nvarchar(4000),
> > @.tmpval nvarchar(4000)
> > SET @.textpos = 1
> > SET @.leftover = ''
> > WHILE @.textpos <= datalength(@.list) / 2
> > BEGIN
> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> > SET @.textpos = @.textpos + @.chunklen
> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> > WHILE @.pos > 0
> > BEGIN
> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> > END
> > SET @.leftover = @.tmpstr
> > END
> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> > ltrim(rtrim(@.leftover)))
> > RETURN
> > END
>
>|||I meant this above
select * from charlist_to_table(@.codes,default)
"Lisa" wrote:
> Thanks for your help. I understand, but there is still something missing.
>
> see the test
> declare @.codes varchar(50)
> select
> @.codes = ('SWA35-2948,
> SWAP2-2892,
> SWA27-2946,
> GRE1-2936,
> ADM2-2930,
> SWA28-2938,
> SWUA2-2938,
> SWA31-2948,
> SWAP4-2950,
> SWUA3-2938)
> --test
> print @.codes
> this come out correct
> SWA35-2948,
> SWAP2-2892,
> SWA27-2946,
> GRE1-2936,
> ADM2-2930,
> SWA28-2938,
> SWUA2-2938,
> SWA31-2948,
> SWAP4-2950,
> SWUA3-2938
> but when I run this
> select * from charlist_to_table(@.promo_code,default)
> I get the following
> listpos str nstr
> 1 SWA35-2948 SWA35-2948
> 2 SWAP2-2892 SWAP2-2892
> 3 SWA27-2946 SWA27-2946
> 4 GRE1-2936 GRE1-2936
> 5
>
> I should have 10 listpos and there still spaces in front out the other values.
> so this only returns the first row's value for code SWA35-2948
> select a.* from dbo.swp_camps as a
> inner join dbo.charlist_to_table(@.codes,Default) as b on a.codes = b.nstr
>
> Any suggestions. Thanks, Lisa
> "Bruce L-C [MVP]" wrote:
> > I call it using default keyword.
> >
> > select str from charlist_to_talbe(@.codes,default)
> >
> > I use a join.
> >
> > select a.* from dbo.tbl_codes a inner join charlist_to_table(@.CODES,Default)
> > b on a.codes = b.str
> >
> > change b.str to b.nstr depending on the datatype of a.codes.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> > news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
> > > Hi, I found the following function on this site and am trying to use it my
> > > reports.
> > > The dataset for my mvp is different from my stored proc I'm using.
> > > The data set for my mvp is simple
> > >
> > > codes dataset => > > select distinct codes from tbl_codes
> > >
> > > values are
> > > AAA-2222
> > > BBB-3333
> > > CCC-444
> > >
> > > In my stored procedure I call the function
> > >
> > > select * from dbo.tbl_codes as a
> > > where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
> > >
> > > the issue is that it only retrives the first code instead of all three.
> > >
> > > this is how I test it:
> > > select nstr from charlist_to_table
> > > ('AAA-2222,
> > > BBB-3333,
> > > CCC-444
> > > ',',')
> > >
> > > I get the following
> > > AAA-2222,
> > > BBB-3333,
> > > CCC-444
> > >
> > > I don't think the function is working in the sp because there is a space
> > > in
> > > front of the values. Even when I put a space in the before the codes data
> > > set I still only get the data for the first code AAA-2222.
> > >
> > > Am I missing something in the code below. Thanks, Lisa
> > >
> > > CREATE FUNCTION [dbo].[charlist_to_table]
> > > (@.list ntext, @.delimiter nchar(1) = N',')
> > >
> > > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> > > str varchar(4000),
> > > nstr nvarchar(2000)) AS
> > > BEGIN
> > > DECLARE @.pos int,
> > > @.textpos int,
> > > @.chunklen smallint,
> > > @.tmpstr nvarchar(4000),
> > > @.leftover nvarchar(4000),
> > > @.tmpval nvarchar(4000)
> > > SET @.textpos = 1
> > > SET @.leftover = ''
> > > WHILE @.textpos <= datalength(@.list) / 2
> > > BEGIN
> > > SET @.chunklen = 4000 - datalength(@.leftover) / 2
> > > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> > > SET @.textpos = @.textpos + @.chunklen
> > > SET @.pos = charindex(@.delimiter, @.tmpstr)
> > > WHILE @.pos > 0
> > > BEGIN
> > > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> > > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> > > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> > > SET @.pos = charindex(@.delimiter, @.tmpstr)
> > > END
> > > SET @.leftover = @.tmpstr
> > > END
> > > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> > > ltrim(rtrim(@.leftover)))
> > > RETURN
> > > END
> >
> >
> >|||Make your @.codes larger. At least for the below that is why it is not
working.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:53AD8EAF-4D39-4C63-8725-BD07E3CBA637@.microsoft.com...
> Thanks for your help. I understand, but there is still something missing.
>
> see the test
> declare @.codes varchar(50)
> select
> @.codes = ('SWA35-2948,
> SWAP2-2892,
> SWA27-2946,
> GRE1-2936,
> ADM2-2930,
> SWA28-2938,
> SWUA2-2938,
> SWA31-2948,
> SWAP4-2950,
> SWUA3-2938)
> --test
> print @.codes
> this come out correct
> SWA35-2948,
> SWAP2-2892,
> SWA27-2946,
> GRE1-2936,
> ADM2-2930,
> SWA28-2938,
> SWUA2-2938,
> SWA31-2948,
> SWAP4-2950,
> SWUA3-2938
> but when I run this
> select * from charlist_to_table(@.promo_code,default)
> I get the following
> listpos str nstr
> 1 SWA35-2948 SWA35-2948
> 2 SWAP2-2892 SWAP2-2892
> 3 SWA27-2946 SWA27-2946
> 4 GRE1-2936 GRE1-2936
> 5
>
> I should have 10 listpos and there still spaces in front out the other
> values.
> so this only returns the first row's value for code SWA35-2948
> select a.* from dbo.swp_camps as a
> inner join dbo.charlist_to_table(@.codes,Default) as b on a.codes = b.nstr
>
> Any suggestions. Thanks, Lisa
> "Bruce L-C [MVP]" wrote:
>> I call it using default keyword.
>> select str from charlist_to_talbe(@.codes,default)
>> I use a join.
>> select a.* from dbo.tbl_codes a inner join
>> charlist_to_table(@.CODES,Default)
>> b on a.codes = b.str
>> change b.str to b.nstr depending on the datatype of a.codes.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
>> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
>> > Hi, I found the following function on this site and am trying to use it
>> > my
>> > reports.
>> > The dataset for my mvp is different from my stored proc I'm using.
>> > The data set for my mvp is simple
>> >
>> > codes dataset =>> > select distinct codes from tbl_codes
>> >
>> > values are
>> > AAA-2222
>> > BBB-3333
>> > CCC-444
>> >
>> > In my stored procedure I call the function
>> >
>> > select * from dbo.tbl_codes as a
>> > where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
>> >
>> > the issue is that it only retrives the first code instead of all three.
>> >
>> > this is how I test it:
>> > select nstr from charlist_to_table
>> > ('AAA-2222,
>> > BBB-3333,
>> > CCC-444
>> > ',',')
>> >
>> > I get the following
>> > AAA-2222,
>> > BBB-3333,
>> > CCC-444
>> >
>> > I don't think the function is working in the sp because there is a
>> > space
>> > in
>> > front of the values. Even when I put a space in the before the codes
>> > data
>> > set I still only get the data for the first code AAA-2222.
>> >
>> > Am I missing something in the code below. Thanks, Lisa
>> >
>> > CREATE FUNCTION [dbo].[charlist_to_table]
>> > (@.list ntext, @.delimiter nchar(1) = N',')
>> >
>> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
>> > str varchar(4000),
>> > nstr nvarchar(2000)) AS
>> > BEGIN
>> > DECLARE @.pos int,
>> > @.textpos int,
>> > @.chunklen smallint,
>> > @.tmpstr nvarchar(4000),
>> > @.leftover nvarchar(4000),
>> > @.tmpval nvarchar(4000)
>> > SET @.textpos = 1
>> > SET @.leftover = ''
>> > WHILE @.textpos <= datalength(@.list) / 2
>> > BEGIN
>> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
>> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
>> > SET @.textpos = @.textpos + @.chunklen
>> > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> > WHILE @.pos > 0
>> > BEGIN
>> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
>> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
>> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
>> > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> > END
>> > SET @.leftover = @.tmpstr
>> > END
>> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
>> > ltrim(rtrim(@.leftover)))
>> > RETURN
>> > END
>>|||thanks, that worked. But, I still have the space issue
listpos str nstr
1 SWA35-2948 SWA35-2948
2 SWAP2-2892 SWAP2-2892
3 SWA27-2946 SWA27-2946
in the str and nstr fields all but the first row has spaces in front of the
value. This is why it's only returning the first row. thanks for you help.
"Bruce L-C [MVP]" wrote:
> Make your @.codes larger. At least for the below that is why it is not
> working.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> news:53AD8EAF-4D39-4C63-8725-BD07E3CBA637@.microsoft.com...
> > Thanks for your help. I understand, but there is still something missing.
> >
> >
> > see the test
> > declare @.codes varchar(50)
> >
> > select
> > @.codes = ('SWA35-2948,
> > SWAP2-2892,
> > SWA27-2946,
> > GRE1-2936,
> > ADM2-2930,
> > SWA28-2938,
> > SWUA2-2938,
> > SWA31-2948,
> > SWAP4-2950,
> > SWUA3-2938)
> > --test
> > print @.codes
> > this come out correct
> > SWA35-2948,
> > SWAP2-2892,
> > SWA27-2946,
> > GRE1-2936,
> > ADM2-2930,
> > SWA28-2938,
> > SWUA2-2938,
> > SWA31-2948,
> > SWAP4-2950,
> > SWUA3-2938
> >
> > but when I run this
> > select * from charlist_to_table(@.promo_code,default)
> > I get the following
> >
> > listpos str nstr
> > 1 SWA35-2948 SWA35-2948
> > 2 SWAP2-2892 SWAP2-2892
> > 3 SWA27-2946 SWA27-2946
> > 4 GRE1-2936 GRE1-2936
> > 5
> >
> >
> > I should have 10 listpos and there still spaces in front out the other
> > values.
> > so this only returns the first row's value for code SWA35-2948
> > select a.* from dbo.swp_camps as a
> > inner join dbo.charlist_to_table(@.codes,Default) as b on a.codes = b.nstr
> >
> >
> > Any suggestions. Thanks, Lisa
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> I call it using default keyword.
> >>
> >> select str from charlist_to_talbe(@.codes,default)
> >>
> >> I use a join.
> >>
> >> select a.* from dbo.tbl_codes a inner join
> >> charlist_to_table(@.CODES,Default)
> >> b on a.codes = b.str
> >>
> >> change b.str to b.nstr depending on the datatype of a.codes.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> >> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
> >> > Hi, I found the following function on this site and am trying to use it
> >> > my
> >> > reports.
> >> > The dataset for my mvp is different from my stored proc I'm using.
> >> > The data set for my mvp is simple
> >> >
> >> > codes dataset => >> > select distinct codes from tbl_codes
> >> >
> >> > values are
> >> > AAA-2222
> >> > BBB-3333
> >> > CCC-444
> >> >
> >> > In my stored procedure I call the function
> >> >
> >> > select * from dbo.tbl_codes as a
> >> > where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
> >> >
> >> > the issue is that it only retrives the first code instead of all three.
> >> >
> >> > this is how I test it:
> >> > select nstr from charlist_to_table
> >> > ('AAA-2222,
> >> > BBB-3333,
> >> > CCC-444
> >> > ',',')
> >> >
> >> > I get the following
> >> > AAA-2222,
> >> > BBB-3333,
> >> > CCC-444
> >> >
> >> > I don't think the function is working in the sp because there is a
> >> > space
> >> > in
> >> > front of the values. Even when I put a space in the before the codes
> >> > data
> >> > set I still only get the data for the first code AAA-2222.
> >> >
> >> > Am I missing something in the code below. Thanks, Lisa
> >> >
> >> > CREATE FUNCTION [dbo].[charlist_to_table]
> >> > (@.list ntext, @.delimiter nchar(1) = N',')
> >> >
> >> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> >> > str varchar(4000),
> >> > nstr nvarchar(2000)) AS
> >> > BEGIN
> >> > DECLARE @.pos int,
> >> > @.textpos int,
> >> > @.chunklen smallint,
> >> > @.tmpstr nvarchar(4000),
> >> > @.leftover nvarchar(4000),
> >> > @.tmpval nvarchar(4000)
> >> > SET @.textpos = 1
> >> > SET @.leftover = ''
> >> > WHILE @.textpos <= datalength(@.list) / 2
> >> > BEGIN
> >> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
> >> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> >> > SET @.textpos = @.textpos + @.chunklen
> >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> >> > WHILE @.pos > 0
> >> > BEGIN
> >> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> >> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> >> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> >> > END
> >> > SET @.leftover = @.tmpstr
> >> > END
> >> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> >> > ltrim(rtrim(@.leftover)))
> >> > RETURN
> >> > END
> >>
> >>
> >>
>
>|||never mind. It actually worked when I ran within the sp in ssrs. thanks.
Before I was testing it in query analyzer.
"Lisa" wrote:
> thanks, that worked. But, I still have the space issue
> listpos str nstr
> 1 SWA35-2948 SWA35-2948
> 2 SWAP2-2892 SWAP2-2892
> 3 SWA27-2946 SWA27-2946
>
> in the str and nstr fields all but the first row has spaces in front of the
> value. This is why it's only returning the first row. thanks for you help.
> "Bruce L-C [MVP]" wrote:
> > Make your @.codes larger. At least for the below that is why it is not
> > working.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> > news:53AD8EAF-4D39-4C63-8725-BD07E3CBA637@.microsoft.com...
> > > Thanks for your help. I understand, but there is still something missing.
> > >
> > >
> > > see the test
> > > declare @.codes varchar(50)
> > >
> > > select
> > > @.codes = ('SWA35-2948,
> > > SWAP2-2892,
> > > SWA27-2946,
> > > GRE1-2936,
> > > ADM2-2930,
> > > SWA28-2938,
> > > SWUA2-2938,
> > > SWA31-2948,
> > > SWAP4-2950,
> > > SWUA3-2938)
> > > --test
> > > print @.codes
> > > this come out correct
> > > SWA35-2948,
> > > SWAP2-2892,
> > > SWA27-2946,
> > > GRE1-2936,
> > > ADM2-2930,
> > > SWA28-2938,
> > > SWUA2-2938,
> > > SWA31-2948,
> > > SWAP4-2950,
> > > SWUA3-2938
> > >
> > > but when I run this
> > > select * from charlist_to_table(@.promo_code,default)
> > > I get the following
> > >
> > > listpos str nstr
> > > 1 SWA35-2948 SWA35-2948
> > > 2 SWAP2-2892 SWAP2-2892
> > > 3 SWA27-2946 SWA27-2946
> > > 4 GRE1-2936 GRE1-2936
> > > 5
> > >
> > >
> > > I should have 10 listpos and there still spaces in front out the other
> > > values.
> > > so this only returns the first row's value for code SWA35-2948
> > > select a.* from dbo.swp_camps as a
> > > inner join dbo.charlist_to_table(@.codes,Default) as b on a.codes = b.nstr
> > >
> > >
> > > Any suggestions. Thanks, Lisa
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > >> I call it using default keyword.
> > >>
> > >> select str from charlist_to_talbe(@.codes,default)
> > >>
> > >> I use a join.
> > >>
> > >> select a.* from dbo.tbl_codes a inner join
> > >> charlist_to_table(@.CODES,Default)
> > >> b on a.codes = b.str
> > >>
> > >> change b.str to b.nstr depending on the datatype of a.codes.
> > >>
> > >>
> > >> --
> > >> Bruce Loehle-Conger
> > >> MVP SQL Server Reporting Services
> > >>
> > >> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> > >> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
> > >> > Hi, I found the following function on this site and am trying to use it
> > >> > my
> > >> > reports.
> > >> > The dataset for my mvp is different from my stored proc I'm using.
> > >> > The data set for my mvp is simple
> > >> >
> > >> > codes dataset => > >> > select distinct codes from tbl_codes
> > >> >
> > >> > values are
> > >> > AAA-2222
> > >> > BBB-3333
> > >> > CCC-444
> > >> >
> > >> > In my stored procedure I call the function
> > >> >
> > >> > select * from dbo.tbl_codes as a
> > >> > where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
> > >> >
> > >> > the issue is that it only retrives the first code instead of all three.
> > >> >
> > >> > this is how I test it:
> > >> > select nstr from charlist_to_table
> > >> > ('AAA-2222,
> > >> > BBB-3333,
> > >> > CCC-444
> > >> > ',',')
> > >> >
> > >> > I get the following
> > >> > AAA-2222,
> > >> > BBB-3333,
> > >> > CCC-444
> > >> >
> > >> > I don't think the function is working in the sp because there is a
> > >> > space
> > >> > in
> > >> > front of the values. Even when I put a space in the before the codes
> > >> > data
> > >> > set I still only get the data for the first code AAA-2222.
> > >> >
> > >> > Am I missing something in the code below. Thanks, Lisa
> > >> >
> > >> > CREATE FUNCTION [dbo].[charlist_to_table]
> > >> > (@.list ntext, @.delimiter nchar(1) = N',')
> > >> >
> > >> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> > >> > str varchar(4000),
> > >> > nstr nvarchar(2000)) AS
> > >> > BEGIN
> > >> > DECLARE @.pos int,
> > >> > @.textpos int,
> > >> > @.chunklen smallint,
> > >> > @.tmpstr nvarchar(4000),
> > >> > @.leftover nvarchar(4000),
> > >> > @.tmpval nvarchar(4000)
> > >> > SET @.textpos = 1
> > >> > SET @.leftover = ''
> > >> > WHILE @.textpos <= datalength(@.list) / 2
> > >> > BEGIN
> > >> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
> > >> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> > >> > SET @.textpos = @.textpos + @.chunklen
> > >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> > >> > WHILE @.pos > 0
> > >> > BEGIN
> > >> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> > >> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> > >> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> > >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> > >> > END
> > >> > SET @.leftover = @.tmpstr
> > >> > END
> > >> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> > >> > ltrim(rtrim(@.leftover)))
> > >> > RETURN
> > >> > END
> > >>
> > >>
> > >>
> >
> >
> >|||Are you putting it on separate lines when you do your test?
select nstr from charlist_to_table
('AAA-2222,
BBB-3333,
CCC-444
',',')
Since you are enclosing the whole thing in a string it is included the
carriage return (which will look like a blank). Do it like this:
select nstr from charlist_to_table
('AAA-2222,BBB-3333,CCC-444',',')
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:FEB9528A-30AB-44DC-A9FD-DBA43412B2CD@.microsoft.com...
> thanks, that worked. But, I still have the space issue
> listpos str nstr
> 1 SWA35-2948 SWA35-2948
> 2 SWAP2-2892 SWAP2-2892
> 3 SWA27-2946 SWA27-2946
>
> in the str and nstr fields all but the first row has spaces in front of
> the
> value. This is why it's only returning the first row. thanks for you
> help.
> "Bruce L-C [MVP]" wrote:
>> Make your @.codes larger. At least for the below that is why it is not
>> working.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
>> news:53AD8EAF-4D39-4C63-8725-BD07E3CBA637@.microsoft.com...
>> > Thanks for your help. I understand, but there is still something
>> > missing.
>> >
>> >
>> > see the test
>> > declare @.codes varchar(50)
>> >
>> > select
>> > @.codes = ('SWA35-2948,
>> > SWAP2-2892,
>> > SWA27-2946,
>> > GRE1-2936,
>> > ADM2-2930,
>> > SWA28-2938,
>> > SWUA2-2938,
>> > SWA31-2948,
>> > SWAP4-2950,
>> > SWUA3-2938)
>> > --test
>> > print @.codes
>> > this come out correct
>> > SWA35-2948,
>> > SWAP2-2892,
>> > SWA27-2946,
>> > GRE1-2936,
>> > ADM2-2930,
>> > SWA28-2938,
>> > SWUA2-2938,
>> > SWA31-2948,
>> > SWAP4-2950,
>> > SWUA3-2938
>> >
>> > but when I run this
>> > select * from charlist_to_table(@.promo_code,default)
>> > I get the following
>> >
>> > listpos str nstr
>> > 1 SWA35-2948 SWA35-2948
>> > 2 SWAP2-2892 SWAP2-2892
>> > 3 SWA27-2946 SWA27-2946
>> > 4 GRE1-2936 GRE1-2936
>> > 5
>> >
>> >
>> > I should have 10 listpos and there still spaces in front out the other
>> > values.
>> > so this only returns the first row's value for code SWA35-2948
>> > select a.* from dbo.swp_camps as a
>> > inner join dbo.charlist_to_table(@.codes,Default) as b on a.codes =>> > b.nstr
>> >
>> >
>> > Any suggestions. Thanks, Lisa
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> I call it using default keyword.
>> >>
>> >> select str from charlist_to_talbe(@.codes,default)
>> >>
>> >> I use a join.
>> >>
>> >> select a.* from dbo.tbl_codes a inner join
>> >> charlist_to_table(@.CODES,Default)
>> >> b on a.codes = b.str
>> >>
>> >> change b.str to b.nstr depending on the datatype of a.codes.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
>> >> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
>> >> > Hi, I found the following function on this site and am trying to use
>> >> > it
>> >> > my
>> >> > reports.
>> >> > The dataset for my mvp is different from my stored proc I'm using.
>> >> > The data set for my mvp is simple
>> >> >
>> >> > codes dataset =>> >> > select distinct codes from tbl_codes
>> >> >
>> >> > values are
>> >> > AAA-2222
>> >> > BBB-3333
>> >> > CCC-444
>> >> >
>> >> > In my stored procedure I call the function
>> >> >
>> >> > select * from dbo.tbl_codes as a
>> >> > where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
>> >> >
>> >> > the issue is that it only retrives the first code instead of all
>> >> > three.
>> >> >
>> >> > this is how I test it:
>> >> > select nstr from charlist_to_table
>> >> > ('AAA-2222,
>> >> > BBB-3333,
>> >> > CCC-444
>> >> > ',',')
>> >> >
>> >> > I get the following
>> >> > AAA-2222,
>> >> > BBB-3333,
>> >> > CCC-444
>> >> >
>> >> > I don't think the function is working in the sp because there is a
>> >> > space
>> >> > in
>> >> > front of the values. Even when I put a space in the before the
>> >> > codes
>> >> > data
>> >> > set I still only get the data for the first code AAA-2222.
>> >> >
>> >> > Am I missing something in the code below. Thanks, Lisa
>> >> >
>> >> > CREATE FUNCTION [dbo].[charlist_to_table]
>> >> > (@.list ntext, @.delimiter nchar(1) = N',')
>> >> >
>> >> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
>> >> > str varchar(4000),
>> >> > nstr nvarchar(2000)) AS
>> >> > BEGIN
>> >> > DECLARE @.pos int,
>> >> > @.textpos int,
>> >> > @.chunklen smallint,
>> >> > @.tmpstr nvarchar(4000),
>> >> > @.leftover nvarchar(4000),
>> >> > @.tmpval nvarchar(4000)
>> >> > SET @.textpos = 1
>> >> > SET @.leftover = ''
>> >> > WHILE @.textpos <= datalength(@.list) / 2
>> >> > BEGIN
>> >> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
>> >> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
>> >> > SET @.textpos = @.textpos + @.chunklen
>> >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> >> > WHILE @.pos > 0
>> >> > BEGIN
>> >> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
>> >> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
>> >> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
>> >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> >> > END
>> >> > SET @.leftover = @.tmpstr
>> >> > END
>> >> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
>> >> > ltrim(rtrim(@.leftover)))
>> >> > RETURN
>> >> > END
>> >>
>> >>
>> >>
>>|||I bet it was the issue with the carriage return.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:0B5C65E7-8118-49B2-A4D7-A30ACCD372D5@.microsoft.com...
> never mind. It actually worked when I ran within the sp in ssrs. thanks.
> Before I was testing it in query analyzer.
> "Lisa" wrote:
>> thanks, that worked. But, I still have the space issue
>> listpos str nstr
>> 1 SWA35-2948 SWA35-2948
>> 2 SWAP2-2892 SWAP2-2892
>> 3 SWA27-2946 SWA27-2946
>>
>> in the str and nstr fields all but the first row has spaces in front of
>> the
>> value. This is why it's only returning the first row. thanks for you
>> help.
>> "Bruce L-C [MVP]" wrote:
>> > Make your @.codes larger. At least for the below that is why it is not
>> > working.
>> >
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
>> > news:53AD8EAF-4D39-4C63-8725-BD07E3CBA637@.microsoft.com...
>> > > Thanks for your help. I understand, but there is still something
>> > > missing.
>> > >
>> > >
>> > > see the test
>> > > declare @.codes varchar(50)
>> > >
>> > > select
>> > > @.codes = ('SWA35-2948,
>> > > SWAP2-2892,
>> > > SWA27-2946,
>> > > GRE1-2936,
>> > > ADM2-2930,
>> > > SWA28-2938,
>> > > SWUA2-2938,
>> > > SWA31-2948,
>> > > SWAP4-2950,
>> > > SWUA3-2938)
>> > > --test
>> > > print @.codes
>> > > this come out correct
>> > > SWA35-2948,
>> > > SWAP2-2892,
>> > > SWA27-2946,
>> > > GRE1-2936,
>> > > ADM2-2930,
>> > > SWA28-2938,
>> > > SWUA2-2938,
>> > > SWA31-2948,
>> > > SWAP4-2950,
>> > > SWUA3-2938
>> > >
>> > > but when I run this
>> > > select * from charlist_to_table(@.promo_code,default)
>> > > I get the following
>> > >
>> > > listpos str nstr
>> > > 1 SWA35-2948 SWA35-2948
>> > > 2 SWAP2-2892 SWAP2-2892
>> > > 3 SWA27-2946 SWA27-2946
>> > > 4 GRE1-2936 GRE1-2936
>> > > 5
>> > >
>> > >
>> > > I should have 10 listpos and there still spaces in front out the
>> > > other
>> > > values.
>> > > so this only returns the first row's value for code SWA35-2948
>> > > select a.* from dbo.swp_camps as a
>> > > inner join dbo.charlist_to_table(@.codes,Default) as b on a.codes =>> > > b.nstr
>> > >
>> > >
>> > > Any suggestions. Thanks, Lisa
>> > >
>> > > "Bruce L-C [MVP]" wrote:
>> > >
>> > >> I call it using default keyword.
>> > >>
>> > >> select str from charlist_to_talbe(@.codes,default)
>> > >>
>> > >> I use a join.
>> > >>
>> > >> select a.* from dbo.tbl_codes a inner join
>> > >> charlist_to_table(@.CODES,Default)
>> > >> b on a.codes = b.str
>> > >>
>> > >> change b.str to b.nstr depending on the datatype of a.codes.
>> > >>
>> > >>
>> > >> --
>> > >> Bruce Loehle-Conger
>> > >> MVP SQL Server Reporting Services
>> > >>
>> > >> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
>> > >> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
>> > >> > Hi, I found the following function on this site and am trying to
>> > >> > use it
>> > >> > my
>> > >> > reports.
>> > >> > The dataset for my mvp is different from my stored proc I'm using.
>> > >> > The data set for my mvp is simple
>> > >> >
>> > >> > codes dataset =>> > >> > select distinct codes from tbl_codes
>> > >> >
>> > >> > values are
>> > >> > AAA-2222
>> > >> > BBB-3333
>> > >> > CCC-444
>> > >> >
>> > >> > In my stored procedure I call the function
>> > >> >
>> > >> > select * from dbo.tbl_codes as a
>> > >> > where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
>> > >> >
>> > >> > the issue is that it only retrives the first code instead of all
>> > >> > three.
>> > >> >
>> > >> > this is how I test it:
>> > >> > select nstr from charlist_to_table
>> > >> > ('AAA-2222,
>> > >> > BBB-3333,
>> > >> > CCC-444
>> > >> > ',',')
>> > >> >
>> > >> > I get the following
>> > >> > AAA-2222,
>> > >> > BBB-3333,
>> > >> > CCC-444
>> > >> >
>> > >> > I don't think the function is working in the sp because there is a
>> > >> > space
>> > >> > in
>> > >> > front of the values. Even when I put a space in the before the
>> > >> > codes
>> > >> > data
>> > >> > set I still only get the data for the first code AAA-2222.
>> > >> >
>> > >> > Am I missing something in the code below. Thanks, Lisa
>> > >> >
>> > >> > CREATE FUNCTION [dbo].[charlist_to_table]
>> > >> > (@.list ntext, @.delimiter nchar(1) = N',')
>> > >> >
>> > >> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
>> > >> > str varchar(4000),
>> > >> > nstr nvarchar(2000)) AS
>> > >> > BEGIN
>> > >> > DECLARE @.pos int,
>> > >> > @.textpos int,
>> > >> > @.chunklen smallint,
>> > >> > @.tmpstr nvarchar(4000),
>> > >> > @.leftover nvarchar(4000),
>> > >> > @.tmpval nvarchar(4000)
>> > >> > SET @.textpos = 1
>> > >> > SET @.leftover = ''
>> > >> > WHILE @.textpos <= datalength(@.list) / 2
>> > >> > BEGIN
>> > >> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
>> > >> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
>> > >> > SET @.textpos = @.textpos + @.chunklen
>> > >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> > >> > WHILE @.pos > 0
>> > >> > BEGIN
>> > >> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
>> > >> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
>> > >> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
>> > >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> > >> > END
>> > >> > SET @.leftover = @.tmpstr
>> > >> > END
>> > >> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
>> > >> > ltrim(rtrim(@.leftover)))
>> > >> > RETURN
>> > >> > END
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >|||that was it.
It actually makes sense now because in SSRS the MVP is
('AAA-2222,BBB-3333,CCC-444')
I'm just use to writing it like this in sql
('AAA-2222,
BBB-3333,
CCC-444')
Thanks - Lisa
"Bruce L-C [MVP]" wrote:
> Are you putting it on separate lines when you do your test?
> select nstr from charlist_to_table
> ('AAA-2222,
> BBB-3333,
> CCC-444
> ',',')
> Since you are enclosing the whole thing in a string it is included the
> carriage return (which will look like a blank). Do it like this:
> select nstr from charlist_to_table
> ('AAA-2222,BBB-3333,CCC-444',',')
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> news:FEB9528A-30AB-44DC-A9FD-DBA43412B2CD@.microsoft.com...
> > thanks, that worked. But, I still have the space issue
> > listpos str nstr
> > 1 SWA35-2948 SWA35-2948
> > 2 SWAP2-2892 SWAP2-2892
> > 3 SWA27-2946 SWA27-2946
> >
> >
> > in the str and nstr fields all but the first row has spaces in front of
> > the
> > value. This is why it's only returning the first row. thanks for you
> > help.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Make your @.codes larger. At least for the below that is why it is not
> >> working.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> >> news:53AD8EAF-4D39-4C63-8725-BD07E3CBA637@.microsoft.com...
> >> > Thanks for your help. I understand, but there is still something
> >> > missing.
> >> >
> >> >
> >> > see the test
> >> > declare @.codes varchar(50)
> >> >
> >> > select
> >> > @.codes = ('SWA35-2948,
> >> > SWAP2-2892,
> >> > SWA27-2946,
> >> > GRE1-2936,
> >> > ADM2-2930,
> >> > SWA28-2938,
> >> > SWUA2-2938,
> >> > SWA31-2948,
> >> > SWAP4-2950,
> >> > SWUA3-2938)
> >> > --test
> >> > print @.codes
> >> > this come out correct
> >> > SWA35-2948,
> >> > SWAP2-2892,
> >> > SWA27-2946,
> >> > GRE1-2936,
> >> > ADM2-2930,
> >> > SWA28-2938,
> >> > SWUA2-2938,
> >> > SWA31-2948,
> >> > SWAP4-2950,
> >> > SWUA3-2938
> >> >
> >> > but when I run this
> >> > select * from charlist_to_table(@.promo_code,default)
> >> > I get the following
> >> >
> >> > listpos str nstr
> >> > 1 SWA35-2948 SWA35-2948
> >> > 2 SWAP2-2892 SWAP2-2892
> >> > 3 SWA27-2946 SWA27-2946
> >> > 4 GRE1-2936 GRE1-2936
> >> > 5
> >> >
> >> >
> >> > I should have 10 listpos and there still spaces in front out the other
> >> > values.
> >> > so this only returns the first row's value for code SWA35-2948
> >> > select a.* from dbo.swp_camps as a
> >> > inner join dbo.charlist_to_table(@.codes,Default) as b on a.codes => >> > b.nstr
> >> >
> >> >
> >> > Any suggestions. Thanks, Lisa
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> I call it using default keyword.
> >> >>
> >> >> select str from charlist_to_talbe(@.codes,default)
> >> >>
> >> >> I use a join.
> >> >>
> >> >> select a.* from dbo.tbl_codes a inner join
> >> >> charlist_to_table(@.CODES,Default)
> >> >> b on a.codes = b.str
> >> >>
> >> >> change b.str to b.nstr depending on the datatype of a.codes.
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "Lisa" <Lisa@.discussions.microsoft.com> wrote in message
> >> >> news:21F23497-06A6-4BF0-9673-EFC1D0ADE87C@.microsoft.com...
> >> >> > Hi, I found the following function on this site and am trying to use
> >> >> > it
> >> >> > my
> >> >> > reports.
> >> >> > The dataset for my mvp is different from my stored proc I'm using.
> >> >> > The data set for my mvp is simple
> >> >> >
> >> >> > codes dataset => >> >> > select distinct codes from tbl_codes
> >> >> >
> >> >> > values are
> >> >> > AAA-2222
> >> >> > BBB-3333
> >> >> > CCC-444
> >> >> >
> >> >> > In my stored procedure I call the function
> >> >> >
> >> >> > select * from dbo.tbl_codes as a
> >> >> > where (a.codes in(select nstr from charlist_to_table(@.codes,',')))
> >> >> >
> >> >> > the issue is that it only retrives the first code instead of all
> >> >> > three.
> >> >> >
> >> >> > this is how I test it:
> >> >> > select nstr from charlist_to_table
> >> >> > ('AAA-2222,
> >> >> > BBB-3333,
> >> >> > CCC-444
> >> >> > ',',')
> >> >> >
> >> >> > I get the following
> >> >> > AAA-2222,
> >> >> > BBB-3333,
> >> >> > CCC-444
> >> >> >
> >> >> > I don't think the function is working in the sp because there is a
> >> >> > space
> >> >> > in
> >> >> > front of the values. Even when I put a space in the before the
> >> >> > codes
> >> >> > data
> >> >> > set I still only get the data for the first code AAA-2222.
> >> >> >
> >> >> > Am I missing something in the code below. Thanks, Lisa
> >> >> >
> >> >> > CREATE FUNCTION [dbo].[charlist_to_table]
> >> >> > (@.list ntext, @.delimiter nchar(1) = N',')
> >> >> >
> >> >> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> >> >> > str varchar(4000),
> >> >> > nstr nvarchar(2000)) AS
> >> >> > BEGIN
> >> >> > DECLARE @.pos int,
> >> >> > @.textpos int,
> >> >> > @.chunklen smallint,
> >> >> > @.tmpstr nvarchar(4000),
> >> >> > @.leftover nvarchar(4000),
> >> >> > @.tmpval nvarchar(4000)
> >> >> > SET @.textpos = 1
> >> >> > SET @.leftover = ''
> >> >> > WHILE @.textpos <= datalength(@.list) / 2
> >> >> > BEGIN
> >> >> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
> >> >> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> >> >> > SET @.textpos = @.textpos + @.chunklen
> >> >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> >> >> > WHILE @.pos > 0
> >> >> > BEGIN
> >> >> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> >> >> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> >> >> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> >> >> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> >> >> > END
> >> >> > SET @.leftover = @.tmpstr
> >> >> > END
> >> >> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> >> >> > ltrim(rtrim(@.leftover)))
> >> >> > RETURN
> >> >> > END
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>