Conditional Fields Based On Export Type
Aug 27, 2007
I need to be able to suppress the printing of a particular value when exporting, but not when displaying on a web viewer on-line. I can place an IIF() condition around the field to do this, but do not know how to obtain a parameter/value/function which would recognize that the viewer has selected an export (To .PDF for example).
I would prefer there be a direct parameter I can read from the RDL language, however recognizing the selection while setting up the viewer to be displayed in the code-behind and setting an external parameter is also an option.
Any help would be appreciated.
Jerry
View 2 Replies
ADVERTISEMENT
Jul 2, 2015
I am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
ID, provider_name, uniq_id, total_spent, total_earned
Here is sample data:
1, Harbor, A07B8, 500.00, 1200.00
2, Harbor, A07B8, 400.00, 800.00
3, Harbor, B01C8, 600.00, 700.00
4, Harbor, B01C8, 300.00, 1100,00
5, LifeLine, L01D8, 700.00, 1300.00
6, LifeLine, L01D8, 200.00, 800.00
I need the results to be just 3 lines:
Harbor, A07B8, 900.00, 2000.00
Harbor, B01C8, 900.00, 1800.00
LifeLine, L01D8, 900.00, 2100.00
But then I would need the totals for the Provider, so:
Harbor, 1800.00, 3800.00
View 3 Replies
View Related
Mar 24, 2008
i have 4 column in my query , and arranged in matrix report
but i want to add some cells according if one column is true or false
i hope that i clear the problem
View 17 Replies
View Related
Jun 4, 2008
Good Afternoon,
I am new to MSSQL and am trying to write a complicated SQL statement that I'm having trouble with. Any help that anyone can offer is much appreciated!
Here is the problem I am tackling:
I have a list of about 5,000 members of our organization stored in the MemberList MSSQL table. I have a separate MSSQL table (CityList) that has approximately 500,000 resident of a city.
I am trying to find matches between MemberList and CityList for the purposes of figuring out which of our members are registered voters.
The tricky part of this problem, is that there is no unique ID (such as a social security number) that is present in each list. Accordingly, I have decided to created several types of matches:
1. NameDOBMatch: Where the FirstName, LastName & DOB fields in MemberList table match the same fields in CityList table.
2. NameAddressMatch: Where the LastName, FirstName & Address fields in the MemberList table match the same fields in the CityList table.
3. DoubleMatch: A combination of the first two matches (i.e. where the LastName, FirstName, DOB, & Address fields in the MemberList table match the same fields in the CityList table).
My goal is to "loop" through the MemberList and CityList tables and to add a new row to a third MSSQL table (MemberMatch) each time one of the aforementioned matches is found. The MemberMatch table has the following fields:
1. MatchID (key)
2. MemberID (Unique ID of member from MemberList table)
3. ResidentID (Unqiue ID of matching member from CityList table).
4. MatchType (value of NameDOBMatch, NameAddressMatch or DoubleMatch, depending on match type).
If anyone could help me create an SQL statement that would accomplish that, I would very much appreciate it!
Thanks,
Bryan
View 9 Replies
View Related
Oct 15, 2013
In the database, there is Date, Store#, Item#, and %Total Sales. In some cases, the same item# for the same date may be given more than one value for '% of Total Sales'. (For some reason this is a valid business scenario that happens rarely, but it happens.)
In that situation only, the requirement is to sum the two values together into one line. So if Item# 123 has a line with a value of .05%, and another line with a value of .08%, I need to sum those two values into one line for Item #123 that has a %Total of .13%. ONLY when an item has more than one percentage assigned, those percentages should be summed. Otherwise, if an item# has only one percentage value assigned, we just want to see that value.
Basically, I would like to implement logic that would work like this:
SELECT Date, Store#, Item#,
CASE WHEN Count(%Total Sales) >1 THEN Sum(%Total Sales)
ELSE %Total Sales
END
FROM (some tables and joins)
GROUP BY Date, Store#, Item#
However, I'm not sure how to craft it so that I don't get a syntax error (this query produces errors).
View 5 Replies
View Related
Mar 12, 2007
Hi,
I have a report that is conditionally showing a textbox based on the previous entry that is working correctly.
My issue is that the non visible entries are still being added to my Sum statement at the end of the report.
I need a way to exclude an entry based on its visibility.
Any help would be greatly appreciated.
View 9 Replies
View Related
Mar 5, 2014
I Have Table Called 'Sales' and 'Voucher',I Need To Show Each Customer ""Dueamount"" Details Based Upon Customer Paid in 'Voucher' Table But One thing I have Not Maintained Transaction History For Customer in 'Sales' Table Means I Have Column named "CreditAmount" in 'Sales' and Column Named "VoucherAmount" in 'Voucher' ,For every transaction I am updating Column named "CreditAmount" in 'Sales', So finally 'Dueamount' Must be calculated according to "VoucherAmount" of customer in 'Voucher' Table....
Sales Table:
BillMasterId BillDate CustomerId NetAmount CreditAmount
26 03/03/2014 101 1000 1000
My Query:
SELECT CONVERT(varchar,BillDate,103) as BillDate,isnull(NetAmount,0) as BillAmount, case when VoucherAmount != 0 then sum(VoucherAmount)else 0 end as'AmountReceived',case when CreditAmount !=0 then CreditAmount else 0 end as 'DueAmount' from Voucher INNER join Sales on CustomerId=CustomerID and BillMasterID=BillMasterID WHERE CONVERT(varchar,BillDate,103)='03/03/2014' AND CustomerId=101
My Output:
BillDate BillAmount AmountReceived DueAmount
03/03/2014 1000 0 0
03/03/2014 1000 500 0
03/03/2014 1000 300 0
03/03/2014 1000 200 0
Exact Output:
BillDate BillAmount AmountReceived DueAmount
03/03/2014 1000 0 1000
03/03/2014 1000 500 500
03/03/2014 1000 300 200
03/03/2014 1000 200 0
View 7 Replies
View Related
Sep 23, 2007
Hi,
I have the following table in MsAccess
EmployeesA
empId integer,
empName varchar(60),
empAge integer,
empStatus char(1) - can be N,D or S - New, Deleted or Shifted
and the following in Sql2005
EmployeesB
Id smallint,
Name varchar(60),
Age int,
Status char(1) - Bydefault 'N'
I have written a Foreach File package that populates the sql server tables (EmployeesB) from Access(EmployeesA). However i want to check for a condition now.
If empStatus = N in EmployeesA, then insert a new record in EmployeesB
If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'
If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row.
How do I do it for each table each row in EmployeesA using a foreach file loop?
Thanks,
lolsron
View 6 Replies
View Related
Jun 15, 2007
I am trying to create a report which has conditional formatting.
The primary dataset is a view of objects with several values
eg
object1,0,4,0,1
object2,0,3,1,1
The secondary dataset is the comparison table and just contains the values
eg
0,3,1,1
I'd like to conditionally format the values based on the comparison table but when I create an expression comparing to the second dateset
eg
=iif(Fields!object1.Value <> Fields!comp_object1.Value , "Red", "SkyBlue")
i get
Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
Not sure if there is a way to tell the expression to look for comp_object1 in dataset2, even tho it is uniquely named?
Ideas gratefully received !
View 7 Replies
View Related
Mar 15, 2007
I have two databases located on seperate machines, using different platforms and credentials.
I have setup dataset1 source to the first database wich provides me the following colums
Incident_id, description, date, status.
And dataset2 datasource provides
incident_id, is_billable
My report is a tabular report showing the four fields from dataset1.
I am trying to apply some conditional formating wich will link the two datasets on the incident_id field.
something along the line of if the dataset1.incident_id is in dataset2.incident_id then turn it red.
I tried using the below expression on the color property but the IN part is not allowed.
=iif(Fields!INCIDENT_ID.Value,"Dataset1" in Fields!INCIDENT_ID.Value,"Dataset2","Black","Red")
View 2 Replies
View Related
Dec 15, 2005
Hi!
I have a table called DB1 that contains this:
MID
IIN
NUM_EVENTS
DATE
MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?
View 6 Replies
View Related
Dec 15, 2005
Hi!
I have a table called DB1 that contains this:
MID
IIN
NUM_EVENTS
DATE
MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?
$3.99/yr .COM!
http://www.greatdomains4less.com
View 3 Replies
View Related
Jul 14, 2015
My source table has two columns... Policynum and PolicyStartdate and data looks like..
.
Policynum PolicyStartdate
123G 01/01/2012
456D 02/16/2012
789A 01/21/2012
163J 05/25/2012
Now my output should return based on 3 parameters..
First two parameters are date range... let say @fromdt and @todt
Third parameter is @policynum
Scenario-1: Enter dates in date range param and leave policynum param blank
Ex: policystartdate between '01/01/2012 and '01/31/2012'.... It returns 1st and 3rd rows from above in the output
Scenario-2: enter policy num in policynum param and don't select any dates
Ex: policynum ='456D' It returns 2nd row in the output
Scenario-3: Select dates in date range param and enter policynum in param
Ex: policystartdate between '01/01/2012 and '01/31/2012' and policynum
='163J'. it should return only 4th row even though dates were selected(Override date range when policynum is entered in param and just return specified policynum row in the output)
I need t-sql code to get above results.
View 12 Replies
View Related
Sep 5, 2007
This is probably an easy question, and I just can't find the solution. I've searched extensively, but I am probably just not searching for exactly what I need.
Basically, I have a Conditional Split. What I need to do is for each row coming out of my split, I need to SELECT some data from another database based on one of the fields and then place the data from the DB into a file for later processing.
Seems pretty simple, considering the power of SSIS. Using tools such as OLE DB Command didn't help - the data that comes out of the OLE DB Command is the input data, not the data returned by the command.
How can I do this?
Thank you!
Nolan
View 1 Replies
View Related
Jul 24, 2015
Basically our cube contains two different Facts:
- Sales Volume: Information about sold articles to a customer incl. Selling date
- Survey: irregular answered survey questions about customers incl. date of answer
and three Dimensions:
- Customer
- Date
- Survey Answer: Information about possible Answer values (e.g. Yes / No)
Relations:
Fact: Sales Volume ------> Dim: Customer <------ Fact: Survey ------> Dim: Survey Answer
'--------------> Dim: Date <----------------'
We would like to be able to determine the aggregated sales volume (sum) of a customer for a specific period depending on the latest survey answer within this period.
For example:
Selected Time period: Jan - Jul 2015
Sales Volume Customer X - Jan - Jul 2015: 1000 Litres
Sales Volume Customer Y - Jan - Jul 2015: 500 Litres
Surveys answered:
15th Jan 15: Customer X, Survey Question A: Yes
2nd Mar 15: Customer X, Survey Question A: No
20th Apr 15: Customer X, Survey Question A: Yes
10th Feb 15: Customer Y, Survey Question A: Yes
20th Jul 15: Customer Y, Survey Question A: No
Latest survey answer (Jan-Jul) Customer X, Question A: Yes
Latest survey answer (Jan-Jul) Customer Y, Question A: No
Excel Pivot should show something like this:
Question | Latest Answer: Yes | No |
------------------------------------------------------------------------------------
A | 1000 Litres | 500 Litres |
How this is possible and has to be implemented?
View 5 Replies
View Related
Sep 4, 2015
We are building a dataload application where parameters are store in a table. And there are multiple packages for each load.There is a column IsChecked column if it is 1 then only the child package should execute.Created a master package. In which i have taken execute SQL task in that storing a results in variable and based on the result the child package should execute. But In executesql task i selected result set as full result set. I am getting the below error.
[Execute SQL Task] Error: Executing the query "SELECT isnull(ID ,0) AS ID FROM DataLoadParameter..." failed with the following error: "The type of the value (DBNull) being assigned to variable "User::LoadValue" differs from the current variable type (Int32). Variables may not change type during execution. Variable types are strict, except for variables of type Object.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
View 3 Replies
View Related
Jul 15, 2014
I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.
I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved. As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?I am including a copy of my stored procedure as it is.
View 6 Replies
View Related
Aug 2, 2007
I have a matrix report with 2 column SaleAmount and ProfitAmounts by Month like
Sale Profit
Dealer 5/1/2007 6/1/2007 7/1/2007 5/1/2007 6/1/2007 7/1/2007
A 100 200 300 20 25 15
B 200 250 50 30 45 19
how can i do following 3 things
1)Add Total column for Sale and Average column for Profit
2)Sort report by lastMonth of Sale (here 7/1/2007) High to low
3)if last month of sale(here 7/1/2007) is less than second last month here (6/1/2007) whole row should be red
thanks
View 1 Replies
View Related
Jul 23, 2005
I have a query where I am trying to export a column that has around2000 characters and it is truncated. What is not setup in MS SQLcorrectly so that DTS will export the csv correctly.Thanks,John
View 1 Replies
View Related
Jan 30, 2008
Hi,
I hide some fields in the Report based on the user's query selection.
However, when the user exports to excel all the report fields appear.
How do I show only the relevant fields in the excel export?
Thanks.
View 13 Replies
View Related
May 14, 2008
I have a report that has 10 columns. While viewing, users should see only 8 columns and 2 will be hidden.
But is there any way we can export those 2 hidden columns to excel?
Any help is greatly appreciated.
Thanks,
View 3 Replies
View Related
May 27, 2014
I have a table having multiple fields but I need to extract data based on the two fields.
Field1 Field2
1 | 123
1 | 213
2 | 123
2 | 3654
2 | 4758
I want the result with 2 cases
1.) distinct in Field 1 but multiple in field 2
Expected result:
Field1 column1 column2 column3
1 | 123 | 213
2 | 123 | 3654 | 4758
2.) multiple in field 1 but distinct in field 2
Expected Result :
Field 2 | Column1 | Column2
123 | 1 | 2
There may be more columns added according to the data available.
View 7 Replies
View Related
Aug 17, 2005
writes "Hi,
I am VERY new to SQL and I do not know if I am word this question correctly, so forgive me.
I would like to create a new table that does calculations on some of the same fields.
•IPA number (from the IPA_Num field)
•Total number of Rx's (sum of rx field )
•Total Paid( sum of amt_paid field)
•Total generic paid (If the field drugs = 1 then sum the amt_paid field)
•Percent generic (If the field drugs = 1 then sum and divide by the sum of the field rx)
•Percent formulary (if the formulary field = A then sum and divide by the rx field)
•Cost difference ( Average amount paid per brand Rx - average amount paid per generic Rx )
Any help is greatly appreciated.
Blessing to all
Yvonne"
View 5 Replies
View Related
Jan 24, 2008
Hi,
I have a table with eight (8) fields, including the primary key (rfpid). Three of the fields are foreign keys, which take their values form lookup tables. They are int fields (pmid, sectorid, officeid). One of the fields in this table is based on putting together the descriptive field in the lookup table for sector (tblsector). The two other fields to be part of this string are the rfpname and rfpid. This creates the following string:
rfpsector_nameproposalscurrent_year
fpname_08_rfpid
NOTE:
The words rfp, proposals are words that have to be part of string;
the slashes are to also appear.
current_year would be defaulting to datepart = year (2008)
The part that has the last two digits of the current year then the underscore and then the rfpid should be connected by an underscore to the rfpname.
I am at a loss and would greatly appreciate any help.
Thanks
View 51 Replies
View Related
Sep 2, 2015
I have a table with two sets of fields, one for PRIMARY Mail Address and the other for ALTERNATE Mail Address. Both sets are five fields long. I will call them PRIMARY & ALTERNATE for this discussion. I want to select the PRIMARY fields if ALTERNATE is either null or zero, and the ALTERNATE fields if data is present.
I have used this syntax but it gives a table with both the PRIMARY & SECONDARY records.
SELECT TXPRCL, TXALTR, TXANAM
FROM dbo.PCWEBF21
WHERE (TXALTR > 0)
UNION
SELECT TXPRCL, TXTAXP, TXTNAM
FROM dbo.PCWEBF21
WHERE TXALTR = 0
Would a Select Case work? something like
Select PRIMARY when ALTERNATE = 0, and ALTERNATE when ALTERNATE > 0 or is not null.
View 17 Replies
View Related
Mar 25, 2008
How can i print or export all fields for all tables in my sql 2005 db?
View 3 Replies
View Related
Sep 20, 2007
Hi
I need to export data to CSV file from Sql Reporting Services.
I am including single quotations in my view to display in SRS for string fields.
After export if we open in Notepad, for string field it is adding multiple quotations.
How to add single quotations for string fields - CSV files,
Please let me know if anybody knows.
Thx
Vijji
View 4 Replies
View Related
Jul 28, 2014
How can I pull out duplicate records based on certain fields?
Table called Bank
I want to pull out records that have duplicate inv_no, cus_no, amount,ordernum
Not all the fields are the same in each record. But I want the records that have these fields that are the same.
View 1 Replies
View Related
Oct 5, 2007
I required to build the search feature for my application which contains combination of at least 20 search fields e.g firstname, lastname. date of birth, sign up date ,etc... I am just wondering what is the best way to do it ,should I create stored procedure with 20 input parameters or should I build it based on each search fields. I need to provide the search results via web services. Could anyone help me?
Thank you
View 5 Replies
View Related
Mar 26, 2008
Hi,
I have three data sets. Data Set A is bound to my table in my report. There are also Data Sets B and C. All three data sets have the identical columns. I have setup data set A so that all of its fields are "Calculated". The calculation for each field will either pick the field from data set B or data set C based upon a parameter called BorC. So the calcualted fields in data set A look like the following:
=iif(Parameters!BorC.Value, First(Fields!MyField.Value, "dataset_B"), First(Fields!MyField.Value, "dataset_C"))
Problem is when I run the report I get internal error. Is it possible to populate one data set from the fields of another data set? If so is there some trick to doing this? Stripping this example down to one column in each of the data sets, and then just directly setting data set A from either B or C still gives me the same error. SO I set the one field in data set A to the following and it still doesn't work:
=First(Fields!MyField.Value, "dataset_B")
OR
=First(Fields!MyField.Value, "dataset_C")
This leads me to believe there is some fundamental problem with using another data set in a Calculated field. Since I think I have done this before I am convinced I am doing sommething wrong.
Any advice you have for me would be greatly appreciated!
Thanks.
View 1 Replies
View Related
Oct 19, 2007
When I was looking at this I knew that I've done this same issue before without using temp tables at all but I need a push to to jar it loose.
I have a table like this:
Balance
Date
1
200
2/14/2000
2
350
2/14/2000
3
32
2/14/2000
2
723
2/14/1998
3
354
2/14/1998
1
321
2/14/2000
2
673
2/14/1998
3
581
2/14/2000
2
574
2/14/1998
3
50
2/14/2000
1
10
2/14/2000
And essentially need this.
Total Balance Before 1/1/2000
Total Balance After 1/1/2000
1
0
531
2
1970
350
3
354
663
Right now I'm splitting it into two temp tables and then joining them together.
Temp Table 1 has the sum of the balance grouped by field1 before 1/1/2000 and Temp table 2 has the after 1/1/2000 criteria.
Table 1 gets updates with field1 values from table 2 that aren't already there. And then the balance field after 1/1/2000 is merged in.
Utimately this will be used in a SPROC for a Multivalued SSRS report.
View 3 Replies
View Related
Mar 22, 2000
I have a varchar field which holds IDs like (1, 3, 5, 19, 23) when I order it, i get it ordered in ASCII order like (1, 19, 23, 3, 5) rather than (1, 3, 5, 19, 23) Even if I convert it to int, I won't be able to order it.
is there any way I can order a varchar field numerically?
Angel
View 1 Replies
View Related
Jul 20, 2005
I have a field in my database which stores product codes. This fieldis a varchar. Some users use characters in their codes but most justuse numbers. Whenever I go to sort the products by code the order ofthe numbers go something like: 1, 100, 101, 2, 210, 220, 2500, 3, ...I understand why this is sorted this way but is there anyway I canhave it sort the numbers in their proper numeric order (1,2,3,100,....) and just have them always appear before any codes that containcharacters? Right now I just have users put zeros in front of theirnumbers like: 0001, 0100, 0101, 0002, ...
View 4 Replies
View Related
