Change Background Color

Hi,

I am using toggle visibility feature in one of my reports and I am trying to change the background color of some cells when I toggle the view. Please help, any help will be appreciated.

Thanks,

-Rohit

To be more precise, there is a parent and it has several children. At first children are hidden, but when I click parent then children become visible. I would like to change the color of the parent when children are visible and go back to transparent when children are hidden. I hope I clearly discribe my question. Thanks.

cells not diplaying

I have 2 dimensions dimA and dimB. Dim A has members Aa and Ab while dimB has members Ba, Bb and Bc. The fact table has columns dimAKey, DimBkey, CountofX.

I need to display a result that shows counts for all members for dimA (whether there is a value in CountofX or not) but for dimB I only want to see the members that have values. eg.

DimA DimB CountofX
-
Aa Ba 3
Aa Bb 4
Aa Bc 2
Ab Ba 7
Ab <blank> <blank>
Ab Bc 3

Can someone help me?

Thanks

Your example does not really make sense, you might have simplified it a bit too much, you could get something like the following, but it does not sound like this is what you are after.

DimA DimB CountofX
-
Aa Ba 3
Aa Bb 4
Aa Bc 2
Ab Ba 7
Ab Bb <blank>
Ab Bc 3

Assuming that you had a value of Ac another alternative would be something like the following, where effectively you get every value of DimA against DimB.All and also all the members of DimB that have values.

DimA DimB CountofX
-
Aa Ba 3
Aa Bb 4
Aa Bc 2
Aa All 9
Ab Ba 7
Ab Bc 3
Ab All 10
Ac All <blank>

Would something like this suit your requirements?

|||I'll try it. Please post the syntax|||

Basically I would create two sets on the row axis, union them and then put them back in their hierarchy order. I don't know what your cube is called or what sort of hierarchy attribute structure you have, but in psuedo code, it would be something like the following (which is SSAS 2005 syntax).

SELECT
{Measures.CountofX} ON COLUMNS
Hierarchize(UNION({DimA.<Hierarchy>.Members} * {DimB.<Hierarchy>.[All]}
, NONEMPTY({DimA.<Hierarchy>.Members} * DimB.<Hierarchy>.Members)),POST) ON ROWS
FROM <Cube>

|||

I could not get the result of

Ac All <blank>

to show up. If CountofX is null for all members of dimB against value Ac, that whole line does not even show up. I need to be able to see all members of dimA at all times regardless of whether intersection with dimB members has values or not. However, for dimB I want to see only those members that have values at intersection with dimA. I hope I have conveyed my problem well enough for you to help me. I am using SSAS 2005

Thanks,

newtomdx

|||

I can't think how any of the tuples involving dimA and the all member of dimB could be excluded, unless your query tool is doing some extra filtering.

What are you using to run the query?

Can you try running it using SQL Server Management Studio?

Can you post the exact query you are running?

|||Yes, there was a filter. It does work correctly once I removed it. Is there any way I can just have all the members of dimA and show members of dimB that have values in countofX without the "All"? Thanks for your help.|||No, in multi-dimensional space (at least in SSAS 2005) there is no such thing as a null value in a dimension. Even if you were not displaying dimB at all, technically the cube is still returning the value that relates to the default member in dimB (which in this case is "All"). If you are using some sort of reporting tool like SSRS, you might be able to surpress the output of the all label.

Cell reference in SQL Report

Hi,

Is it possible to use Cell reference in the calculations, in SQL reports.

Like Excel allows "=A1 * B1", where A1 and B1 are two cells with some numeric value. Is it possible to use Cell reference in SQL reports? Like "textbox1 * textbox 2"... or something like that, with textbox1 and textbox2 containing some numeric values.

Thanks

Perm

Yes. You can name a text box and then reference it using ReportItems!. If you have 2 text boxes named txtA and txtB and you want to add the two and put in a third box you can put ReportItems!txtA.value+ReportItems!txtB.value in the third textbox to get the total of the two cells.|||

Hi Kim,

Thank you very much for the quick reply!! It works.

Now, the next item I'm stuck on, is the running total of this calculated field. Here is the detail of my RDL:

In a table, I have "textbox34" with the formula =count(Fields!ADF_NO.Value) in it and "textbox30" with the formula =first(Fields!TOTSTKS2.Value, "ProdServSales") - reportitems!TOTSTKS.value in it. I have divided "textbox 34" with "textbox30", and placed the results in "textbox36". Now, I want to do a cummulative total (Running total) of "textbox36", using the formula =runningvalue(reportitems!textbox36.Value, sum, nothing) in "textbox14". Its giving me an error saying:

"[rsAggregateReportItemInBody] The Value expression for the textbox 'textbox14' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers."

Any suggestions? I appreciate you help on this.

Thanks

Perm

Cell Orientation

How do i format the Header cells to be oriented by 90 degrees like in Excel. I tried the WRITING MODE under INTERNATIONAL in properties, and choose "tb-rl". But it gives top to bottom. Is there any way to get it bottom to top?

Is there any example to write Expression in Writing Mode property to display in 30 degrees?

Thanks.

Girija, did you ever find a solution to this? I need to do the same thing, rotate headers by 90 degrees.

Thanks.

|||Unfortunately, this is not supported in the current version of Reporting Services. Arbitrary text rotation is something we would like to add in a future release.

Cell Orientation

How do i format the Header cells to be oriented by 90 degrees like in Excel. I tried the WRITING MODE under INTERNATIONAL in properties, and choose "tb-rl". But it gives top to bottom. Is there any way to get it bottom to top?

Is there any example to write Expression in Writing Mode property to display in 30 degrees?

Thanks.

Girija, did you ever find a solution to this? I need to do the same thing, rotate headers by 90 degrees.

Thanks.

|||Unfortunately, this is not supported in the current version of Reporting Services. Arbitrary text rotation is something we would like to add in a future release.

Cell Formatting

Hi,

On importing an XML file using VBA I/O function, I need to apply rules/format to specific Excel 2003 .Cells.

XML
<Column FieldName="ProductReference" FieldNumber="1">
<DataType>character</DataType>
<Format>x(20)</Format>
<Restrictions/>
<Mandatory>true</Mandatory>
<PermittedValues/>
</Column>

If the above XML-lines (strings) are read, I need to apply the following rules/formats to the identified .Cells(x,1):

1.
.Columns("A").NumberFormat = "General" or "Text" ??
2.
That when inputting a value, the user is restricted to 20 Characters
3.
That this is a Mandatory entry field

These values obviously change as per XML Output. Can anyone help, please?Is this related to Crystal Reports?

Cell Calculation for cells that already has values

Hi,

I have the table like this

Jan Feb .. Dec

account 50 20 ...

On the cube i would like to make it using calculated cells and that look like this

account 50 70 ...

Same cells has before.

I have tried a few stuff but i cant put values on top of the ones that are already there, so i get the values in the cell and not the values in the calculated cell.

Funny, though, i have put a constant (1) i the 'AS' part of the calculated cell syntax and it adds the cell value with the constant. But if i try to add with the value time.lag(1) SSAS just ignores the value.

Is there a way to do this by calculated cell?

Thank you

Have you considered simply creating a calculated measure using the YTD() or PeriodsToDate() functions? If you did not want the end users to see the underlying measure you could set it's visible property to false.|||

Hello,

The problem with calculated members is that the user would have to change the query when browsing on the cube.

What i would like is that, for the users, browsing on the this account or any other i could use the same measure. And for that reason i need it to be a calculated cell.

Is there a way to solve this?

|||

You did not mention which version of Analysis Services you are using. I am assuming from the SSAS acronym that you are using 2005, not 2000.

You would not have to change any queries if you renamed the current measure and then created a new calculated measure with the same name as the original. This would be my preferred approach as you could still get to both the cumulative and the incremental amounts.

However, you could replace the value of a measure using an MDX Script assignment.

I am not clear on exactly what your business requirements are, but the following is a sample bit of MDX Script which works against the Adventure Works database to simply adds the previous member to the current member for the [Sales Amount] measure.

([Measures].[Sales Amount]

,[Date].[Fiscal].Members) = ([Date].[Fiscal].Prevmember)

+ ([Date].[Fiscal].CurrentMember);

|||

You did not mention which version of Analysis Services you are using. I am assuming from the SSAS acronym that you are using 2005, not 2000.

You would not have to change any queries if you renamed the current measure and then created a new calculated measure with the same name as the original. This would be my preferred approach as you could still get to both the cumulative and the incremental amounts.

However, you could replace the value of a measure using an MDX Script assignment.

I am not clear on exactly what your business requirements are, but the following is a sample bit of MDX Script which works against the Adventure Works database to simply adds the previous member to the current member for the [Sales Amount] measure.

([Measures].[Sales Amount]

,[Date].[Fiscal].Members) = ([Date].[Fiscal].Prevmember)

+ ([Date].[Fiscal].CurrentMember);

|||

Hello,

This was a good solution, inline with the other solution in the conversation.

I think that i could try altough it will clash a bit with all my other calculated cell. I did not remember to rename the calculated member to the actual measer name.

With all this i assume that the cell calculation doesn't allow this task.

Thank you