Change Display Message When Else Is 0 To Show (Non Active Member)
Feb 11, 2015
I want to change the display message when else is 0 to show "non active member", how to edit this query for this result .
---------------------------------------------------------------
Current Query
cast(sum(case when trx_date > dateadd(month, -3, getdate()) then 1 else 0 end) as varchar(10))+ ' Active Member := '+ user_member
---------------------------------------------------------------
I try this query but not work and prompt error.
cast(sum(case when trx_date > dateadd(month, -3, getdate()) then 1 else "non active" end) as varchar(10))+ ' Active Member := '+ user_member
----------------------------------------------------------------
We are using Windows authenication within our system, and I was wondering how it would be possible to determine if the user conected to the SQL SERVER instance was a member of a particular active directory security group?
I created a Time table using BIS. I found that the default naming of time members is too long and redundant.
For example, the wizard generated "Fiscal Calendar 2015", "Fiscal Quarter 1, 2015", etc. However, shorter expression like "FY2015", "FQ1 2015", etc would be enough for me.
Is it possible to change the default naming rule, or does SSAS works correctly if I update the Time table values using SQL?
I have two tables, Promotion and Promolocation. The Promotion table is used to set up promotions or sales, and consists of a PromoID, StartDate, and EndDate. Each PromoID is referenced in the Promolocation table, which is used to assign items to a promotion for various locations or stores. The Promolocation table consists of PromoID, LocID, SkuID, PromoPrice, and DiscLevel.
There are times where an item or SkuID will exist in more than one promotion, however, our application is currently not intelligent enough to determine which promotion to use, so it sets the active promotion based on the StartDate being before other promotions' StartDate and the EndDate being after other promotions' EndDate.
I want to find all promoid's where a sku exists in more than one promotion. I want to signify which promotion is active, using 1 as the first active promotion, 2 as the next active, 3 as the next, etc. To determine which promotion is the first active promotion, the StartDate must be before any of the other promotions' StartDate, and the EndDate must be after other promotions' EndDate. If the promotions' StartDate is after the other promotions' StartDate but not before the other promotions' EndDate, and the EndDate is before or on other promotions' EndDate, then that's the second active promotion. If the StartDate is the same as other promotions' StartDate, but the EndDate is before other promotions' EndDate, then that's the third active promotion.
For example:
PromoID StartDate EndDate ------- --------- ------- PROMO1 1/1/2004 1/1/2006 (1st Active Promotion) PROMO2 2/1/2004 1/1/2006 (2nd Active Promotion) PROMO3 1/1/2004 12/1/2005 (3rd Active Promotion)
Here's a query I am using to display all active promotions:
select pl.promoid, pr.startdate, pr.enddate, pl.locid, pl.skuid, pl.promoprice, pl.disclevel from promolocation pl inner join promotion pr on pl.promoid = pr.promoid where pr.enddate >= getdate()
Team members appear twice or more if they belong to more than one team. I need to be able to show their name and main team. Team is not important at the moment but I just like to include all team members and display a team name against them.This is what I have at the moment:
SELECT SystemUser.systemuserid, FullName, TeamMembership.TeamID, TeamName FROM Team RIGHT OUTER JOIN TeamMembership ON Team.teamid = TeamMembership.teamid LEFT OUTER JOIN SystemUser ON TeamMembership.systemuserid = SystemUser.systemuserid order by FullName
I have a report with many matrices. Basically what I want to do is display a message such as "No History within Date Range Selected" if there is no data on the report (all of the matrices return no data).
Is there a way to determine if the report is returning no data and show a textbox message in that case?
Hello, I would like to change the active user in a stored procedure Ex. : I'm logged on sql as "userA". I call the stored procedure "spGetInfo". In the first line of "spGetInfo" I would like to do something like "su poweruser", query some data and do "su system_user"
Is there anything like the unix "su" command in SQL ?
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.
We use extended properties of Active Directory to filter the data in reporting services throw a web services. We send the userid (User!UserId) to the webservices and retrieve some information about the user. But if we change the name of one user (rename not recreate), he lost all this properties. When try to execute a report show an error:
Parameter validation failed. It is not possible to provide valid values for all parameters. (rsParameterError) I know this issue is in the border of reporting services, but we have spent a lot of time without results and we need any help.
I have an SSIS package set up to run various SQL scripts and to notify me if any of the SQL scripts fail. This is working fine, but when I get the email it only says that the script has failed. Is there any way to actually show the SQL result with the error in it? This would save me from having to go and manually run the script again to see the error message.
Thanks for everyone's help on this mess so far especially Peso. Yes this was originally Oracle SQL and I now need to convert it to SQL.
Can someone start by showing me at least part of this the best way to convert this to regular SQL? Also are there any tips, guides out there that anyone knows of?
selectCSG_Hist.dbo.OJB_JOBS.IR_TECH_OJB as TECHN, substring(CSG_Hist.dbo.OJB_JOBS.COMPL_CDE_OJB, pstn.p, 3) as RESCODE, Parser.dbo.OJB_JOBS.COMPL_DTE_OJB as COMPLET, Parser.dbo.ELP_Codes.CodeDes as [DESCRIPTION], CSG_Hist.dbo.SBB_BASE.RES_NAME_SBB as RES_NAME, CSG_Hist.dbo.HSE_BASE.ADDR1_HSE as [ADDRESS], CSG_Hist.dbo.OJB_JOBS.JOB_TYP_OJB as JOB, CSG_Hist.dbo.OJB_JOBS.JOB_CLASS_OJB as TYP, CSG_Hist.dbo.OCR_ORDER_COMP.ORDER_NO_OCR as NUMB, CSG_Hist.dbo.OCR_ORDER_COMP.LS_CHG_OP_ID_OCR as OPR, Parser.dbo.ELP_Codes.CommissionAMT as NCommissionAMT, COUNT(DISTINCT Parser.dbo.Parser_OCR.ORDER_NO_OCR) as QTYW fromParser_OCR Inner JoinParser.dbo.Parser_OJB on Parser.dbo.Parser_OCR.ORDER_NO_OCR = CSG_Hist.dbo.OJB_JOBS.ORDER_NO_OJB Inner JOINParser.dbo.RGV_Codes on substring (Parser.dbo.Parser_OJB.COMPL_CDE_OJB, pstn.p, 3) = CODE -- IS THIS A VARIABLE/PARAMETER OR A COLUMN NAME? Inner JOINCSG_Hist.dbo.SBB_BASE on CSG_Hist.dbo.OCR_ORDER_COMP.HSE_KEY_OCR = CSG_Hist.dbo.SBB_BASE.HSE_KEY_SBB Inner JOINCSG_Hist.dbo.HSE_BASE on CSG_Hist.dbo.OCR_ORDER_COMP.HSE_KEY_OCR = Parser.dbo.Parser_OCR.HSE_KEY_OCR Inner JOINCSG_NRT.dbo.NRT_OJB_JOBS ON CSG_NRT.dbo.NRT_OJB_JOBS.IR_TECH_NOJB = CSG_Hist.dbo.OJB_JOBS.IR_TECH_OJB cross Join( select 1 as p union all select 4 union all select 7 union all select 10 union all select 13 union all select 16 ) as pstn WHERECSG_Hist.dbo.OJB_JOBS.IR_TECH_OJB between 950 and 999 and Parser.dbo.Parser_OJB.COMPL_DTE_OJB BETWEEN '2007-01-09' AND '2007-01-22' and CSG_Hist.dbo.Parser_OCR.prin_ocr = 8600 GROUP BYCSG_Hist.dbo.OJB_JOBS.IR_TECH_OJB, substring(CSG_Hist.dbo.OJB_JOBS.COMPL_CDE_OJB, pstn.p, 3), Parser.dbo.Parser_OJB.COMPL_DTE_OJB, Parser.dbo.ELP_Codes.CodeDes, CSG_Hist.dbo.SBB_BASE.RES_NAME_SBB, CSG_Hist.dbo.HSE_BASE.ADDR1_HSE, Parser.dbo.Parser_OJB.JOB_TYP_OJB, Parser.dbo.Parser_OJB.JOB_CLASS_OJB, CSG_Hist.dbo.OCR_ORDER_COMP.ORDER_NO_OCR, CSG_Hist.dbo.OCR_ORDER_COMP.LS_CHG_OP_ID_OCR, Parser.dbo.ELP_Codes.CommissionAMT
helo all..., this is my store procedure. but it can not display message. my friend said it must use output code. can someone add output code to my store procedure, so it can display message?ALTER PROCEDURE [bank].[dbo].[pay]( @no_bill INT, @no_order int, @totalcost money, @message varchar(100) -- make it output parameter in your stored procedure)ASBEGIN TRANSACTION DECLARE @balance AS moneyselect @balance = balancefrom bank.dbo.billwhere no_bill = @no_billselect @totalcost = totalcostfrom games.dbo.totalcostwhere no_order = @no_orderif (@balance > @totalcost)beginset @balance = @balance - @totalcostUPDATE bank.dbo.bill SET [balance] = @balance WHERE [no_bill] = @no_bill-- set @message = 'your have enough balance'endelsebeginset @message = 'sorry, your balance not enough'endCOMMIT TRANSACTIONset nocount off pls.., thx
I have a screen set up in another software package which accepts 2 values on a form -
'Number1' and 'Number2'
I need to check that at least a value is entered in one or both ... If both values are 0 (set on dispaly of the form )then I need to throw an error message to that effect on screen ....
(The Sql script is triggered after the form data has been entered)
Can anyone help me with the SQL script please to check the values ???
I have a report created in Business Intelligence Project.I want to trigger my report result. If it has no returned value, it creates error message displayed on the screen. How could I do this? Any guidance please.
I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.
I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...
"A new member could not be added to a local group because the member has the wrong account type"
I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.
I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...
"A new member could not be added to a local group because the member has the wrong account type"
I am duplicating a record from my asp.net page in to the database. When i click on save I am getting the following error message Violation of PRIMARY KEY constraint 'PK_clientinfo'. Cannot insert duplicate key in object 'clientinfo'. The statement has been terminated. The above message i am getting since i have tried to duplicate the clientname field of my table which is set as the primary key. What i want is instead of this message in the browser i need to display an alert saying "the clientname entered already exists" by checking the value from the database. Here is my code. pls modify it to achieve the said problem if(Page.IsValid==true) { conn.Open(); SqlCommand cmd = new SqlCommand("insert into clientinfo (client_name, address) values ('"+txtclientname.Text+"', '"+txtaddress.Text+"')", conn); cmd.ExecuteNonQuery(); conn.Close(); BindData(); txtclear(); System.Web.HttpContext.Current.Response.Write("<script>alert('New Record Added!');</script>"); Response.Redirect("Clientinfo.aspx"); }
Hello. I am trying to write a report that pulls information in from Active Directory. I have a view created that gets a listing of users and a view that creates a listing of user groups, but I can't seem to figure out how to get all user groups that are associated with the users. This is what I have.
SELECT TOP 100 PERCENT * FROM OPENQUERY(ADSI, 'SELECT cn, groupMembershipSAM FROM ''LDAP://wmdomain.local'' WHERE objectcategory=''group''') Rowset_1
SELECT * FROM OPENQUERY(ADSI, 'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn FROM ''LDAP://wmdomain.local'' WHERE objectCategory = ''Person'' AND objectClass = ''user''') Rowset_1
Is it possible to display only the first few words of a block of text with a "Show More" link at the end, which when clicked would expand the block to show all content?
"Collapsed": Lorem ipsum dolor sit amet, consectetur adipiscing elit, <...show more>
"Expanded": Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum. <... show less>
If I skip those objects that were deleted, is there a query that I can run off Master/MSDB that will show all database objects and when they were last modified, or create date if it has not been modified?
I have a requirement to display a disclaimer message before going to the content of a particular report. The workflow would be:
go to report folder e.g. Reports/Pages/Folder.aspx?ItemPath=%2fMyFolder%2fReports&ViewMode=ListClick on Report xDisplay DisclaimerPress 'ok' on disclaimerContinue to report
This is SSRS 2012 native.
I had contemplated injecting JavaScript into the report, but this appears virtually impossible to do i a simple manner.
I have a column in the following select startement called displayvalue. Displayvalue is a varchar column. Primary everything entered into this column is numeric, but there are times when NR is entered. In the select query if it sees NR, I would like to have NR changed in the resultset to NULL or blank. NR doesn't come up all of the time, but I have not idea how to do this in the select statment.
Here goes..
SELECT SAMPLE.SAMPLEID, SAMPLE.U_WORKORDERNUMBER, SAMPLE.U_SAMPLEDATETIME, SDI.PARAMID, SDI.DISPLAYVALUE FROM SAMPLE SAMPLE, SDIDATAITEM SDI WHERE ( (SAMPLE.SAMPLEID = SDI.KEYID1) AND (SAMPLE.U_WORKORDERNUMBER = '0060') AND (SAMPLE.U_SAMPLEDATETIME > '31-DEC-2003') AND (SDI.PARAMID = 'BOD') ) ORDER BY SAMPLE.SAMPLEID ASC, SDI.PARAMID ASC
Is it possile to definea custom format for the 'display format' property of a measure so that the results are shown in Thousands or Millions or Billions e.g
100,000 can be shown either as 100T OR 1,000,000 can beshown as 10M OR 10,000,000 1B
Sorry if I got my numbers wrong but I hope you get the idea?
