Sitemap

Amicuk Programming Answers

How To hide a column on a Matrix using a parameter that is set-up as a multi-select

2015-10-11   Views:0

Advertisement

Hi, I have a multi-select parameter (has 4 choices ie. A,B,C,D).  In the matrix the parent Column group is a date and the child group is based on the Multi-select parameter field.  When all choices are selected the report returns a column per date an

Hi,
I have a multi-select parameter (has 4 choices ie. A,B,C,D).  In the matrix the parent Column group is a date and the child group is based on the Multi-select parameter field.  When all choices are selected the report returns a column per
date and then within that date column 4 sub columns as expected.....This all works. 
Challenge ... I added another column (Col5).  For Col5 I only want it to show if value B has been selected from the multi choice
Another way of saying it....  How do I get a column to appear (Visibility) if a specific  Value has been select in a multi-choice parameter
Tx
Andrew
Andrew Payze

The replay answer
Advertisement
Hi,
here is the query
SELECT        ProjectNumber, ProjectDescription, WBS, TaskNumber, TaskName, TaskDescription, TaskManager, Results, ResourceExpenditure, CostSet, Currency,
                         ReportingDate, Value, YEAR(ReportingDate) AS Year, { fn MONTHNAME(ReportingDate) } AS Month, ActualValue, PriorEAC
FROM            vwForecastAccuracy
WHERE        (CostSet IN (@CostType)) AND (YEAR(ReportingDate) = @Year) AND (Results = 'Cost')
below is the Code, not sure how to attach RDL
Thanks
Andrew
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<Body>
<ReportItems>
<Tablix Name="matrix1">
<TablixCorner>
<TablixCornerRows>
<TablixCornerRow>
<TablixCornerCell>
<CellContents>
<Textbox Name="textbox3">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value />
<Style>
<FontFamily>Tahoma</FontFamily>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>textbox3</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCornerCell>
</TablixCornerRow>
<TablixCornerRow>
<TablixCornerCell>
<CellContents>
<Textbox Name="Textbox8">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value />
<Style>
<FontFamily>Tahoma</FontFamily>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox8</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCornerCell>
</TablixCornerRow>
</TablixCornerRows>
</TablixCorner>
<TablixBody>
<TablixColumns>
<TablixColumn>
<Width>1in</Width>
</TablixColumn>
<TablixColumn>
<Width>1in</Width>
</TablixColumn>
</TablixColumns>
<TablixRows>
<TablixRow>
<Height>0.21in</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="Value">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Sum(Fields!Value.Value)</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<Format>'$'#,0;('$'#,0)</Format>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Value</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
<rd:FormatSymbolCulture>en-US</rd:FormatSymbolCulture>
</Style>
</Textbox>
</CellContents>
<DataElementOutput>Output</DataElementOutput>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Textbox4">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=RunningValue(Fields!ActualValue.Value,sum, "matrix1_ProjectNumber")</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox4</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
<DataElementOutput>Output</DataElementOutput>
</TablixCell>
</TablixCells>
</TablixRow>
</TablixRows>
</TablixBody>
<TablixColumnHierarchy>
<TablixMembers>
<TablixMember>
<Group Name="matrix1_ReportingDate">
<GroupExpressions>
<GroupExpression>=Fields!ReportingDate.Value</GroupExpression>
</GroupExpressions>
</Group>
<SortExpressions>
<SortExpression>
<Value>=Fields!ReportingDate.Value</Value>
</SortExpression>
</SortExpressions>
<TablixHeader>
<Size>0.21in</Size>
<CellContents>
<Textbox Name="Month">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!Month.Value</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontWeight>Bold</FontWeight>
<Format>MM/dd/yyyy</Format>
<Color>White</Color>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>Month</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>#6e9eca</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixHeader>
<TablixMembers>
<TablixMember>
<Group Name="CostSet">
<GroupExpressions>
<GroupExpression>=Fields!CostSet.Value</GroupExpression>
</GroupExpressions>
</Group>
<SortExpressions>
<SortExpression>
<Value>=Fields!CostSet.Value</Value>
</SortExpression>
</SortExpressions>
<TablixHeader>
<Size>0.25in</Size>
<CellContents>
<Textbox Name="CostSet1">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!CostSet.Value</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontWeight>Bold</FontWeight>
<Color>White</Color>
</Style>
</TextRun>
</TextRuns>
<Style>
<TextAlign>Center</TextAlign>
</Style>
</Paragraph>
</Paragraphs>
<rd:DefaultName>CostSet1</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>#6e9eca</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixHeader>
<TablixMembers>
<TablixMember />
<TablixMember />
</TablixMembers>
</TablixMember>
</TablixMembers>
<DataElementOutput>Output</DataElementOutput>
<KeepTogether>true</KeepTogether>
</TablixMember>
</TablixMembers>
</TablixColumnHierarchy>
<TablixRowHierarchy>
<TablixMembers>
<TablixMember>
<Group Name="matrix1_ProjectNumber">
<GroupExpressions>
<GroupExpression>=Fields!ProjectNumber.Value</GroupExpression>
</GroupExpressions>
</Group>
<SortExpressions>
<SortExpression>
<Value>=Fields!ProjectNumber.Value</Value>
</SortExpression>
</SortExpressions>
<TablixHeader>
<Size>1in</Size>
<CellContents>
<Textbox Name="ProjectNumber">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!ProjectNumber.Value</Value>
<Style>
<FontFamily>Tahoma</FontFamily>
<FontWeight>Bold</FontWeight>
<Color>White</Color>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>ProjectNumber</rd:DefaultName>
<ActionInfo>
<Actions>
<Action>
<Drillthrough>
<ReportName>BUDVAR 11 Budget Detail By WBS</ReportName>
<Parameters>
<Parameter Name="ProjectNumber">
<Value>=Fields!ProjectNumber.Value</Value>
</Parameter>
<Parameter Name="Year">
<Value>=Parameters!Year.Value</Value>
</Parameter>
<Parameter Name="CostSet">
<Value>=Parameters!CostType.Value</Value>
</Parameter>
</Parameters>
</Drillthrough>
</Action>
</Actions>
</ActionInfo>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>#6e9eca</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixHeader>
<DataElementOutput>Output</DataElementOutput>
<KeepTogether>true</KeepTogether>
</TablixMember>
</TablixMembers>
</TablixRowHierarchy>
<RepeatColumnHeaders>true</RepeatColumnHeaders>
<RepeatRowHeaders>true</RepeatRowHeaders>
<DataSetName>BudgetData</DataSetName>
<Height>0.67in</Height>
<Width>3in</Width>
<Style />
</Tablix>
</ReportItems>
<Height>0.84708in</Height>
<Style />
</Body>
<Width>3.85417in</Width>
<Page>
<LeftMargin>1in</LeftMargin>
<RightMargin>1in</RightMargin>
<TopMargin>1in</TopMargin>
<BottomMargin>1in</BottomMargin>
<Style />
</Page>
<AutoRefresh>0</AutoRefresh>
<DataSources>
<DataSource Name="BIDatabase">
<DataSourceReference>VSPDEV011</DataSourceReference>
<rd:SecurityType>None</rd:SecurityType>
<rd:DataSourceID>f3bf5788-4fb5-4822-89d9-2f4518f5488d</rd:DataSourceID>
</DataSource>
</DataSources>
<DataSets>
<DataSet Name="BudgetData">
<Query>
<DataSourceName>BIDatabase</DataSourceName>
<QueryParameters>
<QueryParameter Name="@CostType">
<Value>=Parameters!CostType.Value</Value>
</QueryParameter>
<QueryParameter Name="@Year">
<Value>=Parameters!Year.Value</Value>
</QueryParameter>
</QueryParameters>
<CommandText>SELECT ProjectNumber, ProjectDescription, WBS, TaskNumber, TaskName, TaskDescription, TaskManager, Results, ResourceExpenditure, CostSet, Currency,
ReportingDate, Value, YEAR(ReportingDate) AS Year, { fn MONTHNAME(ReportingDate) } AS Month, ActualValue, PriorEAC
FROM vwForecastAccuracy
WHERE (CostSet IN (@CostType)) AND (YEAR(ReportingDate) = @Year) AND (Results = 'Cost')</CommandText>
</Query>
<Fields>
<Field Name="ProjectNumber">
<DataField>ProjectNumber</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ProjectDescription">
<DataField>ProjectDescription</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="WBS">
<DataField>WBS</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="TaskNumber">
<DataField>TaskNumber</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="TaskName">
<DataField>TaskName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="TaskDescription">
<DataField>TaskDescription</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="TaskManager">
<DataField>TaskManager</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Results">
<DataField>Results</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ResourceExpenditure">
<DataField>ResourceExpenditure</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CostSet">
<DataField>CostSet</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Currency">
<DataField>Currency</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ReportingDate">
<DataField>ReportingDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="Value">
<DataField>Value</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Year">
<DataField>Year</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Month">
<DataField>Month</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ActualValue">
<DataField>ActualValue</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="PriorEAC">
<DataField>PriorEAC</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="ProjectLookup">
<Query>
<DataSourceName>BIDatabase</DataSourceName>
<CommandText>SELECT DISTINCT ProjectNumber, ProjectDescription, ProjectNumber AS Expr1
FROM vwForecastAccuracy</CommandText>
</Query>
<Fields>
<Field Name="ProjectNumber">
<DataField>ProjectNumber</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ProjectDescription">
<DataField>ProjectDescription</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Expr1">
<DataField>Expr1</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="CostTypeLookup">
<Query>
<DataSourceName>BIDatabase</DataSourceName>
<CommandText>SELECT DISTINCT CostSet, CostSet AS CostSetDesc
FROM vwForecastAccuracy
UNION
SELECT NULL AS Expr1, 'All' AS CostSetDesc
ORDER BY CostSet</CommandText>
</Query>
<Fields>
<Field Name="CostSet">
<DataField>CostSet</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CostSetDesc">
<DataField>CostSetDesc</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="YearLookup">
<Query>
<DataSourceName>BIDatabase</DataSourceName>
<CommandText>SELECT DISTINCT YEAR(ReportingDate) AS Year
FROM vwForecastAccuracy
ORDER BY Year</CommandText>
</Query>
<Fields>
<Field Name="Year">
<DataField>Year</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
</DataSets>
<ReportParameters>
<ReportParameter Name="Year">
<DataType>String</DataType>
<Prompt>Year</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>YearLookup</DataSetName>
<ValueField>Year</ValueField>
<LabelField>Year</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
<ReportParameter Name="CostType">
<DataType>String</DataType>
<Prompt>Cost Set</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>CostTypeLookup</DataSetName>
<ValueField>CostSet</ValueField>
<LabelField>CostSetDesc</LabelField>
</DataSetReference>
</ValidValues>
<MultiValue>true</MultiValue>
</ReportParameter>
</ReportParameters>
<Language>en-US</Language>
<ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>
<rd:ReportUnitType>Inch</rd:ReportUnitType>
<rd:ReportID>19da6d82-a69b-4bb7-a634-2fee3191c5d8</rd:ReportID>
</Report>
Andrew Payze

Go to See the other 3 answers

How To hide a column on a Matrix using a parameter that is set-up as a multi-select

Category:DefaultRelease time:2015-10-11Views:130

Hi, I have a multi-select parameter (has 4 choices ie. A,B,C,D).  In the matrix the parent Column group is a date and the child group is based on the Multi-select parameter field.  When all choices are selected the report returns a column per date an[More]

SSRS 2005 - Dynamically control the width of columns in Matrix report or Hide any column

Category:DefaultRelease time:2015-10-11Views:130

Hi All, I just want to hide some column without having white space in Matrix report in SSRS 2005. Although I am aware of that perhaps this feature is not available on SSRS 2005. So, I just want to know if we can handle the width of column dynamically[More]

Hide the Column Or Remove the extra data

Category:DefaultRelease time:-0001-11-30Views:130

Hi ALL , I have developed using the development procedure from Simon_Hou and it works perfectly fine . Thank you Simon_Hou http://social.msdn.microsoft.com/Forums/office/en-US/842e2dcb-d949-4297-9d91-eac989692cb5/difference-between-the-grouped-column[More]

Hide the column from ssrs report

Category:DefaultRelease time:-0001-11-30Views:130

Hello, I am creating matrix report , I want to hide the column in which Data Field is null. I did tried by writing this expression =IIF(IsNothing((Fields!test_name.Value, "matrix1_category")),true,false) but problem with this expression is it hi[More]

Adding Columns to the matrix in SAP B1 user form using Visual studio 2010

Category:DefaultRelease time:2015-10-11Views:130

Hi, Using Visual studio 2010, I have created a SAP B1 user form( b1f ) with one matrix.Matrix is having 5 columns. Its working properly. Later, I want to add few more columns. Then I added columns to the matrix as I have created columns initially. Bu[More]

How to show or Hide Generic Column in a regular report

Category:DefaultRelease time:2015-10-11Views:130

Hi All, I have created report based on "SQL query (pl/sql function body returning sql query). It returns columns based on some conditions. I have total 60 generic columns like (COL1,COL2....COL60) but it returns 18 or 20 columns based on my condition[More]

How to hide Actions Column in Table

Category:DefaultRelease time:2015-10-11Views:130

Hi All, I have a simple requirement: I am showing a table (items) - the "Actions" column (Column with Buttons to Edit and Delete a particualr row) must not be show -> no changes to the table are allowed (I need to hide/ disable this column) H[More]

Hide a column in one view, but show in other

Category:DefaultRelease time:2015-10-11Views:130

Hi all. I need to hide a column on "Table" view, but column needs to show up on "Pivot table". I know we can hide it on pivot ( exclude It ) and show on table, but I want to know if reverse is possible. This is needed because I am doin[More]

Hide a column in one table view but show it in another

Category:DefaultRelease time:2015-10-11Views:130

I need to hide a column in one tables view but show it in another table view in the same report. Is this possible? Any help on this is appreciated. Edited by: VNC on Jan 11, 2010 9:26 AMHi VNC, If you're crafty with javascript, you could probably wri[More]

How to Hide a column in OBIEE 11g

Category:DefaultRelease time:2015-10-11Views:130

Hi, I am trying to hide a column of presantation layer table in OBIEE 11g. But it is not working. I am doing the follwoing. Open the permission tab of the column. Given no access to Everyone. Given No access the user/Application Role. When i go to we[More]

How to remove or hide Name column while upload document in document library

Category:DefaultRelease time:2015-10-11Views:130

Hi All, In below print screen the first column Name* which always show when I'm going to upload the file which I don't want. It will be really help full if you provide some solution how to hide this column while uploading the document. Its really con[More]

Hot
Interestingly, after a very positive experience by adding the open DNS numbers 208.67.222.222 and 208.67.220.220, I found today that every time I tried to open Safari it knocked me offline. So I've now resorted back to the number originally assigned [More]
Ok everytime I plug in my MacBook Air to my Dell Flat Panel TV/Monitor with the VGA cable, I get the desktop background (Aurora) only, there are no icons, menu, mouse etc., it is like frozen Please help.It sounds like the MBA is running the Dell in e [More]
When i connect my ipod shuffle to itunes i get an error message the ipod cannot be synced, the required disk cannot be found  anyone have this happen? How do i get past it?I seem to recall that this may be cause by overloading the USB bus (lack of po [More]
Please help! When I go to General, there isn't an option for Software Updates.If you connect to iTunes on another computer, everything in your iPhone will be deleted. Perhaps if you post details about the "connection error", someone here can hel [More]
Where are the preset files (Metadata, Develop, etc.) files located on the hard disk under Windows?  I would like to sinc my main compuer settings with my laptop. Thank you. SteveIf you go to preference in LR and select Presets, Show Lightroom Presets [More]
Hello, Ive got a 2012 Macbook Pro that i installed a Samsung 830 in when I bought it a year ok, worked fine up until 2 weeks ago. One evening the system got sluggish and hanged so i powered it off and when rebooting i got the no boot volume questionm [More]
Dear All... working on SCM 5.0..SNP Created Planning Area & Planning Book...Copied frm stad PA 9ASNP02 In interactive Demand planning...selection....while loading the data..following error occurs??? "Conflict identified between selection and key [More]
hot to hide any kind of video frm everywhr.. And keep it in a ssingle sftware...guide ppllz..This should be applicable to your phone too.. /t5/Cseries/I-want-to-hide-my-Personal-Image-and-Video-files-on-C5/m-p/887903/highlight/true#M11677 /t5/Nseries [More]
Hi While Integrating DocuMaker generated PDF to an eSignature Vendor(whose input is a PDF), we found that PDFs created using DocuMaker is malformed/invalid. For example, paths are drawn using page description such as: 176 2672 m 2953 2672 l 2953 2550 [More]
I'm having some trouble using Premiere Pro Creative Cloud. I edited videos using CS6 and CC with the same sequence settings, font settings, and export settings (i.e. YouTube SD widescreen 29.97 preset), and exported with Media Encoder. But the qualit [More]