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

addning roles to a user

I created a "standard" role called "TestRole". Then I went to the list of
users and selected "dbo". I tried adding "TestRole" but I received a message
"Error 15405: Cannot use the reserved user or role name 'dbo'". I have
looked thru various sources to try to understand this but nothing really
straightforward explains what the problem is.
Hi Steven
Please ALWAYS state what version you are running.
The user dbo can do everything in a database. Why do you want to add that
user to a role?
What are you trying to accomplish?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Steven.Dahlin" <StevenDahlin@.discussions.microsoft.com> wrote in message
news:46113FB9-B978-4DC3-83BE-609BACE5F8F1@.microsoft.com...
>I created a "standard" role called "TestRole". Then I went to the list of
> users and selected "dbo". I tried adding "TestRole" but I received a
> message
> "Error 15405: Cannot use the reserved user or role name 'dbo'". I have
> looked thru various sources to try to understand this but nothing really
> straightforward explains what the problem is.
>
sql

addning roles to a user

I created a "standard" role called "TestRole". Then I went to the list of
users and selected "dbo". I tried adding "TestRole" but I received a message
"Error 15405: Cannot use the reserved user or role name 'dbo'". I have
looked thru various sources to try to understand this but nothing really
straightforward explains what the problem is.Hi Steven
Please ALWAYS state what version you are running.
The user dbo can do everything in a database. Why do you want to add that
user to a role?
What are you trying to accomplish?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Steven.Dahlin" <StevenDahlin@.discussions.microsoft.com> wrote in message
news:46113FB9-B978-4DC3-83BE-609BACE5F8F1@.microsoft.com...
>I created a "standard" role called "TestRole". Then I went to the list of
> users and selected "dbo". I tried adding "TestRole" but I received a
> message
> "Error 15405: Cannot use the reserved user or role name 'dbo'". I have
> looked thru various sources to try to understand this but nothing really
> straightforward explains what the problem is.
>

addning roles to a user

I created a "standard" role called "TestRole". Then I went to the list of
users and selected "dbo". I tried adding "TestRole" but I received a message
"Error 15405: Cannot use the reserved user or role name 'dbo'". I have
looked thru various sources to try to understand this but nothing really
straightforward explains what the problem is.Hi Steven
Please ALWAYS state what version you are running.
The user dbo can do everything in a database. Why do you want to add that
user to a role?
What are you trying to accomplish?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Steven.Dahlin" <StevenDahlin@.discussions.microsoft.com> wrote in message
news:46113FB9-B978-4DC3-83BE-609BACE5F8F1@.microsoft.com...
>I created a "standard" role called "TestRole". Then I went to the list of
> users and selected "dbo". I tried adding "TestRole" but I received a
> message
> "Error 15405: Cannot use the reserved user or role name 'dbo'". I have
> looked thru various sources to try to understand this but nothing really
> straightforward explains what the problem is.
>

Adding Integers

The code below has this line
SET @.SOGallons = @.ODTGallons

I need it to add the Current value of @.SOGallons to the newly selected value of @.ODTGallons and set that as the new value of @.SOGallons.

I've tried
SET @.SOGallons = @.SOGallons + @.ODTGallons

SET @.SOGalTemp = @.SOGallons
SET @.SOGallons= @.SOGalTemp + @.ODTGallons

Neither Worked

<CODE>
FROM [CSITSS].[dbo].[Orderdt] as ODT LEFT OUTER JOIN [CSITSS].[dbo].[Orddtcom] as OCOM
ON ODT.[Companydiv] = OCOM.[Companydiv] AND ODT.[OrderNumber] = OCOM.[OrderNumber] AND
ODT.[Sequence] = OCOM.[Sequence] WHERE ODT.[Companydiv]= 'GLPC-TRANS' AND ODT.[OrderNumber] = @.OrdNum AND
([LineType] = 'IP' OR [LineType] = 'SO' OR [LineType] = 'DL' OR [LineType] = 'PU')

OPEN TC1

FETCH NEXT FROM TC1 INTO @.LT, @.ODTGallons, @.ODTComm
WHILE @.@.FETCH_STATUS=0
BEGIN
IF @.LT = 'SO'
BEGIN
SET @.SplitTest = 1
SET @.SOGallons = @.ODTGallons
IF @.SOGallons > 0
BEGIN
SET @.SOGalTest = 1
END
ELSE
BEGIN
SET @.SOGalTest = 0
END
IF @.SplitTest <> @.SOGalTest
BEGIN
SET @.SOGalTest = 0
END
END
ELSE
BEGIN
SET @.SOGalTest = 1
END
FETCH NEXT FROM TC1 INTO @.LT, @.ODTGallons, @.ODTComm
END
CLOSE TC1
DEALLOCATE TC1</CODE>LineType Gallons Commodity
DL 4000 #2 ULSD DYED
IP 7000 87 NL / ETH
PU 4000 #2 ULSD DYED
SO 7000 87 NL / ETH

There may be multiple lines of any of the above line types.

IP = Initial Pickup
PU = Additional Pickup
SO = Stop Off
DL = Final Delivery

I need to know if all of the gallons that were picked up where delivered

IP + PU = SO + DL

I'm doing a check on the validity of the commodity type as well but with the forums helps we figured that one out yesterday.|||First, your issue is NULL issue.
You said
I've tried
SET @.SOGallons = @.SOGallons + @.ODTGallons

SET @.SOGalTemp = @.SOGallons
SET @.SOGallons= @.SOGalTemp + @.ODTGallons

But before you use @.SOGallons, you need to initialise it. Otherwise it stays as NULL. And whatever value added to it becomes NULL. This is why it failed. Say, insert this line "SET @.SOGallons = 0" before "SET @.SOGallons = @.SOGallons + @.ODTGallons".

Next, in your WHILE Loop, put "SET @.SplitTest = 1" before the loop started as it is a constant.|||Hey,

Thanks.

I know VB fairly well but am completely new to SQL as of about 3 weeks ago. I always seem to know what I want to do but am continually making small syntax errox that trip me up.

adding an article to a publication

When adding an article I have checked the checkbox to show unpublished
articles
and then selected the required the tables. After this, I have run the
snapshot agent for this publication.
The 2 tables I added contained no data yet the snapshot started the coping
of rows of other tables in the publication . Is this right as I thought this
snapshot would just process information for the unpublished tables?
thanks for any help
Sammy
Hi Sammy,
This is expected behavior if you have a merge or snapshot publication. If
you have a transactional publication, a snapshot of all articles will always
be generated if the immediate_sync publication property is set to true.
Typically, the immediate_sync publication property is set to true if you
allowed anonymous subscriptions while creating the publication through the
CreatePublication wizard. If none of what I said previously applies to your
scenario, it would be great if you can post more details about your
publication so either me or other folks on this newsgroup are in a better
position to figure this out.
HTH
-Raymond
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:0A215E11-F110-458D-A9FB-E29B4747E3E9@.microsoft.com...
> When adding an article I have checked the checkbox to show unpublished
> articles
> and then selected the required the tables. After this, I have run the
> snapshot agent for this publication.
> The 2 tables I added contained no data yet the snapshot started the coping
> of rows of other tables in the publication . Is this right as I thought
> this
> snapshot would just process information for the unpublished tables?
>
> thanks for any help
> Sammy
>