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.
I'm developing my first Analysis Server report in SRS. Everything was going well when I discovered the filters & parameters pane was missing in the MDX Query Designer in data mode. I have my columns and rows but need to drag a dimension into the filters & parameters pane in MDX Query Designer and set a filter and parameter on it. Any idea how to make it viewable?
I am creating a dashboard. It has two charts. One will utilize 'calendar year' data and other utilizes 'Fiscal Year' data.
I have 'CY start date', 'CY end date','FY start date', 'FY end date', 'Reference Date' columns in my data set.
I have a report parameter of date type that the user selects. When the user selects the date, I have to display the corresponding 'Fiscal Year' data for the entire year.
My question is, how can i filter the data set based on 'FY start date', 'FY end date' field values when i have only one Date parameter.
How can i get these data set field values based on parameter.
I have a 2 reports A and B.A is a dashboard with graphical objects like graphs and pie charts.B is a simple table with the base data used in A (see it as the factual table data)I would like to click on any of the objects on A and pass them into B BUT I don't want to use extra parameters on B.Is it possible somehow to pass the valued from A to B using action and then use this as filters of the table in B instead of forcing me to create parameters in B?
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.
Hi, I have an app in C# that executes a query using SQLCommand and parameters and is taking too much time to execute.
I open a SQLProfiler and this is what I have :
exec sp_executesql N' SELECT TranDateTime ... WHERE CustomerId = @CustomerId', N'@CustomerId nvarchar(4000)', @CustomerId = N'11111
I ran the same query directly from Query Analyzer and take the same amount of time to execute (about 8 seconds)
I decided to take the parameters out and concatenate the value and it takes less than 2 second to execute.
Here it comes the first question... Why does using parameters takes way too much time more than not using parameters?
Then, I decided to move the query to a Stored Procedure and it executes in a snap too. The only problem I have using a SP is that the query can receive more than 1 parameter and up to 5 parameters, which is easy to build in the application but not in the SP
I usually do it something like (@CustomerId is null or CustomerId = @CustomerId) but it generate a table scan and with a table with a few mills of records is not a good idea to have such scan.
Is there a way to handle "dynamic parameters" in a efficient way???
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
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?
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.
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.
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?
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.
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?
I am trying to search a SQL database using a TextBox and a DropDownList. The textbox being where the user enters there search query and the dropdownlist allowing them witch column to search. If I hardcode in the table column to search everything works fine but as soon as I parameterize the column to search I no longer get any results. While stepping through the code both parameters do get assigned the correct values to make the sql statement valid. Any advice would be appreciated. .... code that does not work ..... string connection = ConfigurationManager.ConnectionStrings["serverlist"].ConnectionString; string sqlquery = "SELECT server_id, hostname, os, description, owner FROM server_info WHERE @SearchItem=@SearchQuery"; conn = new SqlConnection(connection); comm = new SqlCommand(sqlquery, conn); String searchQuery = searchBox.Text; String searchItem = DropDownList1.SelectedValue; comm.Parameters.Add("@SearchQuery", System.Data.SqlDbType.VarChar); comm.Parameters["@SearchQuery"].Value = searchQuery; comm.Parameters.Add("@SearchItem", System.Data.SqlDbType.VarChar); comm.Parameters["@SearchItem"].Value = searchItem; ......
Hi all, I am trying to write an sql query in my web application to select some records from the database using parameters. I use SqlClient for that. The query has a WHERE clause where its supposed to values from the various dropdownlists that I have in my application. I bind the SelectedValue from the dropdownlists to the appropriate parameters and the SQL query does not work if all the conditions are satified in the WHERE clause. Can I have an SQL query where I can provide all the parameters in the WHERE clause (with AND conditions) and it takes only those values where there is some value from the dropdownlist and ignore the rest of the parameters. This is what I am trying to do: private DataSet GetSalesDataBas() { DataSet ds = new DataSet(); SqlConnection connection = new SqlConnection("..."); SqlCommand command = connection.CreateCommand();
SqlParameter prm_1 = new SqlParameter(); SqlParameter prm_2 = new SqlParameter(); SqlParameter prm_3 = new SqlParameter(); SqlParameter prm_4 = new SqlParameter(); SqlParameter prm_5 = new SqlParameter(); SqlParameter prm_6 = new SqlParameter(); SqlParameter prm_7 = new SqlParameter();
if (DropDownList23.SelectedIndex == 0)//if no sort field selected { strWhere = @" WHERE ""UserName""='" + userName + "' AND ([Status] LIKE 'REF%A' OR [Status] LIKE 'A%')" + " AND m.[Part Number] LIKE '%' + @pNum + '%'" + " AND m.[Serial Number] LIKE '%' + @sNum + '%'" + " AND (Location = @paramLoc" + " AND m.[District] = @paramDist" + " AND m.[Part Number]= @paramPNum" + " AND m.[Description]= @paramDesc" + " AND m.[Building]= @paramBuild" + ")"; } else { strWhere = @" WHERE ""UserName""='" + userName + "' AND ([Status] LIKE 'REF%A' OR [Status] LIKE 'A%')" + " AND m.[Part Number] LIKE '%' + @pNum + '%'" + " AND m.[Serial Number] LIKE '%' + @sNum + '%'" + " AND (Location= @paramLoc" + " AND m.[District]= @paramDist" + " AND m.[Part Number]= @paramPNum" + " AND m.[Description]= @paramDesc" + " AND m.[Building]= @paramBuild" + ")" + " ORDER BY " + DropDownList23.SelectedValue + " " + DropDownList24.SelectedValue; }
string sqlSalesData = @"SELECT * FROM Main m" + strWhere; command.CommandText = sqlSalesData; SqlDataAdapter salesOrderAdapter = new SqlDataAdapter(command); salesOrderAdapter.Fill(ds, "Main"); salesOrderAdapter.Dispose(); command.Dispose(); return ds; }
I am creating a package within which I have a query step that needs to accept a value from a variable step that has already been run, but I cant get it to work and the books online dont help!
The query is simple
insert into table1 select col1 = cola, col2 = colb, col3 = ? from tableA
Even though the variable that I want use has been set, I cant get the query to recognise there are any parameters. If I click the parameter button in the query set-up, I get a message saying that col3 is an invalid column name and then a box saying that an error occured when parsing the statement fro parameters !
I'm developing one MDX query to place in my report, but I'm having a lot of troubles when using one of my report parameters.
I hope I can explain me good enough.
I have a report parameter that's used in the WHERE clause to choose the value of one of my dimensions, the problem is that I'm also trying to compare the value of this parameter to decide the value of another dimension, but the comparison isn't working at all.
Let me show one example:
The parameter name in question is: DimFolderWalletDesciption.
This is suppost to do the following: If my parameter as the selected value "Investimento", then the "Indexation Group" selected should be "Rendimento Fixo" if not, there shouldn't be any "filter" in the Dimension "Indexation Group".
I've tryed a lot of combinations, but none works, what am I doing wrong?
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?
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?
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.
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.
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?
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?
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?
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?.
I have a web form that is populated from the DB values. User can update teh record by keying in new values in the form fields. In my .vb class file I have an update statement - strSql = "UPDATE msi_Persons SET Firstname=@firstname where Id=@id" I have the parameters defined as cmd.Parameters.Add(New SqlParameter("@firstname", _FirstName)) cmd.Parameters.Add(New SqlParameter("@id", _Id)) _FirstName & _Id are the private variables whose values a set thru set and get methods. I expect _FirstName to have the new value I am keying in the form. Can any body help me with what's wrong here? This is not updating the database. When I do trace.write it shows me the Update statement as "UPDATE msi_Persons SET Firstname=@firstname where Id=@id" instead of @firstname and @id being replace by actual values. Please help. Thanks,Shaly
I believe all of us use SQL Server Management Studio to execute query. As I encountered serious problem with this method, due to typo error by accident, I am wondering if we can run the .sql file from dos and supply parameters with it.Possible?
Hi all. I have a problem on my query. let me explain first the scenario.
I have a windows application project that views a report through report viewer(Report of all employees that have a religion of adventist, born again, catholic, baptist, etc....). In my windows application, I have checkboxes for a specific religion. So, if I checked baptist and catholic, report generated is all employee that has religion of baptist and catholic.
My concern is, How could i make a query of this?
sample tables. table - employee dcno name religion 1 jon baptist 2 rose catholic 3 joy baptist 4 mike adventist 5 soy born again 6 rich protestant
