Custom sorting

I have a parameter in my report called SortType and depending on the value of the parameter I want to sort by a particular column. I have tried doing this by using a custom function as follows:

Function SortTypeValue() As String
If Report.Parameters!SortType.Value = "1" Then
Return "=Fields!StockDesc.Value"
else Return "=Fields!StockCode.Value"
End If
End Function

Then in the sorting tab I have entered in the expression field: =Code.SortTypeValue().

However this is not doing anything. The data is just displayed in the order it is taken from the database. Can this be done? If so can anybody please tell me what I am doing wrong?

Thanks in advance.
Dynamic use of parameters for grouping can be achieved using the following syntax:

=Fields(Parameters!ParamName.Value).Value

Maybe this will also work for sorting

--

Your code is going to return the same value regardless of sort type anyway.
=Fields!StockDesc.Value
You don't need it if the above method works.

--

You can set interactive sorting so the user can sort on any column they wish:

right click on the column header
interactive sort
tick tickbox
set sort expression to the value held in that particular column (i.e. if column is sales amt then =fields!salesamt.value)

done

if you need to do this for a whole raft of columns, it is quicker to do it in the (F4) properties window:
expand the 'user sort' propery and set the 'sort expression' drop down as per the column name
NB keep sort expression selected otherwise when you click on the next column you wish to add a sort to, the properties collapse|||You say my code will return the same value regardless of sort type anyway. Why? Each condition returns a different string.

If I was to use =Fields(Parameters!ParamName.Value).Value I would still need a way to determine which column the user wants to sort on so I know which ParamName to insert.|||To paraphrase the jerky boys "my eyes is going crazy"
but you still don't need the code anyway.

With regards to using my technique for sorting.....

It works.

I just created a very simple report.
I added one parameter called 'sort by'

I just set the values of this to the names of the db columns returned. Spelling exactly as per db cols!

In the table, sorting tab, i just added this

=Fields(Parameters!sortby.Value).Value

go to the preview, Pick your column name, run

simple|||

One step further. . .

Create the parameter as described by "adolf garlic" and also create a parameter named "SortDirection" and give the parameter values "Ascending" and "Descending"

In the table/sort "Expression" field(s) enter:

=Iif(Parameters!SortDirection.Value = "Ascending", Fields(Parameters!SortBy.Value).Value, "")

=Iif(Parameters!SortDirection.Value = "Descending", Fields(Parameters!SortBy.Value).Value, "")

and set the proper direction for both expressions.|||Thanks adolf garlic. Works like a treat. Exactly what I was after.
sql

Custom sort the dimension members

Hello users of SSAS2005!

I am in the funny position of developing a cube for (at least) three different user groups, and each need "their" special treatment. My problem is in sort order of one particular dimension. The dimension has 3 fields: ID (=person number), Firstname and Lastname - as simple as straightforward as it can be.

Now Finance wants it sorted by ID to relate it to their documents. Human resources needs it by last name, so it can be copy-pasted directly into their structures.

It gets really hilarious when IT needs it sorted by first name (not joking here) because an external application delivers correlating date sorted by first name (again, I am not making that up).

Since I cannot convience anyone onto one common sort I need to make all three possible sort orders available, and I might end up with having 3 identical dimensions or one dimension with 3 identical attributes (except for sorting)

We use Excel as our frontend, I know I can sort a cube there as well. This is, of course, if the field to base the sorting upon is dragged onto the same axis, but that is a bit clumsy too.

What is the "best practices" approach to such a funny situation? (Except for changing the company workflow, let's take that as ... sigh ... static.)

Hello Ralf! I am not sure that this is possible in SSAS/MDX.

One way to solve this is to make the SSAS 2005 reports in Reporting Services 2005.

Check Books On Line for dynamic sorting in reporting services. It is also possible to sort with parameters.

If you can only use Excel pivot tables you can make three different reports, each sorted according to each groups preference.

Publish them i Sharepoint(or any other portal).

HTH

Thomas Ivarsson

HTH

Thomas Ivarsson

Custom sort records in a stored proc

Is there any way to modify this proc so that:
If it recieves a non-negative centre_id the data is ordered by
centre_name but with the centre whose id was passed, being first in
the the list.
If -1 is passed then just order by centre_name.
thanks.
CREATE PROCEDURE [dbo].[get_centres]
@.centre_id smallint
AS
SELECT centre_id, centre_name FROM tbl_centre
ORDER BY CASE
WHEN centre_id= @.centre_id Then 0
WHEN centre_id=-1 THEN centre_name
ELSE centre_id End
GOSELECT centre_id, centre_name
FROM tbl_centre
ORDER BY
CASE WHEN centre_id <> @.centre_id THEN 1 END,
centre_name
David Portas
SQL Server MVP
--|||Try,
...
order by
case when @.centre_id > -1 and centre_id = @.centre_id then 0 else 1 end,
centre_name;
AMB
"hals_left" wrote:

> Is there any way to modify this proc so that:
> If it recieves a non-negative centre_id the data is ordered by
> centre_name but with the centre whose id was passed, being first in
> the the list.
> If -1 is passed then just order by centre_name.
> thanks.
> CREATE PROCEDURE [dbo].[get_centres]
> @.centre_id smallint
> AS
> SELECT centre_id, centre_name FROM tbl_centre
> ORDER BY CASE
> WHEN centre_id= @.centre_id Then 0
> WHEN centre_id=-1 THEN centre_name
> ELSE centre_id End
> GO
>|||Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<1114789890.6
70395.10780@.g14g2000cwa.googlegroups.com>...
> SELECT centre_id, centre_name
> FROM tbl_centre
> ORDER BY
> CASE WHEN centre_id <> @.centre_id THEN 1 END,
> centre_name

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 DLL #Error Issue -Really Critical - Need your Help to Sort out the Issue

Hi MSTR Mentors,
I am in need of your Help... I am having this issue for a very long
time...Hope I will get a solution from you guys...
We have a Custom .net dll, referred in the RDL, the dll has a function
which gets the encrypted Credit Card# and returns the Decrypted value to
the Report.
I have done all the steps listed below
1. Assert permissions in your custom assembly.
2. Make the custom assembly available to Report Designer and to Report
Server.
3. Reference the custom assembly in your report.
4. Modify the code access security settings for your custom assembly.
Ref: http://support.microsoft.com/?kbid=842419#XSLTH3133121123120121120120
Step 1: I have added the following code to Assert Permission.
SecurityPermission secPerm = new SecurityPermission
(PermissionState.Unrestricted);
secPerm.Assert(); - Is this Assert Permission Correct
Step 2: Copied the assembly to the following locations
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\
bin
&
C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer
Step 3: Reference the custom assembly in the report (thru Report Properties)
Step 4: Modify the code access security for the custom assembly by creating
new permission set and code groups.
Copy the code from the security.config(C:\WINNT\Microsoft.NET\Framework\
v1.1.4322\CONFIG\) and paste it in
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\
rssrvpolicy.config
&
C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer\
rspreviewpolicy.config
PERMISSION SET
<PermissionSet class="NamedPermissionSet"
version="1"
Name="ILNReportsPermissionSet">
<IPermission class="SecurityPermission"
version="1"
Flags="Assertion, Execution"/>
</PermissionSet>
CODE GROUP
<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="TESTPS"
Name="TESTCG"
Description="">
<IMembershipCondition class="UrlMembershipCondition"
version="1"
Url="file://C:/Program
Files/Microsoft SQL Server/MSSQL/Reporting
Services/ReportServer/bin/CreditCardDecrypt.dll"/>
</CodeGroup>
Save and close the files
The tried to run the report in the DebugLocal Mode - Fails ?
Dll Code: I have attached the custom assembly code.
Note: To make sure the Dll works fine in Web, I have created a sample web
application and tested the dll by passing the values to the dll function It
works fine, but not working with the RDL.
Custom DLL Code
using System;
using System.Text;
using System.Net;
using System.IO;
using System.Collections.Specialized;
using System.Configuration;
using System.Security.Permissions;
using System.Security.Cryptography;
using FCLX509 = System.Security.Cryptography.X509Certificates;
using WSEX509 = Microsoft.Web.Services2.Security.X509;
using WSECRY = Microsoft.Web.Services2.Security.Cryptography;
[assembly:CLSCompliant(true)]
namespace CreditCardDecrypt
{
/// <summary>
/// Summary description for DecryptClass.
/// </summary>
///
public class DecryptClass
{
public DecryptClass()
{
//
// TODO: Add constructor logic here
//
}
public static string DecryptCardInfo(string cc,string subjectName,string
storeName)
{
string sCreditCard = "";
try
{
if(cc == "" || cc== null)
return sCreditCard = "NA";
SecurityPermission secPerm = new SecurityPermission
(PermissionState.Unrestricted);
secPerm.Assert();
WSEX509.X509CertificateStore.StoreLocation location = WSEX509.X509CertificateStore.StoreLocation.LocalMachine;
WSEX509.X509CertificateStore.StoreProvider provider = WSEX509.X509CertificateStore.StoreProvider.System;
WSEX509.X509CertificateStore store = new WSEX509.X509CertificateStore
(provider, location, storeName);
bool fopen = store.OpenRead();
if(fopen)
{
WSEX509.X509CertificateCollection certs = store.FindCertificateBySubjectString(subjectName);
if (certs.Count > 0)
{
WSEX509.X509Certificate cer = certs[0];
WSECRY.RSACryptoServiceProvider rsaCsp = (WSECRY.RSACryptoServiceProvider)cer.Key;
byte[] cipherData = Convert.FromBase64String(cc);
byte[] plainData = rsaCsp.Decrypt(cipherData, false);
sCreditCard = Encoding.UTF8.GetString(plainData);
}
}
if (store != null)
store.Close();
return sCreditCard;
//return cc;
}
catch(Exception ex)
{
return ex.ToString();
}
}
}
}
Thanks
Balaji
Note : This is my email id (bkkrishnan[AT]hotmail.com).
--
Message posted via http://www.sqlmonster.comCan any one please help on this.....
Still can't able to fix the issue
Thanks
Balaji
--
Message posted via http://www.sqlmonster.com