Showing posts with label dataset. Show all posts
Showing posts with label dataset. Show all posts

Monday, March 19, 2012

Conditional Sum Statement

Hey all,
Quick question, I have a field that I need to sum only when another
field is a certan value.
For example, a dataset with 5 fields {row_id, dealer_id, rep_id,
sales_code, sales_amt} and grouped by dealer_id then rep_id, I would
want to sum the sales_amt field when the first character of the
sales_code field is an 'X'. So for the set:
1, 1, 999, X100, $200.00
2, 1, 999, 200, $500.00
3, 1, 999, 898, $1000.00
4, 1, 555, X340, $2000.00
5, 1, 555, X444, $23.00
The resultant sums would be:
for dealer_id 1: 2223.00
for rep_id 999: 200.00
for rep_id 555: 2023.00
I am trying to do this in MRS as opposed to making it an additional
field in my querry.
Thanks!I think this is what you are asking...
And There is probably an easier way to do this but in the expression builder
IIF(SUBSTR(sales_code),1,1) = "x", Sum(sales_amt ),"")
Or something along those lines...
Hope that helps
Kerrie
Jimmy V wrote:
>Hey all,
>Quick question, I have a field that I need to sum only when another
>field is a certan value.
>For example, a dataset with 5 fields {row_id, dealer_id, rep_id,
>sales_code, sales_amt} and grouped by dealer_id then rep_id, I would
>want to sum the sales_amt field when the first character of the
>sales_code field is an 'X'. So for the set:
>1, 1, 999, X100, $200.00
>2, 1, 999, 200, $500.00
>3, 1, 999, 898, $1000.00
>4, 1, 555, X340, $2000.00
>5, 1, 555, X444, $23.00
>The resultant sums would be:
>for dealer_id 1: 2223.00
>for rep_id 999: 200.00
>for rep_id 555: 2023.00
>I am trying to do this in MRS as opposed to making it an additional
>field in my querry.
>Thanks!
--
Message posted via http://www.sqlmonster.com|||Kerrie,
I had to create a calculated field and summed it that way, i did use
your code snippit to generate my calculated field.
Thanks!!!|||Glad I could help, That is the best thing i have heard all day.
Thanks!
Jimmy V wrote:
>Kerrie,
>I had to create a calculated field and summed it that way, i did use
>your code snippit to generate my calculated field.
>Thanks!!!
--
Message posted via http://www.sqlmonster.com|||Hi Jimmy,
Easy way of doing is, if u want a sum by Sales order =x...., AND REP_ID
create a group with the sales order =x JUST "X" and u will get the value for
it
and then subtract this one with rest of value.
regards
JERROB
"Jimmy V" wrote:
> Hey all,
> Quick question, I have a field that I need to sum only when another
> field is a certan value.
> For example, a dataset with 5 fields {row_id, dealer_id, rep_id,
> sales_code, sales_amt} and grouped by dealer_id then rep_id, I would
> want to sum the sales_amt field when the first character of the
> sales_code field is an 'X'. So for the set:
> 1, 1, 999, X100, $200.00
> 2, 1, 999, 200, $500.00
> 3, 1, 999, 898, $1000.00
> 4, 1, 555, X340, $2000.00
> 5, 1, 555, X444, $23.00
> The resultant sums would be:
> for dealer_id 1: 2223.00
> for rep_id 999: 200.00
> for rep_id 555: 2023.00
> I am trying to do this in MRS as opposed to making it an additional
> field in my querry.
> Thanks!
>

Thursday, March 8, 2012

Conditional parameters

I have two parameters, one list box generated from a union dataset, that has
an option to generate the report for all suppliers. When this option is
selected the second parameter (a search parameter, based on user input) must
not allow the input of %, can anyone let me know how this can be achieved.
Also can a error message be displayed when the user inputs a %?
Any help greatly anticipated.
Dave.I assume that you are generating reports directly from the
report manager.
I dont know if there is a direct way of acchieving this
but would like to suggest a work around.
Have an additional text box and type in some error text in
it eg : "Please do not enter %" or "Wrong parameters"
Then set the hidden property of this text box to an
expression that validates the parameter value and hides
the textbox if the values entered are correct otherwise
the textbox is visible.
Then for other controls on the report set the same
condition for visibility but in this case make the
controls visible if the parameters entered are correct
otherwise the controls will be hidden.
So if the user enters a wrong value for the second
parameter then only one textbox will appear asking the
user to enter the values again.
Hope this helps!
>--Original Message--
>I have two parameters, one list box generated from a
union dataset, that has
>an option to generate the report for all suppliers. When
this option is
>selected the second parameter (a search parameter, based
on user input) must
>not allow the input of %, can anyone let me know how this
can be achieved.
>Also can a error message be displayed when the user
inputs a %?
>Any help greatly anticipated.
>Dave.
>.
>

Conditional Parameter in Where Clause

I'm trying to figure out a way to filter a dataset using a parameter only when the user enters a value for the parameter and to not apply the filter if the parameter is left blank (or null) by the user. I would like to do this within the WHERE clause of the SELECT statement to minimize the size of the dataset whenever possible. Is there such a thing as a default parameter value that equates to "any value"?

Nothing I have tried works (but I'm new to SQL, Report Server and the Visual Basic Development Environment).

Thanks in advance,

Chris

Rather than leaving the parameter unselected, you need to add an option with a value of NULL and text that matches your scenario e.g. blank, "All", "N/A", "Unspecified" etc. To do this you'll need to modify the query for the paramter dataset to:

SELECT id = NULL, name = 'All'
UNION ALL
SELECT id, name
FROM param_table

Then update your main query with the following WHERE clause

WHERE id = ISNULL(@.param, id)

so when the null option is selected the WHERE clause equates to id=id which is always true and hence all rows are returned.

Hope this helps.

|||

Thanks Adam,

I was not familiar with ISNULL. I got it to work sort of like I wanted it to by checking the "Allow Null Value" checkbox and making the default value NULL in the Report Parameters dialog box and then putting this in the WHERE clause:

WHERE LITEM.SIZE = ISNULL(@.Input_Size, LITEM.SIZE)

However, I could not figure out where to put the following statement (everything I tried resulted in an error - but I'm probably missing something obvious):

SELECT id = NULL, name = 'All'
UNION ALL
SELECT id, name
FROM param_table

...and therefore, the user must uncheck the NULL checkbox in order to enter a filter value and it's not real obvious that when NULL is checked, that the filter is not applied.

Thanks again for pointing me in the right direction!

Chris

|||

By your response it seems like your parameter is a textbox the user types into, is that correct?

My prerred way is to present the user a list of options i.e. a dropdown. In that case you don't get a null checkbox. The options in the dropdown can either be typed in on the paramter screen or can come from a dataset. The SELECT statement I provided is meant as an example of query used to populate such a dataset i.e. it includes a NULL option.

If you wish to use a textbox then you could alter your SQL query and rather than using ISNULL you could use an OR in your WHERE clause as follows

WHERE LITEM.SIZE = @.Input_Size
OR @.Input_Size = '' -- empty string

If LITEM.SIZE and @.Input_Size are integers then it gets a little more complicated. You'll need to experiment.

|||

Adam,

Thanks! It's now working just the way I wanted it to!

Chris Heitman

Conditional Image Visibility with Embedded code

I am trying to create a embedded function that will accept a dataset field
value and based on the value, make an image visibility hidden or visible in
the body of the report. I receive an error that the mathimage is not
declared. How do I reference an image in the body of the report. The field is
within a list and based on the subject an image is displayed in the bodiy of
the report.
Below is a sample of the embedded code.
Function VisibleYN(subject as string,passedyn as boolean) as double
if subject = "Math" and passedyn = true then
mathimage.visibility = visible
end if
if subject = "Science" and passedyn = true then
scienceimage.visibility = visible
end if
THe following expression is in the visibility property tab of the textbox
that holds the subject.
code.visibleyn(fields!subject.value as string, passedyn as boolean)
Any help would be appreciated!
--
MarieThe actual property is under the visibility and is named "IsHidden", so your
function should return a boolean rather than try and set a property.
Change
mathimage.visibility = visible
to
Return False
and likewise for your science one to get it to work.
"Marie" wrote:
> I am trying to create a embedded function that will accept a dataset field
> value and based on the value, make an image visibility hidden or visible in
> the body of the report. I receive an error that the mathimage is not
> declared. How do I reference an image in the body of the report. The field is
> within a list and based on the subject an image is displayed in the bodiy of
> the report.
> Below is a sample of the embedded code.
> Function VisibleYN(subject as string,passedyn as boolean) as double
> if subject = "Math" and passedyn = true then
> mathimage.visibility = visible
> end if
> if subject = "Science" and passedyn = true then
> scienceimage.visibility = visible
> end if
> THe following expression is in the visibility property tab of the textbox
> that holds the subject.
> code.visibleyn(fields!subject.value as string, passedyn as boolean)
> Any help would be appreciated!
> --
> Marie|||The reason I am trying to set the property is because the function is being
called from a textbox bound to a field. Based on the value in the field I am
setting the property of a checkmark image. If I return the boolean value how
do I use this value to set the property of another report item? I was hoping
I could do something like Reportitem.image1.ishidden = false.
Any suggestions?
Thanks for your help.
--
Marie
"David Swanson" wrote:
> The actual property is under the visibility and is named "IsHidden", so your
> function should return a boolean rather than try and set a property.
> Change
> mathimage.visibility = visible
> to
> Return False
> and likewise for your science one to get it to work.
> "Marie" wrote:
> > I am trying to create a embedded function that will accept a dataset field
> > value and based on the value, make an image visibility hidden or visible in
> > the body of the report. I receive an error that the mathimage is not
> > declared. How do I reference an image in the body of the report. The field is
> > within a list and based on the subject an image is displayed in the bodiy of
> > the report.
> >
> > Below is a sample of the embedded code.
> >
> > Function VisibleYN(subject as string,passedyn as boolean) as double
> > if subject = "Math" and passedyn = true then
> > mathimage.visibility = visible
> > end if
> > if subject = "Science" and passedyn = true then
> > scienceimage.visibility = visible
> >
> > end if
> >
> > THe following expression is in the visibility property tab of the textbox
> > that holds the subject.
> >
> > code.visibleyn(fields!subject.value as string, passedyn as boolean)
> >
> > Any help would be appreciated!
> >
> > --
> > Marie|||You can use an expression for the hidden property of the image in the report.
Right click on the image, expand Visibility, then use this in the Hidden
property
=TextBox1.Value="Math"
or
=iif(textBox1.Value="Math",true,false)
I was looking for something similar the other day, and I found the custom
code is a little quirky because I could not find a way to access control
members via code.
When I passed Me as an object, I got some interesting results. For example,
try
this:
function Test(me as object) as boolean
msgbox("String=" & Me.ToString)
return false
end sub
TextBox9.Value=Code.Test(me)
Check out the title of the msgbox itself! It looks like there is funcky
name mangling going on in the host, which prevents us from using objects
directly in custom code.
msgbox("Name=" & Me.Name) indicates a public member not available, so it
appears that unless there is a reflection trick, I have yet to find a way to
access member variables of a report control in custom code.
I'll keep an eye on this thread, and I hope this is of some help to you..
Dwayne

Wednesday, March 7, 2012

Conditional formatting based on second dataset

I am trying to create a report which has conditional formatting.

The primary dataset is a view of objects with several values

eg

object1,0,4,0,1

object2,0,3,1,1

The secondary dataset is the comparison table and just contains the values

eg

0,3,1,1

I'd like to conditionally format the values based on the comparison table but when I create an expression comparing to the second dateset

eg

=iif(Fields!object1.Value <> Fields!comp_object1.Value , "Red", "SkyBlue")

i get

Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Not sure if there is a way to tell the expression to look for comp_object1 in dataset2, even tho it is uniquely named?

Ideas gratefully received !

Yes, I'm pretty sure you can do what you want.

I'll assume that the fields in dataset2 are named comp_object1, comp_object2, etc. I will also assume that dataset2 has one row -- because if it has multiple rows to match dataset1's rows, you should put these columns in the *same* dataset.

In the table based on the first dataset, you can use expressions like this for column #1 -- and I'm pretty sure the scope is case-sensitive, so you may have to play with what I'm suggesting here

Code Snippet

=iif(Fields!object1.Value <> First(Fields!comp_object1.Value, "dataset2") , "Red", "SkyBlue")

To use the expression builder to do this, notice that on the left side there is a "Datasets" item in the list. If you select it, then the middle list has your datasets listed. Click on dataset2 in the middle list and you will see the appropriate items in your right-side list of possible fields...

>L<

|||Thanks, that worked!|||

hi,

this is works perfactly........ but i have another issue that i want to display 2 different dataset's fields in a single table. i can put 2nd database's field in table but it can put only with aggregate functions like (first, last...etc.) but i want to put it directly.........bcz if i put it with first function i can't get other records............. can u help me?

|||

Basically, except for aggregates that tell it what to display, the processing engine has no way to figure out what record from the other dataset to display in each row <s>.

You can either write a query that combines the data into one dataset, so that you provide the relationship yourself, or you can use a subreport, passing a parameter from the parent dataset that indicates what data to display in the subreport.

Alternatively you can probably do something in code to pull the data from the other dataset (I haven't tried this because I don't really see the point, but it would probably work) .

You can also nest a data region (another table or list or matrix) inside a group row for a table -- and this other data region has its own dataset -- but I gather this isn't what you have in mind.

>L<

|||

hi lisa,

Thx a lot for suggestion..............i have tried to put another data region in table with aggregate function but still its not works and gives error........ do u have any otherway?

|||

Well, I'm not sure I understand exactly what you tried so I can't explain or help <s>.

Tell us about your two datasets.

Tell us what data region you put in, and where exactly you put it, etc.

Tell us what the error was...

>L<

Conditional formatting based on second dataset

I am trying to create a report which has conditional formatting.

The primary dataset is a view of objects with several values

eg

object1,0,4,0,1

object2,0,3,1,1

The secondary dataset is the comparison table and just contains the values

eg

0,3,1,1

I'd like to conditionally format the values based on the comparison table but when I create an expression comparing to the second dateset

eg

=iif(Fields!object1.Value <> Fields!comp_object1.Value , "Red", "SkyBlue")

i get

Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Not sure if there is a way to tell the expression to look for comp_object1 in dataset2, even tho it is uniquely named?

Ideas gratefully received !

Yes, I'm pretty sure you can do what you want.

I'll assume that the fields in dataset2 are named comp_object1, comp_object2, etc. I will also assume that dataset2 has one row -- because if it has multiple rows to match dataset1's rows, you should put these columns in the *same* dataset.

In the table based on the first dataset, you can use expressions like this for column #1 -- and I'm pretty sure the scope is case-sensitive, so you may have to play with what I'm suggesting here

Code Snippet

=iif(Fields!object1.Value <> First(Fields!comp_object1.Value, "dataset2") , "Red", "SkyBlue")

To use the expression builder to do this, notice that on the left side there is a "Datasets" item in the list. If you select it, then the middle list has your datasets listed. Click on dataset2 in the middle list and you will see the appropriate items in your right-side list of possible fields...

>L<

|||Thanks, that worked!|||

hi,

this is works perfactly........ but i have another issue that i want to display 2 different dataset's fields in a single table. i can put 2nd database's field in table but it can put only with aggregate functions like (first, last...etc.) but i want to put it directly.........bcz if i put it with first function i can't get other records............. can u help me?

|||

Basically, except for aggregates that tell it what to display, the processing engine has no way to figure out what record from the other dataset to display in each row <s>.

You can either write a query that combines the data into one dataset, so that you provide the relationship yourself, or you can use a subreport, passing a parameter from the parent dataset that indicates what data to display in the subreport.

Alternatively you can probably do something in code to pull the data from the other dataset (I haven't tried this because I don't really see the point, but it would probably work) .

You can also nest a data region (another table or list or matrix) inside a group row for a table -- and this other data region has its own dataset -- but I gather this isn't what you have in mind.

>L<

|||

hi lisa,

Thx a lot for suggestion..............i have tried to put another data region in table with aggregate function but still its not works and gives error........ do u have any otherway?

|||

Well, I'm not sure I understand exactly what you tried so I can't explain or help <s>.

Tell us about your two datasets.

Tell us what data region you put in, and where exactly you put it, etc.

Tell us what the error was...

>L<

Saturday, February 25, 2012

Conditional Count in Detail Row - Count results across columns

Hi,
I am trying to establish a way to count the number of True/False entries
across a number of columns. That is I have a dataset returning results of
organisations (rows) and whether they have opted for a particular criteria
(columns). I want to know how many of the criteria a particular organisation
has opted for and display that on the end of the detail row for each
organisation.
Org Name Criteria1 Criteria2 Criteria3 Total Selected
ABC Ltd Yes No Yes 2
LMO Ltd No No Yes 1
XYZ Ltd Yes Yes Yes 3
Totals 2 1 3
I have managed the Totals in the footer using a conditional count.
eg =Count(iif(Fields!cf_subindicator111name.Value = "Yes",
Fields!cf_subindicator111name.Value, Nothing))
I am unsure of how to do the same thing at the end of the detail line (Total
Selected) for a conditional count of the discreet fields in the dataset. I
have 89 Criteria in all to count.
I have a feeling that this needs to be done at the SQL query level creating
a running total for each organisation returned where they have opted Yes for
a Criteria but I don't know where to start with this.
Hope this is clear.
Any ideas?
SimonHi,
You were right :). You should do it in your stored procedure. Maybe you
should use PIVOT operator ans aggregation function, like COUNT. Also, you
should try the RunningValue function in SSRS in worst case scenario.
Regards,
Janos
"Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
news:17CF23B8-E99A-4D2F-9947-9AC264D09CCF@.microsoft.com...
> Hi,
> I am trying to establish a way to count the number of True/False entries
> across a number of columns. That is I have a dataset returning results of
> organisations (rows) and whether they have opted for a particular criteria
> (columns). I want to know how many of the criteria a particular
> organisation
> has opted for and display that on the end of the detail row for each
> organisation.
> Org Name Criteria1 Criteria2 Criteria3 Total Selected
> ABC Ltd Yes No Yes 2
> LMO Ltd No No Yes 1
> XYZ Ltd Yes Yes Yes 3
> Totals 2 1 3
> I have managed the Totals in the footer using a conditional count.
> eg =Count(iif(Fields!cf_subindicator111name.Value = "Yes",
> Fields!cf_subindicator111name.Value, Nothing))
> I am unsure of how to do the same thing at the end of the detail line
> (Total
> Selected) for a conditional count of the discreet fields in the dataset. I
> have 89 Criteria in all to count.
> I have a feeling that this needs to be done at the SQL query level
> creating
> a running total for each organisation returned where they have opted Yes
> for
> a Criteria but I don't know where to start with this.
> Hope this is clear.
> Any ideas?
> Simon|||Thanks Janos.
The issue here is I am unclear on the specifics of how to do this, where it
sits in the select statement and what the format would be. I am only just
managing to get the select, from and where statements working at the moment
:) New to Reporting Services and SQL and could do with some pointers. I will
look up the Pivot operator and see if that gets me anywhere. Any pointers
would be appreciated.
Simon
"BERKE Janos" wrote:
> Hi,
> You were right :). You should do it in your stored procedure. Maybe you
> should use PIVOT operator ans aggregation function, like COUNT. Also, you
> should try the RunningValue function in SSRS in worst case scenario.
> Regards,
> Janos
> "Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
> news:17CF23B8-E99A-4D2F-9947-9AC264D09CCF@.microsoft.com...
> > Hi,
> >
> > I am trying to establish a way to count the number of True/False entries
> > across a number of columns. That is I have a dataset returning results of
> > organisations (rows) and whether they have opted for a particular criteria
> > (columns). I want to know how many of the criteria a particular
> > organisation
> > has opted for and display that on the end of the detail row for each
> > organisation.
> >
> > Org Name Criteria1 Criteria2 Criteria3 Total Selected
> >
> > ABC Ltd Yes No Yes 2
> > LMO Ltd No No Yes 1
> > XYZ Ltd Yes Yes Yes 3
> >
> > Totals 2 1 3
> >
> > I have managed the Totals in the footer using a conditional count.
> >
> > eg =Count(iif(Fields!cf_subindicator111name.Value = "Yes",
> > Fields!cf_subindicator111name.Value, Nothing))
> >
> > I am unsure of how to do the same thing at the end of the detail line
> > (Total
> > Selected) for a conditional count of the discreet fields in the dataset. I
> > have 89 Criteria in all to count.
> >
> > I have a feeling that this needs to be done at the SQL query level
> > creating
> > a running total for each organisation returned where they have opted Yes
> > for
> > a Criteria but I don't know where to start with this.
> >
> > Hope this is clear.
> >
> > Any ideas?
> >
> > Simon
>|||Hi Simon,
Post here a similar table structure to me, and I'll write the code for you
;). I will add some comments to my code as well.
Regards,
Janos
"Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
news:0C17ED91-B21D-4805-9B65-A200BBA595B4@.microsoft.com...
> Thanks Janos.
> The issue here is I am unclear on the specifics of how to do this, where
> it
> sits in the select statement and what the format would be. I am only just
> managing to get the select, from and where statements working at the
> moment
> :) New to Reporting Services and SQL and could do with some pointers. I
> will
> look up the Pivot operator and see if that gets me anywhere. Any pointers
> would be appreciated.
> Simon
> "BERKE Janos" wrote:
>> Hi,
>> You were right :). You should do it in your stored procedure. Maybe you
>> should use PIVOT operator ans aggregation function, like COUNT. Also, you
>> should try the RunningValue function in SSRS in worst case scenario.
>> Regards,
>> Janos
>> "Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
>> news:17CF23B8-E99A-4D2F-9947-9AC264D09CCF@.microsoft.com...
>> > Hi,
>> >
>> > I am trying to establish a way to count the number of True/False
>> > entries
>> > across a number of columns. That is I have a dataset returning results
>> > of
>> > organisations (rows) and whether they have opted for a particular
>> > criteria
>> > (columns). I want to know how many of the criteria a particular
>> > organisation
>> > has opted for and display that on the end of the detail row for each
>> > organisation.
>> >
>> > Org Name Criteria1 Criteria2 Criteria3 Total Selected
>> >
>> > ABC Ltd Yes No Yes 2
>> > LMO Ltd No No Yes 1
>> > XYZ Ltd Yes Yes Yes 3
>> >
>> > Totals 2 1 3
>> >
>> > I have managed the Totals in the footer using a conditional count.
>> >
>> > eg =Count(iif(Fields!cf_subindicator111name.Value = "Yes",
>> > Fields!cf_subindicator111name.Value, Nothing))
>> >
>> > I am unsure of how to do the same thing at the end of the detail line
>> > (Total
>> > Selected) for a conditional count of the discreet fields in the
>> > dataset. I
>> > have 89 Criteria in all to count.
>> >
>> > I have a feeling that this needs to be done at the SQL query level
>> > creating
>> > a running total for each organisation returned where they have opted
>> > Yes
>> > for
>> > a Criteria but I don't know where to start with this.
>> >
>> > Hope this is clear.
>> >
>> > Any ideas?
>> >
>> > Simon|||Janos,
Hi, not sure what you mean but my current query is as follows.
SELECT CRMAF_FilteredIncident.customeridname,
CRMAF_FilteredIncident.incidentid,
CRMAF_FilteredCF_Assessment.cf_casenumberid,
CRMAF_FilteredCF_Assessment.cf_applicationsubtypename,
CRMAF_FilteredCF_Assessment.cf_applicationtypename,
CRMAF_FilteredCF_Assessment.cf_subindicator111name,
CRMAF_FilteredCF_Assessment.cf_subindicator112name,
CRMAF_FilteredCF_Assessment.cf_subindicator121name,
CRMAF_FilteredCF_Assessment.cf_subindicator122name,
CRMAF_FilteredCF_Assessment.cf_subindicator123name,
CRMAF_FilteredCF_Assessment.cf_subindicator131name,
CRMAF_FilteredCF_Assessment.cf_subindicator141name,
CRMAF_FilteredCF_Assessment.cf_subindicator211name,
CRMAF_FilteredCF_Assessment.cf_subindicator221name,
CRMAF_FilteredCF_Assessment.cf_subindicator231name,
CRMAF_FilteredCF_Assessment.cf_subindicator232name,
CRMAF_FilteredCF_Assessment.cf_subindicator233name,
CRMAF_FilteredCF_Assessment.cf_subindicator241name,
CRMAF_FilteredCF_Assessment.cf_subindicator242name,
CRMAF_FilteredCF_Assessment.cf_subindicator243name,
CRMAF_FilteredCF_Assessment.cf_subindicator244name,
CRMAF_FilteredCF_Assessment.cf_subindicator245name,
CRMAF_FilteredCF_Assessment.cf_subindicator251name,
CRMAF_FilteredCF_Assessment.cf_subindicator252name,
CRMAF_FilteredCF_Assessment.cf_subindicator311name,
CRMAF_FilteredCF_Assessment.cf_subindicator312name,
CRMAF_FilteredCF_Assessment.cf_subindicator313name,
CRMAF_FilteredCF_Assessment.cf_subindicator321name,
CRMAF_FilteredCF_Assessment.cf_subindicator322name,
CRMAF_FilteredCF_Assessment.cf_subindicator323name,
CRMAF_FilteredCF_Assessment.cf_subindicator331name,
CRMAF_FilteredCF_Assessment.cf_subindicator332name,
CRMAF_FilteredCF_Assessment.cf_subindicator341name,
CRMAF_FilteredCF_Assessment.cf_subindicator342name,
CRMAF_FilteredCF_Assessment.cf_assessmenttypename,
CRMAF_FilteredCF_Assessment.cf_subindicator411name,
CRMAF_FilteredCF_Assessment.cf_subindicator421name,
CRMAF_FilteredCF_Assessment.cf_subindicator422name,
CRMAF_FilteredCF_Assessment.cf_subindicator423name,
CRMAF_FilteredCF_Assessment.cf_subindicator431name,
CRMAF_FilteredCF_Assessment.cf_subindicator441name,
CRMAF_FilteredCF_Assessment.cf_subindicator511name,
CRMAF_FilteredCF_Assessment.cf_subindicator512name,
CRMAF_FilteredCF_Assessment.cf_subindicator513name,
CRMAF_FilteredCF_Assessment.cf_subindicator514name,
CRMAF_FilteredCF_Assessment.cf_subindicator521name,
CRMAF_FilteredCF_Assessment.cf_subindicator522name,
CRMAF_FilteredCF_Assessment.cf_subindicator523name,
CRMAF_FilteredCF_Assessment.cf_subindicator524name,
CRMAF_FilteredCF_Assessment.cf_subindicator525name,
CRMAF_FilteredCF_Assessment.cf_subindicator6110name,
CRMAF_FilteredCF_Assessment.cf_subindicator6111name,
CRMAF_FilteredCF_Assessment.cf_subindicator611name,
CRMAF_FilteredCF_Assessment.cf_subindicator612name,
CRMAF_FilteredCF_Assessment.cf_subindicator613name,
CRMAF_FilteredCF_Assessment.cf_subindicator614name,
CRMAF_FilteredCF_Assessment.cf_subindicator615name,
CRMAF_FilteredCF_Assessment.cf_subindicator616name,
CRMAF_FilteredCF_Assessment.cf_subindicator617name,
CRMAF_FilteredCF_Assessment.cf_subindicator618name,
CRMAF_FilteredCF_Assessment.cf_subindicator619name,
CRMAF_FilteredCF_Assessment.cf_subindicator621name,
CRMAF_FilteredCF_Assessment.cf_subindicator622name,
CRMAF_FilteredCF_Assessment.cf_subindicator623name,
CRMAF_FilteredCF_Assessment.cf_subindicator624name,
CRMAF_FilteredCF_Assessment.cf_subindicator625name,
CRMAF_FilteredCF_Assessment.cf_subindicator626name,
CRMAF_FilteredCF_Assessment.cf_subindicator631name,
CRMAF_FilteredCF_Assessment.cf_subindicator632name,
CRMAF_FilteredCF_Assessment.cf_subindicator633name,
CRMAF_FilteredCF_Assessment.cf_subindicator634name,
CRMAF_FilteredCF_Assessment.cf_subindicator641name,
CRMAF_FilteredCF_Assessment.cf_subindicator642name,
CRMAF_FilteredCF_Assessment.cf_subindicator643name,
CRMAF_FilteredCF_Assessment.cf_subindicator651name,
CRMAF_FilteredCF_Assessment.cf_subindicator652name,
CRMAF_FilteredCF_Assessment.cf_subindicator711name,
CRMAF_FilteredCF_Assessment.cf_subindicator712name,
CRMAF_FilteredCF_Assessment.cf_subindicator713name,
CRMAF_FilteredCF_Assessment.cf_subindicator714name,
CRMAF_FilteredCF_Assessment.cf_subindicator721name,
CRMAF_FilteredCF_Assessment.cf_subindicator731name,
CRMAF_FilteredCF_Assessment.cf_subindicator741name,
CRMAF_FilteredCF_Assessment.cf_subindicator742name,
CRMAF_FilteredCF_Assessment.cf_subindicator743name,
CRMAF_FilteredCF_Assessment.cf_subindicator811name,
CRMAF_FilteredCF_Assessment.cf_subindicator812name,
CRMAF_FilteredCF_Assessment.cf_subindicator821name,
CRMAF_FilteredCF_Assessment.cf_subindicator911name,
CRMAF_FilteredCF_Assessment.cf_subindicator921name,
CRMAF_FilteredCF_Assessment.cf_subindicator931name,
CRMAF_FilteredCF_Assessment.cf_subindicator941name,
CRMAF_FilteredCF_Assessment.cf_subindicator951name,
CRMAF_FilteredCF_Assessment.cf_subindicator961name,
CRMAF_FilteredIncident.cf_casesubtypename
FROM FilteredCF_Assessment AS CRMAF_FilteredCF_Assessment INNER JOIN
FilteredIncident AS CRMAF_FilteredIncident ON
CRMAF_FilteredCF_Assessment.cf_casenumberid =CRMAF_FilteredIncident.incidentid
WHERE (CRMAF_FilteredIncident.cf_casesubtypename = 'Assessment')
What I want to do is a conditional count where the subindicator fields are
equal to yes and be able to total those at the end of each row.
Thanks.
"BERKE Janos" wrote:
> Hi Simon,
> Post here a similar table structure to me, and I'll write the code for you
> ;). I will add some comments to my code as well.
> Regards,
> Janos
>
> "Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
> news:0C17ED91-B21D-4805-9B65-A200BBA595B4@.microsoft.com...
> > Thanks Janos.
> >
> > The issue here is I am unclear on the specifics of how to do this, where
> > it
> > sits in the select statement and what the format would be. I am only just
> > managing to get the select, from and where statements working at the
> > moment
> > :) New to Reporting Services and SQL and could do with some pointers. I
> > will
> > look up the Pivot operator and see if that gets me anywhere. Any pointers
> > would be appreciated.
> >
> > Simon
> >
> > "BERKE Janos" wrote:
> >
> >> Hi,
> >>
> >> You were right :). You should do it in your stored procedure. Maybe you
> >> should use PIVOT operator ans aggregation function, like COUNT. Also, you
> >> should try the RunningValue function in SSRS in worst case scenario.
> >>
> >> Regards,
> >>
> >> Janos
> >>
> >> "Simon W3st" <SimonW3st@.discussions.microsoft.com> wrote in message
> >> news:17CF23B8-E99A-4D2F-9947-9AC264D09CCF@.microsoft.com...
> >> > Hi,
> >> >
> >> > I am trying to establish a way to count the number of True/False
> >> > entries
> >> > across a number of columns. That is I have a dataset returning results
> >> > of
> >> > organisations (rows) and whether they have opted for a particular
> >> > criteria
> >> > (columns). I want to know how many of the criteria a particular
> >> > organisation
> >> > has opted for and display that on the end of the detail row for each
> >> > organisation.
> >> >
> >> > Org Name Criteria1 Criteria2 Criteria3 Total Selected
> >> >
> >> > ABC Ltd Yes No Yes 2
> >> > LMO Ltd No No Yes 1
> >> > XYZ Ltd Yes Yes Yes 3
> >> >
> >> > Totals 2 1 3
> >> >
> >> > I have managed the Totals in the footer using a conditional count.
> >> >
> >> > eg =Count(iif(Fields!cf_subindicator111name.Value = "Yes",
> >> > Fields!cf_subindicator111name.Value, Nothing))
> >> >
> >> > I am unsure of how to do the same thing at the end of the detail line
> >> > (Total
> >> > Selected) for a conditional count of the discreet fields in the
> >> > dataset. I
> >> > have 89 Criteria in all to count.
> >> >
> >> > I have a feeling that this needs to be done at the SQL query level
> >> > creating
> >> > a running total for each organisation returned where they have opted
> >> > Yes
> >> > for
> >> > a Criteria but I don't know where to start with this.
> >> >
> >> > Hope this is clear.
> >> >
> >> > Any ideas?
> >> >
> >> > Simon
> >>
>

Friday, February 24, 2012

Condition (if / else) in SQL?

Hi all,

I'm building a DataSet on Visual Studio and don't know how to do a condition (if/else) with SQL...

I have a search form, with a DropDownList and have 2 options in it: Search by Title or Search by Author. If the "Title" is selected, then the value is "title and if "Author" is selected, then the value is "author".

Here is what I have right now for the DataSet, as seperated queries but I think I can combine them to be one single query

1.This will returns the songs that matches the title:

SELECT LYRICS_PK, LYRICS_TITLE, LYRICS_TITLE2, LYRICS_WRITER, LYRICS_WRITER2, LYRICS_COWRITER, LYRICS_DATE_ADDED, UserId_FK,
LYRICS_APPROVED, LYRICS_TYPE, LYRICS_VIEWS, LYRICS_ADDED_BY
FROM t_lyrics

WHERE ((@.LYRICS_TITLE IS NULL) OR (LYRICS_TITLE LIKE '%' + @.LYRICS_TITLE + '%') OR (LYRICS_TITLE2 LIKE '%' + @.LYRICS_TITLE + '%')) AND (@.LYRICS_TYPE = 'title')

2. This returns the songs that matches the author:

SELECT LYRICS_PK, LYRICS_TITLE, LYRICS_TITLE2, LYRICS_WRITER,LYRICS_WRITER2, LYRICS_COWRITER, LYRICS_DATE_ADDED, UserId_FK,
LYRICS_APPROVED, LYRICS_TYPE, LYRICS_VIEWS, LYRICS_ADDED_BY
FROM t_lyrics

WHERE ((@.LYRICS_AUTHOR IS NULL) OR (LYRICS_AUTHOR LIKE '%' +@.LYRICS_AUTHOR + '%') OR (LYRICS_AUTHOR2 LIKE '%' + @.LYRICS_AUTHOR + '%'))AND (@.LYRICS_TYPE = 'author')

This is very inefficient because I have 2 queries, and I need to build 2 ObjectDataSources as well as 2 different GridViews to display the results. I think we can do something like

SELECT ... ...

FROM t_lyrics

if (@.LYRICS_TYPE = 'title')

DO THE WHERE CLAUSE THAT RETURNS MATCHES WITH TITLE

else if (@.LYRICS_TYPE = 'author')

DO THE WHERE CLAUSE THAT RETURNS MATCHES WITH AUTHOR

But I don't know how to write that in T-SQL.

Any help would be greatly appreciated,

Thank you very much,

Kenny.

Example of a simple sp with if else, you can tune your queries in that line i guess:

ifexists (select *fromsysobjectswhere id =object_id ('sp_test'))drop proceduresp_testgocreate proceduresp_test@.typevarchar(1)asif (@.type ='A')beginprint'A was the entered type'returnend elsebeginprint'something else was the type'returnend
|||

Does anyone know how to write it in the query and not as SP?

Thank you very much,

Kenny.

|||

You can use SQL case statements for conditional clauses, too...
http://www.4guysfromrolla.com/webtech/102704-1.shtml

|||

Does anyone know how to write it in the query and not as SP?

if (@.type ='A')beginprint'A was the entered type'returnend elsebeginprint'something else was the type'returnend

Friday, February 17, 2012

Concurrent access problem...

Hi All
I am developing portfolio management application. In this application i
am fetching data from database and populate to dataset and that dataset
is getting binded to the datagrid... it is multiuse application and one
portfolio is getting accessed by more than one user. somehow i want to
make sure that one transaction can be updated by only one person. no
more than one person should be allowed to update one transaction... can
anyone suggest me good approach to solve this problem
thanks
Deep OceanOne simple approach would be to have a timestamp or datetime column in the
table .
Between the Retrieval and Save of the data - check for the datetime column
to have same value, which mean no other user has updated the record. If the
column values are not same between retrieval and save then return an error
message to the user that the record has already been updated by some other
user and user need to refresh the screen to do a frech update.
"Deep Silent Ocean" wrote:

> Hi All
>
> I am developing portfolio management application. In this application i
> am fetching data from database and populate to dataset and that dataset
> is getting binded to the datagrid... it is multiuse application and one
> portfolio is getting accessed by more than one user. somehow i want to
> make sure that one transaction can be updated by only one person. no
> more than one person should be allowed to update one transaction... can
> anyone suggest me good approach to solve this problem
> thanks
> Deep Ocean
>|||Is this a web application using a disconnected ADO.NET dataset or a client
server type application using a connected ADO recordset?
For ADO recordsets bound to a grid (ex: Visual Basic or MS Office), it is a
matter of specifying the LockType property of the recordset to optimistic or
pessimistic.
http://msdn.microsoft.com/library/d.../>
ocktype.asp
http://msdn.microsoft.com/library/d...ocktypeenum.asp
http://msdn.microsoft.com/library/d...
ursortypex.asp
In web application, data changes are made to an ADO.NET database indirectly
via a disconnected dataset, so the issues are different. Here is an
excellent article on handling concurrency issues in a web application.
http://msdn.microsoft.com/msdnmag/i.../09/DataPoints/
Pessimistic locking can sometimes inadvertently prevent users from
performing otherwise harmless operations, so I favor the method of using a
timestamp column and then giving the user the flexibility of choosing
whether or not to save their changes over another edit. However, this should
be rarely needed. It's also more of a business process issue than a
technical issue. Generally speaking, an account, invoice, portfolio, etc.
should be assigned to a specific account manager. It would seem confusing to
have multiple people working the same customer, but perhaps you mean
something different by "portfolio".
"Deep Silent Ocean" <ocean.indian@.gmail.com> wrote in message
news:O2tvKf9vFHA.908@.tk2msftngp13.phx.gbl...
> Hi All
>
> I am developing portfolio management application. In this application i am
> fetching data from database and populate to dataset and that dataset is
> getting binded to the datagrid... it is multiuse application and one
> portfolio is getting accessed by more than one user. somehow i want to
> make sure that one transaction can be updated by only one person. no more
> than one person should be allowed to update one transaction... can anyone
> suggest me good approach to solve this problem
> thanks
> Deep Ocean

Friday, February 10, 2012

Concatenating Strings

Suppose that I get a resultset of state abbreviations from dataset. Is there a way to write a code so that final output would be concatenation of states separated by a ",".

I tried maintaining a variable in the "Code" block of the report. Apparently, the delcarations do not work as expected.

I want to do something like this:

Dim str As String

and then str should be updated as each row gets rendered. Is that possible?

Thanks.

You may want to read this blog article about "custom aggregates": http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

-- Robert

Concatenating rows into one field for summary

Hi,
I am looking for a way of putting multiple values from different rows into
one field, for example:
The dataset returns 3 rows, with two fields, first name and surname like
below:
FirstName Surname
Joe Bloggs
David Beckham
Sue Smith
I want to get all of those surnames, and put them say into the final totals
of the report so they look like Bloggs,Beckham,Smith.
Can anyone see a way of archiving this.
Cheers
LukeYou cant do it in the same query because you will be using the first name if
not then you can use this method. what you can do is to create a seperate
dataset with the following method and refer this dataset in your report in
final totals
select @.aa = COALESCE(surnames + ', ', '') from ..... etc.. etc...ofcourse
you
need to fillup. :-)
any problem let me know.
Amarnath
"lukethepunk" wrote:
> Hi,
> I am looking for a way of putting multiple values from different rows into
> one field, for example:
> The dataset returns 3 rows, with two fields, first name and surname like
> below:
> FirstName Surname
> Joe Bloggs
> David Beckham
> Sue Smith
> I want to get all of those surnames, and put them say into the final totals
> of the report so they look like Bloggs,Beckham,Smith.
> Can anyone see a way of archiving this.
> Cheers
> Luke
>|||Hi,
Thanks thats almost got me what i want (i'd never heard of the COALESCE
function!)
The only problem i've got now is there are duplicate values ending up in the
end string, eg: Bloggs, Bloggs, Smith, Beckham, Beckham
Is there anyway i can keep them out using the sql, or will i have to use
some custom code in the report to keep them uniquie?
Cheers
Luke
"Amarnath" wrote:
> You cant do it in the same query because you will be using the first name if
> not then you can use this method. what you can do is to create a seperate
> dataset with the following method and refer this dataset in your report in
> final totals
> select @.aa = COALESCE(surnames + ', ', '') from ..... etc.. etc...ofcourse
> you
> need to fillup. :-)
> any problem let me know.
> Amarnath
>
> "lukethepunk" wrote:
> > Hi,
> >
> > I am looking for a way of putting multiple values from different rows into
> > one field, for example:
> >
> > The dataset returns 3 rows, with two fields, first name and surname like
> > below:
> >
> > FirstName Surname
> > Joe Bloggs
> > David Beckham
> > Sue Smith
> >
> > I want to get all of those surnames, and put them say into the final totals
> > of the report so they look like Bloggs,Beckham,Smith.
> >
> > Can anyone see a way of archiving this.
> >
> > Cheers
> > Luke
> >|||Hi luke,
unfortunetly there is no such thing like distinct coalesce.. May be what you
can do is to take a distinct and then pass the result set to coalesce. Pl try.
Amarnath
"lukethepunk" wrote:
> Hi,
> Thanks thats almost got me what i want (i'd never heard of the COALESCE
> function!)
> The only problem i've got now is there are duplicate values ending up in the
> end string, eg: Bloggs, Bloggs, Smith, Beckham, Beckham
> Is there anyway i can keep them out using the sql, or will i have to use
> some custom code in the report to keep them uniquie?
> Cheers
> Luke
>
> "Amarnath" wrote:
> > You cant do it in the same query because you will be using the first name if
> > not then you can use this method. what you can do is to create a seperate
> > dataset with the following method and refer this dataset in your report in
> > final totals
> > select @.aa = COALESCE(surnames + ', ', '') from ..... etc.. etc...ofcourse
> > you
> > need to fillup. :-)
> >
> > any problem let me know.
> >
> > Amarnath
> >
> >
> >
> > "lukethepunk" wrote:
> >
> > > Hi,
> > >
> > > I am looking for a way of putting multiple values from different rows into
> > > one field, for example:
> > >
> > > The dataset returns 3 rows, with two fields, first name and surname like
> > > below:
> > >
> > > FirstName Surname
> > > Joe Bloggs
> > > David Beckham
> > > Sue Smith
> > >
> > > I want to get all of those surnames, and put them say into the final totals
> > > of the report so they look like Bloggs,Beckham,Smith.
> > >
> > > Can anyone see a way of archiving this.
> > >
> > > Cheers
> > > Luke
> > >|||Hi,
I have managed to get it working by passing the results to some custom code,
and looping through the string and sending back only one of each.
Thanks for your help
Luke
"Amarnath" wrote:
> Hi luke,
> unfortunetly there is no such thing like distinct coalesce.. May be what you
> can do is to take a distinct and then pass the result set to coalesce. Pl try.
> Amarnath
> "lukethepunk" wrote:
> > Hi,
> >
> > Thanks thats almost got me what i want (i'd never heard of the COALESCE
> > function!)
> >
> > The only problem i've got now is there are duplicate values ending up in the
> > end string, eg: Bloggs, Bloggs, Smith, Beckham, Beckham
> >
> > Is there anyway i can keep them out using the sql, or will i have to use
> > some custom code in the report to keep them uniquie?
> >
> > Cheers
> > Luke
> >
> >
> > "Amarnath" wrote:
> >
> > > You cant do it in the same query because you will be using the first name if
> > > not then you can use this method. what you can do is to create a seperate
> > > dataset with the following method and refer this dataset in your report in
> > > final totals
> > > select @.aa = COALESCE(surnames + ', ', '') from ..... etc.. etc...ofcourse
> > > you
> > > need to fillup. :-)
> > >
> > > any problem let me know.
> > >
> > > Amarnath
> > >
> > >
> > >
> > > "lukethepunk" wrote:
> > >
> > > > Hi,
> > > >
> > > > I am looking for a way of putting multiple values from different rows into
> > > > one field, for example:
> > > >
> > > > The dataset returns 3 rows, with two fields, first name and surname like
> > > > below:
> > > >
> > > > FirstName Surname
> > > > Joe Bloggs
> > > > David Beckham
> > > > Sue Smith
> > > >
> > > > I want to get all of those surnames, and put them say into the final totals
> > > > of the report so they look like Bloggs,Beckham,Smith.
> > > >
> > > > Can anyone see a way of archiving this.
> > > >
> > > > Cheers
> > > > Luke
> > > >

Concatenating fields together in Reporting Services

I have several fields in the report that are concatenations of two or more
datafields in the dataset. What we have found is that RS is padding out the
fields to the full size from the database, so if we put together, it doesn't
look right.
For example, if I go to put firstname and lastname together instead of
getting "John Doe" I end up with "John Doe". Does anyone know of
a way to tallow for the default to be not to pad out the field. The only
alternative that I have found is to trim() the fields as needed, but it
really slows down the development process.
Thanks
JeffWhat is the type of Lastname and firstname in the database. Is it nvarchar
or is it a specified number of caracters ?
I don't have this issue with varchar.
Cédric
"Jeffery J. Morgan" <jmorgan@._tranquilitysolutions.com> wrote in message
news:ORsk4POhEHA.1184@.TK2MSFTNGP12.phx.gbl...
>I have several fields in the report that are concatenations of two or more
> datafields in the dataset. What we have found is that RS is padding out
> the
> fields to the full size from the database, so if we put together, it
> doesn't
> look right.
> For example, if I go to put firstname and lastname together instead of
> getting "John Doe" I end up with "John Doe". Does anyone know
> of
> a way to tallow for the default to be not to pad out the field. The only
> alternative that I have found is to trim() the fields as needed, but it
> really slows down the development process.
> Thanks
> Jeff
>