how can I query the plotted shift sched of employees for a particular day? like I would like to know what shift the employees were scheduled
from day1 to 15 of May 2008? the days that I would like to query would be dynamic so it can be day1 to 15 or day3 to day5 or just day20...
I am generating report with my datasource to an OLAP Cube. I have scenario that there are 2 dimension tables pointing to a single fact table. According to a user input, i have to use one dimension table and not the other and vice versa. I tried using IIF statement in the MDX query designer., but was facing errors.
First of all i want to know if this is possible and if yes, how?
Also , Is it possible to open a window form on clicking any report data
(Similar to assigning hyperlink, but i want to open a window form instead!!!)
I have a question regarding how to use report prompts in SSRS reports that are based on SQL queries. When I added the prompt into the query for use as a filter value, it says that there is an error. It does not recognise the '!' inside the parameter prompt string. Example is parameter!month_prompt.Value, which the ! is not recognised.
I am a newbie to SQL Server. I have a problem, in filtering the records returned by a query. I have a table which contains 1 million records, it has a user defined primary key which is of character type. The problem is i need to filter the output of a select query on the table based on two parameters i send to that query. The first parameter will be the starting row number and the second one is the ending row number. I need a procedure to do this.
For Eg: MyProc_GetRowsFromBigTable(startRowNo,endRowNo) should get me only the rows in the specified range.
Does anyone know if this is possible right out of the box in SSRS 2005 against an OLAP data source?
I have several parameters. My second parameter is to be filtered based on the first parameter (kinda like cascading), but how do I do this against an OLAP data source? Lets say I have param1 and param2 in a dataset. I want Param2 to show the locations only based on what I select in Param1. Same but a little different: I have Parameter1 and then my second parameter (Param2) is a boolean (True/False). I want to show Parameter 3/Paramater 4 based on selection of Param2 (So, if true, show Param3, if false, show Param 4) and remember we are doing this in a sequence. Can you do this thru SSRS? Any help would be great. Thanks for your time in advance. Kent
Is it possible to fill a parameter list with values based on another parameter value? Here's what I have so far (which hasn't worked)... I'd like to generate a report listing information for a student. The report viewer would first select a school from the first drop-down menu, and then the second drop-down menu would populate with the list of students at that school. I have a dataset that calls a sp which returns a list of schools (SchoolID and SchoolName fields from the database table). I have another dataset that calls a sp (with SchoolID as the parameter) which returns a list of students for that school. Both datasets return the appropriate data when tested individually, but when I set up the Report Parameters and build the report, these errors come up... The value expression for the query parameter '@SchoolID' refers to a non-existing report parameter 'SchoolID'. The report parameter 'Student' has a DefaultValue or a ValidValue that depends on the report parameter "SchoolID". Forward dependencies are not valid. ...Is it possible for the reoprt to generate a list of available parameter values based on the value selected for another parameter? Any help you can give me would be great!! Thank you
I am using reporting services 2012, Can we make visibility of report parameter dynamic, ie can we make parameter visible or hide on certain condition or its visibility depends on other parameters Is this feature available in any other updated version of ssrs?
I would like to be able to adjust the multi-value property of a parameter based on the value of another parameter in my report. The controlling paramter would be binary with two options for Single or Multiple selection. I would like my parameter to default to multi-value, which I can do on the screen selection. I have tried to add an IIF statement to the XML code, with no success. Any ideas would be helpful.
I have a report parameter called para1 which is a drop-down list and what I want to do is display another report parameter based on the para1 selection.
So for example, para1 contains a, b, c choices. if a user selects b, I would like para2 to display but if the user selects a or c, I don't want the para2 to display.
I have this semi-complex query that is selecting items from numerous tables residing on 2 different databases. So far the query works perfectly. Here is the problem: The user is given the option of selecting items based on whether a course is Completed (C) Incomplete (I) Passed (P) Failed (F) or (ALL). I am not really sure how to do the select all, the others I can do depending on the value...
Any thoughts??
Query:
sql Code:
Original - sql Code
ALTER PROCEDURE [dbo].[Sel_CourseActivityPerUser] ( @status varchar(25), @course varchar(100), @datesmalldatetime ) AS
I am creating a report that will act as statements for our companies, I am slightly stuck at the moment, I have two required parameters one is the company prefix and the other is the customer code.
I have a image at the top of the statement that I want to be dynamic i.e. when there is a company code of 1 I want one particular image and when there is a company code of 2 I want another.
Is this possible? If so are there any instructions on how one might set this up
After upsizing an access mdb backend to SQL Server 2005, some of the access frontend queries need rewriting.
One of my Access queries contains several joined tables (tables that used to live on users local c drive) and also references the value of a form control as its where criteria. When this is run against linked sql tables it is very slow.
So I thought I would use a view to represent the joined tables and link to that in Access, but views don't allow you to pass parameters. How do I create a view that I can link to in my access front end whose contents will vary based on a passed parameter from Access? I am OK at vba coding so I can call to this object in code if necessary. The recordsource needs to be updateable.
I am getting runtime error for the below simple execution of sp even I tried casting the @dt to varchar still getting the same error. I want to delete the table based on the 2 parameter ...
1. Table Name first parameter 2. InCondition is the column name which of type datetime
CREATE PROCEDURE dbo.[DeleteTable](@InTblName NVARCHAR(250),@InCondition NVARCHAR(250)) AS BEGIN DECLARE @DeleteSQL NVARCHAR(250) DECLARE @Dt DATETIME SET @Dt = GETDATE() SELECT @DeleteSQL = N'DELETE FROM ' + @InTblName +' WHERE '+@InCondition+ '=''' + @Dt+ '''' SELECT(@DeleteSQL) EXECUTE sp_executesql @DeleteSQL END
I have corrected the code now, I am not getting the output it is throwing error at run time.
EXEC [DeleteTable] 'TABLE_NAME','COLUMN_NAME'
Where column_name is of datatype datetime
Msg 241, Level 16, State 1, Procedure spDeleteTable, Line 8 Conversion failed when converting date and/or time from character string.
Hi, I have a matrix report containing a parameter.The count of groups may change based on the parameter's value.Also these groups are connected to each other with toggle property. For example; --If the parameter's value is "year", the report have three (year, month, day) groups.Day is connected to month and month is connected to year with toggle property. --If the parameter's value is "month", the report have two (month, day) groups.Day is connected to month with toggle property. --If the parameter's value is "day", the report have one (day) group.There will be no toggle property.
The question is: I wrote expressions for this report and set the visibility properties of the groups, but when I select the "day" or "month" value for the parameter, no groups are shown.I think the reason is "being connected to each other with toggle property".
I have a report that has 2 report parameters. The first paramter is a string type that allows null. It is supposed to allow the user to enter a memberId or just select all members (if the NULL is checked). The second report parameter is a multi valued selection that contains a list of country codes populated from a query. What I like to have is when the user decides to enter a memberid (uncheck the NULL) to have the country codes report parameters disabled. Is there a way to do that? Thanks, Elie
I have bunch of reports that take same set of parameters. I am trying parametrize the report type so that depending on the report type selected, body should display that report when user hits "View report" button. How can I do this? Pardon me if there is an obvious solution as I am pretty new to the joys of MS Reporting Services.
I would need to add a monthly based parameter to my reports. In my actual scenario i have 2 datetime parameters : "start date" - "end date", that denotes the time interval in days. Is there any standard way to customize these parameters to set an interval between months? In other words, i would need 2 parameters like these : "start month" (eg. august 2005) - "end month" (eg. march 2006), with the report filtering data among this given interval of months.
I would like some help conceptualizing how to acomplish this task. My client has requested the ASP.NET web/SQL Server application send an email to one of our locations based on whether that location complied (via the web app) to each week's report. Here's how the flow would go: The location checks the webpage weekly and selects their compliance to each record on the report. (They could overlook one of the records and not make a selection.) The SQL db would then query the compliance table and create a report of those locations with at least one "non-compliance." Then the Db would send a reminder email to each location on that report. The Db tables have the email address info assigned to each location, so I can join that to the compliance log table. Can this be done? I am using SQL Server Management Studio Express and Visual Web Developer (VB.net). Thank you for your ideas,SMc
I am on the verge of being able to do exactly what I want, but just can't seem to find the right combination of things to do it. I'm sure all of you wonderful folks will be able to point it out to me immediately, but I've been looking at it too long or something....
I have a record of individual sales with the state, and quarter of the sale.
sale_id state quarter 001 NY 2005Q1 003 WI 2006Q2 etc.
I create a report with a matrix to show count(sale_id) with Quarter as the column group and State as the row group. This works fine.
Now what I want to do is to get percentages based on quarterly sales. In other words, what percent of sales for 2005Q1 in NY vs. all sales in 2005Q1. So I create a second dataset (called total) with an SQL query like so:
SELECT count(sale_id) FROM data_table WHERE quarter = @QueryQuarter
Now, back in the matrix I want to use the column that we're in (2005Q1, 2005Q2, etc.) as the value that is passed to this query.
This is a simple concept, but I can't seem to figure out the correct call to pass the column group to the query as the parameter.
Thank you for any pointers you might be able to give. As I said, I'm right on the verge and just can't quite get it.
Im trying to create a drop down parameter whereby if i select a certain field, a different dropdown will be filtered off only the relevant selections, is this possible.
Hi everybody, Is there a way to set SelectParameter for SQLDataSource in ASPX file using System.Configuration.ConfigurationManager.AppSettings["SiteID"]) ? Thanks a lot in advance.
I need to write SP where user select SUN to MON check boxes. If user select Class A with sun,mon and wed check boxes then i need to insert data as below
I am using SQL 2005. I have a SELECT query in a stored proc with 3 parameters: @subaccount,@numDaysCutoff,@numDaysPcts. The proc needs to be modified to return data when subaccount values are any of these:
FRRIJ FRRIC FRMM ROBECO FRJV MAIL FRUKV FRICE
Currently I use a WHERE condition and am able to get data correctly. However, for a NULL value I should get everything including those not in the above list. Should I use CASE statement instead? How?
@subaccount VARCHAR(8) = NULL , @numDaysCutoff INT = 1 , @numDaysPcts INT = 1
SELECT Subaccount = ISNULL(h.subaccount, lo.subaccount) , SecurityID = ISNULL(h.security_id, lo.security_id) , SecurityName = s.name , QtyHeldAndPending = ISNULL(h.quantity, 0) + (CASE WHEN lo.type = 1 THEN lo.resulting_quantity * (-1) WHEN lo.type = 2 THEN lo.resulting_quantity ELSE 0 END ) , L.AverageDailyVolume , XDaysVol = L.AverageDailyVolume * @numDaysPcts , CutoffVol = L.AverageDailyVolume * @numDaysCutoff , DaysVolHeld = h.quantity / NULLIF(L.AverageDailyVolume, 0) , HeldPctNDaysVol = h.quantity / NULLIF((L.AverageDailyVolume * @numDaysPcts), 0) * 100 , TargetedHoldingsUSD = tm.ApprovedPortfolioTarget * iv.value_usd , CutoffVolUSD = L.AverageDailyVolume * @numDaysCutoff * s.price_usd , TargetedPctNDaysVol = (tm.ApprovedPortfolioTarget * iv.value_usd) / NULLIF((L.AverageDailyVolume * @numDaysPcts * s.price_usd), 0) * 100 , DaysVolTargeted = (tm.ApprovedPortfolioTarget * iv.value_usd) / NULLIF((L.AverageDailyVolume * s.price_usd), 0) , NDaysCutoff = @numDaysCutoff , NDaysPcts = @numDaysPcts FROM subaccount_positions_table h --vGlobalHoldings h JOIN iv_subaccount_table iv ON iv.subaccount = h.subaccount FULL OUTER JOIN LiveOrders lo ON lo.subaccount = h.subaccount AND lo.security_id = h.security_id FULL OUTER JOIN TM_DerivedSecurityTargetDetail tm ON tm.Subaccount = h.subaccount AND tm.SecurityID = h.security_id LEFT JOIN dbo.security_table s ON s.security_id = COALESCE(h.security_id, lo.security_id) LEFT JOIN dbo.SecurityLiquidity L ON L.SecurityID = h.security_id AND SourceID = 99 WHERE (h.subaccount = ISNULL(@subaccount, h.subaccount) OR lo.subaccount = ISNULL(@subaccount, h.subaccount) ) AND status = 1 AND ( h.quantity > (L.AverageDailyVolume * @numDaysCutoff) -- qtyHeld > XDaysVol OR -- Targeted Vol exceeds cutoff ISNULL((tm.ApprovedPortfolioTarget * iv.value_usd), 0) > ISNULL((L.AverageDailyVolume * @numDaysCutoff * s.price_usd), 0) -- Target > XDaysVol ) ORDER BY ISNULL(h.subaccount, lo.subaccount), ISNULL(h.security_id, lo.security_id)
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 have a subreport added to the main report and I want to make this report visible only when the parameter value is met.
Ex, I have a parameter CustName in the main report and want to show the subreport when the custName = xxxxx. There reports are parameter driven not data driven reports.
I have a main report and a subreport. Subreport is not at row level but is at report level. The sub report has a parameter that needs to be passed from mainreport. However, the subreport's parameter should be set and the subreport should be refreshed when the user clicks on a row in mainreport. Can I use drill-through / navigation feature in any way to build this functionality?
I'd like to hide/show a table based on selected parameters. In the current setup I have a matrix and two tables underneath that need to be displayed from time to time. First of I use a multi-value parameter called "Lieferart". Depending on the parameter I have set the visibility of the two tables with the following expression:
=iif(Parameters!lieferart.Value(0) like "Nagel%", False, True)
This expression doesn't work though, any ideas??? I am also unsure about what I have to do If multiple values from the parameter list are selected as Parameters!lieferart.value(0) doesn't necessarily need to have a label like "Nagel" included...
