Collation problem: excel

Showing posts with label excel. Show all posts

Sunday, March 25, 2012

Combining data from differnet Excel sheets

Hi all
I need to add data from two Excel sheets (both on the same workbook)
to an existing table in my SQL DB.
The problem is that each sheet holds different fields for the same
record, though the records are sorted by row numbers.
(I had to split the fields to different sheets because Excel has a
limit of 256 fields in each sheet)
My sheets are quite large (~55,000 rows and 200 columns each) and I'll
have to repeat this action many time, so some kind of an automation
will nice (VB, DTS package?)

I am new to SQLServer, so an example will be most helpful.

Many thanks,
Ilan"Ilan" <levyi@.walla.co.il> wrote in message
news:78b6d73b.0402260720.530dac71@.posting.google.c om...
> Hi all
> I need to add data from two Excel sheets (both on the same workbook)
> to an existing table in my SQL DB.
> The problem is that each sheet holds different fields for the same
> record, though the records are sorted by row numbers.
> (I had to split the fields to different sheets because Excel has a
> limit of 256 fields in each sheet)
> My sheets are quite large (~55,000 rows and 200 columns each) and I'll
> have to repeat this action many time, so some kind of an automation
> will nice (VB, DTS package?)
> I am new to SQLServer, so an example will be most helpful.
> Many thanks,
> Ilan

I don't really understand your description, but you might look at using DTS
to load the data into staging tables, then use TSQL to move the data to the
production tables. That's a common general approach, but without more
specific details of what you're trying to do, perhaps including a more exact
description of the data and the destination table structure, it's not easy
to be more precise.

Simon|||Simon,
Sorry for not being clear enough.
What I have are two sheets, each holding 200 columns (fields 1-200 at
the first and fields 201-400 at the second), where each row (or
record) starts at the first sheet and continues at the second. All the
data is numeric.
What I need to do is somehow concatenate the data on the corresponding
rows from both sheets and (eventualy) append them to an existing table
in MSSQL.
I could try and export each sheet as a seperate CSV file and then work
with them as text file from some DOS manipulation, but since they are
very long (50,000 rows) it doesn't look easier.
The data in the sheets in arranged according to the target SQL table,
that is columns 1-200 in the first sheet are fields 1-200 in the
table, and columns 1-200 in the second sheet are fields 201-400.
I am new to MSSQL so I don't know how to work with the tables within
SQL, but if you show me the path, I'll follow your leads.

Thanks,
Ilan

"Simon Hayes" <sql@.hayes.ch> wrote in message news:<403e40b5$1_3@.news.bluewin.ch>...
> "Ilan" <levyi@.walla.co.il> wrote in message
> news:78b6d73b.0402260720.530dac71@.posting.google.c om...
> > Hi all
> > I need to add data from two Excel sheets (both on the same workbook)
> > to an existing table in my SQL DB.
> > The problem is that each sheet holds different fields for the same
> > record, though the records are sorted by row numbers.
> > (I had to split the fields to different sheets because Excel has a
> > limit of 256 fields in each sheet)
> > My sheets are quite large (~55,000 rows and 200 columns each) and I'll
> > have to repeat this action many time, so some kind of an automation
> > will nice (VB, DTS package?)
> > I am new to SQLServer, so an example will be most helpful.
> > Many thanks,
> > Ilan
> I don't really understand your description, but you might look at using DTS
> to load the data into staging tables, then use TSQL to move the data to the
> production tables. That's a common general approach, but without more
> specific details of what you're trying to do, perhaps including a more exact
> description of the data and the destination table structure, it's not easy
> to be more precise.
> Simon|||Here's how I might go about this:

1) Create the 400-field table on SQL Server. (I don't have an easy way to that
w/o knowing the data). If you don't have reall field names, then use Fld001,
Fld002, Fld003, ... Fld400

2) Add a 401st field, RowID, int NOT NULL

3) Open your Excel workbook, and put the formula "=ROW(A1)" in cell A1 on both
sheets, and fill it down.

4) Insert a row at row 1, and put in the same field names you had in you SQL
table, RowID, Fld001, Fld002, etc

5) Place that Excel workbook either on the SQL Server directly, or on a machine
that the SQL Server can access w/ Admin privs.

6) Import the data (by right-clicking the destination table and choosing All
tasks>import data). When the wizard comes up, choose Excel file as source, and
the destination.

In the next wizard screem choose Use a query to copy data.

In the next screen, add Sheet1 and Sheet2, all fields.

In the next screen (I think it's the sort screen), you can blow by it.

In the next screen, specify the condition as Sheet1.RowID = Sheet2.RowID

The next screen you tell it which table to append to. Pick the destination
table, and finish (run it!)

That should do it...assuming your destination table and your Excel file have
field names exactly the same.|||levyi@.walla.co.il (Ilan) wrote in message news:<78b6d73b.0402261607.49674ae7@.posting.google.com>...
> Simon,
> Sorry for not being clear enough.
> What I have are two sheets, each holding 200 columns (fields 1-200 at
> the first and fields 201-400 at the second), where each row (or
> record) starts at the first sheet and continues at the second. All the
> data is numeric.
> What I need to do is somehow concatenate the data on the corresponding
> rows from both sheets and (eventualy) append them to an existing table
> in MSSQL.
> I could try and export each sheet as a seperate CSV file and then work
> with them as text file from some DOS manipulation, but since they are
> very long (50,000 rows) it doesn't look easier.
> The data in the sheets in arranged according to the target SQL table,
> that is columns 1-200 in the first sheet are fields 1-200 in the
> table, and columns 1-200 in the second sheet are fields 201-400.
> I am new to MSSQL so I don't know how to work with the tables within
> SQL, but if you show me the path, I'll follow your leads.
> Thanks,
> Ilan
> "Simon Hayes" <sql@.hayes.ch> wrote in message news:<403e40b5$1_3@.news.bluewin.ch>...
> > "Ilan" <levyi@.walla.co.il> wrote in message
> > news:78b6d73b.0402260720.530dac71@.posting.google.c om...
> > > Hi all
> > > I need to add data from two Excel sheets (both on the same workbook)
> > > to an existing table in my SQL DB.
> > > The problem is that each sheet holds different fields for the same
> > > record, though the records are sorted by row numbers.
> > > (I had to split the fields to different sheets because Excel has a
> > > limit of 256 fields in each sheet)
> > > My sheets are quite large (~55,000 rows and 200 columns each) and I'll
> > > have to repeat this action many time, so some kind of an automation
> > > will nice (VB, DTS package?)
> > > > I am new to SQLServer, so an example will be most helpful.
> > > > Many thanks,
> > > Ilan
> > I don't really understand your description, but you might look at using DTS
> > to load the data into staging tables, then use TSQL to move the data to the
> > production tables. That's a common general approach, but without more
> > specific details of what you're trying to do, perhaps including a more exact
> > description of the data and the destination table structure, it's not easy
> > to be more precise.
> > Simon

First of all, if you have a 400 column table, then you should probably
review your data model - there aren't many situations where there is a
genuine need for such a table. In fact, depending on what your data
is, you might find that you can't do this anyway, since you are
limited to 8060 bytes per row.

But if you really do need to do this, then one possible solution is
along the following lines:

1. Add a column in Excel to identify each row. Make sure that this
correctly 'links' the data on each sheet, ie. row 1 on sheet A is
'continued' on row 1 on sheet B.

2. Create two staging tables, each with 201 columns, for the ID column
plus the 200 data columns.

3. Load the sheets into the staging tables with DTS

4. Insert the data into the final table:

INSERT INTO dbo.Destination
SELECT a.col1, a.col2, ..., a.col200, b.col201, b.col202, ...,
b.col400
FROM dbo.StagingA a
JOIN dbo.Staging B b
ON A.RowID = b.RowID

However, I strongly suggest your review your data model, for the
reasons I mentioned above.

Simon|||sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0402270117.1d1202fd@.posting.google.com>...
> levyi@.walla.co.il (Ilan) wrote in message news:<78b6d73b.0402261607.49674ae7@.posting.google.com>...
> > Simon,
> > Sorry for not being clear enough.
> > What I have are two sheets, each holding 200 columns (fields 1-200 at
> > the first and fields 201-400 at the second), where each row (or
> > record) starts at the first sheet and continues at the second. All the
> > data is numeric.
> > What I need to do is somehow concatenate the data on the corresponding
> > rows from both sheets and (eventualy) append them to an existing table
> > in MSSQL.
> > I could try and export each sheet as a seperate CSV file and then work
> > with them as text file from some DOS manipulation, but since they are
> > very long (50,000 rows) it doesn't look easier.
> > The data in the sheets in arranged according to the target SQL table,
> > that is columns 1-200 in the first sheet are fields 1-200 in the
> > table, and columns 1-200 in the second sheet are fields 201-400.
> > I am new to MSSQL so I don't know how to work with the tables within
> > SQL, but if you show me the path, I'll follow your leads.
> > Thanks,
> > Ilan
> > "Simon Hayes" <sql@.hayes.ch> wrote in message news:<403e40b5$1_3@.news.bluewin.ch>...
> > > "Ilan" <levyi@.walla.co.il> wrote in message
> > > news:78b6d73b.0402260720.530dac71@.posting.google.c om...
> > > > Hi all
> > > > I need to add data from two Excel sheets (both on the same workbook)
> > > > to an existing table in my SQL DB.
> > > > The problem is that each sheet holds different fields for the same
> > > > record, though the records are sorted by row numbers.
> > > > (I had to split the fields to different sheets because Excel has a
> > > > limit of 256 fields in each sheet)
> > > > My sheets are quite large (~55,000 rows and 200 columns each) and I'll
> > > > have to repeat this action many time, so some kind of an automation
> > > > will nice (VB, DTS package?)
> > > > > > I am new to SQLServer, so an example will be most helpful.
> > > > > > Many thanks,
> > > > Ilan
> > > > I don't really understand your description, but you might look at using DTS
> > > to load the data into staging tables, then use TSQL to move the data to the
> > > production tables. That's a common general approach, but without more
> > > specific details of what you're trying to do, perhaps including a more exact
> > > description of the data and the destination table structure, it's not easy
> > > to be more precise.
> > > > Simon
> First of all, if you have a 400 column table, then you should probably
> review your data model - there aren't many situations where there is a
> genuine need for such a table. In fact, depending on what your data
> is, you might find that you can't do this anyway, since you are
> limited to 8060 bytes per row.
> But if you really do need to do this, then one possible solution is
> along the following lines:
> 1. Add a column in Excel to identify each row. Make sure that this
> correctly 'links' the data on each sheet, ie. row 1 on sheet A is
> 'continued' on row 1 on sheet B.
> 2. Create two staging tables, each with 201 columns, for the ID column
> plus the 200 data columns.
> 3. Load the sheets into the staging tables with DTS
> 4. Insert the data into the final table:
> INSERT INTO dbo.Destination
> SELECT a.col1, a.col2, ..., a.col200, b.col201, b.col202, ...,
> b.col400
> FROM dbo.StagingA a
> JOIN dbo.Staging B b
> ON A.RowID = b.RowID
> However, I strongly suggest your review your data model, for the
> reasons I mentioned above.
> Simon

Simon,
I spent the last weekend trying to get Excel to work with this amount
of data, without success. I guess I've reached his limits (there is a
good description of Excel memory limits in:
http://www.decisionmodels.com/memlimitsc.htm , FYI).
So I decided I'll export the data to 10 separate CSV files (each
holding ~50 columns) and then use another application (e.g. MathLab)
to join them back together. Another option is to run all the
calculations in MathLab.
As for the data, what I have is meteorological data every 5 minutes
for 6 months (hence the 52,560 rows). For this data I'm calculating 4
other parameters for every 1 hour, 2 hours, ..., 96 hours (hence the
4x96 plus 20 original columns, giving 404 columns). Then I have a
total of 4 years data for each meteorological station (yielding
420,480 rows) that needs to be multiplied by 15 meteorological
station. Altogether I have over 6,000,000 records and 400 fields to
analyze. The only solution I can think of for this amount of data is
to put it on an SQL DB, and run queries that will be analyzed with a
statistical program (e.g. SPSS, JMP or other). For the record, this is
part of my PhD thesis on air pollution and meteorology in a coastal
region.
If anyone has any experience analyzing this amount of data that he is
willing to share, I'll be happy to learn.

Ilan|||Ilan,

You didn't say if the data load procedure that I suggested (and Simon
reiterated, almost word-for-word) got your data into SQL Server.

Now you seem to be asking a different question, like "Gee, what tool should I
be using for this data?"

We can't make that decision unless we know exactly what kinds of calculations
you plan to do. For if you're doing complex math analysis (using advanced
statistical models, complex numbers, or hyperbolic trigonometry), then you're
probably better-off with a product like MathLab.

If you're just doing normal stats (Avg, StDev, basic trig), then SQL Server
will shine.

Now, about your data model:

<<As for the data, what I have is meteorological data every 5 minutes
for 6 months (hence the 52,560 rows). For this data I'm calculating 4
other parameters for every 1 hour, 2 hours, ..., 96 hours (hence the
4x96 plus 20 original columns, giving 404 columns>
OK, so you have 20 values coming in every 5 minutes, and then 4 calculated
values every hour up to 96 hours. I have to assume that your 4 calucalted
values are "rolling" calucaltions; otherwise, your model would contain 4X96
duplicate columns of data every 12 rows...

...do I have it right so far?

Without knowing how the data is going to be used, it's hard to sugeest a good
data model. Maybe the one-table is good because you know you're always showing
all 400+ fields in your reports! (I don't know how any human could
intelligently parse such printed data, but meteorologists might be special...)

More likely, you're pulling reports like this: Show the 24/48/72/96 hour trends
for these 3 locations from May to August for [special calculation 1 of the 4].
That's a 12-cell report...much more manageable, useful and meaningful. If
that's more like how the data will be used, then you might break the massive
table into 5 tables:

Table1: The 20 fields
Table2: 96 fields for the first calc (+ the key field(s) from Table1)
Table3: 96 fields for the 2nd calc (+ the key field(s) from Table1)
Table4: 96 fields for the 3rd calc (+ the key field(s) from Table1)
Table5: 96 fields for the 4th calc (+ the key field(s) from Table1)

Another approach would be to ditch the 96-field idea altogether, instead using
4X96 RECORDS in a realted table, using sound relational models, just in case
your 4 calc fields becomes 5 some day! And what if your 20 fields becomes 21,
22?

That might be ultra-ugly in your case, because you'd have a parent table with 6
million records, and a child table with 2.4 BILLION records. (ea of the 6 mil
would have ~400 child records)

I only sugest that as a matter of completeness...a true relational approach
probably ISN'T what you need. Technically, the relational model dictates that
you'd have 120,000,000 Measurement records (6 Mil X 20), with everything else
(the 4X96) being calcualted as needed! That's the absolute most-flexible way to
do this, because it doesn't care how many original (20) or calc (4) fields
there are.

But it might also be the SLOWEST execution. (if your calcs are just averages,
that's not a problem)

Anyway, I'm just throwing all of this out as food for thought. This much I
know:

SQL Server can certainly load the data from those Excel files...|||dcmfan@.aol.comSPNOAM (DCM Fan) wrote in message news:<20040301041339.02011.00000734@.mb-m19.aol.com>...
> Ilan,
> You didn't say if the data load procedure that I suggested (and Simon
> reiterated, almost word-for-word) got your data into SQL Server.

I did try your procedure with a sample file and it works greate,
except for two problems: First, Excel couldn't handle the calculations
of files this size (it stopped responding), so obviously I cannot use
it for my needs (I only found this limit AFTER posting my original
message). Second, I have to repeat this action many times, so I'm
hopping for a way to automate the proccess like using VB.

> Now you seem to be asking a different question, like "Gee, what tool should I
> be using for this data?"
> We can't make that decision unless we know exactly what kinds of calculations
> you plan to do. For if you're doing complex math analysis (using advanced
> statistical models, complex numbers, or hyperbolic trigonometry), then you're
> probably better-off with a product like MathLab.
> If you're just doing normal stats (Avg, StDev, basic trig), then SQL Server
> will shine.

Well, this is why I wanted to use Excel in the first place. I am
calculating some basic trig (sin, cos) and algebra both on multiple
rows (e.g. every hour) and on columns. This is easier to do on a
Spreadsheet or a matrix (hence MatLab) then on a Database.

> Now, about your data model:
> <<As for the data, what I have is meteorological data every 5 minutes
> for 6 months (hence the 52,560 rows). For this data I'm calculating 4
> other parameters for every 1 hour, 2 hours, ..., 96 hours (hence the
> 4x96 plus 20 original columns, giving 404 columns>>
> OK, so you have 20 values coming in every 5 minutes, and then 4 calculated
> values every hour up to 96 hours. I have to assume that your 4 calucalted
> values are "rolling" calucaltions; otherwise, your model would contain 4X96
> duplicate columns of data every 12 rows...
> ...do I have it right so far?

Yes.

> Without knowing how the data is going to be used, it's hard to sugeest a good
> data model. Maybe the one-table is good because you know you're always showing
> all 400+ fields in your reports! (I don't know how any human could
> intelligently parse such printed data, but meteorologists might be special...)

Thats where 3D graphics come in, displaying the data in such a way
that a human eye can SEE it and make some sense of it all.

> More likely, you're pulling reports like this: Show the 24/48/72/96 hour trends
> for these 3 locations from May to August for [special calculation 1 of the 4].
> That's a 12-cell report...much more manageable, useful and meaningful. If
> that's more like how the data will be used, then you might break the massive
> table into 5 tables:
> Table1: The 20 fields
> Table2: 96 fields for the first calc (+ the key field(s) from Table1)
> Table3: 96 fields for the 2nd calc (+ the key field(s) from Table1)
> Table4: 96 fields for the 3rd calc (+ the key field(s) from Table1)
> Table5: 96 fields for the 4th calc (+ the key field(s) from Table1)

Yes, I will also be making these kind of analysis/queries on the data,
but I'm not sure I understand why 5 tables are better then putting all
the data in one. This is another issue I know I need the advise of
experts.

> Another approach would be to ditch the 96-field idea altogether, instead using
> 4X96 RECORDS in a realted table, using sound relational models, just in case
> your 4 calc fields becomes 5 some day! And what if your 20 fields becomes 21,
> 22?

Hmmm. Well, this is certainly a food for thought, and I see I'll have
to re-think the way I want to use the database and what kind of data
I'll need to pull out of it.
On a second thought, this is one of the attraction of doing pure
research: you don't always know what kind of answers you'll get, and
what new questions these answers will raise.

Many thanks,
Ilan
PS, I'll be doing this thinking in the next week in Paris, France on
vacation. Please excuse if I don't post replies.|||<<First, Excel couldn't handle the calculations
of files this size (it stopped responding), so obviously I cannot use
it for my needs (I only found this limit AFTER posting my original
message). Second, I have to repeat this action many times, so I'm
hopping for a way to automate the proccess like using VB.>
Oh, so you still need the Excel files, but you also need a way to load
historical data into SQL Server ultimately. I was under the impression that
Excel would go away completely...

<<Well, this is why I wanted to use Excel in the first place. I am
calculating some basic trig (sin, cos) and algebra both on multiple
rows (e.g. every hour) and on columns. This is easier to do on a
Spreadsheet or a matrix (hence MatLab) then on a Database.>
Yes, easier for humans b/c of its 2-dimensional element. Databases are more
3-D-ish, and a bit more difficult to grasp AT FIRST, but once you see the
light, you'll wish you had started in SQL. The programming language of SQL
Server (T-SQL) will give you ever set operation you need...

<<Thats where 3D graphics come in, displaying the data in such a way
that a human eye can SEE it and make some sense of it all.>
No doubt...then you're obviously going to be using some other tool for that!

<<Yes, I will also be making these kind of analysis/queries on the data,
but I'm not sure I understand why 5 tables are better then putting all
the data in one. This is another issue I know I need the advise of
experts.>
Well, if you're the only person who's going to use this tool, it might not
matter. But if you're going to build a web front-end where hundreds of users
can query the data and get reports, then it makes sense to break it up. If you
ever need data from all 5 tables, great, join them on the key fields. But if
you don't, then you can save processing time by limiting the domain to the
target calcuations.

<<On a second thought, this is one of the attraction of doing pure
research: you don't always know what kind of answers you'll get, and
what new questions these answers will raise.>
But it seems that the technology is actually getting in the way of your
research...

Combining Cross-Tab and charts

Dear all,
I'm a noob to SQL Reporting Services but have plenty of experience with
other MS applications (including excel and access). I was wondering whether
it's possible to combine a cross-tab and a chart within a single report. If
so, how do you do this? I believe the power of a report (often) exists out of
numbers combined with a graphical display.
thanks in advance,
mischaOn Apr 26, 10:22 am, mischa <mis...@.discussions.microsoft.com> wrote:
> Dear all,
> I'm a noob to SQL Reporting Services but have plenty of experience with
> other MS applications (including excel and access). I was wondering whether
> it's possible to combine a cross-tab and a chart within a single report. If
> so, how do you do this? I believe the power of a report (often) exists out of
> numbers combined with a graphical display.
> thanks in advance,
> mischa
If I understand you correctly, you should be able to use a matrix
control and a chart control.
Regards,
Enrique Martinez
Sr. Software Consultant

Thursday, March 8, 2012

Columns on Rows?

Plastic is on the bar, have a drink on me. I have a table that came from
Excel and I am trying to normalize it. There is one could with an ID and
between 2 and 29 columns of varchar data. These columns are NULL for the
remainder of columns. here are a few rows:
1 abc def NULL NULL NULL etc...
2 qwe rty erd dsw NULL NULL etc...
3 abc fgh NULL NULL NULL NULL etc...
How do I write a query that will normalize this data?
I want something like this:
1 abc
1 def
2 qwe
2 rty
2 erd
2 dsw
3 abc
3 fgh
Thanks in advance,
TonyThis is a multi-part message in MIME format.
--=_NextPart_000_0312_01C37ECA.0AA38CD0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Try:
select
ID
, Col
from
(
select
b.ID
, case x.Period
when 1 then b.Col1
when 2 then b.Col2
when 3 then b.Col3 -- and so on
end as Col
from
Budgets as b
cross join
(
select 1 as Period
union all
select 2
union all
select 3 -- and so on
) as x
) as y
where
Col is not null
order by
ID
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message =news:#JrqsoufDHA.1700@.TK2MSFTNGP10.phx.gbl...
Plastic is on the bar, have a drink on me. I have a table that came =from
Excel and I am trying to normalize it. There is one could with an ID =and
between 2 and 29 columns of varchar data. These columns are NULL for =the
remainder of columns. here are a few rows:
1 abc def NULL NULL NULL etc...
2 qwe rty erd dsw NULL NULL etc...
3 abc fgh NULL NULL NULL NULL etc...
How do I write a query that will normalize this data?
I want something like this:
1 abc
1 def
2 qwe
2 rty
2 erd
2 dsw
3 abc
3 fgh
Thanks in advance,
Tony
--=_NextPart_000_0312_01C37ECA.0AA38CD0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Try:
select
ID
, Col
from
(
select
b.ID
, =case x.Period
=when 1 then b.Col1
=when 2 then b.Col2
=when 3 then b.Col3 -- and so on
end =as Col
from
=Budgets as b
cross join
(
=select 1 as Period
=union all
=select 2
=union all
=select 3 -- and so on
) as x
) as y
where
Col is not null
order by
ID
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Anthony Zessin" =wrote in message news:#JrqsoufDHA.1700=@.TK2MSFTNGP10.phx.gbl...Plastic is on the bar, have a drink on me. I have a table that came =fromExcel and I am trying to normalize it. There is one could with an ID andbetween 2 and 29 columns of varchar data. These columns are =NULL for theremainder of columns. here are a few rows:1 abc =def NULL NULL =NULL etc...2 qwe =rty erd dsw NULL NULL etc...3 =abc fgh NULL =NULL NULL NULL etc...How do I =write a query that will normalize this data?I want something like this:1 abc1 def2 qwe2 =rty2 erd2 dsw3 =abc3 fghThanks in advance,Tony

--=_NextPart_000_0312_01C37ECA.0AA38CD0--|||Cross-tab query / pivot table.
Code:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q175574
http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_14_04j7.asp
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608
http://www.sqlteam.com/item.asp?ItemID=2955
Software:
http://www.rac4sql.net
http://www.ag-software.com/xp_ags_crosstab.asp
http://www.tysonsoftware.co.uk/TheQueryTool.asp
"Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
news:#JrqsoufDHA.1700@.TK2MSFTNGP10.phx.gbl...
> Plastic is on the bar, have a drink on me. I have a table that came from
> Excel and I am trying to normalize it. There is one could with an ID and
> between 2 and 29 columns of varchar data. These columns are NULL for the
> remainder of columns. here are a few rows:
> 1 abc def NULL NULL NULL etc...
> 2 qwe rty erd dsw NULL NULL etc...
> 3 abc fgh NULL NULL NULL NULL etc...
> How do I write a query that will normalize this data?
> I want something like this:
> 1 abc
> 1 def
> 2 qwe
> 2 rty
> 2 erd
> 2 dsw
> 3 abc
> 3 fgh
>
> Thanks in advance,
> Tony
>
>|||An approach using UDF and cursor.
Create sample data :
drop table tt
create table tt (i int, col1 varchar(5), col2 varchar(5))
insert into tt values(1, 'val1', 'val2')
insert into tt values(2, 'val1', 'val2')
insert into tt values(3, 'val1', 'val2')
--you will have to create a function that will change columnn seperated values to the rows.
--create a function as it is no need to change it. only change the way you call it as
--shown in the following script.
CREATE FUNCTION seperator
(@.str varchar(8000), @.del varchar(1))
RETURNS @.fin_tab TABLE (val varchar(8000)) AS
BEGIN
DECLARE @.pos int
DECLARE @.final_val varchar(8000)
SELECT @.str = LTRIM(RTRIM(@.str))
IF RIGHT(@.str,1) = @.del
set @.str = LEFT(@.str, Len(@.str) - 1)
set @.pos = charindex(@.del, @.str)
WHILE @.pos > 0
BEGIN
SET @.final_val = left(@.str, charindex(@.del, @.str) - 1)
INSERT @.fin_tab VALUES(@.final_val )
select @.str = substring(@.str, @.pos + 1, len(@.str)), @.pos = charindex(@.del, @.str)
END
INSERT @.fin_tab VALUES(@.str)
RETURN
END
go
--Following is the T-SQL code to call the above function and loop through the table.
declare @.i int, @.col1 varchar(5), @.col2 varchar(5)
declare @.str varchar(8000)
if object_id('tempdb..#temp_tab') is not null
drop table #temp_tab
create table #temp_tab(i int, crostab_col varchar(5))
declare c1 cursor static for
SELECT i, col1, col2 from tt
open c1
fetch next from c1 into @.i, @.col1, @.col2
while @.@.fetch_status = 0
begin
set @.str = @.col1 + ';' + @.col2
insert into #temp_tab
select @.i idd ,val from seperator(@.str , ';')
fetch next from c1 into @.i, @.col1, @.col2
end
close c1
deallocate c1
--final table with the columns turned to rows. of the above TT table
select * from #temp_Tab
--
- Vishal|||This is a multi-part message in MIME format.
--=_NextPart_000_0015_01C37EBB.3E88E950
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Brilliant! Thank you.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OsY3UuufDHA.3616@.TK2MSFTNGP11.phx.gbl...
Try:
select
ID
, Col
from
(
select
b.ID
, case x.Period
when 1 then b.Col1
when 2 then b.Col2
when 3 then b.Col3 -- and so on
end as Col
from
Budgets as b
cross join
(
select 1 as Period
union all
select 2
union all
select 3 -- and so on
) as x
) as y
where
Col is not null
order by
ID
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message =news:#JrqsoufDHA.1700@.TK2MSFTNGP10.phx.gbl...
Plastic is on the bar, have a drink on me. I have a table that came =from
Excel and I am trying to normalize it. There is one could with an ID =and
between 2 and 29 columns of varchar data. These columns are NULL for =the
remainder of columns. here are a few rows:
1 abc def NULL NULL NULL etc...
2 qwe rty erd dsw NULL NULL etc...
3 abc fgh NULL NULL NULL NULL etc...
How do I write a query that will normalize this data?
I want something like this:
1 abc
1 def
2 qwe
2 rty
2 erd
2 dsw
3 abc
3 fgh
Thanks in advance,
Tony
--=_NextPart_000_0015_01C37EBB.3E88E950
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns:o =3D "urn:schemas-microsoft-com:office:office"><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =charset=3Dwindows-1252">
<META content=3D"MSHTML 6.00.2800.1226" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#c0c0c0>
<DIV>Brilliant! </DIV>
<DIV>Thank you.</DIV>
<BLOCKQUOTE style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Tom Moreau" <<A =href=3D"mailto:tom@.dont.spam.me.cips.ca">tom@.dont.spam.me.cips.ca</A>>= wrote in message <A =href=3D"news:OsY3UuufDHA.3616@.TK2MSFTNGP11.phx.gbl">news:OsY3UuufDHA.3616=@.TK2MSFTNGP11.phx.gbl</A>...</DIV>
<DIV>Try:</DIV>
<DIV> </DIV>
<DIV>
select
 ID
, Col
from
(
select<o:p></o:p>
 b.ID
, =case x.Period<o:p></o:p>
 =
when 1 then b.Col1<o:p></o:p>
 =
when 2 then b.Col2<o:p></o:p>
 =
when 3 then b.Col3 -- and so on
 =end as Col
from<o:p></o:p>
 =Budgets as b<o:p></o:p>
cross join<o:p></o:p>
(<o:p></o:p>
 =select 1 as Period<o:p></o:p>
 =union all<o:p></o:p>
 =select 2<o:p></o:p>
 =union all<o:p></o:p>
 =select 3 -- and so on<o:p></o:p>
) as x
) as y
where
<o:p> Col is not null</o:p>
order by<o:p></o:p>
 ID</DIV>
<DIV> -- Tom</DIV>
<DIV> </DIV>
=<DIV>--- T=homas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL =Server Professional Toronto, ON Canada <A =href=3D"www.pinnaclepublishing.com=">http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=
/sql</A></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>"Anthony Zessin" <<A =href=3D"mailto:Anthony.Zessin@.rrtc.com">Anthony.Zessin@.rrtc.com</A>> =wrote in message <A =href=3D"news:#JrqsoufDHA.1700@.TK2MSFTNGP10.phx.gbl">news:#JrqsoufDHA.1700=@.TK2MSFTNGP10.phx.gbl</A>...</DIV>Plastic is on the bar, have a drink on me. I have a table that came from Excel and I am trying to normalize it. There is one =could with an ID and between 2 and 29 columns of varchar data. These =columns are NULL for the remainder of columns. here are a few rows: 1 abc =def NULL NULL =NULL etc... 2 qwe =rty erd dsw NULL NULL etc... 3 =abc fgh NULL =NULL NULL NULL etc... How do I =write a query that will normalize this data? I want something like this: 1 abc 1 def 2 qwe 2 rty 2 erd 2 dsw 3 abc 3 =fgh Thanks in advance, Tony </BLOCKQUOTE></BODY></HTML>
--=_NextPart_000_0015_01C37EBB.3E88E950--|||This is a multi-part message in MIME format.
--=_NextPart_000_0025_01C37EE6.F21FBD10
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Um, about that plastic on the bar. I'd like a cold beer and a Dr. =McGillicuddy's Peppermint Schnapps - to go. ;-)
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message =news:uCO9rXvfDHA.2188@.TK2MSFTNGP10.phx.gbl...
Brilliant! Thank you.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OsY3UuufDHA.3616@.TK2MSFTNGP11.phx.gbl...
Try:
select
ID
, Col
from
(
select
b.ID
, case x.Period
when 1 then b.Col1
when 2 then b.Col2
when 3 then b.Col3 -- and so on
end as Col
from
Budgets as b
cross join
(
select 1 as Period
union all
select 2
union all
select 3 -- and so on
) as x
) as y
where
Col is not null
order by
ID
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message =news:#JrqsoufDHA.1700@.TK2MSFTNGP10.phx.gbl...
Plastic is on the bar, have a drink on me. I have a table that came =from
Excel and I am trying to normalize it. There is one could with an ID =and
between 2 and 29 columns of varchar data. These columns are NULL for =the
remainder of columns. here are a few rows:
1 abc def NULL NULL NULL etc...
2 qwe rty erd dsw NULL NULL etc...
3 abc fgh NULL NULL NULL NULL etc...
How do I write a query that will normalize this data?
I want something like this:
1 abc
1 def
2 qwe
2 rty
2 erd
2 dsw
3 abc
3 fgh
Thanks in advance,
Tony
--=_NextPart_000_0025_01C37EE6.F21FBD10
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns:o =3D "urn:schemas-microsoft-com:office:office"><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =charset=3Dwindows-1252">
<META content=3D"MSHTML 6.00.2800.1226" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#c0c0c0>
<DIV>Um, about that plastic on the =bar. I'd like a cold beer and a Dr. McGillicuddy's Peppermint Schnapps - to go. ;-)</DIV>
<DIV> -- Tom</DIV>
<DIV> </DIV>
<DIV>---- Thomas A. =Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada <A href=3D"www.pinnaclepublishing.com=">http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=
/sql</A> .</DIV>
<DIV>"Anthony Zessin" <<A href=3D"mailto:Anthony.Zessin@.rrtc.com">Anthony.Zessin@.rrtc.com</A>> =wrote in message <A href=3D"news:uCO9rXvfDHA.2188@.TK2MSFTNGP10.phx.gbl">news:uCO9rXvfDHA.2188=@.TK2MSFTNGP10.phx.gbl</A>...</DIV>
<DIV>Brilliant! </DIV>
<DIV>Thank you.</DIV>
<BLOCKQUOTE style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Tom Moreau" <<A =href=3D"mailto:tom@.dont.spam.me.cips.ca">tom@.dont.spam.me.cips.ca</A>>= wrote in message <A =href=3D"news:OsY3UuufDHA.3616@.TK2MSFTNGP11.phx.gbl">news:OsY3UuufDHA.3616=@.TK2MSFTNGP11.phx.gbl</A>...</DIV>
<DIV>Try:</DIV>
<DIV> </DIV>
<DIV>
select
 ID
, Col
from
(
select<o:p></o:p>
 b.ID
, =case x.Period<o:p></o:p>
 =
when 1 then b.Col1<o:p></o:p>
 =
when 2 then b.Col2<o:p></o:p>
 =
when 3 then b.Col3 -- and so on
 =end as Col
from<o:p></o:p>
 =Budgets as b<o:p></o:p>
cross join<o:p></o:p>
(<o:p></o:p>
 =select 1 as Period<o:p></o:p>
 =union all<o:p></o:p>
 =select 2<o:p></o:p>
 =union all<o:p></o:p>
 =select 3 -- and so on<o:p></o:p>
) as x
) as y
where
<o:p> Col is not null</o:p>
order by<o:p></o:p>
 ID</DIV>
<DIV> -- Tom</DIV>
<DIV> </DIV>
=<DIV>--- T=homas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL =Server Professional Toronto, ON Canada <A =href=3D"www.pinnaclepublishing.com=">http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=
/sql</A></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>"Anthony Zessin" <<A =href=3D"mailto:Anthony.Zessin@.rrtc.com">Anthony.Zessin@.rrtc.com</A>> =wrote in message <A =href=3D"news:#JrqsoufDHA.1700@.TK2MSFTNGP10.phx.gbl">news:#JrqsoufDHA.1700=@.TK2MSFTNGP10.phx.gbl</A>...</DIV>Plastic is on the bar, have a drink on me. I have a table that came from Excel and I am trying to normalize it. There is one =could with an ID and between 2 and 29 columns of varchar data. These =columns are NULL for the remainder of columns. here are a few rows: 1 abc =def NULL NULL =NULL etc... 2 qwe =rty erd dsw NULL NULL etc... 3 =abc fgh NULL =NULL NULL NULL etc... How do I =write a query that will normalize this data? I want something like this: 1 abc 1 def 2 qwe 2 rty 2 erd 2 dsw 3 abc 3 =fgh Thanks in advance, Tony </BLOCKQUOTE></BODY></HTML>
--=_NextPart_000_0025_01C37EE6.F21FBD10--|||This is a multi-part message in MIME format.
--=_NextPart_000_0032_01C37EE6.FA7CB300
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
A cursor, you say? ;-)
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Vishal Parkar" <vgparkar@.hotmail.com> wrote in message =news:uhz$hzufDHA.3200@.tk2msftngp13.phx.gbl...
An approach using UDF and cursor.
Create sample data :
drop table tt
create table tt (i int, col1 varchar(5), col2 varchar(5))
insert into tt values(1, 'val1', 'val2')
insert into tt values(2, 'val1', 'val2')
insert into tt values(3, 'val1', 'val2')
--you will have to create a function that will change columnn seperated =values to the rows.
--create a function as it is no need to change it. only change the way =you call it as
--shown in the following script.
CREATE FUNCTION seperator
(@.str varchar(8000), @.del varchar(1))
RETURNS @.fin_tab TABLE (val varchar(8000)) AS
BEGIN
DECLARE @.pos int
DECLARE @.final_val varchar(8000)
SELECT @.str =3D LTRIM(RTRIM(@.str))
IF RIGHT(@.str,1) =3D @.del
set @.str =3D LEFT(@.str, Len(@.str) - 1)
set @.pos =3D charindex(@.del, @.str)
WHILE @.pos > 0
BEGIN
SET @.final_val =3D left(@.str, charindex(@.del, @.str) - 1)
INSERT @.fin_tab VALUES(@.final_val )
select @.str =3D substring(@.str, @.pos + 1, len(@.str)), @.pos =3D =charindex(@.del, @.str)
END
INSERT @.fin_tab VALUES(@.str)
RETURN
END
go
--Following is the T-SQL code to call the above function and loop =through the table.
declare @.i int, @.col1 varchar(5), @.col2 varchar(5)
declare @.str varchar(8000)
if object_id('tempdb..#temp_tab') is not null
drop table #temp_tab
create table #temp_tab(i int, crostab_col varchar(5))
declare c1 cursor static for
SELECT i, col1, col2 from tt
open c1
fetch next from c1 into @.i, @.col1, @.col2
while @.@.fetch_status =3D 0
begin
set @.str =3D @.col1 + ';' + @.col2
insert into #temp_tab
select @.i idd ,val from seperator(@.str , ';')
fetch next from c1 into @.i, @.col1, @.col2
end
close c1
deallocate c1
--final table with the columns turned to rows. of the above TT table
select * from #temp_Tab
--
- Vishal
--=_NextPart_000_0032_01C37EE6.FA7CB300
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

A cursor, you say? =;-)
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Vishal Parkar" wrote =in message news:uhz$hzufDHA.3200=@.tk2msftngp13.phx.gbl...An approach using UDF and cursor.Create sample data :drop =table ttcreate table tt (i int, col1 varchar(5), col2 =varchar(5))insert into tt values(1, 'val1', 'val2')insert into tt values(2, 'val1', 'val2')insert into tt values(3, 'val1', 'val2')--you will =have to create a function that will change columnn seperated values to the rows.--create a function as it is no need to change it. only change =the way you call it as--shown in the following script.CREATE =FUNCTION seperator = (@.str varchar(8000), @.del varchar(1)) RETURNS @.fin_tab =TABLE (val varchar(8000)) AS =BEGIN DECLARE @.pos int DECLARE @.final_val varchar(8000) SELECT @.str =3D LTRIM(RTRIM(@.str)) IF RIGHT(@.str,1) =3D @.del set @.str ==3D LEFT(@.str, Len(@.str) - 1) set @.pos ==3D charindex(@.del, @.str) WHILE @.pos => 0 BEGIN SET =@.final_val =3D left(@.str, charindex(@.del, @.str) - 1) =INSERT @.fin_tab VALUES(@.final_val ) select @.str =3D =substring(@.str, @.pos + 1, len(@.str)), @.pos =3D charindex(@.del, @.str) END =INSERT @.fin_tab VALUES(@.str) =RETURNENDgo--Following is the T-SQL code to call the above function and loop through the table.declare @.i int, @.col1 varchar(5), @.col2 =varchar(5)declare @.str varchar(8000)if object_id('tempdb..#temp_tab') is not =nulldrop table #temp_tabcreate table #temp_tab(i int, crostab_col varchar(5))declare c1 cursor static forSELECT i, col1, col2 =from ttopen c1fetch next from c1 into @.i, @.col1, =@.col2while @.@.fetch_status =3D 0beginset @.str =3D @.col1 + ';' + =@.col2insert into #temp_tabselect @.i idd ,val from seperator(@.str , ';')fetch =next from c1 into @.i, @.col1, @.col2endclose c1deallocate =c1--final table with the columns turned to rows. of the above TT tableselect * =from #temp_Tab--- Vishal

--=_NextPart_000_0032_01C37EE6.FA7CB300--|||This is a multi-part message in MIME format.
--=_NextPart_000_0554_01C37F38.61804C00
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
That wasn't as brilliant as yours, Tom :-)
-- - Vishal
--=_NextPart_000_0554_01C37F38.61804C00
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

That wasn't as brilliant as yours, Tom =:-)
-- - =Vishal
--=_NextPart_000_0554_01C37F38.61804C00--

Wednesday, March 7, 2012

column widths changing in PDF export

Hi,
When exporting my report to excel, the column widths change resulting in mis
alignment, and certain columns being place on new pages, which is not very
user friendly at all.
Is there anyway that I can prevent this from happening?
ThanksRob, we had this problem too, and it happens in Excel exports too. The
solution for us was to make sure the overall report with was less than 29cm
and that any Graphics were optimised to the size displayed in the report
(rather than the report shrinking the graphics, for some reason, if the
actual size of the graphic meant that the edge would be displayed off the
page, this would trigger a form feed?)
Hope this helps
Mikey :o)
"Rob" <kothesit@.newsgroup.nospam> wrote in message
news:Ou9TA0ayHHA.2224@.TK2MSFTNGP02.phx.gbl...
> Hi,
> When exporting my report to excel, the column widths change resulting in
> mis alignment, and certain columns being place on new pages, which is not
> very user friendly at all.
> Is there anyway that I can prevent this from happening?
> Thanks
>|||Hi Rob ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Saturday, February 25, 2012

Column or Field variable for an Excel query.

Hello all.
I have an excel query that pulls data from a SQL database. I have 2 parameters that represent the [start] and [end] of a between criteria. Here is the query.

SELECT CltDue.CDClientName
, CltDue.CDEventDesc
, CltDue.CDDescription
, (empfname)+' '+(emplname)
, CltDue.CDTargetAmount
, CltDue.CDTargetHours
FROM VPM.dbo.CltDue CltDue, VPM.dbo.Employee Employee
WHERE CltDue.CDInCharge = Employee.ID
AND ((CltDue.CDStart Between ? And ?))

This works fine, but what i wish to do is create a 3rd parameter for CltDue.CDStart
There are 4 fields in the CltDue table that i need.CDstart,CDdateComplete, CDTarget, and CDDateDelivered.
I want the user to be able to choose one of these 4 from a list box and then enter the beginning and end date.
Is excel capable of this?
Thanks for any help.

Quote:

Originally Posted by silversubey

Hello all.
I have an excel query that pulls data from a SQL database. I have 2 parameters that represent the [start] and [end] of a between criteria. Here is the query.

SELECT CltDue.CDClientName
, CltDue.CDEventDesc
, CltDue.CDDescription
, (empfname)+' '+(emplname)
, CltDue.CDTargetAmount
, CltDue.CDTargetHours
FROM VPM.dbo.CltDue CltDue, VPM.dbo.Employee Employee
WHERE CltDue.CDInCharge = Employee.ID
AND ((CltDue.CDStart Between ? And ?))

This works fine, but what i wish to do is create a 3rd parameter for CltDue.CDStart
There are 4 fields in the CltDue table that i need.CDstart,CDdateComplete, CDTarget, and CDDateDelivered.
I want the user to be able to choose one of these 4 from a list box and then enter the beginning and end date.
Is excel capable of this?
Thanks for any help.

As with any of the VBA applications excel included you should be able to create either an dynamic SQL statement including your column values from a selectable list and then fire the SQL to the SQL server to retrieve your dataset or even better reference an stored procedure either way....you are in the SQL Server forum so maybe you are better served in an Excel forum to follow through on the VBA application side of it?

Regards

Jim :)|||Thanks, I'll give it a try

Sunday, February 19, 2012

Column Group Row Alignment - Matrix

I just can not understand why when I add text to a group header the report displays more group row space but when I export to excel the extra space disappears....

What the....Help Microsoft please explain...

Thank You...

Any help on this would be great...

Please

Sunday, February 12, 2012

Color bleeding into next columns when exported to excel

I have one issue left to solve before releasing my drilldown report to production. The report displays fine in report manager; however, when it is exported to excel, the color (Coral) in the last column on the right is bleeding into the next three columns in excel. I have looked at properties, sizing and searched the archives. Anyone have an idea how I can fix this to render properly?

I fixed this one myself. We had a hidden logo that has pushed the page size wider than anticipated. We deleted the logo and reduced the page width on the layout and it works fine now.