Reporting Services :: Count Values In A Column Based Upon Distinct Values In Another Column In SharePoint List
Sep 7, 2015
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B
red - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red - pen
blue - pencil
blue - highlighter
blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
red blue green
pen 2 0 1
marker 0 1 0
pencil 1 3 0
highlighter 1 1 1
We tried Sum but not able to display in single row.
View 2 Replies
ADVERTISEMENT
May 13, 2009
I have integrated sharepoint list data to SQL Server reporting services. I am using the below to query sharepoint list data using sql reporting services.
<Query> <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
<Parameters> <Parameter Name="listName">
[Code] .....
By using this query, I am getting a dataset which includes all the columns of sharepoint list. Among these columns, I wanted to display only 2 columns (i.e Region and Sales type) using chart. I have created a Region parameter but when I click preview, the drop down box is giving me all the repeatative values of region like
RG1,RG1,RG1,RG2,RG2,RG2,RG2,RG3..........
I wanted to display only distinct values of Region parameter so that whenever end user select region from the parameter drop down, it will display the respective value of Sales type column.Also when I select only RG1 parameter, it is giving me a chart including the sales type of all the Regions. (it should display me only the sales type of RG1) How can I link these 2 columns so that they will display the values respectively.
View 36 Replies
View Related
Sep 3, 2015
I have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.
What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column.
I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far.
View 3 Replies
View Related
Nov 25, 2015
I'm new to SSRS (We are using SQL 2014) and have been asked to create a report from SharePoint list data. One of the columns in my list contains XML data. I need to pull three different fields from this XML. I see lots on using xml as a datasource but I'm using a SharePoint list as my datasource. Before I get too far down the wrong road I thought I'd ask here if there was a built in means of parsing XML or if I should look at using the Text Function under Common Functions in the Epression builder?
View 8 Replies
View Related
May 6, 2014
In My cube I have :
A Fact table <<Company Revenue>>
2 Dimension <<Month.Dim>> , <<Company>>
I have 4 company say (A,B,C,D) but i have record for only company A & D that too only for December month .
When I create a SSRS report out of the cube i want a matrix where all the company names to be displayed in the rows & all the months in the columns and company's revenue in data
What my report is displaying :
Company | December
-------------------------------------------------------
A | 20000
-------------------------------------------------------
D | 3900
-------------------------------------------------------
what is needed :
Company | January | February | March | ........... | December
-----------------------------------------------------------------------------------
A | | | | | 20000
----------------------------------------------------------------------------------
B | | | | |
----------------------------------------------------------------------------------
C | | | | |
----------------------------------------------------------------------------------
D | | | | | 3900
-----------------------------------------------------------------------------------
How do i achieve this either in SSAS or SSAS.
View 5 Replies
View Related
Jun 22, 2009
In SQL server Reporting service we need to export excel formula for summing column values. scenario : After generating report we are exporting report to excel file using report viewer.when user will modify a column value we need to calculate(update) automatically sum of the column values.Basically we are setting excel formula.
View 3 Replies
View Related
Sep 18, 2015
MS SQL Server 2008 R2
I have both positive and negative values in a single column, where I want sum total of positive values & negative values. Is there any Expression for this to sort out.
View 8 Replies
View Related
Dec 9, 2013
I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?
CREATE TABLE [dbo].[Import_CustomerSales](
[CustomerId] [nvarchar](50) NULL,
[CustomeName] [nvarchar](50) NULL,
[CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]
View 5 Replies
View Related
Jan 9, 2015
Okay, I've been working on this for a couple of hours with no success. I'm trying to find the number of telephone numbers that are associated with multiple students at different school sites. I've created a temp table that lists all phone numbers that are associated with more than one student. I'm now trying to query that table and count the number of telephone numbers that are associated with more than one site. Essentially, I'm looking for parent/guardians that have students at different sites.
Here's an example of what I'm hoping to accomplish:
*In this example, I'm just trying to get a count of the different/distinct school sites associated with each number. If I can, at the same time, limit it to a count of > 1 (essentially excluding parents with students at the same site), even better :)
===================================
Temp table
===================================
SCHOOL | STU_ID | STU_PHONE
101 | 12345 | 111-222-3333
101 | 23456 | 111-222-3333
102 | 34567 | 111-222-3333
101 | 45678 | 999-888-7777
101 | 56789 | 999-888-7777
101 | 67890 | 555-555-5555
102 | 78901 | 555-555-5555
103 | 89012 | 555-555-5555
==================================
Wanted query results
==================================
STU_PHONE | #Students | UNIQUE_SCHOOLS
111-222-3333 | 3 | 2
999-888-7777 | 2 | 1
555-555-5555 | 3 | 3
View 1 Replies
View Related
Sep 11, 2007
We have a query in which there are 20,000 rows and 90 distinct ID's
If we say
SELECT distinct siteid ,ts1, ts2, ts3, ts4, ts5, ts6, ts7, ts8
from #TEMPX
we get the 90 distinct values, but if we say
SELECT distinct siteid ,ts1, ts2, ts3, ts4, ts5, ts6, ts7, ts8, ts1avg, ts2avg, ts3avg, ts4avg, ts5avg, ts6avg, ts7avg, ts8avg from #TEMPX
TS1 contains 90 distinct values, whily ts1avg has 2,000 disitinct rows
Is there a way to get Distinct to work against only one column, i.e. SiteID ?
View 2 Replies
View Related
Apr 16, 2014
How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:
Table Name: test1
Columns: m1 (int), m2 (int), m3 (int) >>> etc
Array/Row1: 1 2 3 4 5 6 7 8 9 10
------
Table Name: test2
Columns: n1 (int), n2 (int), n3 (int), n4 (int), n5 (int)
Row 1: 3, 8, 18, 77, 12
Row 2: 1, 4, 5, 7,18, 21
Row 3: 2, 4, 6, 8, 10
Answer: 2 out of 5
Answer: 4 out of 5
Answer: 5 out of 5
View 2 Replies
View Related
Feb 12, 2014
I want to add $ symbol to column values and convert the column values to western number system
Column values
Dollar
4255
25454
467834
Expected Output:
$ 4,255
$ 25,454
$ 467,834
My Query:
select ID, MAX(Date) Date, SUM(Cost) Dollars, MAX(Funded) Funding from Application
COST is the int datatype and needs to be changed.
View 2 Replies
View Related
Dec 6, 2013
Distinct name that match both subjects (math, science) from classname in level 2 only. Not sure where to even start. Example table below:
name subject level
bob math 2
hank math 1
joe science 2
bob science 2
joe math 2
ben science 2
carl science 1
View 2 Replies
View Related
Jun 16, 2015
Here is a requirement. Need to update the columns in the tables with the latest values available in CSV.
The file is based on department so the list of tables which is under this department all the corresponding tables needs to updated.
The CSV file which is dynamic in nature the number of columns changes it has header of the columns that needs to be updated.
The destination tables are listed under department table for each department. So I have to update the columns in the tables with the values in csv.
View 4 Replies
View Related
Dec 16, 2004
I have a table 'wRelated' with the following columns
[related_id] [int]
[channel_id] [int]
[mui] [varchar]
[price_group_id]
[type_id] [int]
[related_mui] [varchar] (100)
[date_started] [smalldatetime]
[date_ended] [smalldatetime]
[date_entered] [datetime]
[deleted] [tinyint],
[rank] [int]
data in column [mui] is repeated as the table has more than one entries for the same [mui],
The requirement is to select the distinct[mui] but value in all the other columns for the same mui should be select in the next row with null for the same [mui]
The recordset expected should be something like this.
[mui],[related_mui],[price_group_id],[date_entered],[date_ended] m123,rm345,'pr','12-10-2003',12-12-2004'
null,rm789,'ar','12-1-2003',26-2-2004'
null,rm999,'xy','14-12-2002',12-2-2004'
m777,rm889,'pr','12-12-2004',12-12-2004'
null,rm785,'yy','1-10-2002',12-12-2004'
m888,rm345,'pr','2-8-2003',12-12-2004'
null,rm345,'tt','30-7-2002',12-12-2004'
I have tried Unions and temporary table inserts.
View 1 Replies
View Related
Sep 7, 2012
My table has a column called Period i want to get a list of different periods example:
Period
1
2
31
1
2
4
12
31
2
then run an sql statement and should return you the following
Period
1
2
4
12
31
View 3 Replies
View Related
Nov 3, 2014
select pr.birthdate, pr.operationdate from patientrecord pr
Total no.of rows = 24420
select distinct pr.birthdate, pr.operationdate from patientrecord pr
Total no.of rows = 23528
It seems there are some rows with same birthdate and operationdate.
I want to get the list of rows(with all columns) that has got same birthdate and operationdate
View 1 Replies
View Related
Feb 22, 2007
Greetings,
I need to avg column data based on grid textboxes. I cannot use AVG because the column values come from a comma delimited string. I have tried using !Parameters to store a count and total for each column but they are always read only. What is the best approach.
The columns come from a field which is in the format of (99,75,60,100,-1,20,-1,80,75) for each record
-1 means the values are not counted in the average. I have a function for each textbox that parses the value from the list based on the column index. There can be 1 to many columns.
Everytime I to set the value of a field or parameter at runtime I get a read-only error.
Maybee the only way is to use calculated fields.
Any ideas.
View 2 Replies
View Related
Jul 20, 2005
12.) Now you have two different tables - each with two columns.Table #1Single Column2 rows with a value equal to 1 and 2Table #2Single column2 rows with a value equal to 2 and 4Construct a statement returning in a single column all the valuescontained, but not the common values.This is another question that I got in an interview, I missedit.......Thanks,Tim
View 1 Replies
View Related
Oct 9, 2015
I am using SQL server 2012 and Report builder 3.0 to build my report. I have build a report which product the following table
What I try to achieve is to add 2 calculated field at the bottom of the table which will represent 2 Total value based on a condition
OutTotal field should show the SUM of the Quantity only for ItemStatus=0
InTotal field should show the SUM of the Quantity only for ItemStatus=1
IMPORTANT : I cannot group my data because I need to shown them in a time Wise flow
I have try to insert the first field and define an expression but it gives an #Error. The expression I used is as below
=SUM(IIF(Fields!ItemStatus.Value = 0, Fields!ItemQuantity.Value*Fields!ItemUnitWeight.Value,0))
The Quantity filed in my table is calculated from expression Fields!ItemQuantity.Value*Fields!ItemUnitWeight.Value
View 2 Replies
View Related
Feb 24, 2015
I have the following tables:
tbl_Person (personID, first_name, lastname)
tbl_student (studentID)
tbl_stupar (personID, StudentID, relationship)
tbl_person_Phone (personID, phone)
I need to list all students who have both parents phone number is null. The parent relationship values 1 and 2 indicates the person is either Mom (value 2) or dad (value 1) of the student. Note: I am using student parent as an example to write my query.
View 4 Replies
View Related
Dec 13, 2013
When I am trying to insert to data from SQL ssis package to SharePoint list people or group column I am getting below error.[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "SharePoint List Destination" (25) failed with error code 0x80131500 while processing input "Component Input" (34). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
View 8 Replies
View Related
Nov 2, 2015
I have the following result query :
Which is produce by following SP :
SELECT
P.StoreID,
P.InventoryDate,
P.ProductID,
SUM(PIT.Quantity * P.ItemUnitWeight) AS TotalWeight,
SUM(PIT.UsedQuantity * P.ItemUnitWeight) AS UsedWeight,
[code]....
What is the way to add a calculated columned Named "Stocked" which will represent the sum of "Remaining" column for each rows ?
View 9 Replies
View Related
Feb 3, 2008
Hi,
I have a table employee with 4 columns,
empno fname lname deptno
1 abc def 10
2 fff hhh 20
3 abc def NULL
4 abc def NULL
5 abc def 50
suppose i want to know the total number of null values in a particular column say deptno how shuld i write a query?
select count(deptno) from employee
where deptno IS NULL..
When i query this i get the result as 0..
View 8 Replies
View Related
Apr 10, 2007
I have been banging my head against the wall for TWO days. I have
gone back and forth with a very patient guy on thescripts.com. You
can see the ridiculous thread here
http://www.thescripts.com/forum/threadnav628777-1-10.html
If you have time, at least peruse that so we don't go in circles.
Anyway, if you guys can help me solve this, I will be forever
grateful!!
Here is the "basic" problem:
Here is an example for TWO different entities in the database.
EntityID XmlFieldName Value
1 City Austin
1 State TX
1 Country US
2 CityName Los Angeles
2 StateCode CA
2 CountryCode US
2 Zip 111111
Here is how the two different results should be
where EntityID = 1
<Address City="Austin" State="TX" Country="US"/>
where EntityID = 2
<Address CityName="Los Angeles" StateCode="TX" CountryCode="US"
Zip="111111"/>
Notice how the attribute names (City or CityName, State or StateCode,
etc) are based off the XmlFieldName and I don't know in advance what
the possible values will be? I also don't know how many attributes
there will be, but they can be different per entity, depending on how
they have set up an address in our application.
Another thing to note, is that I kind of have this working in an sproc
using PIVOT and generating a table with the values that have the
correct dynamic column names (you can see this on my other thread I
posted above) but I REALLY need this to not use dynamic SQL (so can
use it in a function) if possible and be able to be used in a select
statement, whether it be a temp table as I would like to get a result
set back that I can do a FOR XML RAW on. If this is confusing, it is
because I am delerious. OR is there a way to return a table from an
SPROC that has dynamic columns built?
Please help!! Thanks so much!!!
Brian
View 6 Replies
View Related
Sep 26, 2007
Hi,
I have a table as follows
Table Master
{
Id varchar(20),
Cat1 datetime,
Cat2 datetime,
Cat3 datetime,
Cat4 datetime
}
and the data in the table is as follows
Table Master
{
Id cat1 cat2 cat3 cat4
-----------------------------------------------
1 d11 null d13 d14
2 d21 d22 d23 d24
3 NULL d32 d33 d34
4 d41 d42 NULL NULL
}
I want to retrive column names and its values wheb the ID matches to some value.
Can any one please let me know how to do this?
Thanks alot
~Mohan
View 3 Replies
View Related
Nov 30, 2015
I have created a ssrs report which connects to vertica database through odbc connection. When I try to pass parameter value through parameter (e.g.: column name IN (@parameter) ) then getting error message in query designer prompting "Error in list of values in IN clause. Unable to parse query text. ". Using sql server 2012 , visual studio 2010 version and HP Vertica 7.1 .
View 6 Replies
View Related
Oct 1, 2015
My data is like below:
ClassId ClassName StudentId Subject SubjectId
1 ESL 12 English 20
1 ESL 13 Science 30
1 ESL 12 Social 40
1 ESL 12 Maths 50
Required output: parameters are Subject column values
ClassId ClassName TotalStudents SubjectIds
1 ESL 1 20, 40, 50
1 ESL 1 30
When one student takes multiple subjects then count student only once, so in the above data 12 is one student id takes multiple subjects so counted only once. TotalStudents value is 1
I did write below query:
Declare @subjectids string
set @subjectids = '20,30,40,50'
-- will split @subjectids and store in temp table
select classname, classid, Count(Distinct StudentId)
from mytable
where SubjectsIds in @subjectIds
group by ClassId, ClassName, SubjectId,
but it gives me below output:
ClassId ClassName TotalStudents SubjectIds
1 ESL 1 20
1 ESL 1 30
1 ESL 1 40
1 ESL 1 50
View 9 Replies
View Related
Sep 21, 2007
Hi,
I have at table as foolows
Table Cat3
{
ID,
Update datetime
}
and also have a master table as follows
Table Master
{
ID,
Cat1 Datetime,
Cat2 datettime
}
My requirement is to alter themaster table schema i.e to add a column with the name as of the table name i.e Cat3 and will lok lie as foolows
Table Master
{
ID,
Cat1 Datetime,
Cat2 datettime,
Cat3 Datetime
}
I would like to insert the data to this column. The sample output is as follows
Before insertng the data of table ;Cat3' into the Master table
Table Master
{
Id Cat1 Cat2
---------------------------------
1 D1 D2
2 D2 NULL
3 D3 D4
}
And The Cat3 table data is as follows
Table Cat3
{
ID Update
--------------------------
1 D5
3 D6
4 D7
}
The final putput of the master table should be as follows
Table Master
{
Id Cat1 Cat2 Cat3
------------------------------------------------------
1 D1 D2 D5
2 D2 NULL NULL
3 D3 D4 D6
4 NULL NULL D7
}
Can any one please let me know the query to achieve this
Thank you very much for your time and support
~Moahn
View 10 Replies
View Related
Feb 26, 2008
I am trying to count a column field in a single table and return two count values as one record set using group by.
field1 = group by (department) nvarachar
field2 = count (closed) datetime
I have tried using derived tables with no luck getting the desired result.
field2 is a datetime field as indicated I want a count for two conditions
1. WHERE field2 is null
2. WHERE field2 is not null
End Results would like this
======
Department | OpenItems | ClosedItems
Department1 | 32 | 24
Departmnet2 | 87 | 46
Department3 | 42 | 76
=======
I got it *almost* working with derived tables, but the group by function was not putting the department as one single row. I was getting multiple rows for departments.
I realize this is probably a simple answer and I am making this a lot harder than it actually is....
Any suggestions?
View 3 Replies
View Related
Jul 14, 2015
How to set default values for selection list navigator without using parametrized queries for data source view.
View 2 Replies
View Related
Oct 27, 2015
I'm trying to Update a column table based on values from another table but I need these values are random.My query looks like this and doesn´t work
DECLARE @Rowini int,
DECLARE @lastrow int
SET @Rowini = 1
SET @Lastrow = 80000
[code]...
View 8 Replies
View Related
Aug 7, 2007
Hello all. I hope someone can offer me some help. I'm trying to construct a SQL statement that will be run on a Dataset that I have. The trick is that there are many conditions that can apply. I'll describe my situation:
I have about 1700 records in a datatable titled "AISC_Shapes_Table" with 49 columns. What I would like to do is allow the user of my VB application to 'create' a custom query (i.e. advanced search). For now, I'll just discuss two columns; The Section Label titled "AISC_MANUAL_LABEL" and the Weight column "W". The data appears in the following manner:
(AISC_Shapes_Table)
AISC_MANUAL_LABEL W
W44x300 300
W42x200 200
(and so on)
WT22x150 150
WT21x100 100
(and so on)
MT12.5x12.4 12.4
MT12x10 10
(etc.)
I have a listbox which users can select MULTIPLE "Manual Labels" or shapes. They then select a property (W for weight, in this case) and a limitation (greater than a value, less than a value, or between two values). From all this, I create a custom Query string or filter to apply to my BindingSource.Filter method. However I have to use the % wildcard to deal with exceptions. If the user only wants W shapes, I use "...LIKE 'W%'" and "...NOT LIKE 'WT%" to be sure to select ONLY W shapes and no WT's. The problems arises, however, when the user wants multiple shapes in general. If I want to select all the "AISC_MANUAL_LABEL" values with W <= 40, I can't do it. An example of a statement I tried to use to select WT% Labels and MT% labels with weight (W)<=100 is:
Code SnippetSELECT AISC_MANUAL_LABEL, W
FROM AISC_Shape_Table
WHERE (W <= 100) AND ((AISC_MANUAL_LABEL LIKE 'MT%') AND (AISC_MANUAL_LABEL LIKE 'WT%'))
It returns a NULL value to me, which i know is NOT because no such values exist. So, I further investigated and tried to use a subquery seeing if IN, ANY, or ALL would work, but to no avail. Can anyone offer up any suggestions? I know that if I can get an example of ONE of them to work, then I'll easily be able to apply it to all of my cases. Otherwise, am I just going about this the hard way or is it even possible? Please, ANY suggestions will help. Thank you in advance.
Regards,
Steve G.
View 4 Replies
View Related
