+/- For Toggling Visible And Invisible Groupings
Mar 17, 2007When defaulted to Visible or Invisible, +/- displays correctly. But when using the Expression, + is always showing as the default even when a group is expanded. Is this expected? TIA.
ADVERTISEMENT
How To Make Textbox Invisible When Viewed On Screen But Visible When Printed?
May 22, 2007I have a couple reports that have parameters on them. Problem is when the report is printed, the parameters are not displayed.
To solve this, I have added the selected values to the header section of the report in a text box. I now want to find away to hide these values when the report is being viewed on screen but visible when printed. Any suggestions are welcome. Thanks
Update Boolean Toggling
Mar 3, 2006Hello,
In an update statement, I want to take a boolean and reverse it. Is there an easy way to do this? SOmething like:
update table set boolfield = not boolfield where ID = @ID
but that doesn't work; is there anything like this where it will reverse it easily?
Thanks.
Sql Query Groupings
May 23, 2007I have a requirement in the application that. I have columns in the following format:-
Col1 Col2 Col3
1 A1 A2
1 A1 B2
2 B1 C2
1 A1 D3
These type of contents the table have contents . What I require id if for any row if ‘Col1’ and ‘Col2’ values are same then the n the query should show only one record with ‘Col3’ values concatenated as comma separated array.
For the above e.g. the result should be.
Col1 Col2 Col3
1 A1 A2, B2, D3
2 B1 C2
Week Groupings
Jul 26, 2007Hi all,
I have this code below and it appears to work, but I wondered if there was a better way.
Basically I have a table with some sales. The amount sold is stored in a varchar (for a very stupid reason) called ticketNo, the date of the sale is stored in txDate (thankfully this is a date).
We need to report out the total amount sold for a period in weekly groupings with the week starting on Monday. If a week starts within the period specified but does not finish we extend the period to include the whole week.
As I said the code below appears to work (no I have not tested extensively), but it looks like it will be pretty resouce intensive.
Any suggestions?
declare @minTxDate DateTime
declare @maxTxDate DateTime
set @minTxDate = '02-Jul-2007'
set @maxTxDate = '31-Aug-2007'
declare @StartOfYear DateTime
declare @LocalMaxTxDate DateTime
declare @WeekDay int
declare @StartWeek int
declare @EndWeek int
set @StartOfYear = '01-Jan-' + Convert(varchar(4),Year(getDate()))
set @WeekDay = DatePart(weekday,@StartOfYear)
set @StartWeek = DatePart(week, @minTxDate)
set @EndWeek = DatePart(week, @maxTxDate)
set @LocalMaxTxDate = DateAdd(d,10,@maxTxDate)
set DATEFIRST 1
select
DateAdd(d,((DatePart(week, tmpSale.txDate)-1)*7)+@WeekDay-1, @StartOfYear) 'FirstDayOfWeek',
DatePart(week, tmpSale.txDate) 'WeekOfSale',
COALESCE(sum (Convert(decimal, ticketNo)),0) 'TonnesSold'
from
(select
Sale.txDate,
Sale.ticketNo
from Sale
where Sale.txDate between @minTxDate and @LocalMaxTxDate)
tmpSale
where DatePart(week, tmpSale.txDate) between @StartWeek and @EndWeek
group by DatePart(week, tmpSale.txDate),
DateAdd(d,((DatePart(week, tmpSale.txDate)-1)*7)+@WeekDay-1, @StartOfYear)
Ranking With Groupings
Sep 4, 2015how I could achieve a ranking number, where I have some grouping involved.I want to have the groups in sets of 4, but if I get to a new person the ranking increases, even if I haven't filled the group. For example:
Name Year Sales Ranking
-------------------- ----------- ----------- --------------------
John Smith 2009 1296 1
John Smith 2010 1296 1
John Smith 2011 1296 1
John Smith 2012 1296 1
John Smith 2013 1296 2
[code]....
So, when I get to John's 5 year, the Rank increased. I then only had two more rows before I get to Nina, but the Rank number goes up, as the details are for a different person from the prior user.
Select *, DENSE_RANK() over (Order by Name) + ((RANK() over (Partition by Name Order by Year) - 1) /4) as Ranking
from #Test
Order by Name, Year
Drop Table #TestThis works for John, but Nina then has a Ranking of 2 for her only year, where as she should be 3. Steve also then has a value one too low for all of his ranks.
The Collapse/toggling Effect In SSRS
May 27, 2008Hi Guys,
I have created a report where I have Z-Index all set correctly, but I cannot see the desired effect of toggling as can be seen in the AdventureWorks sample of SalesOrder report that gets shipped with SSRS. What am I missing?
Matrix Column Visibility Toggling
Dec 7, 2007Hi, 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.
Groupings In Matrix Layout?
Mar 28, 2006Hello,
I am trying to come up with a Matrix report using RS 2005 that looks like this one below. I have row data: question, columns data: Ethnicity and Gender, detail data: Gender average response and Ethnicity average reponse. I utlilized Report Wizard to create a report in a Matrix format , however I wasn't able to have Gender and Ethnicity column on the same line. The report wizard groups columns so Gender is a subset of Ethnicity or Ethnicity is a subset of Gender. How can is solve this issuee so that Gender and Ethnicity data is presented in a Matrix format and Gender and Ethnicity are not part of each other.
Thank you!
F
M
Asian
Multi-cultural
Non-resident
Unknown
White
Black
Hispanic
Native American
a. Worked on a paper or project that required integrating ideas or information from various sources
3.95
3.99
3.54
4.50
3.28
4.20
4.18
4.04
3.92
3.90
b. Used library resources
4.26
4.09
4.12
4.33
4.10
4.33
4.26
4.22
4.10
4.10
c. Prepared multiple drafts of a paper or assignment before turning it in
3.97
3.76
3.80
4.50
3.58
3.86
3.95
4.09
4.00
4.00
Toggling Visibility Between Two Text Boxes In Ssrs
Oct 9, 2012how visibility can be toggled between two textboxes in ssrs? For example if textbox1 is visible and textbox2 is hidden initially and textbox2 should be visible when clicked on textbox1 and textbox1 should go invisible when textbox2 is shown... and then again when clicked on textbox2, textbox1 should be visible and textbox2 should go invisible.In a short, it is like switching visibility between 2 textboxes... A click on first textbox should display the second textbox and another click on second text box should display first text box.I cannot provide parameter as it is not there in the requirement. how to toggle the text within a single text box to fulfill above requirement...
View 1 Replies View RelatedMatrix Visibility And Toggling With Multi Row Fields
May 31, 2007I have a matrix with two row fields. The second one doesnt show unless i press the toggle button on the first row field. How can make the first row field automatically expanded to show the second row field?
Thanks.
Fuzzy Lookups And Groupings Algorithm
Sep 28, 2006Hello,
I'm trying to clean my data using fuzzy lookup algorithm though SSIS, but i get null values everywhere. This is what i did:
I applied the fuzzy lookup in a table (tblValues). As source table i have the tblValues, and as reference table in Fuzzy Lookup i have the tblValues as well, resulting null values in all fields/columns.
Do i have to create my own reference table? If yes, how do i do that and what values will i have in this table?I didn't understand how the reference table must be in order the algorithm to work. Any suggestions?
Thank you in advance!
Toggling Visibility Of Rows In A Table Based On The Value Of A Subreport
Mar 21, 2008
i have a report that contains a sub-report that is embeded in one of the textboxes of the table. what i am trying to do
is have the main table toggle visilibity based on the value of the sub-report...in my case the toggle is very simple - if the sub-report returns any value at all, then i need the row in the parent table to be 'visible'
any help would be greatly appreciated.
thanks!
-dk
Problems With Groupings On Reporting Services 2005
Sep 18, 2007Dear people,
I have a problem with a report where users want to see transacctions grouped by Account number and type(e.g: incomes and payments).
And so I use a table object where I put 2 groups:
1) Group by Account Number: Field!Account.Value
2) Group by Transaction Type: Field!Type.Value
I put in the detaill row group by: Field!Type.Value and Field!Account.Value.
When I execute the report only show the first row per transaction type, but the dataset has some rows per transaction type.
Please help me with this problem, thanks...
Turn Report Auto-scroll Feature Off When Toggling Drill-down
Aug 9, 2007Is there a way to turn IE7's auto scroll off so that every time you drill down by clicking on one of the "+" toggle buttons the report doesn't recenter itself. We find this window jumping/scrolling highly annoying to watch.
This problem does NOT occur in FFox, just Microsoft's browsers.
We are displaying this report running on another server running Visual Studio 2005's ReportViewer control in an aspx (ASP.NET) page.
We are using SSRS 2005, std edition.
FFox 2.0.0.6, IE7.
Will be glad to post more info if requested.
Dynamically Creating Excel Worksheets Based On Groupings From Query In SSIS
Apr 24, 2008Is there anyway to dynamically create Excel Worksheets based on a group identity results from a query?
Thanks!
SkySeek.
Combine Data And Split Into Separate Txt Files For Each Header/detail Row Groupings
Mar 16, 2006I€™ve created with the help of some great people an SSIS 2005 package which does the follow so far:
1) Takes an incoming txt file. Example txt file: http://www.webfound.net/split.txt
The txt file going from top to bottom is sort of grouped like this
Header Row (designated by €˜HD€™)
Corresponding Detail Rows for the Header Row
€¦..
Next Header Row
Corresponding Detail Rows
€¦and so on
http://www.webfound.net/rows.jpg
2) Header Rows are split into one table, Maintenance Detail Rows into another, and Payment Detail Rows into a third table. A uniqueID has been created for each header and it€™s related detail rows to form a PK/FK relationship as there was non prior to the import, only the relation was in order of header / related rows below it when we first started. The reason I split this out is so I can massage it later with stored proc filters, whatever€¦
Now I€™m trying to somehow bring back the data in those table together like it was initially using a query so that I can cut out each of the Header / Detail Row sections into their own txt file. So, if you look at the original txt file, each new header and it€™s related detail rows (example of a cut piece would be http://www.webfound.net/rows.jpg) need to be cut out and put into their own separate txt file.
This is where I€™m stuck. How to create a query to combine it all back into an OLE DB Souce component, then somehow read that souce and split out the sections into their own individual txt files.
The filenames of the txt files will vary and be based on one of the column values already in the header table.
Here is a print screen of my package so far:
http://www.webfound.net/tasks.jpg
http://www.webfound.net/Import_MaintenanceFile_Task_components.jpg
http://www.webfound.net/DataFlow_Task_components.jpg
Let me know if you need more info. Examples of the actual data in the tables are here:
http://www.webfound.net/mnt_headerRows.txt
http://www.webfound.net/mnt_MaintenanceRows.txt
http://www.webfound.net/mnt_PaymentRows.txt
Here's a print screen of the table schema:
http://www.webfound.net/schema.jpg
Invisible Data
Aug 23, 2005I am facing a problem of invisible data. I have developed a ASP based application in my local laguage and data is entered from 8 clients. Now I have found that in a particular table in the SQL Server very few data is not visible if I see from the SQL query analyzer. But if I go the application and try to enter the data again it gives a message that the data already exist and next time if i see it from query analyzer the data is displayed.
Please help.
Bidyut Rn. Gohain
What Is That Invisible Character?
Feb 27, 2008Hey, everybody,
I need your help on figuring out what is this invisible character in one of my data column.
The file is from IBM DB2 as text. I am comparing the data with the one in SQL server. There is one white space at the end of the column I couldn't get rid off.
The text strings from both side look idential except the DB2 one has a "space" at the end.
The len() show it's 13, while the sql one is 12. However, the rtrim()/ltrim() did not get rid of it.
This column is at the end of the row. So it could be CR or LF.
To use padindex or stuff functions, I need to know what is this stuff.
Talking about working hard for "NOTHING".
Thanks!
Invisible Components
Aug 10, 2007I am not sure why when the packages are run, the components dissapear. The only thing I see is the result in the output.
So there is no visual on the tabs.
Thanks
DBO Invisible In User List
Mar 20, 2002Recently found a situation which I can not figure out the cause. Wish somebody can help me here.
The scenario is as follows:
Server: SQL 7.0 with SP2
login name: DBA, standard SQL login, member of sysadmins
DB Name: TEST
DBA is the owner of database TEST and a member of db_owners in the database. Using EM, if you look at Users in TEST database, the only entry you can find is DBA where both Names (user name Login Name) are the same: DBA. There's no entry for user dbo. (from BOL, dbo can not be deleted)
Run sp_helpuser gives out two rows.
user login name
dbo NULL
DBA DBA
I don't know what happened to this machine before. Any clue about the possible cause is highly appreciated.
Hong
My Reports Folder Is Invisible
May 9, 2007I am trying to locate the "My Reports" folder on the SSRS site, but I dont think I am able to see it. I am an admin on the box with Browser, Content Manager, My Reports roles.
I also tried creating the folder manually and it says the folder exists but I cant see it.
I have also checked on the "Site Settings" and checked the box "Enable My Reports". What else am I missing here?
Thanks,
sj
Any Way To Run A Invisible Trace On Security Audit?
Apr 21, 2004Is there anyway I could run an Audit trace on SQl Server which records SQL Server System Admin Login/Logout , failed login and machine names I don't want the trace window to show on screen however would like a file generated for later viewing. Also due to Firewall issues we have, We don't have SQL tools enabled to connect to that server.
View 2 Replies View RelatedHow To Create Invisible Table In 2005?
May 6, 2008Hi,
Can I create invisible table in SQL Server 2005?
Access(all versions) has this option if you add Usys_TableName.
The table will be invisible, but you can get data from it
Mark
Invisible Rows After Failed Syncronization
Nov 9, 2006Publisher: SQL Server 2000 SP4
Subscriber: SQL Server 2005 Mobile Edition
Sometimes, after a failed merge replication(due to communication error) some rows on the subscriber became invisible for the publisher.
I've reproduced a case:
[START Short Version]
All rows inserted between a comm failed sync and a fine sync became invisible.
This seems to be caused because the subscriber keeps generating rows with the same generation number and the publisher doesn't look for this generation anymore.
[END Short Version]
[START Detailed case]
STEP 0: INITIAL STATE
Publisher
Orders Table
EMPTY
Subscriber
NO DATABASE
STEP 1: After FIRST SYNC and INSERTING 2 Orders
Publisher
Orders Table
EMPTY
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC000001 4 4 81000002 4 4 81
STEP 2: After SECOND SYNC
Publisher
Orders Table
OrderId
000001
000002
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80
STEP 3: INSERT ANOTHER ORDER
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80
000003 6 6 81
STEP 4: After THIRD SYNC (with comm error)
Error: [NativeError:28037][HRESULT:-2147012889]->[A request to send data to the computer running IIS has failed. For more information, see HRESULT.]
When: Error happens provoked after sending from Subscriber to Publisher but before ending syn process.
Publisher
Orders Table
OrderId
000001
000002
000003
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80
000003 6 6 81
Note: Publisher has the row but subscriber keeps the state in 81.
STEP 5: INSERT ANOTHER ORDER (BEFORE A GOOD SYNC)
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80
000003 6 6 81
000004 6 6 81
Note: Orders 000003 and 000004 have the same system info
STEP 6: After FOURTH SYNC
Publisher
Orders Table
OrderId
000001
000002
000003
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80
000003 6 6 81
000004 6 6 81
Note: Even with a fine merge process Publisher didn't get the 000004 row and the Subscriber didn't update the __sysMC
STEP 7: INSERT ANOTHER ORDER
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80
000003 6 6 81
000004 6 6 81
000005 8 8 81
STEP 8: After FIFTH SYNC
Publisher
Orders Table
OrderId
000001
000002
000003
000005
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80
000003 6 6 81
000004 6 6 81
000002 8 8 80
Note: Data from generation 8 merge correctly, but data from generation 6 keeps invisible to publisher.
STEP 9: DUMMY UPDATE ON GEN 6 ROWS
Action: I made a non relevant update to force the Subscriber update the generation.
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80
000003 6 10 81
000004 6 10 81
000005 8 8 80
Note: sysCG get its value updated correctly.
STEP 10: After SIXTH SYNC
Publisher
Orders Table
OrderId
000001
000002
000003
000004
000005
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80
000003 6 6 80
000004 6 6 80
000002 8 8 80
Note: Data merges correctly.
[END Detailed case]
I could code a system that checks all the publication tables and updates all the last generation rows in case of communication error but i would really like to avoid doing it.
I don't know if I'm missing something or if this is a bug or a known issue.
This is a big problem for me because communication errors on cellular phone based connection are quite common and users keep working even if there's an error.
Any comment will be appreciated.
Sorting Makes Table Invisible
Sep 13, 2006I have a table which visibility can be toggled by a text box. By default it is invisible. After it is made visible, clicking a sortable column header makes the table invisible. Does this mean sorting makes the table go back to its default visibility?
How Do You Make A Report Parameter Invisible???
Jun 4, 2007Hi, I have 3 report parameters.
If a user selects 'value1' from the first parameter list then parameter list 2 & 3 should be open for the user to make their selection. BUT if the user selects 'value2' from parameter 1 then only parameter list 2 should be open and parameter list 3 invisible. Is this possible??
Invisible Controls Durig Runtime
Jul 25, 2007Hi,
Do you know why when I run my ssis packages in the dev machine, the diagrams are not visible during run time.
I can design the package but not sure why when I start the package, the diagrams in the control flow can not be viewed.
Please note that if I do this on the server, I can see the diagrams during run time.
Thanks
How To Make Table Row Invisible Based On Certain Condition
Apr 16, 2008
HI
I have the following scenario in my report.
-The data is displayed in a table
-The table groups by one field
-Each table row calls a subreport
-There is about 6 paramaters in the report
-The last paramater of the list of paramters is a multivalue paramater and based on what is selected in the list the corresponding subreport must be shown.
-So i use a custom vbscript funtion to determine if a specific value was selected or not.
This functionality is working fine.
My problem is if the user does not select all the values in the multi select then i want to make the row invisble
and remove the whitespace so that there is not a gap between the other subreports which is shown.
I can make the subreport invisible inside the row but there is still the white space which does not display very nicly.
How can i make the row invisible if the vbscript function that is called returns a false value?
Here is the funtion I call -> Code.InArray("ValueToSearchFor", Parameters!MultiValueDropDown.Value)
The Function returns a true or false.
Thanks.
How To Handle Invisible Characters On An Account Number
Dec 6, 2007
I am joining on table A AND Table B ON the accountnumber field. The accountnumber on the tblB has some invisble characters at the end, ie "12344 ". The Table A does not have these invisble chars and is "12344".
Due to the extra spaces at the end, the join fails and does not find a match for that acctno. I have used ltrim(rtrim) hoping that will elimante the extra spaces and find a match.
Heres my qry:
select * from Tbla a
inner join tblb B
on ltrim(rtrim(ACCTNO))=ltrim(rtrim(ACCT))
How do I do this, so that it finds a match and returns the acctno.
After Sorting Textbox Is Getting Collapsed Making Table Invisible
Apr 15, 2008
Hi.
I found unpleasant issue within RS:
1. Imagine RS report with Table(with interactive sorting) whose visibility is toggled by Textbox:
2. After sorting the Table the Textbox is getting collapsed making the Table invisible. But the Table should remain visible.
Is this MS bug? Are there workarounds?
If my question is not understandable I can send simple *.rdl report with sample.
Thank you in advance for help.
Invisible String? I Turn To You, The Reliable ASP.NET Forum Experts, For A Last Ditch Try. Please Help Me Out.
Mar 14, 2008I have been tring to get this one line figured out for a few days now.
'Job2 Info
Dim selectSQL2 As StringselectSQL2 = "SELECT * FROM '" & CompanyKey & "'" '<<-------HERE
Dim cmd2 As New SqlCommand(selectSQL2, con)
'Job2 Select
Try
con.Open()
reader = cmd2.ExecuteReader()
(I have the full code below.) So here is the problem, this code is not populating the datagrid. There is data in the table I am selecting from. When I log in, my CompanyKey value displays in the label as "21". When I take out the "CompanyKey" variable, and just type in 21, the grid is populated. It is confusing the heck out of me. I have tried it this way:
selectSQL2 = "SELECT * FROM [" & CompanyKey & "]"
-and this way:
selectSQL2 = "SELECT * FROM & CompanyKey
and all the other ways I could think of. I researched it and can just not get it to work any way I do it. Any suggestions? Full code below:
____________________________________
Imports System.DataImports System.Data.CommonImports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.PageProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true")
'Job1(Info)Dim currentUserID
currentUserID = Context.User.Identity.Name.ToString()
Label1.Text = currentUserID
Dim selectSQL1 As StringselectSQL1 = "SELECT companyKey FROM Company WHERE UserID = ('" + currentUserID + "')"
Dim cmd1 As New SqlCommand(selectSQL1, con) Dim reader As SqlDataReader
Dim CompanyKey
'Job1 Select
Try
con.Open()
reader = cmd1.ExecuteReader()Do While reader.Read()
CompanyKey = reader("CompanyKey").ToString()
Loop
reader.Close()Catch err As Exception
ReaderError.Text = "Error selecting record."
ReaderError.Text &= err.Message
Finally
con.Close()
End Try
'Job2 Info
Dim selectSQL2 As StringselectSQL2 = "SELECT * FROM [" & CompanyKey & "]"
Dim cmd2 As New SqlCommand(selectSQL2, con)
'Job2 Select
Try
con.Open()
reader = cmd2.ExecuteReader()
GridView1.DataSource = reader
GridView1.DataBind()
reader.Close()Catch err As Exception
ReaderError.Text = "Error selecting record."
ReaderError.Text &= err.Message
Finally
ReaderResults.Text = CompanyKey
con.Close()End TryEnd Sub
End Class
SQL Executive NOT Visible...
Mar 23, 1999Hi,
When I register an SQL Server from elsewhere on the network, some of them are not displaying SQL Executive in the Enterprise manager.
Why is this?