Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Can you post an example?
Best regards
Stefan
How can I send you the file, then it will be easy for you to see?
Thank you
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;
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
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