Field Value As A Set Or Member To A Parameter In MDX
Jun 19, 2007
Hello Everyone,
I have report based on a cube and it is working. What it does is display a matrix based on six parameters selected. The matrix displays with the following results (there is some more, but this is the gist):
Job Level Headcount
Partners 102
Managers 85
Associates 98
By clicking on a number it properly navigates to the next report (also based on a cube) and properly passes all of the parameters. All of these paramters are dimensions.
But the rows (Partners, Managers, Associates) are not a parameter in this first report. So when I click a number and it navigates to the next report, it is not passing the JobLevel (Partners, Managers, Associates) I clicked on.
When I add the JobLevel to pass to the parameters in the second report, I can only add it as a Fields!JobLevel.Value, I can't pass it as a Dimension or a Set - which is what I assume the second report needs.
I tried StrToSet in the Navigation expression, but it doesn't recognize StrToSet in that expression.
How can I pass a Field value to populate a parameter in a second report that uses MDX for its parameters?
Thank you for the help.
-Gumbatman
View 1 Replies
ADVERTISEMENT
Feb 13, 2007
Not sure where the problem is The @bucket-size is an integer value being entered by the user.
with
set [end] as strtomember("[REL TURN HRS].[TRN HRS].&[" + @bucket_size + "]")
member measures.bucket1 as
iif( isempty(sum([REL TURN HRS].[TRN HRS].&[0] :
[end],[Measures].[FACT CUT RELEASE Count])),0,sum([REL TURN HRS].[TRN HRS].&[0]:
[end],[Measures].[FACT CUT RELEASE Count]))
SELECT NON EMPTY { measures.bucket1} ON COLUMNS,
NON EMPTY {[CUSTOMER JOB].[Cust-Title-Issue-Job].[JOB_NUMBER].ALLMEMBERS } ON ROWS
FROM [DW INSIGHT]
WHERE ([CUSTOMER JOB].[JOB_NUMBER].&[A403EZ0])
View 3 Replies
View Related
May 27, 2015
I have a requirement to show the parent value which is passed and also all the leaf member details in MDX. For example in Adventure works, if I pass Bikes as the value in MDX, the results should display Bikes along with All the child member names as a measure value in SSAS MDX. I only have one hierarchy with Product Category-> Subcategory-> Product.
I am trying to use the following MDX but I am getting all the members. I need to get only Bikes in my report.
WITH MEMBER [Prod_Passed] AS SETTOSTR(AXIS(1))
MEMBER [Prod_Child] AS DESCENDANTS ([Dim Product].[Hierarchy].CURRENTMEMBER,,LEAVES).item(0).NAME
MEMBER [Child_Value] AS ([Dim Product].[Hierarchy].CURRENTMEMBER,[Measures].[Sales Amount])
SELECT [Dim Product].[Hierarchy].[Product Category].&[1] * [Dim Product].[Product Key].[Product Key].MEMBERS ON ROWS,
{[Measures].[Sales Amount],[Prod_Passed],[Prod_Child],[Child_Value]} ON COLUMNS FROM
[AdvWorks]
View 10 Replies
View Related
Mar 23, 2007
Hi all,
I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.
I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...
"A new member could not be added to a local group because the member has the wrong account type"
I tried a lot of things... without success.
Is there someone who can help me....
PS: There is no error in the LOG
Thanks
View 18 Replies
View Related
Mar 23, 2007
Hi all,
I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.
I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...
"A new member could not be added to a local group because the member has the wrong account type"
I tried a lot of things... without success.
Is there someone who can help me....
PS: There is no error in the LOG
Thanks
View 4 Replies
View Related
Oct 23, 2014
I need to create a member that one of its Attributes (maybe my term is wrong) is a reference to another entity member named group
the code
createRequest.Members.MemberType = MemberType.Leaf;
createRequest.Members.Members = new System.Collections.ObjectModel.Collection<Member> { };
Member aNewMember = new Member();
aNewMember.MemberId = new MemberIdentifier() { Name = uag.groupName, MemberType = MemberType.Leaf };
[code]....
"The attribute data type is not valid".Is it wrong to add the reference as attribute? How can I embed the reference in the new member?
View 4 Replies
View Related
Sep 24, 2015
I have a table where I need to delete rows if the member does not exists in the member table. Will the following syntax work ?
Delete T
FROM Event_Temp_Lead_Screen T
left join member M on ( M.MemberID = T.MemberID )
where
T.Gender is NULL
View 9 Replies
View Related
May 1, 2007
Hi,
I'm using MS Report Designer 2005 and have created a report that uses a cube, with a dimension set up to convert null values to unknown (nullProcessing = UnknownMember).
When I create a parameter using the checkbox in the graphical design mode's filter pane, Report Designer automatically sets the constrained flag, eg:
STRTOMEMBER(@DimOrganisationBUSADDRSTATE, CONSTRAINED).
When running the report and selecting the 'Unkown' value from the parameter list, the error 'the restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated' occurrs.
How can I prevent the constrained flag from being used, or am I doing something wrong with converting null values to 'Unknown'?
Thanks
View 10 Replies
View Related
Jul 24, 2006
Hi,
I'm trying to figure out how to pass a field name into a procedure so the procedure can selectively find out the value of different fields. The DB table I'm interested in has multiple bool fields in it and the field name is the parameter I want to pass into the procedure. With the example code I list below, there is only one row that exists in the table so the end result should be dependent upon the value in the field. The calling procedure is "attempting" to pass in the name of the field, and the called procedure should use the paramter passed in as the field to return in the table. If the field returned in the called procedure is true, the called procedure returns "0" to the calling procedure, otherwise it returns 1. The syntax I have doesn't seem to work in that the called procedure always returns true from the field and the calling procedure always gets 0 back from the called procedure.
In the calling procedure, I'm doing a select just to find out if a 0 or 1 was returned, but this will not be the final version of the code, its just for test purposes.
Any help on what I'm doing wrong greatly appriciated.
- Bruce
ALTER PROCEDURE [dbo].[caller]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @include int,
@Committee bit
execute @include = dbo.callee @Committee
IF @include = 0
BEGIN
select * from ProvCompensation
END
ELSE
BEGIN
select * from ProvCommittee
END
END
ALTER PROCEDURE [dbo].[callee]
(@fName bit)
AS
BEGIN
DECLARE @aBool bit
SET NOCOUNT ON;
select @aBool = @fName from ProvisionsPlanSum where PlanId = -99
IF @aBool = 'true'
BEGIN
RETURN (0)
END
ELSE
BEGIN
RETURN (1)
END
END
View 4 Replies
View Related
Jul 23, 2005
say I have a table with field1,field2,field3,....and have a SP with a parameter @myfieldis there a way I can doSelect @myfield from Table1thanks for your help
View 7 Replies
View Related
May 20, 2008
If we make a change to a parameter and tab to the next parameter (or use the mouse to get to another parameter field), the cursor is not in the next parameter field. This behavior is seen in Reports Manager reports. Is there a fix for this problem?
View 5 Replies
View Related
Oct 12, 2007
I've created a parameter in my report that gives a list of incident #'s which are in the int format. I've always used a Union Select 'All' to allow the user to pull up one item or all items. In this case, I can't get it to work because of the formatting difference.
Here is the code for the dataset called Incident that I'm using for the parameter:
Code Block
Select Incident
from Table
Union Select ' All'
order by 1
This is in the where clause of the main dataset:
Code Block( Incident = @Incident and @Incident<> ' All'
or Incident <> @Incident and @Incident = ' All')
I have this working on a bunch of reports but this is the first time I've tried to use this theory on an integer field. Any ideas?
View 8 Replies
View Related
Mar 29, 2007
I have a boolean field.
I want the users to be able to select
show all marked YES
show all marked NO
show all.
How can this be accomplished?
Thanks
View 6 Replies
View Related
Feb 26, 2007
Hi,
I'm using the SQL Datasource control. I want to use a value of a row in a session control parameter.
I have something like this now:
<asp:SessionParameter Name="ArticleType" SessionField="Article_type_" & number_in_gridview />
number_in_gridview
must be filled with a value in the gridview of the current row.
Does anybody how to do this ?
Regards,
Vortex
View 1 Replies
View Related
Dec 10, 2005
I have the following database structure:
The table aspnet_users, which we all know
Another table with an uniqueidentifier which references the user iD
I want to set the value of the iD in one of the entries in the second table to null.
I tried this:
string assigned is null, i am passing this as a method parameter
...............
string queryString = "UPDATE [table2] SET AssignedUserId=@assigned WHERE ProblemId = @id";
System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
...................
System.Data.IDataParameter dbParam_au_pr = new System.Data.SqlClient.SqlParameter();
dbParam_au_pr.ParameterName = "@assigned";
if (assigned == null)
{
dbParam_au_pr.Value = null;
}
else
{
dbParam_au_pr.Value = assigned;
}
dbParam_au_pr.DbType = System.Data.DbType.String ;
dbCommand.Parameters.Add(dbParam_au_pr);
I also tried using "" instead of null, or not using that "if" statement at all.
I get an error that says:
Parameterized Query '(@id nvarchar(1),@assigned
nvarchar(4000))UPDATE [tracker_Probl' expects parameter @assigned,
which was not supplied.
Please help with this as soon as possible.Thanks
View 1 Replies
View Related
May 23, 2007
Let me simplify. The value of my dataset field Dimension_1 is
[SOB Year].[Yr Id].[Yr Id].[MEMBER_CAPTION]
I want to use parameters for SOB Year and Yr Id like I do on the query.
But if I use
="["& Parameters!dimension_1_table.Value &"].["& Parameters!dimension_1_column.Value &"].[" & ["& Parameters!dimension_1_column.Value &"]"
it returns null for the values. I don't know how to make these parameters
I am passing in parameters for the table and column to build an MDX query here is my query.
="SELECT NON EMPTY { [Measures].[Liability Amt] } ON COLUMNS, NON EMPTY { (["& Parameters!dimension_1_table.Value &"].["& Parameters!dimension_1_column.Value &"].["& Parameters!dimension_1_column.Value &"].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [SOB Clean] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"
Anyone know what value I need to use on the dataset field to access the column?
Thanks,
Roger
View 10 Replies
View Related
Jan 24, 2007
Hi,
I have some difficulties to format a Date value in my reporting service.
As you know you can use this kind of expression:
=DateTime.Parse(Parameters!ReportingDate.Value).ToString("dd/MM/yyyy")
But it does not work, and when I just want to use the format property of the textbox content such as "dd/MM/yyyy", it displays "dd/MM/yyyy", not the real formatted data, this property works for "=Today()" but not for parameters and fields. What is the diffrence?
Have you an idea to format a Parameter/Field of Date type such as dd/MM/yyyy?
Many thanks!
View 8 Replies
View Related
Jan 13, 2007
Hello,
Can anyone say me how i can make a Report with Parameter Boolean field,and as Default Value true and False. ( Both ).
With Multivalue and in the Query = Field in (@BoolPara) have i a Error in the Query.
Thanks
View 1 Replies
View Related
Jul 20, 2005
I have two tables: eg. a person-table (no nulls allowed), with an idand so on, and a person_course table (an intermediate table in amany-to many relationship between person table and courses tables),with two fields person_id and course_id.But I want to make ONE multipurpose stored procedure, which has ONLYoptional parameters on all fields in the person table AND the fieldcourse_id in the person_course table.I have no problems making optional parameters on the person table (eg.P.ID=ISNULL(@PersonID, P.ID ) ) BUT the problem is, when I try to addan optional parameter on the field course_id it dosn't produce theright results. Some times the course_id is null (because some personshavn't joined a class yet).Is there a way around it?Ole S. Pedersen
View 1 Replies
View Related
Apr 13, 2007
I am currently writing a report that will use as one of the filters the date (in this case it is the date of service of a physician office visit).
however the DB developers included the date and the time of the visit in one field so my resulting data set contains 4/13/2007 4:30pm for example, so using an @date parameter as a filter on the data field doesn't work because when you run the report and enter the date in the parameter it doesn't return anything.
I am an SQL Report writer amateur so I am sure there is an easy fix, any and all help will be appreciated.
Mark
View 7 Replies
View Related
Apr 3, 2007
I want multiple parameter on report.
One of which is a mCategoryname field which is appearing in the combo.[and working fine ]
Now there should be another parameter whichwould take date parameter [yyyy-mm-dd]
User should not able to enter invalid date.
Both parameter should be necessary.
How can i do that ?
View 1 Replies
View Related
Sep 8, 2007
My value is approx 50 characters wide, the parameter drop down is only displaying about 10 characters, although I have plenty of room on the screen.
Also, in a multi-parameter environment, are there any controls on where the parameters are being displayed?
thanks
View 2 Replies
View Related
Oct 30, 2007
Hello,
I have a report where I'm showing a list of users to select.
When previewing the report, the width of the field is automatically set to the largest label in the dropdown list.
But when I change the parameter to have "Multi-Values", then the width is set to a fixed value and a horizontal scrollbar appears.
Does anyone know how to have a "Multi-Values" parameter, but keeping the lasgest width of the labels in the dropdown list ?
Thanks in advance
View 3 Replies
View Related
Mar 26, 2008
Hi,
I'm attempting to use the following code to display either 'All' or the date value selected by the user from a Report parameter;
=iif(Parameters!FromCheckOutDateDate.Value.ToString = "[Check Out Date].[Date].[All]", "All", "From Date: " + Parameters!FromCheckOutDateDate.Value.ToString.Substring(26,10))
This is throwing an error ('#Error').
I can use the following code with no error, though its not as useful;
=iif(Parameters!FromCheckOutDateDate.Value.ToString = "[Check Out Date].[Date].[All]", "All", "Not all")
I can even use this to display the selected value (i.e. 2007-01-01);
Parameters!FromCheckOutDateDate.Value.ToString.Substring(26,10)
Why can't I use them both in my iif statement?
Can someone please help?
View 2 Replies
View Related
Mar 5, 2008
SELECT ArticleID, ArticleTitle, ArticleDate, Published, PublishDate, PublishEndDateFROM UserArticlesWHERE (ArticleDate >= PublishDate) AND (ArticleDate <= PublishEndDate)
When I use the above on a GridView I get nothing. Using SQL manager it works great.
I don't know how to pass the value of the ArticleDate field as a default parameter or I think that's what I don't know how to do.
I am trying to create a app that I can set the dates an article will appear on a page and then go away depending on the date.
Thanks for any help!
View 1 Replies
View Related
Sep 6, 2015
I have two reports 'Product Details' and 'Sales by Product'
When i click on 'Product ID' field in 'Product Details' report, It has to drill through to 'Sales by Product' which has a parameter 'Product ID' in it.
I have written the following javascript
="javascript:void(window.open('http://servername/Rportserver/Pages/Report.aspx?ItemPath=%2fTestDrillThru%2fSales+By+Product&rs:Command=Render&ChildProductID="& Fields!ProductID.Value &"','_blank',',resizable=1,width=1600,height=800,left=50'))"
When i drill through, field value is not passing to 'child product id' in sales by product. I have to give the value again in child report.
View 5 Replies
View Related
Mar 18, 2008
I have a gridview connected to a sqldatasource, and it works pretty good. It gives me the subsets of the information that I need. But, I really want to let them choose all the companies and/or any status. What's the best way to get all the values in the gridview...besides removing the filters :)
I thought the company would be easy, I'd just set the selected value to blank "", and then it'd get them all....but that's not working. And, for the boolean, I have no idea to get the value without having a separate query.
(tabs_done=@tabsdone) and (company like '%' + @company + '%')1 <asp:DropDownList ID="drpdwnProcessingStatus" runat="server">
2 <asp:ListItem Value="0">Open</asp:ListItem>
3 <asp:ListItem Value="1">Completed</asp:ListItem>
4 </asp:DropDownList>
5
6
7 <asp:DropDownList ID="drpdwnCompany" runat="server">
8 <asp:ListItem Value="">All</asp:ListItem>
9 <asp:ListItem Value="cur">Cur District</asp:ListItem>
10 <asp:ListItem Value="jho">Jho District</asp:ListItem>
11 <asp:ListItem Value="sea">Sea District</asp:ListItem>
12 <asp:ListItem Value="san">Net District</asp:ListItem>
13 <asp:ListItem Value="sr">Research District</asp:ListItem>
14 </asp:DropDownList>
15
16
17 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:HRFormsConnectionString %>"
18 SelectCommand="SELECT DISTINCT [id], [lastname], [company] FROM [hr_term] hr where (tabs_done=@tabsdone) and (company like '%' + @company + '%')">
19 <SelectParameters>
20 <asp:ControlParameter ControlID="drpdwnProcessingStatus" DefaultValue="0" Name="tabsdone" PropertyName="SelectedValue" />
21 <asp:ControlParameter ControlID="drpdwnCompany" DefaultValue="" Name="company" PropertyName="SelectedValue" />
22 </SelectParameters>
23 </asp:SqlDataSource>
24
View 3 Replies
View Related
Jan 22, 2007
Hello,
My problem is with Validating Date parameter filed, when I user enters wrong (assume user is entering date instead of selecting from date picker). Where following are scenarios I would like to validate.When user enters non-date.
When user enters 30/feb/yyyy.When user enters 29/feb/yyyy. (for non-leap year).When user enters 31/mm/yyyy. (for months does not includes day 31)
Could you explain in a little more detail how to accomplish this?
thanks in advance,
Ramesh KS
View 3 Replies
View Related
Jul 9, 2015
I need to set a field value based on two date time parameter's.What is the correct syntax to allow me to pass the value into the field in my SSRS expression ?
=IIf(Parameters!EndDate > Parameters!StartDate.Value, "Overdue")
View 3 Replies
View Related
Oct 24, 2007
Hello,
I have a report which displays a customers invoice, in both the companys local currency, and the customers local currency.
The report language is "English (United Kingdom)"
The fields showing customers currency language setting is set to something else, i.e. "France (French)" to display the Euro currency.
The application handles 34 currencies, the query returns the language string, ("France (French)"), to allow the report to bind its language setting to the querys output.
However, it doesn't work, a normal textbox will display the correct country name string, but Reporting Services cannot bind the language setting to a query result. So I also tried setting it as a report parameter, but no joy either (all currencys revert to USD).
I'm using =First(Fields!curFormat.Value, "myDataSet") to bind the 'language' setting, the result of this expression returns "France (French)", which is a valid option for this language setting, as it's in the drop down list.
Rather than create 34 seperate reports for each currency, are there any suggestions on how to bind a fields language setting to a query result?
View 3 Replies
View Related
Aug 4, 2015
I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).
However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.
Below code.
ALTER Procedure [dbo].[sp_ViewCreate]
/* Input Parameters */
@TableName Varchar(20),
@Dist Varchar(20)
AS
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)
[code]....
View 9 Replies
View Related
Oct 3, 2015
I hope to update a DateTime column value with a Time input parameter. Poor attempt below but it looks like the @ApptTime param is coming in as 10:45:00.0000000 and I might have an existing @SendOnDate as: 2015-10-05 07:00:00.000...I hope to end up with 2015-10-05 10:45:00.000
ALTER PROCEDURE [dbo].[SendEditUPDATE]
@QuePoolID int=null
,@ApptTime time(7)
,@SendOnDate datetime
[code]...
View 14 Replies
View Related
Apr 15, 2004
for example
with member [Measures].[Accumulated Sales] as 'Sum(YTD(),[Measures].[Store Sales])'
select
{[Measures].[Store Sales],[Measures].[Accumulated Sales]} on columns,
{Descendants([Time].[1997],[Time].[Month])} on rows
from Sales
I need more then 1 member how to do in
Thanks
View 4 Replies
View Related
