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
Hi All I have a satellite a350 laptop can anyone tell me whats the biggest harddrive I can have and memory please ThanksHi The RAM upgrade depends on the available chipset. Not quite sure about your A350-xxx model but many of the Sat A350 notebooks w [More]
Hello, I am wondering if I have to upgrade Remote Desktop software to a version higher that 2.2 if I switch from Tiger to Leopard; or can I stay with Remote Desktop 2.2?We have now got the situation that we can control the server over the VPN using s [More]
I have a new computer. My IT support asked me to give Outlook email another chance. I did for a week and decided it's not to my liking. So I installed Thunderbird. I did a search to find an email, and the search came back - 0 found. I knew this was w [More]
Hi, When I view PDF files created on a PC, in Preview the text is replaced by jibberish and square boxs. I have been told that the font is Arial. This font is installed on my Mac. Can I find out from Preview why it displays the PDF incorrectly or wha [More]
After nearly 3 years with BT my monthly usage allowance has suddenly started being exceeded even though I am not using the internet any differently. The alert emails were being sent to the wrong email so I only found out when my paper bill finally ar [More]
Is there a way to turn off Photosharing in iWeb 8.0? That is, turn off photo Downloading. DetailView.js can be modified to accomplish this but that seems like a kluge......same question here: some of the photos on my site (www.christelvanmeeuwen.eu - [More]
Gday.. Been reading these threads so apologies in advance if this is covered elsewhere.. I like others upgraded from AP3.0.0 to AP3.0.1.. and initially things went well: processing sped up, Swap memory stays a manageable 1~2GB (or less). Couple of pr [More]
Hello Experts, I have a drop down list in my BSP Application. I have build the dropdown using internal table. <htmlb:dropdownListBox id = "REV_STAT" table = "<%= int_stat_typ %>" selection = "<%= application->wf_sta [More]
I am trying to find solution for some type of settings in ACS. Imagine for instance real situation as follows: There is group "A" with 100 users. I need for 20 of them assign the access to devices in the group "B". I can't find any eas [More]
If I bought iphone 5 in kuwait and my iphone motherboard can't repair in kuwait? Can apple repair it? And how much?You can contact the AppleCare support from http://support.apple.com/kb/HE57 and see where the iPhone can be repaired...but warranties c [More]