Selective Row Group Display...
Mar 31, 2008
I've got a report linking to a SSAS cube. It has 2 row groups Region and Country. The Country group is a subset of the Region group and works fine, it collapses and expands ok.
Now, due to a boss who doesn't like the way this looks, I have to find another way to display it. I was wondering if it is possible to have a report parameter that could be selected and the appropriate column grouping be displayed depending on the value.
i.e. User selects Report Parameter value 'Region' and the Region row group becomes visible, the Country group becomes invisible. The exact opposite happening if the user selects Country.
I can't find any obvious way to do this. Any ideas?
Rob
View 1 Replies
ADVERTISEMENT
Sep 29, 2015
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, ...
View 4 Replies
View Related
Mar 29, 2007
hi
i have a database that has multiple records under the same id (each time a record is updated, it's assigned a date_value), i'm wanting one row to be generated using the data from the most recent date_value field.
a sample of the database looks something like this:
ID date_value data1 24 march info11 25 march info2
my statement looks like this:select max(date_value), data from table_name where data is not null group by id
this seems to bring up the following error for all the fields not in the group by part of the code:Column 'COLUMN_NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
what am i doing wrong?
TIA!
View 1 Replies
View Related
Aug 3, 2015
I would like to display all the products with maximum SeqNo from the table below:
TABLE
ProductIDSeqNoBalance
111215
11135
111420
111510
12115
1212100
121325
121445
OUTPUT
ProductIDSeqNoBalance
111510
121445
View 3 Replies
View Related
Jul 2, 2007
Hi,
I want to display details ection for each group in a single table using one dataset.
For ex:
Group 1 Row --> Name Age
Details 1 Row--> XXX 30
Details 2 Row--> YYY 20
Group 2 Row --> City Country
Details 1 Row--> CCC ZZZZ
Details 2 Row--> BBB AAAA
View 3 Replies
View Related
Jun 26, 2007
Hi, I am creating a matrix and adding multiple row groups. The problem is that I am unable to display headers for each of these groups - can anyone help??
View 6 Replies
View Related
Jul 24, 2007
I have to display group header inspite of not having records in that group.
Group1 [First page orgid1]
values
Group 2valuesgroup1 [Second Page orgid2]
No values group2 no values
View 1 Replies
View Related
Aug 25, 2015
How can I display 0 when using COUNT and GROUP BY?I'm using SELECT CASE in my query. I was trying to use COALESCE but no result, COUNT result = 1. (there should be 0).
COALESCE((COUNT(DISTINCT (CAST((CASE
WHEN CurrStat = @Stat AND LogDate = @LogDate THEN Enumber ELSE 0 END) AS int)))), 0) AS InTrack,
View 5 Replies
View Related
May 16, 2008
I have a report that is grouped based on a field. There is no way to pre-determine the number of detail lines each group will have. Is there a way to set the print condition such that if any group details do not fit onto one page then the whole group should be printed on the next page. Something like this:
Group1
DetailLine1 -------------
DetailLine2-------------
DetailLine3------------
-
-
DetailLinen-------------
Group2
DetailLine1 -------------
DetailLine2-------------
DetailLine3------------
-
-
DetailLinen-------------
Group3
DetailLine1 -------------
DetailLine2-------------
DetailLine3------------
-
-
DetailLinen-------------
If ALL Group2 details do not fit onto the same page print it on a new page. (I don't want each group on a separate page, I just want the group detail to start on a new page IF THEY DO NOT ALL fit on one page)
View 3 Replies
View Related
May 25, 2015
I am new to SSRS. I created a reporting services with 3 groups. I would like to know how to create different colors for each group so that all my values displayed by color group ?.
View 4 Replies
View Related
Jun 17, 2007
The following is a simplified version of my SQL statement. I am attempting to do a simple count(*) with two groupings and a where clause. This is Select command for a GridView. However, I am unable to display zeros. The rows are completely missing and I would greatly appreciate someone's help. I have already tried Group By All, but that, unfortunately, does not work. Here is my SQL statement:
"SELECT [GENDER], [RACE], COUNT(*) FROM [TABLE] WHERE ([COLUMNNAME] ='SOMETHING') GROUP BY [GENDER], [RACE]"
Thanks for the help in advance!
View 1 Replies
View Related
Dec 18, 2007
Hello
I have a report that retrieves its data from Analysis Services. The data includes a count and dollar value of projects against their current status: It looks something similar to
(group1) status1 10 $200,000
(detail) p1 1 $5,000
p2 1 $10,000
.
.
p10 1 $20,000
(group1) status3 5 $90,000
(detail) .
.
(group1) status4 15 $150,000
(detail) .
.
In the report I hide the detail rows. I have a fixed/known number of statuses (in this case 4) and need to show all 4 in the report. eg
(group1) status1 10 $200,000
(detail) p1 1 $5,000
p2 1 $10,000
.
.
p10 1 $20,000
(group1)status2 0 $0
(group1) status3 5 $90,000
(detail) .
.
(group1) status4 15 $150,000
(detail) .
.
ie in this case I need to show status 2 (that doesn't exist in the data set) with zero totals.
Does anyone know if this is possible to get SSRS to display each of the status groups (in a known fixed list) and then match them to the records in the dataset.
As an alternative, if I were using SQL Server I could add rows to the dataset using a union statement. Is there similar functionality using mdx? My mdx skills are very basic.
Thanks
Stewart
View 1 Replies
View Related
Feb 14, 2008
Friends,
I am using SSRS (Sql server reporting services) for one of my report related to EIS-MIS.
i want group wise page numbering and also display the no. of pages for that particular group.
e.g suppose group 1 has 5 pages then it should display
page no. 1 /5, 2/5,3/5,4/5, 5/5. now suppose group change then it shold display 1/4 like this
I have already implemented groupwise page numbering using then custom code and i have call that function from the header portion of the report.
now the question is how can i display the total no. of pages in particular group?
For that i have add one count column in query itself but as we know we can't use field value in header and footer portion and also we can't use global variables in data portion.
So kindly guide me
Thanks,
Manoj Patel
View 2 Replies
View Related
Aug 9, 2007
Hello,
I am fairly new to SQL 2005 and Reporting Services.
We are trying to create a report that will display sales data based on group membership from Active Directory.
For example, if USER1 logs in and looks at a Year to Date Sales report, it will only show data that pertains to his group. If USER2 logs in and accesses the same report, it will display different Year to Date information because he is in a different group.
Background Information: We are running SQL 2005 Enterprise Edition Service Pack 2 with Analysis and Reporting Services. We are delivering the reports through a Sharepoint site.
Please let me know if anyone has a good way to tackle this.
Thanks,
Justin
View 6 Replies
View Related
Aug 16, 2007
Trying to track the users who are logged in and making changes to my database. I have granted permission through the Windows Groups and the log displays the group names and not the user names. Is there something to set to display the user and the group?
Thanks
Amanda
View 4 Replies
View Related
Apr 6, 2008
Hi. I have a SQL statement currently ending with "ORDER BY sc.TypeID, sc.ObjetID DESC"where the TypeID can be of value 1,2,3,4,5,6,7,8, 9 and 10. What I would like to achieve is to selectively order the datas by TypeID, meaning in this case that I want ALL the TypeID of value "1" to come first and then all the rest ("2" and higher) un-ordered. This is important that the rest remains unordered by their TypeID as I only want them to be ordered by their ID (ObjetID DESC). Is that possible? If yes how?Thank you.Francis
View 4 Replies
View Related
Feb 3, 2004
Hello all,
Is there a way to do a selective restore? I need to create a dev db and the prod backup is 17 gig and I have a space crunch on the server. My idea was to restore the complete backup and subsequently shrink the db size after truncating data in tables which are not frequently used? Is that a good idea. Any help will be appreciated.
Thanks in advance!
View 1 Replies
View Related
Jan 10, 2006
Hi,
I need to be able to export from an existing database, all fields with a certain column id. I have been pointed in the direction of bcp, however I am having difficulty finding the right syntax.
Thanks,
D.
(P.S. MSSQL 2000 Server)
View 6 Replies
View Related
Mar 6, 2007
I have
table T2
id amount ss
1142
2151
3162
4172
table T3
idamount ss
131
142
251
362
472
481
491
I run
UPDATE t2, t3 SET t2.amount = t3.amount
WHERE t2.id1=t3.id and t3.ss=1;
T2 becomes
ID1amount ss
132
251
3162
492
but I want t2 to be
ID1amount ss
132
251
3162
4 82
how can I do that?
Thanks
View 4 Replies
View Related
Feb 6, 2007
Is it possible to use replication for inserts and updates only for some tables and inserts updates and deletes on others. I want to be able to delete data from the source database but not my replicated database. Does anyone know of a way to use replication to facilite this?
Thanks for any input you can provide.
View 5 Replies
View Related
Oct 20, 2007
I'm in the current mode of migration from WIn2k and I 'm trying to setup user where they are only allowed access to one table.
If I grant thenm read /write access to the db they can obviously see all of the db due to the size of the db I do not wish to go down the root of denying every table as there is over 100. Is there an easier way??
View 4 Replies
View Related
Jun 13, 2002
I was trying to use the case statement in where clause so that the comparison (> , <, =) can be made depending upon the value of the local variable @fl.
PLease tell me how to do that. Or suggest me how can I make selective comparison (<,>,=) in where clause depending upon a local variable. Please note that I donot want to use if/else satement and write select statement everytime based on the local variable as in that case the code length will be huge. Please suggest me a coensized query.
SQL that I queried:
use pubs
go
declare @fl int
select fl = 3
select * from titles
where case fl when 1 then price > 90
when 2 then price < 90
when 3 then price = 90
end
The following error is given
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '>'.
View 2 Replies
View Related
Oct 25, 2000
Can I transfer data from one database to the other for only selected records
pls help
thanks
sunthar
View 1 Replies
View Related
Jun 1, 2004
HI,
Is it possible in SQL Server to restore Databases partially?
In the sense, I want to backup just 2 tables from a database & restore them, instead of having to take a backup of the entire database & restore the entire database.
One way to do would be to export data & then importing data for only those selective tables, but I dont want to do that, bcause that would probably take more time & can be error prone.
Thanks
Raman.
View 4 Replies
View Related
Sep 13, 2007
I need to 'copy' selective (filtered) rows from table 'A' to table 'B' on a regular basis, but only if they do not exist in table 'B'.
If rows get deleted from table 'A' they must remain table 'B'. If rows change in table 'A' (unlikely) they should be update in table 'B'
What feature of SQL should I be using?
- triggerering a stored procedure on Insert
- replication
- SSIS
View 1 Replies
View Related
Nov 13, 2006
Hi.
I am using Visual Studio 2005 professional and SQL Server 2005 Express Edition.
I am having a bit of an issue at the moment with my project. I have a large SQL server database (over 600,000 rows of data decoded from a text datafile and stored by my C# program) where I need to select a series of distinct rows.
I have a datacolumn of varchar(10) called UID which is assigned to every row in my database. It's not a unique code so it can happen several times. By selecting a specific UID code, I can narrow down my selection to a specific range of rows (which are incremental in order). This range will contain many names.
What I want to do is find a UID which will have one of two names. I have so far done this:
SELECT DISTINCT F1.UID FROM MyDataBase F1
INNER JOIN MyDataBase F2 ON
F1.LineIndex < F2.LineIndex AND
F1.UID = F2.UID AND
F1.Names IN (Bob', 'John', 'Peter', 'Sarah', 'Anne', 'Stewart', 'David', 'Alan', 'Linda') AND
F2.Names IN ('Bob', 'John', 'Peter', 'Sarah', 'Anne', 'Stewart', 'David', 'Alan', 'Linda')
This will return any UID which contains at least two of these names. The LineIndex ensures that the comparison doesn't happen on the same row of data (it's a column set to type int with IDENTITY(1,1) which is also my PRIMARY KEY). This is partially what I want, the selection of UID rows which will have at least two names from the list. Also, the same name will never appear twice in the same UID list.
Now, the problem I am facing is that I want a select a UID range with names in the order I have specified, so if I want a UID list where the names I want are Peter and Linda, I only want to return a set of UID rows where Peter appears earlier in the returned rows than Linda.
It is possible that other UIDs have the same names, but in reverse order, starting Linda, Alan, David etc...John, Bob. I would want to discard these entries.
Can anyone please give me any hints as to how I could get around this?
Thanks in advance,
Sean
View 5 Replies
View Related
Sep 5, 2000
Hi,
I am trying to do selective updates for rows where a column matches a column in another table. I want to do something like this, only 'this' does not work, and nothing else I could think of (I tried joins also) worked. What am I missing? I hope this explanation makes sense.
UPDATE queryresultsmodel SET queryresultsmodel.tableforcedoutdate = getdate()
Where Exists (Select tablename from queryresultsmodel q inner join orphanul o on q.tablename = o.name)
Thanks for any help,
Judith
View 1 Replies
View Related
Nov 27, 2007
We have a Table that contains a schedule. The schedule has certain pieces of information that are required to be updated by certain members of the
organization, specifically Shift, Start Date, and Line. Each record also has a column for the customer that it is for.
The schedule has a Column, [CSR] which lists the name of the person who is responsible for updating it. Format of that is domainnamejohndoe
What we would like to do is block domainnamejohndoe from being able to update or insert on any of the records that he is not responsible for.
So if he attempts to change the shift for example, it denies the change and possible pops up an error indicating that the change was blocked because he is not the responsible party.
Input in this is through a query which views the table data in MS Access. It cannot be moved to a Form without some serious redesign, so I cannot put the block in there. I assumed that this could be blocked with a Trigger that checks the [CSR] column, matches it to the logged in user through the suser_sname(suser_sid()) and then either allows or denies the update.
So is this a viable idea, or should I explore the move to an input form and make the change there in MS Access via VBA.
At first we started with just an audit trail, but I started to think that the audit trail would be needed only for records that should be allowed to be changed and that we should deny changes to those who are not allowed to make them.
Below is the audit trail trigger, I figure that the change should occur somewhere within that first IF statement, or make the first IF the second, and the first should be the permission check.
At this point I am stuck as to how to block the updates. I tried using Deny but that was not working. Filtering the results by only showing the logged in user their records is not an option as they need visibility to the entire schedule.
This is for SQL 2000 SP4/Windows 2000 SP4
CREATE TRIGGER audit_mschange
ON dbo.T_PP_Table_2
FOR update AS
IF (UPDATE([Start Dt]) OR Update(shift) or Update
(comments)
or Update(status) or Update (line))
BEGIN
INSERT INTO T_MS_RB_AUDIT
(ord_no,line_tank_old,line_tank_new,shift_old,shif t_new,comments_old,comments_new,status_old,status_ new,trx_dt,trx_username,
start_dt_old,start_dt_new)
SELECT ins.[PP Ord No],
del.line,ins.line,
del.shift,ins.shift,
del.comments,ins.comments,
del.status, ins.status,
getdate(),
suser_sname(suser_sid()),
del.[Start Dt],
ins.[Start Dt]
FROM inserted ins, deleted del
WHERE ins.[PP Ord No]=del.[PP Ord No] AND
ins.autonumber=del.autonumber AND
(ins.status <> del.status OR ins.[Start Dt] <>
del.[Start Dt] or ins.status <> del.status
or ins.comments <> del.comments or ins.line <>
del.line )
END
View 11 Replies
View Related
Jun 26, 2015
Say I have a report with 3 pages, each page has a different dataset and takes some processing time.
is it possible to give the user the option to just run the first page say if thats all they need?
Reduce the processing time of the report to only that that is necessary?
View 2 Replies
View Related
Jan 25, 2006
Hi,
I have a problem that I would like help with.
I have to write an SQL Server 2000 stored procedure that returns rows from a table (a SELECT with an ORDER BY). A front end system calls the stored procedure and displays the returned rows. If there is more than one screen's worth of data (ie more than 20 rows returned from the table) then there is a requirement that the stored procedure only returns the rows for the screen ie, for screen 1 I need to return rows 1-20, for screen2 I need to return rows 21-40, screen 3 = rows 41-60.
The screen number will be passed into the stored procedure so I can work out what rows to return, the only problem is how can I tell SQL Server to only select the required rows (ie, say rows 21 to 40 from the returned rows). I can't see any arguments that allow selective rows to be returned (apart from TOP).
Any ideas??
Regards,
Chris
View 3 Replies
View Related
Jul 23, 2005
Dear netters,We are looking to build a SQL Server database that will be hooked up toa Web server (WebLogic or a .NET).The database will house data for multiple customers, and therequirement is to have no customer see other customer data. Web serverwill be responsible for authenticating users (ids and passwords will bemaintained there/possibly stored in the database, but will not beactual database logins) -- and establishing pooled connections to thedatabase, using some sort of a proxy login.Ideally, we do not want to have the database itself visible to "theworld", it should reside behind a firewall, and be accessible throughthe Web server, which will do all authentication and routing.We want to have either tables or views defined in such a way that:1. Users can only see their own data and NEVER can see anybody else's.2. Users' access to the database is logged (this includes updates aswell as queries).3. We would like to provide some sort of a reporting mechanism -- whereusers can form their own queries. So, something like a Crystal or anAccess front end, that users are typically familiar with... The problemis that this requires users having direct access to the database (?),and opens up tables (unless, there is a way to create parameterizedviews that will always restrict access to a subset of records).Any advice on how to best implement this is greatly appreciated.Thank you in advance!
View 4 Replies
View Related
Jul 19, 2006
I am beginning design for a project to maintain an on-line archive of historical data for a production database, and then truncate old, rarely-used data from the production database. I am considering a method whereby we (A) replicate data changes from the production database to an archive database and (B) truncate old data from the production database according to predetermined business rules.
I am trying to determine whether SQL Server merge or transactional replication can be used to accomplish the replication portion of this requirement.
Given this scenario, there are two types of modifications that are made to the production database: (1) application inserts, deletes and updates which should be replicated, and (2) truncation deletes (as described in "B", above) which should not be replicated.
My question is: Will I be able to control the replication process such that application deletes are replicated, but "truncation" deletes are not? Could static row filters somehow be used to accomplish this?
View 6 Replies
View Related
Jun 9, 2008
Hi all.
I have been banging my head against a brick wall with this for a couple of days now. I am wrighting a custom front end for one of our SQLServer DB's and need to run a select agains the DateTime Field. The field Format is [DD/MM/YYYY HH:MM:SS]. I am tring to get the select to pull all the records on a set date only (keeping the time available but not selecting against it) so that the brass can see at what time on a given day an action was performed. Problem is, the way the programing software works, I can only build the SQL using string information. I have tried CONVERT and CAST both ways (converting the date to varchar and converting the string to date and also converting them both at the same time). Everything that I have tried returns an empty data set when used with the WHERE LIKE criteria (oh yeah, I'm also using LIKE % wildecards to retrive all the times against a specific date) but does populate when no filter is applied.
This is where my SQL is at just now:
SELECT Files.FileID, Useres.UserName, FileStatusLog.StatusCode, FileStatusLog.StatusDate
FROM (Files INNER JOIN FileStatusLog ON Files.FileID = FileStatusLog.FileID) INNER JOIN Users ON FileStatusLog.UserID = Users.UserID
WHERE (((CONVERT(varchar(10), FileStatusLog.StatusDate,103) LIKE '%{variable}%'));
This also returns an emtpy data set when the LIKE is replaced with ='{exact date entry in format: DD/MM/YYYY}', but does return the full table when the WHERE is removed and also when the LIKE is set to just a bare '%' with no other filter values.
Because of the fact that the results are returned when LIKE '%' is used I am not even sure if it is the CONVERT that is causing the problem. I have even tried assigning the escape character manualy to '+' incase the / values of the date in the LIKE variable where escaping other characters. Needless to say this did not make life any better.
As you may have noticed already SQL is not exactly my strong suit, but no one else here even knows what it is, never mind what it's used for, so I got droped with the task. Also - The DB was created by a third party so I have no write permissions to the DB.
Any help with this would be greatly appreciated as it's gotten personal now
Thaks in advance for any assistance
View 6 Replies
View Related
