|
Try this one...
=(RowNumber(Nothing) - (RowNumber(Nothing) Mod 10)) / 10
It should produce a group number for each 10 records; it's basically the same as DivRem, but since DivRem requires a variable by reference to hold the remainder I don't know of a way to use it in SSRS. Anyhow, for records 1 - 10 this should produce 1, 11 - 20, 2, etc. RowNumber(Nothing) Mod 10 gives you the remainder, in this case the number in the ones position (if it's RowNumber 24, it will give you 4) which you then subtract from row number to give you the tens position (24 - 4 = 20) which is then divided by 10 to give the group number (20 / 10 = 2).
Keep It Simple Stupid! (KISS)
|
|
|
|
|
One other thing, I've never done this before so I cannot say for sure that you can do it in SSRS. If you cannot get it to work; add a column to your result set to hold the group number and populate it in the query that produces the data and then just group on the group number column in the report. I've had to use that approach for other complex things that I just couldn't get to work directly in the SSRS report.
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Ok thank u..I tried using tat formula..But it is displaying one record in every page...
|
|
|
|
|
Hi Guys
I Have A function to select Parent rows And child of the specific id.
my function have been worked properly but Suddenly its generate this Error :
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)
My Function Is :
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[fn_EmpRec]
(
@TemplatesID int,
@depth int
)
RETURNS @Templates TABLE
(
TemplatesID bigint,
[Name] nvarchar(50),
ParentID bigint,
depth int,
Weight float
)
AS
BEGIN
-- insert current Template into working table
INSERT INTO @Templates
SELECT TemplatesID,
[Name],
ParentID,
@depth
,Weight
FROM [Assessment].[Template]
WHERE TemplatesID = @TemplatesID
-- holding variable to keep track of current child
DECLARE @curTemplatesID int
-- get the first child
SELECT @curTemplatesID = MIN(TemplatesID)
FROM [Assessment].[Template]
WHERE ParentID = @TemplatesID
-- iterate each child and make the recursive call
WHILE @curTemplatesID IS NOT NULL
BEGIN
INSERT INTO @Templates
SELECT *
FROM dbo.fn_EmpRec(@curTemplatesID, @depth + 1)
SELECT @curTemplatesID = MIN(TemplatesID)
FROM [Assessment].[Template]
WHERE TemplatesID > @curTemplatesID AND
ParentID = @TemplatesID
END
RETURN
END
Any Sujestions?
|
|
|
|
|
It looks like the recursion amount in your function is dependent on the data, so perhaps you now have data which ends up to more than 32 recursions levels.
Use for example PRINT statement and print out different information from the function when executing it. Info like:
- @depth
- @TemplatesID
- @curTemplatesID etc.
That info might explain the situation and/or logic problems for you when you see the actual data that's being used.
|
|
|
|
|
This most definitely is the problem. The usual alternative to recursion is to introduce a stack structure. Essentially, you're using a stack with recursion: the call stack. However, when you run into these types of issues you usually have to create your own stack. It's pretty easy to do in SQL Server, a temp table will be your stack and you'll have an integer column that represents the nest level. As you go through your loop you will select from the temp table records with max nest level. If those items have children, you will create a new nest level, if not you have reached the end of that level of nesting and will work back through the earlier nest levels. Also, with a stack you usually have to pre-fill it with a single root record to get the whole process going. I've seen an article or two about doing this type of thing with Common Table Expressions, but have never tried that approach myself, but it may end up being easier that way.
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Ben Fair wrote: I've seen an article or two about doing this type of thing with Common Table Expressions, but have never tried that approach myself, but it may end up being easier that way
Yes, actually a normal tree structure is very easy to handle with CTE with or without recursion limits (configurable). If the data describes a net it's more complicated and also limited, but I don't believe that's the case in here.
|
|
|
|
|
Hi All,
OUR DB is SQL SERVER 2000 and it is in a server which having windows 2003 standard x64 edition.
and our client machine is having windows web server 2008 and we are trying to connect SQL DB for there.
But we have recieved error like below.
"
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
"
can you please help to fix the issue?
Thanks in advance.
|
|
|
|
|
The error is quite general so:
- recheck the connection string at client side (protocol, instance name, server name etc.)
- check that the server side is listening on desired protocol (also check it's settings)
- check that there are no firewalls blocking the conversation. If needed open the port in Vista firewall
|
|
|
|
|
Hi all,
I want to get concatenatenames of all rows in one column ..can any one help me.
suppose i get A
B
C AS 3 3 DIFFERENT ROWS
I WANT TO CONCATENATE GET ABC AS THE RESULT = NEW COLUMN..
CAN ANY ONE CAN HELP
|
|
|
|
|
An easy way would be writing a stored procedure, opening a cursor on the table, loop through it and while looping, concatenate the return string.
|
|
|
|
|
I hate cursors so I love this concat function
DECLARE @Text VARCHAR(8000)<br />
<br />
SET @Text = ''<br />
<br />
SELECT @Text = @Text + ',' + class<br />
FROM PFClass<br />
<br />
SELECT @Text
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Agreed......much rather use WHILE EXISTS(
Any suggestions, ideas, or 'constructive criticism' are always welcome.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
Yeah, also thought it was neat.
|
|
|
|
|
You can use COALESCE function of sql server for this purpose.
Do good and have good.
|
|
|
|
|
You reckon? How exactly?
Regards,
Rob Philpott.
|
|
|
|
|
Something like this:
create table #X
(
C char(1)
)
insert into #X values ('A')
insert into #X values ('B')
insert into #X values ('C')
declare @z as varchar(2000)
set @z = ''
select @z = @z + C from #X
select @z
drop table #x
yields: ABC
Regards,
Rob Philpott.
|
|
|
|
|
Thank u verymuch it worked fine...
|
|
|
|
|
Is it possible to call a web service from Stored Procedure (Sql Server 2000),
If so how can we achieve this?
Thanks In Advance
|
|
|
|
|
vamsimohan21 wrote: Is it possible to call a web service from Stored Procedure (Sql Server 2000)
One way could be to create a program that calls the web service and use for example xp_cmdshell to call this program.
|
|
|
|
|
Mika Wendelius wrote: xp_cmdshell
Not recommended because this opens a high-risk security hole. IMHO, xp_cmdshell should be avoided at all costs.
|
|
|
|
|
Dave Kreskowiak wrote: Not recommended because this opens a high-risk security hole
True in many cases, but with Sql Server 2000 that was the only way that came in mind.
|
|
|
|
|
Its not a Good idea. Why Dont you call a Stored a Stored Procedure from a Web Service?
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswam@its.co.za
|
|
|
|
|
I have Table1 in Db Ms Access connection to VB2005
the Table has these columns A,B,C,D,F,G,H.. the typed of H is Date/time and the Typed of D is number and other columns is Text
I tried this Sql and it succeed with me.
Select Last( F),Last( G), SUM(D), A,B,C FROM Table1 Group by A, B, C
I want to make modification on that sql by define that row not by Last, but by MAX(H), or Order By H DESC
it's mean if the Table like this:
A :: B :: C :: D :: F :: G :: H
xxx :: yyy :: zzz :: 111 :: fff :: ggg ::2 Dec 08
xxx :: yyy :: zzz :: 222 :: rrr :: sss :: 1 Dec 08
x99 :: y99 :: z99 :: 432 :: uuu :: vvv :: 3 Dec 08
the result will be like this
A :: B :: C :: D :: F :: G
xxx :: yyy :: zzz :: 333 :: fff :: ggg
x99 :: y99 :: z99 :: 432 :: uuu :: vvv
|
|
|
|
|
did you try
Order by max(H) desc
Or
name the colum max(h) DateOrder
Order by DateOrder Desc
One of them may work in Access. Make sure that H is a date field NOT varchar.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
