What is the purpose of sql queries in an expression and what would be the correct syntax to implement as an expression?

rhservan
rhservan used Ask the Experts™
on
My concern is in the question.
An simple example of the expression would be appreciated as well for explaining syntax.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Here's a sracastic example from a email signature I saw:
$ select * from users where clue > 0
no rows returned


This statement says from the table users,  give me all users where clue is greater than zero  (a user who has a clue).

If there were three users whose clue column contained 1, 0, 2 it would return 2 rows.

In this case all users had zero clues in their column, so since no entry contained a value greater than zero, no rows were returned.

I hope you found this instructive as well as amusing.
Scott

Commented:
Now the purpose of an sql query is to extract data fitting certain criteria from a database table, whether it is MS Access, SQL server, Oracle, etc, etc.    In the example above the criteria was clue >0.  

Select * says give me the entire row if the clue column contains greater than zero.

Author

Commented:
This is the first half of the question:

What is the purpose of sql queries in an expression

in an SSRS 2008 (see tag) expression, is the key
SSRS = Sql Server Reporing services
Expression =  tool built into this product.

Thanks for the quick response but I am looking for different information than this.
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

rhservan,

Are you talking about Report Builder?  

http://www.sqlmusings.com/2010/04/01/exploring-ssrs-2008-r2-report-builder-3-0-%E2%80%93-report-parts/

To put simply you would have a SQL query that extracts the data from a table (or tables) into a dataset, and then a report layout that would graphically display the results.  If this isn't the info you are looking for, can you provide us a little more direction of what you are trying to do?

Author

Commented:
No one has answered this question correctly.  I have provided all the information necessary
Tag: SSRS 2008

Here is the question again:

What is the purpose of SQL QUERIES IN AN  "EXPRESSION" and what would be the correct syntax to implement as an expression?

If you don't know what expressions are in the SSRS designer you wont know this answer.

Commented:
For syntax:
The following table shows examples of filter equations that use different data types and different operators. The scope for the comparison is determined by report item for which a filter is defined. For example, for a filter defined on a dataset, TOP % 10 is the top 10 percent of values in the dataset; for a filter defined on a group, TOP % 10 is the top 10 percent of values in the group.

Simple Expression
 Data Type
 Operator
 Value
 Description
 
[SUM(Quantity)]
 Integer
 >
 7
 Includes data values that are greater than 7.
 
[SUM(Quantity)]
 Integer
 TOP N
 10
 Includes the top 10 data values.
 
[SUM(Quantity)]
 Integer
 TOP %
 20
 Includes the top 20% of data values.
 
[Sales]
 Text
 >
 =CDec(100)
 Includes all values of type System.Decimal (SQL "money" data types) greater than $100.
 
[OrderDate]
 DateTime
 >
 2008-01-01
 Includes all dates from January 1, 2008 to the present date.
 
[OrderDate]
 DateTime
 BETWEEN
 2008-01-01

2008-02-01
 Includes dates from January 1, 2008 up to and including February 1, 2008.
 
[Territory]
 Text
 LIKE
 *east
 All territory names that end in "east".
 
[Territory]
 Text
 LIKE
 %o%th*
 All territory names that include North and South at the beginning of the name.
 
=LEFT(Fields!Subcat.Value,1)
 Text
 IN
 B, C, T
 All subcategory values that begin with the letters B, C, or T.
 

Examples with Report Parameters
--------------------------------------------------------------------------------

The following table provides examples of filter expression that includes a single-value or multivalue parameter reference.

Parameter type
 (Filter) Expression
 Operator
 Value
 Data Type
 
Single value
 [EmployeeID]
 =
 [@EmployeeID]
 Integer
 
Multivalue
 [EmployeeID]
 IN
 [@EmployeeID]
 Integer
 

For group examples:
The following table provides examples of group expressions that you can use to define a group.

Description
 Expression
 
Group by the Region field.
 =Fields!Region.Value
 
Group by last name and first name.
 =Fields!LastName.Value

=Fields!FirstName.Value
 
Group by the first letter of the last name.
 =Fields!LastName.Value.Substring(0,1)
 
Group by parameter, based on user selection.

In this example, the parameter GroupBy must be based on an available values list that provides a valid choice to group on.
 =Fields(Parameters!GroupBy.Value).Value
 
Group by three separate age ranges:

"Under 21", "Between 21 and 50", and "Over 50".
 =IIF(First(Fields!Age.Value)<21,"Under 21",(IIF(First(Fields!Age.Value)>=21 AND First(Fields!Age.Value)<=50,"Between 21 and 50","Over 50")))
 
Group by many age ranges. This example shows custom code, written in Visual Basic .NET, that returns a string for the following ranges:

25 or Under

26 to 50

51 to 75

Over 75
 =Code.GetRangeValueByAge(Fields!Age.Value)

Custom code:

Function GetRangeValueByAge(ByVal age As Integer) As String

Select Case age

Case 0 To 25

GetRangeValueByByAge = "25 or Under"

Case 26 To 50

GetRangeValueByByAge = "26 to 50"

Case 51 to 75

GetRangeValueByByAge = "51 to 75"

Case Else

GetRangeValueByByAge = "Over 75"

End Select

Return GetRangeValueByByAge

End Function
 


See more here:

http://msdn.microsoft.com/en-us/library/bb630426.aspx

Ok first of all, I am well versed in what an expressions are, but it's very difficult to get your requirements from what you asked originally.

An example of how to use expressions in a sql query.

Let's say we have a parameter that allows the user to enter in the number of records returned... Call it TopValue for sake of this example.

You would then do something like the following in your SQL query.

=IIF(Parameters!TopValue >0, "Select top" + Cstr(Parameters!TopValue.Value + " name from users"
Thx sgdought! I was just trying to come up with a way around a report design issue I'm having and that will help me a lot...  

Author

Commented:
@Auric1983 - Thanks for your efforts - Just trying to communicate the best I can through text.  To me this is a bit difficult. But  lets give it another try.

Below is a sample of an sql queriy in an expression in SSRS 2008 which I am working on. I did not create this but it is in a report.  I have never seen a select statement used this way.  Is a select statement in my SSRS 2008 Expression box possible?  

="select distinct DRL_ID from l_carp.p_goods_receipts, l_carp.p_gr_cer_nums, l_carp.p_b2mml_logs, DIMS.SOX, DIMS.BUSHELS
where DRL_ID = crg_ref_DRL_ID
and crg_cat_num = SOX.rod_batch and brsn_ref_crg_id = crg_id and brsn_cer_num = SOX.SOXer
and SOX.container_id = BUSHELS.container_id
and BUSHELS.bun_cer = '" + Parameters!container.Value.ToString + "'
and bml_processed_flag in ('Z','G','M')"
I would imagine, that in that report the author also has a dataset calling that Expression as the select statement.

To me the query that you listed would be better off setup in the "SQL TEXT" of the dataset, rather than an expression with a minor change.

="select distinct DRL_ID from l_carp.p_goods_receipts, l_carp.p_gr_cer_nums, l_carp.p_b2mml_logs, DIMS.SOX, DIMS.BUSHELS
where DRL_ID = crg_ref_DRL_ID
and crg_cat_num = SOX.rod_batch and brsn_ref_crg_id = crg_id and brsn_cer_num = SOX.SOXer
and SOX.container_id = BUSHELS.container_id
and BUSHELS.bun_cer = @ContainerValue
and bml_processed_flag in ('Z','G','M')"

Author

Commented:
Correction
The line:

and bushels.bun_cer =@containerValue (This converted after I pasted in the post)

Actual
bushels.bun_cer= ' " + Parameters!container.value.tostring + " '
Actually No I made that change in the version in my post.

Looks to me like the original report author didn't understand how to use Parameters in a query in SSRS, and instead used an expression to build the SQL statement.

Check the datasets in that report to see if there is one calling that expression textbox.

Author

Commented:
The above expression is run in a dataset.

There is another expression in a text box which provides a count of DRL_ID after "test text"
="test text (" & IIF(count(Fields!DRL_ID.value) is nothing, 0,count(Fields!DRL_ID)) &")"


What about on the "DATASETS" tab of the report? there must be a dataset pointing to that expression somewhere.

Author

Commented:
1. This runs as an expression in DataSet2
="select distinct DRL_ID from l_carp.p_goods_receipts, l_carp.p_gr_cer_nums, l_carp.p_b2mml_logs, DIMS.SOX, DIMS.BUSHELS
where DRL_ID = crg_ref_DRL_ID
and crg_cat_num = SOX.rod_batch and brsn_ref_crg_id = crg_id and brsn_cer_num = SOX.SOXer
and SOX.container_id = BUSHELS.container_id
and BUSHELS.bun_cer = '" + Parameters!container.Value.ToString + "'
and bml_processed_flag in ('Z','G','M')"

2. This runs as an expression in TextBox 1.
="test text (" & IIF(count(Fields!DRL_ID.value) is nothing, 0,count(Fields!DRL_ID)) &")"

Author

Commented:
The expression in Text box 1 actually points to the dataset2,  sorry I left that out of the expression on #2
oh ok.

Well Item 1, you could use @ParameterName instead of the "+ Parameters..."  it would work better.

#2 is normal what it is doing is concatenating "test text" with the count of DRL_ID, syntax looks ok.

Author

Commented:
When would it be advantageous to run a query, like the one above,  as an expression?
If you were dynamically creating the SQL query and you wanted to be able to specify fields in a where clause for example.

Author

Commented:
Thank you for your contributions, the information you provided assisted with resolving my problems.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial