Hi, I need help.
I have 4 filters/parameters on top of the report but it looks very untidy.
Is there a way where i can set the boxes sizes & alignment etc.
I would like to make it look more nicer.
Regards,
I have an ordering database with several tables that store data of orders belonging to a wide variety of clients. There is a generic report that I need to run which outputs the same data elements. However the criteria to select these orders will vary widely between each client. For e.g.
i) for client# 1 it could be all orders that are still open after 30 days of placing an order
(
OrderStatus = 'Open'
AND
GetDate() - OrderCreationDate >= 30
)
ii) for client# 2 it could be all orders that have been completed 60 days or earlier
(
OrderStatus = 'Completed'
AND
GetDate() - OrderCompletedDate >= 60
)
iii) for client# 3 it could be a combination of different things (all orders in West Region that are in hold status for more than 10 days + all orders in Eastern Region that are in shipping and are expected to be delivered in the next 2 days + all completed orders for the rest of the regions).
(
OrderRegion = 'West'
AND
OrderStatus = 'Hold'
AND
GetDate() - OrderHoldDate >= 10
[code].....
I want to have a stored procedure that selects all data and dynamically attach the where condition at the end for filtering. This way I wouldn't have to worry about any additions/changes that are made to the selection criteria. I can build an interface for admins who can use the UI to maintain the selection criteria and not worry about any code changes to accommodate it. I would like to design a table that holds this criteria. At this point in time, I am thinking of using key value pairs (Column Name, Column Value) but I am not sure how to implement multiple logical operators.
Good Day, nicholas here! Can i know is there any way to change datetime format to 13:00:01(hh:mm:ss) in Microsoft SQL Server 2000 Enterprise Manager's table ... Please help me!
Many Thanks!
I have a report that I need to develop in landscape format. Also I need it to be landscape when converting to PDF and taking printouts. let me know the exact height and width measurements in inches for the report and report body?
I need the margin to be 0.5 inch.
I'm designing a database that stores personal details of students and teachers for a school. I, wish to know if a there is away of formatting the bit data type to male/female. Cause i am only able to achieve 0 and 1 in this data type. So is there any way to change it, or is there any other data type suitable to provide only two options Also the database should be protected in a way that students may not be able to view or change. Only teachers/admin have the right to access/modify the data.
View 6 Replies View RelatedIs Format Painter is available in SQL Server Management Studio Report Design?If not, any other way can we copy format?
View 4 Replies View RelatedWorking on a new database where the Date and Time are stored in a Date Time Field.
Then working on an OLDER database file within the same SQL Database contains these 2 items as integers:
transDate = "71615" (July 16, 2015)
transTime = "12345" (01:23:45 AM)
How do we convert both of them into a single SQL DateTime field such as "2015-07-16 01:23:45.000" so that it can be used in a join restricting to a date time in a different SQL File that properly has the DateTime in it?
This works well for converting the transDate Part in the select statement:
dbo.IntegerToDate(at.transDate) as transDate
* That returns: "2015-07-16 00:00:00.000"
* The resulting data must work directly in a Microsoft SQL Server Management Studio Query using either using the "on" statement or part of the "where" clause. In other words, NOT as a stored procedure!
Also must be able to be used as a date difference calculation when comparing the 2 files Within say + or - 5 seconds.
Hi all,
I'm using SSAS 2005 with Excel 2007. My data store is very poor at the moment and most of the tables for the dimensions get rebuilt every night. The current result is as follows;
User selects product "Issue May 2008" as a filter from the product dimension, saves and closes the report.
User opens the report after the data has refreshed, the data is different, the filter shows Issue May 2008 but when you click on it the item above it is displayed - this is because the IDs have changed in the source table.
Is there a way to make it so the text/description of the dimension is used rather than the ID?
In my head that explanation makes sense, but I can understand how it might not so please let me know if it doesn't
Regards,
Phil
I was wondering if it's possible to set filters per user. For example we have a client who has offices in 10 different states, we want an admin of an office to only be able to run reports on their state. Is this possible without creating 10 different models/views?
Thanks
Hello.
Is there a good article on the net for working with filters?
Also, I have a report with 10 parameters.
Each parameter get his default values from a dataset.
The problem is that the report ake a lot of time to load since it need to run all 10 datasets to load the parameters values.
Is there a way to speed things up?
Thanks.
Hi,
I am creating report using reporting service and datasource is SQL Server 2005 Cube.
I am using query builder and i am creating query parameters in the query builder but when i run the report is showing the filters but data is not filtering based on which i selected from the list.
How do i create filters?? Please help out on this.
Thanks
Hi.
Is it possible to create a report via SSRS and deilver it via the subscription service in an email such that when user opens their own report (filtered by their user id) they can then dynamically change filters (cbo boxes) a la an excel pivot table which will then refresh the data in the report for them?
I can't seem to find a way to do this. Drill-down is not what is wanted...they want to simply cheange selections in cbo boxes and have the data all refresh.
Thanks!
I have a report generated through a number of recursive CETs and would like to add a FILTER parameter for the user to select on. (Using Parameters in the query will not work because recursion cannot be limited at the outset by the parameter).
The values to be selectable are:
Sprint 1 - R 1.0
Sprint 2 - R 1.0
Sprint 3 - R 1.0
Sprint 4 - R 2.0
Sprint 5 - R 2.0
Sprint 6 - R 2.0
...etc
I want the report to display only those Releases (R n.0) which the user specifies, eg:
Release 2 would select all data with R 2.0 in the field.
Using Table properties-Filters for the field works IF I use "=", as:
Expression: Operator: Value: An....
=Fields!SprintName.Value = Sprint 4 - R 2.0 or
=Fields!SprintName.Value = Sprint 5 - R 2.0 or
=Fields!SprintName.Value = Sprint 6 - R 2.0
Issues:
1. I am unable to use Like (I could specify Like '%- R 2.0%' and have all Release 2.0 displayed), but it does not work with any of the syntax I have tried. Using only a single expression row, - R 2.0, '%- R 2.0%', "%- R 2.0%" all fail by displaying NO data. CAN Like be used? And what is the syntax?
2. I do not have any control over the And/Or column -- it is always greyed-out, and inserts "or" automatically if the Operation is "=", and "and" if the operation is "Like". How do I control this paramter?
3. And, most important, how do I get user input to the filter field, such that If I enter Release 2 from a drop down (?), I can display the appropirate data?
NB: VS2005 sp1/SQL2005 sp2
Assistance greatly appreciated....
Dear ppl,
I am using Merge Replication between SQL Server 2005 (Publisher) and Pocket PC (Subscriber). I have a .NET compact framework appliction on the Pocket PC that replicates data from the server.
I am using SqlCeReplication class on the Pocket PC application to synchronise the data. By default, when i call the Syncrhonise() method, it pulls all the data from the server. What I want is to pass a parameter from the Pocket PC and filter the data based on that paramter. E.g. from the Employee table, I want only those Employee that belongs to a CompanyID that I pass as a parameter.
Is there a way to do this, so that i can pass parameters from my PDA application (Windows Mobile), and make the Filters specified in the Publication to use that parameter to filter out the rows.
Regards
Nabeel Farid
Hi,
Is there a way to create a new mining model from an existing one but with a different filter? I can see that the SELECT INTO DMX statement perform the creation. Is there a syntax for DMX in SQL Server 2008 for setting the filter for the new model?
thanks,
Gustavo Frederico
Does anyone know if you can use any other parameters in the row filters for merge replication besides the functions SUSER_NAME() and HOST_NAME()?
I would like to create a publication for a couple thousand mobile databases to replicate with one SQL Database but filter what data they get based on some parameters. Do I have to hard code WHERE statements into static filters and create a publication for every user (seems a little ridiculous)?
Is there a proper way to do this using the SUSER_NAME and give each user a different connection name that will filter data properly?
Thanks,
Patrick Kafka
Greetings,
I have a fairly simple report that displays one table (table1). The table
has a filter that I created by opening the properties of the table and
entering
=Parameters!abs_comparison.Value
in the Value field of the Filters tab. The filter operates on the
=Fields!Absolute_Comparison.Value
field.
The report displays the row count from the dataset (=CountRows("mfrep_rqw"),
but this value doesn't change when the filter is applied. I'd also like to
display the number of filtered rows, but I can't figure out how to do it.
Any ideas?
Thanks,
Mike Hayes
Hi,
I have applied two filters for table in the SSRS report, one is StartDate and another is EndDate. But I was unable to choose the Boolean operator (And/Or) to use to combine this expression.
I came to know that the Boolean operator cell activates after we begin to enter an expression in the next row. But by default it is set as "And" operator. Now i want to cahnge that to "Or" operator it is in disable. Is there any way to enable this cell to choose the operator from the cell.
Any help would be appreciated.
Thanks
Dinesh
I am converting a crystal report to ssrs 2005.
I would like to know what the best method of handling a rather large record selection task.
Here is the crystal version.
if {?WhseRegion} = 'Western' then {R_ShippingLog;1.InvUnitSite} in ['LA','AL','DA','DB','OA','PO','SE','TH','TN']
else if {?WhseRegion} = 'Eastern' then {R_ShippingLog;1.InvUnitSite} in ['AD','BE','CH','CI','EL','FL','IL','JL','KC','KS','MA','MI','MK','NE','NJ','NO','PA','SA','STL']
else {R_ShippingLog;1.InvUnitSite} in ['LA','AL','DA','DB','OA','PO','SE','TH','TN','AD','BE','CH','CI','EL','FL','IL','JL','KC','KS','MA','MI','MK','NE','NJ','NO','PA','SA','STL']
What I trying to figure out is how to incorporate the above in the dataset filter area on the datagrid.
any ideas?
I have created a report showing both first name and last names with the following filters. Both of them are prompted parametres
Last Name Contains ------------------
First Name Contains ------------------
When users enters both then we get the results back. I want the first name to be optional such that if user enters something for the first name then take it into the search criteria else show all the first names which matches the last name criteria.
Example: Last Name = "SMITH", First Name = (user has not entered and NULL check box is checked). I don't get any results since this filter translates to find all the records which has the last name SMITH and First Name IS NULL),
Instead I want this to be select all the records which has the last name SMITH and First Name <> EMPTY. Is there a way I can do this using the fiter magic?
Thanks.
I am developing a summary report that will have multiple tables, charts and matrixes, all using the same set of data. However, one table may only show one month of data while another will show three months. If they all use the same Dataset with the same data parameters, is the data only pulled once? And then each component can use Filters to further refine the data? If this is true, this would seem to be the best option.
Or does each report component execute the query independantly?
Before I get too far down the road developing this report, I'd like to know the best way to do it from the beginning.
Thank you for your input.
Rob
I have created a report showing both first name and last names with the following filters. Both of them are prompted parametres
Last Name Contains ------------------
First Name Contains ------------------
When users enters both then we get the results back. I want the first name to be optional such that if user enters something for the first name then take it into the search criteria else show all the first names which matches the last name criteria.
Example: Last Name = "SMITH", First Name = (user has not entered and NULL check box is checked). I don't get any results since this filter translates to find all the records which has the last name SMITH and First Name IS NULL),
Instead I want this to be select all the records which has the last name SMITH and First Name <> EMPTY. Is there a way I can do this using the fiter magic?
Thanks.
Can I customize the report filters?
I need to use the data from another table (in a Drop Down control) to filter the data of my report instead of having a simple textbox.
How can I do this?
Hi,
I am getting data from an external source. External data has a column called "Type". I have a variable in my package which contains the list of types as shown below:
Filtered_type_List = 2,4,8,10,11
If this variable(Filtered_type_List) is blank, then I need all the data from the external source and if it is not blank then I only need the records matching to his list. How can I implement this in DataFlow Task?
Thanks
In the Package configurations wizard, I am trying to edit an existing configuration using the edit button. In the Configuration Filter, I get the list of several filters (the filters which were used for other packages). Whe I try to reuse an used filter, it is forcing me to set a new value and when I go back to SQL Server tables , I see the old value has got erased.
Can I not use an existing filter?. Do I need to use new filters for every new package?.
Thanks.
Does anyone know the best way to set up filters against the Cube data.
Ex. The Cube was built with data for 1998,1999, 2000.
The totals and the rollups will include the value for all years.
Client A want to access the Cube Data through Excel 2000 but only wants to see the values for 1999 - with the correct totals and rollups...
I know you can uncheck the other years in the filters in excel, but the totals and rollups will still include the values summing for all years.
Is there away to pass in a query on the fly?
Thanks.
Hi,
I would like to setup a merge replication from a main database (publisher), to around 100 client databases (subscribers), that contain only subset information for each distinct client: each client has then its own database to view and modify its own data (Filtered on rows and columns). The client databases will initially be hosted on the same SQL server Instance.
I don't want to setup manually 100 publications with static filters, and more may come in the future: a pain to setup and maintain because I have to configure the tables, the columns and the row filters (and joins) each time.
I would like then to setup one publication with a dynamic filter, to filter in a way or another on a specific client. Creating subscribers becomes then a piece of cake. The dynamic filter would apply based on a property specific to the client, but... HOW?
Filtering on HOST_NAME() will not work because several subscribers are on the same server.
Filtering on SUSER_SNAME() will not work because merge agent will always use the same user name for connecting to the publisher (using push subcription, all merge agents are on the same server), and I have not find out how it can be configured by merge agent: even if the merge agent jobs have different owner, it is always the SQL Server Agent login that is used to connect to the publisher (I am using windows authentication).
I was thinking about using DB_NAME(), and have specific db name for each client DB, but DB_NAME() provides the name of the publisher DB, not the subscriber DB. etc ...
What could I use in this case to dynamically filter on client data without having to fall into heavy replication administration and setup.
Thanks for your help,
Best regards
i'am working with SSRS 2008 and my source is OLAP(mdx query) i created a report like this
the report return me the following data
Nbre all Products Nbre Dell Products Nbre HP Products
Month 1 50 50 50
Month 2 100 100 100
what i need is doing filter for a specific columns for example for Nbre Dell Products show me only the number of Dell Products also for Hp Products how can i do it.
I am using Sql 2005 and merge replication with push subscriptions. I have several dynamic join filters on some of my tables.
The join filters all use a central table that maps say a server location name (something that is returned from HOTNAME() in my case) to an for a store branch ID. This is a retail system database.
When I add a new new subscription I update this table with the new server location name and it's corresponding branch ID. My filtered tables all have a foreign key in them that is the branch ID. I can then effectively join from the server location name to a Branch ID.
What I have noticed is that if I update one row in the map table, sql server will re-generate all partitioned rows for all subscribers, even for rows that haven't been updated.
The net result is that when I add a subscription, my existing subscriptions all get about 52,000 row updates.
Am I seeing this because I said my partitions will overlap when I created the table articles?
Thanks for any help
Graham
Dear ppl,
I am using Merge Replication between SQL Server 2005 (Publisher) and Pocket PC (SQL Mobile 2005-Subscriber). I have a Windows Mobile appliction on the Pocket PC that replicates data from the server.
I am using SqlCeReplication class on the Pocket PC application to synchronise the data. By default, when i call the Syncrhonise() method, it pulls all the data from the server. What I want is to pass a parameter from the Pocket PC and filter the data based on that paramter. E.g. from the Employee table, I want only those Employee that belongs to a CompanyID that I pass as a parameter.
Is there a way to do this, so that i can pass parameters from my PDA application (Windows Mobile), and make the Filters specified in the Publication to use that parameter to filter out the rows.
Regards
Nabeel Farid
Got a dataset coming from a stored procedure.
There is a parameter I must put on the report that filters the dataset.
the choices are "exclude" and "only", based on that I have to filter my dataset like
if they select exclude then I have to filter where "debitType <> 'Intercompany'"
if they select on;y then I have to filter where "debitType = 'Intercompany'"
Is this at all possible in the filter part?
It is easy as pie in Crystal record selector.
am using report parameter name date and its data type ia datetime,and in the dataset also the same type.but the problm is whn im using this to filter data using this date parameter,the following error will pop up.
"an error occurred during local report processing.The value provided for the Report parameter 'Date' is not valid for its type."
Plese giv me solutn to this problm
thanks
Jo
I'm trying to calculate the average number of sick days per person so that it can be broken down by person role or department or some other dimension. I have a calculation for sick days that works ok and is [measures].[sick days.I'm trying to create another calculation that is [total staff] but it doesn't work.
My final calculation will be [avg sick days] = [measures].[sick days] / [measures].[total staff]
The bit I can't get to work is making it ignore any filters. For example I have a measure called [staff] and as you would expect it can be broken down by [person].[role] or [department].[department].
I'm trying to get [total staff] to return the total when it's used with [person] or [department]. I've managed to get it to work with [person] by using ROOT([Person]) but then I will need to do this for all different hierarchies that will use it.