Hierarchical group

[#1]

Hi,

I have a hierarchical group called Region2Branch. It has 2 fields Region and Branch. It shows the values for all regions and when you click on a specidfic region it shows all the branches for that region with their associated values.

We have a business requirement that Region A has Branch A, B, C, D but D must not be displayed its value must be counted with/under A.

In models where I don''t have drillsdown but only use Branch an if statetement works fine, but not with a group.

How can I accomplish this?

Thank you.

Ryab

[#2]

Hi,

I would generate a seperate field in load-script where you match D to A with an if-statement, eg. field "BranchSpecial", .

Then you can use the field "BranchSpecial" in your group ...

Best regards

Stefan

[#3]

Hi Stefan, Thank you.

In my load script I have

Select
Convert(Int, CASE CODEVAL WHEN '08' THEN '02' WHEN '27' THEN '02' WHEN '06' THEN '04' WHEN '24' THEN '17' ELSE CODEVAL END) As BranchID,
CASE CODECOMDESC WHEN 'MOOIPLAAS' THEN 'PRETORIA' WHEN 'JOHANNESBURG' THEN 'PRETORIA' WHEN 'ROSH PINAH' THEN 'SISHEN' WHEN 'POTGIETERSRUST' THEN 'POLOKWANE' ELSE CODECOMDESC END As Branch,
Convert(Int, GROOTBOEK_CTR) As RegionID
From tblCentreCodes_code;

To try and map the branches. It shows the branches correctly but does not sum the values of the mapped branches?

Thanks

Ryan

[#4]

Can you post an example?

Best regards
Stefan

[#5]

How can I send you the file, then it will be easy for you to see?

Thank you

[#6]

Ok, this is the code in my file. I have 1 group called Region2Branch which uses fields Region and Branch.

I want to sum certain branches under 1 branch.

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='R #,##0.00;R-#,##0.00';
SET TimeFormat='hh:mm:ss TT';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;password=;Initial Catalog=QlickViewStage;Data Source=sastkpdb;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=NCENCS02;Use Encryption for Data=False;Tag with column collation when possible=False];
Marketer:
Select CODEVAL As BEMARKERSKODE, CODECOMDESC as Marketer, Convert(Int, SENTRUM_K) as BranchID
 From tblMarketerCode_Code
 Where CODEVAL <> '0'
 And CODEVAL In (Select BEMARKERSKODE From tblModelScoreboardQV);
Region:
Select Convert(Int, CODEVAL) As RegionID, CODECOMDESC As Region
 From tblMainLedgerCentre_code;

Branch:
Select
 Convert(Int, CODEVAL) As BranchID,
 CODECOMDESC As Branch,
 Convert(Int, GROOTBOEK_CTR) As RegionID
 From tblCentreCodes_code;

Scoreboard:
Select LIDNOMMER as MemberNo, Period, AC_KEY, BEMARKERSKODE, CategoryID, Amount, Category,
 Case RecentActivity When 1 Then 'Yes' Else 'No' End As Active
 From tblModelScoreboardQV
 Where Period Between '200809' And '200909';

Budgets:
Select ACC_DATE as Period, BEMARKERS_KODE as BEMARKERSKODE, BEGROTE_BEDRAG As BudgetAmount, BEGROTE_EENHEDE,
 CATEGORY as CategoryID, CODECOMDESC as Category
 From tblMarketerBudget, tblProductCategory_code B
 Where ACC_DATE >= 200809
 And ACC_DATE <= Case Len(Convert(VarChar, DatePart(MM, GetDate())))
 When 1 Then Convert(VarChar, DatePart(YYYY, GetDate())) + '0' + Convert(VarChar, DatePart(MM, GetDate()))
 Else Convert(VarChar, DatePart(YYYY, GetDate())) + Convert(VarChar, DatePart(MM, GetDate()))
 End
 And Category In (1, 2, 3)
 And Category = CODEVAL;

[#7]

ryand wrote:
How can I send you the file, then it will be easy for you to see?
Thank you<div></div>

I have send you a private message with my eMail ...

Best regards

Stefan

[#8]

Hi,

I have received your file but I do not understand your problems, unfortunately ...

Which part of your application does not work as expected?

Best regards
Stefan