Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi every one
i have a problem,
i have month dimension with 1,2,3,...,12 value
i want to create a list box show that
فروردین
اردیبهشت
خرداد
تیر
مرداد
شهریور
مهر
آبان
آذر
دی
بهمن
اسفند
sort فروردین to اسفند is very important,
can anyone help me?
Use a dual value when creating your Month values, either by using QV date and time functions like Month() or by explicitely using dual(), like
LOAD dual(F1, recno()) as Month INLINE [
F1
فروردین
اردیبهشت
خرداد
تیر
مرداد
شهریور
مهر
آبان
آذر
دی
بهمن
اسفند
];
tanks swuehl
it's work lonely but
it's not work,i type your script in my Qv Script
i connect to sql source for my reports,
when i reload my doucument , i see the below error :
can you help me more ?
with best regards
i solve my recent problem
i forgot to put ; (semicolon) before load in my script ,
now how can i relate this month to related code?
my month code are : 1 ... 12
my month name is فروردین .... اسفند
relationships are :
1 = فروردین
2 = اردیبهشت
3 = خرداد
.
.
.
12 = اسفند
can you help me more ?
thanx swuehl
Use a preceding LOAD to your SQL code:
(I am using english names, just replace with your field name and values)
LOAD
//List all your field names except the MonthName field first
Field1,
Field2,
Dual( MonthName,
Match( MonthName, 'Jan', 'Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') )
AS MonthName
;
SQL ... FROM ...;
unfortunately it's not work:
my script is :
//
LOAD
[ردیف]
,[کد کل]
,[شرح کل]
,[کد معین]
,[شرح معین]
,[کد تفصیلی 1]
,[شرح تفصیلی 1]
,[کد تفصیلی 2]
,[شرح تفصیلی 2]
,[کد تفصیلی 3]
,[شرح تفصیلی 3]
,[مبلغ بدهکار]
,[مبلغ بستانکار]
,[شرح سند]
,[شماره سند]
,[کد نوع سند]
,[شرح نوع سند]
,[کد بندر]
,[سال سند]
,[روز سند],
Dual( [ماه سند],
Match( [ماه سند], 'فروردین','اردیبهشت','خرداد','تیر','مرداد','شهریور','مهر','آبان','آذر','دی','بهمن','اسفند') )
AS [ماه سند]
;
SQL select
[ردیف]
,[کد کل]
,[شرح کل]
,[کد معین]
,[شرح معین]
,[کد تفصیلی 1]
,[شرح تفصیلی 1]
,[کد تفصیلی 2]
,[شرح تفصیلی 2]
,[کد تفصیلی 3]
,[شرح تفصیلی 3]
,[مبلغ بدهکار]
,[مبلغ بستانکار]
,[شرح سند]
,[شماره سند]
,[کد نوع سند]
,[شرح نوع سند]
,[کد بندر]
,[سال سند]
,[ماه سند]
,[روز سند]
FROM [SimaAccountHolding].[dbo].[Acn4BI] a
where a.[کد کل] in(81,82,83,84,85,89,71,72,73,74,75,76)
and a.[کد بندر] in (1153,10243,10244,10372,10373,10374,10375,10376,10377,10378,10401,103078,3304,3325,10379,10380)
and a.[کد نوع سند] <>6;
//
is it true?
Hm, I am not sure, I can't read your letters
First argument to Dual() function should be a field name, not quoted using single quotes, but maybe embedded in [...] like the other field names in the LOAD.
Same for first argument to Match(), a (in fact the same as before) field name, embedded in []. Looks like this field name is last in your argument list, not first (and not embedded in [..].
All other 12 arguments to Match() should be literals, not field names. Quote literals using single quotes. The literals are giving the exact month values as they appear in the field from above (the one you want to sort correctly).
In english:
Dual( [MonthFieldName],
Match( [MonthFieldName], 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December')
) AS [MonthfieldName]
So take care to embed the Month field name in [..], all literals in single quotes. And take care that the values you are matching ('January', ...) are the values as they exactely appear in your field.
One more thing?
Is this field a key field or are you concatenating more records after above LOAD? Do we need to take care of other places you are loading values to that field?