How to get total page count for individual groups

Hello,

I have report in which I have created groups base on the customer name. Can anybody please tell me how to get the total page counts for the individual group? I have page break after every new group and I am able to reset page count to 1 when new group start but I am getting the total number of pages for a particular group.

For ex, let say I have 4 groups, 1 group has 3 page, 2 group has 2 pages and 3 group has 6 page and 4th group has 7 pages I need something like,

For 1st group

'Page 1 of 3’ when user click next page it should be 'Page 2 of 3' etc

Similarly for other groups as wll.

Thanks!

Hello,

Brian Welcker has a blog post that covers this:

http://blogs.msdn.com/bwelcker/archive/2005/05/19/420046.aspx

but you will want to modify his code slightly to use a hash table as he notes at the end.

Let me know if you need assistance implementing this solution or if this doesn't solve your issue.

Larry

|||

Hi Larry,

Thanks a lot for the reply.

I am able to implement the code but I am not able to get the total page count for the individual group.

Let say there are total 15 Pages in the report and there are 3 groups with page break then I need something like

For Group 1 paging would be -> Page 1 of 3, Page 2 of 3.....etc

For Group 1 paging would be -> Page 1 of 5, Page 2 of 5, Page 3 of 5...etc

For Group 1 paging would be -> Page 1 of 7

Currently I am getting like

Group 1 - Page1 of 15, Page 2 of 15...ect

Group 2 - Page 1 of 15, Page 2 of 15..etc

Group 3 - Page 1 of 15, Page 2 of 15..etc

Thanks

|||

I see. I didn't fully read your original post. Take a look at the following thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1807270&SiteID=1

where Lisa posts a method of doing what you want. It requires some external code as the pages in a report are rendered in a single pass and it would require two passes (one to count and one to update) to accomplish what you are wanting.

Larry

how to get total number of variables using scripttask

I want to loop thru all the variables in my package and set number of variables that had variable-name begin w/LOCAL, so I can use the name to generate a dynamic SQL query for the next EXEC SQL Task. any one know how to do this.

Use the count property on the variables collection.|||

that is what I did, Dts.Varaiables.count, but it did not return the right numbers. I have over 10 variables and it return 1.

|||

Have you added your 10+ variables to either the ReadOnlyVariables or ReadWriteVariables list? The Dts.Variables collection is generated based on those preset list of variables.

Thanks,
Patrik

|||

no, I did not.

OK, let me back out a little bit, I try to use the event handler as a trigger for my custom audit logging, for each task when I am done, I want to use a script to find out what is in my variables pool, loop thru them and build a query, (“select count(*) from ‘[“ + @.variable1), and then the next execute SQL task will execute this and return the count to a variable so I can have the OnPostExecute event write out the record counts, so I only have 1 variable “MySQL” in the ReadWriteVariables list, since what I want is when I create a new package, all I have to do is to add or remove local variables, no code changes is need it. if I have to put in all the variables, then I defeat my intention, is there a work around for this? Any suggestion is greatly appreciated.

|||

What sort of processing are you trying to achieve with the Script Task? Which variables are you using and looking for in the loop? Is the collection of variables you're interested in always the same? If you don't fill in the ReadOnlyVariables and ReadWriteVariables properties you can do the work using the VariableDispenser (http://msdn2.microsoft.com/de-de/library/microsoft.sqlserver.dts.runtime.variabledispenser.aspx); however, you would still need to know the name of the variables you're interested in reading.

If you're using the Script Task just to create a SQL command statement for the Execute SQL Task you could look at using expressions (http://msdn2.microsoft.com/en-us/library/ms141214.aspx).

Cheers,
Patrik

|||

Hi..

You can read all variables by using ActiveX Script Task instead of using Script Task.

Function Main()
Dim oVal

Dim isSystem, varType, varName
Dim str
MSGBOX "Total Variable = " & DTSGlobalVariables.Count

For each oVal in DTSGlobalVariables
isSystem = oVal.SystemVariable
varType = oVal.DataType
varName = oVal.QualifiedName

MSGBOX varName & " / " & varType & " / " & "System:" & isSystem

Next
Main = DTSTaskExecResult_Success

End Function

HTH

ADConsulting / SQLLeader.com / Daeseong Han

How to get the total time of the records in a table

Hello,

I have one table that has a column called CallDuration. This columns has always the format "1/01/2000 12:01:38 AM". The date part "1/01/2000" I want to discard, and sum the time part to get a total time in my query. How can I do that?

Thxselect datediff(hour,'1/01/2000 12:01:38 AM', '1/01/2000 11:10:38 AM') Hours,
datediff(minute,'1/01/2000 12:01:38 AM', '1/01/2000 11:10:38 AM') % 60 Minutes,
datediff(minute,'1/01/2000 12:01:38 AM', '1/01/2000 11:10:38 AM') TotalMinutes|||select datediff(hour,min(callduration), max(callduration)) Hours,
datediff(minute,min(callduration), max(callduration)) % 60 Minutes,
datediff(minute,min(callduration), max(callduration)) TotalMinutes

How to get overall total and total of specific instance?

I'm pretty new to SSAS, so I looked through the forum to see if this had been answered. I couldn't find it, but if its here please let me know.

I have a simple fact table of vendors and products purchased: Vendor, Part, Qty

Multiple Vendors can carry the same Part. I want to display the total Qty over all Vendors and the total Qty by a specific vendor for a specific Part purchased.

Example:

Vendor Part Qty TotalQTY

001 5x 3 10

002 4c 1 8

003 4c 7 8

004 6q 9 15

001 6q 6 15

What would be the best way to go about this?

Thanks in advance!

This 1st row of sample data is confusing, because it seems to be the only row for Part 5x; but maybe a row is missing:

Vendor Part Qty TotalQTY

001 5x 3 10

Anyway, assuming that there are Vendor and Part dimensions and a [Qty] "sum" measure, [TotalQty] could simply be like:

([Measures].[Qty], [Vendor].[Vendor].[All Vendors])

|||Thank you very much! I can see why you are an MVP.

how to get disk total space info?

hi all,
I know that master..xp_fixeddrives can give you the free space info for each
drive on your sql server.
but is there any proc that can return the total disk space?
also, anyone know how to see the code inside of master..xp_fixeddrives?
many thanks!!
JJI don;t knoiw of any way for SQL t odo that... But...
Don't you know how big the disks are ?
These values will not change often.
Create a table and put this data in it...
"JJ Wang" wrote:

> hi all,
> I know that master..xp_fixeddrives can give you the free space info for ea
ch
> drive on your sql server.
> but is there any proc that can return the total disk space?
> also, anyone know how to see the code inside of master..xp_fixeddrives?
> many thanks!!
> JJ|||xp_fixeddrives is an extended stored proc. written in C/C++ and compiled.
It most likely calls the GetDiskFreeSpaceEx API function to get the free
space. You could always write your own extended stored proc to make the
same call, but utilize the other information returned like total disk size.
Here's more info. on that:
http://support.microsoft.com/defaul...b;en-us;231497.
It might be easier just to do it from your client app - most languages have
easier ways to get at this particular info., and I think I read that SQL
2005 will also offer easier methods to access this - but someone else will
need to give you specifics on that.
"JJ Wang" <JJ Wang@.discussions.microsoft.com> wrote in message
news:B388A868-9058-45D9-A99F-38BD00B8B0B4@.microsoft.com...
> hi all,
> I know that master..xp_fixeddrives can give you the free space info for
> each
> drive on your sql server.
> but is there any proc that can return the total disk space?
> also, anyone know how to see the code inside of master..xp_fixeddrives?
> many thanks!!
> JJ|||CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
/* Returns the total capacity for any drive letter */
RETURNS NUMERIC(20)
BEGIN
DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13),
@.drv INTEGER, @.drivesize VARCHAR(20)
SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
IF @.rs<> 0
SET @.drivesize = NULL
EXEC sp_OADestroy @.drv
EXEC sp_OADestroy @.fso
RETURN @.drivesize
END
GO
SELECT dbo.GetDriveSize('C')
David Portas
SQL Server MVP
--|||thank you guys so much for all your useful tips!!
Dave,
thank you so much for the function of yours, it works like a charm!!
have you been using this in your production servers? is there anything I
should look out in this function?
many thanks!!
JJ
"JJ Wang" wrote:

> hi all,
> I know that master..xp_fixeddrives can give you the free space info for ea
ch
> drive on your sql server.
> but is there any proc that can return the total disk space?
> also, anyone know how to see the code inside of master..xp_fixeddrives?
> many thanks!!
> JJ|||I have used this in production - not as something to be called
regularly but just as required to refresh a table of drive sizes. I
agree with CBretana that it makes sense to retain this information in a
table but in a large SAN environment with many drive arrays (we were
managing about 15 terabytes across 6 servers) it can be a difficult to
keep that information accurate and up-to-date.
You need to be wary about memory and resource leaks when calling COM
objects with sp_OACreate. I haven't had problems with this one (that's
under SP3, which I assume you are at) but do test it out in your
environment.
David Portas
SQL Server MVP
--|||hi Dave,
thanks again for the wonderful info!
yes, our sql servers are at the highest sp.
how long have you been using this function on your prod servers? and how
often do you refresh your drive size table? Sounds like 'daily' is a bad
idea to run the com objects, huh? :-)
how much memory and cpu in avg do your servers have(which you run this
function on)?
thanks Dave!
JJ
"David Portas" wrote:

> I have used this in production - not as something to be called
> regularly but just as required to refresh a table of drive sizes. I
> agree with CBretana that it makes sense to retain this information in a
> table but in a large SAN environment with many drive arrays (we were
> managing about 15 terabytes across 6 servers) it can be a difficult to
> keep that information accurate and up-to-date.
> You need to be wary about memory and resource leaks when calling COM
> objects with sp_OACreate. I haven't had problems with this one (that's
> under SP3, which I assume you are at) but do test it out in your
> environment.
> --
> David Portas
> SQL Server MVP
> --
>