customized order by

I would like to some how customize my order by clause such that data gets
ordered in a very specific manner.
Please consider the following ddl
set nocount on
go
create table z_my_tbl_del
( i int,
n char(8)
)
go
insert z_my_tbl_del values(1,'aaa')
insert z_my_tbl_del values(1,'aaa')
insert z_my_tbl_del values(2,'bbb')
insert z_my_tbl_del values(2,'bbb')
insert z_my_tbl_del values(3,'ccc')
insert z_my_tbl_del values(3,'ccc')
insert z_my_tbl_del values(4,'ddd')
insert z_my_tbl_del values(4,'ddd')
insert z_my_tbl_del values(5,'eee')
insert z_my_tbl_del values(5,'eee')
go
select * from z_my_tbl_del
/*
/*
i n
-- --
1 aaa
1 aaa
2 bbb
2 bbb
3 ccc
3 ccc
4 ddd
4 ddd
5 eee
5 eee
*/
*/
go
drop table z_my_tbl_del
go
How can I write order by clause such that it orders by bbb first, then ddd
and then it would sort rest of the items regularly.
Please let me know if there is a way of doing it.
TIA...
/*
i n
-- --
2 bbb
2 bbb
4 ddd
4 ddd
1 aaa
1 aaa
3 ccc
3 ccc
5 eee
5 eee
*/ORDER BY CASE n WHEN 'bbb' THEN 1 WHEN 'ddd' THEN 2 ELSE 3 END, i
"sqlster" <trisha@.nospam.nospam> wrote in message
news:9524A826-4A16-4AED-A0E1-F7A4D9AAF4B2@.microsoft.com...
>I would like to some how customize my order by clause such that data gets
> ordered in a very specific manner.
> Please consider the following ddl
> set nocount on
> go
> create table z_my_tbl_del
> ( i int,
> n char(8)
> )
> go
> insert z_my_tbl_del values(1,'aaa')
> insert z_my_tbl_del values(1,'aaa')
> insert z_my_tbl_del values(2,'bbb')
> insert z_my_tbl_del values(2,'bbb')
> insert z_my_tbl_del values(3,'ccc')
> insert z_my_tbl_del values(3,'ccc')
> insert z_my_tbl_del values(4,'ddd')
> insert z_my_tbl_del values(4,'ddd')
> insert z_my_tbl_del values(5,'eee')
> insert z_my_tbl_del values(5,'eee')
> go
> select * from z_my_tbl_del
> /*
> /*
> i n
> -- --
> 1 aaa
> 1 aaa
> 2 bbb
> 2 bbb
> 3 ccc
> 3 ccc
> 4 ddd
> 4 ddd
> 5 eee
> 5 eee
> */
> */
> go
> drop table z_my_tbl_del
> go
> How can I write order by clause such that it orders by bbb first, then ddd
> and then it would sort rest of the items regularly.
> Please let me know if there is a way of doing it.
> TIA...
> /*
> i n
> -- --
> 2 bbb
> 2 bbb
> 4 ddd
> 4 ddd
> 1 aaa
> 1 aaa
> 3 ccc
> 3 ccc
> 5 eee
> 5 eee
> */
>|||Basically, you want 2 levels of ordering with the first level consisting of
an expression that evaluates 'bbb' and 'ddd' at the top of the list.
Modify your query like so:
select
*
from
#z_my_tbl_del
order by
case n
when 'bbb' then 1
when 'ddd' then 2
else 3
end,
n
"sqlster" <trisha@.nospam.nospam> wrote in message
news:9524A826-4A16-4AED-A0E1-F7A4D9AAF4B2@.microsoft.com...
>I would like to some how customize my order by clause such that data gets
> ordered in a very specific manner.
> Please consider the following ddl
> set nocount on
> go
> create table z_my_tbl_del
> ( i int,
> n char(8)
> )
> go
> insert z_my_tbl_del values(1,'aaa')
> insert z_my_tbl_del values(1,'aaa')
> insert z_my_tbl_del values(2,'bbb')
> insert z_my_tbl_del values(2,'bbb')
> insert z_my_tbl_del values(3,'ccc')
> insert z_my_tbl_del values(3,'ccc')
> insert z_my_tbl_del values(4,'ddd')
> insert z_my_tbl_del values(4,'ddd')
> insert z_my_tbl_del values(5,'eee')
> insert z_my_tbl_del values(5,'eee')
> go
> select * from z_my_tbl_del
> /*
> /*
> i n
> -- --
> 1 aaa
> 1 aaa
> 2 bbb
> 2 bbb
> 3 ccc
> 3 ccc
> 4 ddd
> 4 ddd
> 5 eee
> 5 eee
> */
> */
> go
> drop table z_my_tbl_del
> go
> How can I write order by clause such that it orders by bbb first, then ddd
> and then it would sort rest of the items regularly.
> Please let me know if there is a way of doing it.
> TIA...
> /*
> i n
> -- --
> 2 bbb
> 2 bbb
> 4 ddd
> 4 ddd
> 1 aaa
> 1 aaa
> 3 ccc
> 3 ccc
> 5 eee
> 5 eee
> */
>|||1) use table for the ordering
CREATE TABLE SpecialSort
(sort_order INTEGER NOT NULL,
n CHAR(3) NOT NULL);
INSERT INTO S.sort_order VALUES (1, 'bbb');
INSERT INTO S.sort_order VALUES (2, 'ddd');
INSERT INTO S.sort_order VALUES (3, 'aaa');
etc.
SELECT F.*, S.sort_order
FROM Foobar AS F, SpecialSort AS S
WHERE S.n = F.n
ORDER BY S.sort_order;
2) use a string
SELECT F.*, CHARINDEX (n, 'bbbdddaaaccceee') AS sort_order
FROM Foobar
ORDER BY sort_order;|||Why do you want to do this? Just one time? I would suggest that you add a
sort column to your actual table if you want to sort it differently
(especially since the client probably will want to change the sort
sometimes.) If it is just for certain reports, then implement a report sort
order table and you can then change the ordering at will.
create table sortOrder(
reportName varchar(10),
n varchar(8),
sortOrder int,
primary key (reportName, N),
unique (reportName, sortOrder)
)
insert into sortorder values('yours','bbb',1)
insert into sortorder values('yours','ddd',2)
select z_my_tbl_del.*
from z_my_tbl_del
left outer join sortorder
on z_my_tbl_del.n = sortOrder.n
and sortOrder.reportName = 'yours'
order by coalesce(sortorder.sortOrder,2000000000) asc, z_my_tbl_del.n
insert into sortorder values('yours','bbb',1)
insert into sortorder values('yours','ddd',2)
insert into sortorder values('mine','eee',1)
insert into sortorder values('mine','ddd',2)
insert into sortorder values('mine','aaa',3)
select z_my_tbl_del.*
from z_my_tbl_del
left outer join sortorder
on z_my_tbl_del.n = sortOrder.n
and sortOrder.reportName = 'mine'
order by coalesce(sortorder.sortOrder,2000000000) asc, z_my_tbl_del.n
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"sqlster" <trisha@.nospam.nospam> wrote in message
news:9524A826-4A16-4AED-A0E1-F7A4D9AAF4B2@.microsoft.com...
>I would like to some how customize my order by clause such that data gets
> ordered in a very specific manner.
> Please consider the following ddl
> set nocount on
> go
> create table z_my_tbl_del
> ( i int,
> n char(8)
> )
> go
> insert z_my_tbl_del values(1,'aaa')
> insert z_my_tbl_del values(1,'aaa')
> insert z_my_tbl_del values(2,'bbb')
> insert z_my_tbl_del values(2,'bbb')
> insert z_my_tbl_del values(3,'ccc')
> insert z_my_tbl_del values(3,'ccc')
> insert z_my_tbl_del values(4,'ddd')
> insert z_my_tbl_del values(4,'ddd')
> insert z_my_tbl_del values(5,'eee')
> insert z_my_tbl_del values(5,'eee')
> go
> select * from z_my_tbl_del
> /*
> /*
> i n
> -- --
> 1 aaa
> 1 aaa
> 2 bbb
> 2 bbb
> 3 ccc
> 3 ccc
> 4 ddd
> 4 ddd
> 5 eee
> 5 eee
> */
> */
> go
> drop table z_my_tbl_del
> go
> How can I write order by clause such that it orders by bbb first, then ddd
> and then it would sort rest of the items regularly.
> Please let me know if there is a way of doing it.
> TIA...
> /*
> i n
> -- --
> 2 bbb
> 2 bbb
> 4 ddd
> 4 ddd
> 1 aaa
> 1 aaa
> 3 ccc
> 3 ccc
> 5 eee
> 5 eee
> */
>

Customization at runtime

I have a report which i am using in vb6.

it has following
itemid, itemname,price

but i want to give user choice to change this order at runtime,
according to him like
itemname,itemid,price or it can be any combination.

Ideas will be appreciated.
Thanks in AdvanceHi,
yes you could but not Only using CR but VB + SQL manupulating to suit u r requirement

the SQL which brings u data has to be
select Itemid as Filed1, itemname as Filed2 , price as Filed3

ie u let the user decided the orer he wants (in VB he decides through a ordering using selecting from a Left Listbox to right Listbox)

the according to that u Generate the SQL

select Itemid as Filed1, itemname as Filed2 , price as Filed3
Or
select itemname as Filed1, Itemid as Filed2 , price as Filed3
Or
......(u can simply do this depending on the use selection)

but in crystal Report use a 'Field Definitions Only' in (Database Expert --> create new connection ) then create a .ttx file with 3 field of type String with name Filed1, Filed2, Filed3 then place them in report in order

in VB set the DS value got from SQL to this report the

Note : the Order is Truely dynamic in SQL but user feels like the rpt is, but u have created only one Rpt.

Hope u got it
FaFa|||Thanks it worked.

Do you have any idea about letting user to place fields at specified position.

Thanks in Advancesql

Custom Sort

Is it possible to use an expression to manage a sort.
I want to be able to display in say the following order
Product A
Product C
Product B
id's wont work as they are numbered all over the place.
Thanks in advanceOn Sep 11, 7:20 pm, Tango <Ta...@.discussions.microsoft.com> wrote:
> Is it possible to use an expression to manage a sort.
> I want to be able to display in say the following order
> Product A
> Product C
> Product B
> id's wont work as they are numbered all over the place.
> Thanks in advance
Sure that shouldn't be a problem. You will want to right-click the
table/matrix control -> select Properties -> select the Sorting tab ->
select <Expression...> -> below the Expression column, enter the
desired expression. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||thank you
i was more looking for what the expression would be
"EMartinez" wrote:
> On Sep 11, 7:20 pm, Tango <Ta...@.discussions.microsoft.com> wrote:
> > Is it possible to use an expression to manage a sort.
> > I want to be able to display in say the following order
> > Product A
> > Product C
> > Product B
> >
> > id's wont work as they are numbered all over the place.
> > Thanks in advance
>
> Sure that shouldn't be a problem. You will want to right-click the
> table/matrix control -> select Properties -> select the Sorting tab ->
> select <Expression...> -> below the Expression column, enter the
> desired expression. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>sql

Custom Report Item: Textbox

Hi all,
If it is possible, how I can extend the Textbox item with a DataSet
property. In order to display a field from multiple rows, with a separator ?
Example:
a dataset with 10 rows (and 1 field "Name")
and I want to display in the Textbox
"Name1, Name2, Name3, Name4, Name5, Name6, Name7, Name8, Name9, Name10"
ThanksI'm sure this is possible, just to give you a point in one possible
direction...
From a Database side of things, you could pivot the data in your SQL
statement. SQL Server 2005 has new SQL commands called PIVOT and UNPIVOT...
Once your rows values are pivotted to columns in a single row, then you
could specify them easily enough in the text box.
Hope that helps.
Dan.
"gbouzebra" <gbouzebra@.discussions.microsoft.com> wrote in message
news:D8959BBA-3778-4129-9E3F-AB4443A58C5F@.microsoft.com...
> Hi all,
> If it is possible, how I can extend the Textbox item with a DataSet
> property. In order to display a field from multiple rows, with a separator
> ?
> Example:
> a dataset with 10 rows (and 1 field "Name")
> and I want to display in the Textbox
> "Name1, Name2, Name3, Name4, Name5, Name6, Name7, Name8, Name9, Name10"
> Thanks

Custom Order By...Revisited

Hi

I would like to return rows in a custom order, similar to this post. However, in my case I do not have a fixed number of 'sort by' items. The order I would like to have returned from Col2 is:

'A' Numeric Values in numeric order 0-99 All other alpha values

For example:
'A', '0', '1', '3', '65', 'MyValue1', 'MyValue2'

How could I achieve this?

thanks

Richard

Here You Go...

Code Snippet

Create Table #sorttest (

[Col2] Varchar(100)

);

Insert Into #sorttest Values('A');

Insert Into #sorttest Values('12');

Insert Into #sorttest Values('1');

Insert Into #sorttest Values('3');

Insert Into #sorttest Values('5');

Insert Into #sorttest Values('6');

Insert Into #sorttest Values('78');

Insert Into #sorttest Values('100');

Insert Into #sorttest Values('MyValue1');

Insert Into #sorttest Values('MyValue3');

Insert Into #sorttest Values('MyValue2');

Select

*

From

#sorttest

Order By

Case When Col2 Like '[A-Z]' Then 1

When Isnumeric(Col2)=1 Then 2

Else 3 End,

Case When Isnumeric(Col2)=1 Then Convert(float,Col2) End,

Col2

/*

::Output

Col2

-

A

1

3

5

6

12

78

100

MyValue1

MyValue2

MyValue3

*/

|||hi, try this

SELECT MyMixedField
FROM MyTable
WHERE PATINDEX('%[0-9]%', MyMixedField) = 0
UNION ALL
SELECT MyMixedField
FROM MyTable
WHERE ISNUMERIC(MyMixedField) = 1
UNION ALL
SELECT MyMixedField
FROM MyTable
WHERE PATINDEX('%[0-9]%', MyMixedField) > 0
and ISNUMERIC(MyMixedField) <> 1|||i think manivannan's solution is much better,

@.manivannan..

it think its better to use patindex('%[0-9]%', col2) = 0 for alpha valued records

Order By

Case When patindex('%[0-9]%', col2) = 0 Then 1

When Isnumeric(Col2)=1 Then 2

Else 3 End,

Case When Isnumeric(Col2)=1 Then Convert(float,Col2) End,

Col2|||thank you both very much for your quick and accurate responses!

Works perfectly
|||

It seems that manivannan's suggested use of

Like '[A-Z]'

is most likely the best option to sort on single letter alpha. Don't you think that the

patindex('%[0-9]%', col2) = 0

suggestion 'might' include things like an asterisk, period, @. symbol, $ symbol, etc.? (-As well as allowing multi-character alpha entries on the first sort level...)

However, I would caution that using manivannan's suggestion to use isnumeric() 'could' cause problems. For example, the following evaluates to TRUE

SELECT isnumeric( '$' )

But is it a number? Should it sort with the alphas?

|||Hi

Good point, but actually in this case we don't allow non-alphanumeric characters in this field, so it's not really an issue!

thanks everyone for help on this

Custom 'Order By' Function?

Hi I have a column varchar(4). Users enter values in one of 5 formats -
1) 1,2,3,4...10,11
2) 01,02,03,04...10,11
3) A1,A2,A3,B1,B2,B3...B10,B11
4) 1.1,2.1,3.1.....10.1,11.1
5) 1.1, 1.1A, 1.1B, 2.1, 2.1A, 2.1B....10.1,10.1A
The queruies that select from this table will only select records with
one of the formats at any one time. Is it possible to ensure the order
is always logically correct based on numerical and alphabetical
ordering, as above?
So far its seems ok except formats 4 & 5 where I get the folowoing
output-
1.3 1.3A 1.3P 11.3 16.3 2.3 2.3P 2.3S
Thanks
hals_leftHi
your query will work fine if ur 4 and 5 looks similar to 2.
the results in 4 & 5 are considered and sorted as per the char value.
prefix 0 ans see the results.
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"hals_left" wrote:

> Hi I have a column varchar(4). Users enter values in one of 5 formats -
>
> 1) 1,2,3,4...10,11
> 2) 01,02,03,04...10,11
> 3) A1,A2,A3,B1,B2,B3...B10,B11
> 4) 1.1,2.1,3.1.....10.1,11.1
> 5) 1.1, 1.1A, 1.1B, 2.1, 2.1A, 2.1B....10.1,10.1A
> The queruies that select from this table will only select records with
> one of the formats at any one time. Is it possible to ensure the order
> is always logically correct based on numerical and alphabetical
> ordering, as above?
> So far its seems ok except formats 4 & 5 where I get the folowoing
> output-
> 1.3 1.3A 1.3P 11.3 16.3 2.3 2.3P 2.3S
> Thanks
> hals_left
>|||On 28 Jul 2005 08:00:54 -0700, hals_left wrote:

>Hi I have a column varchar(4). Users enter values in one of 5 formats -
>
>1) 1,2,3,4...10,11
>2) 01,02,03,04...10,11
>3) A1,A2,A3,B1,B2,B3...B10,B11
>4) 1.1,2.1,3.1.....10.1,11.1
>5) 1.1, 1.1A, 1.1B, 2.1, 2.1A, 2.1B....10.1,10.1A
Hi hals_left,
How does one store 10.1A in a varchar(4) column?

> is thgere now way to write a different
>ordering function?
Try the following. It's not pretty, but it might work:
ORDER BY
CASE
WHEN my_column LIKE '[A-Z]%'
THEN LEFT (my_column, 1)
END,
CASE
WHEN my_column LIKE '[A-Z]%'
THEN CAST (SUBSTRING (my_column, 2, 3) AS int)
WHEN my_column LIKE '%.%'
THEN CAST (LEFT (my_column, CHARINDEX ('.', my_column) - 1) AS int)
ELSE CAST (my_column AS int)
END,
CASE
WHEN my_column LIKE '%.%'
THEN SUBSTRING (my_column, CHARINDEX ('.', my_column) + 1, 4)
END
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Custom Order By

Hi,

I want to ask something. Is there any possibility to use order by using our own criteria. Example: I want to order my query by ProductName with specified criteria. See below

Products:

ProductID ProductName

1 Book

2 Pen

3 Ruler

The query for "select * from Products order by ProductName" will actually result

1 Book

2 Pen

3 Ruler

I want the result be like this:

2 Pen

1 Book

3 Ruler

May be my crazy idea is using this kind of query:

"select * from Products order by ProductName ( 'Pen' , 'Book' , 'Ruler' )"

Thanks in advance .....

If you have a limited number of 'sort by' items, you could use a CASE structure.

SELECT
Col1,
Col2,
Col3,
etc
FROM MyTable
ORDER BY CASE Col2
WHEN 'Pen' THEN 1
WHEN 'Book' THEN 2
WHEN 'Ruler' THEN 3
END

|||

it works, great !!! ...

thanks ....

Custom Logger Doesn't Work in Certain cases

I am testing a set of SSIS packages, In order to test my SSIS packages for errors I have two negative test cases

1) I didn't provide checkpoint file for the checkpoint enabled package.

2) I provide a wrong configuration file

Even though I am using a script task in my "on error" event of my SSIS package. It is not executed. (Perhaps because the package doesn't even execute).

My problem is that SSIS itself puts just a simple one liner in windows event log "Package Failure Error". It does not provide which package failed, why it failed etc. Therefore the admin who gets the ticket to resolve the issue has no clue of what is going wrong and where!

Since my custom logger doesn't even run, I don't know how can I put more details into the windows event log.

How can I resolve this?

regards,

Abhishek.

Sorry to bump so soon, but I am really stuck here.

|||Loggers don't even enter the picture on malformed configuration file warnings/errors. Malformed configurations files are detected at package load time, even before validation. So, if you have a logger ( custom or stock, doesn't matter) defined inside the package, warnings like "invalid xml configuration file" won't be sent there.

The sequence for possible package execution is as follows:
1. Package Load (warnings/errors such as invalid configuration file happen here )
2. Package Validation
3. Package Execution (with validation too)

To trap package load errors, you could capture dtexec's console log output (if you're using that mechanism for package execution).

To implement your own logging; that is, to catch warnings/errors early in the package lifespan without using dtexec's console logger to do so, implement IDTSEvents (by subclassing DefaultsEvents) on package load. See Loading and Running a Local package programmatically (the capturing events from a running package section)

For example, the following will log package load,validate,and execute events, while loggers will get validate and execute events.

Code Snippet

using System;
using System.Diagnostics;
using Microsoft.SqlServer.Dts.Runtime;

namespace IS
{
class PackageRunner
{
static void Main(string[] args)
{
string pkgFileName = args[0];
ISEventsListener eventListener = new ISEventsListener();
// subclass of DefaultEvents

Application isApp = new Application();
// listen for pre-validation errors via LoadPackage
using (Package pkg = isApp.LoadPackage(pkgFileName, eventListener))
{
DTSExecResult validationOutcome = pkg.Validate(null, null, eventListener, null);
if (validationOutcome == DTSExecResult.Success)
{
DTSExecResult executionOutcome = pkg.Execute(null, null, eventListener, null, null);
}
}
Console.WriteLine("Press something...");
Console.ReadKey();
}
}
}

Checkpoint files are a different story, a missing checkpoint file setting will be logged by custom loggers and the package will not execute because of that.

Custom authorization on a particular report

Hi,
I have created a report that displays order summaries based on a parameter
CustomerName. I have many customers and want to give them all access to this
report. I've installed the Forms Authentication sample and am able to
authenticate, so each customer has a login.
The problem is that I want each customer to be able to view only his own
order summary (i.e. the CustomerName parameter to the report must be set to
the customer's login id and he cannot change it). Is there a way to pass the
username given in Forms Authentication to the report?
Passing it in the URL is no good because users can modify the URL. The
authorization pieces in the Forms Authentication sample seem only to grant or
deny access on to a particular report, but not on parameters to the report.
Any tips would be greatly appreciated.
Thanks,
DonYou can use the global user!userid, don't pass this as a parameter.
Bruce L-C
"Don" <Don@.discussions.microsoft.com> wrote in message
news:37361218-AD5E-4B95-BF84-3C863FFD1738@.microsoft.com...
> Hi,
> I have created a report that displays order summaries based on a parameter
> CustomerName. I have many customers and want to give them all access to
this
> report. I've installed the Forms Authentication sample and am able to
> authenticate, so each customer has a login.
> The problem is that I want each customer to be able to view only his own
> order summary (i.e. the CustomerName parameter to the report must be set
to
> the customer's login id and he cannot change it). Is there a way to pass
the
> username given in Forms Authentication to the report?
> Passing it in the URL is no good because users can modify the URL. The
> authorization pieces in the Forms Authentication sample seem only to grant
or
> deny access on to a particular report, but not on parameters to the
report.
> Any tips would be greatly appreciated.
> Thanks,
> Don

Custom Assembly not invoked in Report Manager

Hi,

I developed a custom assembly( in C# ) which references satellite assemblies. In order to refer this assembly in one of my reports, I copied the assembly and its dependencies in the Report Server bin folder and Report Designer folder. Also I inserted CodeGroup tag for the custom asembly in both rssrvpolicy.config and rspreviewpolicy.config with Full Trust.

Now, the report works perfectly when I preview it through Visual Studio.NET IDE. But when I deploy the same report to the report manager, it is not working. It does not give any error, but the textboxes whose expression invoke the method in the custom assembly, have empty values. So, the text boxes show up empty.

Any idea where am going wrong?

Thanks,

Rama

You also need to add code groups for all non-MS assemblies referenced by your custom assembly.
Do not forget to assert permissions (or fulltrust) in your custom assembly

|||

Hi,

As I mentioned in my post, I have added the code group for the custom assembly and given full trust permission for it. I will explain more about the custom assembly.

The custom assembly just reads strings from satellite assemblies( based on the current culture ) and assigns it as value to a textbox in the report. When I preview the report from the Visual Studio.NET IDE , it works perfectly fine i.e. it reads the strings from the satellite assembly linked with the user's culture.

When I deploy the report to the report manager, the custom assembly returns the following exception:

System.Resources.MissingManifestResourceException: Could not find any resources appropriate for the specified culture (or the neutral culture) in the given assembly. Make sure "ReportStrings.resources" was correctly embedded or linked into assembly "ReportLocalization".
baseName: ReportStrings locationInfo: <null> resource file name: ReportStrings.resources assembly: ReportLocalization, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null
at Microsoft.DCMReports.ReportLocalization.ResourceHelper.GetResourceValue(String resourceKey)
at Microsoft.DCMReports.ReportLocalization.Reports.GetValue(String culture, String key)

I have copied the custom assembly to the Report Server bin folder and copied the satellite assemblies onto their specific culture folders i.e. satellite assemblies linked to the custom assembly with culture "de", will be copied to the "de" folder and so on.

Should I do something for this satellite assembly too? Please give your suggestions.

Thanks,

Rama

Custom Assemblies RS 2000

Hi,

I'm creating a report with 3 parameters. The first parameter will hold the user ID. In order to get this I need to write a function to find the NT login name of the user and then search in a SQL Server Table to find what the User ID is for that user.

This user ID will be required in around 15 or so reports so I suspect a custom assembly will suit the purpose rather than adding custom code to each report.

Can anybody tell me if this sort of thing achievable?

Thanks in advance,

Steve

Sure. You can take a look at the documentation on custom assemblies here http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_rdl_6d0i.asp

Cursors....How to get away from using?

Hey guys

I have heard cursors are not the way to go. But I am wondering if/how to get out of a situation that I am using a cursor in...in order to make my stored proc run more effieciently.

I am quite novice in my abilities and I am completely stumped on how to get around using them.

As far as INSERTs go, I think I can work around that, but how would I write UPDATE statements for all lines of a table to say pull a key from another table to reference them together?

I usually make my SELECT statement in the cursor, then update against the criteria from the SELECT statement. Now this is quite a slow process when I am updating 100K records.

Any help or pointers or a link to a good tutorial would be woderful.

Thanks
tiborcursors are on the rare occassion the right way to go. incrementing totals for example. or if the situation requires row by row processing like you need to fire an extended stored procedure.

what you want to read about though is set based processing.

tell me, can you take your select statement and move the from and where clause to the update statement to create an UPDATE FROM statement? Bye bye cursor.|||You might want to take a look at the documentation (http://technet.microsoft.com/en-us/library/ms177523.aspx). Take a look at examples C and F. Example C (from clause) works in both SQL Server 2000 and SQL Server 2005, although not very well documented for SQL Server 2000. Example F (Common Table Expression) works only in SQL Server 2005, but I think it will potentially perform better in some cases. I have not verified this though.|||Great, thanks guys!

The "Using UPDATE with the FROM Clause" in the documentation was exactly what i needed...it took 30 seconds vs 1.5 hours, haha.

I appreciate the help very much.

tibor|||I always love some of the subject titles

Like my response for this one (and don't get offended) would have been..

"Leave the IT Business"

But i'm glad you got what you needed.

Now, post the code so we can make it really fly|||Well, no offense taken...but dont assume that because I asked a SQL question that I am in the IT business. :)|||Well, since your query used quite some amount of time, I DO assume that you have quite a bit of data as well, and you appears to work on some data in or from some kind of business. If that is correct, well... I'm glad I could help, but I would be concerned about what you can end up doing. Databases are not to play with, and as you have noticed, a badly written query may cause the server working for hours, or even days. Please keep that in mind.