I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName Clm ABC-X
X
Page2:
GroupName Clm ABC-Y
Y
Page3:
GroupName Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
Code Block WITH YesterdayCTE AS ( SELECT type = 'Members Joined Yesterday' , Borrowers = (select count(*) from LoanApplication INNER JOIN Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id where (LoanApplication.SubmittedOn >= GETDATE()-1) AND (Member.CuStatus = 'Approved') GROUP BY CreditUnion.Name ) , Depositors = (select count(*) from CDOrder INNER JOIN Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id where (CDOrder.SubmittedOn >= GETDATE()-1) AND (Member.CuStatus = 'Approved') GROUP BY CreditUnion.Name )
),
MonthlyCTE AS
( SELECT type = 'Members Joined Last Month' , Borrowers = (select count(*) from LoanApplication INNER JOIN Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id where (LoanApplication.SubmittedOn >= GETDATE()-30) AND (Member.CuStatus = 'Approved') GROUP BY CreditUnion.Name )
, Depositors = (select count(*) from CDOrder INNER JOIN Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id where (CDOrder.SubmittedOn >= GETDATE()-30) AND (Member.CuStatus = 'Approved') GROUP BY CreditUnion.Name )
),
YearlyCTE AS
( SELECT type = 'Members Joined Last year' , Borrowers = (select count(*) from LoanApplication INNER JOIN Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id where (LoanApplication.SubmittedOn >= GETDATE()-360) AND (Member.CuStatus = 'Approved') GROUP BY CreditUnion.Name ) , Depositors = (select count(*) from CDOrder INNER JOIN Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN CreditUnion ON Member.CreditUnionFK = CreditUnion.Id where (CDOrder.SubmittedOn >= GETDATE()-360) AND (Member.CuStatus = 'Approved') GROUP BY CreditUnion.Name )
),
combinedCTE AS
( SELECT * FROM YesterdayCTE UNION ALL SELECT * FROM MonthlyCTE UNION ALL SELECT * FROM YearlyCTE
)
SELECT * , Members = Borrowers + Depositors FROM combinedCTE
But I get the following error message.
An error occurred while reading data from the query result set. Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (Microsoft Report Designer) =================================== Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (.Net SqlClient Data Provider)
I have an exisitng report that lists unit name, provider, runday, shift, patient. The report groups by unit name and there is a page break after each unit name. So in the current environment the report prints one page per unit that contains all of the providers, rundays, shifts, and patients for that unit name its grouping on. So the report would like like this when it prints:
Unit A Provider 1 Runday 1 Shift 1
patient 1 patient 2 Provider 1 Runday 1 Shift 2
patient 1 patient 2 Provider 1 Runday 2 Shift 1
patient 1 patient 2 Provider 1 Runday 2 Shift 2
patient 1 patient 2 Provider 2 Runday 1 Shift 1
patient 1 patient 2 Provider 2 Runday 1 Shift 2
patient 1 patient 2 Provider 2 Runday 2 Shift 1
patient 1 patient 2 Provider 2 Runday 2 Shift 2
patient 1 patient 2 PAGE BREAK Unit B............(repeat data from page 1) PAGE BREAK Unit C............(repeat data from page 1
The end user would like the ability to keep the report as is but would like to also be able to print the report as one page per each unit name, provider, runday and shift. so it would look like this
Unit A Provider 1 Runday 1 Shift 1
patient 1 patient 2 PAGE BREAK Unit A Provider 1 Runday 1 Shift 2
patient 1 patient 2 PAGE BREAK Unit A Provider 1 Runday 2 Shift 1
patient 1 patient 2
So I created a boolean parameter with a prompt of Page break by Unit, Provider, Runday & Shift? My thought is if the users sets this to False, the report will group on just Unit Name (the first example). If the user sets this to True, the report will group on Unit Name, Provider, Runday & Shift.
I set the grouping expression for this data table as: =iif(Parameters!Grouping.Value = "False", Fields!unit_Name.Value,(Fields!unit_Name.Value,Fields!Lname.Value,Fields!Rundays.ValueFields!Shift.Value))
Within the expression editor window it displays a syntax error and the report will not run.
I am working to move an application from MySQL to SQL Server. The person who developed the MySQL application has little database experience, and took some shortcuts that the lax nature of MySQL allows. One query with which I am struggling looks something like this:
SELECT StartTime + Offset, min(Sensor), max(Sensor) FROM SensorData WHERE SensorID = @SensorID AND StartTime + Offset > @BeginTime AND StartTime + Offset < @EndTime GROUP BY (StartTime + Offset) / 100 ORDER BY StartTime + Offset
What we are trying to accomplish is to return minimum and maximum sensor values over a number of periods between the BeginTime and EndTime. When I run this query in MySQL on a sample dataset, it returns a small number of rows, with each one being the min/max values for a portion of the overall period.
Under MS SQL Server 2008, SP3, I get the two following error messages: Column 'SensorData.StartTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Column 'SensorData.Offset' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I'm having a fight with Reporting Services at the minute when trying to compute an average at the row group level for a value summed in a column group.I have the following column groups:
Year Month Date
And the following row groups:
Region Product SubType (hidden, data at the date level is summed to Product)
At the moment I'm computing the average for SubType for each Date at the Product level (giving a decimal value), so for each day I end up with a nice average, that works. However I am unable to average that average over the whole Year for a Product. The issue being that I'm trying to combine Row Groups (Product) and Column Groups (Date/Year)
Hi, 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,
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?
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.
i dont't know how to select row with max column value group by another column. I have T-SQL
CREATE PROC GET_USER AS BEGIN SELECT T.USER_ID ,MAX(T.START_DATE) AS [Max First Start Date] , MAX(T.[Second Start Date]) AS [Max Second Start Date], T.PC_GRADE,T.FULL_NAME,T.COST_CENTER,T.TYPE_PERSON_NAME,T.TRANSACTION_NAME,T.DEPARTMENT_NAME ,T.BU_NAME,T.BRANCH_NAME,T.POSITION_NAME FROM (
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)
I 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.
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.
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.
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
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:
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
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 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.
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”
I am importing data from a CSV to a DB with a SSIS package. Among some things things it already does, it has to decide if the relation between one row and the following is acceptable. If it is not, the 2nd row is discarded, the next one is taken and the relation value is calculated again to decide whether to keep this one or not, and so on.
To calculate this value, I need to apply a formula that includes sin(), cos() and acos() functions. I have already written this formula as a scalar-valued function in my SQL Server.
So, my question is: - Is there a way to call a function (a UDF) within the Expression in a Derived Column dataflow item? and if not, - Hoy can I use trigonometric functions within the Expression in a Derived Column dataflow item?
I hope someona can tell me something about this... I'm falling into despair! :-s
I am reading an excel file with a field that consists of lastname, firstname. I am using the Derived Column transformation to separate the two fields. The firstname expression works fine: SUBSTRING(F1,FINDSTRING(F1,",",1) + 1,LEN(F1) - FINDSTRING(F1,",",1))
However, the lastname field keeps giving me an error when I use SUBSTRING(F1,1,FINDSTRING(F1,",",1) - 1). I'm sure I've used this substring before in visual studio. Could this be a bug? The error is below.
[Add Columns [2886]] Error: The "component "Add Columns" (2886)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "LastName" (3100)" specifies failure on error. An error occurred on the specified object of the specified component.
one of my SSIS packages use this expression to put todays date in if it is NULL:
(ISNULL(datejs)) ? GETDATE() : datejs
however, what I really want to do though is put a default date in like '2007-01-01' but I get syntax error because SSIS thinks it's a string, which it is I suppose.
I gotta use a Matrix since the number of months is dynamic.
In my test, I have 12 month columns. Under each Month column is a subordinate Count and a Difference column. In the Difference column, I want to calculate the difference in the Count columns relative to the previous month (if the Count for Jan is 15 and the Count for Feb is 27, then Feb's Difference column should be 12).
So, I am stuck trying to generate an expression for the Difference column. This expression needs to refer to the Count of the previous month.
RS's Previous() function cannot be used in a Matrix (because that's the compiler error that I get when I try to use it).
Does anybody know how to do this???
Do I have to use custom code to do this???
If I can't do this calculation in a matrix, then what good is it???