Business Intelligence Development Studio Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Business Intelligence Development Studio Table

Author

Topic

TPie9
Yak Posting Veteran

67 Posts

Posted - 2009-10-23 : 13:02:02

I'm trying to format my results in a table in BIDS but can't figure out how to do it. The 1st table below is what my current table looks like and the 2nd table is what I'd like with the years going across the top of the table and the results below.

HAVE
Customer - Year - Shipments - Revenue
A - 2007 - 5 - 1,000
A - 2008 - 5 - 1,000
A - 2009 - 5 - 1,000
BB - 2007 - 2 - 1,500
BB - 2008 - 2 - 1,500
BB - 2009 - 2 - 1,500

WANT
Customer - Shipment - Revenue - Shipments - Revenue - Shipments - Revenue
2007 - 2007 - 2008 - 2008 - 2009 - 2009 -
A - 5 - 1,000 - 5 - 1,000 - 5 - 1,000
BB - 2 - 1,500 - 2 - 1,500 - 2 - 1,500

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 13:04:01

[code]SELECT Customer,
MAX(CASE WHEN Year=2007 THEN Shipments ELSE NULL END) AS Shipment2007,
MAX(CASE WHEN Year=2007 THEN Revenue ELSE NULL END) AS Revenue2007,
MAX(CASE WHEN Year=2008 THEN Shipments ELSE NULL END) AS Shipment2008,
MAX(CASE WHEN Year=2008 THEN Revenue ELSE NULL END) AS Revenue2008,
MAX(CASE WHEN Year=2009 THEN Shipments ELSE NULL END) AS Shipment2009,
MAX(CASE WHEN Year=2009 THEN Revenue ELSE NULL END) AS Revenue2009
FROM table
GROUP BY Customer[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 13:05:25

if you want to generate this in BIDS add a matrix container with row group as customer and column group as year. then add shipments and revenue as data

TPie9
Yak Posting Veteran

67 Posts

Posted - 2009-10-23 : 14:33:14

I have the info in a matrix, but I don't know how to get alternating shading on the information for every other line. I want the shading because the actual report is thousands of lines and hard to read without alternate shaded lines. I'm trying to put the information in a table because I know how to do alternating shaded lines in a table but not in a matrix. If you know how I can shade alternate lines in a matrix that would be a great help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 14:34:38

alternate shading in rows or columns?

TPie9
Yak Posting Veteran

67 Posts

Posted - 2009-10-23 : 15:00:42

Alternate shading in rows for the matrix.

Thanks for your assistance because I have the information like I need it in table format and alternate rows are shaded. It would still be nice to know how to have alternate row shading in the matrix.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 15:04:29

you can use an expression for color property of row. something like =IIF(RowNumber("yourdataset") % 2 = 0, "Blue","Green")

TPie9
Yak Posting Veteran

67 Posts

Posted - 2009-10-23 : 15:11:38

What would be ("yourdataset")? I tried using =IIF(RowNumber(Fields!Customer.Value) % 2 = 0, "Blue","Green") because it's the customers that'll have the alternating color, but received an error message.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-24 : 04:50:03

quote:
Originally posted by TPie9

What would be ("yourdataset")? I tried using =IIF(RowNumber(Fields!Customer.Value) % 2 = 0, "Blue","Green") because it's the customers that'll have the alternating color, but received an error message.

your dataset should be name of actual dataset you used to populate the matrix

TPie9
Yak Posting Veteran

67 Posts

Posted - 2009-10-26 : 10:02:31

The name of my dataset is "LossOfSales". I tried the following and still received an error message (below of what I tried).

Tried
=IIF(RowNumber(LossOfSales) % 2 = 0, "Blue","Green")

Error Received
An error occurred during local report processing.
The definition of the report '/Loss Of Sales' is invalid.
The background color expression for the textbox 'Shipments' has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of containing data region, or the name of a data set.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-27 : 13:45:47

are you sure you've written this expression inside container that uses LossOfSales? also, it should be =IIF(RowNumber("LossOfSales") % 2 = 0, "Blue","Green")

TPie9
Yak Posting Veteran

67 Posts

Posted - 2009-10-27 : 16:07:16

Below is my matrix and no matter which properties background color expression I put the formula in, I receive an error. Any ideas?

Columns ---> =Fields!Year.Value
Columns ---> Shipments Revenue

Rows ///// Data
=Fields!Customer.Value ///// =Sum(Fields!Shipments.Value) =Sum(Fields!Revenue.Value)
Total

TPie9
Yak Posting Veteran

67 Posts

Posted - 2009-10-30 : 17:24:15

After searching for a few weeks I've finally found steps that will accomplish what I'm looking for, so I included them below in case anyone else is looking for the same things.

Question:
How can I get a green-bar effect (alternating colors) in a matrix?
Answer:
For a green-bar table, you can simply use a background color expression like this: =iif(RowNumber(Nothing) Mod 2,"Green","White")
However, there is currently no GroupNumber() function on which to base a green-bar calculation in a matrix.
GroupNumber can be (mostly) simulated by using the RunningValue function to get a running distinct count of group expression values.
However, the trickiest part of green-bar in a matrix is the fact that some matrix cells may contain no data at all. This makes the group number calculation incorrect for empty cells.
To work around this, you need to effectively calculate the group number in the row header and then use that value inside the data cells.
Step 1: Add a (fake) inner row grouping
Select the innermost row grouping in your matrix. Right-click and select Insert Group.
For the group expression, group on a constant, such as =1
Step 2: Calculate the name of the color in the inner row grouping header
In the Value property of the newly created grouping header, add a calculation for the desired color based on a running value of a count distinct of the containing group expression.
For example: =iif(RunningValue(Fields!Country.Value,CountDistinct,Nothing) Mod 2, "AliceBlue", "White")
Note: If you have more than one row grouping, you may need to do the count distinct on the combination of all group expressions, like this:
=iif(RunningValue(Fields!Country.Value & CStr(Fields!Year.Value),CountDistinct,Nothing) Mod 2, "AliceBlue", "White")
Step 3: Set the background color of the inner row grouping header to =Value
Step 4: Set the background color of the matrix data cell to the value of the inner row grouping header
For example: =ReportItems!ColorNameTextbox.Value
Step 5: Set the background color of the outer row grouping header
You'll need to use the same expression here that you used for the Value of the inner row grouping header.
Step 6: "Cloak" the inner row grouping header (so it looks like part of of the outer grouping header)
Set the right border style of the outer grouping header to None.
Set the left border style of the inner grouping header to None.
Set the font weight of the inner grouping header to 1 pt.
Set the font color of the inner grouping header to =Value.
Set the CanGrow property of the inner grouping header to False.
Drag the inner grouping header to be as narrow as possible.
Optional: Hand-edit the RDL to set the width of the inner grouping header to 0in.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources