Hi:
How can I combine 2 sets of group by together?
same criteria on group by Region, county
setA with where sum(timeSec) <= 30
while setB with where sum(timeSec) > 30
they are not necessary mutual exclusive. thus, union is not good.
However, when I use derived table method, I will have 'dups' on column of 'Region' and 'county', 2 total are fine.
thanks
David
Hi,
I started working with SRS from the past one month. I am breaking my head to do this. My report has a single stored procedure. I made it to work all values of a parameter like
exec sp_employee 'All' (Returns data for all employees)
exec sp_employee '1111' (Returns data for an employee with id 1111)
exec sp_employee '1111,2222' (Returns data for an employee with id 1111 as well as 2222)
This part works perfectly. I could successfully pass the values as mentioned above using a code block.
Now my challenge is how to layout the data on the report.
I need to show the report as follows:
Employeeid Employee Name Address etc.......
Skills:
Skill# Skill Name
1 11111
2 22222
3 33333
4 44444
Contacts:
Contact Type Contact#
Main 111-1111
Emergency 222-1111
Eductation:
Name University
Degree UofU
Masters UofU
I tried using group by employeeid. But i can only show one group of data either Skills/Contacts/Education.
I do not know if I use sub-report, how to pass the parameter from the main, when I pick multiple values for Employeeid.
Any idea how to do this? I appreciate your help,
Thank you,
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, ...
Hello,
I'm new to MDX and was hoping someone could answer this question. I'm working on an MDX query, a simplified version is below.
What I'm trying to do is to pull 2 sets, based on a date range. Based on those, I'd like to see the total "cost" measure for each set, and divide one by another.
What I'm finding is that when I run the query below, the cost is broken out by month. So, my select statement gives me the cost for January 2008 and February 2008 in the CurrentPeriod set, and for January, February, etc. 2007 in the PriorPeriodData set. What I want instead is to get the total "cost" measure for all the months in each set. I believe this is what I'd get if I put the date range in a subcube or a where clause - but in that case, I wouldn't be working with 2 distinct sets.
Is there some other way to write this, or to combine all the months in the set before the measures?
WITH
SET [CurrentPeriod] AS
'{[Activity Date].[Date].&[2008-01-01T00:00:00]:[Activity Date].[Date].&[2008-02-01T00:00:00]}'
SET [PriorPeriod] AS
'{[Activity Date].[Date].&[2007-01-01T00:00:00]:[Activity Date].[Date].&[2007-12-01T00:00:00]}'
MEMBER [Measures].[Cost Variance] AS
'([CurrentPeriod],[Measures].[Cost]) /
([PriorPeriodData],[Measures].[Cost])'
SELECT NON EMPTY {
([CurrentPeriod],[Measures].[Cost]),
([PriorPeriodData],[Measures].[Cost]),
([Measures].[Cost Variance])
} ON COLUMNS
FROM [Cube]
Thanks for your help!
PLEASE SEE my update (post #3) for a better simpler explanation!!!!!
Hi,
I need a query which is basically
(query 1)
Except
(query 2)
SQL server supports
(query 1)Union (query 2)
but I can't get the Except to work.
Alternatively,
I also tried to implement it by using the "NOT IN" but didnt work.
Background: 3 tables, accnts, Opty, Opty_postn
I want
the accounts who have 1 or more opty's at 100% (sold) before 1/1/2002 but zero opty's at 100% after 12/31/2001.
accnts has fields ID & name, ROW_ID
opty has fields acct_id, prob, ROW_ID
opty_postn has fields opty_id, close_dt
My query:
SELECT accnt.name
FROM accnt
WHERE
accnt.ROW_ID IN
(
SELECT acct_id
FROM opty
where prob = 100
AND opty.ROW_ID IN
(SELECT opty_id
FROM opty_postn
WHERE close_dt<1/1/2002)
)
AND accnt.ROW_ID NOT IN
(
SELECT acct_id
FROM opty
where prob = 100
AND opty.ROW_ID IN
(SELECT opty_id
FROM opty_postn
WHERE close_dt >12/31/2001)
)
AND accnt.ROW_ID = opty.accnt_ID
my intent was
select account.names
where id
is in set(prob = 100 & sale before 1/1/02)
but not in ( prob = 100 & sale after 21/31/01)
SO ,the accounts which have sales both before and after get counted. But I want only those accoutns which have sales only before 1/1/02.
I hope i explained this right.
Thanks in advance for your help.
Ash.
Hi,
Can someone briefly explain which this feature of Analysis services is and how canit be of use.
Also, besides BOL, I'll appreciate if someone can recommend a good reading on this.
Thanks.
Hello.
i have dimensions: month, country, customer, item.
measures: amount, price.
i would like to get such result :
for each customer from USA: sum (amount of item in June * price for the same
item in January).
is it possible?
thank you for your help, Mike.
I have the following table structure
CREATE TABLE [dbo].[QDisc](
[Id] [int] NOT NULL,
[MinVal] [int] NOT NULL,
[MaxVal] [int] NOT NULL,
[PerVal] [int] NOT NULL,
CONSTRAINT [PK_QDisc] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
I need to be able to select unique overlapping sets of data based on the minval and maxval.
Simple Example
minval maxval
5 15
16 25
10 20
query would produce
minval maxval
5 10
11 15
16 20
21 25
More Complex example
minval maxval
5 15
16 25
10 20
7 7
1 100
query would produce
minval maxval
1 5
6 6
7 7
8 10
11 15
16 20
21 25
26 100
Extra points if anyone can do this without SP and cursor but I'd be satisfied if someone supplied it that way.
Hi,I've tried without success to create a function where one of theparameters determines the final line of a select statement.The final line includes a 'where referencenumber in (@set)' line.. and@set is the parameter I wish to use which is a string.If the set is just one value, it works fine, however with anything morethan that I just get no records returned.. If I output the parameter,and copy and paste that into query analyser I do get the correct result,so it's nothing to do with string delimiters etc..Any ideas to fix this problem? Or am I going to have to dynamic sql thewhole thing?Cheers,Chris
View 2 Replies View RelatedI need to manipulate some sets, doing joins based on intersection, and looking for subsets. Is there any way to store an array in a single data field? And then do operations based on those array lists? e.g. -
{1,2,3} intersect {2,3,4} ==> {2,4} (would like to then be able to do joins based on intersection, so that I could say, return all cases where two sets have all but one member from each in common...)
Hi,
I'm currently trying to retrieve results from a large dataset, there are over 45000 records and I need to use them all to peform counts etc. I have set up views, but my page is still being returned slowly, is there anything I can do to speed this up?
Thanks
Gemma
I am trying to query one table and get two different timeperiods of data, I am summing monthly totals to provide a running year total, but I also need last month's total in a seperate column. This is what I have so far but the subquery makes me group it which provides duplicate grouping.DECLARE @LASTPD AS INT
SET @LASTPD = (SELECT MAX(LASTPERIOD) FROM TABLE)
SELECT NAME,
POST_PD AS [MONTH],SUM(CHARGE_AMOUNT) AS MONTHLY_$,
LASTMONTH.LAST_MONTH,(SELECT SUM(CHARGE_AMOUNT) AS LAST_MONTH
FROM TABLE INNER JOIN TABLE2
ON TABLE2.NAME = TABLE.NAME
WHERE POST_PD = @LASTPD
AND TABLE2.NUM= 539
GROUP BY NAME) AS LASTMONTH
INTO #TEMP_SAFROM TABLE
INNER JOIN TABLE2
ON TABLE2.NAME = TABLE.NAME,(SELECT SUM(CHARGE_AMOUNT) AS LAST_MONTH
FROM TABLEWHERE TABLE2.NUM = 539
GROUP BY NAME, POST_PDORDER BY NAME, POST_PD
SELECT NAME,
LAST_MONTH,
CAST(SUM(MONTHLY_$)AS DECIMAL(20,2)) AS YEARLY_$
FROM #TEMP_SA
GROUP BY NAME
ORDER BY NAME
I need to upgrade a 6.5 box to 7.0 but also change the character sets. Let me know how this sounds.
-Have a 2nd machine w/ 6.5 on it and same char sets.
-backup/restore data from prod box to 2nd machine.
-upgrade 2nd machine to 7.0 using the same char sets.
-uninstall prod machine and install 7.0 w/ new char set.
-use dts to move data from 2nd machine to new machine. (changing char sets from old to new)
We're constructing a three-tier application. We want the middle tier to
extract a recordset from the database, disconnect from the database,
then pass the recordset to the front tier. After changes have been made
by the front tier, it will pass the recordset back to the middle tier,
which will reconnect, and update the database.
The problem:
Using stored procedures, the recordset is no longer available
once the connection has been closed. Is there any way, using stored
procedures, to be able to keep the recordset available after the
connection has been closed, either by preserving it, copying it, or
otherwise?
I have been tasked with creating a report that shows sales of our products grouped into buckets of 5 each, DESC. I have a table that has the itemNo and revenue. The final report would be something like:
Top 5 Spreads$695,066
Next 5 Spreads$467,845
Next 5 Spreads$416,946
Next 5 Spreads$361,946
Next 5 Spreads$305,607
Next 5 Spreads$270,567
Bottom Spreads$15,954
My initial thinking was to use row_number() and partition to label the rows in groups of 5 like:
item 130,0001
item 229,0001
item 328,0001
item 427,0001
item 526,0001
item 625,0002
item 724,0002
item 823,0002
item 922,0002
item 1021,0002
item 1120,0003
item 1219,0003
item 1318,0003
And then Sum the revenue, grouping by this row_number. But I haven't been able to get it working right.
Dear friend, the following is my query.
1.SELECT a.Avg_Binaryvalue as Avg1,a.Min_Binaryvalue as Min1 from MeasurementData a where Attribute_Flag ='5'
2.SELECT b.Avg_Binaryvalue as Avg2,b.Min_Binaryvalue as Min2 from MeasurementData b where Attribute_Flag ='6'
i need to join this two resultsets. please give me sample query to join this two result sets
i have these 2 queries with the included results sets...What commands could I use to take the TotalBlueCircle Column from the 2nd Results Set and have it included next to the TotalRugby column in the 1st results set??Do i need to do a UNION or use Sub Queries or something else?
View 5 Replies View RelatedI have a maintenance plan setup to expire backup sets after 4 days, but its keep backup copies for an infinite amount of time. I might be misunderstanding whats suppose to happen, but I would expect the files to be removed after 4 days.
View 3 Replies View RelatedHi All,
I would like to match two sets of data. I have setup a view of data that contains a group of customers and their details. I want to view this data, but also find these customers in another table based on matching their surname and date of birth, then retreive the information stored on these customers from the second table.
Does anyone have any suggestions how i would go about doing this?
Thanks in advance
Humate
quote:Originally posted by Michael Valentine Jones
It takes real skill to produce something good out of a giant mess.
I have the following situation. One set of data has 274 rows (set2)and anther has 264 (set1). Both data sets are similar in structure aswell as values for both of them were extracts from the same parenttable. Hope the info would substitute DDL. I need to find the "gap"rows between these two sets.Attempted to run a query likeselect count(*)from set2where not exists(select *from set1)did not yield what I desired. What else to try?TIA.
View 12 Replies View RelatedHello!My client has a need to be able to store Japanese characters in theirPeopleSoft database. So we need to change the character set from fromLatin1_General (1252) to Japanese character set (932) on SQL Server2000 Enterprise. I have 2 questions:1) I would like to know if in SQL Server, the character set is machinespecific or is it defined at the database instance level?2) Can multiple installs of SQL Server co-exist on a single server withdifferent default character sets?ThanksVishal
View 1 Replies View RelatedHi!Another silly question:If a stored procedure returns multiple result sets, how do I choose the oneI want to insert into a table?For example sp_spaceused returns two result sets if object name is ommited.I only need the first result set. How do I do that?Tnx!Darko
View 2 Replies View RelatedHi, I'm trying to return 2 different result sets using the below query mapped to 2 different variables in my execute sql task. I've tried this with one task and 2 seperate tasks but can't get it to work. Is this possible using only one task? It keeps giving me a result set error.
SELECT COUNT(*) AS DeceasedCount
FROM AMGR_User_Fields_Tbl
WHERE (Client_Id = '' or Client_Id is NULL) and Type_Id = 53
SELECT COUNT(*) AS LostCount
FROM AMGR_User_Fields_Tbl
WHERE (Client_Id = '' or Client_Id is NULL) and Type_Id = 469
I was working on what I was told was SQL 2012 and it turns out it is SQL 2005. I wrote two procs that I need to convert to 2005. Here is the code:
SELECT
era_provider_name AS Provider,
RIGHT([era_upi], 5) AS 'ERA Upi',
[era_fy] AS 'ERA FY',
ProcGrp,
COUNT(DISTINCT UCI) AS 'Client Count',
[Code]....
I'm not finding an efficient way to do this. I cannot use GROUPING SETS with 2005. Here is the code for the second proc:
SELECT
CASE
WHEN GROUPING(era_provider_name) = 1 THEN 'TOTALS'
ELSE era_provider_name
END AS era_provider_name,
CASE WHEN GROUPING(era_fy) = 1 THEN 'TOTALFY'
[Code] ...
The results as in SQL 2012 are exactly as I would like them. I want to mimic those results in 2005.
Hi
I have a matrix whos colunm group is filed by Dataset1,
now i want to add naother colunm group,but using the Dataset2
can I use two different dataset for a matrix,
for differnt colunm group
please help me in this regards
thanks
I popped into the Transact-SQL forum to get some help and created the dbo.selectaudit1 table, which can be queried succesfully.
Code Blockcreate table dbo.selectaudit1 (startkey varchar(10) not null,
endvalue varchar(10) null,
endkey as coalesce(endvalue, startkey))
insert into dbo.selectaudit1 (startkey)
select '042'
union all select '140'
union all select '2089'
union all select '2031'
union all select '2051'
union all select '2100'
union all select '2299'
union all select '2300'
union all select '2349'
union all select '2350'
union all select '2389'
union all select '2390'
union all select '2399'
union all select '2732'
union all select '2733'
union all select '2849'
union all select '2850'
union all select '2883'
union all select '2898'
union all select 'V073'
union all select 'V078'
union all select 'V100'
union all select 'V109'
union all select 'V580'
union all select 'V581'
union all select 'V661'
union all select 'V662'
union all select 'V671'
union all select 'V672'
union all select 'V711'
union all select 'V760'
union all select 'V769'
insert into dbo.selectaudit1 (startkey, endvalue)
select '1400' ,'2089'
union all
select '2100', '2299'
union all
select '2300' ,'2349'
union all
select '2350' ,'2389'
union all
select '2390' ,'2399'
union all
select 'V100' ,'V109'
union all
select 'V760' ,'V769'
Then in reporting services, I created a report with the following query in my dataset. This query works fine in a BIDS query windows, however, when I refresh my dataset and attempt to execute the query I am getting the following error:
Cannot find the object "selectaudit1" because it does not exist or you do not have permissions.
(Microsoft SQL Server, Error: 1088)
Code Block
Select
p.MRN
, p.PatientName
, CONVERT(CHAR(20),p.AdmitDate,101) AS AdmitDate
, CONVERT(CHAR(20),p.DischDate,101) AS DischDate
, p.VisitTypeCode
, d.VisitTypeName
, p.AnyDx
, p.PrinDxCode
, p.PrinDxDesc
, p.SecDx1Code
, p.SecDx2Code
, p.SecDx3Code
, p.SecDx4Code
, p.SecDx5Code
, p.SecDx6Code
, p.SecDx7Code
, p.SecDx8Code
, p.SecDx9Code
, p.SecDx10Code
, p.SecDx11Code
, p.SecDx12Code
, p.SecDx13Code
, p.SecDx14Code
, p.SecDx15Code
From dbo.PtMstr p inner join
ampfm.dct_VisitType d on d.VisitTypeCode=p.VisitTypeCode
INNER JOIN dbo.selectaudit1 sel
ON (PrinDxCode between sel.startkey and sel.endkey
AND DischDate between '2006-11-01' and '2007-09-30')
OR (SecDx1Code between sel.startkey and sel.endkey
AND DischDate between '2006-11-01' and '2007-09-30')
What am I missing?
Hi,
I am aware that SSRS does not support stored procedures with multiple data sets, but what if the same stored procedure may only return one set based on a parameter?
For example, lets say if parameter @TableID = 0, the stored procedure returns 2 result sets. But if @TableID = 1, or @TableID = 2, the SP only brings back 1 result set. Is this also not supported?
When I try to do this it still only brings back the fields from the first table. Am I doing something wrong?
Thanks for any insight.
What is the recommended mechanism for selecting paged results from SQL.
Presently I pass various params including the request Max Items Per Page and the requested page.
The I execute the query as a count with the search params.
Then comes the paging logic, which validates the page number against the request page and number of hits etc.
Then a temp table and record variables are created for the results.
Then I run the query again with a cursor and select the appropriate Items into the temp table based on the paging values (First Item and Last Item).
Then I return the temp table & some additional return params with the Total Hits etc.
The Stored procedure is accessed via an ADO.Net client and the system.data.IDBReader populates a .Net strongly typed collection and is for read only display.
Thanks for any input,
Martin.
Hi!
I've posted a feedback with Microsoft to see if we can get them to fix the issue described below, but so far no one from Microsoft has commented to let us know what they're doing about this problem! I'm posting this here to see if maybe we can get more people to rate this feedback or chime in on what a pain it is! Please feel free to add your own comments or how you had to work around this issue and whether or not you think this is something Microsoft should be addressing NOW.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=311679
Provide Individual Page Numbering per Group and Total Pages per Group
Currently in a Reporting Services report, you can't readily reset the page number for each group in a table, nor can you display the total number of pages per group. For example, if I'm printing invoices and each invoice is a separate group, I'd like to be able to print "Page 1 of 5" , "Page 2 of 5" etc. for the first invoice, then "Page 1 of 3" when the next invoice begins, and so on. This was easy in Crystal Reports. I realize that Crystal Reports has a two-pass process that enables that kind of pagination. However, this is REALLY important functionality that's just missing from Reporting Services and I'm hoping you'll provide it REALLY SOON! Yeah, I know there are work-arounds if you can know exactly how many rows of information there are on each page. But gosh! That's not practical, especially if you have second level groups inside the main group or text blocks in rows that can 'grow' to more than one line. I've read a couple of work-arounds, but none of them works correctly and consistently when more than one user is running the same report or when you print the report while you're looking at it on the screen. I still may need access to the overall report page number and the overall total number of pages, so don't get rid of that. It's just that if you're doing this already for the entire report, I don't see why you can't do it per group! Lots of people have been asking for this for years, and I don't understand why it hasn't been implemented.
I've read a few articles on this topic, but no one has come up with a decent work around. My theory is that Microsoft should be addressing this immediately. This is major functionality that's just plain missing from SSRS and should have been there from the start. If anyone from Microsoft can let us know what's going on with this issue or if anyone would like for me to clarify this further, feel free to let me know.
Thanks!
Karen
I have an SSRS report with groups that when exported to excel contains drill-in's (plus marks on left side). The issue I have is that for all the groups in the drill-in, those cells become merged. I want to keep the group drill-in but have the cells UNMERGED. I have heard this can be done with the RDL XML but I don't know what to modify to accomplish this.
View 4 Replies View RelatedI'd like to ask how you would get the OUTPUT below from the TABLE below:
TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B
OUTPUT:
category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6
The code would go something like:
Select category, count(*), .... as id's
from TABLE
group by category
I just need to find that .... part.
My stored procedure displays two result sets. How can i use that result sets in my 3-tier application. I want to bind first resultset to repeater control and second to label control. I am using SqlDataReader...
View 6 Replies View RelatedSupposing I have a sort order/char set installed and some databases working under these circumstances.Is there any way to change order and sets without losing my old databases? If I try to rebuild databases, I lose them,and when i try to restore them, there's a conflict between the new SQL Server 6.5 configuration and the Databases previously created in old sort order/char sets?Is there any solution to this,or once I have chosen the database configurations,I'll have to either recreate the data or die with them??? Neede answers ASAP....
Thanks for your attention, Luciano