grant or deny access to db

I want to avoid users to connect to my dB via odbc
if the user do not use my front application.
If the user try to connect to the db via Acces, Excel or
any other, I want the db to refuse the acces.
How can I do so?
Using application roles would be one option. You can find more info on
application roles in books online.
-Sue
On Fri, 1 Oct 2004 09:32:46 -0700, "fredy"
<anonymous@.discussions.microsoft.com> wrote:

>I want to avoid users to connect to my dB via odbc
>if the user do not use my front application.
>If the user try to connect to the db via Acces, Excel or
>any other, I want the db to refuse the acces.
>How can I do so?

Grand Total Problem in Excel

I found a problem when I use Excel 2003 with MSAS 2005. The problem occurs when I use the Hide Levels feature on a row dimension and select the grand total for columns option. I noticed that when I apply filters to the selection, the Grand Total does not reflect the filtered members. If I Show Details then the Grand Total is properly displayed. However, this problem does not happen on every diminsion.

I was able to duplicate the problem using Adventure Works DW. In Excel, put the Product Categories in the Row, Source Currency Code in the Column, Sales Amount as the Data, and Filter on Weight (2.12) and Date.Calendar Year (CY 2004). Ensure that Grand Total for Columns is checked. Show Details down to the Product level; notice that the Grand Total amount is correct. On the Sub Category column, select Hide Levels. Notice that the Grand total shows the incorrect amount. If I do a Hide Levels on the Category, the Grand Total is also correct. I was wondering if this would be considered an Excel bug, a MSAS bug, or if this is the way it is.

Having traced the MDX queries generated by the Excel pivot table, and rerun them in Management Studio, they return the correct totals, regardless of which levels are hidden in the query. So some further processing seems to occur in Excel - maybe someone else knows more about this phase?|||I was tracing a similar issue in the OWC component today for a client and I can confirm that it appears to be some post processing that Excel is doing. Interestingly I found that if you turn on the "Include Hidden items in totals" button (the one just to the right of the refresh button) that it appears to have the opposite effect with this particular query and causes it to display the correct totals - really strange.|||

Hi Darren,

That strangeness may help identify the culprit - it may be the "Visual Totals" connection mode which Excel employs, when the user elects not to include hidden items in the total. So it may be an AS 2005 issue after all - try the following Adventure Works queries, the 2nd one attempts to emulate the "Visual Totals" connection mode :

-- Query submitted by Excel, which works correctly without Visual Totals mode:

SELECT NON EMPTY HIERARCHIZE( Except(AddCalculatedMembers({DrillDownLevel(
{DrillDownLevel({DrillDownLevel({[Product].[Product Categories].[All Products]})},
[Product].[Product Categories].[Category])}, [Product].[Product Categories].[Subcategory])}) ,
AddCalculatedMembers( DrillDownLevel([Product].[Product Categories].[Category].members))) )
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM [Adventure Works] WHERE ([Measures].[Sales Amount], [Product].[Weight].&[2.12])

-- Equivalent Visual Totals Query, which doesn't work correctly:

SELECT NON EMPTY VisualTotals(HIERARCHIZE( Except(AddCalculatedMembers({DrillDownLevel(
{DrillDownLevel({DrillDownLevel({[Product].[Product Categories].[All Products]})},
[Product].[Product Categories].[Category])}, [Product].[Product Categories].[Subcategory])}) ,
AddCalculatedMembers( DrillDownLevel([Product].[Product Categories].[Category].members))) ))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM [Adventure Works] WHERE ([Measures].[Sales Amount], [Product].[Weight].&[2.12])

|||

I forgot to mention that I also ran the MDX statements generated by Excel into the Management Studio.And as Deepak said, the Grand Totals are correct.

In my situation, the problem only occurs when one particular dimension is used with a certain filter.In SQL Server, I have a table called Project_d.Each record in this table also has the following fields: Grand Parent Project, Project Link, and Responsible Department.Every Project will roll up to a Grand Parent Project.So my Project dimension hierarchy is Grand Parent Project <- Project.A Project may or may not have a Project Link and/or a Responsible Department.The Project Link and Responsible Department are not defined as individual hierarchies, they just attributes under the Project level with the AttributeHierarchyVisible = True.

In Excel, I can filter on Project Link with other dimensions okay.I only seem to get the Grand Total problem when the Project is placed in the Row and the Project Link or Responsible Department is used as the filter and if I Hide Levels (hide the Grand Parent Projects).I tried putting the Project in the filter and the Project Link in the Row, but the Project Link only has one level so there is nothing to hide.

I cannot separate the Project Link and the Responsible Department from the Project SQL Server table because my fact table does not have a relationship to these values; the relationship is with the Project.I hope this extra information helps and thanks to everyone for your insight.

|||

This could be due to the fact that your attributes are related. Mosha has written a very detailed blog post on the interaction between the WHERE clause and related attributes in MDX which may explain your issue. see http://sqljunkies.com/WebLog/mosha/archive/2006/11/01/slicer_axis_interaction.aspx

If this is the issue that you are seeing, then it sounds like this might be fixed in SP2 which is due out shortly.

|||

I read Mosha’s blog and it is very informative; thank you!It does seem that I am experiencing the condition described, however, in my case the Excel generated query shows the correct information when run in Management Studio.When the pivot table is viewed in Excel, I get the following totals:

SHOW ALL Levels

CAD 10,306.80

EUR 2,576.70

GBP 5,153.40

USD 57,546.30

HIDE Levels (Only Product level visible)

AUD 3,301,776.20

CAD 3,053,884.94

EUR 1,031,955.40

GBP 2,483,938.44

USD 15,937,407.35

Below are the queries generated by Excel.I see the Hide Levels contain the Except statement so maybe there’s hope that SP 2 will address the problem.Thanks again!

SHOW ALL Levels

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Source Currency].[Source Currency Code].[All Source Currencies]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({DrillDownLevel({DrillDownLevel({[Product].[Product Categories].[All]})}, [Product].[Product Categories].[Category])}, [Product].[Product Categories].[Subcategory])})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWSFROM [Adventure Works] WHERE ([Measures].[Sales Amount], [Product].[Weight].&[2.12], [Date].[Calendar Year].&[2004])

HIDE Levels

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Source Currency].[Source Currency Code].[All Source Currencies]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY HIERARCHIZE( Except(AddCalculatedMembers({DrillDownLevel({DrillDownLevel({DrillDownLevel({[Product].[Product Categories].[All]})}, [Product].[Product Categories].[Category])}, [Product].[Product Categories].[Subcategory])}) , AddCalculatedMembers( DrillDownLevel([Product].[Product Categories].[Category].members))) ) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWSFROM [Adventure Works] WHERE ([Measures].[Sales Amount], [Product].[Weight].&[2.12], [Date].[Calendar Year].&[2004])|||

Darren - you are absolutely right - the root cause here is the fact that related attributes from Product appear both in WHERE and in the axis - which is exactly the example I used in the blog you cited - http://sqljunkies.com/WebLog/mosha/archive/2006/11/01/slicer_axis_interaction.aspx . I don't beleive Visual Totals play any role here. I confirmed that with SP2, the following query gives correct results:

SELECT NON EMPTY VISUALTOTALS(HIERARCHIZE( Except(AddCalculatedMembers({DrillDownLevel(

{DrillDownLevel({DrillDownLevel({[Product].[Product Categories].[All Products]})},

[Product].[Product Categories].[Category])}, [Product].[Product Categories].[Subcategory])}) ,

AddCalculatedMembers( DrillDownLevel([Product].[Product Categories].[Category].members))) ))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS

FROM [Adventure Works] WHERE ([Measures].[Sales Amount], [Product].[Weight].&[2.12])

HTH,

Mosha (http://www.mosha.com/msolap)

Grand Total Problem in Excel

I found a problem when I use Excel 2003 with MSAS 2005. The problem occurs when I use the Hide Levels feature on a row dimension and select the grand total for columns option. I noticed that when I apply filters to the selection, the Grand Total does not reflect the filtered members. If I Show Details then the Grand Total is properly displayed. However, this problem does not happen on every diminsion.

I was able to duplicate the problem using Adventure Works DW. In Excel, put the Product Categories in the Row, Source Currency Code in the Column, Sales Amount as the Data, and Filter on Weight (2.12) and Date.Calendar Year (CY 2004). Ensure that Grand Total for Columns is checked. Show Details down to the Product level; notice that the Grand Total amount is correct. On the Sub Category column, select Hide Levels. Notice that the Grand total shows the incorrect amount. If I do a Hide Levels on the Category, the Grand Total is also correct. I was wondering if this would be considered an Excel bug, a MSAS bug, or if this is the way it is.

Having traced the MDX queries generated by the Excel pivot table, and rerun them in Management Studio, they return the correct totals, regardless of which levels are hidden in the query. So some further processing seems to occur in Excel - maybe someone else knows more about this phase?|||I was tracing a similar issue in the OWC component today for a client and I can confirm that it appears to be some post processing that Excel is doing. Interestingly I found that if you turn on the "Include Hidden items in totals" button (the one just to the right of the refresh button) that it appears to have the opposite effect with this particular query and causes it to display the correct totals - really strange.|||

Hi Darren,

That strangeness may help identify the culprit - it may be the "Visual Totals" connection mode which Excel employs, when the user elects not to include hidden items in the total. So it may be an AS 2005 issue after all - try the following Adventure Works queries, the 2nd one attempts to emulate the "Visual Totals" connection mode :

-- Query submitted by Excel, which works correctly without Visual Totals mode:

SELECT NON EMPTY HIERARCHIZE( Except(AddCalculatedMembers({DrillDownLevel(
{DrillDownLevel({DrillDownLevel({[Product].[Product Categories].[All Products]})},
[Product].[Product Categories].[Category])}, [Product].[Product Categories].[Subcategory])}) ,
AddCalculatedMembers( DrillDownLevel([Product].[Product Categories].[Category].members))) )
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM [Adventure Works] WHERE ([Measures].[Sales Amount], [Product].[Weight].&[2.12])

-- Equivalent Visual Totals Query, which doesn't work correctly:

SELECT NON EMPTY VisualTotals(HIERARCHIZE( Except(AddCalculatedMembers({DrillDownLevel(
{DrillDownLevel({DrillDownLevel({[Product].[Product Categories].[All Products]})},
[Product].[Product Categories].[Category])}, [Product].[Product Categories].[Subcategory])}) ,
AddCalculatedMembers( DrillDownLevel([Product].[Product Categories].[Category].members))) ))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM [Adventure Works] WHERE ([Measures].[Sales Amount], [Product].[Weight].&[2.12])

|||

I forgot to mention that I also ran the MDX statements generated by Excel into the Management Studio.And as Deepak said, the Grand Totals are correct.

In my situation, the problem only occurs when one particular dimension is used with a certain filter.In SQL Server, I have a table called Project_d.Each record in this table also has the following fields: Grand Parent Project, Project Link, and Responsible Department.Every Project will roll up to a Grand Parent Project.So my Project dimension hierarchy is Grand Parent Project <- Project.A Project may or may not have a Project Link and/or a Responsible Department.The Project Link and Responsible Department are not defined as individual hierarchies, they just attributes under the Project level with the AttributeHierarchyVisible = True.

In Excel, I can filter on Project Link with other dimensions okay.I only seem to get the Grand Total problem when the Project is placed in the Row and the Project Link or Responsible Department is used as the filter and if I Hide Levels (hide the Grand Parent Projects).I tried putting the Project in the filter and the Project Link in the Row, but the Project Link only has one level so there is nothing to hide.

I cannot separate the Project Link and the Responsible Department from the Project SQL Server table because my fact table does not have a relationship to these values; the relationship is with the Project.I hope this extra information helps and thanks to everyone for your insight.

|||

This could be due to the fact that your attributes are related. Mosha has written a very detailed blog post on the interaction between the WHERE clause and related attributes in MDX which may explain your issue. see http://sqljunkies.com/WebLog/mosha/archive/2006/11/01/slicer_axis_interaction.aspx

If this is the issue that you are seeing, then it sounds like this might be fixed in SP2 which is due out shortly.

|||

I read Mosha’s blog and it is very informative; thank you!It does seem that I am experiencing the condition described, however, in my case the Excel generated query shows the correct information when run in Management Studio.When the pivot table is viewed in Excel, I get the following totals:

SHOW ALL Levels

CAD 10,306.80

EUR 2,576.70

GBP 5,153.40

USD 57,546.30

HIDE Levels (Only Product level visible)

AUD 3,301,776.20

CAD 3,053,884.94

EUR 1,031,955.40

GBP 2,483,938.44

USD 15,937,407.35

Below are the queries generated by Excel.I see the Hide Levels contain the Except statement so maybe there’s hope that SP 2 will address the problem.Thanks again!

SHOW ALL Levels

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Source Currency].[Source Currency Code].[All Source Currencies]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({DrillDownLevel({DrillDownLevel({[Product].[Product Categories].[All]})}, [Product].[Product Categories].[Category])}, [Product].[Product Categories].[Subcategory])})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWSFROM [Adventure Works] WHERE ([Measures].[Sales Amount], [Product].[Weight].&[2.12], [Date].[Calendar Year].&[2004])

HIDE Levels

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Source Currency].[Source Currency Code].[All Source Currencies]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY HIERARCHIZE( Except(AddCalculatedMembers({DrillDownLevel({DrillDownLevel({DrillDownLevel({[Product].[Product Categories].[All]})}, [Product].[Product Categories].[Category])}, [Product].[Product Categories].[Subcategory])}) , AddCalculatedMembers( DrillDownLevel([Product].[Product Categories].[Category].members))) ) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWSFROM [Adventure Works] WHERE ([Measures].[Sales Amount], [Product].[Weight].&[2.12], [Date].[Calendar Year].&[2004])|||

Darren - you are absolutely right - the root cause here is the fact that related attributes from Product appear both in WHERE and in the axis - which is exactly the example I used in the blog you cited - http://sqljunkies.com/WebLog/mosha/archive/2006/11/01/slicer_axis_interaction.aspx . I don't beleive Visual Totals play any role here. I confirmed that with SP2, the following query gives correct results:

SELECT NON EMPTY VISUALTOTALS(HIERARCHIZE( Except(AddCalculatedMembers({DrillDownLevel(

{DrillDownLevel({DrillDownLevel({[Product].[Product Categories].[All Products]})},

[Product].[Product Categories].[Category])}, [Product].[Product Categories].[Subcategory])}) ,

AddCalculatedMembers( DrillDownLevel([Product].[Product Categories].[Category].members))) ))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS

FROM [Adventure Works] WHERE ([Measures].[Sales Amount], [Product].[Weight].&[2.12])

HTH,

Mosha (http://www.mosha.com/msolap)

Grand total data drill through in Excel 2007 pivot table

Hi

I'm trying to deliver a flexible customer dashboard for a client using OLAP Analysis services (2000 not 2005) and Excel 2007. Everything works great until i try to drill through to the detail data behind the 'Grand Total' figure for a filtered set of data. I've found a Microsoft article that tells me this is not possible in Excel 2003 - does anyone know if it should work in Excel 2007 (I suspect I'll be disappointed and find that it won't) and has anyone come up with a solution to the issue ? Any help would gratefully appreciated before I go and write a series of macros to get each set of detail records into individual worksheets before consolidating them into one big worksheet.

Any advice gratefully received

dave

Hello! I suspect that the grand total is a calculated member and drill through is not supported on calculated members.

Regards

Thomas Ivarsson

Good Question

I have some Lotus Notes files that I saved as Excel spreadsheets. I migrated
the files to SQL using DTS. One of the files has a table with 2 columns as
follows:
Room ID Number of Beds
201 3
202 2
I need to add a column to that table that will look into the combination of
each RoomID and Number Of Beds and will look like this:
Room ID
201A
201B
201C
202A
202B
So, because Room 201 has a capacity of 3 beds, it showed up as 201A, 201B
and 201C and so forth for the other rooms. What code I can use to accomplish
this?
Thanks a million
--
TSUse an auxiliary numbers table.
Example:
use northwind
go
create table t1 (
room_id int not null unique,
number_of_beds int not null check (number_of_beds between 1 and 5)
)
go
insert into t1 values(201, 3)
insert into t1 values(202, 2)
go
select
identity(int, 1, 1) as number
into
number
from
sysobjects as a
cross join
sysobjects as b
go
declare @.s varchar(255)
set @.s = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
select
ltrim(room_id) + substring(@.s, n.number, 1)
from
t1
inner join
number as n
on n.number <= t1.number_of_beds
order by
room_id,
n.number
go
drop table t1
go
drop table number
go
AMB
"TS" wrote:

> I have some Lotus Notes files that I saved as Excel spreadsheets. I migrat
ed
> the files to SQL using DTS. One of the files has a table with 2 columns as
> follows:
> Room ID Number of Beds
> 201 3
> 202 2
> I need to add a column to that table that will look into the combination o
f
> each RoomID and Number Of Beds and will look like this:
> Room ID
> 201A
> 201B
> 201C
> 202A
> 202B
> So, because Room 201 has a capacity of 3 beds, it showed up as 201A, 201B
> and 201C and so forth for the other rooms. What code I can use to accompli
sh
> this?
> Thanks a million
> --
> TS|||Hello TS,
CREATE TABLE [dbo].[Rooms] (
[RoomID] [int] NULL ,
[NumberofBeds] [int] NULL
) ON [PRIMARY]
GO
select * from Rooms
GO
RoomID NumberofBeds
-- --
200 3
201 4
202 2
select A.RoomID, B.Division
from Rooms A
Inner Join ( Select 'A' 'Division',1 'Sequence'
UNION ALL
select 'B',2
UNION ALL
select 'C',3
UNION ALL
select 'D',4
UNION ALL
select 'E',5
UNION ALL
select 'F',6 ) B
ON B.Sequence <= A.NumberofBeds
Thanks,
Gopi
"TS" <TS@.discussions.microsoft.com> wrote in message
news:07AF5AB5-BFD0-4E5D-90CE-316F267DAF4B@.microsoft.com...
>I have some Lotus Notes files that I saved as Excel spreadsheets. I
>migrated
> the files to SQL using DTS. One of the files has a table with 2 columns as
> follows:
> Room ID Number of Beds
> 201 3
> 202 2
> I need to add a column to that table that will look into the combination
> of
> each RoomID and Number Of Beds and will look like this:
> Room ID
> 201A
> 201B
> 201C
> 202A
> 202B
> So, because Room 201 has a capacity of 3 beds, it showed up as 201A, 201B
> and 201C and so forth for the other rooms. What code I can use to
> accomplish
> this?
> Thanks a million
> --
> TS|||Here is an example of creating a table with the two fields and populating th
e
standard values. Then modifying the table to add new column and updating th
e
value of that column depending on the number of rooms.
Create table #Table1
(
RoomID nvarchar (3),
NumOfBeds nvarchar (1)
)
Insert #Table1
values ('201','3')
Insert #Table1
values ('202','2')
Insert #Table1
values ('203','3')
Insert #Table1
values ('204','2')
select * from #table1
--Alter table to have GUID ID
Alter TABLE [#table1] ADD [RoomType] nvarchar (4)
GO
-- Update values for colum with conditions for number of beds
Update #Table1
Set Roomtype = RoomID+'A'
WHERE Numofbeds ='3'
Update #Table1
Set Roomtype = RoomID+'B'
WHERE Numofbeds ='2'
Select * from #Table1
Drop table #table1
Hope this helps guide you in the right direction.
"TS" wrote:

> I have some Lotus Notes files that I saved as Excel spreadsheets. I migrat
ed
> the files to SQL using DTS. One of the files has a table with 2 columns as
> follows:
> Room ID Number of Beds
> 201 3
> 202 2
> I need to add a column to that table that will look into the combination o
f
> each RoomID and Number Of Beds and will look like this:
> Room ID
> 201A
> 201B
> 201C
> 202A
> 202B
> So, because Room 201 has a capacity of 3 beds, it showed up as 201A, 201B
> and 201C and so forth for the other rooms. What code I can use to accompli
sh
> this?
> Thanks a million
> --
> TS|||Thanks a lot. Your code did exactly what I was looking for. Now the only
thing I need in order to finish the conversion is to include the description
next to the room id as follows:-
This is how the table looked like before applying your code:
Room Capacity Description
201 2 Small Single
202 1 Large Double
This is how the table looks like now after applying your code
RoomID
201A
201B
202A
What I need is to add another column to what I have now so the table will
look like this
RoomID Description
201A Small Single
201B Small Single
202A Large Double
What is the code for that.
Thank you for all your help.
TS
"Alejandro Mesa" wrote:
> Use an auxiliary numbers table.
> Example:
> use northwind
> go
> create table t1 (
> room_id int not null unique,
> number_of_beds int not null check (number_of_beds between 1 and 5)
> )
> go
> insert into t1 values(201, 3)
> insert into t1 values(202, 2)
> go
> select
> identity(int, 1, 1) as number
> into
> number
> from
> sysobjects as a
> cross join
> sysobjects as b
> go
> declare @.s varchar(255)
> set @.s = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
> select
> ltrim(room_id) + substring(@.s, n.number, 1)
> from
> t1
> inner join
> number as n
> on n.number <= t1.number_of_beds
> order by
> room_id,
> n.number
> go
> drop table t1
> go
> drop table number
> go
>
> AMB
>
> "TS" wrote:
>

GoalSeek in SQL Server?

Has anyone create a GoalS function similar to that of Excel? I've
been researching this and trying to script one out myself without much
sucess. If someone could tell me that its impossible, that would be
helpful too. TIA.This is not really what SQL is used for.|||You may be able to do it in MDX using multiple-pass calculations to iterate
to the required result:
http://msdn.microsoft.com/library/d...
anced_6jn7.asp
In pure SQL you could perhaps do it without iteration if the range of
possible values is small enough:
SELECT TOP 1 N1.num, N2.num
FROM some_numbers AS N1, some_numbers AS N2
WHERE (N1.num + (N2.num/2) - 123) = 12345
ORDER BY N1.num, N2.num
David Portas
SQL Server MVP
--|||I have used SQL to solve number puzzles, just for fun (eh!?). Try the
following script.
-- Easy example, all combinations of the numbers 1-9 which add up to 11
DECLARE @.control TABLE ( control_no TINYINT PRIMARY KEY, control_str AS CAST
(control_no AS CHAR(1) ) )
DECLARE @.i INT
SET NOCOUNT ON
SET @.i = 0
-- Add control numbers to temp table
WHILE @.i Between 0 And 9
BEGIN
INSERT @.control VALUES( @.i )
SET @.i = @.i + 1
END
SET NOCOUNT OFF
-- List all combinations which add up to 11
SELECT a.control_no, b.control_no, a.control_no + b.control_no
FROM @.control a, @.control b
WHERE a.control_no + b.control_no = 11
-- Harder example; MENSA-type substitution puzzle
-- Mensa's Number Puzzles for Math Geniuses by Harold Gale has this puzzle:
--
-- Number Puzzle 58
-- Place six three digit numbers of 100 plus at the end of 685
-- so that six numbers of six digits are produced [like 685123].
-- When each number is divided by 111 six whole numbers can be found.
SELECT
a.control_str,
b.control_str,
c.control_str,
a.control_str + b.control_str + c.control_str AS result,
'685' + a.control_str + b.control_str + c.control_str AS result2,
CAST( '685' + a.control_str + b.control_str + c.control_str AS DECIMAL ) /
111 AS result2
FROM @.control a, @.control b, @.control c
WHERE a.control_str + b.control_str + c.control_str > 100
AND ( CAST( '685' + a.control_str + b.control_str + c.control_str AS
DECIMAL ) / 111 ) =
CAST ( CAST( '685' + a.control_str + b.control_str + c.control_str AS
DECIMAL ) / 111 AS INT )
ORDER BY 1
-- This puzzle is similar to ones like this:
-- How many three digit numbers are divisible by 17?
-- Well, the first one is greater than 100. 100/17=5.88235. . . So the first
is 6x17=102. We don't need to list these three digit numbers. The last one i
s
less than 1000. 1000/17=58.8235. . . So our three digit numbers are 6x17,
7x17, 8x17, . . ., 58x17. There are 53 such numbers.
SELECT
a.control_str,
b.control_str,
c.control_str,
a.control_str + b.control_str + c.control_str AS result,
CAST( a.control_str + b.control_str + c.control_str AS DECIMAL ) / 17 AS
result2
FROM @.control a, @.control b, @.control c
WHERE a.control_str + b.control_str + c.control_str > 100
AND ( CAST( a.control_str + b.control_str + c.control_str AS DECIMAL ) /
17 ) =
CAST ( CAST( a.control_str + b.control_str + c.control_str AS DECIMAL ) /
17 AS INT )
ORDER BY 1
-- etc
Obviously the code above is quick and dirty, but you see where I'm going.
If not, look up 'Cartesian products'. Don't go using it in production
environment!
Let me know hot you get on.
Damien
"carmaboy@.gmail.com" wrote:

> Has anyone create a GoalS function similar to that of Excel? I've
> been researching this and trying to script one out myself without much
> sucess. If someone could tell me that its impossible, that would be
> helpful too. TIA.
>|||Did you notice that you wind up generating all possible combinations|||Yes, I'm using the cartesian product to get the answers to the puzzles. Is
that what you mean? Presumably there are other ways to do this, but this wa
s
just a demo, and hey it works!
Damien
"--CELKO--" wrote:

> Did you notice that you wind up generating all possible combinations
>|||Perhaps Joe's point was that declarative SQL solutions generally will
produce the total set of results, effort that is highly redundant if
all solutions are equal. What you probably wanted was just the fastest
single solution. That's why SQL is likely a heavily inefficient
solution - ok for solving recreational problems but in a commercial
environment you'll probably be better off with a procedural language or
a math/stats package.
David Portas
SQL Server MVP
--|||>> Perhaps Joe's point was that declarative SQL solutions generally will pro
duce the total set of results, <<
In fact, I just a did a short piece for DBAzine with a simple bin
packing problem (one bin, n-items) to demonstrate how the answer space
keeps doubling.

Giving understandable name to excel file when exporting with RS

Hello every body,

I'm trying to export excel file on with reporting services. My file has about 50 sheets and reporting services gives them the default name "feuille1, feuille2, feuille3, feuille4 etc....). I would like to give a valid and understandable name to that sheets. In fact i would like to give the name of each page to my sheet. The name of the page was specified in the report query.

Could someone help me please...

Thanx

I do not think that is configurable but you can try asking that question on the SQL Server Reporting services forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=82&SiteID=1

Even though it might not be the most fun task, you could always manually rename those sheets to have more meaningful names atfer you exported the report.

Good luck!
Othmane