anyone good with queries

Hi below is a simplified database table setup that I have but basically it is
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it is
Null in all 3 tables I will just use 0 for the value. So use maxi from table
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
******************************************
pri key * color_id * size_id * date* maxi *
******************************************
* 1 * 2 * 5 * 2/1/07* 7 *
******************************************
* 2 * 4 * 6 * 2/2/07* NULL *
******************************************
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
Paul G
Software engineer.
Try:
select
coalesce (a.maxi, b.maxi, c.maxi, 0)
from
table A a
left join
tableB b on b.[pri key] = a.[pri key]
left join
tableC c on c.[pri key] = a.[pri key]
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
Hi below is a simplified database table setup that I have but basically it
is
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it
is
Null in all 3 tables I will just use 0 for the value. So use maxi from
table
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
******************************************
pri key * color_id * size_id * date* maxi *
******************************************
* 1 * 2 * 5 * 2/1/07* 7 *
******************************************
* 2 * 4 * 6 * 2/2/07* NULL *
******************************************
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
Paul G
Software engineer.
|||it works! thanks
Paul G
Software engineer.
"Tom Moreau" wrote:

> Try:
> select
> coalesce (a.maxi, b.maxi, c.maxi, 0)
> from
> table A a
> left join
> tableB b on b.[pri key] = a.[pri key]
> left join
> tableC c on c.[pri key] = a.[pri key]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
> Hi below is a simplified database table setup that I have but basically it
> is
> 3 tables that are joined and one table has a date in it. Also in all 3
> tables is a value maxi and I need to retrieve 1 maxi value for each day from
> the query. If the value is NULL in table A I want to get the value from
> table B and if it is null in table B I want to get it from table C. If it
> is
> Null in all 3 tables I will just use 0 for the value. So use maxi from
> table
> A if not null, but if null check table b and use maxi from that table if not
> null, then if null use value from table c for maxi.
> tableA
> ******************************************
> pri key * color_id * size_id * date* maxi *
> ******************************************
> * 1 * 2 * 5 * 2/1/07* 7 *
> ******************************************
> * 2 * 4 * 6 * 2/2/07* NULL *
> ******************************************
> tableB
> ****************************
> * color id * color name * maxi *
> ****************************
> * 2 * blue * 6 *
> ****************************
> * 4 * red * NULL *
> ***************************
> tableC
> ****************************
> * size id * color name * maxi *
> ****************************
> * 5 * small * 6 *
> ****************************
> * 6 * medium * 10 *
> ***************************
> so a query for a date range of 2/1/07 to 2/2/07 would return
> 2 records and maxi would be 7 for 2/1/07 from table A and 10 from
> table C for 2/2/07.
> Thanks
> --
> Paul G
> Software engineer.
>

anyone good with queries

Hi below is a simplified database table setup that I have but basically it i
s
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it i
s
Null in all 3 tables I will just use 0 for the value. So use maxi from tabl
e
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
****************************************
**
pri key * color_id * size_id * date* maxi *
****************************************
**
* 1 * 2 * 5 * 2/1/07* 7 *
****************************************
**
* 2 * 4 * 6 * 2/2/07* NULL *
****************************************
**
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
Paul G
Software engineer.Try:
select
coalesce (a.maxi, b.maxi, c.maxi, 0)
from
table A a
left join
tableB b on b.[pri key] = a.[pri key]
left join
tableC c on c.[pri key] = a.[pri key]
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
Hi below is a simplified database table setup that I have but basically it
is
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it
is
Null in all 3 tables I will just use 0 for the value. So use maxi from
table
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
****************************************
**
pri key * color_id * size_id * date* maxi *
****************************************
**
* 1 * 2 * 5 * 2/1/07* 7 *
****************************************
**
* 2 * 4 * 6 * 2/2/07* NULL *
****************************************
**
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
Paul G
Software engineer.|||it works! thanks
--
Paul G
Software engineer.
"Tom Moreau" wrote:

> Try:
> select
> coalesce (a.maxi, b.maxi, c.maxi, 0)
> from
> table A a
> left join
> tableB b on b.[pri key] = a.[pri key]
> left join
> tableC c on c.[pri key] = a.[pri key]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
> Hi below is a simplified database table setup that I have but basically it
> is
> 3 tables that are joined and one table has a date in it. Also in all 3
> tables is a value maxi and I need to retrieve 1 maxi value for each day fr
om
> the query. If the value is NULL in table A I want to get the value from
> table B and if it is null in table B I want to get it from table C. If it
> is
> Null in all 3 tables I will just use 0 for the value. So use maxi from
> table
> A if not null, but if null check table b and use maxi from that table if n
ot
> null, then if null use value from table c for maxi.
> tableA
> ****************************************
**
> pri key * color_id * size_id * date* maxi *
> ****************************************
**
> * 1 * 2 * 5 * 2/1/07* 7 *
> ****************************************
**
> * 2 * 4 * 6 * 2/2/07* NULL *
> ****************************************
**
> tableB
> ****************************
> * color id * color name * maxi *
> ****************************
> * 2 * blue * 6 *
> ****************************
> * 4 * red * NULL *
> ***************************
> tableC
> ****************************
> * size id * color name * maxi *
> ****************************
> * 5 * small * 6 *
> ****************************
> * 6 * medium * 10 *
> ***************************
> so a query for a date range of 2/1/07 to 2/2/07 would return
> 2 records and maxi would be 7 for 2/1/07 from table A and 10 from
> table C for 2/2/07.
> Thanks
> --
> Paul G
> Software engineer.
>

anyone good with queries

Hi below is a simplified database table setup that I have but basically it is
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it is
Null in all 3 tables I will just use 0 for the value. So use maxi from table
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
******************************************
pri key * color_id * size_id * date* maxi *
******************************************
* 1 * 2 * 5 * 2/1/07* 7 *
******************************************
* 2 * 4 * 6 * 2/2/07* NULL *
******************************************
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
--
Paul G
Software engineer.Try:
select
coalesce (a.maxi, b.maxi, c.maxi, 0)
from
table A a
left join
tableB b on b.[pri key] = a.[pri key]
left join
tableC c on c.[pri key] = a.[pri key]
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
Hi below is a simplified database table setup that I have but basically it
is
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it
is
Null in all 3 tables I will just use 0 for the value. So use maxi from
table
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
******************************************
pri key * color_id * size_id * date* maxi *
******************************************
* 1 * 2 * 5 * 2/1/07* 7 *
******************************************
* 2 * 4 * 6 * 2/2/07* NULL *
******************************************
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
--
Paul G
Software engineer.|||it works! thanks
--
Paul G
Software engineer.
"Tom Moreau" wrote:
> Try:
> select
> coalesce (a.maxi, b.maxi, c.maxi, 0)
> from
> table A a
> left join
> tableB b on b.[pri key] = a.[pri key]
> left join
> tableC c on c.[pri key] = a.[pri key]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
> Hi below is a simplified database table setup that I have but basically it
> is
> 3 tables that are joined and one table has a date in it. Also in all 3
> tables is a value maxi and I need to retrieve 1 maxi value for each day from
> the query. If the value is NULL in table A I want to get the value from
> table B and if it is null in table B I want to get it from table C. If it
> is
> Null in all 3 tables I will just use 0 for the value. So use maxi from
> table
> A if not null, but if null check table b and use maxi from that table if not
> null, then if null use value from table c for maxi.
> tableA
> ******************************************
> pri key * color_id * size_id * date* maxi *
> ******************************************
> * 1 * 2 * 5 * 2/1/07* 7 *
> ******************************************
> * 2 * 4 * 6 * 2/2/07* NULL *
> ******************************************
> tableB
> ****************************
> * color id * color name * maxi *
> ****************************
> * 2 * blue * 6 *
> ****************************
> * 4 * red * NULL *
> ***************************
> tableC
> ****************************
> * size id * color name * maxi *
> ****************************
> * 5 * small * 6 *
> ****************************
> * 6 * medium * 10 *
> ***************************
> so a query for a date range of 2/1/07 to 2/2/07 would return
> 2 records and maxi would be 7 for 2/1/07 from table A and 10 from
> table C for 2/2/07.
> Thanks
> --
> Paul G
> Software engineer.
>

Any way to programmatically deploy an SSAS solution?

Hi all,

I'm trying to add a nightly build/deploy process for my SSAS solution. Basically - to take my AS solution from source control, deploy it to our dev server, and process just a few partitions.

What's the best way to do this? Looking at the solution folder, the .dsv, .dim, .cube, .ds files are all xml-based, which is good, but they don't seem like XML/A. (Seem very similar to a serialized version of AMO objects, but not quite exactly)

The only way I'd guess to do this is to manually load the individual xml files into an object model, and then map them to the AMO objects (since they're pretty similar..), and deploy those to the server. Is there a more efficient way to directly de-serialize the .dim,.cube, etc files into the AMO structures and deploy?

Take a look at the Deployment Wizard which you can run from Start... Programs... Microsoft SQL Server 2005... Analysis Services... Deployment Wizard.

Also see more info including command line switches here:

http://msdn2.microsoft.com/en-us/library/ms174817.aspx

|||

Furmangg, perfect tip on using the deployment wizard. I think this will suit our needs very well.

I was going to follow up w/ the correct method of compiling - I tried msbuild unsuccessfully, but I found this great blog post by Thomas Kejser -

http://schastar.spaces.live.com/blog/cns!12BCB785A5D8B3D4!148.entry?beid=cns!12BCB785A5D8B3D4!148&d=1&wa=wsignin1.0

Sweet!

Any load testing tool to test queries

Guys,

Is there any tool that can test the scalability of Analysis Services?

Basically we want to fire multiple queries to the Analysis Services and check if there is any performance degradation.

Is there any utility or tool that I can use?

Rgds

Hari

SQL Performance monitor works with SSAS too.|||

The Community samples site has an ASLoadSim tool listed which might be worth a look http://www.codeplex.com/SQLSrvAnalysisSrvcs

Any Idea's on this !

Well I have to implement Mail Merge ...user defined Letters in short...
basically like this..
The user can enter any custom letter with Fields we will provide them to put
into the letter ...We will them store this in database(right now thinking on
storing in HTML format).
For eg Letter template would be like
To,
<Name>
<Address1>
Hello <Name>
Thank you
Regards,
<MyName>
<MyPhone>
When the user then selects to generate a letter selecting this Template,then
we need to subsitute appropriate fields and generate the letters for all the
selected user clients by the user.
Please how can i achieve this with RS...any suggestions welcomed...
Thank uUnfortunately, this is not really supported in Reporting Services. We had
planned to add support for rich text (paragraphs, justification, formatting,
etc.) in SQL 2005 but ran out of time.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sunny" <Sunny@.discussions.microsoft.com> wrote in message
news:B8306DA0-C272-43F1-8151-4B380F85199B@.microsoft.com...
> Well I have to implement Mail Merge ...user defined Letters in short...
> basically like this..
> The user can enter any custom letter with Fields we will provide them to
> put
> into the letter ...We will them store this in database(right now thinking
> on
> storing in HTML format).
> For eg Letter template would be like
> To,
> <Name>
> <Address1>
> Hello <Name>
> Thank you
> Regards,
> <MyName>
> <MyPhone>
> When the user then selects to generate a letter selecting this
> Template,then
> we need to subsitute appropriate fields and generate the letters for all
> the
> selected user clients by the user.
> Please how can i achieve this with RS...any suggestions welcomed...
> Thank u
>|||Thanks for the reply !!
Well i am ready to give up on Rich text formatting here...but how can I
actually achieve the working of such templates as letters.How can i do this
with RS.
Is Inner Html supported in RS.
Thank u
"Brian Welcker [MSFT]" wrote:
> Unfortunately, this is not really supported in Reporting Services. We had
> planned to add support for rich text (paragraphs, justification, formatting,
> etc.) in SQL 2005 but ran out of time.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Sunny" <Sunny@.discussions.microsoft.com> wrote in message
> news:B8306DA0-C272-43F1-8151-4B380F85199B@.microsoft.com...
> > Well I have to implement Mail Merge ...user defined Letters in short...
> > basically like this..
> > The user can enter any custom letter with Fields we will provide them to
> > put
> > into the letter ...We will them store this in database(right now thinking
> > on
> > storing in HTML format).
> > For eg Letter template would be like
> >
> > To,
> > <Name>
> > <Address1>
> >
> > Hello <Name>
> > Thank you
> >
> > Regards,
> > <MyName>
> > <MyPhone>
> >
> > When the user then selects to generate a letter selecting this
> > Template,then
> > we need to subsitute appropriate fields and generate the letters for all
> > the
> > selected user clients by the user.
> > Please how can i achieve this with RS...any suggestions welcomed...
> >
> > Thank u
> >
> >
>
>|||You can't - embedded HTML is part of the rich text feature that was cut. If
you think about it, since we support multiple output format, we would have
to parse the HTML and represent it in other formats (Excel, PDF, etc.)
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sunny" <Sunny@.discussions.microsoft.com> wrote in message
news:F8F014D9-7D93-4BFA-97CB-A1F990BADA83@.microsoft.com...
> Thanks for the reply !!
> Well i am ready to give up on Rich text formatting here...but how can I
> actually achieve the working of such templates as letters.How can i do
> this
> with RS.
> Is Inner Html supported in RS.
> Thank u
>
> "Brian Welcker [MSFT]" wrote:
>> Unfortunately, this is not really supported in Reporting Services. We had
>> planned to add support for rich text (paragraphs, justification,
>> formatting,
>> etc.) in SQL 2005 but ran out of time.
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Sunny" <Sunny@.discussions.microsoft.com> wrote in message
>> news:B8306DA0-C272-43F1-8151-4B380F85199B@.microsoft.com...
>> > Well I have to implement Mail Merge ...user defined Letters in short...
>> > basically like this..
>> > The user can enter any custom letter with Fields we will provide them
>> > to
>> > put
>> > into the letter ...We will them store this in database(right now
>> > thinking
>> > on
>> > storing in HTML format).
>> > For eg Letter template would be like
>> >
>> > To,
>> > <Name>
>> > <Address1>
>> >
>> > Hello <Name>
>> > Thank you
>> >
>> > Regards,
>> > <MyName>
>> > <MyPhone>
>> >
>> > When the user then selects to generate a letter selecting this
>> > Template,then
>> > we need to subsitute appropriate fields and generate the letters for
>> > all
>> > the
>> > selected user clients by the user.
>> > Please how can i achieve this with RS...any suggestions welcomed...
>> >
>> > Thank u
>> >
>> >
>>

Any ideas

I need for a database to give my users and indication that a renewal has been complete. Basically what happens is every year once a month a report is generated from sql of how many employees need their gaming license renewed the filter is based off of a field called final suit. I need to find a way to let them know through the database that an employee has been renewed. anyone got any ideas??My first idea (and I like this one the best) was to shake up a margarita...

Then I read your question

I would have a column in a table for Renewal Date. When they get renewed, update it with that date. Since the renewal is for 1 year anything that is not renewed would be earlier than today - 1 year

I still like my first idea|||How are you doing?? Nice to hear from you.
Well they have to be renewed every 2 years, there is a column in the table with the renewal date on it already, so your saying that should be their indication that they have been renewed already??|||USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myEmployee99(LastName varchar(30), FirstName varchar(30), RenewalDate datetime)
GO

INSERT INTO myEmployee99(LastName, FirstName, RenewalDate)
SELECT 'Johnson', 'Arnie' , '10/31/2001' UNION ALL
SELECT 'Kaiser' , 'Brett' , '10/31/2002' UNION ALL
SELECT 'Lynn' , 'Connie' , '10/31/2003' UNION ALL
SELECT 'More' , 'Desiree', '10/31/2004'
GO

SELECT 'Employees that Need to Renew: ' + LastName + ', ' + FirstName
+ ' --> ' + CONVERT(varchar(5), DATEDIFF(dd,RenewalDate,GetDate())-730) + ' Days Late'
FROM myEmployee99
WHERE DATEDIFF(dd,RenewalDate,GetDate())-730 > 0
GO

SET NOCOUNT ON
DROP TABLE myEmployee99
GO|||Was that what you were looking for?|||I dont think thats what I am looking for Brett but thank you anyways, I do appreciate it :)
You see I have a query that runs off the team members Final suit date. (by the way all the dates data types are DATETIME). I need something that tells the users as soon as they go in the database looking for whether a renewal has been completed or not. YOu see they get a report every month that tells them what renewals need to be pulled (files from filing cabinet) and renewed but I need something that tells them if they have already completed the renewal or not. Does that make sense??|||Is a renewal in your case a proces or is it enough to have it all on the 'final suit' field? How does the field 'final suit' work (when is it set the first time) and how does it change? If the 'final suit' can indicate when a license is renewed, why can it not tell if a license has been renewed in the past?|||If you post some DDL, sample data and the expected results you have (like the code I posted). I'm sure we can figure something out.

It's easier to see with samples...|||Hi there,
This all sounds extremely odd.
To me, it sounds as though you want to notify the customer as soon as the renewal happens. This can be done with a trigger.

You can create an update trigger that will use the SQL sendmail to send a email to the customer perhaps?

Even better - write a VB app for Outlook that will check the SQL table and send a mail to the customer if there renewal was done in the last 24 hours|||I use to have a drop down box that said yes or no to indicate if the renewal was complete or not??

Answer: My First Report

I need to create a report for a phone directory. Basically my report needs this format.

department
employee
employee
and so on.....
department
employee
employee
and so on.....
department
My data is stored in two tables

department -

deptID
deptName

Main -

deptID
empName
title
order by

Departments are listed in department table. there is an associated deptID in both tables. in the main table every employee is listed and they have a deptID that is associated with the department table. The order by column is an integer that list the order they should appear in the format. 1 would be the top person and so on.

How would you bring in the data into reporting services to display in this format? Would this be one SQL Statement or am I looking at multiple Datasets?

I was playing with the Union ALL and realized I don't have equal parts.

I will be pulling the deptid and deptname from the department tables

from the main table I need deptid to match tables, and then I need to display. name, title, and phone. department table is just used to display the department name.

|||Do let me know if the following is what you were looking to accomplish:

I created the following 2 tables (similar to yours I believe):

create table department (deptID int, deptName varchar(100))

create table Main (deptID int, empName varchar(100), title varchar(100))

When designing the report:

Enter the following in the Query String (or the query that you have to select all the required fields from the tables) -
select * from department
join main on main.deptid = department.deptid

Click Next, Choose Tabular Matrix
Click Next, Choose DeptName as 'Group' and EmpName and Title as 'Details'
Click Next, select 'Enable drilldown' if you wanted to expand the deptnames
Enter report name, click Finish and view the Preview.

You will find the report grouped by Dept Name, with empname, title appearing under each dept name.