DISTINCT QUERY PLEASE HELP

I do an distinct query like this:
query = "SELECT DISTINCT name, total = COUNT(*) from products where name LIKE '%" & searchString & "%' GROUP BY name"
The query works fine but I need to select one more field called "info" so I just tried this:
query = "SELECT DISTINCT name, info, total = COUNT(*) from products where name LIKE '%" & searchString & "%' GROUP BY name"
but then I get the error message:
"Column 'products.info' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"
I want do group just by "name" and not "info"
How can I select this "info" field to?? somebody know??

Hi,
You can use
Select name, max(info), total=count........ Group by name
To "fool" the Sql server, but I am not sure this is what you want.
Will the info column have the same value for all the rows with the samevalue in the name column? Then you might as well use group by name,info the result willbe the same.
But if the info column has different values within the same name, which one will you have displayed?
BTW: It should not be necessary to use distinct in this query
|||

Now i do realize I cant get the info field because its just a distinct query. Forget this message.
thanks anyway

DISTINCT OR GROUP BY

I have some records in my Sql Db.
There are some duplicate record in my email and adsoyad fields
Email Adsoyad
a@.a.com Savas
a@.a.com Pele
b@.b.com Savas
c@.c.om Ilim
d@.d.com Hasan
d@.d.com Hasan
I want to eliminate email address which can be duplicate and its Adsoyad
field.
There can be different adsoyad records for duplicate email records. I want
to select one of them which doesnt have any importance for me .
I also have soma email records which doesnt have @. character. I want to
eliminate those records too. Is there any command in SQL like INSTR ?Do you want to delete the row or update the fields?
For deleting the rows with email without '@.'
Delete from table where email not like '_%@._%'
I put the additional requirement that there must be at least one char in
front of and 1 char behind the @. sign...
The second thing you wanted to do is delete the dupe emails by picking the
Adsoyad which does not have meaning for you... You'll have to repst and
describe how you determine which Adsoyad doesn't have meaning and someone
will help you with the SQL...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Savas Ates" wrote:

> I have some records in my Sql Db.
> There are some duplicate record in my email and adsoyad fields
> Email Adsoyad
> a@.a.com Savas
> a@.a.com Pele
> b@.b.com Savas
> c@.c.om Ilim
> d@.d.com Hasan
> d@.d.com Hasan
> I want to eliminate email address which can be duplicate and its Adsoyad
> field.
> There can be different adsoyad records for duplicate email records. I want
> to select one of them which doesnt have any importance for me .
> I also have soma email records which doesnt have @. character. I want to
> eliminate those records too. Is there any command in SQL like INSTR ?
>
>|||> There can be different adsoyad records for duplicate email records. I want
> to select one of them which doesnt have any importance for me .
Use DISTINCT, then you can tell from your query that that is your purpose.
GROUP BY is typically used for aggregation.

> I also have soma email records which doesnt have @. character. I want to
> eliminate those records too. Is there any command in SQL like INSTR ?
Yes, look at CHARINDEX, PATINDEX. You might also consider a function or
even a check constraint that actually validates the format of an e-mail
address. Then you can't get any crap in there in the first place. Search
groups.google.com, there are plenty of examples out there ready to use.|||IT doesnt matter which adsoyad Record im gonna choose. I want to just pick
one of adsoyad records ?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com>, haber iletisinde
unlar yazd:E9100F46-9958-4408-B527-2209F25B40B5@.microsoft.com...
> Do you want to delete the row or update the fields?
> For deleting the rows with email without '@.'
> Delete from table where email not like '_%@._%'
> I put the additional requirement that there must be at least one char in
> front of and 1 char behind the @. sign...
> The second thing you wanted to do is delete the dupe emails by picking the
> Adsoyad which does not have meaning for you... You'll have to repst and
> describe how you determine which Adsoyad doesn't have meaning and someone
> will help you with the SQL...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Savas Ates" wrote:
>|||> IT doesnt matter which adsoyad Record im gonna choose. I want to just pick
> one of adsoyad records ?
Do you need adsoyad in the result? Can you provide more clear requirements
so we don't have to ask 80 follow-up questions? Please see
http://www.aspfaq.com/5006|||Yep I need adsoyad records too. But it doesnt matter which one i can come
up. I want to elimitate email addresses which are dublicate and adsoyad
records whics is tied to one of the duplicate email addresses. I should say
that i dont need to chooese any adsoyad records exactly . Just wanna pick
up one of them .
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%2366CMQ1TGHA.1236@.TK2MSFTNGP11.phx.gbl...
> Do you need adsoyad in the result? Can you provide more clear
> requirements so we don't have to ask 80 follow-up questions? Please see
> http://www.aspfaq.com/5006
>|||(reposting using the Microsoft's site, since the Google Groups post was not
found here)
Hi, Savas
Try something like this (untested):
SELECT Email, MIN(Adsoyad)
FROM YourTable
WHERE Email LIKE '_%@._%._%' AND Email NOT LIKE '%@.%@.%'
GROUP BY Email
Note that the LIKE expression above (although more complex than what
you have suggested), still doesn't ensure a valid e-mail address (as
per RFC 822).
Razvan

Distinct on single column?

Hi,

This is a query that joins a vouple of tables to display all the products purchased by a group of customers and the price they paid for it.

SELECT DISTINCT (p.code),p.descript_1 + ' ' + p.descript_2 + ' ' + p.descript_3 as description,sol.p_sales as price,sol.q_ordered as quantity,(sol.p_sales * sol.q_ordered) as total,so.date_in as dateFROM EfasLive..debtor AS d

INNER JOIN Informatica..so AS so ON so.deb_code = d.code AND so.co_code = d.co_code

INNER JOIN Informatica..so_line AS sol ON sol.code = so.code AND sol.co_code = so.co_code AND sol.acc_year = so.acc_year AND sol.efas = so.efas

INNER JOIN EfasLive..part AS p ON p.code = sol.part

WHERE d.[grp{003}] = 'GROUP' AND p.co_code = 1 AND p.code NOT LIKE '&%' AND so.date_in > DATEADD(m,-3,GETDATE()) AND sol.q_ordered > 0

ORDER BY (p.code), datum DESC

The problem with this is that it returns multiple lines for every product (p.code). Like so:

code description price quantity total date

603244 description_1 17.950000 150.000000 2692.500000000000 2007-08-01 00:00:00

603244 description_1 17.950000 150.000000 2692.500000000000 2007-07-10 00:00:00

603245 description_2 17.950000 40.000000 718.000000000000 2007-07-24 00:00:00

603245 description_2 17.950000 25.000000 448.750000000000 2007-07-16 00:00:00

603663 description_3 16.890000 27.000000 456.030000000000 2007-07-20 00:00:00

603663 description_3 16.890000 150.000000 2533.500000000000 2007-07-10 00:00:00

603663 description_3 16.890000 30.000000 506.700000000000 2007-07-03 00:00:00

I'd like there to be only 1 line for every different code with it's description. The idea is that the other rows are dropped and that only the first one remains. The one with the most recent purchase. I tried with GROUP BY but that's probably wrong since you'd have to add all the other columns as well and you end up with the same one. And even with adding a HAVING at the end I can't see how this could be solved

edit: There aren't any actual relationships in the tables (it's ancient you see ...) I'm using SQL 2005 though.

Hello

Just a few ideas, do not have an MSSQL instace nearby to test:

1) use cursor, which I'd prefer to avoid

- declare cursor for "Select Distinct (code) From EfasLive..part"

- for each cursor value do the select on joined tables where date = MAx(date) to get the most recent value

2) do something like

Select ....
From (Select Distinct (code) From EfasLive..part) as p Inner Join... (the rest of the tables)...
Where date = Max(date)

The idea is to join distinct "code" values with other tables and filter only the most recent one for each table (that's what "Max(date)" is for)

3) try to use CTEs (Common Table Expressions)

Post the solution after you find one! Tnx
|||

Hmz I'll try out some of this stuff. Thx! But the multiple instances of code don't come from Efaslive..part. They are actually from so_line. An so_line is actually an orderline. For every order there could be multiple lines each containing a different product. But since it's over a timespan of 3 months it will include multiple orders and thus multiple so_lines containing the same product (once for every order it was in). So doing a distinct on code in Efaslive..part probably won't work. Or at least it doesn't make sense to me I'll most definitely look into CTEs and post my findings or a solution.

edit: actually this can be simplified ... just pretend that the result I get is a simple select query from a single table. As if it was a CTE Even then I'd have no clue how to drop the older records The only technique I know is to group them but then you'd have to use MAX or COUNT or AVG or whatever .. and then I wouldn't have the correct price and/or date. So you wouldn't realy be dropping them.

I'll look into the pointer thing.

|||

Here the query,

Code Snippet

;With CTE

as

(

SELECT DISTINCT

p.code

, p.descript_1 + ' ' + p.descript_2 + ' ' + p.descript_3 as description

, sol.p_sales as price

, sol.q_ordered as quantity

, sol.p_sales * sol.q_ordered as total

, so.date_in as date

, max(so.date_in) over(partition by p.code) as maxdate

--, Row_Number() over(partition by p.code order by so.date_in desc) rid

FROM

EfasLive..debtor AS d

INNER JOIN Informatica..so AS so

ON so.deb_code = d.code

AND so.co_code = d.co_code

INNER JOIN Informatica..so_line AS sol

ON sol.code = so.code

AND sol.co_code = so.co_code

AND sol.acc_year = so.acc_year

AND sol.efas = so.efas

INNER JOIN EfasLive..part AS p

ON p.code = sol.part

WHERE

d.[grp{003}] = 'GROUP'

AND p.co_code = 1

AND p.code NOT LIKE '&%'

AND so.date_in > DATEADD(m,-3,GETDATE())

AND sol.q_ordered > 0

)

Select

code

, description

, price

, quantity

, total

, date

From

CTE

Where

date = maxdate

--rid=1

DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

Hello,

I have a DB of professors and information related with them. I created the cube, it consist of:

Measures:

Measure group Professors:

Amount of projects (COUNT proj_id)

Amount of pulications (COUNT pub_id)

Amount of e_books (COUNT book_id)

--

Measure group Projects:

Distinct amount of projects (DISTINCT COUNT proj_id)

--

Measure group Publications:

Distinct amount of publications (DISTINCT COUNT pub_id)

--

Measure group E_books:

Distinct amount of e_books (DISTINCT COUNT book_id)

Calculated measures:

Amnt_Projects

iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])

Amnt_Publications

(similar to the above one)

Amnt_E_books

(similar to the above one)

Dimensions:

dimPROFESSORS

- prof_id

-surname

-name

-gender

dimPROJECTS

- proj_id

-type name

-name

dimPUBLICATIONS

- pub_id

-type name

-name

dimE_BOOKS

- book_id

-name

Data_Projects

-data_id

-years

Data_Publications

-data_id

-years

Data_E_books

-data_id

-years

For example, when I browse the cube:

prof_id Amount of projects Distinct amount of projects Amnt_Projects

1032 30 1 1

1070 90 2 2

1111 0 1 0

1137 0 1 0

1234 1404 9 9

1721 504 7 7

2661 85 5 5

... ... ... ...

6999 20 1 1

9956 50 5 5

Uknown 0

Grand Total 2421 11 11

Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be ?59“ for all professors). How could I get the right value to be shown in Grand Total?

Any help would be appreciated as I'm very new to the MDX.

|||

Maybe you need to sum the project counts for each professor in the selection, like:

Amnt_Projects

Sum(existing [DimProfessors].[prof_id].[prof_id], [Measures].[ Distinct amount of projects])

|||Thank You Deepak. This calculation works perfectly, just i added my calculated measure expression instead of [Measures].[ Distinct amount of projects]. I am very happy that at last it works!!!
|||Hello again. I was working with KPI's and noticed, that this calculation needs to be improved. When I try to filter by any particular professor, the Grand Total remains the same - for the projects always "59". How could I change the calculateted member?

Thank You in advance.
|||

Were you testing the KPI with the KPI Browser in BIDS? If so, please check directly with an MDX query, since the browser may be using a subselect vs. where. The query would be like:

select

KPIValue("ProfCalc") on 0

from ProfCube

where [DimProfessors].[prof_id].&

|||I tested Your SQL query, and You are right. This query works fine. But how can I make the KPI browser in BIDS show the right values (I use the Amnt_Projects calculation for the KPIValue expression)? The same with cube browser:

prof_id Amnt_Projects
1032 1
1070 2
1111 0
Grand Total 59

Thank You again. I appreciate Your help very much!
|||

In that case, a different approach may be needed, based on a recent Forum post:

- Create a new "row count" measure called Amnt_Projects for the Professors measure group.

- Add a statement to the cube MDX script, assigning values at the DimProfessors leaf level to Amnt_Projects:

([DimProfessors].[prof_id].[prof_id], [Measures].[Amnt_Projects]) = [Measures].[ Distinct amount of projects];

|||I don't know if I'm doing something wrong, but in this case I get #VALUE! for every professor.
First, I created a row count measure Amnt_Projects in the Professors measure group. Than I created calculated measure as You wrote and named it Amnt_projects2.

What is more, I am creating reports using this cube. I tried to make the one similar to the "Teritory Sales Drilldown" example. But the strange thing is with Amnt_Projects (the calculation, that You provided earlier). I make this kind of drilldown: Professor (Name/Surname) and Amnt_Publications->Type of Publication and Amnt_Publications->Name of Publication and Amnt_Publications

I get the results:
Professor Type of Publication Name of Publication Amnt_Publication
Professor1 #Error
Type1 0
Name1 0
Name2 0
Type2 #Error
Name3 1
Name4 0

So when the value of Amnt_Publication is 0, everything is ok, but when it has to sum one's, it shows #Error.

Thank You!
|||By the way, I get this cind of warning in the reporting services when I preview the report:
"The Value expression for the textbox ‘Amnt_Publications’ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type."
|||I have just notices that using distinct count fits me very well in this reporting services situation. It behaves very differently than in Analysis Services browser. In Reporting Services it counts distinct values and shows null value for those professors that have no publications. And in Analysis Services it shows "1" for null value. Indeed strange.

Still would appreciate Your help with those Amnt in Analysis Services, which I use for KPI value and browse in KPI browser.

Thank You!
|||"I created calculated measure as You wrote and named it Amnt_projects2" - in the approach which I suggested, there is no calculated measure. The cube script assignment applies to the new cube measure: "Amnt_Projects".|||Thank You for answering. But could You be more specific? (about "The cube script assignment applies to the new cube measure: "Amnt_Projects". ")This is my first try with SQL Server and I have only a couple of days to finish this.

What is more, I need those KPI using not the whole Amount of projects, but something like this:

KPI for projects = Amount of Type1 projects*0,6 + Amount of Type2projects *0,3 + Amount of Type3*0,1

as I mentioned, I have such kind of dimension Projects:

dimPROJECTS

- proj_id

-type name (there are three Types of projects)

-name (the name of project itself)

Thank You very much!
|||OK, I have just assigned the KPI value by myself and it works.Of course it works only in MS SQL Server Manegement Studio..I tested it by SQL query and it should work in Reporting Services. But because I'm still using that previous Amnt_projects -->Sum(existing [DimProfessors].[prof_id].[prof_id], [Measures].[ Distinct amount of projects]), it doesn't work properly in KPI browser. The KPI Value expresion is:

SUM([Dim Projects].[Type Name].&[Type1],[Measures].[Amnt_Projects])* 0.6 + SUM([Dim Projects].[Type Name].&[Type2],[Measures].[Amnt_Projects])* 0.3 + SUM([Dim Projects].[Type Name].&[Type3],[Measures].[Amnt_Projects]) * 0.1

So I would appreciate Your explanation about "The cube script assignment applies to the new cube measure: "Amnt_Projects". "

Thank You in advance!
|||

Not sure whether you reviewed the earlier post, which I provided a link to - but the approach I suggested is similar:

- Create a new "row count" measure called Amnt_Projects for the Professors measure group (this replaces the calculated measure: Amnt_Projects)

- Add this statement to the cube MDX script, assigning values at the DimProfessors leaf level to Amnt_Projects (it doesn't create any new measures):

([DimProfessors].[prof_id].[prof_id], [Measures].[Amnt_Projects]) = [Measures].[ Distinct amount of projects];

DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

Hello,

I have a DB of professors and information related with them. I created the cube, it consist of:

Measures:

Measure group Professors:

Amount of projects (COUNT proj_id)

Amount of pulications (COUNT pub_id)

Amount of e_books (COUNT book_id)

--

Measure group Projects:

Distinct amount of projects (DISTINCT COUNT proj_id)

--

Measure group Publications:

Distinct amount of publications (DISTINCT COUNT pub_id)

--

Measure group E_books:

Distinct amount of e_books (DISTINCT COUNT book_id)

Calculated measures:

Amnt_Projects

iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])

Amnt_Publications

(similar to the above one)

Amnt_E_books

(similar to the above one)

Dimensions:

dimPROFESSORS

- prof_id

-surname

-name

-gender

dimPROJECTS

- proj_id

-type name

-name

dimPUBLICATIONS

- pub_id

-type name

-name

dimE_BOOKS

- book_id

-name

Data_Projects

-data_id

-years

Data_Publications

-data_id

-years

Data_E_books

-data_id

-years

For example, when I browse the cube:

prof_id Amount of projects Distinct amount of projects Amnt_Projects

1032 30 1 1

1070 90 2 2

1111 0 1 0

1137 0 1 0

1234 1404 9 9

1721 504 7 7

2661 85 5 5

... ... ... ...

6999 20 1 1

9956 50 5 5

Uknown 0

Grand Total 2421 11 11

Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be ?59“ for all professors). How could I get the right value to be shown in Grand Total?

Any help would be appreciated as I'm very new to the MDX.

|||

Maybe you need to sum the project counts for each professor in the selection, like:

Amnt_Projects

Sum(existing [DimProfessors].[prof_id].[prof_id], [Measures].[ Distinct amount of projects])

|||Thank You Deepak. This calculation works perfectly, just i added my calculated measure expression instead of [Measures].[ Distinct amount of projects]. I am very happy that at last it works!!!
|||Hello again. I was working with KPI's and noticed, that this calculation needs to be improved. When I try to filter by any particular professor, the Grand Total remains the same - for the projects always "59". How could I change the calculateted member?

Thank You in advance.
|||

Were you testing the KPI with the KPI Browser in BIDS? If so, please check directly with an MDX query, since the browser may be using a subselect vs. where. The query would be like:

select

KPIValue("ProfCalc") on 0

from ProfCube

where [DimProfessors].[prof_id].&

|||I tested Your SQL query, and You are right. This query works fine. But how can I make the KPI browser in BIDS show the right values (I use the Amnt_Projects calculation for the KPIValue expression)? The same with cube browser:

prof_id Amnt_Projects
1032 1
1070 2
1111 0
Grand Total 59

Thank You again. I appreciate Your help very much!
|||

In that case, a different approach may be needed, based on a recent Forum post:

- Create a new "row count" measure called Amnt_Projects for the Professors measure group.

- Add a statement to the cube MDX script, assigning values at the DimProfessors leaf level to Amnt_Projects:

([DimProfessors].[prof_id].[prof_id], [Measures].[Amnt_Projects]) = [Measures].[ Distinct amount of projects];

|||I don't know if I'm doing something wrong, but in this case I get #VALUE! for every professor.
First, I created a row count measure Amnt_Projects in the Professors measure group. Than I created calculated measure as You wrote and named it Amnt_projects2.

What is more, I am creating reports using this cube. I tried to make the one similar to the "Teritory Sales Drilldown" example. But the strange thing is with Amnt_Projects (the calculation, that You provided earlier). I make this kind of drilldown: Professor (Name/Surname) and Amnt_Publications->Type of Publication and Amnt_Publications->Name of Publication and Amnt_Publications

I get the results:
Professor Type of Publication Name of Publication Amnt_Publication
Professor1 #Error
Type1 0
Name1 0
Name2 0
Type2 #Error
Name3 1
Name4 0

So when the value of Amnt_Publication is 0, everything is ok, but when it has to sum one's, it shows #Error.

Thank You!
|||By the way, I get this cind of warning in the reporting services when I preview the report:
"The Value expression for the textbox ‘Amnt_Publications’ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type."
|||I have just notices that using distinct count fits me very well in this reporting services situation. It behaves very differently than in Analysis Services browser. In Reporting Services it counts distinct values and shows null value for those professors that have no publications. And in Analysis Services it shows "1" for null value. Indeed strange.

Still would appreciate Your help with those Amnt in Analysis Services, which I use for KPI value and browse in KPI browser.

Thank You!
|||"I created calculated measure as You wrote and named it Amnt_projects2" - in the approach which I suggested, there is no calculated measure. The cube script assignment applies to the new cube measure: "Amnt_Projects".|||Thank You for answering. But could You be more specific? (about "The cube script assignment applies to the new cube measure: "Amnt_Projects". ")This is my first try with SQL Server and I have only a couple of days to finish this.

What is more, I need those KPI using not the whole Amount of projects, but something like this:

KPI for projects = Amount of Type1 projects*0,6 + Amount of Type2projects *0,3 + Amount of Type3*0,1

as I mentioned, I have such kind of dimension Projects:

dimPROJECTS

- proj_id

-type name (there are three Types of projects)

-name (the name of project itself)

Thank You very much!
|||OK, I have just assigned the KPI value by myself and it works.Of course it works only in MS SQL Server Manegement Studio..I tested it by SQL query and it should work in Reporting Services. But because I'm still using that previous Amnt_projects -->Sum(existing [DimProfessors].[prof_id].[prof_id], [Measures].[ Distinct amount of projects]), it doesn't work properly in KPI browser. The KPI Value expresion is:

SUM([Dim Projects].[Type Name].&[Type1],[Measures].[Amnt_Projects])* 0.6 + SUM([Dim Projects].[Type Name].&[Type2],[Measures].[Amnt_Projects])* 0.3 + SUM([Dim Projects].[Type Name].&[Type3],[Measures].[Amnt_Projects]) * 0.1

So I would appreciate Your explanation about "The cube script assignment applies to the new cube measure: "Amnt_Projects". "

Thank You in advance!
|||

Not sure whether you reviewed the earlier post, which I provided a link to - but the approach I suggested is similar:

- Create a new "row count" measure called Amnt_Projects for the Professors measure group (this replaces the calculated measure: Amnt_Projects)

- Add this statement to the cube MDX script, assigning values at the DimProfessors leaf level to Amnt_Projects (it doesn't create any new measures):

([DimProfessors].[prof_id].[prof_id], [Measures].[Amnt_Projects]) = [Measures].[ Distinct amount of projects];

sql

Distinct Count in a new Measure Group

Here is the situation:

I have a cube that has transaction level data and there is a control number that goes with this data, but it isn't one for one. If I have 60 million rows in the fact table, I have 50 million control numbers in a control number dimension.

I would like to be able to slice the data by date (for instance) and then do a distinct count on the control number dimension. I can do a distinct count w.o having to do a join of course because I can count the distinct instances of the key to the control number dimension.

One more note - the control number dimension is not a degenerate dimension...it is not one to one on the fact table.

I would like to use the DISTINCT COUNT measure in SSAS, but it makes me create a new measure group to do so. All of my additive measures are in one group, the distinct count is forced into another group. This forces double the processing time.

Does anyone know why this behavour happens and can someone explain why a DISTINCT COUNT measure has to go in a seperate measure group?

Thanks,

Mark

Mark,

Do you already have a DISTINCT COUNT measure in that measure group? Analysis Services will only allow you to have one DISTINCT COUNT per measure group.

|||Maybe this MDX will overcome the limitation of one unqiue count.

Distinct(Filter([Customer].[Customer Name].Members,Not IsEmpty([Measures].[Sales Amount]))).Count

Timmy|||

Actually, I don't have one in the first measure group. The first measure group is just regular measures, but if I create a new measure that is a distinct count measure, it automatically puts it in another group.

I have now heard from other sources that this is an exptected behavior. So no biggie, just needed to know.

ps - sorry it took so long to respond.

Thanks,

Mark

http://spaces.msn.com/mgarnerbi

|||I noticed this too, does anyone know the underlying reason why this is? Is this going to be fixed in a future version?sql

Distinct Count in a new Measure Group

Here is the situation:

I have a cube that has transaction level data and there is a control number that goes with this data, but it isn't one for one. If I have 60 million rows in the fact table, I have 50 million control numbers in a control number dimension.

I would like to be able to slice the data by date (for instance) and then do a distinct count on the control number dimension. I can do a distinct count w.o having to do a join of course because I can count the distinct instances of the key to the control number dimension.

One more note - the control number dimension is not a degenerate dimension...it is not one to one on the fact table.

I would like to use the DISTINCT COUNT measure in SSAS, but it makes me create a new measure group to do so. All of my additive measures are in one group, the distinct count is forced into another group. This forces double the processing time.

Does anyone know why this behavour happens and can someone explain why a DISTINCT COUNT measure has to go in a seperate measure group?

Thanks,

Mark

Mark,

Do you already have a DISTINCT COUNT measure in that measure group? Analysis Services will only allow you to have one DISTINCT COUNT per measure group.

|||Maybe this MDX will overcome the limitation of one unqiue count.

Distinct(Filter([Customer].[Customer Name].Members,Not IsEmpty([Measures].[Sales Amount]))).Count

Timmy
|||

Actually, I don't have one in the first measure group. The first measure group is just regular measures, but if I create a new measure that is a distinct count measure, it automatically puts it in another group.

I have now heard from other sources that this is an exptected behavior. So no biggie, just needed to know.

ps - sorry it took so long to respond.

Thanks,

Mark

http://spaces.msn.com/mgarnerbi

|||I noticed this too, does anyone know the underlying reason why this is? Is this going to be fixed in a future version?

Distinct Count in a new Measure Group

Here is the situation:

I have a cube that has transaction level data and there is a control number that goes with this data, but it isn't one for one. If I have 60 million rows in the fact table, I have 50 million control numbers in a control number dimension.

I would like to be able to slice the data by date (for instance) and then do a distinct count on the control number dimension. I can do a distinct count w.o having to do a join of course because I can count the distinct instances of the key to the control number dimension.

One more note - the control number dimension is not a degenerate dimension...it is not one to one on the fact table.

I would like to use the DISTINCT COUNT measure in SSAS, but it makes me create a new measure group to do so. All of my additive measures are in one group, the distinct count is forced into another group. This forces double the processing time.

Does anyone know why this behavour happens and can someone explain why a DISTINCT COUNT measure has to go in a seperate measure group?

Thanks,

Mark

Mark,

Do you already have a DISTINCT COUNT measure in that measure group? Analysis Services will only allow you to have one DISTINCT COUNT per measure group.

|||Maybe this MDX will overcome the limitation of one unqiue count.

Distinct(Filter([Customer].[Customer Name].Members,Not IsEmpty([Measures].[Sales Amount]))).Count

Timmy
|||

Actually, I don't have one in the first measure group. The first measure group is just regular measures, but if I create a new measure that is a distinct count measure, it automatically puts it in another group.

I have now heard from other sources that this is an exptected behavior. So no biggie, just needed to know.

ps - sorry it took so long to respond.

Thanks,

Mark

http://spaces.msn.com/mgarnerbi

|||I noticed this too, does anyone know the underlying reason why this is? Is this going to be fixed in a future version?

Distinct and Group By

*** This is a follow-up to my recent post "Distinct For Only Some Columns In
A Row".
Recently, I posted a question. That question was answered, but a new
question surfaced immediately afterwards. What follows is a combination of
my former post and the new question...
I'm trying to work with this query:
SELECT T1.UserID, T2.MiscID
FROM Table2 T2 INNER JOIN Table1 T1 ON T2.ID = T1.ID
WHERE (T2.Category IN (2, 3, 4, 5)) AND (T1.ZipCode IN (22201,22202,22203))
Based on the data in the tables, this query generates the following result:
UserID MiscID
10 105
10 107
11 109
11 120
11 122
The problem is that I do not want duplicate UserID's (I show only 10 and 11,
but the table actually has many rows with duplicate UserID values). Instead,
I want unique (distinct) UserID's and the lowest MiscID for each row. I
would therefore like the result set to look like this:
Using Group By, I get this query:
SELECT T1.UserID, MIN(T2.MiscID) AS MiscID
FROM Table1 T1 INNER JOIN
Table2 T2 ON T1.ID = T2.ID
WHERE (T2.Category IN (2, 3, 4, 5))
AND (T1.ZipCode IN (22201,22202,22203))
GROUP BY T1.UserID
UserID MiscID
10 105
11 109
This solves the need for non-duplicated UserID's. But now I require an
additional column in my query
results--but I still want only the unique UserID keys. My query is now:
SELECT T1.UserID, MIN(T2.MiscID) AS MiscID, T2.Age
FROM Table1 T1 INNER JOIN
Table2 T2 ON T1.ID = T2.ID
WHERE (T2.Category IN (2, 3, 4, 5))
AND (T1.ZipCode IN (22201,22202,22203))
GROUP BY T1.UserID, T2.Age
This is giving me the following results:
UserID MiscID Age
10 105 32
10 106 37
11 109 50
11 112 52
11 118 42
What I require of my results is only the minimum (MIN) MiscID for each
UserID--and its Age:
UserID MiscID Age
10 105 32
11 109 50
I need all three columns because they are returned to a third-tier (client)
application, but I can only have unique UserID values.
I think once I understand how to do this, I will be able to tackle similar
issues that would arise when I add more columns. If there are many ways to
approach this problem, I would prefer a more complex method that would
perform best. This is a query that will be used many times in an enterprise
environment, so response time is key.
Thank you so much for any help.
-- JimWe'd need to see your DDL for the tables involved. Here's a guess:
SELECT
x.UserID, x.MiscID, t3.Age
FROM
(
SELECT T1.UserID, MIN(T2.MiscID) AS MiscID
FROM Table1 T1 INNER JOIN
Table2 T2 ON T1.ID = T2.ID
WHERE (T2.Category IN (2, 3, 4, 5))
AND (T1.ZipCode IN (22201,22202,22203))
GROUP BY T1.UserID
) as x
JOIN Table2 t2 on t2.UserID = x.UserID and t2.MiscID = x.MiscID
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Jim Little" <123@.yahoo.com> wrote in message
news:Ly4Ud.60429$911.14807@.fe2.texas.rr.com...
*** This is a follow-up to my recent post "Distinct For Only Some Columns In
A Row".
Recently, I posted a question. That question was answered, but a new
question surfaced immediately afterwards. What follows is a combination of
my former post and the new question...
I'm trying to work with this query:
SELECT T1.UserID, T2.MiscID
FROM Table2 T2 INNER JOIN Table1 T1 ON T2.ID = T1.ID
WHERE (T2.Category IN (2, 3, 4, 5)) AND (T1.ZipCode IN (22201,22202,22203))
Based on the data in the tables, this query generates the following result:
UserID MiscID
10 105
10 107
11 109
11 120
11 122
The problem is that I do not want duplicate UserID's (I show only 10 and 11,
but the table actually has many rows with duplicate UserID values). Instead,
I want unique (distinct) UserID's and the lowest MiscID for each row. I
would therefore like the result set to look like this:
Using Group By, I get this query:
SELECT T1.UserID, MIN(T2.MiscID) AS MiscID
FROM Table1 T1 INNER JOIN
Table2 T2 ON T1.ID = T2.ID
WHERE (T2.Category IN (2, 3, 4, 5))
AND (T1.ZipCode IN (22201,22202,22203))
GROUP BY T1.UserID
UserID MiscID
10 105
11 109
This solves the need for non-duplicated UserID's. But now I require an
additional column in my query
results--but I still want only the unique UserID keys. My query is now:
SELECT T1.UserID, MIN(T2.MiscID) AS MiscID, T2.Age
FROM Table1 T1 INNER JOIN
Table2 T2 ON T1.ID = T2.ID
WHERE (T2.Category IN (2, 3, 4, 5))
AND (T1.ZipCode IN (22201,22202,22203))
GROUP BY T1.UserID, T2.Age
This is giving me the following results:
UserID MiscID Age
10 105 32
10 106 37
11 109 50
11 112 52
11 118 42
What I require of my results is only the minimum (MIN) MiscID for each
UserID--and its Age:
UserID MiscID Age
10 105 32
11 109 50
I need all three columns because they are returned to a third-tier (client)
application, but I can only have unique UserID values.
I think once I understand how to do this, I will be able to tackle similar
issues that would arise when I add more columns. If there are many ways to
approach this problem, I would prefer a more complex method that would
perform best. This is a query that will be used many times in an enterprise
environment, so response time is key.
Thank you so much for any help.
-- Jim|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
In particular, you have a "magical universal" id column in both tables
and a truly vague "misc_id" in another. That is absurd, since an
identifier has to identify a particular kind of entity.
The names table1 and Table2 give us no hint as to what they mean in the
data model, either. Can we get better specs?|||OK--Fair enough. I posted the "generic" tables because I'm not authorized to
give the actual ones. However, let me create some tables that illustrate my
issue and I think it could benefit everyone.
I'll upload those very soon.
Thanks,
-- Jim|||Hi Jim
Assuming you want the lowest ImageNo per user. Two possible (untested) ways:
SELECT T.UserID AS Expr1,
T.FirstName AS Expr2,
T.LastName AS Expr3,
M.LastLogon AS Expr4,
I.Description AS Expr5,
I.ImageNo AS Expr6,
I.PixelHeight AS Expr7,
I.PixelWidth AS Expr8,
I.FileName AS Expr9
FROM dbo.T_Images I
JOIN dbo.T_Members M ON I.UserID = M.UserID
JOIN dbo.T_Users U ON I.UserID = U.UserID
JOIN ( SELECT UserId, MIN(ImageNo) AS ImageNo FROM dbo.T_Images GROUP BY
UserId ) L ON I.UserID = L.UserID AND I.ImageNo = L.ImageNo
SELECT T.UserID AS Expr1,
T.FirstName AS Expr2,
T.LastName AS Expr3,
M.LastLogon AS Expr4,
I.Description AS Expr5,
I.ImageNo AS Expr6,
I.PixelHeight AS Expr7,
I.PixelWidth AS Expr8,
I.FileName AS Expr9
FROM dbo.T_Images I
JOIN dbo.T_Members M ON I.UserID = M.UserID
JOIN dbo.T_Users U ON I.UserID = U.UserID
WHERE I.ImageNo = ( SELECT MIN(L.ImageNo) FROM dbo.T_Images L WHERE I.UserID
= L.UserID )
You may want to look at example data as insert statements
http://vyaskn.tripod.com/code.htm#inserts
instead of attaching data files.
John
"Jim Little" <123@.yahoo.com> wrote in message
news:Bd6Ud.61154$911.53142@.fe2.texas.rr.com...
> Hello:
> I hope this helps: I've attached an image of the tables involved. I have
> also attached a text document with a Create script (no drops are included
> for safety in case you have the same table names in your db). I pasted the
> attached Create script below for convenience (it is the same as the
> text-file attachment).
> In addition, I attached three data files, one for each data table. These
> are ASCII delimitted format.
> The query I am using is:
> SELECT dbo.T_Users.UserID AS Expr1, dbo.T_Users.FirstName AS Expr2,
> dbo.T_Users.LastName AS Expr3, dbo.T_Members.LastLogon AS Expr4,
> dbo.T_Images.Description AS Expr5,
> dbo.T_Images.ImageNo AS Expr6, dbo.T_Images.PixelHeight AS Expr7,
> dbo.T_Images.PixelWidth AS Expr8,
> dbo.T_Images.FileName AS Expr9
> FROM dbo.T_Images INNER JOIN
> dbo.T_Members ON dbo.T_Images.UserID =
> dbo.T_Members.UserID INNER JOIN
> dbo.T_Users ON dbo.T_Images.UserID =
> dbo.T_Users.UserID
> This is giving me (sorry for the word wrapping):
> Expr1,Expr2,Expr3,Expr4,Expr5,Expr6,Expr
7,Expr8,Expr9
> 1,Jim ,Smith ,2005-01-05 00:00:00.000,Hawaii
> ,1,500,500,hawaii.jpg
> 1,Jim ,Smith ,2005-01-05 00:00:00.000,Grand
> Canyon ,2,350,350,gcanyon.jpg
> 1,Jim ,Smith ,2005-01-05 00:00:00.000,Time
> Square ,3,400,600,timessq.jpg
> 2,Steve ,Jones ,2005-01-22
> 00:00:00.000,Orlando ,2,500,500,orlando.jpg
> 2,Steve ,Jones ,2005-01-22 00:00:00.000,LA
> What I would like to see is:
> Expr1,Expr2,Expr3,Expr4,Expr5,Expr6,Expr
7,Expr8,Expr9
> 1,Jim ,Smith ,2005-01-05 00:00:00.000,Hawaii
> ,1,500,500,hawaii.jpg
> 2,Steve ,Jones ,2005-01-22
> 00:00:00.000,Orlando ,2,500,500,orlando.jpg
>
> Notice that the results I would like to see are unque to the first (Expr1,
> or UserID) column--but I get to see all the other columns that apply to
> that row.
> One idea I had is to first create a Select that selects only the UserIDs.
> Then, use those UserIDs in an "IN" or "EXISTS" clause.
> Thank you for any insights.
> Regards,
> -- Jim
> ===============
>
> CREATE TABLE [dbo].[T_Images] (
> [ImageID] [int] IDENTITY (1, 1) NOT NULL ,
> [UserID] [int] NOT NULL ,
> [ImageNo] [smallint] NOT NULL ,
> [Description] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PixelHeight] [smallint] NOT NULL ,
> [PixelWidth] [smallint] NOT NULL ,
> [FileName] [char] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[T_Members] (
> [UserID] [int] NOT NULL ,
> [LastLogon] [datetime] NOT NULL ,
> [MemberAlias] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Password] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[T_Users] (
> [UserID] [int] IDENTITY (1, 1) NOT NULL ,
> [FirstName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>|||Looks great--Thank you John. I will try both and I'll post feedback either
way within a day.
Thanks,
-- Jim

Displaying Totals in matrix-control based upon a RunningValue-expression

Hi,
I have a Matrix-control which has to "summarize" a lot of data in the end,
based upon boolean values. (the column-grouping is a static group).
Customer1
Test1 0 1 0 Value 1
Test 1.1 Value 2
Test 1.2 Value 3
Test 2 1 0 1 Value 4
Test 2.1 Value 5
Test 3 0 1 0 Value 6
______________________________________
Totals: 1 2 1
Customer 2
Test 1 ...
The values are bool-values AND not always displayed. I want to summarize
the values as they appear on the screen, not how they are in the dataset.
Because, now the runningValues-function always summarizes to much, which is
also al the "invisible" data between the main groups. I have written
something to display 0 in the subgroups, but it doesn't change my result.
Is it possible to do this and, if so, how?
Thanks a lot for your answers!
IveAre you giving a Scope'
"Ive" wrote:
> Hi,
> I have a Matrix-control which has to "summarize" a lot of data in the end,
> based upon boolean values. (the column-grouping is a static group).
> Customer1
> Test1 0 1 0 Value 1
> Test 1.1 Value 2
> Test 1.2 Value 3
> Test 2 1 0 1 Value 4
> Test 2.1 Value 5
> Test 3 0 1 0 Value 6
> ______________________________________
> Totals: 1 2 1
> Customer 2
> Test 1 ...
> The values are bool-values AND not always displayed. I want to summarize
> the values as they appear on the screen, not how they are in the dataset.
> Because, now the runningValues-function always summarizes to much, which is
> also al the "invisible" data between the main groups. I have written
> something to display 0 in the subgroups, but it doesn't change my result.
> Is it possible to do this and, if so, how?
> Thanks a lot for your answers!
> Ive
>
>|||Yes,
I am!
Problem is that the Scope doesn't seem to work correctly: it refers to the
dataset in stead of the matrix itself.
I have tried something like this:
=RunningValue(Abs(cInt(Fields!PlannedChecked.Value)),Sum,"matrix1_SubGroup")
I again refer to the fact that Fields!PlannedChecked.Value is a boolean
value and that he doesn't seem to summarize the values as in the control,
but in the dataset. He doesn't summarize correctly.
The runningValue function was inserted with an IIF -statement in combination
with the InScope-function to determine whether I'm actually in a
summarize-column.
A count would also do the job, but how to do that if I can't put a
limitation on the scope?
A dSum would do the trick, but that isn't supported.
I'm really stuck on this one, but as long as someone hasn't officially told
me how the RunningValue-function works exactly in a matrix-control, I keep
searching for a solution and reason.
Regards,
Ive
"Soan" <Soan@.discussions.microsoft.com> wrote in message
news:EF2FBEA3-C751-4C63-84C5-C319251D5F7C@.microsoft.com...
> Are you giving a Scope'
> "Ive" wrote:
>> Hi,
>> I have a Matrix-control which has to "summarize" a lot of data in the
>> end,
>> based upon boolean values. (the column-grouping is a static group).
>> Customer1
>> Test1 0 1 0 Value 1
>> Test 1.1 Value 2
>> Test 1.2 Value 3
>> Test 2 1 0 1 Value 4
>> Test 2.1 Value 5
>> Test 3 0 1 0 Value 6
>> ______________________________________
>> Totals: 1 2 1
>> Customer 2
>> Test 1 ...
>> The values are bool-values AND not always displayed. I want to summarize
>> the values as they appear on the screen, not how they are in the dataset.
>> Because, now the runningValues-function always summarizes to much, which
>> is
>> also al the "invisible" data between the main groups. I have written
>> something to display 0 in the subgroups, but it doesn't change my result.
>> Is it possible to do this and, if so, how?
>> Thanks a lot for your answers!
>> Ive
>>

Displaying Series Group Total for a RS 2005 Chart

Hi there,

I'm trying to create a chart in RS 2005 that will split one set of
data into two separate lines while at the same time, creating a line
that will also display the average of all records. Some background
may be useful:

TABLE

Student_IdAttended_Meeting_CountTest_ScoreTest_ID11090125851315011109522580231652

What I would like to do is create 1 line that shows the average test score for all students with the test id as the x-axis value, 1 line that shows the average test score for all students who attended at least 5 meetings, and 1 line that does the same for all students who attended less than 5 meetings. In the Values box, I am using "=cint(AVG(Fields!Test_Score.Value))", and I have set a series group with the group on expression as "=Fields!Attended_Meetings_Count.Value > 5". This works great for splitting the line representing all values into the two lines I want, however, the test_score for all students line goes away. Anyone have any ideas on how to generate this line again? I've tried adding it as another value, but the series group then applies itself to the new values line as well and I end up with 4 lines.

Help!
-Rich

Hi,

From your description, it seems that you want to show three average values in three different conditions, right?

Why don't you calculate these three values separately? See the following sample:

=CINT(AVG(IIF(Fields!Attended_Meetings_Count.Value > 5, Fields!Test_Score.Value,0)))

=CINT(AVG(IIF(Fields!Attended_Meetings_Count.Value < 5, Fields!Test_Score.Value,0)))

=CINT(AVG(Fields!Test_Score.Value,0))

Thanks.

|||

Hi there,

Thanks for the reply. I did try the values you had suggested above, however, I get the following error when trying to view the preview:

The Value expression for the chart 'chart1' has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

Any other suggestions?

Thanks again!

Rich

Displaying Reports

I have a folder with many reports in it. Each report has a name and a
description of the report. How can I group the reports together?
IE:
Data A Report
Data A Chart
Data B Report
Data B ChartThe only way is by name of the report.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"eric_rs1" <ericrs1@.discussions.microsoft.com> wrote in message
news:0D88A0BB-C1A9-44F7-97EF-55F15538F6AA@.microsoft.com...
> I have a folder with many reports in it. Each report has a name and a
> description of the report. How can I group the reports together?
> IE:
> Data A Report
> Data A Chart
> Data B Report
> Data B Chart
>|||Here is another example. I need to have these two reports together.
Daily Phone Stats Report
Weekly Phone Stats Report
"eric_rs1" wrote:
> I have a folder with many reports in it. Each report has a name and a
> description of the report. How can I group the reports together?
> IE:
> Data A Report
> Data A Chart
> Data B Report
> Data B Chart
>|||Can't you use multiple folders?
True, you can only use a single folder when deploying from Visual Studio,
but once deployed, you can move the report anywhere.
"eric_rs1" <ericrs1@.discussions.microsoft.com> wrote in message
news:76F91A9E-DA73-4D9D-9D2B-24658B77BD7C@.microsoft.com...
> Here is another example. I need to have these two reports together.
> Daily Phone Stats Report
> Weekly Phone Stats Report
> "eric_rs1" wrote:
>> I have a folder with many reports in it. Each report has a name and a
>> description of the report. How can I group the reports together?
>> IE:
>> Data A Report
>> Data A Chart
>> Data B Report
>> Data B Chart
>>

Displaying max values of each group in SQL

Hello everyone;

I have here a code that displays the most recent date for each group of records. But the problem is, I am not able to include some fields of the table.

There are 3 tables named CUST, ACCT, and TRAN:

CUST:
CNO NAME
CN101 DAN
CN102 AAA

ACCT:
ANO CNO
AN101 CN101
AN102 CN102

TRAN:
TNO ANO TDATE BAL
TN101 AN101 01/25/2006 3,000
TN102 AN101 02/15/2006 5,000
TN103 AN102 02/01/2006 4,000
TN104 AN102 02/27/2006 8,000
TN105 AN102 03/18/2006 2,000

And the resultant table should look something like this:

ANO NAME TDATE BAL
AN101 AAA 02/15/2006 5,000
AN102 BBB 03/18/2006 2,000

Now, here's my code:

SELECT DISTINCT

B.NAME,
C.ANO,
MAX(A.TDATE)

FROM TRAN A,
CUST B,
ACCT C

WHERE B.CNO = C.CNO
AND C.ANO = A.ANO

GROUP BY B.CNO,
B.NAME,
C.ANO;

And the resultant table is:

NAME ANO MAX(TDATE)
AN101 AAA 02/15/2006
AN102 BBB 03/18/2006

The problem is, I want to add the field 'BAL' to the resultant table but when I insert 'BAL' to the 'SELECT' clause, the result will look something like this:

ANO NAME TDATE BAL
AN101 AAA 01/25/2006 3,000
AN101 AAA 02/15/2006 5,000
AN102 BBB 02/01/2006 4,000
AN102 BBB 02/27/2006 8,000
AN102 BBB 03/18/2006 2,000

I will really appreciate any help.

Thnks,
dan15phselect B.NAME
, C.ANO
, A.TDATE
, A.BAL
from TRAN A
inner
join ACCT C
on C.ANO = A.ANO
inner
join CUST B
on C.CNO = B.CNO
where A.TDATE
= ( select max(TDATE)
from TRAN
where ANO = A.ANO )|||Sorry for taking so looong to reply. But anyway, thanks for the help r937 (http://www.dbforums.com/member.php?find=lastposter&t=1606412). I finally made it. just made a couple of changes to the code. Actually, I'm still a newbie in SQL and havent used 'inner join' (just recently) and seldom in using inner queries. thanks a lot for the help.:D

Displaying images clarafication

I have seen many postings regarding asp. sql and images. One posting
on another group stated that images (jpg, etc) can be called by a db
instead of being stored on the db.

I am asking for some help with coding such a thing. What I want to do
is include in the db the location (url) for the image so I can add it
a pserson's record set and then be able to have it called from the db
to post on a web page.
This is for an awards system.

Thanks for any help.

Davedavestrike (davestrike@.nventure.com) writes:
> I have seen many postings regarding asp. sql and images. One posting
> on another group stated that images (jpg, etc) can be called by a db
> instead of being stored on the db.
> I am asking for some help with coding such a thing. What I want to do
> is include in the db the location (url) for the image so I can add it
> a pserson's record set and then be able to have it called from the db
> to post on a web page.
> This is for an awards system.

I think you have misunderstood something. SQL Server is not able
to invoke an URL. Well, OK, you write an XP or an COM object for the
task, but that would be to retrieve data from the URL into the server.

The usual alternative to storing images within the database, is to
only store a filename (or for that matter a URL), and the you retrieve
that filename to the application, and then the application retrieves
the file.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Displaying Details of a group

I have two groups. I want to do this:

Group 1

Group2

Group2 Summary info

Group2 Details

Group2 Details

Group2 Details

Group2 Details

I don't know how to get the table details row to only show my group 2 details.

Thanks

Patawa,

You can do this by creating a Table that has two groupings for Group1

and Group2. From the layout described, remove the Table Header and the

Tables and grouping Footers. For the Group2 summary information, add a

second header row to the Group2 grouping and add the appropriate

aggregates. Then, you can place the detail fields in the detail section

of the Table.

Ian

displaying date ranges in the report header

I am using a table to structure my report.
Body of the report
Table header
Group Header
Details
Group Footer
Table Footer
Earlier I had put the report name with the company logo in the report
header section but now I moved it to the body of the report just above
the table. I am trying to display the date range next to the report
name. I am trying to use an expression saying that if the date
parameters are NULL, then print the date today() else print the two
date parameters with a hyphen seperating them. On running the report it
gives an error that I caanot use expressions in a text box or in the
report header. I am creating a variable in the dataset as a calculated
field and then setting the expression.
Can someone please help'
Regards
JaideepYes, specify the expression in a textbox (assume the textbox name is
textbox5 on the body and make the visibility property FALSE.
Next, in the report header, for the expression use:
=ReportItems!TextBox5.Value
=-Chris
"jai" <dbasybase@.gmail.com> wrote in message
news:1161364744.236416.98920@.k70g2000cwa.googlegroups.com...
>I am using a table to structure my report.
> Body of the report
> Table header
> Group Header
> Details
> Group Footer
> Table Footer
> Earlier I had put the report name with the company logo in the report
> header section but now I moved it to the body of the report just above
> the table. I am trying to display the date range next to the report
> name. I am trying to use an expression saying that if the date
> parameters are NULL, then print the date today() else print the two
> date parameters with a hyphen seperating them. On running the report it
> gives an error that I caanot use expressions in a text box or in the
> report header. I am creating a variable in the dataset as a calculated
> field and then setting the expression.
> Can someone please help'
> Regards
> Jaideep
>|||Chris,
Thanks for the help. I am trying to set this expressions and it keeps
telling me that dbnull cannot be used.
IIf((Parameters!date1.Value = System.DBNull And Parameters!date2.Value
= System.DBNull),Today(),(Parameters!date1.Value & "-" &
Parameters!date2.Value))
I have two parameters. I am using a stored proc in the background. If
ther parameters are null it gives me data for the previous day.
I need to show the report heading as the <Report name> <date>/<date1 -
date2>
I think it the system does not want to accept the system.DBNull
Regards
Jaideep
Chris Conner wrote:
> Yes, specify the expression in a textbox (assume the textbox name is
> textbox5 on the body and make the visibility property FALSE.
> Next, in the report header, for the expression use:
> =ReportItems!TextBox5.Value
> =-Chris
> "jai" <dbasybase@.gmail.com> wrote in message
> news:1161364744.236416.98920@.k70g2000cwa.googlegroups.com...
> >I am using a table to structure my report.
> >
> > Body of the report
> >
> > Table header
> > Group Header
> > Details
> > Group Footer
> > Table Footer
> >
> > Earlier I had put the report name with the company logo in the report
> > header section but now I moved it to the body of the report just above
> > the table. I am trying to display the date range next to the report
> > name. I am trying to use an expression saying that if the date
> > parameters are NULL, then print the date today() else print the two
> > date parameters with a hyphen seperating them. On running the report it
> > gives an error that I caanot use expressions in a text box or in the
> > report header. I am creating a variable in the dataset as a calculated
> > field and then setting the expression.
> >
> > Can someone please help'
> >
> > Regards
> >
> > Jaideep
> >|||I think I got it. I should use is Nothing instead of System.DBNull.
I will try what you had said.
Jaideep
jai wrote:
> Chris,
> Thanks for the help. I am trying to set this expressions and it keeps
> telling me that dbnull cannot be used.
> IIf((Parameters!date1.Value = System.DBNull And Parameters!date2.Value
> = System.DBNull),Today(),(Parameters!date1.Value & "-" &
> Parameters!date2.Value))
> I have two parameters. I am using a stored proc in the background. If
> ther parameters are null it gives me data for the previous day.
> I need to show the report heading as the <Report name> <date>/<date1 -
> date2>
> I think it the system does not want to accept the system.DBNull
> Regards
> Jaideep
> Chris Conner wrote:
> > Yes, specify the expression in a textbox (assume the textbox name is
> > textbox5 on the body and make the visibility property FALSE.
> >
> > Next, in the report header, for the expression use:
> > =ReportItems!TextBox5.Value
> >
> > =-Chris
> >
> > "jai" <dbasybase@.gmail.com> wrote in message
> > news:1161364744.236416.98920@.k70g2000cwa.googlegroups.com...
> > >I am using a table to structure my report.
> > >
> > > Body of the report
> > >
> > > Table header
> > > Group Header
> > > Details
> > > Group Footer
> > > Table Footer
> > >
> > > Earlier I had put the report name with the company logo in the report
> > > header section but now I moved it to the body of the report just above
> > > the table. I am trying to display the date range next to the report
> > > name. I am trying to use an expression saying that if the date
> > > parameters are NULL, then print the date today() else print the two
> > > date parameters with a hyphen seperating them. On running the report it
> > > gives an error that I caanot use expressions in a text box or in the
> > > report header. I am creating a variable in the dataset as a calculated
> > > field and then setting the expression.
> > >
> > > Can someone please help'
> > >
> > > Regards
> > >
> > > Jaideep
> > >|||Don't use that = use instead iif((Parameters!date1.Value = Nothing ...
to test for nullability.
=-Chris
"jai" <dbasybase@.gmail.com> wrote in message
news:1161371300.024539.176980@.k70g2000cwa.googlegroups.com...
> Chris,
> Thanks for the help. I am trying to set this expressions and it keeps
> telling me that dbnull cannot be used.
> IIf((Parameters!date1.Value = System.DBNull And Parameters!date2.Value
> = System.DBNull),Today(),(Parameters!date1.Value & "-" &
> Parameters!date2.Value))
> I have two parameters. I am using a stored proc in the background. If
> ther parameters are null it gives me data for the previous day.
> I need to show the report heading as the <Report name> <date>/<date1 -
> date2>
> I think it the system does not want to accept the system.DBNull
> Regards
> Jaideep
> Chris Conner wrote:
>> Yes, specify the expression in a textbox (assume the textbox name is
>> textbox5 on the body and make the visibility property FALSE.
>> Next, in the report header, for the expression use:
>> =ReportItems!TextBox5.Value
>> =-Chris
>> "jai" <dbasybase@.gmail.com> wrote in message
>> news:1161364744.236416.98920@.k70g2000cwa.googlegroups.com...
>> >I am using a table to structure my report.
>> >
>> > Body of the report
>> >
>> > Table header
>> > Group Header
>> > Details
>> > Group Footer
>> > Table Footer
>> >
>> > Earlier I had put the report name with the company logo in the report
>> > header section but now I moved it to the body of the report just above
>> > the table. I am trying to display the date range next to the report
>> > name. I am trying to use an expression saying that if the date
>> > parameters are NULL, then print the date today() else print the two
>> > date parameters with a hyphen seperating them. On running the report it
>> > gives an error that I caanot use expressions in a text box or in the
>> > report header. I am creating a variable in the dataset as a calculated
>> > field and then setting the expression.
>> >
>> > Can someone please help'
>> >
>> > Regards
>> >
>> > Jaideep
>> >
>|||Chris,
I created the text box in the body and set the expression which is
being displayed properly. Now I created another text box in the report
header section and in the expression I said
ReportItems!TextBox5.Value
It is giving me an error --
The Value expression for the textbox 'textbox17' contains an error:
The expression referenced a non-existing reportitem in the reportitems
collection.
Preview complete -- 0 errors, 1 warnings
I can understand the error but how do i add something to the
reportitems collection?
Jaideep
Chris Conner wrote:
> Don't use that = use instead iif((Parameters!date1.Value = Nothing ...
> to test for nullability.
> =-Chris
> "jai" <dbasybase@.gmail.com> wrote in message
> news:1161371300.024539.176980@.k70g2000cwa.googlegroups.com...
> > Chris,
> >
> > Thanks for the help. I am trying to set this expressions and it keeps
> > telling me that dbnull cannot be used.
> >
> > IIf((Parameters!date1.Value = System.DBNull And Parameters!date2.Value
> > = System.DBNull),Today(),(Parameters!date1.Value & "-" &
> > Parameters!date2.Value))
> >
> > I have two parameters. I am using a stored proc in the background. If
> > ther parameters are null it gives me data for the previous day.
> >
> > I need to show the report heading as the <Report name> <date>/<date1 -
> > date2>
> >
> > I think it the system does not want to accept the system.DBNull
> >
> > Regards
> >
> > Jaideep
> > Chris Conner wrote:
> >> Yes, specify the expression in a textbox (assume the textbox name is
> >> textbox5 on the body and make the visibility property FALSE.
> >>
> >> Next, in the report header, for the expression use:
> >> =ReportItems!TextBox5.Value
> >>
> >> =-Chris
> >>
> >> "jai" <dbasybase@.gmail.com> wrote in message
> >> news:1161364744.236416.98920@.k70g2000cwa.googlegroups.com...
> >> >I am using a table to structure my report.
> >> >
> >> > Body of the report
> >> >
> >> > Table header
> >> > Group Header
> >> > Details
> >> > Group Footer
> >> > Table Footer
> >> >
> >> > Earlier I had put the report name with the company logo in the report
> >> > header section but now I moved it to the body of the report just above
> >> > the table. I am trying to display the date range next to the report
> >> > name. I am trying to use an expression saying that if the date
> >> > parameters are NULL, then print the date today() else print the two
> >> > date parameters with a hyphen seperating them. On running the report it
> >> > gives an error that I caanot use expressions in a text box or in the
> >> > report header. I am creating a variable in the dataset as a calculated
> >> > field and then setting the expression.
> >> >
> >> > Can someone please help'
> >> >
> >> > Regards
> >> >
> >> > Jaideep
> >> >
> >|||I forgot to mention - it is CASE sensitive.. by default, the textbox names
are in lowercase. Sorry about that.
=-Chris
"jai" <dbasybase@.gmail.com> wrote in message
news:1161374030.963039.245760@.b28g2000cwb.googlegroups.com...
> Chris,
> I created the text box in the body and set the expression which is
> being displayed properly. Now I created another text box in the report
> header section and in the expression I said
> ReportItems!TextBox5.Value
> It is giving me an error --
> The Value expression for the textbox 'textbox17' contains an error:
> The expression referenced a non-existing reportitem in the reportitems
> collection.
> Preview complete -- 0 errors, 1 warnings
> I can understand the error but how do i add something to the
> reportitems collection?
> Jaideep
>
> Chris Conner wrote:
>> Don't use that = use instead iif((Parameters!date1.Value = Nothing ...
>> to test for nullability.
>> =-Chris
>> "jai" <dbasybase@.gmail.com> wrote in message
>> news:1161371300.024539.176980@.k70g2000cwa.googlegroups.com...
>> > Chris,
>> >
>> > Thanks for the help. I am trying to set this expressions and it keeps
>> > telling me that dbnull cannot be used.
>> >
>> > IIf((Parameters!date1.Value = System.DBNull And Parameters!date2.Value
>> > = System.DBNull),Today(),(Parameters!date1.Value & "-" &
>> > Parameters!date2.Value))
>> >
>> > I have two parameters. I am using a stored proc in the background. If
>> > ther parameters are null it gives me data for the previous day.
>> >
>> > I need to show the report heading as the <Report name> <date>/<date1 -
>> > date2>
>> >
>> > I think it the system does not want to accept the system.DBNull
>> >
>> > Regards
>> >
>> > Jaideep
>> > Chris Conner wrote:
>> >> Yes, specify the expression in a textbox (assume the textbox name is
>> >> textbox5 on the body and make the visibility property FALSE.
>> >>
>> >> Next, in the report header, for the expression use:
>> >> =ReportItems!TextBox5.Value
>> >>
>> >> =-Chris
>> >>
>> >> "jai" <dbasybase@.gmail.com> wrote in message
>> >> news:1161364744.236416.98920@.k70g2000cwa.googlegroups.com...
>> >> >I am using a table to structure my report.
>> >> >
>> >> > Body of the report
>> >> >
>> >> > Table header
>> >> > Group Header
>> >> > Details
>> >> > Group Footer
>> >> > Table Footer
>> >> >
>> >> > Earlier I had put the report name with the company logo in the
>> >> > report
>> >> > header section but now I moved it to the body of the report just
>> >> > above
>> >> > the table. I am trying to display the date range next to the report
>> >> > name. I am trying to use an expression saying that if the date
>> >> > parameters are NULL, then print the date today() else print the two
>> >> > date parameters with a hyphen seperating them. On running the report
>> >> > it
>> >> > gives an error that I caanot use expressions in a text box or in the
>> >> > report header. I am creating a variable in the dataset as a
>> >> > calculated
>> >> > field and then setting the expression.
>> >> >
>> >> > Can someone please help'
>> >> >
>> >> > Regards
>> >> >
>> >> > Jaideep
>> >> >
>> >
>|||Thanks that did the trick. But I found another problem. If the date
range is one day apart then the text box displays the correct values
but if the range is more than two days, then it does not display the
values but the report returns the data.
Any ideas?
Jaideep
Chris Conner wrote:
> I forgot to mention - it is CASE sensitive.. by default, the textbox names
> are in lowercase. Sorry about that.
> =-Chris
> "jai" <dbasybase@.gmail.com> wrote in message
> news:1161374030.963039.245760@.b28g2000cwb.googlegroups.com...
> > Chris,
> > I created the text box in the body and set the expression which is
> > being displayed properly. Now I created another text box in the report
> > header section and in the expression I said
> > ReportItems!TextBox5.Value
> >
> > It is giving me an error --
> > The Value expression for the textbox 'textbox17' contains an error:
> > The expression referenced a non-existing reportitem in the reportitems
> > collection.
> > Preview complete -- 0 errors, 1 warnings
> >
> > I can understand the error but how do i add something to the
> > reportitems collection?
> >
> > Jaideep
> >
> >
> >
> > Chris Conner wrote:
> >> Don't use that = use instead iif((Parameters!date1.Value = Nothing ...
> >>
> >> to test for nullability.
> >>
> >> =-Chris
> >>
> >> "jai" <dbasybase@.gmail.com> wrote in message
> >> news:1161371300.024539.176980@.k70g2000cwa.googlegroups.com...
> >> > Chris,
> >> >
> >> > Thanks for the help. I am trying to set this expressions and it keeps
> >> > telling me that dbnull cannot be used.
> >> >
> >> > IIf((Parameters!date1.Value = System.DBNull And Parameters!date2.Value
> >> > = System.DBNull),Today(),(Parameters!date1.Value & "-" &
> >> > Parameters!date2.Value))
> >> >
> >> > I have two parameters. I am using a stored proc in the background. If
> >> > ther parameters are null it gives me data for the previous day.
> >> >
> >> > I need to show the report heading as the <Report name> <date>/<date1 -
> >> > date2>
> >> >
> >> > I think it the system does not want to accept the system.DBNull
> >> >
> >> > Regards
> >> >
> >> > Jaideep
> >> > Chris Conner wrote:
> >> >> Yes, specify the expression in a textbox (assume the textbox name is
> >> >> textbox5 on the body and make the visibility property FALSE.
> >> >>
> >> >> Next, in the report header, for the expression use:
> >> >> =ReportItems!TextBox5.Value
> >> >>
> >> >> =-Chris
> >> >>
> >> >> "jai" <dbasybase@.gmail.com> wrote in message
> >> >> news:1161364744.236416.98920@.k70g2000cwa.googlegroups.com...
> >> >> >I am using a table to structure my report.
> >> >> >
> >> >> > Body of the report
> >> >> >
> >> >> > Table header
> >> >> > Group Header
> >> >> > Details
> >> >> > Group Footer
> >> >> > Table Footer
> >> >> >
> >> >> > Earlier I had put the report name with the company logo in the
> >> >> > report
> >> >> > header section but now I moved it to the body of the report just
> >> >> > above
> >> >> > the table. I am trying to display the date range next to the report
> >> >> > name. I am trying to use an expression saying that if the date
> >> >> > parameters are NULL, then print the date today() else print the two
> >> >> > date parameters with a hyphen seperating them. On running the report
> >> >> > it
> >> >> > gives an error that I caanot use expressions in a text box or in the
> >> >> > report header. I am creating a variable in the dataset as a
> >> >> > calculated
> >> >> > field and then setting the expression.
> >> >> >
> >> >> > Can someone please help'
> >> >> >
> >> >> > Regards
> >> >> >
> >> >> > Jaideep
> >> >> >
> >> >
> >|||Chris,
I have been seeing that if the report is more than one page, the system
prints the date range in the report header on the last page.
I checked the properties but did not find anything unusual there.
Regards
Jaideep
jai wrote:
> Thanks that did the trick. But I found another problem. If the date
> range is one day apart then the text box displays the correct values
> but if the range is more than two days, then it does not display the
> values but the report returns the data.
> Any ideas?
> Jaideep
> Chris Conner wrote:
> > I forgot to mention - it is CASE sensitive.. by default, the textbox names
> > are in lowercase. Sorry about that.
> >
> > =-Chris
> >
> > "jai" <dbasybase@.gmail.com> wrote in message
> > news:1161374030.963039.245760@.b28g2000cwb.googlegroups.com...
> > > Chris,
> > > I created the text box in the body and set the expression which is
> > > being displayed properly. Now I created another text box in the report
> > > header section and in the expression I said
> > > ReportItems!TextBox5.Value
> > >
> > > It is giving me an error --
> > > The Value expression for the textbox 'textbox17' contains an error:
> > > The expression referenced a non-existing reportitem in the reportitems
> > > collection.
> > > Preview complete -- 0 errors, 1 warnings
> > >
> > > I can understand the error but how do i add something to the
> > > reportitems collection?
> > >
> > > Jaideep
> > >
> > >
> > >
> > > Chris Conner wrote:
> > >> Don't use that = use instead iif((Parameters!date1.Value = Nothing ...
> > >>
> > >> to test for nullability.
> > >>
> > >> =-Chris
> > >>
> > >> "jai" <dbasybase@.gmail.com> wrote in message
> > >> news:1161371300.024539.176980@.k70g2000cwa.googlegroups.com...
> > >> > Chris,
> > >> >
> > >> > Thanks for the help. I am trying to set this expressions and it keeps
> > >> > telling me that dbnull cannot be used.
> > >> >
> > >> > IIf((Parameters!date1.Value = System.DBNull And Parameters!date2.Value
> > >> > = System.DBNull),Today(),(Parameters!date1.Value & "-" &
> > >> > Parameters!date2.Value))
> > >> >
> > >> > I have two parameters. I am using a stored proc in the background. If
> > >> > ther parameters are null it gives me data for the previous day.
> > >> >
> > >> > I need to show the report heading as the <Report name> <date>/<date1 -
> > >> > date2>
> > >> >
> > >> > I think it the system does not want to accept the system.DBNull
> > >> >
> > >> > Regards
> > >> >
> > >> > Jaideep
> > >> > Chris Conner wrote:
> > >> >> Yes, specify the expression in a textbox (assume the textbox name is
> > >> >> textbox5 on the body and make the visibility property FALSE.
> > >> >>
> > >> >> Next, in the report header, for the expression use:
> > >> >> =ReportItems!TextBox5.Value
> > >> >>
> > >> >> =-Chris
> > >> >>
> > >> >> "jai" <dbasybase@.gmail.com> wrote in message
> > >> >> news:1161364744.236416.98920@.k70g2000cwa.googlegroups.com...
> > >> >> >I am using a table to structure my report.
> > >> >> >
> > >> >> > Body of the report
> > >> >> >
> > >> >> > Table header
> > >> >> > Group Header
> > >> >> > Details
> > >> >> > Group Footer
> > >> >> > Table Footer
> > >> >> >
> > >> >> > Earlier I had put the report name with the company logo in the
> > >> >> > report
> > >> >> > header section but now I moved it to the body of the report just
> > >> >> > above
> > >> >> > the table. I am trying to display the date range next to the report
> > >> >> > name. I am trying to use an expression saying that if the date
> > >> >> > parameters are NULL, then print the date today() else print the two
> > >> >> > date parameters with a hyphen seperating them. On running the report
> > >> >> > it
> > >> >> > gives an error that I caanot use expressions in a text box or in the
> > >> >> > report header. I am creating a variable in the dataset as a
> > >> >> > calculated
> > >> >> > field and then setting the expression.
> > >> >> >
> > >> >> > Can someone please help'
> > >> >> >
> > >> >> > Regards
> > >> >> >
> > >> >> > Jaideep
> > >> >> >
> > >> >
> > >

DisplayGroupTree Format?

Hi, I have a report in which I have created a group. The grou field is an integer in the database. I could format the groupname field on the report to display it without any decimals.
However, when I bind the report to a viewer on windows form, the displaygrouptree diplays the group name with decimals. For ex:, the report displays 2000 and the displaygrouptree displays it as 2000.00 How can I make the displaygrouptree to display it as 2000?

Any suggestions are greatly appreciated.

Thanks.You could use a formula to convert it to a string and group on the formula.
e.g.

totext({table.field}, 0, '')

Display subtotals and grand total

Hi,

I have a table:

CREATE TABLE [dbo].[TBL_REPORT1](

[Source] [varchar](3) NULL,

[Contract No] [varchar](15) NOT NULL,

[Business Group] [varchar](4) NULL,

[Customer Name] [varchar](50) NULL,

[Equipment Description] [varchar](20) NULL,

[Lease Type] [varchar](2) NULL,

[Term] [int] NULL,

[Booking Date] [datetime] NULL,

[# of Assets] [int] NULL,

[Equipment Cost] [money] NULL,

[Restructured] [varchar](3) NOT NULL

)

Sample Data

INSERT INTO [TBL_REPORT1] VALUES('SFS','319-0010146-001','SEF','NorthBay Healthcare Group','SBT Performance Cont','LP',132,'Apr 4 2007 12:00:00:000AM',1,2612000.0000,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','729-0015625-023','SEF','Black Diamond Properties, Inc.','Kubota L48 TLB Tract','OL',60,'Apr 3 2007 12:00:00:000AM',1,36000.0000,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','729-0015648-007','SEF','The River Wilderness Club, Inc.','Honda Salsco Greens','OL',48,'Apr 5 2007 12:00:00:000AM',1,11401.0000,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','749-0013599-020','VEN','THYSSENKRUPP BUDD COMPANY','COMPUTER GEAR','CS',30,'Apr 5 2007 12:00:00:000AM',1,232965.0300,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','749-0016965-002','VEN','GREEN OAK TOWNSHIP','COMPUTER GEAR','CS',33,'Apr 5 2007 12:00:00:000AM',1,56789.9100,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','749-0052401-001','VEN','Zircon Corp.','INJECTION MOLDING MC','CS',70,'Apr 11 2007 12:00:00:000AM',1,74380.0300,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','766-0001804-007','IGP','Helena Chemical Company','1800 GAL','TL',36,'Apr 18 2007 12:00:00:000AM',17,292147.7000,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','769-0002040-001','CBF','Ball Packaging Corp.','Second Filler/Seamer','CS',1,'Apr 13 2007 12:00:00:000AM',1,276928.4500,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','769-0002040-002','CBF','Ball Packaging Corp.','Second Filler/Seamer','CS',1,'Apr 13 2007 12:00:00:000AM',1,377415.3500,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','769-0002053-001','CBF','VIH Helicopters USA, Inc.','Sikorsky S-61N','CS',84,'Apr 6 2007 12:00:00:000AM',1,4612500.0000,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','778-0014680-024','CPM','SUN MICROSYSTEMS, INC.','trade receivable','CS',2,'Apr 5 2007 12:00:00:000AM',1,20177632.2300,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','778-0015956-014','CPM','Autozone Inc.','trade receivable','CS',11,'Apr 3 2007 12:00:00:000AM',1,2128173.3600,'No')

I want to display subtotals - sum of [Equipment Cost] for each [Business Group] and also the grand total.

Sample Output:

SFS 769-0002040-001 CBF Ball Packaging Corp. Second Filler/Seamer CS 1 00:00.0 1 276928.5 No SFS 769-0002040-002 CBF Ball Packaging Corp. Second Filler/Seamer CS 1 00:00.0 1 377415.4 No SFS 769-0002053-001 CBF VIH Helicopters USA, Inc. Sikorsky S-61N CS 84 00:00.0 1 4612500 No CBF Count 3 SFS 778-0015956-014 CPM Autozone Inc. trade receivable CS 11 00:00.0 1 2128173 No SFS 778-0014680-024 CPM SUN MICROSYSTEMS, INC. trade receivable CS 2 00:00.0 1 20177632 No CPM Count 2

I tried with CUBE and ROLLUP but then with multiple fields it was not giving me the right output. Can anyone help. Thanks.

Posted above was the sample count subtotal generated in Excel. I am interested in getting only the subtotal count/sum and the grand total. Labels like CBF Count, CPM Count and not required.

SFS 769-0002040-001 CBF Ball Packaging Corp. Second Filler/Seamer CS 1 00:00.0 1 276928.5 No SFS 769-0002040-002 CBF Ball Packaging Corp. Second Filler/Seamer CS 1 00:00.0 1 377415.4 No SFS 769-0002053-001 CBF VIH Helicopters USA, Inc. Sikorsky S-61N CS 84 00:00.0 1 4612500 No CBF Total 5266844 SFS 778-0015956-014 CPM Autozone Inc. trade receivable CS 11 00:00.0 1 2128173 No SFS 778-0014680-024 CPM SUN MICROSYSTEMS, INC. trade receivable CS 2 00:00.0 1 20177632 No CPM Total 22305806 SFS 766-0001804-007 IGP Helena Chemical Company 1800 GAL TL 36 00:00.0 17 292147.7 No IGP Total 292147.7 Grand Total 27864797

|||

If using SQL Server 2005, have you looked at COMPUTE?

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

Dan

|||Thanks.|||I hope it does what you need!