Reporting Services Parameter([rsFieldReference] The Value Expression For The...)
Aug 1, 2007
@StartDate is a report parameter and CustomerNo is the field I want to render on the report,I want to combine the sql ,but it display the error message below when I preview the report.If I query in the design form and input the value of the parameter ,it run correctly .
---------------------------------------------
Data sql:
declare @sql nvarchar(4000)
declare @sqlWhere nvarchar(4000)
set @sql='select CustomerNo from table1 '
set @sqlWhere=''
if @StartDate<>''
begin
set @sqlWhere =@sqlWhere + ' StartDate=''' +@StartDate + ''''
end
if ltrim(rtrim(@sqlWhere))<>''
begin
set @sqlWhere =' where ' + @sqlWhere
end
set @sql=@sql+@sqlWhere
exec sp_executeSQL @sql
------------------------------------------
Error message:
[rsFieldReference] The Value expression for the textbox €˜CustomerNo€™ refers to the field €˜CustomerNo€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
View 5 Replies
ADVERTISEMENT
May 6, 2015
I am trying to create a column chart that calculates the percentage of computers in our IT environment that are Actively communicating to our SCCM Server.
I have two datasets:
1. Total_Count_Of_AD_PC DataSet.
2. PC_With_Active_SCCM_Clients dataset.
Basically i wan to calculate the percentage for each Region (i.e. AP for Asia Pacific, EMEA, Americas).
Below is the Total_Count_Of_AD_PC  Dataset screenshot.
Below is the PC_With_Active_SCCM_Clients dataset.
Below is the expression that i used that is causing the error.
=CountDistinct(IIf(Fields!Region.Value="AP", "PC_With_Active_SCCM_Clients"),(Fields!Name.Value, "PC_With_Active_SCCM_Clients"),Nothing)/CountDistinct(IIf(Fields!Region.Value="AP", "Total_Count_Of_AD_PC"),(Fields!name.Value,
"Total_Count_Of_AD_PC"),Nothing)
Below is the error message....
The Y expression for the chart ‘Chart2’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset.
View 6 Replies
View Related
Oct 28, 2015
I have created 1 report with 2 datasets. This report is attached to the 1st dataset.For example,1st one is "Smallappliances", 2nd is "Largeappliances".
I created a tablix and, the 1st column extracts Total sales per Sales person between 2 dates from 1st dataset (Small appliances). I used running values expression and it works fine.
Now, I would like to add another column that extracts Total sales per sales person between 2 dates from 2nd dataset (Large appliances). I am aware that I need to use Lookup expression and it is giving me the single sales value rather than the total sales values. So, I wanted to use RunningValue expression within lookup table to get total sales for large appliances.
This is the lookup expression that I added for the 2nd column.
=Lookup(Fields!salesperson.Value,Fields!sales_person.Value,RunningValue(Fields!sales_amount.Value,
sum, " sales_person"),
"Largeappliances").
I get this error when I preview the report.An error occurred during local report processing.The definition of the report is invalid.An unexpected error occurred in report processing.
(processing): (SortExpression ++ m_context.ExpressionType)
View 7 Replies
View Related
Jun 23, 2015
I have a scenario where I would like to use the 'OR' statement in SSRS expression.
I have the following fields:
GLnumber, ProgramControl, GLAmount & EscrowDepositAndWithdrawal.
What I would like to do is as follow:
If GLnumber is 12200.22162 then I would like to take EscrowDepositAndWithdrawal
OR
If ProgramControl is 'escrow' then I would like to take EscrowDepositAndWithdrawal
OTHERWISE I would like my expression to take GLAmount My expression looks something like this:
=IIf(Fields!GLNumber.Value = "12200.221620" OR Fields!ProgramControl.Value = "ESCROW",Fields!EscrowDepositAndWithdrawal.Value,Fields!GLAmount.Value)
It appears only the first IF statement is true. It does not appear that the OR statement never be true.
I am not sure why the OR statement never satisfied. I know I have a record with ProgramControl = "ESCROW".
View 2 Replies
View Related
Aug 21, 2015
I am working on existing ssrs report. When I see the preview I could see the extra parameter than the actual parameter.Â
How do I know , when this parameter is coming in the preview ?
View 5 Replies
View Related
Jul 29, 2015
I have two report , first is main report which is matrix and have one parameter User_ids which is multi value selection and my second report is basic chart of user_wise performance.
Now, my main report (matrix ) works fine for Multiple selection of users and i have putted one textbox on main report chart which has action properties set for chart report, when user click on chart button it must goes to chart with user selected in main report. Now , i have used expression for parameter to send it like ..
=join(parameter!user_id!value,",") which pass selected value to chartÂ
And when I am selecting single user it passing that value to chart parameter list but , when it is more than one user it errors with conversion failed when converting the nvarchar value '121,128' to data type int. But my chart also works when passing 121,128 in user parameter in preview of report .
View 2 Replies
View Related
Aug 28, 2015
my dataset from sharepoint list. and this dataset value assign to parameter. i want when no any parameter is selected than it should filter like "ALL". when i select alow null value it give me prompt error you  can not select null in multivalue parameter.How can i do it. i am using share point list.
View 3 Replies
View Related
Oct 18, 2015
I am using reporting services 2012, Can we make visibility of report parameter dynamic, ie can we make parameter visible or hide on certain condition or its visibility depends on other parameters Is this feature  available in any other updated version of ssrs?Â
View 2 Replies
View Related
Aug 10, 2015
I am using report builder 3.0.
I have a report parameter called para1 which is a drop-down list and what I want to do is display another report parameter based on the para1 selection.
So for example, para1 contains a, b, c choices. if a user selects b, I would like para2 to display but if the user selects a or c, I don't want the para2 to display.
View 5 Replies
View Related
Dec 3, 2015
Using SQL Server 2008R2 and Report Builder 3.0..I have an action set in a text box of a table. My intent is to pass the value of that text box (which is variable) to a sub-report in a popup window. Here's my code: URL....The parameter of the report I'm trying to open is @SONum.I'm guessing my error is involved in the formatting of how the value of the parameter is being passed. I've also seen examples where the report server and report values were parameterized, but I don't know where to define
Parameters!ServerAddress.Value anywhere.Do I need to have something set up a certain way within the report I'm opening? Here's the report Parameter settings on the report I'm trying to open.
View 3 Replies
View Related
Apr 22, 2015
I'll go to a dataset, open up the query designer, add a new parameter, then refresh the fields, but the parameter won't be added as a report parameter. If I go to the dataset properties under the list of parameters, the value in the dropdown will be blank. However, sometimes this will automatically add.Â
Is this a bug in Visual Studio? How do I get around this?Â
View 3 Replies
View Related
Mar 25, 2008
Hi all,I wrote the following expression in one cell in my table
if there is no value for fields i am getting the following out put in that particular cell.
I am calculating percentage.
I have more than one datasets in my report.
=Round(100*Sum(Fields!WCOUNT_5.Value, "MaxNumeracyCountForSelectedSch")/Sum(Fields!QCOUNT.Value, "MaxNumeracyCountForSelectedSch"),2) & "%"
NaN%(This is the result in Cell)
Pls help me.
View 5 Replies
View Related
Apr 20, 2015
Below is my query, it output's a percentage based on some calculations that i've had to make to fit in with requirements.The trouble is that it is also coming with NaN values which i need set to 0%,modify the below query to accomplish this?
=Sum(IIF(IIF(ISNOTHING(Fields!TotalHours.Value) = true,25,Fields!TotalHours.Value) <= 24,1,0)) / Sum(Fields!SubTotal.Value)
View 7 Replies
View Related
Sep 22, 2015
I have a ssrs report R1 having a button B1 and a another report R2. On the click of B1 i want to download R2 in excel format.I have used action->Go to url property on B1 and have used the below expression to fetch the url for downloading R2 in excel format.
Â
=Globals!ReportServerUrl &"?" & Globals!ReportFolder & "/" & "ReportName" &
"¶m1=" & Parameters!param1.Value &
"¶m2=" & join(Parameters!param2.Value,",") &
"&rs:Format=excel"
The above expression is working.But if the parameter values contain "&" then i need to handle it in the url so i used the below expression.
=Globals!ReportServerUrl &"?" & Globals!ReportFolder & "/" & "ReportName" &
"¶m1=" & Replace(Parameters!param1.Value,"&","' + escape('&') + '") &
"¶m2=" &Replace(join(Parameters!param2.Value,","),"&","' + escape('&') + '") &
"&rs:Format=excel"
but it is not working and giving the below error.The path of the item ' ' is not valid. The full path must be less than 260 characters long; other restrictions apply. If the report server is in native mode, the path must start with slash. (rsInvalidItemPath).
How to handle the "&" in the expression?
View 2 Replies
View Related
Apr 24, 2015
Is it possible to format part of an expression?
I have an expression:
=(Fields!Intervention_Date_Of_Service.Value & vbcrlf & Fields!Intervention_Comment.Value & Fields!Intervention_ID.Value )
I would like my report to display Fields!Intervention_Date_Of_Service.Value underlined
i.e.,
"4/6/2015 12:45:00 PM"
Applied Data Script
ID 76584
View 2 Replies
View Related
Nov 22, 2015
I have a report with an expression to give a calculated field value (VoidDays)Â as follows:=DateDiff("d",Fields!Void_Start.Value, IIf(Fields!Void_End.Value is nothing, today, Fields!Void_End.Value))I have a number of different codes in the output such as VLOSS, VMAINT, VHOUS etc.When I run my report my days total for VLOSS is always correct but all other values are one day short. In the example shown in image below VMAINT should be 6 days and VHOUS should be 20 days which would then both add up to the VLOSS of 26 days
Â
I cant currently fix these days at source for a number of reasons hence I need to fix these on the report output.How can I add one day onto each of my Void Days Total for every code except VLOSS which is always correct.
View 11 Replies
View Related
Jun 22, 2015
I need returning a 0 from my lookup value versus it currently returning a blank space in my SSRS report.Â
=Lookup(Fields!InsuranceCarriersID.Value , Fields!InsuranceCarrierId.Value, Fields!TotalBalance.Value, "InsAgingSubReport")
View 3 Replies
View Related
Sep 30, 2015
I have a field called end date which can be empty to I have it coded to set todays date if empty using the following
=Iif (Fields!actual_discharge_ date. value Is Nothing, Today(), Fields!actual_discharge_date.value) which works fine.
The problem is I have to figure length of service which is coded as=
DateDiff("d", fields!approved_start_date.value, fields!actual_discharge_date.value)
Which works except when there is no actual discharge date. it will not see the current date that the first expression adds but still shows a huge negative value.
View 6 Replies
View Related
Jun 3, 2015
Is there a way of taking a result from a query and pulling out a certain account number and displaying those account numbers - as fred? Eg. I have a drop down of account number (s) however they pull from a table that only displays them as account numbers. I would like to give more a description to certain account numbers that are pulled. Eg. account number 234 will be fred and account number 555 will be sam etc. Trying to add it to the query on the dataset is complicated, so I was wondering if I created an additional blank field with an expression to be able to do this?Â
View 7 Replies
View Related
Jun 25, 2015
I would like to suppress the entire row in tablix if no data despite multiple expresions used in a text box.
If data the it will show per the DB data else it should not show anythig or leave it as blank row
The issue is where I have appended ";" and "'" in the expressions but still comes in the report output because it is appended even if no data.
View 19 Replies
View Related
Apr 24, 2015
Is there any way to default to the first day of the month in a SSRS expression. I'm looking to do the reverse of the EOMONTH() function.The date value is coming from a parameter @BegDate and formatted YYYY-MM-DD. So if the parameter is set to 2015-08-31 it would show 2015-08-01
View 2 Replies
View Related
Sep 30, 2015
How do I use an expression to position a tablix on the report?
View 4 Replies
View Related
Jul 29, 2015
Is it possible to use a IIF IsNothing expression and check another condition? For all the values that are blank, I want it to check another column if  "Disposition" = 2 then I want to show "Inactive"
=iif(isnothing(Fields!Value)," ","MS " & Fields!Value)
View 3 Replies
View Related
Oct 13, 2015
I have a stacked column with 2 series("phones", "no phones") on the categories, What I am trying to do is to show only one of the series in each category,I used the visibility option in the series properties, and chose show or hide based on an expression using the expression below if(Fields!PENETRATIONSERIES. Value="no phone",FALSE,TRUE) i get no graph , But when I switch the true and false value, the graph show with the two series there.
View 3 Replies
View Related
Mar 8, 2007
I have an expression in a group that calculates a percentage of sales:
=iif(Sum(Fields!BOOKD.Value)=0,0,IIF(Sum(Fields!NET.Value)=0,0,((Sum(Fields!BOOKD.Value)-Sum(Fields!NET.Value))/Sum(Fields!BOOKD.Value))))*100
If there is a 0 balance for both the book dollar and the net dollar the expression returns #Error.
I have made sure that if the value is null it should return a 0 in the initial query.
,SUM(ISNULL(R.BOOKD,0)) BOOKD
,SUM(ISNULL(R.NET,0)) NET
Any suggestions would be greatly appreciated.
View 1 Replies
View Related
Oct 29, 2015
I have  @for_dte parameter(date data type) in a ssrs report
using below expressionÂ
=Format(Parameters!for_dte.Value, "dd/MMM/yyyy") -i got Output:10/Oct/2015
but i need Output:10/OCT/2015(ie OCT in capitals).
How can i achieve that using expression.
View 2 Replies
View Related
Oct 20, 2015
I have 2 reports in a single report
Report 1:Â This report has a tablix and a pie chart which displays member counts for genders.Dataset is written such that the gender names are always displayed in the tablix even when the count is Zero for Female and Male
Report 2:Â This report has a tablix and a pie chart which displays member counts for category Age.Dataset is written such that the age category names are always displayed in the tablix even when the count is Zero for age categories
Both Report 1 and Report 2 are place in separate rectangles and then these 2 rectangles are place in one single rectangle.Now I want to hide this main rectangle with an expression.
View 4 Replies
View Related
Feb 11, 2011
Is that possible to merge adjacent cells conditionally in SSRS 2008?
For Example
Col AÂ ColB
Col C ColD ColE ColF
If condtion is true
ColC and Col DÂ to be under Col A and ColE and Colf to be under Col B
Or Col C to be under Col ColA and Cold to ColF to be undercol B
View 6 Replies
View Related
May 21, 2015
I have created a heat map and it is working pretty well. The only issue I am having is that the expression for the fill is using "DataSet1"
=Code.GetHeatmapColor(sum(Fields!AnnualPremium.Value),Min(Fields!AnnualPremium.Value,"DataSet1",Recursive),
Max(Fields!AnnualPremium.Value,"DataSet1",Recursive))
This is making the heatmap look at the whole dataset instead of just what I am grouping by. Within the Dataset there are Regions and Credit Unions. Since the Dataset is looking at an entire region, the heatmap is coloring based on all data for the region. I need to heatmap to color based on the Credit Unions in that region. The Credit Unions are a group. I need the group to be the value it is referencing in the heat map and not "DataSet1". I have been told to use scope or a variable but cannot get it to work correctly.
View 3 Replies
View Related
Jun 1, 2015
I have a data field in a SSRS Report that contains the requestor's User Id. Their ID is prefixed with "PRIV"...And I'm assuming that is the direct result of the network domain. I need to create a SSRS expression to determine if the User ID is prefixed with "PRIV" and then parse that out and use what's behind the "" as their true User Id.
example...."PRIVID123456" should appear as "ID123456" in the report data line.
View 5 Replies
View Related
Apr 22, 2015
I am using the following expression to place the month in the heading of a column.
=MonthName(Month(DateAdd("M",-1,Now)))
The expression above gives me the previous month, but I need to have it as "Jan", not "January".
expression that will give me the three character month name instead of the full month. Â I've tried substituting the "M" with "MMM", but get an error. Â I've also tried "Mon", but again, I
get an error.
View 4 Replies
View Related
Sep 23, 2015
I am trying to add a filter and sorting to a matrix, within the matrix I have a column which works out the percentage and is as follows
=(Sum(Fields!Sum_Distinct_JobBilledExVAT.Value) + Lookup(Fields!ClientId.Value, Fields!JobClientId.Value, Fields!Sum_JobBilledExVAT.Value, "ProfJobClientsFees") + Lookup(Fields!ClientId.Value, Fields!ClientId.Value, Fields!Sum_JobBilledExVAT.Value, "ProfClientsFeesID")) / (Lookup(Fields!ClientId.Value, Fields!ClientId.Value, Fields!Sum_MatchedToOtherPost.Value, "ProfClientsMatch") + Lookup(Fields!ClientId.Value, Fields!ClientId.Value, Fields!Sum_JobBilledExVAT.Value, "ProfClientsCC") + Lookup(Fields!ClientId.Value, Fields!ClientId.Value,
[Code] ....
I tried enclosing the query in Abs( ), which I have done in the past, although in the past I wasn't using Lookup functions so I don't know if that is the reason it won't work.
View 6 Replies
View Related
Apr 17, 2015
I need a report that shows calcul of a field for current year as well as previous year respecting this rule expression(Last-Last Previous)/Last Previous*100 (I work with MDX Query and i work with SSRS 2008).
Also the report runs on a Year Parameter. Below is an example for the result for example i selected the years 2010 2011 2012 i can select another years because i have the report runs on a Year Parameter
year
Data 2010 2011 2012
hp 14 25 30
Dell 17 18 20
and the result i want
year
Data 2010 2011 2012 2011/2012
hp 14 25 30 0.002 (Last -Last Previous)/(last Previous*100) =(30-25)/(25*100)
Dell 17 18 20 0.0040
View 8 Replies
View Related