Dynamic Drop-down Parameters In Custom .net Web Page Error
Feb 7, 2008
Hi,
I created one report with 5 dynamic drop down parameters. The first parameter is passed from URL and the other's parameter drop downs should get populated based on the first one. These 4 parameters default to some value which I defined.
Everything is working fine in the Report Manager but when the report is integrated in a web page I am getting some problems.
After the drop-downs are populated and the user wants to filter down by using different values in the drop down, causes the report to generate automatically and all the dropdowns are reset to default.
Have you had this problem before? Anyone can advise me how to solve this issue?
I found the following paragraph while searching on here:
************************************************************************ You could easily set up a parameter for each column and then display that column conditionally based on the parameter.
For instance, if you have a column that displays First Name, you could have a parameter called DisplayFirstName. Then in design view you'd select the whole FirstName column and in the Visibility-Hidden property set it to :
This could easily become a big, unwieldy report, though, if you have a great number of dynamic columns. Also, The width of the report is set at design time, and it includes the width of all your columns, not just the visible ones. This could cause you some pagination problems. ************************************************************************
That is exactly my problem. I have lots of dynamic columns which causes the width of the report to be wide. Thus even though at run time the report only shows columns within a page, the report itself consists of a lot of white spaces after the selected columns. Does anyone know a solution to this? If not, I guess creating the rdl with code manually is the only way? Please advise. Thanks in advance.
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.
I'm writing my first SSIS custom task. I have added Public properties, which appear in the standard task properties window, and to one of them I have added an EditorAttribute as follows:
   <Category("General"), _    Browsable(True), _    EditorAttribute(GetType(UIFileNameEditor), _    GetType(System.Drawing.Design.UITypeEditor))> _    Public Property FilesToArchive() As String
[Code] ....
When I select the FilesToArchive property in the Properties window, I get an ellipsis that appears in the property value. When I click on the ellipsis, it brings up the FolderBrowserDialog, which is defined in the UIFileNameEditor class. This all works fine.
What I want to do is to bring up the EditorUI, the one you get when you double click on the task, and also populate that with properties which can be edited. I have a class which inherits DTSBaseTaskUI, which is displayed when I double click on the task.
I can also get properties to be displayed in that UI but I cannot get them to be editable in that UI, using the same technique as in the standard Properties window, as described above.
I am trying to use sql injection to create a dynamic drop table statment. I have tried to reformat a bunch of ways but can not get it. What am I doing wrong?
set @sql = 'if exists (select * from dbo.sysobjects where id = object_id(N' + @@tblname + ') and OBJECTPROPERTY(id, N' + 'IsUserTable' + ') = 1)' drop table @@tblname execute(@sql)
I am writing my first custom SSIS task and I can see that, if I put a public property into the task, I see that property in the standard Properties window. If I add a property of type String, I can put a value in that property, in the task code, and when I instantiate the task in a package, I can see the value I entered.
To try and get a drop down list property in the Properties window, I declared a property of type Combobox, and indeed a drop down list appears in that property.
My problem is trying to get values in that property. I have used the test items:
   Property.Items.add("Fred")    Property.Items.Add("Jim")
But I do not see the values in the drop down list. All I do see is one item with a value of "(none)".
I created a re port using the Report builder. The report runs fine. I want to create a parameter on the report. I created a new dataset with the query "select distinct dept from table" . Then i created a new parameter to get the values from the second dataset. After running the reports when i selected 2 departemnts, its still showing all the records.
We are developing a custom report item but we cannot find a way to get the report parameters. In Vb examples there is mentioning of globals but these do not seem to be present in the c# enviroment.
Could anyone give a solution on how to get to the report parameter collection in run-time.
I am trying to retrieve and manipulate the value of the stored procedure parameters in the following custom code:
Code Snippet Public Function ShowLoading(ByVal intOption as integer, byVal BasicLoading as string, byVal BasicHealthExtra as string, byVal RiderLoading as string, byVal RiderHealthExtra as string, byVal TotalAmount as string) As string
dim strRider as String dim strCompanyName as string strCompanyName = Fields!CompanyName.Value
select case strCompanyName case "and Plus Rider ": strRider = "Plus Rider" case "and Assist Rider ": strRider = "Assist Rider" end select return strRider
End Function
However, i got this error when I try to preview the report: An error occurred during local report processing, The definition of the report :
'/reportC' is invalid. There is an error on line 5 of custom code: [BC30469] Reference to a non-shared member requires an object reference.
Any help is greatly appreciated. Thanks in advance.
I am interested in adding custom buttons to the parameters toolbox (under "View Report"). I'm trying to provde a way for users viewing the report to go "Back" or up a level (in the drill through reports). Does anyone have any information on this topic, or know if it's even possible?
I am trying to build a report that allows a user to enter partial text that filters the values in a drop down For examle; Lets say I am selecting a value from a drop down for States and I want to type in FL to filter the collection down so that only Florida appears in the select list. I know the above example wont work since I cannot actaully enter text into a drop down, however I have also tried to seperate this functionality by creating one report parameter for entering the search text and another parameter that will display a filtered data set using the text entered in the first parameter.
Is this something that reporting services can handle ? If it is please provide a brief example. Thanks
I have problem with SQL Reporting Services, actually i want display the 3 parameters in a single line in a report i.e. in the while preview of the report and after deploying the report (by default it is showing 2 parameter per line). for example if have 6 parameters i wanted it display it in 2 rows and 3 columns format.
I've created a custom dll using ADOMD.net that takes in a few parameters (Username being one of them) and returns a set of Allowed Members to apply dynamic dimension security. Example:
Code Snippet
Security.GetSetList(UserName,1,"SQL Server")
This works fine for users with less than, say, 100 tuples/members in their set....however for users with more an error:
Error ocurred retreiving child nodes: The 'S' attribute in the 'S' dimension has a generated dimension security expression that is not valid. ...
However, if I take the same set and explicitly (ie
Code Snippet
{[S].[1],[S].[2],.......[S].[50000]}) put it in the Allowed set there's no issue!
I noticed that it takes the UDF a couple of seconds longer to return for users with a large security context. Is there a time-out parameter that controls this? Is there a limitation in the amount of members I can add via custom dll (UDF)? (Some users can have up to 70,000 members)
I should also mention that the UDF uses the StrtoSet function to convert the security context string to a set. The string can be as large as 210,000 characters. Is there a limitation on the string size that can be passed in this function?
I am trying to put my parameters in my page header.It do works fine.I am using a textbox to hold the value of my parameters.I use
= Join(Parameters!resourceParams.Label, ", ")
in my textbox expression.But my problem is,when there are too many values specified(because i am using a dropdown with multi select) the page header,overlaps the body of my report. The page header's text overlap the contents of my report body.What could be the reason or work around for this?? Thanks guys
I have found "as have many others" that when adding dynamic visibility " tosub_reports and tables etc" to SSRS reports then pagebreaks are switched off. Has anyone found a workaround for this? Or am I doing something wrong
My report has one page per record grouped on an id. Each record has possible additonal information in another table which if present I would like to show on a new page. I can get this to work ok by just appending a subreport to the report based on the value of a report parameter, however this does not meet the requirement. I need to have the subreport on a new page for each record.
We have an asp.net page that pops-up from the main site and has the report service control on it. This first pop-up page generates the report and has embedded links for multiple sub reports. When we click on the link for the sub report it generates the report but it stays in the first pop-up. Is there a way to have another pop-up(preferably a different pop-up for each link the user clicks) page? Thanks in advance.
I have a report that i want to beable to do some dynamic paramaters on it. I have a start and end date of when they want to run the report but i also have a mulivalue list box for users filtering for users. I want to add a supervisor filter on form also. But I have a problem.
I want to allow the users to select a supervisor and filter for all employees under that, or allow them to select a set of users and allow them to filter for that also. Also i would like when they select no supervisor or user it just filters for the dates and picks all up all the users.
I thought i could do something like in access like this Like UserName & "*" but it doesnt work.
Here is my dataset code i am using currently: SELECT U.Name, A.row_date, A.split, A.SumOfti_stafftime, A.AHT, A.AvgACW, A.AvgACD, A.AvailTime, A.SplitSkill_Incalls, A.SplitSkill_Outcalls, A.SumOfacdtime, A.SumOfti_othertime, A.SumOfacwtime, A.SumOfti_auxtime0, A.SumOfti_auxtime1, A.SumOfti_auxtime2, A.SumOfti_auxtime3, A.SumOfti_auxtime4, A.SumOfti_auxtime5, A.SumOfti_auxtime6, A.SumOfti_auxtime7, A.SumOfti_auxtime8, A.SumOfti_auxtime9, U.Sup FROM tblAvayaDaily AS A RIGHT OUTER JOIN tblUsers AS U ON A.logid = U.[Avaya ID] WHERE (A.split = 1651) AND (U.Name IN (@UserName)) AND (A.row_date BETWEEN @rDateStart AND @rDateEnd) OR (A.split = 1655) AND (U.Name IN (@UserName)) AND (A.row_date BETWEEN @rDateStart AND @rDateEnd) OR (A.split = 1653) AND (U.Name IN (@UserName)) AND (A.row_date BETWEEN @rDateStart AND @rDateEnd)
I am currently working on a project that involves creating dynamic insert and update statements for dynamically created tables. The tables I am creating the statements for could have up to 1000 columns.
Obviously building the entire insert/update SQL string dynamically in VB.Net and sending it to the Sql server leaves the system vulnerable to injection attack or error caused by single quotes in strings.
I am using the Patterns and Practices Data Application block for my data access.
What I am considering is creating the dynamic sql as a statement with the columns names and values in as parameters (@XXXXX) and then looping through my list of columns and values and adding them as by AddInParameters on the DbCommand.
- Can it handle such large amount of parameters at all? - What kind of structure does ADO.net send to the SQL server? i.e. Will using so many parameters mean that the data communicated to the SQL server from the webserver by the parameter method would be much larger than the substituted dynamic sql string? - How does ADO.NET/SQL Server 2005 perform with such large amounts of parameters on a DBCommand?
Any advice as to whether this is a viable way to proceed would be much appreciated.
Oh yeah... I am using VB.NET in VIsual Studio 2005 with a SQL Server 2005 DB.
When I try to use (from page header or footer) a parameter or global or a function I wrote, I get the error below. Anyone got any ideas? Other reports within the VS 2005 project don't have this problem. Thanks....
An unexpected error occurred in Report Processing. Object reference not set to an instance of an object.
* We have a DAL that generates all SQL dynamically out of a nobject model. Standard very powerfull O/R mapper. * In the DAL, for CRUD operations, we generate the statements dynamically. As an example, let's take INSERT. * The insert is generated ONCE, with parameters, and cached. For every reuse, the parameters are replaced (in value), and the whole thing commited.
I see hte following negative: I can not easily batch multiple inserts. Parameters have to be unique per batch. So, if I want to batch two inserts, I need two sets of parameters.
Alternative:
Instead of generating the SQL with parameters, we generate the SQL as a string ready to be inserted for / with a String.Format, and then I encode the parameters and make one SQL String out of this. Now, please - don't say "sql injection", we are not that stupid, the layer handles this already, properly encoding all dangerous values.
With this approach, the SQL statement would be a string and not use any parameter. As a result, I could batch them up as much as I want (ok, up to a certain string size). I need to keep parameters around anyway (for blobs etc.), but most objects do not have blobs, and the SQL is prettty small. This small SQL could be batched significantly (100 statements per batch, propably mode) and be submitted to the database. As a result, the round trips to the databae would go down.
Now, my question is - which of the two approaches is more advisable, from a performance point of view? Again, stuff like SQL injection and ease of handling are totally irrelevant - the SQL never leaves the DAL and is generated in there, and we will go through a lot of complexitiy for higher performance.
Normally I would say batching should be better. SQL Server can auto-parameterize the statements (reusing the query plan), and / but the network round trips are the larger issue here.
I'm trying to write a formula (actually, four) which will be used in Panorama NovaView to allow a user to see Revenue (on rows) by Previous Month (Current Year), Previous Month (Previous Year), Current YTD (ending at month in question), Previous YTD (ending at month in question) (on columns -- not necessarily in that order).
Basically, when run anytime during November 2005, the output would look like:
2004 Total | Oct 2004 | 2005 Total | Oct 2005 $ 100,000 | 15,000 | 120,000 | 17,500
The Dimension I'm working with is [Calendar Year], with levels: (All), [Calendar Year], [Calendar Month], [Calendar Week].
I've been focusing on the formula for the first column, since it is the most complex (I think). Here's the jist of what I am trying to do:
SELECT Measures.Revenue ON ROWS, <<Last Year>>.January : <<Last Year>>.<<LastMonth>> ON COLUMNS FROM SalesCube
I believe some combination of PeriodsToDate(), LastPeriod() and/or <<Current Year>>.PrevMember & <<Current Month>>.PrevMember is what I need, but I just can't get the syntax right.
I would like to use a where clause that can make use of any combination of the 4 parameters (the two dates should be together)
1 2 3 4 / x x x x / x x x x / x where x = not supplied / = supplied a value
(and so the list continues) Can anybody assist me or give me insights on how to go about this complicated WHERE construct without listing all the probable combinations of the supplied parameters in series of IF statements.
I have a winform application, where in we get the parameters from the reporting service and we display them in a panel for user's to input their criteria. So far everything worked good, but now we have a requirement wherein dynamic functionality is needed.
For Example:
Based on a selection of a combobox value, we want the other control to be enabled or disabled. Also we want default value of a control to be calculated based on another control's value like .. if user enters value "1" in textbox1 then texbox 2 should have default value of "6". (Textbox1 + 5).........and mix and match of such capabilities.
The Question is........IS IT POSSIBLE??? and if yes...HOW!!!
Hi I am using sql server reporting services 2000 and in a report I have more than 20 parameters of data type Boolean. I want another parameter drop down which have "select all" and "clear all" options. When user select €œselect all€? option from the parameter list all 20 parameters value should be false.
I am somewhat new to DTS packages - please bear with me...
:: Overview: From a web page, I need to create a text file on a User's mapped drive drive (note: the mapped drive is always the same and it is a drive that the IIS cannot see, and FTP is not an option here). One of the parameters I need to pass from the web page (to the DTS) is the text file name.
Currently, I have a stored procedure that creates my dataset for my text file, and I can create the text file from the web page onto the IIS server, but this does not solve my problem.
My dilemma is how do I create the dynamic text onto the mapped drive? The SQL server CAN see the mapped drive.
Can I bring together what the Stored Procedure creates AND pass thru the dataset and the dynamic name to a DTS so it can create and save the file?
Can the Stored Procedure be eliminated and it all be done via DTS?
What about passing in parameters directly to a DTS from the web page? I'm not finding much help on this portion
Any help and/or direction would be appreciated as I am on a tight deadline!
