Addtion / Sum of Parameter Array Values - SSRS

Hi All,
I want to Sum instead of Join Parameter arrays selected values and use
it within the report to display data based on total sum of all the
values selected. Join concatanates them with comma delimited values
but i want sum of all those values.
Example as below
Value DisplayText
1 - Current Month
2 - Previoius Year
4 - Half Year
8 - Year to date
so if
current month and half year
are selected then i want to read 1 + 4 = 5 and not 1,4 as currently i
get by join function.
Code = SUM(Parameters!dropdownbox.value)
Result = #Error
Code = Join(Parameters!dropdownbox.value,",")
Result = "1,4"
Code = SUM(Join(Parameters!dropdownbox.value,","))
Result = #Error
Code = Parameters!dropdownbox.count
Result = 4 (it gives me length of array)
Code = ?
Result = 5 (This is the result is want...but can make it work)
Any help greatly appreciated
Regards
Nirav Lulla
Yotta ConsultingOn May 14, 1:02 pm, nlulla <nirav.lu...@.gmail.com> wrote:
> Hi All,
> I want to Sum instead ofJoinParameterarrays selected values and use
> it within the report to display data based on total sum of all the
> values selected.Joinconcatanates them with comma delimited values
> but i want sum of all those values.
> Example as below
> Value DisplayText
> 1 - Current Month
> 2 - Previoius Year
> 4 - Half Year
> 8 - Year to date
> so if
> current month and half year
> are selected then i want to read 1 + 4 = 5 and not 1,4 as currently i
> get byjoinfunction.
> Code = SUM(Parameters!dropdownbox.value)
> Result = #Error
> Code =Join(Parameters!dropdownbox.value,",")
> Result = "1,4"
> Code = SUM(Join(Parameters!dropdownbox.value,","))
> Result = #Error
> Code = Parameters!dropdownbox.count
> Result = 4 (it gives me length of array)
> Code = ?
> Result = 5 (This is the result is want...but can make it work)
> Any help greatly appreciated
> Regards
> Nirav Lulla
> Yotta Consulting
I'm not sure how many items are in your dropdown list but this will
work if you only have a few...
=CInt(Parameters!site.Value(0)) + CInt(Parameters!site.Value(1))|||Hi James,
Thanks for posting your reply, you suggesstio would only work if i
have set fixed length of options, but i don't know how this is going
to work in case of unknown number of options.
For now , I have Created a .net class file DLL with following code and
referenced the dll in my .rdl file, it works for me, but would be good
if it can be done within SSRS itself. Any more suggesstions welcome
Nirav Lulla
Yotta Consulting
Public Class ClsCommon
Const bDisplayColumn As Boolean = False
Const bHideColumn As Boolean = True
Shared Function SumOfArrayString(ByVal ArrayString As String) As
Integer
Dim arylist As System.Array
Dim sum As Integer
Try
arylist = ArrayString.Split(",")
For Each item As Integer In arylist
sum += CInt(item)
Next
Catch ex As Exception
Return -1
Finally
Return sum
End Try
End Function
End Class

Additional Information

I found the way to pass a open connection (I had been trying to use an ole
connection instead of a sqql connection) so I think I have eliminated that
as a problem. The stack trace seems to imply that it is not happy with the
parameters. I have them defined as follows:
Dim arParms(3) As SqlParameter
arParms(0) = New SqlParameter("@.id", SqlDbType.Int)
arParms(0).Value = Request.Params("ID")
arParms(1) = New SqlParameter("@.clickcount", SqlDbType.Int)
arParms(1).Direction = ParameterDirection.Output
arParms(2) = New SqlParameter("@.url", SqlDbType.VarChar, 100)
arParms(2).Direction = ParameterDirection.Output
Wayne
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:ux63E$T2FHA.400@.TK2MSFTNGP09.phx.gbl...
>I am tyring to use SqlHelper to execute a SP with some passed parameters.
>When I try the statement shown below I get an error "Object reference not
>set to an instance of an object"
> I suspect the connection string as I am not sure exactly what SqlHelper
> wants for that value? I've tried including the provider but it doesn't
> like that. Googling SqlHelper gave me more confusion than help. The
> connection string works fine for normal command use.
> Can anyone point me to some clear information on exactly what the
> SqlHelper command expects in this case? Is there a way to specify an
> existing connection to SqlHelper?
> ===========================
> SqlHelper.ExecuteNonQuery("Data Source = myServer.net; Initial Catalog =
> DB_150547; User ID = myID; Password=myPW", CommandType.StoredProcedure,
> "GetAdClicks", arParms)
>Duihh!!!
The parameter array was Dimed as "3" but there are only 3 elements! The code
was translated from some older "C#" code and they must do it different?
Changed the Dim value to "2" and the problem is solved.
Wayne
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:uTUw5VU2FHA.3964@.TK2MSFTNGP10.phx.gbl...
>I found the way to pass a open connection (I had been trying to use an ole
>connection instead of a sqql connection) so I think I have eliminated that
>as a problem. The stack trace seems to imply that it is not happy with the
>parameters. I have them defined as follows:
> Dim arParms(3) As SqlParameter
> arParms(0) = New SqlParameter("@.id", SqlDbType.Int)
> arParms(0).Value = Request.Params("ID")
> arParms(1) = New SqlParameter("@.clickcount", SqlDbType.Int)
> arParms(1).Direction = ParameterDirection.Output
> arParms(2) = New SqlParameter("@.url", SqlDbType.VarChar, 100)
> arParms(2).Direction = ParameterDirection.Output
> Wayne
>
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:ux63E$T2FHA.400@.TK2MSFTNGP09.phx.gbl...
>

Adding multiple role assignments all at once

Is there a way to add multiple role assignments to a report definition all at once?

Instead of going to the Report Manager, selecting each individual report definition, and then clicking on "New Role Assignment" multiple times to add users, is there a way to add multiple users all at once?

You can write a script and use rs.exe.

Here is a pointer to the tool. There should also be information for example scripts.

http://msdn2.microsoft.com/en-us/library/ms162839.aspx

Adding an identity column solely for the benefit of the clustering

- Instead having indexes:
ParentID, SomeValue (non-clustered)
ParentID, SomeOtherValue (non-clustered)
SomeValue, ParentID (non-clustered)
SomeOtherValue, ParentID (non-clustered)
Create just three indexes:
ParentID
SomeValue
SomeOtherValue
SQL Server is smart enough to use them when necessary.

> This table could end up containing 20+ million records.
> Less so on offline clients, which would only contain a subset
> of the master database, but which would be unable to spread
> the tables and indexes across multiple disk systems.
Is there any other column that you can use for a clustered index (remember,
a good candidate will be one that you use in range queries)?
If so, then you do not need to add the identity column.
AMB
"Joergen Bech @. post1.tele.dk>" wrote:

> Let's say I have a table containing string values:
> ValueTable:
> --
> ValueID guid
> ParentID guid
> SomeValue nvarchar(1000)
> SomeOtherValue nvarchar(1000)
> with indexes on
> ValueID (clustered)
> ParentID, SomeValue (non-clustered)
> ParentID, SomeOtherValue (non-clustered)
> SomeValue, ParentID (non-clustered)
> SomeOtherValue, ParentID (non-clustered)
> Note: GUIDs are required for this application. The table does
> not actually look like this, but is a simplification for the sake
> of the example.
> This table could end up containing 20+ million records.
> Less so on offline clients, which would only contain a subset
> of the master database, but which would be unable to spread
> the tables and indexes across multiple disk systems.
> Now the question is: Would it make sense to add an
> identity column (8-byte long), make this column the clustered
> index, and change the ValueID index to non-clustered?
> The idea is to reduce the size of the non-clustered indexes,
> seeing that the clustered column would only be half the size
> of the original - and force insertion of new records to the end
> of the table, rather than all over the place.
> But besides some savings in space, would I actually gain anything
> in terms of performance, seeing that each insertion would require
> several non-clustered GUID indexes to be updated?
> Pros and cons of adding an identity column in the above scenario?
> TIA,
> Joergen Bech
>
>On Fri, 25 Mar 2005 05:55:03 -0800, "Alejandro Mesa"
<AlejandroMesa@.discussions.microsoft.com> wrote:

>- Instead having indexes:
>ParentID, SomeValue (non-clustered)
>ParentID, SomeOtherValue (non-clustered)
>SomeValue, ParentID (non-clustered)
>SomeOtherValue, ParentID (non-clustered)
>Create just three indexes:
>ParentID
>SomeValue
>SomeOtherValue
>SQL Server is smart enough to use them when necessary.
Sorry. That would require bookmark lookups. By having "Value, ID"
and "ID, Value", I basically have covering indexes for all IDs
satisfying a specific value, as well as all values (typically 20-50)
for a specific ID. I tried the single-column index approach, but
this - though a great space-saver - requires a bit more work for
the server.

>Is there any other column that you can use for a clustered index (remember,
>a good candidate will be one that you use in range queries)?
>If so, then you do not need to add the identity column.
Not really. As I mentioned in the first post, the purpose of adding
the identity column was
1) to have a narrow clustered index, thereby saving space in the non-
clustered indexes.
2) to force insertion of new records to take place at the end of the
table, rather than causing splits all over the place, which would
happen when basing it on a GUID.
Then again: Even though the clustered index (the table itself) never
needs defragging - being identity-based and all - is probably not of
any use at all performance-wise, if it is not used for anything but
saving NC-space (i.e. it is not even used for joins of any kind).
/JB
>
>AMB
>
>"Joergen Bech @. post1.tele.dk>" wrote:
>|||
>Then again: Even though the clustered index (the table itself) never
>needs defragging - being identity-based and all - is probably not of
>any use at all performance-wise, if it is not used for anything but
>saving NC-space (i.e. it is not even used for joins of any kind).
To correct myself: They are, of course, used for bookmark lookups,
in which case an always-defragged clustering index is nice to have.
Though - if most high-performance queries are served by covering
indexes, bookmark lookups won't be needed anyway.
Oh well. As the identity index won't be referenced by any T-SQL
code, I can always do all the tweaking and testing I like later.
/JB