I have the below script to create directories based on my select statement.
create table students
(
f_name varchar(100),
l_name varchar(100)
);
insert into students values ('John','Smith'),
[Code] ...
The result I am getting is truncating the directory names as in the below:
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
I have a report that uses different datasets based on the year selected by a user.
I have a year_id parameter that sets a report variable named dataset_chosen. I have varified that these are working correctly together.
I have attempted populating table cell data to display from the chosen dataset. As yet to no avail.
How could I display data from the dataset a user selects via the year_id options?
If I want to select a value from a certain row in my Dataset.
I have a dataset called labels in my Report. in this data set I have diffrent row and in each row I have a label with an
ID. For example:
ID Text
TEXT0001 Hi
TEXT0002 Good Morning
TEXT0003 Good bye
Is this is the right way to get the second row?
=First(Fields!ID.Value="TEXT0002", "Labels")
I need to set a field value based on two date time parameter's.What is the correct syntax to allow me to pass the value into the field in my SSRS expression ?
=IIf(Parameters!EndDate > Parameters!StartDate.Value, "Overdue")
When we assign security in SRS, in the user name field we have to type the domainusername manually. We cannot see the drop down of all the users in Active directory. Is it possible to configure SRS to get the names from Active directory. If we add any new users to our AD, it automatically refreshes at certain set intervals.
I am talking to a similar function in Sharepoint.
I browsed through a bit on this and think it is not possible. But I want to make sure that I am on the right path.
Can you please correct me if my understanding is right?
I appreciate your help and thank you for your time,
Dear All
I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
Thanks Ross
The permissions granted to user 'xxxxyyy' are insufficient for performing this operation. When a user is deleted and then readded to active directory. Reporting services returns the insufficient permissions granted error. In dbo.users in the sql database there are 2 entries for the deleted user one with UserType 0 and another with UserType 1. How do you clean up reporting services or sql to allow the reciently re-added user to re-connect to reporting services. We use the MY-Reports option of reporting services but cannot delete the home directory for this user either in report Mgr. The same error is returned for this action.
When I go to Save As Report it doesn't allow me to create a new directory (folder)
Here is where the directory (folder) needs to be created: gdgdb01preportserverProduction.
Where can I create this directory (folder)?
I am running into a weird issue with a new SQL Reporting Services 2014 server I built. I installed SQL Reporting 2014 on Windows Server 2012 R2 and configured Kerberos, but the site is extremely slow. After some reconfiguration and log captures I have determined the issue has to do with the Kerberos setup, however I am running a similar configuration with SQL Reporting Services 2008 on Windows Server 2008 R2 and do not run into the same errors.
The error I see while using Wireshark is KRB Error: KRB5KDC_ERR_BADOPTION NT Status: STATUS_NO_MATCH. When I drill down the into the error I can see the kerberos string is testprjmnmtreports14.company.com, which is the URL we are using to access the site. I made sure to add that name as an SPN for the service account that is running SQL Reporting Services, however I still receive the error.
Then I tried configuring the site to run without a hostheader, so I accessed the site with the server name, ECTSTSQLRS5, and the site works perfectly fine, no errors are reported either. So it seems I have isolated the issue down to Kerberos but I am not sure how to resolve it. Here is some more information about my environment:
DNS/URL used: testprjmnmtreports14.company.com
Server Name (FQDN): ECTSTSQLRS5.company.int
AD Domain Name: company.int
Server Version: Windows Server 2012 R2
AD Functional Level: 2008 R2
As you can see I am trying to use a .com address but my AD domain is .int which I think is the issue, but I do not have the same problem on my other server that is running Windows Server 2008 R2. What do I need to do to allow my new site on 2012 R2 to work with this DNS Alias?
I have a set of records that will always be the same. A template if you wanna call it that.
"SELECT ID, Field1, Field2, Field3, Field4, Field5, Field6, Field7 FROM TABLE WHERE Field2 = 000"
This returns a 20 record set. The user will click a button and create another set of 20 records, where they can make changes accordingly.
I have select to split FullName on LastName and FirstName columnselect
Substring(FullName, 1,Charindex(',', FullName)-1) LName
,Substring(FullName, Charindex(',', FullName)+1, LEN(FullName)) FName
from Table1
Is it possible to create function based on that select? If yes. How it to do?
i am not getting a result back when i run the query below.
select * from users where DateCreated = '2004-02-19'
so i went into the table and looked at the record. for DateCreated field i have both date and time. ex: 2004-02-19 08:40:00
how can i select this record with out using the time in the select statement. what i want to see is how many users signed up for a day. any ideas?
Has anyone used this successfully from an OLEDB source component, or even from the Execute SQL Task? I've seen some examples of using a script component, but nothing that uses it through a connection manager.
View 6 Replies View RelatedI have built an Advanced Search page which allows users to select which columns to return (via checkbox) and to enter search criteria for any of the selected columns (into textboxes). I build the SQL statement from the properties of the controls. Works great.
My problem is getting my gridview control to play nicely. At first I used a SqlDataReader and bound the gridview to it, thus giving me the ability to run new SQL statements through it (with different columns each time). Worked nicely. But, per Microsoft, sorting can only be done if the gridview is bound to a datasource control like the SqlDataSource. So I wrote the code to handle sorting. No big deal; worked nicely. But I could not adjust the column widths programmatically unless bound to a datasource control like the SqlDataSource. And could not figure out a work around.
So, I decided to use the SqlDataSource. Works great. Except, I cannot figure out how to run a new SELECT statement through the SQLDataSource and have the gridview respond accordingly. If I try to return anything other than the exact same columns defined declaratively in the html, it pukes. But I need to be able to return a new selection of columns each time. For example, first time through the user selects columns 1,2,3,4 – the gridview should show those 4 columns. The second time the user selects columns 2,5,7 – the gridview should those 3 columns (and ONLY those 3 columns). Plus support selection and sorting.
I am desperate on this. I've burned 2.5 days researching and testing. Does anyone have any suggestions?
Thanks,
Brad
I basically want to select all GRNID's from one table but they have to be between dates in another table.So I want all GRN's between two dates found in the ABSPeriodEndDate table. To find out the start date for the between clause I need to find the MAX Period then minus 1 and the max year. To find the end date of the between clause I want I need to find both the max period and year. But I want the DateStamp column to return the results for the between clause. My query is below:
SELECT tblGRNItem.GRNID
FROM tblGRNItem
INNER JOIN ABSPeriodEndDates ON tblGRNItem.DateCreated = ABSPeriodEndDates.DateStamp
WHERE tblGRNItem.DateCreated BETWEEN
(SELECT ABSPeriodEndDates.DateStamp FROM ABSPeriodEndDates WHERE ABSPeriodEndDates.DateStamp = (SELECT
[code]....
ID DATE(dd/mm/yy) TYPE QTYIN COST_IN_AMT COST_OUT_AMT(MOVING AVERAGE)
1 01/01/2007 PURCHASE 10 1000
2 01/01/2007 PURCHAES 5 1100
3 01/01/2007 SALES -5 *TobeCalculated
4 02/01/2007 Purchase 20 9000
5 02/01/2007 SALES -10 *TobeCalculated
5 02/01/2007 purchase 50 8000
6 03/01/2007 Sales -10 *TobeCalculate
7 01/01/2007 Purchase 20 12000
I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here)
In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly.
When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly.
The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated.
Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time.
Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?
Can I use Reporting Services 2005 in a workgroup to serve reports for a windows application which is distributed accross the network?
I've already tried but when the application on a client pc requested a report from the report server i got a message about not having enough permissions to see the report. The windows application was builded using VB .NET 2.0 and VS2005.
The report server instance is installed on a server 2003 enterprise along with the SQL Server 2005.
If this is possible how can set permisions or make my application to be able to requests and show reports?
Thanks a lot to anybody who takes the time to answer.
Antonio Alfaro
I need to hide a row based on a value but show it for the rest of the values! this is the expression i am using for the first row is
=IIF(Fields!QuestionRef.Value = "1.2" or "2.1" or "10.2" or "20.2" or "23.2" or "30.1", true , false)
And for the second row
=IIF(Fields!QuestionRef.Value = "1.2" or "2.1" or "10.2" or "20.2" or "23.2" or "30.1", true, false)
But it either hides the row for all values or shows it for all values.
I am using SQL server 2012 and Report builder 3.0 to build my report. I have build a report which product the following table
What I try to achieve is to add 2 calculated field at the bottom of the table which will represent 2 Total value based on a condition
OutTotal field should show the SUM of the Quantity only for ItemStatus=0
InTotal field should show the SUM of the Quantity only for ItemStatus=1
IMPORTANT : I cannot group my data because I need to shown them in a time Wise flow
I have try to insert the first field and define an expression but it gives an #Error. The expression I used is as below
=SUM(IIF(Fields!ItemStatus.Value = 0, Fields!ItemQuantity.Value*Fields!ItemUnitWeight.Value,0))
The Quantity filed in my table is calculated from expression Fields!ItemQuantity.Value*Fields!ItemUnitWeight.Value
Mr. Pearson,
I am currently reading your article titled, "MDX in Analysis Services: Create a Cube-Based Hierarchical Picklist". This article is directly applicable to a problem we are currently trying to solve regarding ragged hierarchies as input parameters.
I have not read the entire article through but am in the process of doing so. Also, I will be trying to implement your solution.
I have one question, will SSRS support multi selection when using a hierarchical picklist? Thanks in advance for any assistance.
I am using the following script to check existence of table in the Database and create it dynamically...
This is working when table not existed, it error-ed when the table existed...
This script i am using in the Exec Sql Task.....
[Execute SQL Task] Error: Executing the query "declare @ODSDB varchar(50)
declare @SQLSTMT varcha..." failed with the following error: "There is already an object named 'addressTable' in the database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not
set correctly, or connection not established correctly.
declare @ODSDB varchar(50)
declare @SQLSTMT varchar(max)
set @ODSDB = 'SampleDB'
begin
set @SQLSTMT = '
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(''' + @ODSDB + '.dbo.addressTable'') and Type=''U'')
[Code] ...........
I have a table that holds notes for item's. I'm want to do a select statement where one of my columns assigns a sequential value to each row based on the item number. Would like data to look like this where doc_no would be my row_number function:
item_no seq_no note doc_no
ABC 1 blah 1
ABC 2 blahh 1
ABC 3 bla3 1
XYZ 1 more n 2
XYZ 2 another 2
EFG 1 blahhh 3
I'm working on a scheduling report to display work to be completed on each day. I have each day iteration as separate subreports by adding day values to "Today()" I'm trying to hide subreports that return as Sat or Sun.
Here's what I'm using to define my WeekDayName=WeekDayName(Weekday(DateAdd("d",1,Today())),True,0)
What I would like to do is =WeekDayName(Weekday(DateAdd("d",1,Today())),True,0) = 'Sat' OR WeekDayName(Weekday(DateAdd("d",1,Today())),True,0) = 'Sun' but I get an error when I attempt that.
I'm entering this under Sub-Report Properties - Show/Hide based on Expression using Report Builder 3.0.
how I can achieve this?
Using Report Builder 3.0 on SQL 2008 R2
I am trying to get 3 report parameters working to show 3 different tables.
table 1 = Area 1 data
table 2 = Regional Area 1
table 3 = Stores in Regional Area 1
Currently I am working on the biggest one. E.g. if all 3 parameters have information in them, then you use table 3. I have written out the coding as the following
=iif(isnothing(Parameters!Area.Value),True, False ) and
iif(isnothing(Parameters!Regional.Value),True, False ) and
iif(isnothing(Parameters!store.Value),True,False)
But it errors stating end of statement expected. What I have missed or not done here?
I am trying to use SSRS to allow users to select any one or several columns from a set of cascading parameters which will then do a "data dump" of the contents of the selected columns for "export".I know how to do cascading parameters, but I am having problems coming up with a way of using the parameters to dynamically build a matrix which has as its columns the list selected in the parameters. I need the user to choose WHICH columns will be displayed. There could be 1, 2, 3, up to 50 columns.
View 2 Replies View RelatedI have an issue in trying to format rows base on conditions. Below is a replication of the tables and the select statement.
CREATE TABLE #CompareVal
(CompareValID INT Not Null
, ValName NVARCHAR(75) Null
, Vehicle INT Null
, Driver INT Null
);
GO
[Code] ....
First issue, James and Jane does not have a driver available and that should show "No Driver available"I am to compare values in VehicleAvailable and DriverAvailable to the first row - (Group Value Standard row) so that when a value is less than the value in first row, it should be Gold, if equal to, Blue and if greater than then, Red.
The first row is to be Black. In other for me to be able to compare, I added columns like so:
SELECT #CompanyName
, [Description]
, ISNULL(CAST(VehicleAvalible AS NVARCHAR(30)),'N/A') AS VehicleAvalible
, ISNULL(CAST(DriverAvailable AS NVARCHAR(30)),'No Driver available') AS DriverAvailable
, 0 AS TotalVehicles
, 0 AS TotalDrivers
[Code] ...
And my expression for "VehicleAvailable" column is :
=Switch(Fields!Description.Value = "Group Value Standard" AND Fields!VehicleAvalible.Value = Fields!TotalVehicles.Value, "Black"
, Fields!Description.Value = "Group Value Standard" AND Fields!VehicleAvalible.Value < Fields!TotalVehicles.Value, "Black"
, Fields!Description.Value = "Group Value Standard" AND Fields!VehicleAvalible.Value > Fields!TotalVehicles.Value, "Black"
[Code]....
This doesn't work as I am comparing integer against text value. How do I format to get result like the below image?
I have a GridView dispalying from a SQLServerDataSource that is using a SQL Select Union statement (like the following):
SELECT FirstName, LastNameFROM MasterUNION ALLSELECT FirstName, LastNameFROM CustomORDER BY LastName, FirstName
I am wondering how to create Update and Insert statements for this SQLServerDataSource since the select is actually driving from two different tables (Master and Custom). Any ideas if or how this can be done? Specifically, I want the Custom table to be editable, but not the Master table. Any examples or ideas would be very much appreciated!
Thanks,
Randy
I am working on SSRS. Based on parameter value I needs to call the related view. Below are my current expression query. but it has some error.
=IIF(Parameters!RacServiceOfferingsOnly.Value=0, SELECT * FROM VW_FLIGHT_SHEET_SUMM_RAC_N WHERE "TransferDate" BETWEEN DECODE(:StartDate,NULL, "TransferDate",:StartDate) AND DECODE(:EndDate,NULL, "TransferDate",:EndDate) AND
(:TransferType IS NULL OR :TransferType = "Direction"),SELECT * FROM VW_FLIGHT_SHEET_SUMM_RAC_Y WHERE "TransferDate" BETWEEN DECODE(:StartDate,NULL, "TransferDate",:StartDate) AND DECODE(:EndDate,NULL, "TransferDate",:EndDate))
I have one requirement. We have one application in banking and they have developer that application using c# and .Net code so For Business Users--when they enter their details and when they want to check Reports--it should take to a another UI or web page where Users will see all the reports--- and clicking the report he/she should be get able to export it into different formats..
i know that we can give report manager URL--but due to various issues-- we have choosen to built a web[age where they can see the reports work-flow:
so--user opens bank site--enters his details-goes into reports--it should open new webpage or UI, he should see all reports--he should export these reports..
After that based upon security levels each user can see their individual reports.
I am producing a report in Report Builder 2 containing a chart and table. The user is able to select single or multiple choices on a parameter called Specialty. My present chart is only suitable when one Specialty is selected. Therefore I only want this to be visible when the user has selected one Specialty. I am planning to create a second chart which is suitable for than more than one selection. So if the user selects more than one Specialty the existing chart will not be visible and instead will be replaced by the second.I think this should be done using Chart Properties>Visibility but cannot develop the logic of the expression.
View 5 Replies View RelatedI have some duplicate values for my query results, about 200 duplicates out of 30000 rows. Of these 200 duplicates I want to keep the ones that have a higher value for... 'UpdatedBatchID'.
SELECT
IR.Id as 'ID'
, CAST(IR.Priority as varchar) as 'Priority'
, IRSupportGroupDN.DisplayName as 'Support Group'
, DATEADD(MI,DATEDIFF(mi,GETUTCDATE(),GETDATE()),IR.CreatedDate) as 'Created Date'
, DATEADD(MI,DATEDIFF(mi,GETUTCDATE(),GETDATE()),IR.ResolvedDate) as 'Resolved Date'
, SLOConfig.DisplayName as 'SLO'
, DATEADD(MI,DATEDIFF(mi,GETUTCDATE(),GETDATE()),SLOFact.TargetEndDate) as 'SLO Target'
, SLOStatusDN.DisplayName as 'SLO Status'
, SLOMetric.DisplayName as 'SLO Metric'
, SLOFact.UpdatedBatchId as 'UpdatedBatchID'
I need to hide a column just for certain values from a table like contract decorators I tried using
=IIF(Fields!MainTrade.Value = "Contracts Decorator, Bricklayer", True, False)
But it just hid the column for all values, then i tried this but it doesn't seem to work (Think it's not correct though)
=IIF(Instr("Contracts Decorator, Bricklayer","," & Fields!MainTrade.Value & ",") > 0,true , false).
Below is the table and the highlighted column i am trying to hide just for those two fields. The table has a page break so that it would be displayed on a different page for each value, but i'm not sure it is possible as i have only done it for integers and rows before.