Report Builder - Filter Drop Down Order Not Always Correct
May 14, 2008
My Problem
=========
I have a problem with a parameter drop down that is on my report. On one report server (i.e. a SQL Server Reporting Server instance), the drop down data is ordered correctly. On another, it appears to be random.
The report is created using Report Builder v9.0.2047.0.
I have used SQL Server Profiler and I have found that an "order by" clause is appended on one of the report servers, while it is excluded on another.
About my Set-up
============
I have configured multiple report servers (i.e. SQL Server Reporting Server instances) on a single SQL Server 2005 instance. The SQL Server 2005 details are:
productversion = 9.00.3054.00
productlevel = SP2
edition = Standard Edition (64-bit)
Another post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1379591&SiteID=1) puts this problem down to SP2. However, both my report servers are on the same SQL server instance.
I also see that Bob has a blog on a possible solution for this: http://blogs.msdn.com/bobmeyers/archive/2007/10/11/sorting-the-values-in-parameter-dropdowns-in-report-builder.aspx.
However, I would like to understand why the report is behaving differently.
View 2 Replies
ADVERTISEMENT
Mar 7, 2007
Hi All,
Hope someone can help here. Since installing Service Pack 2 onto our SQL 2005 instances our report models are not working correctly. When selecting fields within the models as filters, and getting them to prompt when the report is run, the ordering of the data within the filters does not match the selected ordering in the model definition. The ordering is random each time the report is run.
This issue does not happen when setting the filter up, the data appears in the correct order, but when running the report the filter data is incorrect.
I have spent the last 4 hours building a new system and testing this with each stage of service packing. The RTM version of SQL 2005 is ok, SP1 is also OK, but when SP2 is applied the ordering fails.
Any ideas?
Many thanks in advance.
Regards
Nick
View 4 Replies
View Related
Mar 26, 2007
Can anybody please let me know if cascading parameter feature of Report Designer can be implemented in Report Builder in any possible way (using filter or report parameters).
We have a scenario where the end user wants to create an adhoc report with two input parameters (or filters). Both the parameters will be selected from a list of available values at run time. The problem is that the second parameter's list of values should be populated based on what is selected in the first parameter.
Thanks in advance.
View 3 Replies
View Related
Jan 10, 2008
Guys,
In my report builder when I drag the SKU in the filter area, its doesnt give me the dropdown and show me the distinct values, it just gives me a text box and expects me to type in the SKU.
I have other attributes whcih have the same property as SKU but they all work fine when I drag them into Filter area.
Is there a limitation in reporting svcs where if the Field has more than "n" number of distinct records it will not populate the dropdown, instead just present you with a text box?
My DataModel Table has around 110K records and roughly 7K distinct SKU's
I compared the SKU field to other fields in the Report Model Designer and also DataSource View and nothing seems to be out of normal to not show the dropdown with distinct values except for the # of distinct records.
Appreciate your help in advance
View 4 Replies
View Related
May 17, 2007
When applying a filter in Report Builder the filter will timeout when loading data for the filter. It does not happen all of the time seems to happen more often during the business day. Has anyone else run into this probelm? Is there a way to increase the timeout?
View 6 Replies
View Related
May 2, 2006
Hi
Im creating a filter in Report Builder with a start date and an end date. That is I want the user to able to choose a dateinterval. How do I change the label shown in the report for the interval. I want it to say Startdate and Enddate not what I have in my Sql server
Thanks
/Stefan
View 3 Replies
View Related
Oct 18, 2007
I have built a data model in visual studio that I am using with report builder in reporting services. The data model is using data from a view in SQL Server 2005. I am trying to apply a filter to the data model to restrict the data available in report builder. I know that I could change the code for the view and filter that data that way, but I would rather creates two models each one producing differnt sets of filtered data. I have tried adding a filter to the data model, but it does not seem to work. Do anyone have any suggestions?
View 3 Replies
View Related
May 6, 2008
when i use a filter in report builder, it's not able to retrieve the information to populate the dropdown menu, the dropdown menu is empty. I get a triangle with exclamation mark and the following message and the drop down box is not being populated:
the requested list could not be retrieved because the query is not valid or connection could not be made to datasource
I am able to generate reports but not able to use filter. what could cause this issue ?
Chi
View 13 Replies
View Related
Oct 26, 2007
I am trying to use a wildcard in a Filter condition within a SSRS - Report Builder report.
Are wildcards of anytime in fact allowed in this tool? I get errors when I try to use SQL-like wildcards such as '%'. I've also been unable to find any mention of "wildcards" in HELP.
Please help as soon as possible. THANKS!
View 7 Replies
View Related
Apr 25, 2007
Hello,
is there a way, how to build drop-down parameters in report builder when creating a report based on SSAS report model ?
View 1 Replies
View Related
Apr 23, 2007
In Report Builder, can you link prompt/filter fields to one another? For instance, I have Users and Groups and I want to dynamically fill the dropdown for Users based on the Group(s) chosen in the group prompt/filter. Or show the hierarchy as a expanding tree list for navigating the hierarchy to choose the user/groups to report on.
Another example would be product category & subcategory in adventure works.
And just to throw in a wrench, one of the areas I want to do this has 4 levels to the hierarchy.
Thanks for the help,
Toni
View 3 Replies
View Related
Jul 9, 2015
Created Prod order status report, in status, we have different status
created =0
start =4
released =3
reported as finished =5
ended =7
I have the report, in report don't want to show the Prod order for ended status, how can I add the filter for this so it can show for all the other status not ended status. when I did on filter <7 , it did not work
View 4 Replies
View Related
Dec 14, 2006
I have a date filter, and I default it to first day this month and last day this month under relative date, when I run it it givis me error:
The Value expression for the report parameter €˜FromDate€™ contains an error: [BC30456] 'Date' is not a member of 'Integer'. (rsCompilerErrorInExpression)
Can anyone fix this problem?
But it works for Today or (n)months ago.
Thanks.
View 6 Replies
View Related
Feb 6, 2008
Hi,
When i select datasource in Report Builder, i am able to see all the available DataSources.
Eg: I have selected one datasource from the list and which has 3 tables(table1, table2, table3) associated to that datasource.
when i drag and drop table1 fields to report, i am not able to see the other 2 tables(table2 & table3)
Is there any property or relationship do i need to maintain?
Thanks,
SR.
View 5 Replies
View Related
Nov 12, 2007
Good morning all,
I have searched everywhere for this and I can't find any information on it. When I use the Query Builder, in the top pane, the Filter pane, there are five columnsimension, Hierarchy, Operator, Filter Expession and Parameters.
In the dropdown for Operator, one of the choices is MDX. I cannot find any documentation on how to use this operator. I want to limit my filter to SELF_AND_AFTER, and I am hoping that this is possible using this operator. I know how to hand code it, but I have a ton of other tweaks to make to the query, so I want to use the Query Designer as much as I can.
Does anyone have any links to documentation on how to use this operator?
Thanks,
Kathryn
View 2 Replies
View Related
Apr 18, 2007
I'm using a cursor in SQL Server 2000 to assist me in calculating for each store, the Sales Rank of a zip code. There are about 1500 stores, and 125,000 store/sales/zip code records.
I am finding that this works for about 95% of the stores, but about 5% are getting fouled up, where the store's records are getting split in the sequencing, and so the store ends up with two zips ranked 1, two ranked 2, two ranked 3, etc.
In the DB structure, there is a constraint restricting one record per store (org_id) per zip code (postalcode).
Here's my code. Basically what I'm trying to have the cursor do is go through the table, ordered by org_id (store) asc, org_criteria_value (sales) desc, and rank the zip codes. When a new store is encountered, reset the counter to 1 and start ranking again. Do this until all the records are processed.
/*CREATE Sales_Table table */
CREATE TABLE [dbo].[Sales_Table] (
[count_id] [int] NULL ,
[org_id] [int] NULL ,
[postalcode] [varchar] (20) NULL,
[sales] [numeric](18,6) NULL ,
[sales_rank] [integer] NULL,
[org_criteria_input_date] [datetime] NULL
) ON [PRIMARY]
insert into Sales_Table
select 0 as count_id, omd.org_id, omd.postalcode, omd.org_criteria_value, 0 as cum_rank, org_criteria_input_date
fromorg_model_data omd
join org o on o.org_id = omd.org_id
where o.client_id = @ClientID
and model_Criteria_id = 27
and org_criteria_value <> 0
order by omd.org_id asc, omd.org_criteria_value desc
-- DECLARE CURSOR for Sales_Table
declare SalesRankCursor CURSOR
SCROLL dynamic FOR
select org_id, sales, sales_rank
from Sales_Table
for update of sales_rank
-- CREATE LOOP TO UPDATE SALES RANK in Sales_Table with valid values
OPEN SalesRankCursor
while exists (Select * from Sales_Table where sales_rank = 0)
Begin
FETCH NEXT FROM SalesRankCursor
set @StoreNext = @StoreCurrent
set @SalesRank = (@SalesRank + 1)
update Sales_Table
set@StoreCurrent = org_id
where current of SalesRankCursor
if @StoreCurrent <> @StoreNext
begin
set @SalesRank = 1
end
update Sales_Table
setsales_rank = @SalesRank
where current of SalesRankCursor
End
CLOSE SalesRankCursor
DEALLOCATE SalesRankCursor
Any ideas?
View 5 Replies
View Related
Aug 15, 2007
Hello!
I have an SSIS package, run by the DTExec utility, each night. When I run my package, I assign a value to a variable (package scope) (/SET "Package.Variables[User:: psRunNo]";0154). (Note: all the variables here are strings).
Value for variable psRunNo = 0154
In the package, I have another package variable called S_SOURCE_FILE_NAME, which is an expression and that uses the variable psRunNo.
Expression for S_SOURCE_FILE_NAME = @[User:: psRunNo] + "_{TABLENAME}.txt"
So, at the beginning, the value of variable S_SOURCE_FILE_NAME is 0154_{TABLENAME}.txt with that example.
Then, I use the variable S_SOURCE_FILE_NAME in the expression used to assign the ConnectionString property of a Connection (a source file), like this:
REPLACE( @[User:: S_REJECTED_ROWS_FILE_NAME] ,"{TABLENAME}", "STADDRES")
So here for example, the final value for ConnectionString would be 0154_STADDRES.txt
Everything works nice, most of the time. Sometimes (intermittent problem), the value of the ConnectionString for one of the many Connections I have in the package is not assigned with the right value of psRunNo. The ConnectionString gets the value of psRunNo which is saved into the package (when it was deployed) instead of the value of psRunNo passed with the DTExec.
It is like if the ConnectionString value was computed before the variable psRunNo (and S_SOURCE_FILE_NAME expression) was assigned to the new value, but only for one of the connections (all my connections use the same kind of expression for their ConnectionString property).
Does somebody had similar precedence problem? Is there some settings I could use to indicate a precedence in assignation of variables ? Where does the "natural" precedence in assignation comes from in SSIS ??
Thanks!
View 5 Replies
View Related
Sep 29, 2015
I have a report that displays its date dropdown badly: 10AM,10PM, 11AM, 11PM etc when it should be 10AM, 11AM, etc. The used measure is Hour Name which looks like "01AM" "02AM" etc. And here is the current query
WITHÂ
MEMBER [Measures].[ParameterCaption] AS [Report Verified Time].[Hour Name].CURRENTMEMBER.MEMBER_CAPTIONÂ
MEMBER [Measures].[ParameterValue] AS [Report Verified Time].[Hour Name].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS [Report Verified Time].[Hour Name].CURRENTMEMBER.LEVEL.ORDINALÂ
SELECT {[Measures].[ParameterCaption],
[Measures].[ParameterValue],Â
[Code] ....
View 2 Replies
View Related
Jan 17, 2007
Hi,
SQL Server Reporting Services 2005.
I have 13 parameters, ordered correctly within the Report Parameter screen.
When displayed in the Preview tab they are all ordered correctly, but when viewed in the application the first 4 are at the top but ordered incorrectly. The remainder are ordered correctly.
I have tried reordering, saving, deploying, viewing and then doing the same but in the correct order without any success.
Please can someone suggest how I can get the parameters to appear in the correct order within the application?
Thanking you,
dwemh
View 6 Replies
View Related
Nov 9, 2000
Hello:
We are presently testing various upgrade scripts to our current application which is scheduled to shortly be upgraded to mssql 7.0. We are testing under mssql 7.0,sp 2.
As it works now, I receive some existing scripts that have been modified and some stored proceures that are new. For the existing stored procedures, I usually take my best guess as to what the order of creation will be, test it in my script for recompile(actually all are dropped first and then the guesswork on the creates). This is usually trial and error as I run the script, see any sysdepends errors such as:
"CREATE PROCEDURE: ep_invoiceheaderformat_spv0101
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'ep_assumepay"
And then move the order of the create procedures around in the script and try again until I get a clean run in a test database I use just to syntactically test the scripts.
I looked at the sysdepends table for the database and pretty much decided that the object numbers and stuff was pretty much incomprehensible to me.
Alternatively I could compile each one separately but I would have the same problem subsequently trying to generate a script of the al of the create procedures... in the right order which would not
My question is:
1) Is there a way I can read and understand what the data means in sysdepends?
2) Figure out a way to utilize the data there to create or generate the create stored procedure text in the correct clean compile order?
3) Any other suggestions?
Any information which can be proveded will be greatly appreciated. Thanks.
David Spaisman
View 1 Replies
View Related
Sep 9, 2015
I just created a report builder. I have a main report and i wanted to create a sub report. why i cant or i cant view the path or the folder of my .rdl file to be use as my sub report.
View 5 Replies
View Related
Jun 30, 2006
After I use the report builder to create a generic report, how do I actually get that report into the report designer so that I can modify it more effectivly?
The issue that I have now is that the file on the report server is not a .rdl file and if I simply save it as one and then bring it into VS to modify it the code file is a html structure rater than a XML file type.
Any suggestions would be appreciated. Thanks
View 3 Replies
View Related
Sep 6, 2007
I've created a linked server with a pretty basic Excel spreadsheet, and used this command to create a linked server to it:
sp_addlinkedserver ''XL_SPS_1', 'Excel', 'Microsoft.Jet.OLEDB.4.0', 'c:MyExcel.xls', null, 'Excel 8.0'
I want to use this as the data from which to build a report model. As linked servers don't show up in the Data Source View wizard, I created a view in SQL Server:
create view MyExcel
as
select * from XL_SPS_1...Sheet1$
Okay, great, now the view shows up in the DSV wizard and I can create the data source view. However, when I create a new report model based on this data source view, the Report Model Wizard tells me at "Create entities for all tables" that I've got an error when it processes dbo_MyExcel that "Table does not have a primary key."
I assume this is where the identifying attributes for the entities in the report model are taken from, so I really can't go further. Does anyone have an idea as to how to add a primary key to a linked server (Excel) in SQL 2005? Can this be done? Other than importing spreadsheet data to a SQL table, how can I get around this?
Thanks,
--Stan
View 3 Replies
View Related
Feb 16, 2007
We have a remote business client who has a proxy server setup and this translates into receiving a 407 proxy authentication blocking error when attempting to download the report builder application for deployment off the report server which is housed locally with us. The proxy server does not allow them to download application type files off another site. At the moment, they would not like to disable the proxy for these users to allow them to deploy the application on their client or add the report server as a trusted site. Is there a method by which Report Builder can be locally installed on the client pc instead of online only with it knowing the correct report server to access and then still launch correctly when the report builder button is pressed through report manager?
I am not a web application guru by any means and we have tried to replicate the oneclick deployment and what occurs in the local settings/apps/2.0 subdirectories, but the application still attempts to download/install again. If we attempt to just launch the local executable on the client, report builder assumes the client pc (localhost) is the report server to access. Is there a method which will succeed?
View 1 Replies
View Related
Jul 17, 2007
Hi,
I am unable to launch report builder from the report manager from a client machine. But I am able to launch from the localhost.
Even I am able to launch report builder by clicking reportbuilder.exe on the local machine.
Please help !!
OS - Windows XP professional
SQL Reporting Services 2005.
View 1 Replies
View Related
Dec 15, 2006
hi
I have installed SQL Server Reporting Service on window xp. everything working fine except one thing.
I have installed sql server with my a/c. my a/c have admin rights. when i giving http://dineshpatel/reports it is displaying page but Report Builder and other option are not displaying. I hope i don't have admin rights.
I have checked with local administrator login also but same problem.
what additional setting are require for admin rights?
Dinesh Patel
View 10 Replies
View Related
Oct 28, 2015
I have a report builder drill down report. I have row groups with totals.   It looks like the attached.  The problem is when the report is not expanded the Grand Totals column is not accurate... it is displaying the totals of one of the rows when expanded.The expression in the Total Show text box is
= Switch ( Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
MID(Fields!protocol_id.Value,1,7)="THERAPY", Â Â Â Â Â Â Â Â Â Â
                     Sum(IIF(Fields!status.Value = "CO", CDbl(Fields!TX_CO.Value),      Nothing)),
 MID(Fields!protocol_id.Value,1,7) = "GENERAL" and                          MID(Fields!program_id.Value,1,6)
= "INTAKE", Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Sum(IIF(Fields!status.Value = "CO",
[code]...
Is there any way to not display the expression in the Total columns unless the report is expanded?
View 2 Replies
View Related
Feb 1, 2007
HI, there,
I added a new windows user "ReportUser" which is a local user (not in administrator group). When I connect to the report manage's web page, I use reportuser to log in (when the anonymous access is disabled it will ask a log in). Report builder icon is not showing up on the report manager home page.
If I log in as a user in administrator group, the reportr builder icon shows up so I can download it and build report.
I have assigned reportuser "Content manager" and "repoter builder" access to the home folder. Is there anything else I have to do to make the report build icon show up on the home page?
Thanks
Jack
View 5 Replies
View Related
Sep 17, 2007
I have provided required permissions, still user not able to view report builder option in his console.
what could be wrong? what needs to be done to provide report builder to users.
Deleep P
View 5 Replies
View Related
Dec 19, 2006
I have client machine in one domain, say A domain , on which I have installed .net 2.0 without any problem. I have report server running in other domain (B domain) where I have deployed few models which will be used from domain A to create ad hoc reports by launching Report Builder. I can access other reports in domain B from A
But when I click on Report Builder ..it tries to connect to server in domain B but fails to connect and shows following log in a text pop up
I know, problem is because of cross domain communication but question is then how to fix it ? what changes I need to do for that in firewall, domain etc etc settings?
Here is the error, I have highlighted lines which might give some clue:
------------------------------------------------------------------------------------------
PLATFORM VERSION INFO
Windows : 5.2.3790.65536 (Win32NT)
Common Language Runtime : 2.0.50727.42
System.Deployment.dll : 2.0.50727.42 (RTM.050727-4200)
mscorwks.dll : 2.0.50727.42 (RTM.050727-4200)
dfdll.dll : 2.0.50727.42 (RTM.050727-4200)
dfshim.dll : 2.0.50727.42 (RTM.050727-4200)
SOURCES
Deployment url : http://192.168.1.197/ReportServer/ReportBuilder/ReportBuilder.application
ERROR SUMMARY
Below is a summary of the errors, details of these errors are listed later in the log.
* Activation of http://192.168.1.197/ReportServer/ReportBuilder/ReportBuilder.application resulted in exception. Following failure messages were detected:
+ Downloading http://192.168.1.197/ReportServer/ReportBuilder/ReportBuilder.application did not succeed.
+ The remote server returned an error: (500) Internal Server Error.
COMPONENT STORE TRANSACTION FAILURE SUMMARY
No transaction error was detected.
WARNINGS
There were no warnings during this operation.
OPERATION PROGRESS STATUS
* [12/19/2006 6:49:58 AM] : Activation of http://192.168.1.197/ReportServer/ReportBuilder/ReportBuilder.application has started.
ERROR DETAILS
Following errors were detected during this operation.
* [12/19/2006 6:50:14 AM] System.Deployment.Application.DeploymentDownloadException (Unknown subtype)
- Downloading http://192.168.1.197/ReportServer/ReportBuilder/ReportBuilder.application did not succeed.
- Source: System.Deployment
- Stack trace:
at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
at System.Deployment.Application.SystemNetDownloader.DownloadAllFiles()
at System.Deployment.Application.FileDownloader.Download(SubscriptionState subState)
at System.Deployment.Application.DownloadManager.DownloadManifestAsRawFile(Uri& sourceUri, String targetPath, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation)
at System.Deployment.Application.DownloadManager.DownloadDeploymentManifestDirectBypass(SubscriptionStore subStore, Uri& sourceUri, TempFile& tempFile, SubscriptionState& subState, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation)
at System.Deployment.Application.DownloadManager.DownloadDeploymentManifestBypass(SubscriptionStore subStore, Uri& sourceUri, TempFile& tempFile, SubscriptionState& subState, IDownloadNotification notification, DownloadOptions options)
at System.Deployment.Application.ApplicationActivator.PerformDeploymentActivation(Uri activationUri, Boolean isShortcut)
at System.Deployment.Application.ApplicationActivator.ActivateDeploymentWorker(Object state)
--- Inner Exception ---
System.Net.WebException
- The remote server returned an error: (500) Internal Server Error.
- Source: System
- Stack trace:
at System.Net.HttpWebRequest.GetResponse()
at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
COMPONENT STORE TRANSACTION DETAILS
No transaction information is available.
View 1 Replies
View Related
Feb 26, 2015
I was wondering if there was any way to add a value field to a report, with the time it took for the report to Process.
It would probably be a text field with an Expression, but don't know how that would go.
I know that in Expression there is a value for ExecutionTime (when report began to run), but nothing about when it ends.Can this be done? and if yes, how?
View 3 Replies
View Related
Mar 22, 2011
Is there a way to see Report-Level variables in Report Builder 3.0? Also, just as a feedback, it would be really nice if there were a way to see and easily access all of the variables used in a report from one location instead of going to the dataset, the report, and then to the individual groups (if needed) to see the variables. Also, it would be very useful if Group-level variables can show up in the Expression Builder as well.
View 2 Replies
View Related
Jul 6, 2015
I've gotten to the point of publishing the new reports on the SSRS server and have run into the following problem.
I use a field in the primary report with the "Go to report" action to open up another report (also published) for more details. It works properly from within ReportBuilder (2.0) using the report server and I can go to and from the secondary report as I expect. Its passing the correct parameters to the second report and its runs.
But when I use the reports from the SSRS web, the Go To link no longer works. Mouse hover shows its still a link but clicking goes no where. I can run both reports independently from SSRS and they still work. Â
I've tried this in IE and Chrome with no luck in either. I also poked around at Compatability view and popup blocker but nothing made any difference.
View 2 Replies
View Related
