I've a requirement per below business rule to change the Scheduled Delivery Date on the report. Below is the DDL:
1) If the Job has a batch number, the Scheduled Ship Date will be next monday to Requested Delivery Date. Say for example if the job has Requested Delivery Date as 2015-07-29 and it also has a batch number then Scheduled Ship Date will be 2015-08-03.
2) If the Job does not have a batch number then the Scheduled Ship Date will be the Monday before the REquested Delivery Date. Say for example if the Job J012347 has Requested Delivery date as 2015-08-04 and it does not have batch number then the Scheduled
Ship Date will be the Monday before i.e. 2015-08-04.
Similarly if the Requested Delivery date is 2015-08-07 and it does not have a batch number then Scheduled Delivery Date will be 2015-08-03.
DECLARE @Date datetime;
SET @Date = GETDATE();
DECLARE @TEST_DATA TABLE
(
DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,JobNumberVARCHAR(10) NOT NULL
,JobStatus CHAR(1) NOT NULL
I have a report that uses several filters including a from and to date filter, I have a field named TimeLastMod which is a Timestamp. I am extracting the From and To dates from this field using Select format(TimeLastMod,'MMM') as FromMonth,format(TimeLastMod,'MMM') as ToMonth From Eventlogs syntax.
These I pass on to a parameter and eventually to a tablix filter in an between opeartion. When I run the report, the records returned by the report is not what is expected as it sometimes returns an extra months data e.g., using between Aug and Sept will return october data as well.
Here I am trying to do switch then nested IIF in it. I dont know where i missed but this doesnt work.for first condition in switch, 'WEEKLY', WW2 and WW1 is in number form but of text/string datatype. so after cast them to integer/number datatype then those are use in iff operation.for second condition in switch, 'MONTHLY', WW2 and WW1 is month name. so the objective is to get month number from month name. After that, then WW1,WW2 are use in iff operation.
Have a nvarchar column in ms sql that, based on user input, may or may not have a carriage return and a line break together (CHR(13)&CHR(10)), just a carriage return (CHR(13)), just a line break (CHR(10)). In using that column in ssrs, I need to find if any of the above exist and replace them with the string "x0Dx0A", "x0D", or "x0A" respectively.
System.Web.Services.Protocols.SoapException: The Value expression for the textrun ‘Info.Paragraphs[0].TextRuns[0]’ contains an error: [BC30455] Argument not specified for parameter 'Replacement' of 'Public Function Replace(Expression As String, Find As String,
CREATE FUNCTION [dbo].[get_date_only] (@date datetime) RETURNS datetime AS BEGIN RETURN dateadd(day, DateDiff(day, 0, GetDate()), 0) END
CREATE FUNCTION [dbo].[get_month_end] (@date datetime) RETURNS datetime AS BEGIN RETURN dateadd(ms, -3, dateadd (m,datediff(m,0, dateadd(m,1,@date)),0)) END
CREATE FUNCTION [dbo].[get_month_start] (@date datetime) RETURNS datetime AS BEGIN RETURN dateadd(m,datediff(m,0, @date),0) END
CREATE FUNCTION [dbo].[get_today_end] (@today datetime) RETURNS datetime AS BEGIN return dateadd(ms, -3, datediff(d,0,dateadd(d,1,@today))) END
CREATE FUNCTION [dbo].[get_today_noon](@date datetime) RETURNS datetime BEGIN RETURN DATEADD(hh, 12, DATEADD(d,DATEDIFF(d,0, @date),0)) END
CREATE FUNCTION [dbo].[get_today_start] (@today datetime) RETURNS datetime AS BEGIN return dateadd(day, 0, datediff(d,0,@today)) END
CREATE FUNCTION [dbo].[get_tomorrow_noon](@date datetime) RETURNS datetime BEGIN RETURN DATEADD(hh, 12, DATEADD(d,DATEDIFF(d,-1, @date),0)) END
CREATE FUNCTION [dbo].[get_week_end] (@date datetime) RETURNS datetime AS BEGIN return dateadd(yyyy, datepart(yyyy, dateadd(weekday,7-datepart(weekday, @date),@date))-1900, 0) + dateadd(ms, -3, dateadd(dy, datepart(dy, dateadd(weekday,7-datepart(weekday, @date),@date)),0) ) END
CREATE FUNCTION [dbo].[get_week_start] (@date datetime) RETURNS datetime AS BEGIN return dateadd(yyyy, datepart(yyyy, dateadd(weekday,1-datepart(weekday, @date),@date))-1900, 0) + dateadd(dy, datepart(dy, dateadd(weekday,1-datepart(weekday, @date),@date))-1,0) END
CREATE FUNCTION [dbo].[get_weekday_end] (@weekday tinyint, @date datetime) RETURNS datetime AS BEGIN return dateadd(yyyy, datepart(yyyy, dateadd(weekday,@weekday- datepart(weekday, @date),@date))-1900, 0) + dateadd(ms, -3, dateadd(dy, datepart(dy, dateadd(weekday,@weekday-datepart(weekday, @date), @date)),0) ) END
CREATE FUNCTION [dbo].[get_weekday_start] (@weekday tinyint, @date datetime) RETURNS datetime AS BEGIN return dateadd(yyyy, datepart(yyyy, dateadd(weekday,@weekday- datepart(weekday, @date),@date))-1900, 0) + dateadd(dy, datepart(dy, dateadd(weekday,@weekday-datepart(weekday, @date), @date))-1,0) END
CREATE FUNCTION [dbo].[get_year_start] (@date datetime) RETURNS datetime AS BEGIN RETURN DATEADD(year,DATEDIFF(year,0, @date),0) END
CREATE FUNCTION [dbo].[get_yesterday_end] (@today datetime) RETURNS datetime AS BEGIN return dateadd(ms, -3, datediff(d,0,@today)) END
CREATE FUNCTION [dbo].[get_yesterday_start] (@today datetime) RETURNS datetime AS BEGIN RETURN dateadd(day, -1, datediff(d,0,@today)) END
Then create a Table-Valued Function like so:
CREATE FUNCTION [dbo].[udfCommonDates] (@date datetime) RETURNS @t table (week_start datetime, week_end datetime, lastweek_start datetime, lastweek_end datetime, month_start datetime, month_end datetime, lastmonth_start datetime, lastmonth_end datetime, yesterday_start datetime, yesterday_end datetime, today_start datetime, today_end datetime, thisweek_monday_start datetime, thisweek_monday_end datetime, year_start datetime, year_end datetime, tomorrow_noon datetime, today_noon datetime, date_only datetime) BEGIN INSERT @t SELECT dbo.get_week_start ( @date ) AS week_start, dbo.get_week_end ( @date ) AS week_end, dbo.get_week_start ( DATEADD(d, -7, @date ) ) AS lastweek_start, dbo.get_week_end ( DATEADD(d, -7, @date ) ) AS lastweek_end, dbo.get_month_start( @date ) AS month_start, dbo.get_month_end ( @date ) AS month_end, dbo.get_month_start ( DATEADD(m,-1, @date) ) AS lastmonth_start, dbo.get_month_end ( DATEADD(m,-1,@date) ) AS lastmonth_end, dbo.get_yesterday_start ( @date ) AS yesterday_start, dbo.get_yesterday_end ( @date ) AS yesterday_end, dbo.get_today_start (@date) AS today_start, dbo.get_today_end ( @date ) AS today_end, dbo.get_weekday_start(1,@date) AS thisweek_monday_start, dbo.get_weekday_end(1,@date) AS thisweek_monday_end, dbo.get_year_start(@date) AS year_start, dbo.get_year_end(@date) AS year_end, dbo.get_tomorrow_noon(@date) AS TomorrowNoon, dbo.get_today_noon(@date) AS TodayNoon, dbo.get_date_only(@date) AS DateOnly RETURN END
Now the RS folks might be thinking but how does this help me as I need a dataset and a dataset can only be based on a Stored Procedure or a direct table. No problem create the following stored procedure:
CREATE PROCEDURE [dbo].[uspCommonDates] AS begin set datefirst 1 declare @date datetime set @date = getdate() select * from dbo.udfCommonDates(@date) end
Now you've got a stored procedure to use as a dataset...Now in reporting services add a new dataset:
Now go to the report parameters section of the report:
Now pick that dataset dsFunctions (or whatever you called it) and then pick any of the value fields from the scalar functions such as:
On my SSRS report, I use a date parameter to let the user select a date with the little calendar tool. When a date is selected, I have a small bit of coding run to convert the date to text, because when the report first fires up, the field is populated with "1/1/1900," and I need that turned into a blank character to let the report pull up all rows. So far, all well and good.If someone picks a date, then the report will filter the data on that selected date. Works fine.
But, it appears there is no way to get the calendar tool to go back to allowing ALL dates - so that all records are pulled - except by manually typing in, or selecting it with the tool, 1/1/1900.If I try to clear the field, causing it to use '' as a WHERE criteria (WHERE AdmitDate LIKE '%' + @AdmitDateTxt + '%'), it repopulates the field with the last selected date. So, I guess the question is, how does one tell the calendar tool for picking dates for a date parameter to reset back such that all records are pulled, not just those for a single date, without being required to type in, 1/1/1900? Or, is there some way of telling the date parameter to select all dates?
I have a table in which records are inserted daily and with them i am storing the dates also. Now in SSRS i need to show the data for one week . The format should be like :
In above example Business Name , Phase, Activity will repeat lonely but its work description will be generated in next section according to that business name and that date. How to achieve this task ? I have referred : [URL] ....
I have a requirement to display project start date and finish date in timescale view. The data in database table available as per below -The start date and project finish date should fill color in the timeline view as per below example, by quarters for a current financial year, last year and plus two years a head.
I just created a report builder. I have a main report and i wanted to create a sub report. why i cant or i cant view the path or the folder of my .rdl file to be use as my sub report.
Code : protected void Page_Load(object sender, EventArgs e) { Session["ID"] = "2"; string strConnString = ConfigurationManager.ConnectionStrings["BSDConnectionString"].ConnectionString; var con = new SqlConnection(strConnString); using (var sda = new SqlDataAdapter())
[Code] ....
That was my code , now lets see what my problem is :
I am getting only two dates in a single row from sql to my asp.net webform and then bindng those dates in jQuery UI Datepicker. Now if i set session to 1 then date picker works well but if i put session = 2 , it shows the end date till 2020 which is wrong.
Below are the dates which are fetched from database and i have copied here for your ease.
When Session["ID"] = "1"; Start Date : 01/07/2014 00:00:00 End Date : 05/02/2015 00:00:00
When Session["ID"] = "2";
Start Date : 07/04/2015 00:00:00 End Date : 27/08/2016 00:00:00
I have set my mindate to startdate and maxdate to end date. please check and see where the error is happening.
Also point of interest is that if i don't fetch values from database and use only List<string> in my web method then every thing works well. like this :
[WebMethod] public static List<string> GetDates() { List<string> arr = new List<string>(); arr.Add("2014-07-01"); arr.Add("2015-02-05"); return arr; }
Our DBA has installed reporting services on a server and now in order to access the report manager, one has to be an Admin on that Server. I am guessing that there is a mistake in the configuration of Reporting Services. Usually it should allow anybody who was added to the roles in the properties section of the Report Manager, right? I have also added the users to the DB..
Also I am using Windows Authentication to access Report Catalog items (Reporting Services is installed on Server2) from a web Application(deployed on Server1) and displaying the report using report viewer. For some reason, server1 has to be in an Admin role on Server2 to access the report catalog/report. This is kinda strange for me as I don't want everybody to be an Admin on Server2. Can anybody please point in the right direction?
I have created range bar chart and I am not able to achieve the following tasks.
1. Change X-axis Label Format to Quarter:
I have x-axis with dates and y axis of project groups. I have changed x-axis interval type = month and interval=3.
Set the Maximum = Max(ProjectEndDate) and Minimum = Min(ProjectStartDate).
Now my chart showing 3 months x-axis interval dates in mm/dd/yyyy format. I want to change this interval date format to Quarter. The problem is LabelsFormat property is not recognize the "=Q or q or quarter" and also not accepting the expressions. How can I achieve this?
2. Placing series side by side when it is not overlapping
I want to place the same group series side by side only when the previous project end date is less than next project start date, otherwise place the next project to next row. How can I achieve this?
I have done the following and a domain user would not access report created a login to the SQL server to the user (this SQL Server is where data source DB is)went to site setting in Report Manager and made this use a system userright clicked on report folder and made this user in the browser roleeven checked that in the report in question, the user is already in the browser role Still the user would not access the report! "User .......... does not have required permission" is the error message I am getting.
I have written a report visual studio. The report has 10 multi-value parameters that pull data from their individual data sets within the reports. When running the report from within Visual Studio it renders fine. There are no errors reported, only a warning related to a pathname I have use to retrieve image data and display on the report.Deployment of the report is error free.When I view the report I briefly get the "Loading" splash window, but then nothing. None of the headers or static text is displayed. The results window is blank.
The report defaults all the parameter values. In trying to debug the issue I have found by reducing the number of parameter values the report will render. Once I have all the values added, the report does nothing.Below is the query being used for the report. The where clause in the query shows the parameters being used and the syntax.We are running on Windows Server 2008 R2 and SQL Server 2008 R2.
Ralph SELECT RTRIM(a.ITEMNMBR) AS Style, RTRIM(a.ITEMDESC) AS Description, a.ITMSHNAM AS UPC, c.LOCNCODE AS Store, d.LISTPRCE AS ListPrice, a.CURRCOST AS Cost, a.USCATVLS_1 AS [Main Category], a.USCATVLS_2 AS Market, a.USCATVLS_3 AS [Alternate Retail], a.USCATVLS_4 AS Country, b.ITEMXTRAS_1_Type AS Type, b.ITEMXTRAS_2_Finish AS Finish, b.ITEMXTRAS_3_SubCategory AS SubCategory, b.ITEMXTRAS_4_Department AS Department,
We are about to implement SSRS reports (SQL Server 2012), and have found one issue we don't like. The report looks great when we open it in pdf format. However, when we open it in Excel format, we want it to be the raw data, not look like the pdf report. I tried the simpl headers, and that removed the page header/footer. However, the report format was still in play.In other words, we would like the report when opened in Excel, to look just like it does when we open it as a CSV.I have tried to modify the config file a couple of times. Here is my latest attempt:
Essentially I have set Visible = "false" for the Excel type, and tried to duplicate the CSV, changing the name to make it look to the user like it is "Excel". This fails. I checked the event viewer, and I see two entries.. The RSReportServer.config file has been modified. (Information)The value for UrlRoot in RSReportServer.config is not valid. The default value will be used instead.I literally took the file, opened it in Notepad, pasted in my changes, from another notepad window, and tried to run the report. I did not see EITHER of the two names I expected, using the OverrideNames tag. As I said earlier, I was able to affect the outcome, by setting SimpleHeaders to true (or however it is identified). That did work in that the page header and footer was removed.
I have a ssrs report having 2 tables in with 4 columns in each. When I go to export option in preview I can see all data coming in one excel sheet, But I am trying to get 2 tables in 2 different pages in Excel when I export.First page of excel comes with first table data with 4 columns and second page of excel comes with second table data with 4 columns .
I am working on reports in SSRS 2008 (not R2)... There are some reports with parameters that are hidden when the report is accessed through normal URL using ReportViewer.asx..The thing is that these hidden parameters need to be visible when the report is accessed using SSRS Report Manager.
I'm wondering how to print a SSRS report without bringing up a report viewer. Is there way to print(PDF format) the SSRS report in the web application( .net ) directly from the "print" button without bringing up a report viewer?
I have a report builder drill down report. I have row groups with totals. It looks like the attached. The problem is when the report is not expanded the Grand Totals column is not accurate... it is displaying the totals of one of the rows when expanded.The expression in the Total Show text box is
I have few reports under different folders in Report Manager. Is there a way to set the data source for the whole folder rather than each report individually? There is also a folder where reports generate dynamically. Since the report project doesn't exist anymore, I can't set the data source through code. How can I set the data source for the whole folder?
I was able to log in to SSRS report manager. But i was unable to run a report i designed on report builder.
It prompts for login when i wanted to run the report and i used the same windows authentication to sign in
It will show me the parameters but when i click on view report it gives this error
"Data source 'DataSource1': An error has occurred.
Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'DataSource1'. ---> System.Data.SqlClient.SqlException: Login failed for user 'Acc-11Admin'."
I want to repeat line on each page of report on PDF while using 2 columns on report. The problem is that if i use table border it will also repeat after second table. i want a line between two tables.
We are generating excel report using report viewer at run time but if excel report having more then 65000 record generating error Microsoft. Reporting Services.OnDemandReportRendering.ReportRenderingException: Excel Rendering Extension: Number of rows exceed.
I am trying to develop a report from cube,and it has one drill through report.when i click on sales amount field on main report,then it open the drill through report with details.Here i passed the **(category,subcat,product)parameters(Cascaded)** to drill through report in action part of main report.In my main report i have two columns .
when i click on 100, parameters are passed & it open the detail report correctly, but when i click on 50,the values for subcat parameter is not getting values in detail report and same as the product also.
How do I get data on my linked report based on my grouped subtotal and grand total from the main report. The subtotal and grand total are calculated columns.
I have a 3 columns in my matrix in the SSRS summary report. Actn_COAST, ActnCITY and NumbOfAccts.
The following is code for my summary report. The results are shown below.
SELECT Distinct ActnCITY, Count(ACCT) as NumbOfAccts, CASE WHEN ActnCITY in ('NY', 'OH', IN, 'NJ', 'SC', 'NC') THEN 'EAST COAST' WHEN ActnCITY IN ('CA'. 'NV', 'UT', 'WA', 'OR') THEN 'WEST COAST' ELSE 'OTHER' END AS Actn_COAST
FROM tbl1 where ACTNDATE between @STARTDT and @EndDT
Code for my detail report contains the following SQL
SELECT * FROM tbl1 where ACTNDATE between @STARTDT and @EndDT AND @ActnCITY = ActnCITY
I have linked my report based on the NumbOfAccts column. I am able to get data if I click any of the NumbOfAccts values related to the state I want. However when I am not sure how to make the subtotal and grand total work. I want when I click on the subtotal of either coast, I should be able to see records of that coast e.g., if I select 37 I should be able to see all the records in East Coast. If I click on the Grand Total, I only want data related to those 2 coasts.
Is there a way to subscribe SSRS report using dynamic parameters for email and trigger the report from autosys job so that report should generate the exact time the job is triggered.Let me describe, my SSRS report should be triggered on success of one autosys job. i need to send email parameter and time of report schedule from this autosys job.
Hi, I'm having problems navigating from one report to another one if the second report has a multi-valued report parameter. When I navigate to the second report, I don't pass any parameters, but I get an error "parameter is missing a value" for the multi-valued report parameter. I have it setup as allow "multi-value" and "blank value". Any idea what the issue might be? Thanks.
How to display the logo in middle of report header , The logo is embedded in the Image folder . The column of the report are static. we are using SSRS 2008
I have a SSRS report with 4 sub reports embedded. Previously i faced a situation where sub reports was getting displayed with static data even if there is no data being pulled from data set. I solved this problem according to solution provided in my previous post here. Now blank space is getting displayed in place of empty sub report. But still the main report that hosts empty sub report (Which has a header with header name coming from main data set) is getting displayed. Is there any way wherein the whole main report can be suppressed when the sub report has no data?
