How do I check for the existence of a specific value in a column in a
dataset? For example, I have 'dataset' with 'columnA' and I want to find out
if ANY row in that dataset has a 'columnA' of value 'valueA'. Seems simple
but I'm having trouble. Thanks.
StephanieOn May 22, 12:42 pm, Stephanie <Stepha...@.discussions.microsoft.com>
wrote:
> How do I check for the existence of a specific value in a column in a
> dataset? For example, I have 'dataset' with 'columnA' and I want to find out
> if ANY row in that dataset has a 'columnA' of value 'valueA'. Seems simple
> but I'm having trouble. Thanks.
> Stephanie
The closest thing to the functionality you want is with and expression
similar to this:
=Max(iif(Fields!columnA.Value = 'valueA', 1, 0))
So if the value exists in the column values, the expression will
return a 1. Otherwise, it will return a 0.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
Showing posts with label dataset. Show all posts
Showing posts with label dataset. Show all posts
Monday, March 19, 2012
Saturday, February 25, 2012
Charting from two datasets
I have a chart that is displaying data from two datasets. Dataset #1 returns
a set of "all rows". Dataset #2 returns rows that are a subset of the
Dataset #1 with two status values - let's say "True" and "False". I wish to
display a stacked column chart with three segments - "True", "False" and
"Unknown", where unknown is the count of items from Dataset #1 that are not
represented in Dataset #2. How do I do this? What I have tried is a Series
group keyed to the status value from Dataset #2 (i.e. True, False). My data
value is labeled "Known" and is the count from Dataset #2. But when I add a
2nd data value (i.e. "Unknown") that is the count from Dataset #1, it
effectively multiplies the series to 4 values instead of the desired 3. In
other words, I want the chart to show series values of "Known True", "Known
False" and "Unknown", instead of "Known True", "Known False", "Unknown True",
"Unknown False".You would need to join the data in your query first. Create one chart from
two datasets is not supported.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"BootieDay" <BootieDay@.discussions.microsoft.com> wrote in message
news:0198843D-B474-48E6-9280-2D90038DD765@.microsoft.com...
>I have a chart that is displaying data from two datasets. Dataset #1
>returns
> a set of "all rows". Dataset #2 returns rows that are a subset of the
> Dataset #1 with two status values - let's say "True" and "False". I wish
> to
> display a stacked column chart with three segments - "True", "False" and
> "Unknown", where unknown is the count of items from Dataset #1 that are
> not
> represented in Dataset #2. How do I do this? What I have tried is a
> Series
> group keyed to the status value from Dataset #2 (i.e. True, False). My
> data
> value is labeled "Known" and is the count from Dataset #2. But when I add
> a
> 2nd data value (i.e. "Unknown") that is the count from Dataset #1, it
> effectively multiplies the series to 4 values instead of the desired 3.
> In
> other words, I want the chart to show series values of "Known True",
> "Known
> False" and "Unknown", instead of "Known True", "Known False", "Unknown
> True",
> "Unknown False".|||Thanks Robert. It is my understanding though that supporting an aggregate
from a 2nd dataset within a single data region IS supported, it does seem to
be working, just not giving me the series organization I want. Joining is
not an option as Dataset #1 is provided by an extension DLL and Dataset #2 is
SQL.
"Robert Bruckner [MSFT]" wrote:
> You would need to join the data in your query first. Create one chart from
> two datasets is not supported.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "BootieDay" <BootieDay@.discussions.microsoft.com> wrote in message
> news:0198843D-B474-48E6-9280-2D90038DD765@.microsoft.com...
> >I have a chart that is displaying data from two datasets. Dataset #1
> >returns
> > a set of "all rows". Dataset #2 returns rows that are a subset of the
> > Dataset #1 with two status values - let's say "True" and "False". I wish
> > to
> > display a stacked column chart with three segments - "True", "False" and
> > "Unknown", where unknown is the count of items from Dataset #1 that are
> > not
> > represented in Dataset #2. How do I do this? What I have tried is a
> > Series
> > group keyed to the status value from Dataset #2 (i.e. True, False). My
> > data
> > value is labeled "Known" and is the count from Dataset #2. But when I add
> > a
> > 2nd data value (i.e. "Unknown") that is the count from Dataset #1, it
> > effectively multiplies the series to 4 values instead of the desired 3.
> > In
> > other words, I want the chart to show series values of "Known True",
> > "Known
> > False" and "Unknown", instead of "Known True", "Known False", "Unknown
> > True",
> > "Unknown False".
>
>
a set of "all rows". Dataset #2 returns rows that are a subset of the
Dataset #1 with two status values - let's say "True" and "False". I wish to
display a stacked column chart with three segments - "True", "False" and
"Unknown", where unknown is the count of items from Dataset #1 that are not
represented in Dataset #2. How do I do this? What I have tried is a Series
group keyed to the status value from Dataset #2 (i.e. True, False). My data
value is labeled "Known" and is the count from Dataset #2. But when I add a
2nd data value (i.e. "Unknown") that is the count from Dataset #1, it
effectively multiplies the series to 4 values instead of the desired 3. In
other words, I want the chart to show series values of "Known True", "Known
False" and "Unknown", instead of "Known True", "Known False", "Unknown True",
"Unknown False".You would need to join the data in your query first. Create one chart from
two datasets is not supported.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"BootieDay" <BootieDay@.discussions.microsoft.com> wrote in message
news:0198843D-B474-48E6-9280-2D90038DD765@.microsoft.com...
>I have a chart that is displaying data from two datasets. Dataset #1
>returns
> a set of "all rows". Dataset #2 returns rows that are a subset of the
> Dataset #1 with two status values - let's say "True" and "False". I wish
> to
> display a stacked column chart with three segments - "True", "False" and
> "Unknown", where unknown is the count of items from Dataset #1 that are
> not
> represented in Dataset #2. How do I do this? What I have tried is a
> Series
> group keyed to the status value from Dataset #2 (i.e. True, False). My
> data
> value is labeled "Known" and is the count from Dataset #2. But when I add
> a
> 2nd data value (i.e. "Unknown") that is the count from Dataset #1, it
> effectively multiplies the series to 4 values instead of the desired 3.
> In
> other words, I want the chart to show series values of "Known True",
> "Known
> False" and "Unknown", instead of "Known True", "Known False", "Unknown
> True",
> "Unknown False".|||Thanks Robert. It is my understanding though that supporting an aggregate
from a 2nd dataset within a single data region IS supported, it does seem to
be working, just not giving me the series organization I want. Joining is
not an option as Dataset #1 is provided by an extension DLL and Dataset #2 is
SQL.
"Robert Bruckner [MSFT]" wrote:
> You would need to join the data in your query first. Create one chart from
> two datasets is not supported.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "BootieDay" <BootieDay@.discussions.microsoft.com> wrote in message
> news:0198843D-B474-48E6-9280-2D90038DD765@.microsoft.com...
> >I have a chart that is displaying data from two datasets. Dataset #1
> >returns
> > a set of "all rows". Dataset #2 returns rows that are a subset of the
> > Dataset #1 with two status values - let's say "True" and "False". I wish
> > to
> > display a stacked column chart with three segments - "True", "False" and
> > "Unknown", where unknown is the count of items from Dataset #1 that are
> > not
> > represented in Dataset #2. How do I do this? What I have tried is a
> > Series
> > group keyed to the status value from Dataset #2 (i.e. True, False). My
> > data
> > value is labeled "Known" and is the count from Dataset #2. But when I add
> > a
> > 2nd data value (i.e. "Unknown") that is the count from Dataset #1, it
> > effectively multiplies the series to 4 values instead of the desired 3.
> > In
> > other words, I want the chart to show series values of "Known True",
> > "Known
> > False" and "Unknown", instead of "Known True", "Known False", "Unknown
> > True",
> > "Unknown False".
>
>
chart x-axis y-axis
I'm trying to convert an old chart to Reporting Services.
We have used the same dataset for years, it is very very simple. But it seems to me that Reporting Services can't deal with this simple dataset.
We have used the same dataset for years, it is very very simple. But it seems to me that Reporting Services can't deal with this simple dataset.
My question is: How can I get create a line chart using this dataset.
The data set consists of 1 row with 96 columns (a day divided into 96 sets of 15 minutes). Each column records how many events occurs within the 15 minutes. Along the x axis I would like to display a vertical line every 4 hours. The y axis should be numbered 0 to the highest value in the results.
I could not make this dataset any more simple than it is. But Reporting Services just chokes on this.
Here is a sample of the dataset :
1 2 0 4 5 6 7 8 9 10... 96It looks as thought what I may need is to reset the dataset to look like this:
time events
00-14 1
15-29 2
30-44 0
...
The only way I could do that with the existing tables would be 96 unions.
96 Unions!
Microsoft I must say that I am not so impressed with reporting services. Dragging fields to a myesterous chart "category" or "series" was dumb to begin with.
Chart with 3 year series
Hello
I have a dataset with a date field and a value field. I wish to display the
value fields for current year as well as the previous 2 years together with
different colours on the same month interval on the X-axis. Can someone lend
a hand on how to do this?
thanks in advance
Chris PulfordExample is attached below. The basic idea is to group by months on the
categories and group by year on the series.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="Sales">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style />
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<Color>Brown</Color>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Pastel</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!UnitPrice.Value *
Fields!Quantity.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Style>
<BorderWidth>
<Default>6pt</Default>
</BorderWidth>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Marker />
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>100</PointWidth>
<Type>Line</Type>
<Title>
<Caption>Sales</Caption>
<Style>
<FontSize>14pt</FontSize>
<FontWeight>700</FontWeight>
</Style>
</Title>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="Sales_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Month(Fields!OrderDate.Value)</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Month(Fields!OrderDate.Value)</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=MonthName(Month(Fields!OrderDate.Value))</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Height>6.125in</Height>
<SeriesGroupings>
<SeriesGrouping>
<DynamicSeries>
<Grouping Name="Sales_SeriesGroup1">
<GroupExpressions>
<GroupExpression>=Year(Fields!OrderDate.Value)</GroupExpression>
</GroupExpressions>
</Grouping>
<Label />
</DynamicSeries>
</SeriesGrouping>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Sales</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundGradientEndColor>White</BackgroundGradientEndColor>
<BackgroundGradientType>TopBottom</BackgroundGradientType>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<MinorTickMarks>Outside</MinorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>6.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>da5964d0-11a7-4e51-9b22-cc4fa55fdd7a</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT [Order Details].UnitPrice, [Order
Details].Quantity, Orders.OrderDate
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>bc811835-2302-4f9e-9c89-a99d4d3f5fd2</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>
I have a dataset with a date field and a value field. I wish to display the
value fields for current year as well as the previous 2 years together with
different colours on the same month interval on the X-axis. Can someone lend
a hand on how to do this?
thanks in advance
Chris PulfordExample is attached below. The basic idea is to group by months on the
categories and group by year on the series.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="Sales">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style />
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<Color>Brown</Color>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Pastel</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!UnitPrice.Value *
Fields!Quantity.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Style>
<BorderWidth>
<Default>6pt</Default>
</BorderWidth>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Marker />
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>100</PointWidth>
<Type>Line</Type>
<Title>
<Caption>Sales</Caption>
<Style>
<FontSize>14pt</FontSize>
<FontWeight>700</FontWeight>
</Style>
</Title>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="Sales_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Month(Fields!OrderDate.Value)</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Month(Fields!OrderDate.Value)</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=MonthName(Month(Fields!OrderDate.Value))</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Height>6.125in</Height>
<SeriesGroupings>
<SeriesGrouping>
<DynamicSeries>
<Grouping Name="Sales_SeriesGroup1">
<GroupExpressions>
<GroupExpression>=Year(Fields!OrderDate.Value)</GroupExpression>
</GroupExpressions>
</Grouping>
<Label />
</DynamicSeries>
</SeriesGrouping>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Sales</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundGradientEndColor>White</BackgroundGradientEndColor>
<BackgroundGradientType>TopBottom</BackgroundGradientType>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<MinorTickMarks>Outside</MinorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>6.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>da5964d0-11a7-4e51-9b22-cc4fa55fdd7a</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT [Order Details].UnitPrice, [Order
Details].Quantity, Orders.OrderDate
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>bc811835-2302-4f9e-9c89-a99d4d3f5fd2</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>
Friday, February 24, 2012
chart problem
Hello everybody,
I have problem to make a simple line report:
My Query's dataset:
select VALUE_IND, DATE_IND
from uc.TB_INDI as TI, uc.TB_DEF_IND as TDI
where TDI.LIB_DEF_IND = @.param1 and
TDI.NO_DEF_IND = TI.NO_DEF_IND and
TI.NO_PRJ = @.param2
The result of this query is (from my test base):
VALUE_IND Date_IND
5.0 2005-01-02 00:00:00.000
15.0 2005-02-02 00:00:00.000
10.0 2005-03-02 00:00:00.000
25.0 2005-04-02 00:00:00.000
9.0 2005-05-02 00:00:00.000
33.0 2005-06-02 00:00:00.000
25.0 2005-07-02 00:00:00.000
10.0 2005-08-02 00:00:00.000
28.0 2005-09-02 00:00:00.000
55.0 2005-10-02 00:00:00.000
5.0 2005-11-02 00:00:00.000
10.0 2005-12-02 00:00:00.000
I want to make a chart 'Simple Line' with date in X axis and value in Y
axis. So, in 'Drop data fields here', i put VALUE_IND (which contains
Fields!VALUE_IND.Value like value) and in 'Drop category fields here', i
put DATE_IND (which contains =Fields!DATE_IND.Value like expression)
But when i make a preview, i have only one date record: January in X
axis (x5)and all the value are grouped at the right of the chart!
I don't understand what's happening, i need help please.
Thanks in advance,
aVravrama wrote:
> Hello everybody,
> I have problem to make a simple line report:
> My Query's dataset:
> select VALUE_IND, DATE_IND
> from uc.TB_INDI as TI, uc.TB_DEF_IND as TDI
> where TDI.LIB_DEF_IND = @.param1 and
> TDI.NO_DEF_IND = TI.NO_DEF_IND and
> TI.NO_PRJ = @.param2
> The result of this query is (from my test base):
> VALUE_IND Date_IND
> 5.0 2005-01-02 00:00:00.000
> 15.0 2005-02-02 00:00:00.000
> 10.0 2005-03-02 00:00:00.000
> 25.0 2005-04-02 00:00:00.000
> 9.0 2005-05-02 00:00:00.000
> 33.0 2005-06-02 00:00:00.000
> 25.0 2005-07-02 00:00:00.000
> 10.0 2005-08-02 00:00:00.000
> 28.0 2005-09-02 00:00:00.000
> 55.0 2005-10-02 00:00:00.000
> 5.0 2005-11-02 00:00:00.000
> 10.0 2005-12-02 00:00:00.000
>
> I want to make a chart 'Simple Line' with date in X axis and value in Y
> axis. So, in 'Drop data fields here', i put VALUE_IND (which contains
> Fields!VALUE_IND.Value like value) and in 'Drop category fields here', i
> put DATE_IND (which contains =Fields!DATE_IND.Value like expression)
> But when i make a preview, i have only one date record: January in X
> axis (x5)and all the value are grouped at the right of the chart!
> I don't understand what's happening, i need help please.
> Thanks in advance,
> aVr
I try again with no success... In fact, i dont't understand the
philosophie of RS.
aVr|||avrama wrote:
> avrama wrote:
>> Hello everybody,
>> I have problem to make a simple line report:
>> My Query's dataset:
>> select VALUE_IND, DATE_IND
>> from uc.TB_INDI as TI, uc.TB_DEF_IND as TDI
>> where TDI.LIB_DEF_IND = @.param1 and
>> TDI.NO_DEF_IND = TI.NO_DEF_IND and
>> TI.NO_PRJ = @.param2
>> The result of this query is (from my test base):
>> VALUE_IND Date_IND
>> 5.0 2005-01-02 00:00:00.000 15.0 2005-02-02
>> 00:00:00.000 10.0 2005-03-02 00:00:00.000 25.0
>> 2005-04-02 00:00:00.000 9.0 2005-05-02 00:00:00.000
>> 33.0 2005-06-02 00:00:00.000 25.0 2005-07-02
>> 00:00:00.000 10.0 2005-08-02 00:00:00.000 28.0
>> 2005-09-02 00:00:00.000 55.0 2005-10-02 00:00:00.000
>> 5.0 2005-11-02 00:00:00.000 10.0 2005-12-02
>> 00:00:00.000
>> I want to make a chart 'Simple Line' with date in X axis and value in Y
>> axis. So, in 'Drop data fields here', i put VALUE_IND (which contains
>> Fields!VALUE_IND.Value like value) and in 'Drop category fields here', i
>> put DATE_IND (which contains =Fields!DATE_IND.Value like expression)
>> But when i make a preview, i have only one date record: January in X
>> axis (x5)and all the value are grouped at the right of the chart!
>> I don't understand what's happening, i need help please.
>> Thanks in advance,
>> aVr
>
> I try again with no success... In fact, i dont't understand the
> philosophie of RS.
> aVr
Ok, it's good, it's working...
It was an X axis problem.
Thanks for you help
aVr
I have problem to make a simple line report:
My Query's dataset:
select VALUE_IND, DATE_IND
from uc.TB_INDI as TI, uc.TB_DEF_IND as TDI
where TDI.LIB_DEF_IND = @.param1 and
TDI.NO_DEF_IND = TI.NO_DEF_IND and
TI.NO_PRJ = @.param2
The result of this query is (from my test base):
VALUE_IND Date_IND
5.0 2005-01-02 00:00:00.000
15.0 2005-02-02 00:00:00.000
10.0 2005-03-02 00:00:00.000
25.0 2005-04-02 00:00:00.000
9.0 2005-05-02 00:00:00.000
33.0 2005-06-02 00:00:00.000
25.0 2005-07-02 00:00:00.000
10.0 2005-08-02 00:00:00.000
28.0 2005-09-02 00:00:00.000
55.0 2005-10-02 00:00:00.000
5.0 2005-11-02 00:00:00.000
10.0 2005-12-02 00:00:00.000
I want to make a chart 'Simple Line' with date in X axis and value in Y
axis. So, in 'Drop data fields here', i put VALUE_IND (which contains
Fields!VALUE_IND.Value like value) and in 'Drop category fields here', i
put DATE_IND (which contains =Fields!DATE_IND.Value like expression)
But when i make a preview, i have only one date record: January in X
axis (x5)and all the value are grouped at the right of the chart!
I don't understand what's happening, i need help please.
Thanks in advance,
aVravrama wrote:
> Hello everybody,
> I have problem to make a simple line report:
> My Query's dataset:
> select VALUE_IND, DATE_IND
> from uc.TB_INDI as TI, uc.TB_DEF_IND as TDI
> where TDI.LIB_DEF_IND = @.param1 and
> TDI.NO_DEF_IND = TI.NO_DEF_IND and
> TI.NO_PRJ = @.param2
> The result of this query is (from my test base):
> VALUE_IND Date_IND
> 5.0 2005-01-02 00:00:00.000
> 15.0 2005-02-02 00:00:00.000
> 10.0 2005-03-02 00:00:00.000
> 25.0 2005-04-02 00:00:00.000
> 9.0 2005-05-02 00:00:00.000
> 33.0 2005-06-02 00:00:00.000
> 25.0 2005-07-02 00:00:00.000
> 10.0 2005-08-02 00:00:00.000
> 28.0 2005-09-02 00:00:00.000
> 55.0 2005-10-02 00:00:00.000
> 5.0 2005-11-02 00:00:00.000
> 10.0 2005-12-02 00:00:00.000
>
> I want to make a chart 'Simple Line' with date in X axis and value in Y
> axis. So, in 'Drop data fields here', i put VALUE_IND (which contains
> Fields!VALUE_IND.Value like value) and in 'Drop category fields here', i
> put DATE_IND (which contains =Fields!DATE_IND.Value like expression)
> But when i make a preview, i have only one date record: January in X
> axis (x5)and all the value are grouped at the right of the chart!
> I don't understand what's happening, i need help please.
> Thanks in advance,
> aVr
I try again with no success... In fact, i dont't understand the
philosophie of RS.
aVr|||avrama wrote:
> avrama wrote:
>> Hello everybody,
>> I have problem to make a simple line report:
>> My Query's dataset:
>> select VALUE_IND, DATE_IND
>> from uc.TB_INDI as TI, uc.TB_DEF_IND as TDI
>> where TDI.LIB_DEF_IND = @.param1 and
>> TDI.NO_DEF_IND = TI.NO_DEF_IND and
>> TI.NO_PRJ = @.param2
>> The result of this query is (from my test base):
>> VALUE_IND Date_IND
>> 5.0 2005-01-02 00:00:00.000 15.0 2005-02-02
>> 00:00:00.000 10.0 2005-03-02 00:00:00.000 25.0
>> 2005-04-02 00:00:00.000 9.0 2005-05-02 00:00:00.000
>> 33.0 2005-06-02 00:00:00.000 25.0 2005-07-02
>> 00:00:00.000 10.0 2005-08-02 00:00:00.000 28.0
>> 2005-09-02 00:00:00.000 55.0 2005-10-02 00:00:00.000
>> 5.0 2005-11-02 00:00:00.000 10.0 2005-12-02
>> 00:00:00.000
>> I want to make a chart 'Simple Line' with date in X axis and value in Y
>> axis. So, in 'Drop data fields here', i put VALUE_IND (which contains
>> Fields!VALUE_IND.Value like value) and in 'Drop category fields here', i
>> put DATE_IND (which contains =Fields!DATE_IND.Value like expression)
>> But when i make a preview, i have only one date record: January in X
>> axis (x5)and all the value are grouped at the right of the chart!
>> I don't understand what's happening, i need help please.
>> Thanks in advance,
>> aVr
>
> I try again with no success... In fact, i dont't understand the
> philosophie of RS.
> aVr
Ok, it's good, it's working...
It was an X axis problem.
Thanks for you help
aVr
Chart not reflecting new Dataset Changes
I changed the stord proc behind my dataset in my report to bring in less data however my report when previewed and refreshed is not reflecting this. What could be causing this? When I run the dataset in the data tab, it looks right.Have you tried refreshing the fields? In the data tab, click on the refresh fields icon (a small white rectangle with green arrows).|||yea, that wasn't it. It's related to the old IE cache problems again...I guess they will never make it very reliable...even when you have your cache set to refresh on every page load.
Sunday, February 19, 2012
Chart dataset error from sql sum fields
I'm receiving an,
"The expression for the chart â'chart1â' refers to the field â'Bill_Sumâ'.
Report item expressions can only refer to fields within the current data set
scope or, if inside an aggregate, the specified data set scope."
Error whenever I use the fields from my similar running total query
SELECT a.DayCount,
a.Sales,
SUM(b.Sales)
FROM Sales a
CROSS JOIN Sales b
WHERE (b.DayCount <= a.DayCount) AS RunningTotal
GROUP BY a.DayCount,a.Sales
ORDER BY a.DayCount,a.Sales
Anyone run into a similar problem?Just in case anyone else has this problem
if in your select you use a Sum and it will automatically convert it to a
name like AS expr1, Change the expr to that field.
Ex:
Select Sum(Distinct Something.FieldName) As Expr1
Change Expr1 to FieldName and it should work in the graph.
Don't know why I was getting this error.
"Sean Edwards" wrote:
> I'm receiving an,
> "The expression for the chart â'chart1â' refers to the field â'Bill_Sumâ'.
> Report item expressions can only refer to fields within the current data set
> scope or, if inside an aggregate, the specified data set scope."
> Error whenever I use the fields from my similar running total query
> SELECT a.DayCount,
> a.Sales,
> SUM(b.Sales)
> FROM Sales a
> CROSS JOIN Sales b
> WHERE (b.DayCount <= a.DayCount) AS RunningTotal
> GROUP BY a.DayCount,a.Sales
> ORDER BY a.DayCount,a.Sales
> Anyone run into a similar problem?
"The expression for the chart â'chart1â' refers to the field â'Bill_Sumâ'.
Report item expressions can only refer to fields within the current data set
scope or, if inside an aggregate, the specified data set scope."
Error whenever I use the fields from my similar running total query
SELECT a.DayCount,
a.Sales,
SUM(b.Sales)
FROM Sales a
CROSS JOIN Sales b
WHERE (b.DayCount <= a.DayCount) AS RunningTotal
GROUP BY a.DayCount,a.Sales
ORDER BY a.DayCount,a.Sales
Anyone run into a similar problem?Just in case anyone else has this problem
if in your select you use a Sum and it will automatically convert it to a
name like AS expr1, Change the expr to that field.
Ex:
Select Sum(Distinct Something.FieldName) As Expr1
Change Expr1 to FieldName and it should work in the graph.
Don't know why I was getting this error.
"Sean Edwards" wrote:
> I'm receiving an,
> "The expression for the chart â'chart1â' refers to the field â'Bill_Sumâ'.
> Report item expressions can only refer to fields within the current data set
> scope or, if inside an aggregate, the specified data set scope."
> Error whenever I use the fields from my similar running total query
> SELECT a.DayCount,
> a.Sales,
> SUM(b.Sales)
> FROM Sales a
> CROSS JOIN Sales b
> WHERE (b.DayCount <= a.DayCount) AS RunningTotal
> GROUP BY a.DayCount,a.Sales
> ORDER BY a.DayCount,a.Sales
> Anyone run into a similar problem?
Chart Axes Labels
I need to dynamically name the axes of a chart as the data plotted can be changed - Sliced. Ie. Sales dollars, Quantity, Profit etc.
The dataset that is used to plot the graph does not contain the measure description (it's a generic name - MeasureModifier). There is a second data set that contains all the descriptions that the report can be sliced on.
I have a text box that displays the description of the measure and I tried referencing the value as an expression from the Y axes title expression box. - =ReportItems!Parameter1_1.value
However it returns "Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope."
Does anybody know of any other way I can referrence values outside the dataset that the object is derived from?
Cheers
KevinI can actually reference a regular textbox value for the y axes label.
What I can't do - from the Y axes label or even from another text box is reference a value from a textbox that is inside a list box. The list box has a filter that only returns one row from the data set and the textbox displays the field from the dataset. I need to reference that value for the Y axes title but keep getting the error below when I use ReportItems!...
Any ideas how to overcome this?
Thanks
Kevin
"Kevin Wilson" wrote:
> I need to dynamically name the axes of a chart as the data plotted can be changed - Sliced. Ie. Sales dollars, Quantity, Profit etc.
> The dataset that is used to plot the graph does not contain the measure description (it's a generic name - MeasureModifier). There is a second data set that contains all the descriptions that the report can be sliced on.
> I have a text box that displays the description of the measure and I tried referencing the value as an expression from the Y axes title expression box. - =ReportItems!Parameter1_1.value
> However it returns "Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope."
> Does anybody know of any other way I can referrence values outside the dataset that the object is derived from?
> Cheers
> Kevin
>
The dataset that is used to plot the graph does not contain the measure description (it's a generic name - MeasureModifier). There is a second data set that contains all the descriptions that the report can be sliced on.
I have a text box that displays the description of the measure and I tried referencing the value as an expression from the Y axes title expression box. - =ReportItems!Parameter1_1.value
However it returns "Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope."
Does anybody know of any other way I can referrence values outside the dataset that the object is derived from?
Cheers
KevinI can actually reference a regular textbox value for the y axes label.
What I can't do - from the Y axes label or even from another text box is reference a value from a textbox that is inside a list box. The list box has a filter that only returns one row from the data set and the textbox displays the field from the dataset. I need to reference that value for the Y axes title but keep getting the error below when I use ReportItems!...
Any ideas how to overcome this?
Thanks
Kevin
"Kevin Wilson" wrote:
> I need to dynamically name the axes of a chart as the data plotted can be changed - Sliced. Ie. Sales dollars, Quantity, Profit etc.
> The dataset that is used to plot the graph does not contain the measure description (it's a generic name - MeasureModifier). There is a second data set that contains all the descriptions that the report can be sliced on.
> I have a text box that displays the description of the measure and I tried referencing the value as an expression from the Y axes title expression box. - =ReportItems!Parameter1_1.value
> However it returns "Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope."
> Does anybody know of any other way I can referrence values outside the dataset that the object is derived from?
> Cheers
> Kevin
>
Thursday, February 16, 2012
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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
Tuesday, February 14, 2012
Character String Query Doesnt Fill Dataset
I'm working in a ASP.NET 2.0 application with a SQL Server 2000 database on the back end. I have a strongly typed dataset in the application that calls a stored procedure for the select. I'm having trouble filling the dataset at runtime though.
I am trying to use a character string query because I setup different columns to be pulled from a table each time and in a different order so my T-SQL looks like this:
set @.FullQuery = 'Select ' + @.FieldsinOrder + ' from tblExample'
exec (@.FullQuery)
This works fine in query analyzer. The results return and display correctly. However, when I run the application, the dataset does not get filled. It is like the results do not output to the application.
If I change the query to be a normal select it works. For example:
select * from tblEmample
That works fine. What is it about a select query setup as a character string and then executed that ASP.NET doesn't like?
try to build your command in ASP and pass it to SQL command command object
like:
Dim
yourCommandAs SqlClient.SqlCommand =New SqlClient.SqlCommand(" 'Select '" + FieldsinOrder + '" from tblExample"', yourconnection)yourCommand.CommandType = CommandType.Text
Maybe it will work.
When your use Exec to execute query inside query is possible that result from this exec is not visible to ASP.NET code as valid result.
Thanks
Subscribe to:
Posts (Atom)