Alerts issue :How to solve with out getting those after exporting

i have a report with 2 fields(name,zipcode).
After exporting to excel, it generates Alerts for Zipcode Field.
some of the Zipcode field values like this (34213,23423,123123-1234,34311-1212)
In zipcode filed after exporting to excel, Iam getting alerts on 34213,23423 but not on 123123-1234.?
These alerts saying "The Number in this cell is formatted as text or preceded by apostrophe"
How can i remove alerts?
Will this be done with the help of rdl with out changing the Excel?
Can any one help on this.Even though we are exporting these as text Excel is assuming the 5 digit zip
codes should be a number.
To avoid this you will have to unset the "Number stored as text" error
checking option in Excel.
From Excel you will need to open Tools : Options : Error Checking and the
uncheck "Number stored as text"
> some of the Zipcode field values like this
(34213,23423,123123-1234,34311-1212)
> In zipcode filed after exporting to excel, Iam getting alerts on
34213,23423 but not on 123123-1234.?
> These alerts saying "The Number in this cell is formatted as text or
preceded by apostrophe"
> How can i remove alerts?
> Will this be done with the help of rdl with out changing the Excel?
> Can any one help on this.
>

Aggregations in Analysis Services STANDARD edition

I have fields in my cube where SUM is not a good aggregation (rate fields for example). I thought I should just set them to No aggregation, but it tells me that semi-additive aggregations are not available in Standard Edition (?). So, two questions...

1) How do I avoid representing these fields incorrectly in Standard Edition? What aggregation should they be set to? I don't want users to point Excel to them and get this big meaningless number!

2) How/Where do I produce Averages, etc? (I'm thinking that I can only do this in the Report or Excel - that it can't be done in the cube, is that right?)

Thanks for you help...

- Jim

You're correct that Standard Edition does not support semi-additive aggregations. These functions include ByAccount, AverageOfChildren, LastChild, FirstChild, LastNonEmpty, and FirstNonEmpty. If these are the right aggregations to use, you might be able to set up a calculated member that gives you something similar by going to the leaf level and then controlling the aggregation to the level your interested in. If you do this, make the measure you are "replacing" not visible to your users.

Regarding averages, store a SUM and a COUNT measure and do a ratio in a calculated member.

Good luck,
Bryan

|||

If you don't have enterprise edition, then this workaround might help:

http://solidqualitylearning.com/Blogs/dejan/archive/2006/11/08/3439.aspx

or

http://lorentsnv.spaces.live.com/blog/cns!235C78448ABCFC07!134.entry

Aggregation against multiple date fields

I have a single table which records calls made to a helpdesk.
Each call has a single row in the table, and there are three relevant date fields recorded: occurrence date, reported date, and resolution date.

So it looks a little like this:

OccDate | RepDate| ResDate | CallRef
21/3/05 |22/3/05 | 01/4/05 | PMR001
22/3/05 | 1/4/05 | 26/5/05 | PMR002
01/4/05 | 2/4/05 | 3/6/05 | PMR003
01/5/05 | 2/5/05 | <Null> | PMR004

Now what I want to do is to create a single SQL statement which will allow me to summarise in each month how many calls occurred, were reported, and resolved, so for this data it looks like this:

Month | Occ. | Rep. | Res.
Mar-05 | 2 | 1 | 0
Apr-05 | 1 | 2 | 1
May-05 | 1 | 1 | 1
Jun-05 | 0 | 0 | 1

I can get each column individually very easily by simply doing:
COUNT(CallRef) GROUP BY (OccDate)
COUNT(CallRef) GROUP BY (RepDate)
COUNT(CallRef) GROUP BY (ResDate)
..but I'm getting in a right pickle when trying to merge these into a single statement.

To start with, I'm guessing that I need to do 2 self-joins so that all three dates are linked to each other.
Then I tried using CASE and COALESCE statements to do conditional aggregations, but that got me nowhere...

I'd be grateful for any help!I tested it on MS SQL Server:

create table t (OccDate datetime, RepDate datetime, ResDate datetime, callRef varchar(10))

insert into t values( cast('3/21/05' AS DATETIME) , cast('3/22/05' as DATETIME), cast('4/1/05' as datetime), 'PMR001')
insert into t values( cast('3/22/05' AS DATETIME) , cast('4/1/05' as DATETIME), cast('5/26/05' as datetime), 'PMR002')
insert into t values( cast('4/1/05' AS DATETIME) , cast('4/2/05' as DATETIME), cast('6/3/05' as datetime), 'PMR003')
insert into t values( cast('5/1/05' AS DATETIME) , cast('5/2/05' as DATETIME), NULL, 'PMR004')

select
mont,
(select count(*) from t t3 where cast(OccDate AS char(3)) + '-' + cast(year(OccDate) AS varchar(4)) = t2.mont) as Occ,
(select count(*) from t t3 where cast(RepDate AS char(3)) + '-' + cast(year(RepDate) AS varchar(4)) = t2.mont) as Rep,
(select count(*) from t t3 where cast(ResDate AS char(3)) + '-' + cast(year(ResDate) AS varchar(4)) = t2.mont) as Res
from
(
select distinct mon as Mont from
(
select cast(OccDate AS char(3)) + '-' + cast(year(OccDate) AS varchar(4)) as mon from t where OccDate is not null
union all
select cast(RepDate AS char(3)) + '-' + cast(year(RepDate) AS varchar(4)) as mon from t where RepDate is not null
union all
select cast(ResDate AS char(3)) + '-' + cast(year(ResDate) AS varchar(4)) as mon from t where ResDate is not null
) t1
) t2

Month Occ Rep Res
-------------
Apr-2005 1 2 1
Jun-2005 0 0 1
Mar-2005 2 1 0
May-2005 1 1 1

to run this on different DB server all you need is replace
cast(ResDate AS char(3)) + '-' + cast(year(ResDate) AS varchar(4))
with other functions which returns
'Month-Year'|||Thankyou so much for the helpful reply - you're a star!|||An other solution is the following:create table t (OccDate date, RepDate date, ResDate date, callRef varchar(10)) ;
insert into t values( '03/21/2005', '03/22/2005', '04/01/2005', 'PMR001') ;
insert into t values( '03/22/2005', '04/01/2005', '05/26/2005', 'PMR002') ;
insert into t values( '04/01/2005', '04/02/2005', '06/03/2005', 'PMR003') ;
insert into t values( '05/01/2005', '05/02/2005', NULL, 'PMR004') ;
SELECT coalesce(m1,m2,m3) AS "month",
coalesce(Occ,0), coalesce(Rep,0), coalesce(Res,0)
FROM ( SELECT m1, COUNT(*) AS Occ
FROM ( SELECT substr(char(OccDate,ISO),1,7) AS m1
FROM t ) AS x1
GROUP BY m1 ) AS y1
FULL OUTER JOIN
( SELECT m2, COUNT(*) AS Rep
FROM ( SELECT substr(char(RepDate,ISO),1,7) AS m2
FROM t ) AS x2
GROUP BY m2 ) AS y2
ON m1 = m2
FULL OUTER JOIN
( SELECT m3, COUNT(*) AS Res
FROM ( SELECT substr(char(ResDate,ISO),1,7) AS m3
FROM t ) AS x3
GROUP BY m3 ) AS y3
ON m2 = m3
ORDER BY 1It has the slight advantage that it works with DB2 V7, and that it might be a bit faster (but I did not verify that ;) )

Aggregate Transpose Problem

My example code (CODE 1) returns quarter counts for several fields in
Northwind (I'm using Northwind to simulate my actual data). It returns code
that looks like FIGURE 1.
Can my sql be modified so the Field names are the GROUPED BY and run down
the 1st column while forcing the Quarters to be the header columns and
replace the ctShipName, ctShipCity and ctShipCountry positions and result
looking like FIGURE 2?
FIGURE 1 (Current Result):
Quarter ctShipName ctShipCity ctShipCountry
1996, Qtr. 3 185 185 185
1996, Qtr. 4 220 220 220
1997, Qtr. 1 241 241 241
1997, Qtr. 2 253 253 253
1997, Qtr. 3 256 256 256
FIGURE 2 (Desired Result):
Fields 1996, Qtr. 3 1996, Qtr. 4 1997, Qtr. 1
1997, Qtr. 2 1997, Qtr. 3
ctShipName 185 220 241
253 256
ctShipCity 185 220 241
253 256
ctShipCountry 185 220 241
253 256
CODE 1:
SELECT CAST(DATEPART(yyyy, Orders.OrderDate) AS VARCHAR) + ', Qtr. ' +
CAST(DATEPART(q,
Orders.OrderDate) AS VARCHAR) AS Quarter
COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity, COUNT(Orders.ShipCountry) AS
ctShipCountry
FROM Categories INNER JOIN
Products ON Categories.CategoryID =
Products.CategoryID INNER JOIN
Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID ON Products.ProductID = [Order Details].ProductID
GROUP BY CAST(DATEPART(yyyy, Orders.OrderDate) AS VARCHAR) + ', Qtr. ' +
CAST(DATEPART(q,
Orders.OrderDate) AS VARCHAR)
ORDER BY CAST(DATEPART(yyyy, Orders.OrderDate) AS VARCHAR) + ', Qtr. ' +
CAST(DATEPART(q,
Orders.OrderDate) AS VARCHAR)Scott (sbailey@.mileslumber.com) writes:
> My example code (CODE 1) returns quarter counts for several fields in
> Northwind (I'm using Northwind to simulate my actual data). It returns
> code that looks like FIGURE 1.
> Can my sql be modified so the Field names are the GROUPED BY and run down
> the 1st column while forcing the Quarters to be the header columns and
> replace the ctShipName, ctShipCity and ctShipCountry positions and result
> looking like FIGURE 2?
Yes, but to conserve space, I only include the first two quarters:
SELECT col AS " ",
SUM(CASE WHEN datepart(YEAR, O.OrderDate) = 1996 AND
datepart(Q, O.OrderDate) = 3 THEN
CASE col WHEN 'ctShipName' THEN
CASE WHEN O.ShipName IS NOT NULL THEN 1 ELSE 0 END
WHEN 'ctShipCity' THEN
CASE WHEN O.ShipCity IS NOT NULL THEN 1 ELSE 0 END
WHEN 'ctShipCountry' THEN
CASE WHEN O.ShipCountry IS NOT NULL THEN 1 ELSE 0 END
END
ELSE 0
END) AS "1996, Qtr. 3",
SUM(CASE WHEN datepart(YEAR, O.OrderDate) = 1996 AND
datepart(Q, O.OrderDate) = 4 THEN
CASE col WHEN 'ctShipName' THEN
CASE WHEN O.ShipName IS NOT NULL THEN 1 ELSE 0 END
WHEN 'ctShipCity' THEN
CASE WHEN O.ShipCity IS NOT NULL THEN 1 ELSE 0 END
WHEN 'ctShipCountry' THEN
CASE WHEN O.ShipCountry IS NOT NULL THEN 1 ELSE 0 END
END
ELSE 0
END) AS "1996, Qtr. 4"
FROM (SELECT col = 'ctShipName'
UNION ALL
SELECT 'ctShipCity'
UNION ALL
SELECT 'ctShipCountry') AS names
CROSS JOIN (Orders O
JOIN [Order Details] OD ON O.OrderID = OD.OrderID
JOIN Products P ON P.ProductID = OD.ProductID
JOIN Categories C ON C.CategoryID = P.CategoryID)
GROUP BY col
Now, I presume that in your real-world case, you result set is not
entirely static as here. In that case, you will have to play with
dynamic SQL again.
But before you rush ahead, have a look at http://www.rac4sql.net. This
is a third-party tool which reportedly is very good for crosstabs and
similar. I have never used it myself, but I've heard people speak
positively about it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||thanks, that was a tough one, but I see what I have to do.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9728DBD278581Yazorman@.127.0.0.1...
> Scott (sbailey@.mileslumber.com) writes:
> Yes, but to conserve space, I only include the first two quarters:
> SELECT col AS " ",
> SUM(CASE WHEN datepart(YEAR, O.OrderDate) = 1996 AND
> datepart(Q, O.OrderDate) = 3 THEN
> CASE col WHEN 'ctShipName' THEN
> CASE WHEN O.ShipName IS NOT NULL THEN 1 ELSE 0 END
> WHEN 'ctShipCity' THEN
> CASE WHEN O.ShipCity IS NOT NULL THEN 1 ELSE 0 END
> WHEN 'ctShipCountry' THEN
> CASE WHEN O.ShipCountry IS NOT NULL THEN 1 ELSE 0
> END
> END
> ELSE 0
> END) AS "1996, Qtr. 3",
> SUM(CASE WHEN datepart(YEAR, O.OrderDate) = 1996 AND
> datepart(Q, O.OrderDate) = 4 THEN
> CASE col WHEN 'ctShipName' THEN
> CASE WHEN O.ShipName IS NOT NULL THEN 1 ELSE 0 END
> WHEN 'ctShipCity' THEN
> CASE WHEN O.ShipCity IS NOT NULL THEN 1 ELSE 0 END
> WHEN 'ctShipCountry' THEN
> CASE WHEN O.ShipCountry IS NOT NULL THEN 1 ELSE 0
> END
> END
> ELSE 0
> END) AS "1996, Qtr. 4"
> FROM (SELECT col = 'ctShipName'
> UNION ALL
> SELECT 'ctShipCity'
> UNION ALL
> SELECT 'ctShipCountry') AS names
> CROSS JOIN (Orders O
> JOIN [Order Details] OD ON O.OrderID = OD.OrderID
> JOIN Products P ON P.ProductID = OD.ProductID
> JOIN Categories C ON C.CategoryID = P.CategoryID)
> GROUP BY col
> Now, I presume that in your real-world case, you result set is not
> entirely static as here. In that case, you will have to play with
> dynamic SQL again.
> But before you rush ahead, have a look at http://www.rac4sql.net. This
> is a third-party tool which reportedly is very good for crosstabs and
> similar. I have never used it myself, but I've heard people speak
> positively about it.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Aggregate Functions on char fields?

hi,
i wondered whether there is any way to simulate a kind of aggegate function
that summerizes char/varchar fiealds.
to make myself clear, please look at the following table t1 which has 2 int
fields:
f1 | f2
1 | 10
1 | 20
2 | 30
select f1, sum(f2) s1 from t1 group by f1
the result would be:
f1 | s1
1 | 30
2 | 30
so far so good.
now, please look at the following table t2 which as 1 int field and 1 char
field:
f1 | f2
1 | A
1 | B
2 | C
select f1, sum(f2) s1 from t2 group by f1
i want the result to be:
f1 | s1
1 | A,B
2 | C
is there any way to do it through 1 query only?
thanks!
edo
First of all it has nothing to do with aggregates. It is called a
contacenation
Second, I'd strongly recommend you doing such reports on the client side
create table w
(
id int,
t varchar(50) not null
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"edo" <ewilde@.nana.co.il> wrote in message
news:uHe0RT1lFHA.3316@.TK2MSFTNGP14.phx.gbl...
> hi,
> i wondered whether there is any way to simulate a kind of aggegate
> function
> that summerizes char/varchar fiealds.
> to make myself clear, please look at the following table t1 which has 2
> int
> fields:
> f1 | f2
> --
> 1 | 10
> 1 | 20
> 2 | 30
> select f1, sum(f2) s1 from t1 group by f1
> the result would be:
> f1 | s1
> --
> 1 | 30
> 2 | 30
> so far so good.
> now, please look at the following table t2 which as 1 int field and 1 char
> field:
> f1 | f2
> --
> 1 | A
> 1 | B
> 2 | C
> select f1, sum(f2) s1 from t2 group by f1
> i want the result to be:
> f1 | s1
> --
> 1 | A,B
> 2 | C
>
> is there any way to do it through 1 query only?
>
> thanks!
>
|||Thanks !!

Aggregate Functions on char fields?

hi,
i wondered whether there is any way to simulate a kind of aggegate function
that summerizes char/varchar fiealds.
to make myself clear, please look at the following table t1 which has 2 int
fields:
f1 | f2
--
1 | 10
1 | 20
2 | 30
select f1, sum(f2) s1 from t1 group by f1
the result would be:
f1 | s1
--
1 | 30
2 | 30
so far so good.
now, please look at the following table t2 which as 1 int field and 1 char
field:
f1 | f2
--
1 | A
1 | B
2 | C
select f1, sum(f2) s1 from t2 group by f1
i want the result to be:
f1 | s1
--
1 | A,B
2 | C
is there any way to do it through 1 query only?
thanks!edo
First of all it has nothing to do with aggregates. It is called a
contacenation
Second, I'd strongly recommend you doing such reports on the client side
create table w
(
id int,
t varchar(50) not null
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"edo" <ewilde@.nana.co.il> wrote in message
news:uHe0RT1lFHA.3316@.TK2MSFTNGP14.phx.gbl...
> hi,
> i wondered whether there is any way to simulate a kind of aggegate
> function
> that summerizes char/varchar fiealds.
> to make myself clear, please look at the following table t1 which has 2
> int
> fields:
> f1 | f2
> --
> 1 | 10
> 1 | 20
> 2 | 30
> select f1, sum(f2) s1 from t1 group by f1
> the result would be:
> f1 | s1
> --
> 1 | 30
> 2 | 30
> so far so good.
> now, please look at the following table t2 which as 1 int field and 1 char
> field:
> f1 | f2
> --
> 1 | A
> 1 | B
> 2 | C
> select f1, sum(f2) s1 from t2 group by f1
> i want the result to be:
> f1 | s1
> --
> 1 | A,B
> 2 | C
>
> is there any way to do it through 1 query only?
>
> thanks!
>|||Thanks !!

Aggregate Functions on char fields?

hi,
i wondered whether there is any way to simulate a kind of aggegate function
that summerizes char/varchar fiealds.
to make myself clear, please look at the following table t1 which has 2 int
fields:
f1 | f2
--
1 | 10
1 | 20
2 | 30
select f1, sum(f2) s1 from t1 group by f1
the result would be:
f1 | s1
--
1 | 30
2 | 30
so far so good.
now, please look at the following table t2 which as 1 int field and 1 char
field:
f1 | f2
--
1 | A
1 | B
2 | C
select f1, sum(f2) s1 from t2 group by f1
i want the result to be:
f1 | s1
--
1 | A,B
2 | C
is there any way to do it through 1 query only?
thanks!edo
First of all it has nothing to do with aggregates. It is called a
contacenation
Second, I'd strongly recommend you doing such reports on the client side
create table w
(
id int,
t varchar(50) not null
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"edo" <ewilde@.nana.co.il> wrote in message
news:uHe0RT1lFHA.3316@.TK2MSFTNGP14.phx.gbl...
> hi,
> i wondered whether there is any way to simulate a kind of aggegate
> function
> that summerizes char/varchar fiealds.
> to make myself clear, please look at the following table t1 which has 2
> int
> fields:
> f1 | f2
> --
> 1 | 10
> 1 | 20
> 2 | 30
> select f1, sum(f2) s1 from t1 group by f1
> the result would be:
> f1 | s1
> --
> 1 | 30
> 2 | 30
> so far so good.
> now, please look at the following table t2 which as 1 int field and 1 char
> field:
> f1 | f2
> --
> 1 | A
> 1 | B
> 2 | C
> select f1, sum(f2) s1 from t2 group by f1
> i want the result to be:
> f1 | s1
> --
> 1 | A,B
> 2 | C
>
> is there any way to do it through 1 query only?
>
> thanks!
>|||Thanks !!

again a problem with xml datasource extension

it seems to be that the xml datasource ignore empty fields. why?

example:

<?xml version="1.0" standalone="yes"?>
<Head>
<ID>121234</ID>
<Title>Hi</Title>
<Text></Text>
<Amount>3443.90</Amount>
</Head>

Only ID, Title and Amount appearing in the resultset.

any suggestions?

Instead of using autodetection you can specify explicitely what you want in the query

<Query>
...
<ElementPath> Head{ID, Title, Text, Amount} </ElementPath>
</Query>

|||

Dear Sasha, may be you know something about this error:

The reports based on XML is perfectly displayed in development Environment.

But than uploaded to Server, the following occurs:

· An error has occurred during report processing.

o An attempt has been made to use a data extension 'XML' that is not registered for this report server.

Thank you very much.

|||

See this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=376677&SiteID=1

-- Robert

again a problem with xml datasource extension

it seems to be that the xml datasource ignore empty fields. why?

example:

<?xml version="1.0" standalone="yes"?>
<Head>
<ID>121234</ID>
<Title>Hi</Title>
<Text></Text>
<Amount>3443.90</Amount>
</Head>

Only ID, Title and Amount appearing in the resultset.

any suggestions?

Instead of using autodetection you can specify explicitely what you want in the query

<Query>
...
<ElementPath> Head{ID, Title, Text, Amount} </ElementPath>
</Query>

|||

Dear Sasha, may be you know something about this error:

The reports based on XML is perfectly displayed in development Environment.

But than uploaded to Server, the following occurs:

· An error has occurred during report processing.

o An attempt has been made to use a data extension 'XML' that is not registered for this report server.

Thank you very much.

|||

See this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=376677&SiteID=1

-- Robert

After SSIS package runs All rows, All Fields are NULL in destination table ?

I am copying a simple table from a Sql Server 2005 database to an *.sdf mobile database.

I am brand new to SSIS and I am probably doing something wrong. But after executing the SSIS package all the rows and all the fields are NULL in the destination database. I put a datagrid viewer between the OLE DB Source and the Sql Server compact edition destination and I can see the real data which is obviously not ALL NULL.

Does anyone have a clue as to why it would be doing this?

Any help would be much appreciated.

Thanks...

I do't have a cue why this would be happening but if I were investigating I would start at the SQL End. That means SQL profiler to find out what insert statements are being issued. Admittedly I'm assuming that Profiler will work with SQL CE.

-Jamie