Data Conversion

Here is my table:
CustomerShortName varchar (50)
CustomerID varchar(8)
OrderMonth char(2)
OrderYear char(4)
OrderCount varchar(50)
RegionID varchar(50)
Then the report is based on this sproc:
CREATE PROCEDURE dbo.renMonthlyOrderReport
(
@.StartMonth AS INT,
@.StartYear AS INT,
@.RegionIDs varchar(25)
)
AS
DECLARE @.Month1 AS INT, @.Month2 AS INT, @.Month3 AS INT, @.Month4 AS INT, @.Month5 AS INT, @.Month6 AS INT
DECLARE @.Year1 AS INT, @.Year2 AS INT, @.Year3 AS INT, @.Year4 AS INT, @.Year5 AS INT, @.Year6 AS INT
SELECT @.Month1 = @.StartMonth
SELECT @.Month2 = @.StartMonth + 1
SELECT @.Month3 = @.StartMonth + 2
SELECT @.Month4 = @.StartMonth + 3
SELECT @.Month5 = @.StartMonth + 4
SELECT @.Month6 = @.StartMonth + 5
SELECT @.Year1 = @.StartYear
SELECT @.Year2 = @.StartYear
SELECT @.Year3 = @.StartYear
SELECT @.Year4 = @.StartYear
SELECT @.Year5 = @.StartYear
SELECT @.Year6 = @.StartYear
IF @.Month1 > 12 SELECT @.Year1 = @.Year1 + 1
IF @.Month2 > 12 SELECT @.Year2 = @.Year2 + 1
IF @.Month3 > 12 SELECT @.Year3 = @.Year3 + 1
IF @.Month4 > 12 SELECT @.Year4 = @.Year4 + 1
IF @.Month5 > 12 SELECT @.Year5 = @.Year5 + 1
IF @.Month6 > 12 SELECT @.Year6 = @.Year6 + 1
IF @.Month1 > 12 SELECT @.Month1 = @.Month1 - 12
IF @.Month2 > 12 SELECT @.Month2 = @.Month2 - 12
IF @.Month3 > 12 SELECT @.Month3 = @.Month3 - 12
IF @.Month4 > 12 SELECT @.Month4 = @.Month4 - 12
IF @.Month5 > 12 SELECT @.Month5 = @.Month5 - 12
IF @.Month6 > 12 SELECT @.Month6 = @.Month6 - 12
SELECT
DISTINCT(dbo.TD_SALES.CustomerShortName),
CAST([dbo].[TD_SALES].OrderMonth as int) as OrderMonth,
CAST([dbo].[TD_SALES].OrderYear as int) OrderYear,
[dbo].[TD_SALES].RegionID,
dbo.TD_SALES.OrderCount
FROM
[dbo].[TD_SALES]
WHERE
(
[dbo].[TD_SALES].OrderMonth = @.Month1 AND [dbo].[TD_SALES].OrderYear = @.Year1 OR
[dbo].[TD_SALES].OrderMonth = @.Month2 AND [dbo].[TD_SALES].OrderYear = @.Year2 OR
[dbo].[TD_SALES].OrderMonth = @.Month3 AND [dbo].[TD_SALES].OrderYear = @.Year3 OR
[dbo].[TD_SALES].OrderMonth = @.Month4 AND [dbo].[TD_SALES].OrderYear = @.Year4 OR
[dbo].[TD_SALES].OrderMonth = @.Month5 AND [dbo].[TD_SALES].OrderYear = @.Year5 OR
[dbo].[TD_SALES].OrderMonth = @.Month6 AND [dbo].[TD_SALES].OrderYear = @.Year6
)
AND
[dbo].[TD_SALES].RegionID IN (SELECT * FROM RegionsTable(@.RegionIDs))
ORDER BY
dbo.TD_SALES.RegionID,
CAST([dbo].[TD_SALES].OrderMonth as int) DESC,
CAST([dbo].[TD_SALES].OrderYear as int) DESC,
dbo.TD_SALES.OrderCount DESC,
dbo.TD_SALES.CustomerShortName
GO
The OrderCount field is varchar because the data can be actual count , * , or null. But right now in my report I have a matrix table. How would I go about displaying the * character, null or a actual number but still be able to use the matrix for subtotaling?
Thanks,
AlTry this as the expression in your data cell (this will treat * as 0 for
aggregation purposes).
=iif(InScope("matrix1_ColumnGroup") AND
InScope("matrix1_RowGroup"),Fields!OrderCount.Value,Sum(iif(Fields!OrderCoun
t.Value="*",0,Fields!OrderCount.Value)))
Change the group names to be the innermost row and column group names in
your matrix.
If you want any * to result in * for the aggregate, you'd do this instead:
=iif(InScope("matrix1_ColumnGroup") AND
InScope("matrix1_RowGroup"),Fields!OrderCount.Value,
iif(Sum(iif(Fields!OrderCount.Value="*",1,0))>0,"*",Sum(iif(Fields!OrderCoun
t.Value="*",0,Fields!OrderCount.Value)))
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Al" <Al@.discussions.microsoft.com> wrote in message
news:D3794A37-C147-492F-BE37-870BF036C541@.microsoft.com...
> Here is my table:
> CustomerShortName varchar (50)
> CustomerID varchar(8)
> OrderMonth char(2)
> OrderYear char(4)
> OrderCount varchar(50)
> RegionID varchar(50)
> Then the report is based on this sproc:
> CREATE PROCEDURE dbo.renMonthlyOrderReport
> (
> @.StartMonth AS INT,
> @.StartYear AS INT,
> @.RegionIDs varchar(25)
> )
> AS
> DECLARE @.Month1 AS INT, @.Month2 AS INT, @.Month3 AS INT, @.Month4 AS INT,
@.Month5 AS INT, @.Month6 AS INT
> DECLARE @.Year1 AS INT, @.Year2 AS INT, @.Year3 AS INT, @.Year4 AS INT, @.Year5
AS INT, @.Year6 AS INT
> SELECT @.Month1 = @.StartMonth
> SELECT @.Month2 = @.StartMonth + 1
> SELECT @.Month3 = @.StartMonth + 2
> SELECT @.Month4 = @.StartMonth + 3
> SELECT @.Month5 = @.StartMonth + 4
> SELECT @.Month6 = @.StartMonth + 5
> SELECT @.Year1 = @.StartYear
> SELECT @.Year2 = @.StartYear
> SELECT @.Year3 = @.StartYear
> SELECT @.Year4 = @.StartYear
> SELECT @.Year5 = @.StartYear
> SELECT @.Year6 = @.StartYear
> IF @.Month1 > 12 SELECT @.Year1 = @.Year1 + 1
> IF @.Month2 > 12 SELECT @.Year2 = @.Year2 + 1
> IF @.Month3 > 12 SELECT @.Year3 = @.Year3 + 1
> IF @.Month4 > 12 SELECT @.Year4 = @.Year4 + 1
> IF @.Month5 > 12 SELECT @.Year5 = @.Year5 + 1
> IF @.Month6 > 12 SELECT @.Year6 = @.Year6 + 1
> IF @.Month1 > 12 SELECT @.Month1 = @.Month1 - 12
> IF @.Month2 > 12 SELECT @.Month2 = @.Month2 - 12
> IF @.Month3 > 12 SELECT @.Month3 = @.Month3 - 12
> IF @.Month4 > 12 SELECT @.Month4 = @.Month4 - 12
> IF @.Month5 > 12 SELECT @.Month5 = @.Month5 - 12
> IF @.Month6 > 12 SELECT @.Month6 = @.Month6 - 12
>
> SELECT
> DISTINCT(dbo.TD_SALES.CustomerShortName),
> CAST([dbo].[TD_SALES].OrderMonth as int) as OrderMonth,
> CAST([dbo].[TD_SALES].OrderYear as int) OrderYear,
> [dbo].[TD_SALES].RegionID,
> dbo.TD_SALES.OrderCount
>
> FROM
> [dbo].[TD_SALES]
> WHERE
> (
> [dbo].[TD_SALES].OrderMonth = @.Month1 AND [dbo].[TD_SALES].OrderYear =@.Year1 OR
> [dbo].[TD_SALES].OrderMonth = @.Month2 AND [dbo].[TD_SALES].OrderYear =@.Year2 OR
> [dbo].[TD_SALES].OrderMonth = @.Month3 AND [dbo].[TD_SALES].OrderYear =@.Year3 OR
> [dbo].[TD_SALES].OrderMonth = @.Month4 AND [dbo].[TD_SALES].OrderYear =@.Year4 OR
> [dbo].[TD_SALES].OrderMonth = @.Month5 AND [dbo].[TD_SALES].OrderYear =@.Year5 OR
> [dbo].[TD_SALES].OrderMonth = @.Month6 AND [dbo].[TD_SALES].OrderYear =@.Year6
> )
> AND
> [dbo].[TD_SALES].RegionID IN (SELECT * FROM RegionsTable(@.RegionIDs))
> ORDER BY
> dbo.TD_SALES.RegionID,
> CAST([dbo].[TD_SALES].OrderMonth as int) DESC,
> CAST([dbo].[TD_SALES].OrderYear as int) DESC,
> dbo.TD_SALES.OrderCount DESC,
> dbo.TD_SALES.CustomerShortName
> GO
>
> The OrderCount field is varchar because the data can be actual count ,
* , or null. But right now in my report I have a matrix table. How would I
go about displaying the * character, null or a actual number but still be
able to use the matrix for subtotaling?
> Thanks,
> Al|||I forgot something. You need to cast as well. Try this:
=iif(InScope("matrix1_MonthGroup") AND
InScope("matrix1_LenderNameGroup"),Fields!OrderCount.Value,
Sum(iif(Fields!OrderCount.Value="*",0,CDbl(Fields!OrderCount.Value))))
Or use CInt instead if the numbers are all integers.
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Al" <Al@.discussions.microsoft.com> wrote in message
news:203A383D-AAF1-49BD-8BC3-05CE64858D23@.microsoft.com...
> I used your expression in the data cell
> =iif(InScope("matrix1_MonthGroup") AND
InScope("matrix1_LenderNameGroup"),Fields!OrderCount.Value,
Sum(iif(Fields!OrderCount.Value="*",0,Fields!OrderCount.Value)))
> I get the desired output for the * char but it still returns '#Error'
if the condition of Fields!OrderCount.Value = "*" is not true.
> "Chris Hays [MSFT]" wrote:
> > Try this as the expression in your data cell (this will treat * as 0 for
> > aggregation purposes).
> >
> > =iif(InScope("matrix1_ColumnGroup") AND
> >
InScope("matrix1_RowGroup"),Fields!OrderCount.Value,Sum(iif(Fields!OrderCoun
> > t.Value="*",0,Fields!OrderCount.Value)))
> >
> > Change the group names to be the innermost row and column group names in
> > your matrix.
> >
> > If you want any * to result in * for the aggregate, you'd do this
instead:
> > =iif(InScope("matrix1_ColumnGroup") AND
> > InScope("matrix1_RowGroup"),Fields!OrderCount.Value,
> >
iif(Sum(iif(Fields!OrderCount.Value="*",1,0))>0,"*",Sum(iif(Fields!OrderCoun
> > t.Value="*",0,Fields!OrderCount.Value)))
> >
> > --
> > My employer's lawyers require me to say:
> > "This posting is provided 'AS IS' with no warranties, and confers no
> > rights."
> >
> > "Al" <Al@.discussions.microsoft.com> wrote in message
> > news:D3794A37-C147-492F-BE37-870BF036C541@.microsoft.com...
> > > Here is my table:
> > > CustomerShortName varchar (50)
> > > CustomerID varchar(8)
> > > OrderMonth char(2)
> > > OrderYear char(4)
> > > OrderCount varchar(50)
> > > RegionID varchar(50)
> > >
> > > Then the report is based on this sproc:
> > > CREATE PROCEDURE dbo.renMonthlyOrderReport
> > > (
> > > @.StartMonth AS INT,
> > > @.StartYear AS INT,
> > > @.RegionIDs varchar(25)
> > > )
> > > AS
> > > DECLARE @.Month1 AS INT, @.Month2 AS INT, @.Month3 AS INT, @.Month4 AS
INT,
> > @.Month5 AS INT, @.Month6 AS INT
> > > DECLARE @.Year1 AS INT, @.Year2 AS INT, @.Year3 AS INT, @.Year4 AS INT,
@.Year5
> > AS INT, @.Year6 AS INT
> > >
> > > SELECT @.Month1 = @.StartMonth
> > > SELECT @.Month2 = @.StartMonth + 1
> > > SELECT @.Month3 = @.StartMonth + 2
> > > SELECT @.Month4 = @.StartMonth + 3
> > > SELECT @.Month5 = @.StartMonth + 4
> > > SELECT @.Month6 = @.StartMonth + 5
> > >
> > > SELECT @.Year1 = @.StartYear
> > > SELECT @.Year2 = @.StartYear
> > > SELECT @.Year3 = @.StartYear
> > > SELECT @.Year4 = @.StartYear
> > > SELECT @.Year5 = @.StartYear
> > > SELECT @.Year6 = @.StartYear
> > >
> > > IF @.Month1 > 12 SELECT @.Year1 = @.Year1 + 1
> > > IF @.Month2 > 12 SELECT @.Year2 = @.Year2 + 1
> > > IF @.Month3 > 12 SELECT @.Year3 = @.Year3 + 1
> > > IF @.Month4 > 12 SELECT @.Year4 = @.Year4 + 1
> > > IF @.Month5 > 12 SELECT @.Year5 = @.Year5 + 1
> > > IF @.Month6 > 12 SELECT @.Year6 = @.Year6 + 1
> > >
> > > IF @.Month1 > 12 SELECT @.Month1 = @.Month1 - 12
> > > IF @.Month2 > 12 SELECT @.Month2 = @.Month2 - 12
> > > IF @.Month3 > 12 SELECT @.Month3 = @.Month3 - 12
> > > IF @.Month4 > 12 SELECT @.Month4 = @.Month4 - 12
> > > IF @.Month5 > 12 SELECT @.Month5 = @.Month5 - 12
> > > IF @.Month6 > 12 SELECT @.Month6 = @.Month6 - 12
> > >
> > >
> > > SELECT
> > > DISTINCT(dbo.TD_SALES.CustomerShortName),
> > > CAST([dbo].[TD_SALES].OrderMonth as int) as OrderMonth,
> > > CAST([dbo].[TD_SALES].OrderYear as int) OrderYear,
> > > [dbo].[TD_SALES].RegionID,
> > > dbo.TD_SALES.OrderCount
> > >
> > >
> > > FROM
> > > [dbo].[TD_SALES]
> > >
> > > WHERE
> > > (
> > > [dbo].[TD_SALES].OrderMonth = @.Month1 AND [dbo].[TD_SALES].OrderYear => > @.Year1 OR
> > > [dbo].[TD_SALES].OrderMonth = @.Month2 AND [dbo].[TD_SALES].OrderYear => > @.Year2 OR
> > > [dbo].[TD_SALES].OrderMonth = @.Month3 AND [dbo].[TD_SALES].OrderYear => > @.Year3 OR
> > > [dbo].[TD_SALES].OrderMonth = @.Month4 AND [dbo].[TD_SALES].OrderYear => > @.Year4 OR
> > > [dbo].[TD_SALES].OrderMonth = @.Month5 AND [dbo].[TD_SALES].OrderYear => > @.Year5 OR
> > > [dbo].[TD_SALES].OrderMonth = @.Month6 AND [dbo].[TD_SALES].OrderYear => > @.Year6
> > > )
> > > AND
> > > [dbo].[TD_SALES].RegionID IN (SELECT * FROM RegionsTable(@.RegionIDs))
> > >
> > > ORDER BY
> > > dbo.TD_SALES.RegionID,
> > > CAST([dbo].[TD_SALES].OrderMonth as int) DESC,
> > > CAST([dbo].[TD_SALES].OrderYear as int) DESC,
> > > dbo.TD_SALES.OrderCount DESC,
> > > dbo.TD_SALES.CustomerShortName
> > > GO
> > >
> > >
> > > The OrderCount field is varchar because the data can be actual
count ,
> > * , or null. But right now in my report I have a matrix table. How
would I
> > go about displaying the * character, null or a actual number but still
be
> > able to use the matrix for subtotaling?
> > >
> > > Thanks,
> > > Al
> >
> >
> >