Hi, please I would like to use something like this>
=IIF(table1_group2 is Collapsed,True,False)
I want to generated some event based on actual state of e.g. report group. if user expand group make this event otherwise (group is collapsed) make another event.
Thanks for your advice.
Hi,Basically I have a Matrix report with two column drilldown parameters, named "result" and "detail of the result". Then When I click on the detail cell I am passing two parameters to filter the linked report.The first one is quite direct I pass it and it's allright.The second one has me crazy... If I pass it while the second column is collapsed, the first value is passed.... When I am not choosing anyone and the sum of all the columns is shown, I need (and surely all of us) That when a drilldown column is collapsed, the respective value of the column that I pass to the parameter is passed as null or better, That I am able to detect when a column is collapsed or not, for just not passing the parameter (faster).Well any help or guidance would help as I am lost on this.Thanks for the help,Jose
The only workaround (partially) I have found is, on the TextBox properties, Navigation tab, Parameters details is to assign the following expression to the Omit column:
=IIf(IsNothing(Fields!Det_Result.Value) Or (Trim(Fields!Det_Resul.Value)="") , True, False)
But that only works when the parameter is text and is a null or empty value... not nice...
I have created a matrix where I have booleans on the rows and columns. They are initially in the collapsed state. For reporting purposes, I would like the initial state to to be expanded or at list a quick fix to quickly convert the collapsed booleans to expanded booleans.
I have navigated to the Layout tab and right-clicked the fields that are tied to the boolean and then clicked on the properties. This bring sup the Textbox Properties. From here, I click on the Visibility tab. At the bottom of the textbox is a section "Initial appearance of the toggle image for this report item:". The default choice is marked as collapsed (+). One would logically think that you you would have to do then is select the expanded (-). choice. Well, when I do this, all it simply does is chance the icon from a '+' to a '-' and the fields are still shown as collapsed.
Any ideas on what I need to do?
Hello everyone here!
I am developing a report which uses the collapse and expand functionality. But the functionality indicates that all the records when rendered in Excel must all be expanded, and collapse button must not be visible once exported.
But I have noticed that when the report is processed, the first row that is grouped was collapsed, but the other records below we're all expanded.
I just can't seem to detect the error for this, because the rendering we're all fine with the HTML version and PDF version, and another thing it is always on the first row that the records were collapsed.
Any idea regarding this scenario? Any help will be highly appreciated. Is this an issue of the Service Pack used for the Reporting Services? Or it is really an Excel issue?
regards,
Mark Guerra
narraSoft Philippines
Software Engineer
I found one database in availability group state change automatically. I found ERR started at
0000233c.00002848::2015/09/11-03:51:09.840 ERRÂ Â [RES] SQL Server Availability Group: [hadrag] ODBC Error: [HYT00] [Microsoft][SQL Server Native Client 11.0]Query timeout expired (0)
And I use google to search and found the link below. It indicated a network issue. Is it the source of the problem. By the way, My failure Condition Level is 1 and I have increased Health Check Timeout from 30,000ms to 150,000ms just now. [URL] ...
--- SQLDIAG ---
cluster log in UTC+8:
0000233c.00002848::2015/09/11-03:51:09.840 INFOÂ [RES] SQL Server Availability Group: [hadrag] SQLMoreResults() returns -1 with following information
0000233c.00002848::2015/09/11-03:51:09.840 ERRÂ Â [RES] SQL Server Availability Group: [hadrag] ODBC Error: [HYT00] [Microsoft][SQL Server Native Client 11.0]Query timeout expired (0)
[Code] ...
i'm hoping someone can help me out with this problem.
i have a database called Tracking.mdf and its associated logfile Tracking_log.ldf
i need to build another database based on the above with more tables and keep the data separate hence the requirement to make a copy of the tracking data file i've tried changing its name but it will not attach. i can change its database name to whatever i want but somehow can't find out how i can say change its actual filename to Tracker.mdf and Tracker_log.ldf
would someone be able to help me out. thanks.
In the old DTS, we can use the ActiveX Script to change any task's property programmatically.
Can we still do it in SSIS? Using the Script task? It seems changing the value of variables then use a expression can do some of the work, but what if a task has no expression defined?
Say, I want to change the Fuzzy look up reference table name.
Can we do it?
I am getting the following error when trying to run a query against an Oracle DB.
"TITLE: Microsoft Report Designer
------------------------------
An error occurred while reading data from the query result set.
OCI-22053: overflow error
------------------------------
ADDITIONAL INFORMATION:
OCI-22053: overflow error
(System.Data.OracleClient)"
The fix is...
set the ReturnProviderSpecificTypes property of the OracleDataAdapter to true and have it read OracleNumber's instead of System.Decimal, which can't handle the value
I need to know How I can do this.
Using BI tools in sql server 2005
I have a view in SQLServer 2005. It took 30 sec. to finish. Then I deleted 4500 records from one table that is used in view. It took 90 sec. to finish now. I did a comparison on Actual Execution Plan between before I deleted data and after I deleted data, they are almost same, only different is Actual Number Rows become less after deleted data. So, I wonder why data become less but time become more. When I look closely on the Actual Execution Plan, the ridiculous thing is, there are only Estimated Operation Cost on each step, no Actual Operation Cost. I guess there are something wrong with optimizer because reuse same Execution Plan, but how can I tell which step wrong without Actual Operation Cost.
Thanks!
Henry
Input
'<Email>
 <Type>Personal</Type>
 <Code>1</Code>
 <Description>solange@nobre.com.br</Description>
 <LocalId>000000000000000000000000868000000001</LocalId> Â
</Email>'
OUTPUT
'<Email> Â
 <Code>1</Code>
 <Description>solange@nobre.com.br</Description>
 <LocalId>000000000000000000000000868000000001</LocalId> Â
 <Type>Personal</Type>
</Email>'
how do this using sql ?Â
Hello
I have a gridview that I use on a products page, my problems is certain products have different attributes that I would like to display.
Therefore what I would like to do is change the SelectCommand property to my SQLDatasource depending on the querystring that is passed.
For instance in my page load event I would have a CASE statement with numerous SQLString Variables.
Here is the current coding for my datasource
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT PS.ProductSizeMM AS [Coupling Size], PS.ProductWallThickness AS [To Suit], PS.Cost AS [Price], PS.Sold_By AS [Sold by] FROM tblProduct AS P INNER JOIN tblProductSize AS PS ON P.ProductCode = PS.ProductCode WHERE (P.ProductDescription = @ProductDescription) ORDER BY PS.Sorter">
<SelectParameters>
<asp:QueryStringParameter Name="ProductDescription" QueryStringField="ProductDescription" />
</SelectParameters>
</asp:SqlDataSource>I have tried declaring a string variable in my page load event (SQLString) then setting the
SelectCommand="SQLString" but this causes a syntax error
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'SQLString'.
Any help would be greatly appreicated!!
Hi,
We using SQL SERVER2000..and in control panel we enabled date/time settings as per IST.
I updated one record at 9.30...It shows time 4.It's showing GMT time,how to change the time format.
I used UTC function to get timing.
Plz help me ASAP.
Hi all,
I have a question regarding a Language property on Report, Table and Cell levels.
My reports must show monetary values in different formats depending on a currency symbol where the Client resides.
For instance, money fields for USA, Canada, UK are shown as 123,456,789.00 and then "$" or "£" symbol;
but European countries should have 123.456.789,00 format and a Euro symbol.
I have found that XXX.XXX.XXX,00 format corresponds to the Language property = "Italian".
If I set the Language property = "Italian" on Report or Cell level at design time, the report shows the expected 123.456.789,00 format, no problem.
(By the way, for some reason, on the Table level this property set does not work at all)
Unfortunately, I was not able to change the Language property to "Italian" on Report or Cell level on-fly using the following expression:
=IIF((Parameters!Symbol.Value="$" OR Parameters!Symbol.Value="£"),"English (United States)","Italian")
For debugging, I even tried:
=IIF((Parameters!Symbol.Value="$" OR Parameters!Symbol.Value="£"),"Italian","Italian")
But all numbers on the report are still shown in the 123,456,789.00 format regardless the Client's currency symbol.
I don't want to have 2 sets of my reports only because if the monetary format difference.
And also I don't want to CAST the monetary value into a string and mask it myself with dots and commas.
I appreciate at advance any help or comment regarding the issue very much.
This is a critical bug and it must be resolved ASAP.
Hi folks,
I have a Projects , each project have many tasks now i want to display tasks replated to each project:
for example:
Project1-------------------->task1
task2
task3
task4
Project2----------------------->task4
task5
task6
.............................................projectN.....................
how to write query for this
i have 2 tables:
Project .......>columns are projectid
Task------------->columns are projectid, taskid
|
Why does this code (below) cause a change in the transaction state?
DECLARE @date DATE,
@string VARCHAR(5)= 'test'
BEGIN TRAN
PRINT XACT_STATE()
[Code] ....
I have a SSIS package loading Excel file. The Excel Source automatically give the length of 255 for all text columns. However, some of the column may exceed 255 length.
I cannot change the length of Error output columns. "Error at Data Flow Task [Excel Source [508]]: The data type for "output "Excel Source Error Output" (517)" cannot be modified in the error "output column "F45" (2345)".
Error at Data Flow Task [Excel Source [508]]: Failed to set property "DataType" on "output column "F45" (2345)".
"
How to change it or truncate it to 255? I am using 64bit VS.
TIA
Can a person change the page title from "Report Viewer/Manager" to the actual report name?
View 3 Replies View RelatedI see various references to the options for a package's protection level (including http://msdn2.microsoft.com/en-us/library/ms141747.aspx) but I can't seem to find anything telling me how to actually look at and/or change the protection level of a package - thus my question is "How do I change the protection level of an SSIS package?"
Thanks!
- Lance
I am launching a package the following way:
DTEXEC.EXE /SQL "ProcessReportingDatabase" /SERVER RTG23SQLDB01 /REPORTING V /SET "Package.Variables[User::RunID].Value";35 /SET "Package.Connections[RSAnalytics].Properties[InitialCatalog]";"Fnd Prj 1 Dec29" /SET "Package.Connections[RSAnalytics].Properties[ServerName]";"RTG23SQLDB01UAT1PROD"
The problem does not happen with the [User::RunID] variable or the [Initial Catalog] property of my [RSAnalytics] connection object. But I am not successful in setting the [ServerName] property.
What happens when executed is that the package retains the ServerName property of the deployed package (Value="RTG23SQLDB01UAT1QA"), instead of what I pass in via DTEXEC???
Is ServerName read-only or something? I've tried configuring the package connection to RTG23SQLDB01UAT1PROD and creating a package configuration that I specify in place of the /SET - but to no avail as well!
This package has to work against 10 instances. I really don't want to have to create a separate package for each instance, and then a hack to figure out which one to call.
TIA - Dave
I am putting together an invoice for my company. I have a text box describing each section of the invoice, followed by a table to list out the charges. I am using multiple tables based on what type of charge the client is receiving.Â
I would like to hide each section if there are no items purchased of that type. I can do this with the table using the expression "=CountRows() < 1", but I do not know how to refer to that table (call it Tablix1 for the sake of discussion) for the text box. I've tried using a ReportItems function as my basis, without success.Â
Hi everyone,
I have a report that a user can enter parameters in. If the user opens the report the parameter box is at the top and expanded. The report is also linked to by another report, so when a user clicks on something in this other report it becomes the parameter for the one I'm having the problem with. The problem is the user can select the number of days that are displayed, so when I link from the other report I just pass 10 by default, but now in the report the parameter box is collapsed and I'm worried that users won't find it. Is there a way to force it to be expanded? Sorry this is probably confusing to read.
Thank you,
Keith
RS + SP2
View 5 Replies View Related
Hi all,
I have a Report and I have used Table in the report to define 5 levels of Sub Groupings: Group1, Group2, Group3, Group4, grpResourceBehavior.
The Sample data which comes is:
Group1 Group2 Group3 Group4 ResourceBehavior BgtHrs BgtAmt
A1 B1 C1 D1 NML 12 12
A1 B1 C1 D1 ODC 0 12
Now, to calculate a field "Cost", I have to use following condition. If the ResourceBehavior is NML, then Cost = BgtAmt/BgtHrs
But, if the ResourceBehavior is ODC, then COst = BgtAmt/(SUm of BgtHrs for the resourcebehavior NML)
I am not able to calculate the Sum of the BgtHrs for different ResourceBehavior. Also, I need this field calculation for each and every group as well, and this makes it all the more complicated.
Any help is appreciated.
Thanks in advance.
Swati
Hello,
In the report I€™m making I set the visibility property of the textboxes comprising a row to be toggled on by an item in the group header. I set the initial visibility to hidden, and initial appearance to collapsed. Now this worked fine, I get group headers with the sum of the records, that I can expand by clicking +.
The problem is that even though the collapsed rows are empty and the lines separating them from each other are invisible, they still take up space so the other group header (when collapsed) starts not right away, but after a space which is equal to the sum of the height of all the hidden rows.
All I managed to do is set the row property to €œcan shrink to accommodate contents€? and set the row height to 0.07937cm this helps but if the group contains 50-100 records the space between headers is quite big.
I do not know why, but I can€™t set the row height to 0.0cm. This problem also bothers me when I hide duplicates in group and the empty row is still visible in the report. It takes up space and the people using the report are complaining for the unnecessary lines that hinder reading.
Could anyone help me with this one?
Regards
Darius
I have to change the name of the history table dynamically to what is passed in the start date.
If @start_date= '1/1/2014' then it should be history_jan14
If @start_date ='02/01/2014' then it should be history_feb14 and so on.
Is there a way you can do it ?I am using SQL Server 2008.
Code :
DECLARE @start_date datetime
DECLARE @end_date datetime
SET @start_date = '01/01/2014'
SET @end_date = '02/01/2014'
[code]...
tblScore contains score for each problem
id problemID score
------------------------
1 1 10
2 2 30
tblSubmission contains problem submissions for each user
id user problemID accepted
-----------------------------------------------
1 UserA 1 0
2 UserA 1 0
3 UserA 1 1
4 UserA 2 1
5 UserB 1 0
6 UserB 1 1
7 UserB 2 1
For UserA :
- For problemID 1
-> submitted three times
-> rejected for first two submission and accepted on third submission.
- For problemID 2
-> submitted one time
-> accepted on first submission
For UserB :
- For problemID 1
-> submitted two times
-> rejected for first submission and accepted on second submission.
- For problemID 2
-> submitted one time
-> accepted on first submission
Now I would like to process the table and want to get the following result :
user Score
--------------------------
UserA 36 (6 + 30)
UserB 38 (8 + 30)
Explanation :
- For each rejected submission, a -2 point penalty.
- UserA have submitted probelmID 1
- > score of problemID 1 is 10.
- > first two times rejectd
- > third time accepted.
-> score = 10 - 4 = 6
- UserA have submitted problemID 2
- > score of problemID 2 is 30
- > first time accepted. No penalty will be counted
- > score = 30
so final score for UserA = 30 + 6 = 36
Similar for UserB.
Hello!
suppose i have two tables, table1 columns(empcode (pk), empDept) and table2 columns(empcode (FK),Date,Attendance) i wanted to write a query to get output like
DEPT ABSENT
-----------------------------
Accounts 10
EDP Section 0 **
Admin 2
Stationary 0**
if no employee is absent in the department it has to display Zero
Product IdIsPrimaryQuantity
P0011
P001.102
P001.204
P001.305
P0021
P002.106
P002.207
P002.309
P002.4010
P002.5011
Need the query for result each group shows multiplied value of group quantity and last row of the group is shown with NULL
Product IdSubProductQuantity
P001 40
P001 P001.3NULL
P002 41580
P002 P002.5NULL
This is same as [URL] ....
Hello,
I have a matrix which lets you drill-down by company divisions and then branches within those divisions, and the data columns show how many employees are required for each divisionranch, and how many they actually have. For example, Division 1 might have 9 / 9 for Actual / Required, but when you drill it down to Branches A and B, they may have 5 / 4 and 4 / 5 respectively. Therefore Branch A has surplus employees and Branch B has a shortfall, but at the Divisional level it looks like there are the correct number of employees.
Naturaly my customer is a bit concerned that this could be misleading data. Is there any way that I can check at the Divisional level (i.e. collapsed) whether a Branch at the expanded level has a surplus or shortage of employees? I'm not sure whether this could be resolved by redesigning the report or if it's even possible. Any ideas would be great!
Regards,
Peter Marshall
I have a textbox, combo box, and a button on a form. I would like to perform a different query depending on the combo box selection. I thought I could do something such as: if (cboSearch.Text == "Selection1")
{
scCmd = "SELECT * FROM tblTable WHERE txtSearch = @Selection1";
}
else if (cboSearch.Text == "Selection2")
{
scCmd = "SELECT * FROM tblTable WHERE txtSearch = @Selection2";
}
else
{
scCmd = "SELECT * FROM tblTable WHERE txtSearch = @Selection3";
} However, this obviously does not operate as I would need it to. What is the proper method for conditional SqlCommand statements like this?
I am pretty new to SQL and facing difficulties with a current problem.
I have a list of customers and a sequence of events they have triggered . I know the sequence in which these events have been triggered and only want to increment a value when a new event is triggered (value to remain the same if the event is the same). I have come close to a solution with Dense_rank but the problem here is that the ranking doesn't reset if the same even previously triggered is triggered a bit later in the sequence. see below an example of current results and expected results:
Customer ID,Sequence ID,event,current result,expected result
1,1,A,4,1
1,2,A,4,1
1,3,B,3,2
1,4,C,2,3
1,5,A,4,4
1,6,A,4,4
1,7,E,1,5
1,8,D,5,6
2,1,B,3,1
2,2,C,2,2
2,3,C,2,2
I have a table in which I would like to change the color of some of the cells based on a condition.
For Example:
IDStartEndDowntime
12/9/15 13:28:412/9/15 13:28:46#VALUE!
22/9/15 13:29:032/9/15 13:29:120:00:17
32/9/15 13:29:172/9/15 13:29:210:00:05
42/9/15 13:29:272/9/15 13:29:310:00:06
When the value of Downtime is greater than 0:00:10 I would like to have the background of that cell to be Red and its font bold. How to accomplish this in query?
When you set a row's hidden property to true, RS writes a white row. Is there a way to keep the background color the same is it was before the row was hidden and just not display the data?
Can you do a IIF(Hidden=True) expression????
Thanks.