I have a project where there are millions of records. For sake of example, let's use the White Pages.
One of the reports my client wants generated is a contact list. The contact list contains name, address, city, state, zip, phone, and several other key data elements.
Naturally, the client wants to be able to sort on each of those elements (easy to do.)
They also want to be able to filter the resultset the report shows, dynamically, at runtime (not so easy, I'm finding).
So, for example, one person may want to run the report and have the results only show people in PA. Another person may run a report and show all the people who are in Boston, MA. Yet another person may want everyone with the last name of "Smith".
Ultimately, the client wants to be able to filter results by virtually every day field displayed on the report (and some fields which are not!)
I've dug around the documentation for Reporting Services, and I readily admit I am a newbie. I cannot see any way to dynamically filter the resultset used to generate the report, other than using "Report Parameters".
The problem with Report Parameters, from what I can tell, is they also require me to have scalar expressions in a WHERE clause in the dataset. So, for example, if I wanted to let people filter on lastname, I would need a "WHERE LASTNAME LIKE @LastNameReportParameter" -- which works ok, as long as the parameter isn't null, of course, so I really have to do something like 'WHERE LastName LIKE ISNULL(@LastNameReportParameter, '%')"
With, literally, a dozen different fields on this report, I don't see where that is a real option -- I literally have to program a WHERE clause that has a dozen elements, some of which are numeric (not alpha), and thus LIKE doesn't work (AGE = 37) unless I get into completed gyrations (WHERE ISNULL(CAST(AGE AS VARCHAR), '') LIKE ISNULL(@AgeParameter, '%')
This seems to be incredibly complicated, and I cannot imagine there isn't an easier way to allow users to filter the resultsets on the reports generated by Report Server. Is there something I am missing?
Also, it has to be something I can address programatically, since the client wants a fairly 'seamless' front end (in C#) where users can simply click a radio button (to select a field to filter by) and type in a value (to filter on) prior to displaying the report in a reportviewer control.
Is there a way to dynamically program a filter expression and pass it to the report server, something akin to a filter expression on a BindingSource?
Any thoughts on this matter would be helpful, or pointers to online documentation, as I'm sort of out of ideas and cannot find anything in the docs. Thanks!
I have a report which contains a parameter called SuppressZero which depending on its value I want to filter out certain data. This parameter can have 3 different values and for each value I need to have a different filter expression. What I would like to do is implement the following:
If SuppressZero = 1 Filter where Quantity <> 0
If SuppressZero = 2 No Filter
If SuppressZero = 3 Filter where Quantity <> 0 Or InStockFamily = "Y"
I have a report that comes with one main image and 15 other images that needs to be visible as needed at fixed position to the main image. Adding all the images is easy but managing the visibility is not working. What is the best way to do it? I am looking for sample but have come up empty.
im working on a report which shows the data based on the users input. I have used a matrix because I have no control on how many fields the users want, so practically we have a dynamic column here. My problem is that they require me to able the user to hide the columns he/she wishes to during runtime. We have a previous report that enables the user to hide it during runtime but it has static column. Any idea on how I could do it?... Thank you so much...
I've seen a number of posts similar to this but i still cannot figure out what i need to do to get it working. So here goes with a couple of newbie questions.
Question 1: Once created how do i go about executing a SSIS package. I want to be able to call it from a C# application from which i pass in a couple of parameters?
Question 2: How do i go about setting the file path of my Excel source to a dynamic value passed at runtime. I want to be able to loop through a number of Excel files and do some processing on them. I've set up a variable (which i think i need to do) after that i get stuck however. Some other posts suggest configuration packages but i cannot get my head around how they work?
Any help on this matter would be gratefully recieved.
I've been playing around with building custom components for SSIS. I've been doing workflow for years (using Java and Oracle). The company I worked for had a framework for publishing data that allowed for user interaction. That's something I'd love to be able to do in SSIS.
Is it possible to create a custom task that interacts with the user at runtime? So, the user starts the SSIS package. At some point, the process pops up a dialog (Windows Form) that asks the user to set a date using a calendar control.
I work on a system that is mirrored. there is production, and UAT(user acceptance testing). We have to do repetitive tasks on the systems. I want to write an application to make those tasks easier, using integration services. I would rather have one app and be able to specify which database server the dtsx packages would use each time. is this possible in dtsx packages, to specify a particular sql server? the tables and databases would not change between production and uat
Hello. I'm trying to create a new login and username inside a trigger using variables.
This code works:
create login testUserName with password = 'testPassword'
The problem comes when I try to use variables for the username and password like this.
create login @username with password = @password
Does anybody know how to get around this problem?
BTW, the error message it gives is this, but I really doubt that semicolons have anything to do with it. If I literally type my data in the create login call it works fine, but when I use variables it doesn't.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near '@username'.
Msg 319, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
I am having a Data flow task in For each loop which will gets 100 sourcetable names and 100 target table names...
am having a simpleData flow task which trasferes from OLEDBSource to OLEDBDestination. I am repeating the Dataflow task which transfers from sourcetablename extracted from for loop to a destination table var.
The problem am gettting is for the first table it is able to transfer correcly because I did mapping for those tables at design time...but for the next coming sourcetable-desttable (which r having different no of cols,datatypes) its giving Validation failed...and...needs to refresh metadata....
is there any way to refresh the metadata of Data flow task (I set the property of OLEDBSource validate external meta to false then also same error is coming)
Let's say I have a table with 3 fields: an ID field (primary key, set as an id field, etc.), a Name field (nvarchar50), and an Age field (int). I have a form that has three elements: DropDownList1: This drop down list contains 3 choices- "=", ">", and "<". Age: This text box is where someone would enter a number. Button1: This is the form's submit button. I want someone to be able to search the database for entries where the Age is either equal to ("="), greater than (">"), or less than ("<") whatever number they enter into TextBox1. The code-behind is shown below. The part I'm confused about is that if I load this page, the query works the -first- time. Then, if I try to change the parameters in the form and submit it, I get the following error: "The variable name '@Age' has already been declared. Variable names must be unique within a query batch or stored procedure." Any help would be appreciated. Here is what I'm using in my code behind: protected void Button1_Click(object sender, EventArgs e) { System.Text.StringBuilder sb = new System.Text.StringBuilder(); sb.Append("SELECT * FROM People WHERE Age "); switch (DropDownList1.SelectedValue) { case "=": sb.Append("= "); break; case ">": sb.Append("> "); break; case "<": sb.Append("< "); break; } sb.Append("@Age"); SqlDataSource1.SelectCommand = sb.ToString(); SqlDataSource1.SelectParameters.Add("Age", TypeCode.Int32, Age.Text); }
I'm a beginner to Report Services, and have tons of questions.
Here's the first one:
if the reports are created based on the condition that the user selects, how can I create the reports with Report Services?
For example,
the user can select the fields that will be shown on the reports, as well as the group fields, the sort fields and restrict fields. So I would not be able to pre-create all possible reports and deploy them to the report server, and I think I should create the reports dynamicly based on what the user select.
Could someone tell me how to do it (create and deploy the reports)?
I'm trying to setup a dynamic ole db connection using the SA user ID, it has to be dynamic because the server name will change and it has to be SA because we're pulling information from system databases that some users don't have access to.
If I setup a regular static connection using SA credentials it works like a charm of course. When I create an expression to use the User:erver variable it doesn't work, it throws an error message saying that "The login failed for user sa" among other things, I'm thiking that the sa's password is not being saved.
Where exactly do I place a password for dynamic connections using sql server users? On the connection string? On the password property of the source? Any ideas?
I need to create a set so that when a user selects a month in filter (say 201506) then it should give me a list of months from 201406 to 201506. Any appropriate MDX query.
This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database) Help pleaseee
I got your email address from your web cast. I really enjoyed the web cast and found it to be very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question regarding the product. I have looked for the information on the web, but was not able to find relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets Are generated using reporting services. On examining the excel sheet, I found out that the name Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc. And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers. http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1 http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Hi, I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.
So, is this possible using data driven subscriptions? Scenario is:
1. User enters parameter used for query, as well as email addresses. 2. Report is generated and displayed on screen. 3. Report is emailed to addresses specified by user.
SQL Server 2000 SP4 to multiple SQL Server 2005 Mobile Edition on PDAs. My DB on SQL2k is published with a single dynamic row filter using host_name() on my 'parent' table and also join filters from parent to child tables. The row filter uses joins to other tables elsewhere that are not published to evaluate what data is allowed through the filter.
E.g. Published parent table that contains suppliers names, etc. while child table is suppliers' products. The filter queries host_name(s) linked to suppliers in unpublished table elsewhere.
First initial sync with snapshot is correct and as I expected - PDA receives only the data from parent (and thus child tables) that matches the row filter for the host_name provided.
However - in my scenario host_name <--> suppliers may later be updated E.g. more suppliers assigned to a PDA for use or vice versa. But when I merge the mobile DB, the new data is not downloaded? Tried re-running snapshot, etc., no change.
Question: I thought the filters would remain dynamic and be applied on each sync?
I run a 'harmless' update on parent table using TSQL e.g. "update table set 'X' = 'X'" and re-sync. Now the new parent records are downloaded - but the child records are not!
Question: I wonder why if parent records are supplied, why not child records?
If I delete existing DB and sync new, I get the updated snapshot and all is well - until more data added back at server...
Any help would be greatly appreciated. Is it possible (or not) to have dynamic filters run during second or subsequent merge?
I have tried building an Inline TVF, as I assume this is how it would be used on the DB; however, I am receiving the following error on my code, I must be missing a step somewhere, as I've never done this before. I'm lost on how to implement this clr function on my db?
Error: Msg 156, Level 15, State 1, Procedure clrDynamicPivot, Line 18 Incorrect syntax near the keyword 'external'. CREATE FUNCTION clrDynamicPivot ( -- Add the parameters for the function here @query nvarchar(4000), @pivotColumn nvarchar(4000),
CREATE PROCEDURE dbo.spinb_CheckYN @FnNameYN varchar(50), @InvLineID int, @FnBit bit output AS
declare @SQL varchar(8000)
set @SQL = ' if dbo.' + @FnNameYN + ' (' + convert(varchar(31),@InvLineID) + ')) = 1 set @FnBit = 1 else set @FnBit = 0'
exec (@SQL) GO
Obviously; @FnBit is not defined in @SQL so that execution will not work. Server: Msg 137, Level 15, State 1, Line 4 Must declare the variable '@FnBit'. Server: Msg 137, Level 15, State 1, Line 5 Must declare the variable '@FnBit'.
So; is there a way to get a value out of a Dynamic SQL piece of code and get that value INTO my OUTPUT variable?
My many thanks to anyone who can solve this riddle for me. Thank You!
Sigh: For now, it looks like I'll have a huge string of "IF" statements for each business rule function, as follows: Hopefully a better solution comes to light.
------ Vertical Build1 - Std Vanes ----------- if @FnNameYN = 'fnb_YN_B1_14' BEGIN if dbo.fnb_YN_B1_14 (convert(varchar(31),@InvLineID) ) = 1 set @FnBit = 1 else set @FnBit = 0 END
------ Vertical Build1 - Scissor Vanes ----------- if @FnNameYN = 'fnb_YN_B1_15' BEGIN if dbo.fnb_YN_B1_15 (convert(varchar(31),@InvLineID) ) = 1 set @FnBit = 1 else set @FnBit = 0 END . . . etc.
I need help with filtering a specific set of numbers. I have a Sql database that is connected to my sql report I have created a tsql statement that pulls a clients name, PO, and invoice number. The prblem I am having is I have 2 different types of invoice numbers one number looks like 123456-1234-T the other looks like 123455-1234-L I need to beable to pull only the invoices with T on one report and L on another report can some on show me how I can sort these in a tsql script
This may be simple, but I can't figure a way to do this. I have the following data returned to a table and need to sum only the items where HDMethod=0 in the table footer. For some reason, something like:
=Sum(Iif(Fields!HDMethod.Value=0, Fields!BDExtended.Value, Nothing)) returns all the rows. There is a LEFT OUTER JOIN: dbo.[Billing Detail].Item = dbo.[History Detail].Item between the tables touched in the query if that helps.
a text input for filtering a gridview that displays the data an SqlDataSource that contains the query. Users can either enter something into the text input or leave it blank. Depending on that, the gridview should either display all data (unfiltered, because nothing was entered into the text field) or filtered data (when something is entered). Now my problem is in defining the query in the SqlDataSource. I could do something like this: SELECT * FROM myTable WHERE myField = @p1; and then add in the appropriate <asp:ControlParameter /> under the <SelectParameters> tag. However, this sorta "fixes" the filter. Regardless of whether users actually type something in or not, the filter is in effect. I want it in such a way that if users do not type in anything, the query essentially becomes: SELECT * FROM myTable; Is there any way to achieve this? Thanks in advance, jason
I'm not sure if this is possible and have been having trouble figuring out the code to do this. I am assigning row_number to a gridview. I then want to filter the results with a dropdown. I am able to get the filter to filter the status but it either renumbers the gridview or it leaves the row numbers blank. Is there a way to have the row_numbers stick to the gridview when I filter? Example below. Thanks Normal:IssueNumber(row_number), Status1, Open2, Open3, Closed4, Open5, Closed "Open" Filter:IssueNumber(row_number), Status1, Open2, Open4, Open "Closed" Filter:IssueNumber(row_number), Status3, Closed5, Closed
Why is Select * from [Merchandise] where [Product Name] like '[ABCD]%'the same as Select * from [Merchandise] where [Product Name] between 'A' and 'D'I can run Select * from [Merchandise] where [Product Name] like 'A%'and get Products that start with the letter "A" but they don't show up when I try to get all "A","B","C","D" Products.
I think I might be missing something here. Here is what I'd like to do:1. Retrieve a list of data from SQL Server.2. Display that data in a gridview.3. Have the user click on a button to then see a subset of that data. (filtering) I can't seem to make this work. When the user clicks the button, I need the GridView to update to show only the specified data. In 1.1 I would created a DataView for the filtering, but am trying to use the latest and greatest. I've seen examples online of people using DropDownLists to act as the dynamic filter parameter. How can I programatically assign this to make it work?Thanks!
Our current method of limiting what data a user can see is implemented solely through our Web based business intelligence tools. No filtering is enabled at the database level. This has become somewhat cumbersome as security is tied exclusively to these tools. The tools use one common logon to access the underlying database.
I would like to implement security at the database level (SQL Server 2000) and thereby produce a more flexible/portable solution. I was thinking of setting up individual database accounts for each user and then tying these into our company structure table by passing system_user result to a constraint.
For example System User name 'Store 2' would reference Store '2' in the structure table. Depending on the user, different columns will need to be referenced to filter the rows. A store user would be validated against the store column, an Area Manager user would be validated against the Area Manager column and Head Office users would not be valiadated at all i.e. they are not filtered.
1) What is the best method to implement such a look up. Can or should I use Check constraints for such a solution?
2) Would a UDF be useful?
Any ideas on the best approach to take would be greatly appereciated.
