État croisé et paramétré avec rubrique calculé.

**djenny** · 19/10/2013, 16h49

Salut à tous! Je suis djenny, débutant en WinDev (version 15).
Je suis entrain de refaire un logiciel que j'avais crée avec vb 6.0, access 2007 et cristal report pour mieux assurer la portabilité. En fait, j'utilise maintenant Windev et hyperfileSQL mais qui ne me permet pas de créer des requêtes croisés dynamiques, hors ce sont ces requêtes qui m'ont aidé à faire mes états avec cristal report. du moins je me suis servi des états croisés mais mon problème est que je suis limité à 2 champs seulement, je ne sais pas ajouter le champ année qui devrait passé en paramètre et surtout le champ calculé qui doit me faire le total de chaque montant se trouvant sur une même ligne mais convertit en dollar.
Voici en image ce que je veux obtenir.

Sans oublier que les résultats de l'état doit être en fonction de l'année qui sera paramétrée.
Merci d'avance pour votre aide et plein succès à tous vos projets

**Menadel** · 24/10/2013, 18h01

Salut,

récupère le code sql de ta requête tableau croisé dynamique à partir de access 2007 et utilise cela dans l'éditeur de requête avec l'option "saisir le code Sql" lorsque tu veut créer une nouvelle requête,ensuite utilise cette requête pour générer ton état

**djenny** · 24/10/2013, 20h30

Merci de ton aide Menadel.
J'ai fais ce que tu m'as demandé, voici mes codes sql que j'ai copiés depuis access:

TRANSFORM SUM(OPERATION.Montant) AS SommeDeMontant
SELECT COMPTE.Num_compte, COMPTE.Lib_compte, DatePart("yyyy",[OPERATION.DatOperation]) AS Expr1, SUM([OPERATION.Montant]*[DEVISE.Taux]) AS Val
FROM DEVISE INNER JOIN (COMPTE INNER JOIN OPERATION ON COMPTE.Num_compte = OPERATION.Num_compte) ON DEVISE.Cod_devise = OPERATION.Cod_devise
WHERE (((OPERATION.Typ_operation)="Recette"))
GROUP BY COMPTE.Num_compte, COMPTE.Lib_compte, OPERATION.Typ_operation, DatePart("yyyy",[OPERATION.DatOperation])
ORDER BY COMPTE.Num_compte
PIVOT OPERATION.Cod_devise;

Mais lorsque j'exécute cette requête sur WinDev 15, il me donne un message de la correction de la requête et qui s'en suit d'un message d'erreur d'initialisation de la requête.
Pour plus de précision, j'ai joint deux images dans ce message!
Encore une fois de plus merci d'avoir répondu à ma discussion.
A tous les experts je dis aussi merci d'avance!

**Menadel** · 29/10/2013, 17h57

Salut
L'erreur que tu reçois est normale car Windev ne reconnait pas les ordres SQL qui sont dans ta requête :TRANSFORM & PIVOT,consulte l'aide de Windev,dans index, la partie: "SQL, Fonctions supportées par HyperFileSQL".
je n'ai pas vu ton analyse mais je pense que tu peut toujours réaliser ton état croisé et paramétré en faisant une requête d'agrégation sans TRANSFORM & PIVOT dans l'éditeur de requête de Windev.

**laurent30s** · 30/10/2013, 12h01

Envoyé par djenny

...Windev et hyperfileSQL mais qui ne me permet pas de créer des requêtes croisés dynamiques, hors ce sont ces requêtes qui m'ont aidé à faire mes états avec cristal report. du moins je me suis servi des états croisés mais mon problème est que je suis limité à 2 champs seulement, je ne sais pas ajouter le champ année qui devrait passé en paramètre et surtout le champ calculé qui doit me faire le total de chaque montant se trouvant sur une même ligne mais convertit en dollar.
Sans oublier que les résultats de l'état doit être en fonction de l'année qui sera paramétrée.

Il est possible de faire un requête croisés dynamique mais comme tu peux le faire avec access par ce qu'hyperfile ne connait pas TRANSFORM et PIVOT.

Pour contourner le problème il faut faire 2 requête

La 1ère doit te retourner la liste des colonnes dynamique.
Si j'ai bien compris dans ton cas il faut qu'elle retourne
FC
USD
EURO
TOT EN USD

Puis tu vas construire une 2ème requête dans une chaîne que tu exécuteras avec HExécuteRequêteSQL()
Le but de cette requête est d'ajouter les colonnes nécessaires de manière conditionnelle (résultat de la 1ère requête)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
sdRQ2 est une Source de Données
sMaRequete est une chaîne
 
sMaRequete = [
SELECT
	NoCompte,
	LibelleCompte,	
]
POUR TOUT REQ_1 		// 1ère requête en supposant que le nom de la colonne retournée soit "Monnaie"
	sMaRequete += RC + "Sum(CASE Monnaie WHEN " + REQ_1.Monnaie + " THEN Montant ELSE 0 END) AS " + REQ_1.Monnaie + ","		
FIN
// on enlève la dernière virgule
sMaRequete = SansCaractèreDroite(gsMaRequete,",")
// on poursuit la construction de la requête
sMaRequete += RC + [
FROM
	etc...
]
// on exécute la 2ème requête
SI PAS HExécuteRequêteSQL(sdRQ2,sMaRequete) ALORS Erreur(HErreurInfo); RETOUR

Bien entendu il faut avoir exécuter la 1èere requête avant d'exécuter ce code.

Pour t'aider plus il faut que tu en dises plus sur ton analyse pour les fichiers concernés...

**djenny** · 30/10/2013, 18h00

Merci beaucoup laurent30s de ton aide.
Au fait je ne suis pas tellement fort aux requêtes SQL, j'ai souvent utilisé l'éditeur des requêtes puis récupérer les codes SQL après.
Je suis persuader que tes codes peuvent marcher, seulement je ne sais pas quoi mettre et à quel endroit...
Aussi par rapport à la première requête je veux savoir s'il s'agit d'une requête intégrée dans l'état croisé ou une simple requête SQL et comment puis-je faire la colonne de Total en USD.

Pour mieux t'éclairer j'ai dû joindre en image mon analyse et l'état que je veux obtenir.
Encore une fois de plus merci laurent30s de ta disponibilité et ta sollicitude!
Merci également à Menadel qui ne cesse de réagir à cette discussion et à tous ceux qui le feront par la suite!
Plein succès!

**laurent30s** · 31/10/2013, 14h34

Pour bien faire il faudrait que tu donnes d'autres précisions :

- Confirme que les entêtes de colonnes FC/USD/EURO correspondes à Code_devise et pas à Lib_devise
- Les montants qui figurent dans les colonnes FC/USD/EURO correspondent à operation.Mont ou s'agit-il d'un calcul avec le taux de devise ?
- Il semble que la colonne TOT (USD) correspondent à un calcul, lequel ?

Dans tous les cas s'il y a des calculs à faire avec le taux de devise il est souhaitable que tu donnes les formules de calcul.
Je vois que dans le fichier devise il y a le champ Dat_taux, je suppose qu'il intervient dans le choix du taux, si c'est le cas il faut que tu nous dise comment...

**djenny** · 31/10/2013, 17h23

Merci Laurent.
Je confirme que:
1. les entêtes de colonnes FC/USD/EURO correspondent à Code_devise.
2. Les montants qui figurent dans les colonnes FC/USD/EURO correspondent à opération.Mont (s'il faut parler d'un calcul se sera la somme de montant selon que les comptes, les dates... sont groupés) le champ taux ici n'intervient pas.
3. la colonne TOT (USD) correspondent à un calcul qui est le suivant:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

TOT_USD = (Opération.Mont * 1) / Devise.Taux

En principe le "1" de la formule représente la valeur de taux(USD).
Ex: j'ai reçu 9000 fc, j'aurai donc 9000 dans la colonne "FC", dans la colonne TOT_USD le calcul sera: TOT_USD = Operation. Mont(FC) * 1 / Taux(FC).
Le champ Dat_taux nous aide du fait que les taux ne sont pas statiques, s'il y a eu une modification du taux il faudra préciser la date de la modification.
Voici les taux de chaque devise:
FC = 900
USD =1
EURO=1.3
Chaque ligne de la colonne TOT_USD converti tout le montant en en dollar (USD) et en calcul la somme.
Merci d’avance et plein succès !

**laurent30s** · 31/10/2013, 20h41

J'ai tout mis dans un procédure TCD_operations_periode()

J'ai considéré que la requête n'extrait que les opérations d'une période choisi dans des champs de saisie (SAI_Date_Debut et SAI_Date_Fin)

Cette requête crée autant de colonne de devises que nécessaire (si tu ajoutes d'autres devises, les colonnes s'ajouteront automatiquement)

Le résultat de la requête est envoyer dans la TABLE_TCD_operation_periode avec une ConstruitTableFichier()
TABLE_TCD_operation_periode peut ne contenir qu'une seule colonne que tu rends invisible
Quand tu exécuteras la procédure, toutes les colonnes de la table seront crées en fonction du résultat de la requête

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
PROCEDURE TCD_operations_periode()
 
nNb_colonne est un entier
sMa_requete est une chaîne
sdRQ1 est une Source de Données
sdRQ2 est une Source de Données
 
sMa_requete = [
SELECT DISTINCT 
	OPERATION.Cod_devise AS Cod_devise
FROM 
	OPERATION
WHERE 
]
sMa_requete += RC + TAB + "OPERATION.Dat_operation BETWEEN '" + SAI_Date_Debut + "' AND '" + SAI_Date_Fin + "'"
 
// Exécution de la requête
SI PAS HExécuteRequêteSQL(sdRQ1,MaConnexion1,hRequêteSansCorrection,sMa_requete) ALORS Erreur("Problème requête 1"); RETOUR
 
sMa_requete = [
SELECT  
	op.Num_compte,
	op.Dat_operation ,
	COMPTE.Lib_compte,
]
// Les colonnes de devise sont construites dynamiquement ici
POUR TOUT sdRQ1 
	sMa_requete += RC + TAB + "SUM(CASE op.Cod_devise WHEN '" + sdRQ1.Cod_devise + "' THEN op.Mont ELSE 0 END) AS " + sdRQ1.Cod_devise + ","	
FIN
 
// La sous requête "op" effectue les conversions des devises
sMa_requete += RC + [
	Sum(op.TOT) AS TOT
FROM 
	(
		SELECT  
			OPERATION.Num_compte,
			OPERATION.Dat_operation,
			OPERATION.Cod_devise,
			OPERATION.mont,
			Taux_USD,
			DEVISE.Taux,
			OPERATION.mont * Taux_USD / DEVISE.Taux AS TOT
		FROM 
			OPERATION,
			DEVISE,
			(	SELECT	Taux AS Taux_USD
			FROM		DEVISE
			WHERE	Cod_devise = 'USD'
			) AS	USD
		WHERE 
			OPERATION.Cod_devise = DEVISE.Cod_devise
]
sMa_requete += RC + TAB + "AND	OPERATION.Dat_operation BETWEEN '" + SAI_Date_Debut + "' AND '" + SAI_Date_Fin + "'"
sMa_requete += RC + [
	) AS op,
	COMPTE
WHERE 
		op.Num_compte = COMPTE.Num_compte
GROUP by Num_compte,Dat_operation,Lib_compte
]
 
// Exécution de la requête
SI PAS HExécuteRequêteSQL(sdRQ2,MaConnexion1,hRequêteSansCorrection,sMa_requete) ALORS Erreur("Problème requête 2"); RETOUR
 
// remplit la table TCD
ConstruitTableFichier(TABLE_TCD_operation_periode,sdRQ2,taRemplirTable)
 
// renomme les colonnes
{"TABLE_TCD_operation_periode._COL1"}..Titre = "N° compte"
{"TABLE_TCD_operation_periode._COL2"}..Titre = "Date"
{"TABLE_TCD_operation_periode._COL3"}..Titre = "Libellé compte"
 
nNb_colonne = TableOccurrence(TABLE_TCD_operation_periode,toColonne)
{"TABLE_TCD_operation_periode._COL"+nNb_colonne}..Titre = "TOT (USD)"

En espérant avoir bien compris ton problème...

**djenny** · 01/11/2013, 13h24

Wow!

- C’est génial.
- Non c’est bizarre.
- Justement quand c’est simple y’a des milliers de réponses et quand c’est bizarre y’en a aucune.

Juste pour te remercier Laurent!

J'ai enfin retrouvé ma requête croisée dynamique

Seulement j'ai encore 2 petits soucis:
1. Comment récupérer le résultat de cette requête (Toujours paramétrée) pour en faire un état?
2. Pour certains cas je voudrais que la date me renvoi juste l'année, par ex: "2013" au lieu de "01/11/2013".

Sinon la discussion est à 90% résolue.
Encore une fois de plus merci beaucoup Laurent!!!
Puisse tes projets réussir d'avantage!!!

**laurent30s** · 04/11/2013, 18h01

Envoyé par djenny

1. Comment récupérer le résultat de cette requête (Toujours paramétrée) pour en faire un état?

Il suffit de placer ce code dans un bouton

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
// Spécification des paramètres d'impression : 
// avec aperçu, en mode paysage
iAperçu(i100)
iParamètre(iOrientation, iOrientation_Paysage)
 
// Impression de la table, sans la date et avec le nombre de pages
TableImprime(TaTABLE)

Envoyé par djenny

2. Pour certains cas je voudrais que la date me renvoi juste l'année, par ex: "2013" au lieu de "01/11/2013".

Pour ça il faut connaître les critères des "certains cas" et dans les SELECT de la requête concaténer une chaîne qui présentera la date ou l'année...

**djenny** · 05/11/2013, 18h40

Merci beaucoup Laurent!
Merci également à Menadel et à toute l'équipe de développez.com pour votre aide!

Je conclue cette discussion à admettant le fait qu'il est possible de faire les requêtes et états croisés et paramétrés avec une rubrique de calcul en utilisant HyperfileSQL et le Wlangage de Windev.

Encore une fois de plus merci beaucoup à Laurent pour ton attention particulière et ta disponibilité à répondant à toutes mes questions!
Plein succès!

État croisé et paramétré avec rubrique calculé. [WD15]

WinDev

Discussions similaires

Partager

Partager