Dynamic Data Set Parameters During Report Run Time
May 25, 2007
I need to pass the out put from a stored procedure as an input parameter to another stored procedure. I created a data set for each stored procedure. Fron the second data set parameter tab, I added a parameter to refer to the field from the first data set.
I get the error Fiels can not be used in query parameter expressions.
I tried two simple queries instead of stored procedures with first query feeding the second query. I get the same error. Is there any other other way to accomplish this?
I have three databases which have the identical data structure. When the user runs a report, he has to select a database. For example, my report query would look like:
Select * from <theDatabase>.dbo.MyTable
How could I assign the value of <theDatabase> at run time depending on what the user selects from the list? In my datasource, I have only specified the server name and no value for initial catalog.
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 have a SSRS report with four parameters,and I want to be able to enter information for two of the parameters and run the report opposed to all four of them. However, when I select allow blanks and only select the parameters that I want to run the report by, the report come back blank..Essentially, I want to be able to the run report by different parameters without having to enter information for all parameters at the same time.
Is there a way to subscribe SSRS report using dynamic parameters for email and trigger the report from autosys job so that report should generate the exact time the job is triggered.Let me describe, my SSRS report should be triggered on success of one autosys job. i need to send email parameter and time of report schedule from this autosys job.
I have three types of specific reports that i have to create with the input parameters (range) either
1: By date (rdl 1)
2.By Month (rdl 2)
3.By Year (rdl 3)
Is it possible ( or how do I ) to create just one report template ( one rdl) with the three sets of parameters ( hiding/invisible which ever two sets base on user selection) and the output of the report will display the desired type( either by year, month or date).
I ask this because its possible to create a drill down report from year down to date etc in report designer (vs 2005). Not sure if I can create one instead of three rdls and with the 'logic' built within that template.
Is it possible to create a report with dynamic parameters or parameters dependant on other ones? I have the following scenario -
Parameter 1 - 'Select an Application' - List of applications that are available
Parameter 2 - 'Select a module' - This is a list of modules that needs to depend on Parameter 1. As a user selects different applications, the appropriate module list will be loaded.
I have a report that uses a begin date and an end date as paremeters along with a user's email adddress to generate thier schedule for the coming week. I would like to schedule this report to run at the beginning of the week and email to each user based on thier email address. So the parameters will be system.date +1 and system.date+6 and user email. I have the users email addresses in a table so the 3rd parameter could be a "for each email in table users...". Is this posssible? Should it be in Report services or a scheduled Stored Procedure? Any help with syntax will be greatly appreciated!
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 am using sql server 2005 entreprise edition. I am just wondering when I have a report with parameters and the report uses stored procedure to retrieve the result set for use in the report, if two people from different places are trying to view the report at the same time, and each of them use different set of parameters.
Will they be able to view their report correctly??
I have a report that I would like to run in the evening due to high database usage that required a user to fill in selected parameters. Is there anyway to allow a user to fill in the report parameters and have it scheduled to run at 3:00 AM.
In VS2008 SSRS, when creating a dataset, only the name of the stored procedure was required. When populating the dataset, VS prompted for the parameters, whether or not there were any. In VS2010, the name of the stored procedure is not enough if there are parameters.When creating a dataset in Visual Studio 2010 for databases and stored procedures in SQL 2012, I must provide (what should be) run-time parameters to obtain a field list and then to preview the report.Â
So the embedded dataset looks like this (wherein the only parameter is a password): exec dbo.spu_procedure1 @PW='999'.This works well and as expected during report design and development. But 999 must be provided at run-time by the requester, and not in the dataset definition. If I remove the parameter in the dataset's exec string before deploying the report, the field list is cleared and the report is unusable. So I cannot overcome what seems a contradiction: necessary to obtain a field list, but "yet-to-be-provided" when the user selects the report.
By keeping @PW='999' in the dataset specification, the user cannot override the parameter and automatically has a password to view the report. Â
I have a report which takes around 5 seconds when run in BIDS but takes around 20 seconds when deployed on report server.Execution log says TimeDataRetrieval is around 3-4 seconds and  rendering time is around 15-17 secs.From this report I am passing 8 parameters to a  drill through report and there are 36 text boxes where I have defined these parameters for drill through action.All these parameters are populated in main dataset.When I deployed the same report without any drill through action and parameter, it takes 5 seconds.So I am suspecting that because of drill through parameters report is taking more rendering time on server.I am using 2008R2 and IE11.
Is it expected behavior that due to so many parameters for drill through action, report will take more rendering time?If yes, then why is it not taking same time in BIDS?
I'm pulling data from Sharepoint via SOAP (GetListItem).
The data pulls correctly. In the VS tab, when I execute the dataset, I can see multiple values and I see what I expect to see.
The problem arises when I actually use the report. I have a single Report Parameter (note that I have no parameters defined in the dataset):
Name: Num Data Type: String Prompt: # Allow Blank Value
From query Dataset: SPList Value field: ows_Num Label field: ows_Num Default Values: Null
So the report prompts the end user to drop down a box containing a list of items (displaying "Num"). This is exactly what I'd like to see. However, if they select any report, it only displays the first one in the Sharepoint List. No field in the report is set to =First(blahblah). They're all =Field!blahblah.
In addition to this, I have a field in the same Sharepoint list that returns a numeric value (nnnn.nnnnnnnnnn). I want this to be a currency format. I've right clicked the text box -> Properties -> Format -> Format Data and entered C. This does not change the output on the report. It remains a value without a dollar sign and with >2 decimal places.
I'm having the same issue with a datetime field. Sharepoint enters a datetime as mm/dd/yyyy hh:mms. I need to drop the "hh:mms", but again, when formatting the box as d in the Report designer, it still renders with the time.
Am having difficulty with report/query parameters, where the report now regularly tells me that I must declare the scalar variable @Site.
I want to use 3 data sources :
1. to select a site from a list of sites - works fine and I can select at runtime
2. Once this is selected - I need to present a pair of dates to the user - min and max for data for the site - needs selection 1 to have been performed. Now I HAVE seen this work - once, the date selectors were greyed out until the site had been chosen, then they became available. Now I get the scalar variable error.
3. Finally I will pull the data with 3 parameters (site, startdate, enddate)
This was almost working, and the detail was produced for item 3, until I introduced the date selection option. Now neither item 2 or 3 will accept the users selected site - its from a drop-down.
All 3 queries are being performed by SQL SP's :
1. exec getsitelist - used to populate the dropdown to select @Site
2. exec getdates @Site - used to preset the start/end dates (NB I would really like calendar control here to select the date, with the value pulled from 1 to set the start point - but hey lets walk first ;-).
3. exec GetData @Site, @Start, @End
SOooo - can SSRS2005 even support dependent parameters of this type ??.
If so - whats the best way to create the parameters etc. ??. NB I can see all 3 parameter defs in 'Report Parameters'.
In the data tab of my report (Reporting Services for SQL Server 2000), I'd like to run a query that requires parameters be passed to it. Is there a way to pass the parameters of the report to that query?
We are using xml to pull data from a custom sharepoint list into sql 2005. We have set a parameter that allows the user to filter the data by surname, however when the user tries to filter the list the drop down box brings up a list of every record, so there are duplicate entries for each surname.
Is there a way of filtering this so that there is only one instance of the users surname instead of it showing all the records?
How can I pass a parameter used for the DataSet query? I'm using this DataSet file as data source of my .rdlc report for Windows Forms.
I've already done a .rdlc report to Web Forms where I passed the query parameter by ObjectDataSource.SelectParameters, but in Windows Forms this object was not created. I just hava the following created objects:
I am using this custom data extension from BI Studio.
Que1: I want to create report parameters from BI studio and capture them in my Data Extension.
Que2: Every report parameter has so many properties, like hidden, internal, default value etc, how do capture that information in my data extension, the parameters only give me name and value.
Que3: I know I can capture command Text. So My command can be Select * From tablename where fieldname= @myFieldname. Once I get this commandText in my data extension, How do extract parameters of the command?
Que4: If I have any custom attributes in my RDL file, Can I capture them in my Data Extension ?
I have a report I want to modify and when I go to deploy it I get:
Changing the report parameters or data sources to the values you specified is not allowed. The report is configured to run unattended. Using the specified values would prevent the unattended processing of this report
Now I understand the error, it is because on the report server's web management interface I entered credentials for the report to run that point to a custom datasource and supplied the appropriate credentials to run the report.
However, I don't want to have to reconfigure that just to deploy a slightly modified version of the report.
How can I get around this? Or what is the proper work around? I just want to change the background color from dark gray to light gray on a header column....
I am attempting to pass report parameters to my query. My report's data source is a WCF web service. I can run normal queries fine, but I have found that without the know-how to pass parameters to my query I am limited with my capabilities.
Assume a string parameter named "Name" with a specified default value of "Jmachol90", how would I pass that into the following query at the designated place: Â Â <Query> Â Â Â Â Â Â <Method Namespace="http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09" Name="SecurityPrincipalsGetRequest"> Â Â Â Â Â Â <Parameters> Â Â Â Â Â Â <Parameter Name="Criteria" Type="XML"> <DefaultValue>
It's well known issue, that one can't use any dataset fields in a report header/footer directly. One of the approach is to create query-based parameter that basically equals =First(Fields!@FieldName@.Value, "@DataSetName@") and use that parameter value instead. But it doesn't work in my case!
My report displays some entity description and is parametrized with EntityID param. Its header contains entity name that, according to the approach, is queried from the data source through the EntityName report parameter. There's important issue: the report is displayed in ReportViewer control, that is embedded into my application and entity ID parameter isn't ser by user in ReportViewer parameters area. Its default value is changed by the application with SetReportParameters() web method every time a user wants to view the report according to the entity the user is exploring in the application. But after the report has been rendered, its header always contains not actual (outdated) entity name. Nevertheless, the report body contains actual data (including entity name). If I alter entity ID parameter in ReportViewer or in web-based Report Manager and refresh report, header displays correct entity name.
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.
* 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.
