I'm trying to show a column only if a certain parameter contains a certain string of characters. So far I've got it working if the parameter is equal to the string of characters by doing
=IIF (Parameters!Param1.Value = "String1", False, True)
but I would like it to work if the Param1.Value contains "String1" ... I tried
=IIF (Parameters!Param1.Value like "%String1%", False, True)
but it doesn't work. Any suggestions?
TIA
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 RelatedSo if my Parameter value is...
MCCL, MCMA, MCPL, MCPR, MCRE, MCSE, MCSN, MCVA, MCCL, MCEP, MCMA, MCRE out of 43 possible choices,
What is the expression syntax to hide my tablix column based on my parameter value?
=iif(instr(Join(Parameters!ColumnVisibility.Value,","),"MCCL","MCMA","MCPL"...)>0,false,true)
Just not sure of the syntax...or do I have to qualify each one???
= IIf(Parameter!PF1.Value = "MCCL" OR Parameter!PF1.Value = "MCMA"..., TRUE, FALSE)
Hello,
I'm using SSRS 2005
I'm trying to write an expression for the "Hidden" property of a column in a table. The column is only populated with data if the group on my table is open. If the group is collapsed, then the column is empty. I'd like to make the column hidden if the group is collapsed. So I'm thinking it would be something like this:
=Iif(Table_1_Group.Hidden = "True", "True", "False")
But I can't find anyone who has written this anywhere.
I'm basically trying to make this report do the same thing a matrix does, but the matrix doesn't let me label the columns. I put textboxes above the matrix in line with the columns, but when I deploy the report to ReportManager, the textboxes get thrown all over the place and don't line up with the columns anymore.
So I guess if you can't answer the first question, an alternate question I have is how are you supposed to label the columns (row groups) in your matrix? They don't have headers, if I try to line textboxes up with them they get all messed up during deployment. They have that textbox up there in the top left corner that runs across the top of all of the row group columns, but if I put labels separated spaces in that textbox, the spaces get removed in rendering so the text is pushed all to the left and doesn't line up anymore.
Thanks,
Andy
I currently have a text box that say "Company", I have the a visibility expression set on that text box as follows:
=iif(Sum(Fields!ID.Value,"Footnote") <> 0, False, True)
Basically if a number is greater than 0 show it, if not don't
I want to modify it to say always show Company, if its greater than 0 show Company *
I have the following Visibility expression set on a SQL 2000 RS report set on the hidden value:
=iif(Parameters!state.Value= "Institutional Agency",true, false)
Problem is that when I choose Insititutional Agency from the drop down list it evaluates the expression and shows the textbox, when another state is choosen it still shows the textbox and I dont want it to? I have ran a test to show the expression (Parameters!state.Value) in a text box, and its showing the right values each time i run the report?
I have a report that gets exported to Excel. In the report there is an expression in the visibility field on one of the detail lines to hide it if it meets certain criteria. When run directly and exported to PDF, it displays correctly (hiding the lines as requested). When exporting to Excel, the lines are not hidden.
Can anyone help on this?
Thank you.
Hi team,
I'm working on Reporting service 2005. When I give an expression for visibility of a table in a report based on a parameter, the contents are coming in a single page and it is not based on the interactive size of the report. I want the data to be coming on different pages based on the interactive size of the page. If the visibility is set directly it works. Could you please help me to solve this issue?
Thanks in advance,
Minu
Hi all,
I have a problem with a report I have created. It has around 52 columns and each column is shown or hidden based on a boolean parameter. Simple huh? I though so.
Each column has an expression similar to =IIF(Parameters!showfirstname.Value,False,True) for the Hidden field. This is not the hidden field for the 'cell' or 'header' but for the entire column.
The problem is, the report is correctly displayed as a pdf, tiff, excel file (possibly others), but all columns with an expression as the hidden value are not displayed in the xml or csv output regardless of the parameter value. This also applies if the expression is =IIF(True,False,True) or =IIF(1=1,False,True).
As soon as I change this field back to a simple 'True' or 'False' it displays correctly. I've tried playing around with setting the output options to values other than the default Auto setting to no avail.
There are numerous comments about this on newsgroups online going back to the first release of reporting services but none of them have solutions.
Regards
John Burns
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.
Product version : SQL Reporting Service 2005 with SQL SP2
It's a report with 3 sub-reports in it, i want to display only one of the 3 sub-reports at a time depending on 1 or 2 parameters received by the parent report. These parameters are verified by an expression into the visibility tab of each sub-report. The two parameters are 2 lists with possibles values between 1 and 2 (not query based). I have verified these values and they are correctly received by report depending on the selection of each list.
Ex.: SubReport1.expression = IIf(Parameters!Regroupement.Value = 1, True, False)
SubReport2.expression =IIf(Parameters!Regroupement.Value = 2 AND Parameters!SautPage.Value = 1, True, False)
SubReport3.expression =IIf(Parameters!Regroupement.Value = 2 AND Parameters!SautPage.Value = 2, True, False)
So here are the posibilities :
when Regroupement.Value = 1 --> SubReport1 will be shown
when Regroupement.Value = 2 AND SautPage.Value = 1 --> SubReport2 will be shown
when Regroupement.Value = 2 AND SautPage.Value = 2 --> SubReport3 will be shown
Now why that doesn't work ? I always see the same report and it's not the right one displayed even i change the selection of my 2 lists for the parameters value.
Hi all,
I have a report with 3 matrix only one matrix is displayed at a time depending on a parameter value, I use an expression in the visibility Property, since then, when I export to Excel the Toggle buttons have disappeared.
How can I keep the Toggles in the Excel file ?
does anyone know why my initial visibility expression (=Level()>1) stays in effect after generating my report? The idea was to display only a couple of levels in the hierarchy at first, so that the user could more easily decide what he wants to expand. But expand doesnt do anything after initial rendering.
The only way I could get this kind of report to work (show hierarchy and expand/collapse) was to live with initial visibility = "visible", but unfortunately, that setting shows all levels right away, not a very user friendly thing.
I am running Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Hello,
I have one column group and three static columns. I'd like to control static column visibility property depending on a field value. I.e. Hidden: =Fields!GroupId<>5.
But I'm not able to find visibility property in column object. Neither in properties explorer nor RDL documentation. I'm only able to set hidden property in textbox objects.
I have RS 2005.
Thank you for any idea.
Jirka Nouza
I have a table with 5 levels of grouping and a detail row. When the report first displays it looks like this:
Group1 Hdr Group2 Hdr Group3 Hdr Group4 Hdr Group5 Hdr Detail Headers
+Group1-Item1
+Group1-Item2
+Group1-Item3
How can I alter the visibility of the group headers when they don't have access into the scope of the groups themselves ? At startup, I only want to show group1's header. If I expand an item in group 1, then I only want to show headers for group 1 and 2, etc ...
Thanks
Hi, currently I am using a matrix of the below format:
Column Grouping
a) Months (From dataset)
b) Amt | Cnt | Ave (Static)
Desired Collapsed View
Jan Feb Mar
Ave Ave Ave
Figures 10 20 30
Current Collapsed View
Jan Feb Mar
Ave Ave Ave
Figures 10 20 30
Expanded View
Jan Feb Mar
Amt Cnt Ave Amt Cnt Ave Amt Cnt Ave
Figures 100 10 10 200 10 20 300 10 30
Issue Faced
When viewing the RS report online, the months cell do not "shrink" even though Amt & Cnt columns were hidden in the Collapsed View.
When the report was exported to excel, the desired collapsed view was showed where the months cell were "resized" when the drilling is collapsed.
Question
Is there any setting required to enable the months cell to "auto shrink" when Amt & Cnt columns are hidden?
Thanks for your help.
Hi,
how to change visibility of a column group in a matrix?
Thanks,
Igor
I have a matrix table with row groups of a date and addresses and also a column group which produces 3 columns and values with in these column.I have a "Total" column based on a Count of the column groups which gives a number.How do you hide all rows where the "Total" column is less then 1? There will be results which still needs the date and addresses groups.
View 2 Replies View RelatedHi, how are you?
I'm having a problem and I don't know if it can be solved with a derived column expression. This is the problem:
We are looking data in a a sql database.
We are writting the SQL result in a flat file.
We need to transform data in one of the columns.
For example: we can have 3 digits as value in a column but that column must be 10 digit length. So we have to complete all the missing digits with a zero. So, that column will have the original 3 digits and 7 zeros. How we can do that tranformation? We must do it from de the flat file or it can be a previous step?
Thanks for any help you can give me.
Regards,
Beli
Hi:
I have a Dervied Column Component, in which there is a column called RecStatusCode. The criteria to give a value to that column is below in SQL:
(SELECT CASE CertParticipant
WHEN 'Y' THEN 'C'
WHEN NULL THEN 'A'
END
FROM [dbo].[SchoolCertRequest]
WHERE SchoolID = (SELECT SchoolCode FROM [WebStrategy].[LAF].[LoanApplication] WHERE LoanApplicationID = @LoanApplicationID) and SchoolBranch = (SELECT BranchCode FROM [WebStrategy].[LAF].[LoanApplication] WHERE LoanApplicationID = @LoanApplicationID) AND LoanType=(SELECT LoanApplicationTypeCode FROM [WebStrategy].[LAF].[LoanApplication] WHERE LoanApplicationID = @LoanApplicationID))
My question is how can I use this SQL in that expression field, or is it possible to have a user variable, with the above sql being the criteria, and the value could be used in the derived column, by just using the variable in the expression. In either case, I hope you understand what I am trying to accomplish. The reason why its like this is because currently in my Data Flow Task, i Have a OLEDB source which returns 25+ column values, the source connects to this derived column item, and then finally the destination oledb where the mapping takes place. So basically I am trying to avoid MERGE JOIN, because i tried using it , and it complicated the whole thing much more then it really needs to be. Like the RecStatusCode column, i have 5 more columns which are dervied, and the values are coming from a SQL statement similar to above. I really need some help here, and I have not seen anything in reference to this in BOL or any articles online. Thanks in advance.
Hi Experts,
I am new to this forum and SQL usage.
I have a database with following table fields as
Refernce - Varchar field
D_C - Varchar field (containing either D or C) and
Amount - Integer
Now i want to create a View & split the Amount column into 2 basd on
split this Amount into 2 columns as below:
Refernce - Varchar field
D_C - Varchar field (containing either D or C) and
Debit Amount - Integer (where D_C is D)
Credit Amount - Integer (where D_C is C)
Regards
SEkar
Please advise on how to get the GROUP BY coded in an acceptable way:DECLARE @LO INTDECLARE @HI INTDECLARE @StartDate varchar(10)DECLARE @EndDate varchar(10)SELECT @StartDate = '01/01/2005'SELECT @EndDate = '06/30/2005'SELECT @LO= 250SELECT @HI= 333SELECTStateCD, CountyCD, Zip, Z.CityName, Z.StateCode, Z.CountyName, 'Criteria' = 'JumboRange:' + Convert(varchar(4),@LO) + '-' +Convert(varchar(4),@HI), 'StartingDate' = @StartDate, 'ThruDate' = @EndDate, JumboAmount = SUM(JumboAmount), JumboMortgages = SUM(JumboMortgages), JumboFIXMortgages= SUM(JumboFIXMortgages), JumboFIXAmount = SUM(JumboFIXAmount), JumboARMMortgages = SUM(JumboARMMortgages), JumboARMAmount= SUM(JumboARMAmount)FROM LoanDetails T INNER JOIN dbo.ZipCodesPreferred ZON T.StateCD = Z.FIPS_State AND T.CountyCD = Z.FIPS_County AND T.Zip =Z.ZipCodeGROUP BYStateCD, CountyCD, Zip, Z.CityName, Z.StateCode, Z.CountyName, 'Criteria' = 'JumboRange:' + Convert(varchar(4),@LO) + '-' +Convert(varchar(4),@HI), 'StartingDate' = @StartDate, 'ThruDate' = @EndDate
View 2 Replies View RelatedI have a table in my report which shows sales values for each month by looking at the month number
so for Jan i use
=IIf(Fields!period_.Value=1,Fields!nett_.Value,0)
for Feb
=IIf(Fields!period_.Value=2,Fields!nett_.Value,0)
this is all good and I get a series of rows per customer with the correct value in the correct column
JAN FEB Mar
Customer A 250
Customer A 350
Customer A 5000
.
However I want to create a summary a line
JAN FEB Mar
Customer A 250 350 5000
I tried using SUM but this doesn't give all of the values in the summary line. It might just give the first and the rest are zeros. Or if the customer didn't have a value in Jan, but did in Feb and March, Feb's value is shown, but March is at 0 etc.
Have spent far too long on this today, so if anyone as any suggestion (come back Crystal all is forgiven ) on any approach I could take to this I'd appreciate it.
Cheers
Steven
Hi, all experts here,
Thank you very much for your kind attention.
I am having a question on derived column expression. The expression I am trying to use for the derived column is as below (column1 is a numeric data type column):
case
when column1<0 then 'yes'
else 'no'
end
But I got the error message though, would please any experts here give me any advices on the expression I used? What is wrong with the expression I used above?
Thank you very much and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
The following simple expression is not working for in a derived column transform. I have tried several things back and forth with lots of frustration.
DAY((DT_DATE)mydate)
In fact any of the date/time functions are not working for me and I keep getting the following error
[Derived Column [380]] Error: The "component "Derived Column" (380)"
failed because error code 0xC0049067 occurred, and the error row
disposition on "output column "Derived Column 1" (824)" specifies
failure on error. An error occurred on the specified object of the specified component.
mydate happens to be imported as string[DT_STR] column from a flat file source.
Thank you in advance.
I am in the "Check Constraints" window trying to add a constraint that during inserts only allow distinct Column2 insertions. What is the Expression for that? I tried UNIQUE(Column2) it give me an error.
Here is my table definition:
Column Name Data Type Allow Nulls
--------------------------------------------------------------------------------------------
Column1(PrimaryKey) int No
Column2 nvarchar(MAX) No
Hi.
I am using the following expression to check if the first charcter of a string is not the letter "E" and if it is, strip it off by selecting the remainder of the string:
SUBSTRING([Derv.comno],1,1) == "E" ? SUBSTRING([Derv.comno],2,10) : [Derv.comno]
This is ok in 99.9% of cases, but ideally I would like to be able to check, and alter the string if the first charcter is anything but numeric
I had something like this in mind:
SUBSTRING([Derv.comno],1,1) != ("1","2","3") ? SUBSTRING([Derv.comno],2,10) : [Derv.comno]
but the syntax is incorrect.
Could you tell me if what I am attempting is actually possible, and if so, point me in the right direction regarding the syntax!
Thanks
I'm trying to import from Excel, using
(Assignment == 0 ? "In Use" : Assignment == 1 ? "In Stock" : "Retired") to convert 3 values to text and I' getting the following error:
An error occurred while attempting to perform data conversion. The source type could not be converted to the destination type.
Could someone please tell me what I am doing wrong?
Thanks
Dean
ps. just fixed it myself by changing the data type of the input column
Greetings,
I have an existing 2000 DTS package that uses the following case statement:
Case
When TERMS_PERCENT ='0'
then 0
else cast(TERMS_PERCENT as decimal(6,2))/100
end as TermsPct
to convert a source DT_STR(4) datatype to a DT_Numeric(5,2) destination column and would like to use an equivelent derived column expression in 2005. Being a DBA by nature and experience I'm having trouble converting this statement to a valid expression without failure, any help would be greatly appreciated.
I'm new to CTE and I'm trying to update some columns in my cte listed below
USE ELU;
GO
WITH FollowupGrid (TransactionID,
DetailLineNum,
TransactionDate,
Quantity,
ItemDescription,
ProductSize,
TransactionType,
FollowupResolve,
ReplenishResolve) AS
(
SELECT TransactionID,
DetailLineNum,
TransactionDate,
Quantity,
ItemDescription,
ProductSize,
TransactionType,
FollowupResolve,
ReplenishResolve
FROM vwFollowupTransactions
WHERE CustomerNum = N'6299'
UNION
SELECT
r.TransactionID,
r.DetailLineNum,
r.TransactionDate,
r.Quantity,
r.ItemDescription,
r.ProductSize,
r.TransactionType,
r.FollowupResolve,
r.ReplenishResolve
FROM vwReplenishTransactions r
WHERE CustomerNum = N'6299'
)
UPDATE FollowupGrid
SET FollowupResolve = N'Yes'
FROM vwFollowupTransactions f INNER JOIN
FollowupGrid ON f.DetailLineNum = FollowupGrid.DetailLineNum AND
f.TransactionID = FollowupGrid.TransactionID
WHERE (f.FollowupResolve is null)
UPDATE FollowupGrid
SET ReplenishResolve = N'Yes'
FROM vwReplenishTransactions t INNER JOIN
FollowupGrid ON t.DetailLineNum = FollowupGrid.DetailLineNum AND
t.TransactionID = FollowupGrid.TransactionID
WHERE (f.ReplenishResolve is null)
(
select * from FollowupGrid
)
GO
When I run this query I get an error message which states"
"Update or insert of view or function 'FollowupGrid' failed because it contains a derived or constant field."
How would I go about updating the FollowupResolve and ReplenishResolve fields in a CTE?
I am getting an error from the case part of the select statement below which reads 'Incorrect Column Expression' then it quotes the case statement. All I am trying to do is convert and return the weight value to kilos if it was entered in pounds.
SELECT Salesinv.Unique, Salesinv.SalesNo, Salesinv.PurchNo, Salesinv.SalesInvNo, Salesinv.InvValue,
(case when Salesinv.WUnits = 'Llb' then round(Salesinv.NettWeight/2.2046,0) else Salesinv.NettWeight end)
FROM Salesinv Salesinv
WHERE (Salesinv.Unique>=38397.3092 And Salesinv.Unique<=38537.39885)
Any help would be greatly appreciated, hopefully thanks in advance.
I am working with Excel, then within Excel I am using MS Query to query a database. I am trying to use the CAST function on a field with numbers (1,2 or 3 digits) so I can convert it to a text value with three digits, i.e. 1 would read 001, 12 would read 012, etc.
I am not using CAST in the design grid. Is this even possible?
I am modifying the underlying SQL code. Here is the line that is giving me trouble:
CAST(GL02GLF.GLF_SEQ_NUM as CHAR(3)) as “Sequence”
What is the equivalent SSIS expression for this C expression to format an unsigned integer by padding it with leading zeros?
sprintf(publ_doc_id, "%010u", id)