Discussion :

[iCorse]

Bonjour à toutes et tous,

Novice dans le SQL, j'ai pu (sous Excel) en suivant divers fils de discussion, me dépatouiller et sortir des stat pour mes commerciaux...

Par contre, j'ai un onglet par requête:

-CA Facturés (Commandes livrées)

Code sql :

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

1
2
3
4
SELECT CO_Nom, sum(DL_MontantHT) AS FACTURES
FROM F_DOCLIGNE, F_COLLABORATEUR
WHERE F_DOCLIGNE.CO_No = F_COLLABORATEUR .CO_No and F_DOCLIGNE.DO_Type = 7 and F_DOCLIGNE.DO_Date BETWEEN ? and ?
Group by CO_Nom

-CA Encours (Commandes en cours de Prépa, en cours de Livraison, facturées) = Estimation de leur CA à terme

Code sql :

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

1
2
3
4
SELECT CO_Nom, sum(DL_MontantHT) AS VENTES
FROM F_DOCLIGNE, F_COLLABORATEUR
WHERE F_DOCLIGNE.CO_No = F_COLLABORATEUR .CO_No and F_DOCLIGNE.DO_Type IN (3, 6, 7) and F_DOCLIGNE.DO_Date BETWEEN ? and ?
Group by CO_Nom

J'ai deux "paramètres" (?) correspondant aux dates de la période, à savoir les cellules C1 et D1 de mes feuilles EXCEL

Jusque là, tout va bien...

Mais lorsque je souhaite "compiler" ces 2 résultats sur un même tableau (2 colonnes : Facturés/Encours)... les "?" ne sont plus reconnus!
Par contre, si je saisi les dates "en dur", j'obtient ce que je veux!

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT Z.CO_Nom, sum(Z.FACTURES) AS FACTURES, sum(Z.ENCOURS) AS ENCOURS
FROM
(SELECT CO_Nom, sum(DL_MontantHT) AS FACTURES, 0 AS ENCOURS
FROM F_DOCLIGNE, F_COLLABORATEUR
WHERE F_DOCLIGNE.CO_No = F_COLLABORATEUR .CO_No and F_DOCLIGNE.DO_Type = 7 and F_DOCLIGNE.DO_Date BETWEEN '01/09/2021' and '30/09/2021'
Group by CO_Nom
UNION
SELECT CO_Nom, 0 AS FACTURES, sum(DL_MontantHT) AS ENCOURS
FROM F_DOCLIGNE, F_COLLABORATEUR
WHERE F_DOCLIGNE.CO_No = F_COLLABORATEUR .CO_No and F_DOCLIGNE.DO_Type IN (3, 6, 7) and F_DOCLIGNE.DO_Date BETWEEN '01/09/2021' and '30/09/2021'
Group by CO_Nom) AS Z
GROUP BY Z.CO_Nom

Comment puis-je intégrer mes "cellules DATE" à cette requête?

Dans l'attente de vous lire,
D'avance merci

[Pierre Fauconnier]

Salut.

Je n'ai pas vu comment tu passais tes paramètres, et ce serait utile que tu montres le code qui les utilise.

Si tu as 4 ? dans la requête, tu pourrais lui passer 4 paramètres, soit Date1, Date2, Date1, Date2 (tu dois "doubler" le passage des paramètres dans l'ordre de lecture).

Si tu as la main côté sql, tu pourrais utilement créer une procédure stockée (ou une requête "sélection" en Access). Quel est ton moteur de données?

[iCorse]

Comme dit, dans mon post initial...
Si je ne demande qu'une simple extraction Nom des commerciaux//CA Facturés (Doc_Type=7) :
Mes "?" correspondent aux paramatre1 et parametre2, que je renseigne via le bouton "Paramètres"



idem pour mon extraction Commerciaux//Encours

C'est en testant de compiler le tableau Commerciaux//Facturés//Encours, que je n'ai plus la possibilité de parametrer quoique ce soit...
Seule la saisie des dates, directement dans la requete, me permet d'obtenir le tableau souhaité

Quel est ton moteur de données?

Vraiment novice dans ce domaine, je ne saurai pas te dire...
En fouillant un peu partout, j'ai trouvé l'adresse du serveur SQL, que j'ai au début exploité via "Power Pivot" ... et un jour, sans savoir comment, j'ai trouvé la fenêtre "propriétés de connexion" dans laquelle se trouve le "texte de commande :"
Depuis ce jour, je fais des copies de mon classeur "Excel initial" et me contente de modifier le texte de commande!

Je suis incapable de partir d'un fichier Excel vierge et d'y insérer une requête SQL
Je sais c'est nul, mais je ne sais pas faire autrement pour l'instant... je continue d'apprendre!
avantage, j'apprends vite

[iCorse]

Je n'ai pas vu comment tu passais tes paramètres, et ce serait utile que tu montres le code qui les utilise.

je n'ai peut-être pas répondu à ta question...
Mes paramètres sont les "?" utilisés dans

Code :

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

F_DOCLIGNE.DO_Date BETWEEN ? and ?

Puis le bouton "paramètres" me permet de faire le lien avec les cellules


Et ce sont ces paramètres que je souhaite intégrer en lieu et place des dates (en rouge) dans ma requête ...

Espérant avoir été plus clair

[Pierre Fauconnier]

Chaque ? représente un paramètre.

Donc pour ta requête d'union "Select ... from T1 where F_DOCLIGNE.DO_Date BETWEEN ? and ? union Select ... from T2 where F_DOCLIGNE.DO_Date BETWEEN ? and ?", tu devrais avoir 4 paramètres dans la fenêtre des paramètres que tu illustres, et tu pourrais renseigner tes cellules.

[iCorse]

Chaque ? représente un paramètre.

Donc pour ta requête d'union "Select ... from T1 where F_DOCLIGNE.DO_Date BETWEEN ? and ? union Select ... from T2 where F_DOCLIGNE.DO_Date BETWEEN ? and ?", tu devrais avoir 4 paramètres dans la fenêtre des paramètres que tu illustres, et tu pourrais renseigner tes cellules.

C'est ce à quoi, je m'attendais, mais ça n'est malheureusement pas le cas...

A la place, j'ai ces messages d'erreur:


puis...


Par contre, quand je relis ton post, je vois que tu écris "from T1 where ... from T2 where...".
Est-ce pour l'exemple? ou devrais-je faire apparaitre cette expression "From Tx"?

[Pierre Fauconnier]

Je ne vois pas quelle requête tu utilises.

Voici un exemple avec une requête Access qui correspond structurellement à la tienne. Tu remarqueras que la requête d'union reprend bien 4 ? (deux par between) et donc, la fenêtre des paramètres propose bien 4 paramètres...

[Pierre Fauconnier]

Tu pourrais aussi te tourner vers Power Query. Tu pourrais alors

• soit récupérer les deux requêtes non filtrées, les combiner puis filtrer (les paramètres sont alors des paramètres "POWER QUERY");
• soit récupérer chaque requête, filtrer chaque requête puis combiner (paramètres POWER QUERY);
• soit filtrer chaque requête lors de la récupération avec des paramètres Power Query que tu passes à la chaine de requête, puis combiner.

Note que les deux premières solutions importent TOUTES les données dans Power Query avant de filtrer, alors que la troisième envoie les paramètres au serveur pour ne récupérer que les données filtrées. La troisième solution est la plus performante (la moins consommatrice de données) et c'est donc en fonction de ta connexion aux données et du volume des données non filtrées qu'il faudra choisir l'une ou l'autre.

[iCorse]

Je ne vois pas quelle requête tu utilises.

Ci-dessous la requête que j'utilise... Et dans laquelle je souhaiterais pouvoir insérer mes paramètes "?", en lieu et place des "dates saisies"

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT Z.CO_Nom, sum(Z.FACTURES) AS FACTURES, sum(Z.ENCOURS) AS ENCOURS
FROM
(SELECT CO_Nom, sum(DL_MontantHT) AS FACTURES, 0 AS ENCOURS
FROM F_DOCLIGNE, F_COLLABORATEUR
WHERE F_DOCLIGNE.CO_No = F_COLLABORATEUR .CO_No and F_DOCLIGNE.DO_Type = 7 and F_DOCLIGNE.DO_Date BETWEEN '01/09/2021' and '30/09/2021'
Group by CO_Nom
UNION
SELECT CO_Nom, 0 AS FACTURES, sum(DL_MontantHT) AS ENCOURS
FROM F_DOCLIGNE, F_COLLABORATEUR
WHERE F_DOCLIGNE.CO_No = F_COLLABORATEUR .CO_No and F_DOCLIGNE.DO_Type IN (3, 6, 7) and F_DOCLIGNE.DO_Date BETWEEN '01/09/2021' and '30/09/2021'
Group by CO_Nom) AS Z
GROUP BY Z.CO_Nom

Quand même étrange, que lorsque je remplace les dates par des "?", la fenêtre des paramètres ne me propose pas 4 paramètres...
Voire m'affiche un message d'erreur "Nombre de paramètres non valide"!

Tu pourrais aussi te tourner vers Power Query. Tu pourrais alors

• soit récupérer les deux requêtes non filtrées, les combiner puis filtrer (les paramètres sont alors des paramètres "POWER QUERY");
• soit récupérer chaque requête, filtrer chaque requête puis combiner (paramètres POWER QUERY);
• soit filtrer chaque requête lors de la récupération avec des paramètres Power Query que tu passes à la chaine de requête, puis combiner.

Note que les deux premières solutions importent TOUTES les données dans Power Query avant de filtrer, alors que la troisième envoie les paramètres au serveur pour ne récupérer que les données filtrées. La troisième solution est la plus performante (la moins consommatrice de données) et c'est donc en fonction de ta connexion aux données et du volume des données non filtrées qu'il faudra choisir l'une ou l'autre.

Je vais me pencher sur l'utilisation de Power Query et voir si cela peut m'apporter une solution.

En tout cas, merci beaucoup pour tes recherches et conseils...

[Pierre Fauconnier]

Salut.

Tu dois remplacer les dates avec les apostrophes. BETWEEN '01/09/2021' and '30/09/2021' devient donc BETWEEN ? and ?. Il faut que tu montres la requête exactement comme tu l'utilises, sinon on ne sait forcément pas voir ce qui pose problème.

[iCorse]

Salut.

Tu dois remplacer les dates avec les apostrophes. BETWEEN '01/09/2021' and '30/09/2021' devient donc BETWEEN ? and ?. Il faut que tu montres la requête exactement comme tu l'utilises, sinon on ne sait forcément pas voir ce qui pose problème.

Je veux bien ne pas être doué en langage SQL, mais quand même...

Voilà donc , la requête que je saisie:

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT Z.CO_Nom, sum(Z.FACTURES) AS FACTURES, sum(Z.ENCOURS) AS ENCOURS
FROM
(SELECT CO_Nom, sum(DL_MontantHT) AS FACTURES, 0 AS ENCOURS
FROM F_DOCLIGNE, F_COLLABORATEUR
WHERE F_DOCLIGNE.CO_No = F_COLLABORATEUR .CO_No and F_DOCLIGNE.DO_Type = 7 and F_DOCLIGNE.DO_Date BETWEEN ? and ?
Group by CO_Nom
UNION
SELECT CO_Nom, 0 AS FACTURES, sum(DL_MontantHT) AS ENCOURS
FROM F_DOCLIGNE, F_COLLABORATEUR
WHERE F_DOCLIGNE.CO_No = F_COLLABORATEUR .CO_No and F_DOCLIGNE.DO_Type IN (3, 6, 7) and F_DOCLIGNE.DO_Date BETWEEN ? and ?
Group by CO_Nom) AS Z
GROUP BY Z.CO_Nom

Mais, une fois validée...

• Je n'obtiens pas mes 4 paramètres
• J'ai les 2 messages d'erreur précédemment cités ("nombre de parametres non valide" et "Index de descripteur non valide")

[Pierre Fauconnier]

Je ne voulais pas te prendre pour une cruche, mais sans voir exactement la requête utilisée, je ne pouvais que supposer que les remplacements étaient corrects, mais sans en être certain.


La première fois que j'ai créé la requête, je n'avais pas la liste des paramètres, mais j'ai eu l'invite suivante au moment de valider:



Ce n'est qu'après avoir validé les 4 paramètres que la fenêtre de modif de la requête a activé le bouton "paramètres" pour afficher la liste des paramètres.

[EDIT] J'avais pensé déporter la clause WHERE sur Z, mais vu le Group By pour sommer les montants, ce n'est pas possible.


Il faudrait voir le volume de données et le moteur utilisé. Si c'est un serveur et qu'il y a beaucoup de données, ça peut être intéressant de filtrer côté serveur avec la clause Where. Il faudrait tester un Power Query pour voir si c'est pénalisant en temps de créer deux requêtes PQ qui ramènent toutes les données, les filtrent, les groupent sur le nom puis que l'on combinerait en un seul tableau. En paramétrisant les dates bornes dans Excel (cellule nommée ou tableau, voir mes billets sur le sujet), il y a des solutions à regarder de ce côté, je pense.

[iCorse]

Je ne voulais pas te prendre pour une cruche, mais sans voir exactement la requête utilisée, je ne pouvais que supposer que les remplacements étaient corrects, mais sans en être certain.

Y'a aucun problème, t'inquiètes!

La première fois que j'ai créé la requête, je n'avais pas la liste des paramètres, mais j'ai eu l'invite suivante au moment de valider:



Ce n'est qu'après avoir validé les 4 paramètres que la fenêtre de modif de la requête a activé le bouton "paramètres" pour afficher la liste des paramètres.

[EDIT] J'avais pensé déporter la clause WHERE sur Z, mais vu le Group By pour sommer les montants, ce n'est pas possible.

Quand je crée les requêtes individuellement, sans "UNION" (soit ENCOURS, soit FACTURES), j'ai l'invite des paramètres... Mais quand je saisi la requête entièrement!

Du coup, pour l'instant, j'ai mes 2 onglets avec mes requetes indivuelles ... et je compile sur un 3ème onglet via une "recherchev"

Je vais bien finir par trouver...

[Pierre Fauconnier]

[...]et je compile sur un 3ème onglet via une "recherchev"

Je vais bien finir par trouver...

RechercheV? Perso, à ce stade, je "compilerais" avec Power Query (Je n'aime pas du tout RECHERCHEV, même si j'ai écrit un tuto dessus) et j'irais vers une solution "tout Power Query", tant qu'à faire. Tout dépend du nombre de lignes à traiter au niveau du filtre. Si c'est un serveur de données, c'est souvent plus intéressant de filtrer à la source, mais il faudrait déjà un très gros volume de données pour que ce soit pénalisant de ramener toutes les lignes dans PQ et de filtrer au niveau de la requête PQ.

Je vais essayer de recréer ta situation sur SQL Server pour voir comment ça se comporte au niveau de la fenêtre des paramètres.

[iCorse]

RechercheV? Perso, à ce stade, je "compilerais" avec Power Query (Je n'aime pas du tout RECHERCHEV, même si j'ai écrit un tuto dessus) et j'irais vers une solution "tout Power Query", tant qu'à faire. Tout dépend du nombre de lignes à traiter au niveau du filtre. Si c'est un serveur de données, c'est souvent plus intéressant de filtrer à la source, mais il faudrait déjà un très gros volume de données pour que ce soit pénalisant de ramener toutes les lignes dans PQ et de filtrer au niveau de la requête PQ.

Je vais essayer de recréer ta situation sur SQL Server pour voir comment ça se comporte au niveau de la fenêtre des paramètres.

Je ne connais pas PowerQuery, ni même le SQL etc etc

Je ne sais pas si tu es allé voir ma présentation. Dans le doute...
Je suis très à l'aise avec la suite Office et suis très curieux par nature! Ne suis pas réfractaire aux langages informatiques et j'apprends vite, même si ce n'est pas mon métier!
J'ai toujours été dans des grosses structures, avec informaticiens et/ou contrôleur de gestion, gros logiciels et avec mes indicateurs de gestion déjà fournis.
Depuis peu, j'ai intégré une petite entreprise familiale, qui plus est réfractaires aux nouvelles technologies.
De ce fait, parler d'investissement dans des mises à jour (hardware comme software) ou autres BI reporting est très compliqué.
J'ai donc tâtonné et chiné sur la toile diverses informations jusqu'à parvenir à des requêtes de base, me permettant d'obtenir une partie des informations dont j'ai besoin.

Ma plus grande frustration, y être parvenu, sans être capable de renouveler l'opération!
Je le redis, je suis "incapable", mais réellement "INCAPABLE", de partir d'un fichier Excel vierge et d'arriver à la fenêtre "Propriétés de connexion..."

Mes premières connexions à notre serveur SQL furent faites par le biais de "Power Pivot" dans Excel (cela m'a permis de comprendre la structure des tables, identifier les noms de champs dont j'ai besoin).
Puis à force de trifouiller en essayant de reproduire ce que je lisais sur divers forums, j'en suis arrivé à obtenir un fichier Excel, dans lequel j'ai une connexion SQL paramétrable.

Depuis ce jour, je le redis "INCAPABLE" de reproduire cela, je duplique mes fichiers, puis modifies mes requêtes.

Ce qui pourrait, d'ailleurs, être une raison possible de mon blocage... Ne me permettant pas de rajouter d'autres "Paramètres".

Désolé pour ce long roman, mais c'est vraiment pour que tu comprennes que ce n'est pas l'envie de ne pas suivre tes conseils (quand tu me proposes de compiler par PQ) qui me fait défaut, mais réellement mes compétences!!

[Pierre Fauconnier]

Je suis allé voir ta présentation. Merci pour les détails que tu y apportes. C'est intéressant.

Tu dis travailler avec Sage... Perso, je sais que BOB50 (logiciel de compta (belge?) repris par Sage travaille avec SyBase comme "serveur" de données. Tu parles de serveur SQL, c'est de MS SQL dont il s'agit?

[iCorse]

Je suis allé voir ta présentation. Merci pour les détails que tu y apportes. C'est intéressant.

Tu dis travailler avec Sage... Perso, je sais que BOB50 (logiciel de compta (belge?) repris par Sage travaille avec SyBase comme "serveur" de données. Tu parles de serveur SQL, c'est de MS SQL dont il s'agit?

MS SQL Server 2012, pour être très précis!

[Pierre Fauconnier]

MS SQL Server 2012, pour être très précis!

Tant qu'à faire, ta version d'Excel?

[iCorse]

Tant qu'à faire, ta version d'Excel?

Excel 2019

[Pierre Fauconnier]

Pour rester d'abord dans la configuration qui est la tienne:

• J'ai créé les deux tables sur mon serveur sql de tests;
• J'ai copié-collé ta requête telle que tu la donnes.

J'ai bien eu la demande des 4 paramètres, et lorsque je reviens dans la requête après, j'ai bien le bouton "Paramètres" accessible et il me donne bien la fenêtre permettant de les valoriser tous les 4.




Cela étant dit, j'ai remarqué une certaine instabilité dans l'apparition et la gestion des fenêtres de paramètres, lors de mes tests. Du coup, j'avais saisi une simple requête "SELECT CO_Nom, 0,0 from F_DOCLIGNE" et l'avais validée. Pas de fenêtre de paramètres, donc. Puis j'ai recollé TA requête complète et j'ai récupéré la demande des paramètres.

Peut-être cela pourrait-il être une piste pour toi? Peux-tu tester cela? Si ça donne rien, on s'aiguillera vers une solution Power Query pour voir si ça tient la route en fonction du volume de données.