Create an Excel Sheet with parameters

On rendering Excel, i want to make a new sheet which contains the RS
parameters of the rendering.
So, the end user get an XLS data and the input parameters.
Is there any way to make this "parameters" sheet ?
Thanks
AntoineTry putting your parameters on a rectangle, in the body of the report, not
the header. Then go to the properties on the rectangle, indicate to have a
page break before or after the object. when the report display in your
browser, the first page will just be any headers and your parameters but,
when you export to excel, a separate tab will have been created for your
parameters. For the online view, you probably will want to indicate that the
rectangle should repeat based on your detailed data area. Hope this helps!
--
ChrisinPA
"tonio932" wrote:
> On rendering Excel, i want to make a new sheet which contains the RS
> parameters of the rendering.
> So, the end user get an XLS data and the input parameters.
> Is there any way to make this "parameters" sheet ?
> Thanks
> Antoine
>sql

Create an email link within the report...

Hello...

I have a column of data on my report that contains an email address. Is it possible to format this so that the person running the report can just click on the link and have that action launch their email program...like Outlook?

thanks for any help

- will

Hi Will - Yes, you can use expressions to build "mailto:" URLs, and set these for the "jump to" value in the navigation tab for the field properties.

|||

Thanks for the reply and info...this worked great.

- will

Create a time dimension

Hi !
I have a question : my dataBase contains a table which have an attribute startTime.
The startTime give the day and the hour of an event.
I want to 'display' the event for a period time (one day or one week, or between to date...).
But I don't know how to do... Because if I add a dimension with this startTime, and in my report I put parameter with two date, I can only choose in the date exit in the attribut StartTime, but as I have a lot, an with a small range, I can't display all days...

I would like that the user choose a date as 27/03/07 and the report display all event in this day...
but I don't know what to do, because StartTime is as 27/03/07 10:50:42...

Can anybody help me ? Thank you !!!

In a named calculation(data source view in SSAS2005) or in a view/table(AS2000) you can try this: http://blog.mike-obrien.net/PermaLink,guid,f3363145-8753-4604-8314-855012a00400.aspx

This works with TSQL

HTH

Thomas Ivarsson

|||Hi Thomas,
Thank you for your answer...
But I have a problem not with named calculation but with the dimension, I didn't understand how to do !
As I explain last time, I have a table with a dateTime value, so I have a list of a lot of event with all this dateTime value, I want to organize them by day, So i want a time dimension that give just the day (as your answer) and which it is link with my dateTime, for example I have 5 event the 25/03/2007, 14 event the 26/03/2007...
so I don't what to do...
May I have to create a time dimension from server and my TimeDate are linked to this table ?

|||

Hello! Maybe I am wrong but I understand your problem as that you have time fragments on your date columns in the fact table and that you would like to change that?

The link I have sent you have examples that transform dates with 2007-03-29:13:30:06:01 to 2007-03-29:00:00:00 . If you build a time dimension with a datetime-column and you enter 2007-03-29 you will get 2007-03-29:00:00:00 in that column and a match between the time dimension and the fact table on dates.

Regards

Thomas Ivarsson

Counting unique entries in a SQL Statement

I have a complicated problem, and I'm new to SQL so any help would be greatly appreciated.

I am creating an export file (fixed width) that contains a breakdown of items on an invoice, and each "export file" can contain many invoices. The problem is that I need to apply an incremental "invoice" count on each line. This isn't as simple as doing a running sum of "1" on each record, because the first 5 rows may all be on the same invoice, and all rows need to be identified as being associated with "invoice 1". The next invoice will be known as "invoice 2" and again may contain many rows, all requiring "invoice 2".

Does this make sense?

EG.: I am shipping products, and the breakdown is: Vessel, Voyage, Invoice No, Product, Mark.....

SAGMIR 025 001 HEM/FIR HLF550...

SAGMIR 025 001 HEM/FIR KILN-D HLF505...

SAGMIR 025 002 HEM/FIR HLF660....

The SQL statement that produces the above is a Select query with a grouping on VES/VOY/BL_ID/PRO/MARK where the "BL_ID" indicates they are on the same invoice, but is not the incremental number I require. Complicated, i know...

Thanks in advance for anyone who can help.....if this explanation isn't clear please tell me!

Michael

Yes, this is not clear to me yet. Could you explain this a bit in detail ? There is a function in SQL Server which can produce a rank based on several columns. The example for procuding such number would be to get new running number for Order Details per order number. Is is this what you are trying to do ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Your best bet is probably a temporary table... and then join back to it.something like the following :

create table #tempinv
(
invid int identity(1,1),
bl_id int not null
)

insert into #tempinv
(
bl_id
)
select distinct BL_ID from invoices
order by BL_ID

select i.VES, i.VOY, t.invid, i.PRO, i.MARK
from invoices i
inner join #tempinv t on t.bl_id = i.BL_ID
order by i.BL_ID

drop table #tempinv

I think this is what you want....

counting rows in all tables in a database

Hej,
Has anyone a script that will produce a list of all the
tables in a database sorted by the number of rows each
table contains?
thanks,
KevinFor something simple using the estimates instead of actually counting the
rows, try something like
select name, rows from sysobjects
where type = 'U' order by rows desc
For a more costly and more accurate version, you'd have to loop through each
table, select count(*), insert the results into a temp table or table
variable, then select from the temp table..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:05c101c3d379$42411190$a401280a@.phx.gbl...
> Hej,
> Has anyone a script that will produce a list of all the
> tables in a database sorted by the number of rows each
> table contains?
> thanks,
> Kevin|||The script below will give information about the row count and space used
for each table ordered by rowcount and alphabetically. You can choose to
have it run with estimates or up-to-date rowcounts, for which you have to
uncomment DBCC UPDATEUSAGE (0) in the beginning of the script.
I have written a script that provides a lot more information about the data
in your tables, including cardinality and maximum size of the data in a
column, but that will take quite a long time to run.You can find it at:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=629
The script that is attached to this message isn't mine btw, but I can't find
back who the original author is.
--
Jacco Schalkwijk
SQL Server MVP
Script:
SET NOCOUNT ON
-- DBCC UPDATEUSAGE (0)
CREATE TABLE #TBLSize
(Tblname varchar(80),
TblRows int,
TblReserved varchar(80),
TblData varchar(80),
TblIndex_Size varchar(80),
TblUnused varchar(80))
DECLARE @.DBname varchar(80)
DECLARE @.tablename varchar(80)
SELECT @.DBname = DB_NAME(DB_ID())
PRINT 'User Table size Report for (Server / Database): ' + @.@.ServerName +
' / ' + @.DBName
PRINT ''
PRINT 'By Size Descending'
DECLARE TblName_cursor CURSOR FOR
SELECT NAME
FROM sysobjects
WHERE xType = 'U'
OPEN TblName_cursor
FETCH NEXT FROM TblName_cursor
INTO @.tablename
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO #tblSize(Tblname, TblRows, TblReserved, TblData,
TblIndex_Size, TblUnused)
EXEC Sp_SpaceUsed @.tablename
-- Get the next author.
FETCH NEXT FROM TblName_cursor
INTO @.tablename
END
CLOSE TblName_cursor
DEALLOCATE TblName_cursor
SELECT CAST(Tblname as Varchar(30)) 'Table',
CAST(TblRows as Varchar(14)) 'Row Count',
CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total Space
(KB)',
CAST(TblData as Varchar(14)) 'Data Space',
CAST(TblIndex_Size as Varchar(14)) 'Index Space',
CAST(TblUnused as Varchar(14)) 'Unused Space'
FROM #tblSize
Order by 'Total Space (KB)' Desc
PRINT ''
PRINT 'By Table Name Alphabetical'
SELECT CAST(Tblname as Varchar(30)) 'Table',
CAST(TblRows as Varchar(14)) 'Row Count',
CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total Space
(KB)',
CAST(TblData as Varchar(14)) 'Data Space',
CAST(TblIndex_Size as Varchar(14)) 'Index Space',
CAST(TblUnused as Varchar(14)) 'Unused Space'
FROM #tblSize
Order by 'Table'
DROP TABLE #TblSize
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:OPxHQI40DHA.2528@.TK2MSFTNGP10.phx.gbl...
> For something simple using the estimates instead of actually counting the
> rows, try something like
> select name, rows from sysobjects
> where type = 'U' order by rows desc
> For a more costly and more accurate version, you'd have to loop through
each
> table, select count(*), insert the results into a temp table or table
> variable, then select from the temp table..
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Kevin" <anonymous@.discussions.microsoft.com> wrote in message
> news:05c101c3d379$42411190$a401280a@.phx.gbl...
> > Hej,
> > Has anyone a script that will produce a list of all the
> > tables in a database sorted by the number of rows each
> > table contains?
> >
> > thanks,
> > Kevin
>|||"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:05c101c3d379$42411190$a401280a@.phx.gbl...
> Hej,
> Has anyone a script that will produce a list of all the
> tables in a database sorted by the number of rows each
> table contains?
> thanks,
> Kevin
The following was adapted from SP_SPACEUSED, and will also give the size in
MB.
SELECT
USER_NAME(O.UID) AS OWNER,
O.NAME AS TABLE_NAME,
ROWS = (SELECT I.ROWS FROM SYSINDEXES I WHERE I.INDID < 2 AND I.ID =O.ID),
RESERVED = (CONVERT(DEC(15),(SELECT SUM(I.RESERVED) FROM SYSINDEXES I
WHERE I.INDID IN (0, 1, 255) AND I.ID = O.ID)) / 128),
DATA = (CONVERT(DEC(15),(SELECT SUM(I.DPAGES) FROM SYSINDEXES I WHERE
I.INDID < 2 AND I.ID = O.ID)) / 128),
TEXT = (CONVERT(DEC(15),ISNULL((SELECT SUM(I.USED) FROM SYSINDEXES I
WHERE I.INDID = 255 AND I.ID = O.ID),0)) / 128),
USED = (CONVERT(DEC(15),(SELECT SUM(I.USED) FROM SYSINDEXES I WHERE
I.INDID IN (0, 1, 255) AND I.ID = O.ID)) / 128)
FROM SYSOBJECTS O
WHERE O.TYPE = 'U'
Mike Kruchten|||http://www.aspfaq.com/2428
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:05c101c3d379$42411190$a401280a@.phx.gbl...
> Hej,
> Has anyone a script that will produce a list of all the
> tables in a database sorted by the number of rows each
> table contains?
> thanks,
> Kevin

Counting query

Hi,
I have two tables: table 1 contains customer information, and table 2
contains order information.
Table 2 is updated everytime a customer orders some goods. Therefore, a
customer, for example, can appear within table 2 on, say, a total of 5
occasions.
I would like a column in table 1 that tells me how many orders the
corresponding customer has placed in total. Is there anyway of linking
table 1 with table 2 to count the total number of orders a particular
customer has made? (i.e. in the above case 5)
Thanks for your time
Paul Evans
While you can do this it is usually not a good idea. The main reason is
that now you have extra work somewhere (most likely a trigger) to keep that
value up to date and in some cases it can get out of sync. It is usually
better to simply issue a SUM or COUNT against the orders table with a WHERE
clause that filters by customer id. You would normally have an index on the
customer id and the operation would be pretty simple. If you use this value
a lot and there are not a large amount of new rows added to the Orders table
you might consider using an indexed view that sums up by customer. See more
in BOL on Indexed Views.
Andrew J. Kelly SQL MVP
"Paul Evans" <paul_evans1@.btinternet.com> wrote in message
news:uuR3pWS5EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two tables: table 1 contains customer information, and table 2
> contains order information.
> Table 2 is updated everytime a customer orders some goods. Therefore, a
> customer, for example, can appear within table 2 on, say, a total of 5
> occasions.
> I would like a column in table 1 that tells me how many orders the
> corresponding customer has placed in total. Is there anyway of linking
> table 1 with table 2 to count the total number of orders a particular
> customer has made? (i.e. in the above case 5)
> Thanks for your time
> Paul Evans
>

Counting items and returning values

I have a table of product orders. It contains a row for "platform" and I
need to return how many times each platform is listed in the DB

Example data for platform could be:
XBOX
XBOX
XBOX
PLAYSTATION
PLAYSTATION
GAMECUBE
PLAYSTATION

I'd like the data to be returned as

XBOX - 3
PLAYSTATION - 3
GAMECUBE - 1

How would I go about doing this please?if the name of the column is "name":
SELECT name, COUNT(name)
GROUP BY name

HTH,
-Cliff

"Andrew Banks" <banksy@.nojunkblueyonder.co.uk> wrote in message
news:ks%ac.351$lN4.6788392@.news-text.cableinet.net...
> I have a table of product orders. It contains a row for "platform" and I
> need to return how many times each platform is listed in the DB
> Example data for platform could be:
> XBOX
> XBOX
> XBOX
> PLAYSTATION
> PLAYSTATION
> GAMECUBE
> PLAYSTATION
> I'd like the data to be returned as
> XBOX - 3
> PLAYSTATION - 3
> GAMECUBE - 1
> How would I go about doing this please?

Counting in Self Joins

I have a view that contains a self join:
SELECT dbo.Clients1.ClientID, dbo.Clients1.AccountName,
dbo.Clients1.OwnedByClientID,
Clients1_1.AccountName AS OwnedByClientName
FROM dbo.Clients1 INNER JOIN
dbo.Clients1 AS Clients1_1 ON
dbo.Clients1.OwnedByClientID = TCSClients1_1.ClientID
How can I also include a column that would count the number of
"OwnedByClientID"s that are owned by a ClientID?
TIA,
PaulIf this is a hierarchy of unknwn depth, you will need a different
model. Google up Nested Sets for trees.|||Thanks.
Actually, at this point it is only 2 or three deep, but you are correct, I
am interested in an arbitrarily deep nesting.
...<snip>...
> If this is a hierarchy of unknwn depth, you will need a different
> model. Google up Nested Sets for trees.
>|||Paul,
In SQL Server 2005, finding this information is not
as hard as it used to be. Here's an example from
the AdventureWorks sample database that calculates
the total number of subordinate employees for each
manager. By default, recursive queries allow 100
levels of recursion before generating an error, though
this can be changed with the MAXRECURSION
option, if your hierarchy can be deeper.
with Ancestry(ManagerID,Subordinate) as (
select
ManagerID, EmployeeID
from AdventureWorks.HumanResources.Employee
union all
select
A.ManagerID,
E.EmployeeID
from AdventureWorks.HumanResources.Employee as E
join Ancestry as A
on A.Subordinate = E.ManagerID
)
select ManagerID, count(*) as subs
from Ancestry
group by ManagerID
-- Steve Kass
-- Drew University
Paul wrote:

>Thanks.
>Actually, at this point it is only 2 or three deep, but you are correct, I
>am interested in an arbitrarily deep nesting.
>...<snip>...
>
>
>

Counting Date Query

I have a table called sv_call_log
The table contains columns row_id, card_num, start_date, end_date.
This table logs calls.
The start_date is when the call begins and end_date is when the call ends.
They are both fromatted as follows EX. 2004-09-20 15:55:29.247
We currently have 4 lines avliable for people to call in out.
Does anyone know how to write a query to show when more than 3 lines are
used at the same time? The query would have to see if 3 or more start_dates
occured between a start_date and end_date. I'm on how I would
write this query or if there is even a way. Any help would be greatly
appreciated. Thank you.Having the entire table DDL would be very useful for us to provide
meaningful help. Otherwise we are just guessing...
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"tarheels4025" <tarheels4025@.discussions.microsoft.com> wrote in message
news:2EBC3CD5-95D8-49CF-B59B-49EBB78E508C@.microsoft.com...
>I have a table called sv_call_log
> The table contains columns row_id, card_num, start_date, end_date.
> This table logs calls.
> The start_date is when the call begins and end_date is when the call ends.
> They are both fromatted as follows EX. 2004-09-20 15:55:29.247
> We currently have 4 lines avliable for people to call in out.
> Does anyone know how to write a query to show when more than 3 lines are
> used at the same time? The query would have to see if 3 or more
> start_dates
> occured between a start_date and end_date. I'm on how I
> would
> write this query or if there is even a way. Any help would be greatly
> appreciated. Thank you.|||Hmm. Interesting.
Hopefully start_date and end_date are actually of datetime datatype,
and the formatting you describe is simply how they are displayed.
I think this will show you any call that starts while at least 2 other
calls are already in progress. If a fourth call starts there will be
rows for both the third and fourth.
--Each call matches itself, possibly others
SELECT X.row_id,
count(Y.row_id) as OverlapLevel,
start_date as start_overlap,
min(end_date) as end_overlap
FROM CallLog as X
JOIN CallLog as Y
ON X.start_date BETWEEN Y.start_date AND Y.end_date
GROUP BY X.row_id
HAVING count(Y.row_id) > 3
Roy Harvey
Beacon Falls, CT
On Thu, 22 Jun 2006 12:03:02 -0700, tarheels4025
<tarheels4025@.discussions.microsoft.com> wrote:

>I have a table called sv_call_log
>The table contains columns row_id, card_num, start_date, end_date.
>This table logs calls.
>The start_date is when the call begins and end_date is when the call ends.
>They are both fromatted as follows EX. 2004-09-20 15:55:29.247
>We currently have 4 lines avliable for people to call in out.
>Does anyone know how to write a query to show when more than 3 lines are
>used at the same time? The query would have to see if 3 or more start_date
s
>occured between a start_date and end_date. I'm on how I would
>write this query or if there is even a way. Any help would be greatly
>appreciated. Thank you.|||Ron,
When I run the query it throws back
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Start_Date'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'End_Date'.
"Roy Harvey" wrote:

> Hmm. Interesting.
> Hopefully start_date and end_date are actually of datetime datatype,
> and the formatting you describe is simply how they are displayed.
> I think this will show you any call that starts while at least 2 other
> calls are already in progress. If a fourth call starts there will be
> rows for both the third and fourth.
> --Each call matches itself, possibly others
> SELECT X.row_id,
> count(Y.row_id) as OverlapLevel,
> start_date as start_overlap,
> min(end_date) as end_overlap
> FROM CallLog as X
> JOIN CallLog as Y
> ON X.start_date BETWEEN Y.start_date AND Y.end_date
> GROUP BY X.row_id
> HAVING count(Y.row_id) > 3
> Roy Harvey
> Beacon Falls, CT
> On Thu, 22 Jun 2006 12:03:02 -0700, tarheels4025
> <tarheels4025@.discussions.microsoft.com> wrote:
>
>|||Sorry about that.
SELECT X.row_id,
count(Y.row_id) as OverlapLevel,
X.start_date as start_overlap,
min(Y.end_date) as end_overlap
FROM CallLog as X
JOIN CallLog as Y
ON X.start_date BETWEEN Y.start_date AND Y.end_date
GROUP BY X.row_id
HAVING count(Y.row_id) > 3
Roy
On Thu, 22 Jun 2006 12:55:02 -0700, tarheels4025
<tarheels4025@.discussions.microsoft.com> wrote:

>Ron,
>When I run the query it throws back
>Server: Msg 209, Level 16, State 1, Line 1
>Ambiguous column name 'Start_Date'.
>Server: Msg 209, Level 16, State 1, Line 1
>Ambiguous column name 'End_Date'.

CountDistinct in a Matrix Report

I have say 10 companies, each company has 2 activities against it. I build my
sql query and it contains the companyid and the activityid against each
record (20 records). I set up a matrix report to break the activities out by
month (created date) and activity type by adding Fields!ActivityID to the
detail section and this works fine. I then want another matrix underneath
that reports how many companies make up the first figure. I use
Countdistinct(Fields!CompanyID) in the detail section and add in subototals.
This report doesn't turn out as I want, the figures in the detail bit are
the same as the first report (they shoild be less), though the subtotal
reports correctly. Anyone know what I'm doing wrong.
Many ThanksSorry Ignore this, I'm being very silly!!
"Paul Whittaker" wrote:
> I have say 10 companies, each company has 2 activities against it. I build my
> sql query and it contains the companyid and the activityid against each
> record (20 records). I set up a matrix report to break the activities out by
> month (created date) and activity type by adding Fields!ActivityID to the
> detail section and this works fine. I then want another matrix underneath
> that reports how many companies make up the first figure. I use
> Countdistinct(Fields!CompanyID) in the detail section and add in subototals.
> This report doesn't turn out as I want, the figures in the detail bit are
> the same as the first report (they shoild be less), though the subtotal
> reports correctly. Anyone know what I'm doing wrong.
> Many Thanks