I am trying to find the best way to use a date parameter on a OLAP report and get the calendar pop to be used instead of a dropdown.
Whenever I change from string to datetime for the parameter I (of course) get an error about different data types, since OLAP is returning the fields as strings and formatted.
Any easy (or best) approach to use to get a calendar pop to work with an OLAP report? I am mainly doing this for date range searches and do not want people to pick from huge dropdowns for dates.
I'm using Cube.My DimDate tables Datekey is integer .But client wants a calendar to set date as you see in picture .how can I change calendar value which will be input by user.
So far so good. I want to send this report by email everyday, so I need to set these parameters as a default value. This is where I run into problems.
When I add any of these parameters I get the following error "The restrictions by the CONSTRAINED flag in the STRTOSET function were violated". This problem occur when I choose NON-queried under Report->Report Parameters, and set a default value for THISYEAR under DEFAULT VALUES
I have a basic SSRS report against an SSAS database with a "start date" parameter. I want to set the default value of that date parameter to Today's date. What is the easiest way to do this? I have no problem doing it against a relational source, just haven't done it against an OLAP source.
Does anyone know if this is possible right out of the box in SSRS 2005 against an OLAP data source?
I have several parameters. My second parameter is to be filtered based on the first parameter (kinda like cascading), but how do I do this against an OLAP data source? Lets say I have param1 and param2 in a dataset. I want Param2 to show the locations only based on what I select in Param1. Same but a little different: I have Parameter1 and then my second parameter (Param2) is a boolean (True/False). I want to show Parameter 3/Paramater 4 based on selection of Param2 (So, if true, show Param3, if false, show Param 4) and remember we are doing this in a sequence. Can you do this thru SSRS? Any help would be great. Thanks for your time in advance. Kent
We have noticed that the calendar months in the Pop-Up Calendar option, based upon month word length, will shift in size and this will cause the left and right arrows to shift. For example, 'May' is much shorter than 'December'.
My user is complaining because she cannot quickly move from one month to another without having to reposition the mouse over the navigation arrow.
I'm creating a report with a Datetime parameter. There's no available values, so the calendar control comes out automatically when I run/preview the report. Now, when I select the date in my calendar control, say for example I select 2/5/07 (2nd of May) and run the report, it gives me data from 5th of Feb. Can anyone help me with this date format problem ???
Not sure if it's related to my problem, I did edit the language tag in the XML source code of my report to have en-AU so that the dates field is showing in the correct dd/mm/yyyy format.
Hi, I've got this ssrs 2005 report that works great passing a few security related parameters from asp.net codebehind. However, there are two date related parameters that won't be coming from my web form, but rather from the report form itself. When I test the report's date parameters from visual studio it work fine, but when I attempt the same report from a reportviewer no matter what input I place on the report's date fields or even if I select the date picker, the report simply resets to default and reloads. And actually the date picker from the reportviewer does not not even pop up.
Partial Class _ReportViewer Inherits System.Web.UI.Page Private Users As New Retailer.Core()
Protected Overrides Sub OnLoad(ByVal e As EventArgs)
Dim Roles() As String = GetRolesForUser(Page.User.Identity.Name.ToString)
Dim cred As New Retailer.ReportServerCredentials("myuser", "mypassword", "mydomain") ReportViewer1.ServerReport.ReportServerCredentials = cred
Dim param As New ReportParameter("r_user", Page.User.Identity.Name.ToString) Dim param2 As New ReportParameter("r_role", Roles(0)) Dim p() As ReportParameter = {param, param2}
on my reportviewer form, at the top I have parameters for startdate and end date, they are not set to internal or hidden.
If I select the calendar icon, i get a javascript error
Line: 606 Object Required
When I attempt to debug I get a Just-In-time failed : Unspecified error. Check the documentation index for 'Just-in-time debugging, errors' for more information. So pretty much I can't see the error or javascript in question.
If i enter anything in the date fields, it disregards them setting them to the defaults.
Again, if i run the report from my vs.net client (not using reportviewer), i can select the calendar and enter dates and it respects them.
Any chance I need to patch sql server or reporting services? maybe ie. I'm on IE 7.0.5730.11 Could my problem be that selecting items on the report itself fails to send my credential information from my codebehind?
I have thought about this, and I cannot in my head come up with a way to create this. Maybe I am thinking about it too much, or maybe it is this difficult and I will need a 3rd party tool.
My users have requested a SQL Reporting Services report that looks like a calendar. Each page will be one month on the calendar. And then the data will fall on the calendar according to a date field in the database. It should not be all that difficult, but I cannot think of a way to acheive this.
Has anyone tried to build a report that is basically just a calendar, and each page is a month of the calendar? If so, did you build it using the standard SQL reporting services tools, or did you purchase a 3rd party vendor?
I'm modifying a report that uses a date parameter as a report filter. The original report had no restrictions on what dates may be entered, so it displayed a neat Calendar picker tool for use in selecting the date for the parameter.
Thing is, this new version needs to have the dates limited to only those available in the source data. So, when I provide a query to describe the available values in the parameter properties window, instead of the nifty Calendar picker, I get a texbox dropdown list. [insert sad sound here]
I was hoping that it would still provide the Calendar picker, but with available dates highlighted in bold or some color or the unavailable dates greyed out, something along those lines; not an unimaginative dropdown list. To define the available values, I use a very simple query;
SELECT DISTINCT Load_Date FROM Census ORDER BY Load_Date
Is there a way to get it to display a Calendar tool rather than the dropdown list, if the parameter is given a list of available dates?
We have developed several reports using VS 2005 and deployed them to our SQL 2005 reporting server. As most of our reports use date parameters we would like to incorporate a calendar control to allow users to select their date range as opposed to typing them in. I have done some reseach online but can only find vague references on how to do this. Has anyone out there been able to successfully deploy a calendar control onto an SQL report? Thanks!
Blank Field Blank Field Blank Field Blank Field Blank Field Blank Field Blank Field
The user would enter the start date, in this case the 1st Jan 07 and then this would populate a table. This seems like it should be so simple but I can't work it out, can anyone help please?
I am facing one problem with reporting services 2005 (SSRS), When i try to pass a parameter to Cube which is built in SSAS, i can not use a parameter where i can type the value instead of choosing it from a dimension.
Any way we can type the value for parameter, instead choosing from the drop down? If can, then how can we create the parameter? and how can i write a MDX to read it?
I am trying to create a calendar style report that will have 12 months (as columns) and store opening listing in rows. I have created a matrix, but the problem that I have is that the store opening listing displays in the right period, but they are not in any order. I would like to have the openings always on top, right under the header in the matrix. Now I have them scattered randomly all over the matrix. I tried numerous way of sorting and that does not work.
I am attaching a sample of what I would like to accomplish (months are columns).
I am developing a report against OLAP. I have a main report and a sub report. The parameters to both are exactly the same. I am passing parameters to sub report from main. Whenever I use Field!Fieldname.Value as a parameter value to the sub report, I get an error saying one or more parameters required to run the report have not been specified. I used the UniqueName, but that doesnt do my grouping right ( I dont think this works for multi-value report)
I am lost...I need to figure this out ASAP. Does somebody know an article or do you know as how to deal with OLAP reports as sub reports and passing parameters as multi-value? I tried to attach the code, but I ran out of maximum characters...
I prepared an OLAP cube for the report data source in the SSAS 2005. The OLAP cube consists of more than 20 dimensions and several measure groups. I then created the subset/view of the OLAP cube using the "Prepective" function and limit to not more than 7 dimensions on each of the subset. How do I reference the OLAP cube subset as the data source when developing the report in the report designer. Furthermore what is the advantage of creating multiple smaller OLAP cubes with less dimensions comparing to one big OLAP cube with several subset/view attached to it. Thanks.
I am using SSRS 2005 to develop this OLAP report. I have the "Distinct Count" as a measure and some dimensions in the OLAP cube. I assigned one of the dimension to the "row" grouping and use the wizard to add the "subtotal" to that row level dimension. However it adds up individually the "Distinct Count" values from that row level dimension instead of the "True Distinct Count" of all attributes of that dimension combined. In my report there are "column" groupings so that by creating another dataset just to calculate the distinct count seperately in the report will not work unless there is a way to link this dataset to the column grouping of the first dataset. Also I don't want to create this dataset as I need to pull in the individual "id" field in the dimension in order to do the "Distinct Count" in the report and it is slowing down the performance. Thanks.
Please tell me how to create Data Source and Data Source View in Report Model Project for OLAP Cube.Whats the provider to use while creating Data Source in Report Model Project to make connection to OLAP Cube.
I have a problem with a reporting services 2005 report. The data source is OLEDB for OLAP 9.
The report contains all the members of a parent child dimension. An example of the implementation is defined in the post following the forum msdn: Http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=138549&SiteID=1.
The report works fine in web. The problem is when the report is exported as Excel, the groups disappeared and the entire dimension is ragged down.
Normally toggled groups in reporting services are exported to Excel with the appearance '+' or '-' on the left side of the sheet. This feature is very useful to hide / show lines.
It seems that with the implementation specified in the msdn post above, It is not possible to export correctly the report in Excel.
So my questions are:
Has someone already encountered this problem? Are there solutions with a different implementation?
I'm trying to get empty cells from an OLAP cube to display in a report designed using the Report Builder. Of the three report types, only the Table report shows the empty cells. Is there any option/setting that I need to enable to get this to work for the Matrix or Chart reports? Something like the "Show Empty Cells" option that is available in the pivot table used by BI Dev Studio?
Our OLAP environment involves an ETL/Data Warehouse/Data Mart server and a cube publisher server. We would like to learn how to automate the Archival/Restore of OLAP databases. We are currently doing it manually though OLAP Manager. Any help would be appreciated. Thanks. James.
-- James E. Bothamley Senior Database Administrator Dave & Buster's, Inc. 2481 Manana Dallas, TX 75220
Work Phone (214) 904-2296 email jbothaml@DaveAndBusters.Com
"Once in a while you can get shown the light in the strangest of places if you look at it right"
Hello, since a couple of days I'm fighting with RS 2005 and the Stored Procedure.
I have to display the result of a parameterized query and I created a SP that based in the parameter does something:
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON CREATE PROCEDURE [schema].[spCreateReportTest] @Name nvarchar(20)= ''
AS BEGIN
declare @slqSelectQuery nvarchar(MAX);
SET NOCOUNT ON set @slqSelectQuery = N'SELECT field1,field2,field3 from table' if (@Name <> '') begin set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + '''' end EXEC sp_executesql @slqSelectQuery end
Inside my business Intelligence Project I created: -the shared data source with the connection String - a data set : CommandType = Stored Procedure Query String = schema.spCreateReportTest When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.
Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter... So inside"Layout" tab, I added the parameter: Name allow blank value is checked and is non-queried
the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!
I had thought that this was possible but I can't seem to figure out the syntax. Essentially I have a report where one of the parameters is populated by a stored procedure.
Right now this is easily accomplished by using "exec <storedprocname>" as the query string for the report parameter. However I am not clear if it is possible to now incorporate User!UserID as parameter to the stored procedure. Is it? Thanks
I have a report parameter called para1 which is a drop-down list and what I want to do is display another report parameter based on the para1 selection.
So for example, para1 contains a, b, c choices. if a user selects b, I would like para2 to display but if the user selects a or c, I don't want the para2 to display.
I'll go to a dataset, open up the query designer, add a new parameter, then refresh the fields, but the parameter won't be added as a report parameter. If I go to the dataset properties under the list of parameters, the value in the dropdown will be blank. However, sometimes this will automatically add.
Is this a bug in Visual Studio? How do I get around this?
I have created a menu report listing available reports and am setting the Jump to settings of each text box in the list to take the user to the relevent report. But I am having a problem setting up drill down to reports with multivalued parameters.
What I want to happen when the user drills down is for the report to open up with no parameter values selected. This works for single value parameters where I have set Null as the default value. But I have not been able to figure out how to do this with MVPs. Does anybody know if this is possible?
As a workaround I have been using Jump to URL (rather than Jump to report), which has no problem opening the report without having to pass any values for the MPV. But I do not like using this as I use different DEV/UAT and PROD environments so would have to amend the URL for each environment.
Seems like this should be possible... Any idea?
Cheers, Mike
PS: I should add I can Jump to reports with MVP without specifying a parameter in development studio but not when the report is published.
OK, I'm pretty sure that the answer to my questions is "no", but the boss is pushing, and I thought I'd double check.
I want the selection of one report parameter to decide what shows up for another report parameter. Specifically in my case it has to do with dates. I have narrowed the results down to monthly dates, meaning that the starting dates always start with mm/1/yyyy and the ending dates are always mm/31/yyyy (for a 31 day month).
The boss wants the ending dates to always be >= the month of the starting dates. So, if the user choose 1/1/2007 for starting, the minimum ending date would be 1/31/2007, etc.. Since the parameters are loaded at report initiation, and SSRS isn't really event driven, I didn't think this was possible. I just wanted to make sure before I tell the boss that it's not. Please advise.
I have a fairly complex report that was given to me that was hard coded for single parameters. There is a dropdown for each market (created from a query in SSRS). The users have to run for each market each week.
Is there a way to use this report as a Sub-report inside a list of a master report and then use a mult-value parameter?
I want this multi-value parameter to build the values for the list and then run the "sub-report" for each value.
