I am trying to report against a cube and I want the user to be able to choose the date for the report.
Now the cube dimension has been built against a calendar which contains values for the next 30 years(!) so when I create a parameterized report the list of dates is ridiculously huge.
I want to filter the dates out and show only the dates for which there is data. How do I filter out the list of dates for a parameter?
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
SSRS : I want to add parameter to my Cube from report is generating ...
View 5 Replies View RelatedHere's tricky one.
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.
Essentially, I want to create a for each loop.
Any ideas?
Hi All,
how do i hide an attributes member value in all the reports where my cube is exposed as data source.
Example : Lets say i have dimension called Product.
Product
Name column Key column
Product A 0
Product B 1
Product C 2
Now i need to create mdx query which will fetch member values except 0 key column value so that member values except 0 Key would be loaded in the parameter list. What is the best query so that i will run in to any performance issues. Is there any cube level setting i can do so that i will be doing one time job ?
Can i use Key() function to compare something like Key([Product].[Product Name]) >0 in the mdx query
ot would Except() function would help?
Thanks in advance
After connected to Analysis Services and following the path below:
Analysis Service - Databases - Cubes
I create the parameters for a custom report after right click on a specific Cube and select Browse to arrive at a view where i can define dimensions and parameters as well as drag and drop measure to the report rows and columns. The left top File bottom only give me a Save Selection or Save ALL option. However, i'm not sure where it saves it to or if it saves the settings for this custom report at all.
I pull this report monthly and it's a pain to recreate all the parameters. How to save this template for future pulls or maybe even incorporate the pull into excel for monthly refresh instead?
I have an SSRS report that gets its data from an SSAS cube, and it essentially displays a list of students and absences. Everything works fine.
But the users only want to see the top N students with the highest absences. For example, today they want to see the top 100, but during summer vacation, they only want to see the top 5.
Is there a way to add this parameter to the SSRS report? So if I select "5" from the dropdown parameter, it will show the top 5 absences.
The query that I use to populate the report is something simple:
SELECT NON EMPTY { [Measures].[Absences] } ON COLUMNS, NON EMPTY { ([Dim Date].[Date Key].[Date Key].ALLMEMBERS * [Dim Student].[Lte Cell Key].[Lte Cell Key].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DimDateDateKey, CONSTRAINED) ) ON COLUMNS FROM [Daily Cube]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Is there any windows command or any tool which would tell me what all 3rd party s/w's or Filtered drivers on my server (like antivirus, monitoring tools like Diskeeper or any I/O throttling s/w etc..)
View 3 Replies View Related
Hello Everyone,
I'm just trying something that I haven't tried before, and I'm wondering if anyone has done it before. I'm building a report with three charts. This chart is going to go inside of Microsoft CRM. One of the charts has to use CRM filtered views, so that a manager can see his whole team's numbers, but a team member will only see his numbers. I can't do this any other way because of the complexity of the query, which has 3 select statements joined together as tables. It would be too complex to try to use a parameter throughout all of those tables, and I'm not sure how I would set up a parameter to show all of the data if the user were the manager, and only the single user's data if it were someone else.
The other two charts use filtered views as well, but they have similar joins, and I had to hard code a column with meaningless data into each SELECT table so that I could use it to join, as the tables had no other similarities.
The problem is that when you upload a report into CRM, if it uses filtered views, you don't have to go into Report Manager and change the data source from a shared to a custom data source. But on reports where I don't use the filtered views, I find that they always break when upload them, because they use the shared data source. This is the case even when I have created the report without the shared data source. So I usually have to go in and change the data source from shared to custom every time I upload a report without filtered views.
Because of the situation I described, this report uses both filtered views and non-filtered views (for the hard-coded columns). When I upload it into CRM, it won't work either way, with a shared or a custom data source.
Has anyone had this problem before?
Hi,
I have parameters in my report. The user can choose the year, month and date (3 parameters).
Now I want to set default vaules for the parameters , so that the user sees the report for example for the current day without selecting the parameters. I tried to set the type of the parameters to DateTime and the default value for example for the year to "=Today().Year" . But when I execute the report an error occures . Something like : no validValue for this parameter.
My Attributes for the year month and date are from an Analyis Services Cube from a Server Time dimension .
Does somebody know how to make it possible to set default values for this parameters?
Other question :
Does somebody know how I can reduce the values for a parameter. For Example I have a parameter "year" from a server time dimension from a cube. The values which are available are "Year 2004", "Year 2005", "Year 2006", "Year 2007".
But I want that the user only can choose "Year 2006" or "Year 2007" ant not every Year or "All".
Or Other Example: The User should only choose a Date that is int the past or Today but not a Date in the future.
Thanks !
JF
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
Good morning,
I'm in the process of creating a report to show employees and managers holiday and absence information. Is there a way of filtering the results of the report based on who is running the report, so that employees could only see their own information and managers could only see theirs and their subordinates information?
What I was hoping to do was create a lookup table which cross-references Windows logins with employee numbers and then use this information to pass a parameter to the SQL query, but I don't know how to retrieve the login from the machine being used to view the report.
I've heard about row level security and it seems ideal in theory but I fear the implimentation of row level security would be far beyond my meagre knowledge.
Any constructive suggestions welcomed.
Thanks,
Paul
I have a date dimension and Fiscal Year is a Hierarchy in it, like April, August and October for different fiscal years. I need to populate this in a report (SSRS) in a combo box so that the user can pick the fiscal year he/she wants. When done, I want to know what is the Fiscal year start month is, like April for April Fiscal year etc.
How do I achieve this? Is it even possible?
I am working on existing ssrs report. When I see the preview I could see the extra parameter than the actual parameter.
How do I know , when this parameter is coming in the preview ?
I have two report , first is main report which is matrix and have one parameter User_ids which is multi value selection and my second report is basic chart of user_wise performance.
Now, my main report (matrix ) works fine for Multiple selection of users and i have putted one textbox on main report chart which has action properties set for chart report, when user click on chart button it must goes to chart with user selected in main report. Now , i have used expression for parameter to send it like ..
=join(parameter!user_id!value,",") which pass selected value to chart
And when I am selecting single user it passing that value to chart parameter list but , when it is more than one user it errors with conversion failed when converting the nvarchar value '121,128' to data type int. But my chart also works when passing 121,128 in user parameter in preview of report .
my dataset from sharepoint list. and this dataset value assign to parameter. i want when no any parameter is selected than it should filter like "ALL". when i select alow null value it give me prompt error you can not select null in multivalue parameter.How can i do it. i am using share point list.
View 3 Replies View RelatedIs it possible to fill a parameter list with values based on another parameter value?
Here's what I have so far (which hasn't worked)...
I'd like to generate a report listing information for a student. The report viewer would first select a school from the first drop-down menu, and then the second drop-down menu would populate with the list of students at that school.
I have a dataset that calls a sp which returns a list of schools (SchoolID and SchoolName fields from the database table).
I have another dataset that calls a sp (with SchoolID as the parameter) which returns a list of students for that school.
Both datasets return the appropriate data when tested individually, but when I set up the Report Parameters and build the report, these errors come up...
The value expression for the query parameter '@SchoolID' refers to a non-existing report parameter 'SchoolID'.
The report parameter 'Student' has a DefaultValue or a ValidValue that depends on the report parameter "SchoolID". Forward dependencies are not valid.
...Is it possible for the reoprt to generate a list of available parameter values based on the value selected for another parameter?
Any help you can give me would be great!! Thank you
Hi
In 2000 is it possible to list second parameter based on selection
in the first parameter list
Cheers
Is it possible to include a text/string as suffix(append to the end of textbox) to an report parameter prompt string ? Something like this
Enter Date: [ Textbox ] format:mm/dd/yyyy
Enter Age:[Textbox ] e.g 50
I need to include text like the one that I have highlighted in blue. Could someone help me out?
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.
However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).
For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).
Something like:
List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac
How can I accomplish this?
I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.
Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
In Report Designer I am able to create a Model data source and see my entities/fields.
But when I run the report I get a "Failed to execute Query" message.
I know in the past this was an issue because Designer doesn't allow you to specify a perspective.
Does anyone know if this has been resolved?
I'm using SP2
Thank you,
Joe
Request is to have a Requirement number from the requirement form generate a report in Reporting Services with the requirement number as a filter.
I can set up the parameter - how does the value get there? Should I be asking this question in the Visual C# group?
Thanks!
Terry B
I have this strange issue.
VS2005
SSRS2005
SSAS2000 cube based on data from SQL 2005 database
When i connect to the SSAS cube and create a dataset in a SSRS 2005 report i do not get a parameter pane.
Also, if i right click on a dimension & select "Add to filter" i get the following error message
"Object reference not set to an instance of an object"
It's not my installation of SQL 2005, as I can connect to an SSAS2005 cube and get the filter/parameter pane and do not get the error message. Parameters work fine.
Have i come across a known issue when reporting on SQL 2005 versus a SQL 2000 AS Cube... or can someone shed some light on the issue for me....
If someone can tell me how to display screenshots I can add them to a post
Thanks
Will
hi,
How to convert cubes , which are generated by Analysis Services, to generate end user reports like pivot tables for excell, pdfs etc. by using Report server.
ur cooperation is highly appreciated.
Satya
Hi there,
We are working on a business intelligence solution and are breaking our heads on something that is too critical for us to proceed any further and support our stance. Please help. The case is as follows.
SELECT [Month]
,[Total Sales]
FROM [TryYas].[dbo].[Yearly]
Month Total Sales
Jan 1000
Feb 200
SELECT [Month]
,[Sale]
FROM [TryYas].[dbo].[Monthly]
Month Sale
Jan 100
Jan 300
Jan 400
Feb 100
Jan 50
Feb 60
Feb 40
Jan 150
Feb 100
Say there are two tables like the ones above.. We need to know if a report model can be built so that we can build reports where the details from the first table will be shown and when the user clicks on the Month (Say Jan) details for Jan Should be pulled from the second table, something like a drilldown would also be fine.
Now, with our understanding here makes us feel that it can't be done with a report model but is possible with Report Desinger. We need to know if it is possible to have such an implementation with a report model when a cube is built or not even with a cube. Any inputs, suggestion, explanations, references, code samples, etc.. etc.. would be of GREAT HELP.
Thanks and regards,
yaseen
I want to create reports in SSRS that use cube in SQL 2000, I don't know which type of connection that i have to use,
I tested it with Microsoft SQL Analysis Services, means I connect to my cube with this type , everything is ok in developpement (create MDX query , make reports...), but in production It gives me this error message (cannot connect to the datasource),
so I don't know how to solve this problem,
Thanks in advance for your help...,
Regards
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!!
What is wrong? What I forgot??
Thankx for any help!
Marina B.
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 am using report builder 3.0.
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.
I am trying to select a group of records based on a parameter valuepassed to the db from a web page. The value comes in as @Status andhas a list of statusID's: (1,2,5,9) I've tried to use"Where table.status IN (Select * from @Status AS ValueList)"And also tried"Where table.status IN (@Status)"And neither worked. Any suggestions?
View 2 Replies View RelatedI am built the cube and processed. The fact table has 2 keys from the same dimension table.
My customers asked me that they want to browse the cube with report filters. The scenario is very strange.
They want to filter on same dimension with different conditions.
For example, they want to use the condition as ProductCode IN (...) and ProductName NOT IN (...)
How can I do this when browsing the cube in excel or any where?